Exercise Wednesday

Goal: Practice using join statements to connect tables in your database. You should also be getting more practice creating and using many to many relationships with your tables.

Warm Up

  • Practice making join statements with your partner.
  • What is the difference between a join ENTITY and a join TABLE?
  • What information do you need to know to make a join entity?
  • How is the LINQ query from a join ENTITY returned?
  • How do join entities make our lives easier?

Code

Finishing the To Do List

Begin by following along with this weekend's homework to add a many-to-many relationship to your ongoing To Do List application.

Then, integrate the following additional features.

  • Add functionality to mark an Item as completed without deleting it (Hint: Create a new boolean Item property and set a default value of true/false.)
  • Allow users to assign due dates for Items.
  • Sort items by their due date. Check out the LINQ documentation on OrderBy - let LINQ do the sorting, not C#.

University Registrar or Doctor's Office

Then select one of the following projects to complete as a multi-day project. To describe the features we want you to build, we will write user stories. User stories are a way of describing a feature that breaks functionality down into the smallest possible pieces and clearly communicates the use case, functionality, and benefit.

University Registrar

Create an app for a University registrar to keep track of students and courses. Here are some user stories for you - build one at a time before moving on to the next one.

  • As a registrar, I want to enter a student, so I can keep track of all students enrolled at this University. I should be able to provide a name and date of enrollment.
  • As a registrar, I want to enter a course, so I can keep track of all of the courses the University offers. I should be able to provide a course name and a course number (ex. HIST100).
  • As a registrar, I want to be able to assign students to a course, so that teachers know which students are in their course. A course can have many students and a student can take many courses at the same time.

If you make it this far, great job! If you have time, work on these other user stories.

  • As a registrar, I want to be able to create departments. A student can be assigned to a department when they declare their major and a course can be assigned to a department when it is created.
  • As a registrar, I want to be able to list out all of the courses or all of the students in a particular department, so that I can inform the counselors which departments need more students and which need more courses.
  • As a registrar, I want to change a student's file to show that they have completed a course, so that I can see if they need to take the course again.
  • As a registrar, I want to list out all of the courses a student has taken, so that I can see if they have met their degree requirements.
  • As a registrar, I want to see how many students have not completed courses in any particular departments, so that I can tell the administration which departments need help.
  • As a registrar, I want to …

If you make it this far in the project, think up and write some other user stories that a University registrar might have.

Doctor's Office

Our next program is for office administrators to track patients in a doctor's office. Ultimately you will have a doctor's office database with tables for doctor, patient, etc. Build out the following features that let an administrator:

  • Add a doctor to the system, including their name and specialty as columns.
  • Add a patient to a doctor, including their name and birthdate. Patient will be its own class, so make sure to build the relationship between a Doctor object and a Patient object.
  • Add more than one doctor to a patient.

Now the doctor's office has been grown to include many doctors with the same specialty. The doctors have organized themselves into specialty groups. So when a user wants to enter a doctor, they must first select a specialty and then add a doctor from there.

  • List all of the doctors in a particular specialty. A doctor can have many specialties. (Hint: change the database from storing specialty as a column to giving it its own table with a relationship to a Doctor object.)
  • View a chart of doctors including the number of patients they see. Use the SQL COUNT function; practice your online search skills to figure out how it works.

Design Your Own

If you'd prefer, choose another project that includes a many-to-many relationship with full CRUD. Get approval from an instructor before beginning. You might choose something brand new, or revisit an earlier in-class project such as Squad or CD Organizer.

Peer Code Review

  • Do database tables and columns follow proper naming conventions?
  • Do you have thorough test coverage with passing tests?
  • Did you write the test methods and make them pass before starting on routes for each class?
  • Does one of your classes have all CRUD methods implemented in your app? That includes: Create, Read (all and singular) Update and Delete (all and singular).
  • Are you able to view both sides of the many-many relationship? For a particular instance of a class, are you able to view all of the instances of the other class that are related to it? And vice versa?
  • Is the many-to-many relationship set up correctly in the database?

Lesson 14 of 14
Last updated more than 3 months ago.