Database Design Project
Relational Normalization Design ComponentScenario
The CIO of DU Data Services, Inc. (CDS) has asked your team to put aside your work on the Doctors R Us (DRU) project to solve an urgent problem presented by Joan Megabucks from the Megabucks Conference Center (MCC). The initial data is posted in Class Content along with all the normalization materials. The database schema for MCC needs to be re-engineered to be a normalized relational logical schema. The database designer who created the original design has been terminated (fired) because the developers and DBA staff refused to use the design. Unless the database is redesigned, the new conference center renovation will not be completed in time for a major conference.Your task is to use design by synthesis (bottom-up design) and produce a logical design that is normalized to Third Normal Form (3NF). To assist you in your redesign effort, Joan has provided a brief description of the company and some sample data that was used in the presentation of the rejected design.The report for the new MCC database design must include the following items. Remember that this problem is a simplification that is not intended to be an accurate representation of the real-world. Use only the attribute/column names provided in the legacy database design. DO NOT ADD ANY NEW ATTRIBUTES TO THE DESIGN.
1. Assumptions. List the assumptions you make about the Megabucks Company and its conference center that are not included in the description documents.
2. Functional Dependencies (FDs)
a. Identify the functional dependencies in the original database design.
b. You may use the graphical format presented in slides provided on Normalization
3. First Normal Form (1NF). Since it is obvious that the sample data obeys 1NF, identify the primary key of the 1NF design and explain why it is the primary key based upon the FDs and assumptions you made.
4. Second Normal Form (2NF) and Third Normal Form (3NF).
a. Decompose the 1NF design into a 2NF plus 3NF design.
b. Assign an appropriate name to each table of the decomposition.
c. Identify the FDs for each table of the decomposition.
d. Identify the primary and foreign keys in the new design. Underline the primary key attributes and identify foreign key attributes with the *. You may also include color coding to help identify the primary and foreign keys.
e. You may complete the design in two steps with 2NF first and then 3NF. Alternatively you may complete the decomposition in a single step.
5. Verification. Explain why your design satisfies the definition of 2NF and 3NF. Note: for this exercise, we are using the definitions based upon the primary key.Submit the results of the redesign as a Word document containing all parts listed above.