24 Write out all the dependencies of 2.3. 2.5 Convert the relation in 23 into 2" Normal Form. Write your answer as relation. 2.6 Convert the relation in 2.5 into 3 Normal Form. Write your answer as a relation. 2.7 What is Normalization? 2.8 Explain Boyce-Codd Normal Form.
"2.1,2.2,2.3, and 2.4 are answered"
The given problem is related to the normalization process of a
The normalization is a process to break the table into smaller tables to that:
a) There is no redundancy
b) Table is free from insertion, update and delete anomaly.
c) Data in the tables can be handled easily
**As per Bartleby guidelines, as the required number of sub-parts to be answered is not mentioned and it is needed to solve first 3 sub-parts. Kindly find the solution to half assignment(first 4 sub-parts).
The given table is in unnormalized form i.e. it is not even in the first normal form of the database normalization.
Explanation:
a) For a single Invoice number, there are many products added to the table.
b) There is no direct primary key that can be used to store the data uniquely.
c) The given table has blank values for Invoice and Client details as previous invoice data is segregated to next records.
The normalization process will decompose the table into smaller units to make it normalized based on various conditions.
The process to normalize the data from unnormalized form to 3NF includes below steps:
Step 1: Normalization to 1NF
To convert to 1NF, below conditions are to be satisfied:
a) The table should have a primary key which is a non-null and unique attribute of the table.
b) Primary key can be composite key also i.e. composed of more than one attribute.
c) There should be no cell in the table with multiple values.
d) The repeating groups should be removed i.e. for the same value of an attribute (attributes), there should be no other attribute(s) whose value is changing.
Step 2: Normalization to 1NF
Below are the conditions to be satisfied to make a table in 2NF:
a) Decompose to the table to 1NF.
b) Decompose the table if there is a non key attribute that is not dependent on the key attribute.
c) Decompose the table if a table has a composite key and a nonkey attribute in the table depends on the subset of the key i.e. depends on one attribute of composite ket instead of a complete key.
Step 3: Normalization to 1NF
Below are the steps to be followed to make a table in 3NF:
a) The table should be in 2NF, if it is not in 2NF then apply decomposition so that tables are in 2NF.
b) There should be NO non key attribute that depends on another non key attribute which is called a transitive dependency. In such case, the table is needed to be decomposed to remove this dependency.
The given table can be converted to 1st Normal form with all the conditions specified for Step 1 in 2.2 are satisfied:
1) Consider that the table has unique invoiceNo for each invoice.
2) There are many ProductNo per single Invoice NO
3) If we make InvoiceNo, ProductNo as composite key, below repeatig group will be intorudced:
Invoice-No, Client-No , Client-name {many records for same left side attributes } -> Product-No, Product-Name, Product-Price , Quantity.
Thus same table cannot be considered to be in 1NF.
The solution is to decompose the table in below forms:
INVOICE(Invoice-No, Client-No, Client-Name)
Invoice_Products(Invoice-No, Product-No, Product-Name, Product-Price , Quantity): Invoice-No is foreign key from INVOICE.
The final 1NF tables in required format are as below:
- INVOICE[Invoice-No, Client-No, Client-Name]
- Invoice_Products[Invoice-No, Product-No, Product-Name, Product-Price , Quantity]
There are below types of dependencies to be considered in database normalization:
- Full Functional Dependency: When the non key attribute depends on COMPLETE key attribute, it is called full functional dependency.
- Transitive Dependency: When anon key attribute determines another non-key attribute, it is called transitive dependency
- Partial Dependency: When table has a composite key and subset of the key determines the non key attribute, it is called partial dependency.
The identified tables as below functional dependencies:
- INVOICE[Invoice-No, Client-No, Client-Name]
Full Functional Dependency: Invoice-No -> Client-No, Client-Name
Transitive dependency: Client-No -> Client-Name
2. Invoice_Products[Invoice-No, Product-No, Product-Name, Product-Price , Quantity]
Full Functional Dependency: Invoice-No, Product-No -> Quantity
Partial dependency: Product-No -> Product-Name, Product-Price
The given table is concluded to be in Unnormalized form.
The table is decomposed to 1NF and then the dependencies of the 1NF database tables is mentioned as a solution of 2.4 part.
Step by step
Solved in 3 steps
