Zelda has a large DVD movie collection. Her friends like to borrow her DVD’s, and she needs a way to keep track of who has what. She maintains a list of friends, identified by unique FID’s (friend identifiers) and a list of DVD’s, identified by DVDID’s (DVD identifiers). With each friend is the name and the all-important telephone numbers which she can call to get the DVD back. With each DVD is the star actor name and title. Whenever a friend borrows a DVD, Zelda will enter that fact into her database along with the date borrowed. Whenever the DVD gets returned, that fact, too, gets noted along with the date returned. Zelda wants to keep a complete history of her friends’ borrowing habits so that she can ask favors of the heavy borrowers (or perhaps refuse to make further loans to those who habitually don’t return them quickly). Below is an ER diagram for a database to help Zelda out. Provide appropriate names for entities E1, E2; attributes A1, A2, A3, A4, A5, A6, relationship R1, keys K1, K2, and structural constraints C1min, C1max, C2min, C2max.
[10 points] Consider the ERD shown below for a Scholarship Application Database, which stores data about each individual application that the HGL Young Bowlers Foundation receives. Write any 4 business rules for this database.
A recommendation must always be associated with an application (and thus the removal of an application may cause referential integrity violations)
A student must always be associated with a school (and thus the removal of a school may cause referential integrity violations)
A student must always be associated with a bowling center (and thus the removal of a bowling center may cause referential integrity violations)
A student must always be unique (and thus the addition of a student center may cause uniqueness violations)
Draw an Entity Relationship (ER) diagram that captures the following information:
Professors have an SSN, a name, an age, a rank, and a research specialty
Projects have a project number, a sponsor name, a starting date, an ending date, and a budget.
Students have an SSN, a name, an age, and a degree program (e.g. B.S. or M.S.)
Each project is managed by one professor (known as the project’s principal investigator).
Each project is worked on by one or more professors (known as the project’s investigators).
Professors can manage and/or work on multiple projects.
Each project is worked on by one or more students.
When a student works on a project, a professor must supervise their work on the project. Students can work on multiple projects; in which case they will have a (potentially different) supervisor for each one.
Department has a department number, a department name, and a main office.
Departments have a professor name (known as the chairman) who runs the department.
Professors work in one or more departments, and for each department that they work in, a time percentage is associated with their job.
Students have one major department in which they are working on their degree.
Each student has another, more senior student (known as a student advisor) who advises the student on what courses to take.
[10 points] Carter blood bank depends on a number of different types of persons for its successful operation. Draw an Enhanced Entity Relationship (EER) diagram based on the below requirements:
Each person in this organization has the following attributes: SSN, Name, Address, and Email. A person may have multiple emails.
Three types of persons are of greatest interest: employees, volunteers, and donors.
Employees only have a Date_hired attribute, volunteers only have Skills attribute, and donors only have Blood_type attribute.
Employees have a relationship named Manages with Department.
There are persons other than employees, volunteers, and donors who are of interest to the organization so that a person need not belong to any of these three groups.