Normalizing Relations (Tables)

Assignment: Normalizing relations PROBLEM 1: THE FOUNDIT COMPANY DATABASE To keep track of office furniture, computers, printers, and so on, the FOUNDIT Company uses the table structure shown in the following Table. Attribute Name Sample Value Sample Value Sample Value ITEM_ID 231134-678 342245-225 254668-449 ITEM_LABEL HP DeskJet 895Cse HP Toner DT Scanner ROOM_NUMBER 325 325 123 BLDG_CODE NTC NTC CSF BLDG_NAME Nottooclear Nottoclear Canseefar BLDG_MANAGER I. B. Rightonit I. B. Rightonit May B. Next a. Given that information, write the relational schema and draw the dependency diagram. Make sure that you label the transitive and/or partial dependencies. b. Write the relational schemas and create a set of dependency diagrams that meet 3NF requirements. Rename attributes to meet the naming conventions, and create new entities and attributes as necessary. c. Draw the Crow’s Foot ERD. PROBLEM 2:DINNER CLUB DATABASE Suppose you are given the following business rules to form the basis for a database design. The database must enable the manager of a company dinner club to mail invitations to the club’s members, to plan the meals, to keep track of who attends the dinners, and so on. • Each dinner serves many members, and each member may attend many dinners. • A member receives many invitations, and each invitation is mailed to many members. • A dinner is based on a single entree, but an entree may be used as the basis for many dinners. For example, a dinner may be composed of a fish entree, rice, and corn. Or the dinner may be composed of a fish entree, a baked potato, and string beans. • A member may attend many dinners, and each dinner may be attended by many members. Because the manager is not a database expert, the first attempt at creating the database uses the structure shown in the following table: a. Given the table structure illustrated in the Table above, write its relational schema and draw its dependency diagram. Label all transitive and/or partial dependencies. (Hint: This structure uses a composite primary key.) Note that DIN_CODE in Figure P6.7a does not determine DIN_ATTEND; just because a dinner is offered does not mean that it is attended. b. Remove all partial dependencies, draw the new dependency diagrams, and identify the normal forms for each table structure you created. c. Remove all transitive dependencies, and draw the new dependency diagrams. Also identify the normal forms for each table structure you created. c. Draw the Crow’s Foot ERD. Note that because the dependency diagrams cannot show the nature of the relationships (1:1, 1:M, M:N), the ER Diagrams remain crucial to the design effort. PROBLEM 3: FSOSS CONFERENCE SCHEDULE DATABASE FSOSS is the Free Software and Open Source Symposium hosted by Seneca College’s Centre for Development of Open [login to view URL] conference is a venue for the discussion of open source issues and the development of open source technologies and [login to view URL] schedule of the conference in 2015 has spanned over 2 days where multiple sessions run in different rooms each day. During a session, a list of presentations take place in order. a. Design the relational database model to support the conference schedule and bring the tables to 3NF. You can start with a table structure that contains all attributes you can derive from the conference schedule. Include session_number and presentation_order among the attributes of the table. b. Draw the Crow’s Foot ERD. c. Imagine we need to add the attendees’ information and their registration date in the database. Also we would like to keep track of who attends each presentation. Modify your ERD to include the previously mentioned requirements.