Entity Relationship Diagram

Homework Assignment #3 (Mod 05)

MSIS 3333

Draw the entities and relationships suggested by each word problem. Include the proper foreign key(s). Remember to make a copy for yourself.

Ternary Relationships

1. Our used car lot only sells cars that we finance. Every car that we sell must also have insurance and a title before it rolls off the lot. We want to capture the date that we sell the car and the amount paid. Cars have a unique VIN, a make, a model, a color and a sticker price. Not every car has been sold. Insurance has an INSID, a policy date and a coverage amount. Insurance policies are only created at the time of the car purchase so every policy is attached to a car. Loans have a LoanID, interest rate, issue date and amount. Loans are also issued at the time of purchase so all loans are assigned to a car purchase event.

2. High school students take classes that are taught by teachers. Each student has a StudentID, name and grade (like Freshman). Teachers have an EmployeeID, name and salary amount. Classes have a unique name (French II) and a description. We want the capture the year, semester and grade for every student taking a class. Every student and every teacher has at least one class but some classes are still in the planning stages and haven’t been offered yet.

Historical Associative Entities

3. Dogs may, or may not, have a person hired to walk them and some dogs may have multiple dog walkers over time. A dog walker may walk many dogs or no dogs at all. Dogs have a DogID, Dogname and DogBreed. Dog walkers have an WalkerID, WalkerName and WalkerAddress. We also want to capture the date and time the dog was walked and the duration of the walk. We do not want to lose any data over time.

4. A student may earn many scholarships though not every student earns a scholarship. Scholarships are awarded each semester to students, with some scholarships going to multiple students. Some scholarships don’t get awarded in a given semester. Students have a StudentID, Name and GPA. Scholarships all have a unique name, an endowment and a reward duration (Spring 2018, Fall 2019, etc). We want to also capture the amount of the scholarship awarded to each student. Students can be awarded the same scholarship over time and we don’t want to lose any records of scholarships awarded.

Historical Ternary Relationships

5. High school students take classes that are taught by teachers. Each student has a StudentID, name and grade (like Freshman). Teachers have an EmployeeID, name and salary amount. Classes have a unique name (French II) and a description. We want the capture the year, semester and grade for every student taking a class. Every student and every teacher has at least one class but some classes are still in the planning stages and haven’t been offered yet. Students may end up taking the same class multiple times and we want to save the records of each occurrence.