Database Design Assignment 3 October 17 2014 Due Date October 26 2014 11 59 PM 1 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 points
View Full Document
Unlocking...