Homework Assignment #04

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.

Unary Relationships

1. We run one of the nicest German Shepherd breeding facilities in the southwest region. Each dog has a name that is unique, a basic color and a disposition. We want to keep track of only the most recent breeding mate for any given dog, when they bred, and the litter size. All of our dogs are currently part of a breeding pair.

2. Same as #1, but our dogs often breed repeatedly with the same dog and we don’t want to lose any data over time.

3. Employees (Empid, name) can mentor other employees, or not. Every employee has a single mentor. Mentors only have one person that they mentor at a time (a mentee). When they get a new mentee we just overwrite the record of the previous mentee.

4. Same as #3 but we do want to keep the historical record of all mentor / mentee relationships, as well as the start and end date of each.

Homework Assignment #1

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.

1. Dogs may, or may not, have an owner but dogs never have more than one owner. An owner may own many dogs or no dogs at all. Dogs have a DogID, Dogname and DogBreed. Owners have an OwnerID, OwnerName and OwnerAddress.

2. A student may earn up to one scholarship though not every student earns a scholarship. Every scholarship is only goes to a single student. Students have a StudentID, Name and GPA. Scholarships all have a unique name, an endowment and a reward duration (Spring 2018, Fall 2019, etc).

3. An NFL player has a unique NFLID, a name, a position and a salary. Each player plays for one team. An NFL team has multiple players. Each team has a unique name, a home city, a salary cap and an owner.

4. A driver may have a traffic ticket, have no tickets or have many tickets. Each ticket has a CitationID, a date, a location and a description. Drivers have a Driver’s License ID (DLID), name and address that is composed of street, city, state and zip code. A ticket is issued to only one driver and all tickets are associated with a driver.

5.

Employees (EmpID, Name, HourlyRate) work in only one of our several warehouses. Each warehouse has several employees and always at least 5 employees. Each warehouse is also managed by one employee. Each warehouse has a unique lot number (LOTNUM), size in square feet, and may or may not have the capacity to store frozen items.

Homework Assignment #2

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.

1. Dogs may, or may not, have an owner and some dogs may have multiple owners. An owner may own many dogs or no dogs at all. Dogs have a DogID, Dogname and DogBreed. Owners have an OwnerID, OwnerName and OwnerAddress. We also want to capture the date the dog was adopted and the adoption fee paid by the owner.

2. A student may earn many scholarships though not every student earns a scholarship. Scholarships are awarded each semester, with some going to multiple students. Some scholarships may not be awarded. Students have a StudentID, Name and GPA. Scholarships all have a unique name, a total endowment and a reward duration (Spring 2018, Fall 2019, etc). We want to also capture the amount of the scholarship awarded to each student.

3. A course may have many sections or not be offered at all (no sections). Sections always belong to a single course. Students take at least one and often multiple sections during a semester. We want to capture a student’s grade and, if that grade is an F, last date attended. Courses have a CID, course name and department. Sections have a CRN, a room and a time. Sections have no students, or many students. Students have a CWID, name and year (like Sophomore).

4.

Concerts are the coming together of one or more bands at one or more locations. So a location will host one or bands and bands will play at one or more locations. When a band plays again at the same location we will just overwrite the prior data. For every concert we want to capture the ticket price and local quiet hours (if any). Bands have a unique name, a genre, and a cost. Locations have a city, state, tax rate and a venue.

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.