Assignment: Normalize Your Classmate's Database
As a database administrator, you may often encounter old legacy databases created years or possibly decades ago. These will often be poorly normalized and may impact application performance or your ability to manage the data effectively. You may be called upon to "refactor" the database-that is, redesign and upgrade it to better fit with modern requirements but still work with existing applications. In this module's discussion, you will practice evaluating a legacy database and normalize the design with a partner by creating an ER diagram and having your partner normalize it for you.
Step 1
In your initial post, create a new relational database design that differs significantly from any previous designs you created for this class and any examples in the module's resources. Use the notation introduced in pages 88 and 104 of the textbook. The database design should represent a design that is lower than third normal form (i.e., it should be in first or second normal form but no higher). Refer to the week's notes and readings for information on the normal forms. Post your design to the discussion forum (by directly write on your ). Please also describe in the post body the purpose of the database you designed, together with the meaning of each table and the relationships between the tables. Reply to the discussion topic and title your reply appropriately according to the database design content.
**Note that, in order for your classmates to conduct normalization, at least one table in your database design MUST have partial and/or transitive dependencies within.
Step 2
For each of your reply posts, you have two options depending on whether you are: A. determining a normal form OR B. disagree with the normal form determinations and/or normalizations of your peers:
Option A:
Refer to the week's notes and readings for information on how to determine a normal form and how to refactor a database design. Select a classmate's database design and reply with the following:
1. The functional dependences of the database design (refer to the notation on page 112 of Harrington (2009)).
2. Your determination of the normal form of the design (first or second). Justify your answer.
3. Refactor the design so that it is in at least third normal form. Justify your answer and post your revised design. Keep in mind that during normalization, new foreign keys may be introduced to the table(s).
4. The new functional dependences of the refactored database.
5. Specify the relationships between the tables in your revised design.
Do not select a classmate that someone else has already picked so pick early. If you have difficulty picking a partner, contact the instructor.
Option B:
Review your classmates' replies to your or to others' initial posts. If you do not agree with their normal form determinations and/or normalizations, reply to your classmate with your revised database design (in at least third normal form), justify the revisions you made, and together with the revised functional dependencies. Specify the relationships between the tables in your revised design.
It's how you justify the decisions you made that matters here. Plus, there may also be an occasion where one of your peers is simply wrong. It will be up to you to explain why. Did your revised design differ from your classmate's? If so, how? Why? Who is right? Are you both right?
Additionally, for either option, you must also include a discussion on denormalization. In Harrington (2009, pp. 130) it says:
"Because of the processing overhead created when performing joins in this way, some database designers make a conscious decision to leave tables unnormalized."
Come up with a case based on your classmate's initial database design you are responding to, with examples, to illustrate 2NF might be a preferred normal form in the design.
Practice makes perfect! In this assignment you will practice normalizing a table. You can choose to either start on this assignment and then work on the discussion , or work on the discussion first and then this activity. It is recommended that you finish both this assignment and discussion before moving to the M4A2 assignment, Normalize Your Database.
Given the relation below (note that there is a composite primary key in this relation):
(PrescriptionNumber, PatientID, Date, Drug, Dosage, PrescriberID, PrescriberName, PatientName)
Answer the following questions:
1. List the functional dependencies for the relation (refer to page 112 of Harrington (2009) for the notation for functional dependencies).
2. Depending on your assumption for the relation (for example, assume that the value of PrescriberID can uniquely determine the value of PrescriberName), your answer might be different from your classmates' . Then, write down the assumptions you have for the functional dependencies you identify.
3. Is this relation in at least 3rd normal form? Explain your reasoning.
4. If your answer to the 2nd question is ‘No,' normalize this relation to 3rd normal form (or above). Write down the final relation(s) after normalization. Keep in mind that during normalization, new foreign keys may be introduced to the relation(s).
5. Specify the relationships between the normalized tables and highlight the foreign keys.
6. List the functional dependencies for the normalized relation(s).