DB Exercises 2 Lab 2 Problems Database Design

DB Exercises 2 Lab 2 Problems

Database Design Exercises: 1. DB Lab 2 -1 What problem would you encounter if you wanted to produce a listing by city using the table below? How would you solve this problem by altering the file structure? . The city names are contained within the address attribute → Decomposing this field at the application level is inefficient (slows down DB execution) address Street City State Country 777 Bonham Ct. , Durham, NC, USA 777 Bonham Ct Durham NC USA 21 Jump St. , Boston, MA 21 Jump St. Boston MA 132 Queen St. , London, England 132 Queen St. London England 1431 -C Broad Ave. , Berlin, Germany 1431 -C Broad Ave. Berlin Germany 3333 Tao St. , Shanghai, China 3333 Tao St. Shanghai China Database Design 2

Database Design Exercises : 2. DB Lab 2 -3 Below is an example of the CLASS entity implemented in MS Access. Do you see a problem? If so, how would you refine your data model? Sample DB There is a data redundancy problem (e. g. , Class Name, Code, etc. ) → Can lead to data anomalies (i. e. , Update/Insertion/Deletion Anomaly) PROFESSOR M teaches N COURSE 1 has M CLASS N ERD Refinement #1 Sample DB takes Ø Separate the course information into a COURSE entity. · A professor can teach many courses. A course can be taught by many professors · A course can consist of many classes. A class belongs to one course · A student can enroll in many classes. Each class can have many students. Database Design M STUDENT 3

Database Design Exercises : 3. DB Lab 2 -3 continued Below is an example of the CLASS entity implemented in MS Access. Do you see a problem? If so, how would you refine your data model? ERD Refinement #2 Ø Decompose many-to-many relationship PROFESSOR M 1 N teaches TEACH M M 1 Sample DB COURSE 1 has M CLASS N 1 M takes ENROLL M M 1 STUDENT Database Design 4

Database Design Exercises : 4. DB Lab 2 -4 The E-R diagram below, which models the course enrollment in a college, has a serious flaw. What is the problem and how can it be fixed? Show a revised E-R diagram. PROFESSOR 1 M teaches TEACH M 1 COURSE 1 has M CLASS 1 M ERD misses the relationship between PROFESSOR and CLASS → does not record who taught the specific classes takes ENROLL M ERD Refinement #3 Ø Relate PROFESSOR to CLASS Sample DB 1 STUDENT COURSE 1 has M CLASS M teach 1 PROFESSOR 1 M ENROLL M 1 STUDENT Database Design 5