Database normalization Assignment Help
Looking for Database Normalization Assignment Help online? We have the best database management experts to solve all of your queries related to normalization in the database with example tables. Chat with DBMS expert to get bet Database normalization Assignment Help at affordable price online, plagiarism-free well-researched solutions for Normalization related queries.
What is Normalization?
normalization will be a component of the design phase for databases. However, distinguishing the normalization method from the ER modeling phase is complicated, meaning that the two methods can be used together. Using an object relationship diagram (ERD) to include a broad image of the data needs and activities of an enterprise, or a macro view. It is generated by way of an iterative method involving the recognition of related persons, their characteristics and connections. The normalization method relies on the attributes of individual organizations and provides a micro image of individuals within the ERD. It is a method used in a database in which we split tables in such a way that it reduces the data redundancy and more over it everything is stored in a better manner.it uses the primary key and foreign key as the major relationships to link the table together for better data flow. There are three types of irregularities that arise by not standardizing the database. Those are-paradox for insertion, updating and deletion. Normalization of the database is little more than arranging the rows and columns of the rows in such a manner as to reduce the system duplication and consistency and increase system honesty.
Database standardization is used for the following purpose:
1. to remove obsolete or unnecessary data.
2. To minimize system redundancy.
3. Verify the interaction between tables and system in tables.
4. Verify that data connections and data are processed logically
Facts about normalization: as per our database normalization expert follwoing are the facts
- The words normalization and normal form refer to the structure of a database.
- Normalization was developed by IBM researcher E.F. Codd in the 1970s.
- Normalization increases clarity in organizing data in Databases.
Stages of the Normalization as per Database normalization Assignment Help expert:
1NF-
If a relation contains composite or multi-valued attribute, it violates first normal form, or a relation is in first normal form if it does not contain any composite or multi-valued attribute. A relation is in first normal form if every attribute in that relation is singled valued attribute.
A table is in 1 NF if:
1. There are only Single Valued Attributes.
2. Attribute Domain does not change.
3. There is a Unique name for every Attribute/Column.
4. The order in which data is stored, does not matter.
2NF-
The normalization of 1NF relations to 2NF involves the removal of partial dependencies. If a partial dependency exists, we remove the partially dependent attribute(s) from the relation by placing them in a new relation along with a copy of their determinant.
3NF-
A relation that is in First and Second Normal Form and in which no non-primary-key attribute is transitively dependent on the primary key, then it is in Third Normal Form (3NF).
Order now to avail Database Normalization Assignment Help now at a discounted price.
Normalization in the database with example tables
Question submitted by the user related to Database normalization Assignment Help:
Normalize the following data to 3NF. Can someone tell me or set up an example of at least the 1NF and show the steps? Any FKs and PKs need to be used accordingly. A picture of the normal form structure an ERD or screenshots would be helpful in MS word
PersonID
Person Name
Person Address
Person Phone Numbers
Person SSN
Marital Status
Person DOB
Spouse Name
Spouse Address
Spouse Phone Numbers
Spouse SSN
Spouse DOB
Marital Status
Child1 Name
Child1 Address
Child1 Phone Numbers
Child1 SSN
Child1 DOB
Marital Status
Child2 Name
Child2 Address
Child2 Phone Numbers
Child2 SSN
Child2 DOB
Marital Status
Child3 Name
Child3 Address
Child3 Phone Numbers
Child3 SSN
Child3 DOB
Marital Status
Vehicle Make
Vehicle Model
Vehicle Year
Vehicle Color
Vehicle Mileage
Vehicle Body Type
Vehicle Repair Date
Vehicle Repair Description
Vehicle Repair Cost
Vehicle Repair Shop Name
Vehicle Repair Shop Address
Repair Shop Phone Numbers
Repair Shop Owner Name
Repair Shop Owner Address
Repair Shop Owner Phone Numbers
Normalization in the database with example tables solution
Step by step answer by Database normalization Assignment Help expert
Step 1:
Find the minimal cover of FDs, which contains
PersonID --> PersonName
PersonID --> PersonDOB
PersonSSN --> PersonAddress
PersonSSN --> PersonPhoneNumbers
SpouseSSN --> SpouseName
SpouseSSN --> SpouseAddress
SpouseSSN --> SpouseDOB
PersonID,SpouseSSN --> MaritalStatus
Child1SSN,Child1DOB --> Child1Name
Child1SSN,Child1DOB --> Child1Address
Child1SSN,Child1DOB --> MaritalStatus
Child2SSN,Child2DOB --> Child2Name
Child2SSN,Child2DOB --> Child2Address
Child2SSN,Child2DOB --> MaritalStatus
Child3SSN,Child3DOB --> Child3Name
Child3SSN,Child3DOB --> Child3Address
Child3SSN,Child3DOB --> MaritalStatus
VehicleMake --> VehicleModel
PersonID,Child1SSN,Child2SSN,Child3SSN,SpouseSSN --> Child1Name
PersonID,Child1SSN,Child2SSN,Child3SSN,SpouseSSN --> Child2Name
PersonID,Child1SSN,Child2SSN,Child3SSN,SpouseSSN --> Child3Name
Step 2.
Find all candidate keys. The set of candidates keys is { `(PersonID, PersonSSN, SpousePhoneNumbers, SpouseSSN, Child1PhoneNumbers, Child1SSN, Child1DOB, Child2PhoneNumbers, Child2SSN, Child2DOB, Child3PhoneNumbers, Child3SSN, Child3DOB,VehicleMake, VehicleYear, VehicleColor, VehicleMileage, VehicleBodyType, VehicleRepairDate, VehicleRepairDescription, VehicleRepairCost, VehicleRepairShopName, VehicleRepairShopAddress, RepairShopPhoneNumbers, RepairShopOwnerName,RepairShopOwnerAddress,RepairShopOwnerPhoneNumbers),` }.
The set of key attributes is: { `PersonID, PersonSSN, SpousePhoneNumbers, SpouseSSN, Child1PhoneNumbers, Child1SSN, Child1DOB, Child2PhoneNumbers, Child2SSN, Child2DOB, Child3PhoneNumbers, Child3SSN, Child3DOB, VehicleMake, VehicleYear, VehicleColor, VehicleMileage, VehicleBodyType,VehicleRepairDate,VehicleRepairDescription,VehicleRepairCost,VehicleRepairShopName, VehicleRepairShopAddress, RepairShopPhoneNumbers, RepairShopOwnerName,RepairShopOwnerAddress,RepairShopOwnerPhoneNumbers` }.
Step 3: Merge FDs with same LHS and whose RHS are non-key attributes, we get the set F1 which contains:
PersonID --> PersonDOB,PersonName
PersonSSN --> PersonPhoneNumbers,PersonAddress
SpouseSSN --> SpouseDOB,SpouseAddress,SpouseName
PersonID,SpouseSSN --> MaritalStatus
Child1SSN,Child1DOB --> MaritalStatus,Child1Address,Child1Name
Child2SSN,Child2DOB --> MaritalStatus,Child2Address,Child2Name
Child3SSN,Child3DOB --> MaritalStatus,Child3Address,Child3Name
VehicleMake --> VehicleModel
PersonID,Child1SSN,Child2SSN,Child3SSN,SpouseSSN --> Child3Name,Child2Name,Child1Name
Step 4: Check each FD in the set F1 for violation of 3NF, and split table accordingly.
Checking FD PersonID --> PersonDOB,PersonName
The FD violates 3NF as its LHS is not a superkey (and RHS is a set of non-key attributes).
The following 3NF table is obtained:
PersonID,PersonDOB,PersonName
with FDs
PersonID --> PersonDOB,PersonName
Checking FD PersonSSN --> PersonPhoneNumbers,PersonAddress
The FD violates 3NF as its LHS is not a superkey (and RHS is a set of non-key attributes).
The following 3NF table is obtained:
PersonSSN,PersonPhoneNumbers,PersonAddress
with FDs
PersonSSN --> PersonPhoneNumbers,PersonAddress
Checking FD SpouseSSN --> SpouseDOB,SpouseAddress,SpouseName
The FD violates 3NF as its LHS is not a superkey (and RHS is a set of non-key attributes).
The following 3NF table is obtained:
SpouseSSN,SpouseDOB,SpouseAddress,SpouseName
with FDs
SpouseSSN --> SpouseDOB,SpouseAddress,SpouseName
Checking FD PersonID,SpouseSSN --> MaritalStatus
The FD violates 3NF as its LHS is not a superkey (and RHS is a set of non-key attributes).
The following 3NF table is obtained:
PersonID,SpouseSSN,MaritalStatus
with FDs
PersonID,SpouseSSN --> MaritalStatus
Checking FD Child1SSN,Child1DOB --> MaritalStatus,Child1Address,Child1Name
The FD violates 3NF as its LHS is not a superkey (and RHS is a set of non-key attributes).
The following 3NF table is obtained:
Child1SSN,Child1DOB,MaritalStatus,Child1Address,Child1Name
with FDs
Child1DOB,Child1SSN --> MaritalStatus,Child1Address,Child1Name
Checking FD Child2SSN,Child2DOB --> MaritalStatus,Child2Address,Child2Name
The FD violates 3NF as its LHS is not a superkey (and RHS is a set of non-key attributes).
The following 3NF table is obtained:
Child2SSN,Child2DOB,MaritalStatus,Child2Address,Child2Name
with FDs
Child2DOB,Child2SSN --> MaritalStatus,Child2Address,Child2Name
Checking FD Child3SSN,Child3DOB --> MaritalStatus,Child3Address,Child3Name
The FD violates 3NF as its LHS is not a superkey (and RHS is a set of non-key attributes).
The following 3NF table is obtained:
Child3SSN,Child3DOB,MaritalStatus,Child3Address,Child3Name
with FDs
Child3DOB,Child3SSN --> MaritalStatus,Child3Address,Child3Name
Checking FD VehicleMake --> VehicleModel
The FD violates 3NF as its LHS is not a superkey (and RHS is a set of non-key attributes).
The following 3NF table is obtained:
VehicleMake,VehicleModel
with FDs
VehicleMake --> VehicleModel
Checking FD PersonID,Child1SSN,Child2SSN,Child3SSN,SpouseSSN --> Child3Name,Child2Name,Child1Name
The FD violates 3NF as its LHS is not a superkey (and RHS is a set of non-key attributes).
The following 3NF table is obtained:
PersonID,Child1SSN,Child2SSN,Child3SSN,SpouseSSN,Child3Name,Child2Name,Child1Name
with FDs
Child1SSN,Child2SSN,Child3SSN,PersonID,SpouseSSN --> Child3Name,Child2Name,Child1Name
Step 5: Finally, add the following table into normalized 3NF table set (obtained by removing RHS attributes of FDs using which we produced a table):
PersonID,PersonSSN,SpousePhoneNumbers,SpouseSSN,Child1PhoneNumbers,Child1SSN,Child1DOB,Child2PhoneNumbers,Child2SSN,Child2DOB,Child3PhoneNumbers,Child3SSN,Child3DOB,VehicleMake, VehicleYear, VehicleColor, VehicleMileage, VehicleBodyType, VehicleRepairDate, VehicleRepairDescription,VehicleRepairCost,VehicleRepairShopName,VehicleRepairShopAddress,RepairShopPhoneNumbers,RepairShopOwnerName,RepairShopOwnerAddress,RepairShopOwnerPhoneNumbers
with FDs.
My Assignment Help having best expert tutors for Database Normalization Assignment Help available 24x7. Th expert tutors of database can resolve all of your doubts related to normalization. So, what are you waiting for, Order now to get solution by getting our Database normalization Assignment Help.
The example of database normalization is the perfect example how our database tutors solve it perfectly. Do not hesitate to ask Database normalization Assignment Help by the best mysql, sql, oracle database expert writers now.
