Database Design - Assignment 3October 17, 2014Due Date: October 26, 2014, 11:59 PM1. Draw an ER diagram for university library system. Specify key attributes of each entity type and structural constraints on each relationship type (cardinality ratios and participation constraints). State any assumptions you make.2. Consider a grade book database in which instructors within an academic department maintain scores/points obtained by individual students in their classes. The data requirements are summarized as follows: o Students are identified by a unique student id, their first and last names, and an email address. o The instructor teaches certain courses each term. The courses are identified by a course number, a section number, and the term in which they are taught. The instructor also assigns grade cutoffs (example 90, 80, 70, and 60) for letter grades A, B, C, D, and F for each course he or she teaches. o Students are enrolled in courses taught by the instructor. o Each course being taught by the instructor has a number of grading components (such as mid-term, final exam, project, etc.). Each grading component has a maximum number of points (such as 100 or 50) and a weight (such as 20% or 10%). The weights of all the grading components of a course usually add up to 100. o Finally, the instructor records the points earned by each student in each of the grading components in each of the courses. For example, student with id=1234 earns 84 points for the grading component mid-term for the course CSc 2310 section 2 in the fall 2005 term. The mid-term grading component may have been defined to have a maximum of 100 points and a weight of 20% of the course grade.a. Design an Enhanced Entity-Relationship diagram for the grade book database.b. Map the designed EER diagram to relational schema.3. Consider the ER diagram in Figure 1. Map the given conceptual schema to relational database schema.4. Consider the EER diagram in Figure 2 for a car dealer. Map the EER schema into a set of relations. For the VEHICLE to CAR/TRUCK/SUV generalization, consider the four options (8A, 8B, 8C, 8D) and show the relational schema design under each of those options.NOTES: ER diagrams should be drawn electronically. You can use one of chart drawing tools (i.e. ERwin or lucidchart.com) to simplify drawing charts. You can use different notations to show cardinality ratios. (i.e. Crow’s Foot notation)Each item (1, 2a, 2b, 3, 4) is 20
View Full Document