Instructions on Phase P5: Normalization
Task information
a) Initial de-normalized relation and functional dependencies
The process starts from a single unified de-normalized relation that includes all attributes from the ER model (as if all data resides in a single table). It is important that there are no duplicate attribute names.
Then you need to determine the initial set of functional dependencies that are valid in the unified relation and need to be valid after the decomposition. It is best to start from a shorter list - the canonical cover of the set of initial functional dependencies.
Once again, you should consider all attributes from the entire model together, and not separately by entities and/or relations. So from the global set of attributes, define a global set of functional dependencies that are valid always and everywhere.
b) Candidate keys and primary key selection
Based on the functional dependencies, you need to formally identify all candidate keys and select the primary key for the de-normalized relation.
Then you need to check in which NF is the de-normalized relation, before starting the process of decomposition.
c) Step-by-step decomposition to highest possible normal form
Starting from the set of functional dependencies and attributes, and without looking at (and ignoring) your previous design (from Phase 1 and 2 of the Project), carry out a step-by-step decomposition procedure to the highest possible normal form.
The decomposition should be done step by step from 1NF, 2NF, 3NF, BCNF, with a brief but formal explanation of the steps taken, that includes info on:
- which specific relation is analyzed in that step
- what are the functional dependencies that apply to that relation, which are the candidate keys and the primary key of that relation
- establish in which normal form is the relation
- which dependencies cause issues with the first subsequent higher normal form in that relation, which one will be first to consider as a starting point for decomposition in two relations
- what relations are obtained after the decomposition
- what dependencies are valid in the new relations
- what are the keys in the new relations
- checking whether the decomposition preserves all functional dependencies from the original relation
- checking whether the decomposition has loss-less join properties
Continue this process in step-wise manner until all relations are in a highest possible normal form while still preserving functional dependencies and preserving loss-less join properties.
d) Final result and discussion
Present the final normalized relational design with appropriate naming of the discovered relations.
Discuss the differences between this relation design and the design from Phase 2 of the Project and on which will be used for the following Project phases.
Restructure your database objects accordingly to the new design, update your Phase 2 Documentation.
Documentation
Create and link a wiki page called Normalization linked to the main project page in the appropriate phase.
Template for the wiki page.
Normalization
De-normalized database form
- Functional dependencies
- Candidate keys and primary key
1NF decomposition
…
2NF decomposition
…
3NF decomposition
…
BCNF if possible
…
Final result and discussion
- Normalized relational model
- Discussion
AI Use
You are free to use an AI to improve your work, provided that you adhere to the following rules:
- you use AI in this phase of the project only to improve your own initial word
- you use AI to create alternate solutions for ideas that you can borrow and use in your own solution
- you document the changes you have made to your own work based on the ideas from the AI tool in the history above
- you document the entire usage of the AI tool in a new wiki page named NormalizationAIUsage linked here, that you structure according to the following template
Normalization AI Usage
Name of AI service/solution that was used
URL:
Type of service/subscription:
Final result
Results in details / description:
Entire AI usage log
- Line-by-line Log of all interactions with the AI (prompt and response)
- …
