Download Solution: Click to Download Solution
Solution File Name: Normalization.doc
Unzip Password: prestobear.com
Case Study: A design firm with offices located in a number of different cities offers services. Each office offers a set of services, but not all cities offer all services. Service rates are set depending on the market in each area.
The following is a report that is created by the firm to show the services offered by each office.
1.Given the table above, draw the dependency diagram for the 1NF. Label all functional dependencies.
2.Starting with the dependency diagram drawn for 1NF, create a set of tables with their dependency diagrams that meet 2NF, 3NF/BCNF requirements. Label the functional dependencies and each table at each stage. Each stage must include complete set of tables (only the diagrams with functional dependencies) and must be based on the prior NF.
3.Using the results of question 2 (i.e., the set of 3NF/BCNF tables), draw an ER diagram. Do not include attributes on your diagram.
4.Draw the decomposition diagram representing the normalization process.
Normalization is the process to normalize (split) a large relation (entity/table) into smaller relations (entities/tables)
The Normalization start with a UNF (UnNormalization Form) form. Then, UNF will be processed into 1NF 2NF 3NF BCNF and ERD
1.1NF: First Normalization Form, in this phrase, we will:
-Detect the Primary Key (PK) for the large relation
-Define Dependencies (one/more attributes depend on other one/more attributes)
studentid –> (studentname, dob)
(semesterid, courseid) –> coursefee
-Functional Dependency (FD): this is the dependency of non-key attributes to key attributes
+If non-key attributes depend on the whole key PK(attr1, attr2), this is fully functional dependency
+If non-key attributes depend on partial key attr1 of PK(attr1, attr2), this is partial functional dependency
-Transitive Dependency (TD): this is the dependency of non-key attributes to non-key attributes
-If the relation still have partial functional dependency –> it is NOT in 2NF
-Split partial functional dependency out of big relation to form new relations
-Keep the fully functional dependency in big realation
-If the relation still have transitive functional dependency –> it is NOT in 3NF
-Split transitive dependency out of big relation to form new relations
-At this phrase, we will have:
+Relations as a result of spiting of partial functional dependency
+Relations as a result of spiting of transitive dependency
+Relations of fully functional dependency
5.ERD / Logical ERD (Entity Relationship Diagram)
-End of BCNF, we will have Entities
-We need to define PK for each Entity
-Next, we will make relationship for these entities
-In ERD, we will define the relation 1:M or 1:1 or M:N
6. EERD / Relational ERD
-For this stage, we need to define the PK and FK (Foreign Key) for each entities
-Refine the M:N relation to 1:M or M:1
-Then, in the diagram, we need to state clearly which PK will map to FK to form a relationship