M5A1 – Recognizing design weaknesses
Quiz Instructions
Design a set of 3NF tables (each is a separate problem) that capture all key data (PK and FK) necessary to track this data. Along the way, you may have to make assumptions due to the lack of familiarity with the domain – you would normally ASK the subject matter experts for clarification. List ALL assumptions you had to make to complete this.
In all situations, identify the tables needed clearly, all primary keys and a few non-key attributes to show the relationships as shown here:
TABLE1 (table1_PK, fields1, field 2 )
TABLE2 (table2_PK, field1, field2 table1_FK)
For bridge tables, be sure to identify the FKs properly
You will of course NOT label them as TABLE1, TABLE2 etc but use appropriate names for the entity types You can work these at any time (no time limit), but SUBMIT ONLY AFTER you answered all questions
Resource: There are several on the web, but this one (http://db4u.wikidot.com/discussion) seems well done
( A local clothing retailer needs your help! They want to track all customer purchases and retain key information about customers to provide data for Direct Marketing services. What data will you need to capture? How will you organize these? See instructions above for your responses. Do not make this overly complicated. You will ne ed multiple tables though. Edit View Insert Format Tools Table 12pt Paragraph 4 pts Question 1 )
( p 0 words )
( A conference director is preparing to host a Women in Computing conference soon. She needs to track all registration information from conference attendees. Since it is a conference, there will be multiple sessions that attendees can participate in. For simplicity assume that there will be no concurrent sessions. What data will you need to capture? How will you organize these? See instructions above for your responses Edit View Insert Format Tools Table 12pt Paragraph 4 pts Question 2 )
( p 0 words )
Question 3
3 pts
For the following ERD what attributes/columns will you need to implement the relationship? No need to list non-key attributes, but you MUST show the PK and FK.?
See instructions above for your responses
Edit View Insert Format Tools Table
12pt Paragraph
p 0 words
Question 4
3 pts
For the following ERD what attributes will you need to implement the relationship? No need to list non-key attributes, but you MUST show the PK and FK. See instructions above for your responses
Edit View Insert Format Tools Table
12pt Paragraph
p 0 words
( For this question, you are given the set of tables designed by a novice developer. Your task is to infer the design weakness based on the implied relationships. Primary keys 3 pts Question 5 )
( have been identified by making them bold and highlighting them. STUDENTS ( stu_ID_PK , stu_LastName , stu_FirstName , stu_phone , stu_email , lockerID_FK ) LOCKERS ( lockerID_PK , location, height, depth, number_shelves ) Edit View Insert Format Tools Table 12pt Paragraph p 0 words )
( For this question, you are given the set of tables as designed by a student. Your task is to infer the design weakness based on the implied relationships. Primary keys have been identified by making them bold and highlighting them. STUDENTS ( stu_ID_PK , stu_LastName , stu_FirstName , stu_phone , stu_email , classID_FK ) CLASSES ( classID_PK , room, courseID , instructor_LastName , instructor_firstName ) 3 pts Question 6 )
