Cn 34 Policy Number example essay topic

Assignment One: Normalising data Normalisation is a bottom-up approach to database design that begins by examining the relationships between the attributes that in turn make up the database. For this example of normalising data we are going to use data from AllRight Accounting which is a firm that stores data about their customers various policies with other companies. The forms that we are extracting the data from are standard forms used by AlRight Accounting to receive the necessary information on their customers. Here the user fills in a form with details such as Personnel Details, Policy Details, and Company Details.

Aaron's Accounting Client Number: CN 34 Policy Number: PN 12 Full Name: Karen Hatfield Policy Description: Covers holder for full Dental treatment Monthly Debt: lb 50 Company Number: CN 100 Policy Start: 1- Sep - 99 Company Name: Dave's Dental Policy Expire: 1- Sep - 00 Source of data: Standard AllRight Accounting customer information form Before we move to First Normal Form (1 NF) we show how the data looks from the outset. This is called Unnormalised form (UNF). Unnormalised Form (UNF) A table containing one or more repeating groups customer Policy (clientno, cName, policyNo, pDesc, pStart, pExpire, mDebt, companyNo, cName) Table Format: Client Rental clientNo cName policyNo pDesc pStart pExpire mDebt companyNo cName CN 34 Karen Hatfield PN 12 PN 15 Covers holder for full dental treatmentCovers holder for full home insurance 1 Sep 991 Aug 99 1 Sep 001 Aug 00 lb 50 lb 60 CN 100 CN 110 Dave's DentalHomes Cover CN 44 Simon Hauss PN 12 PN 20 PN 15 Covers holder for full dental treatmentCovers holder for full car insuranceCovers holder for full home insurance 12 Oct 012 Dec 0112 Jan 01 12 Oct 022 Dec 0212 Jan 02 lb 50 lb 75 lb 60 CN 100 CN 115 CN 110 Dave's DentalCareful CarsHomes Cover First Normal Form (1 NF) A relation in which the intersection of each row and column contains one and only one value. The first step of normalising the data here is to remove any repeating groups, by looking at the table in the Unnormalised form we can see that the repeating data is as follows... Repeating Group = (policyNo, pDesc, pStart, pExpire, debt, companyNo, cName) So to put the data in first normal form we remove the repeating group (Policy Details) by placing the repeating data along with a copy of the original key attribute (clientNo) in a separate relation. We then need to identify a primary key for the new relation (policyNo).

Client (clientNo, cName) PolicyDetailsCompany (clientNo, policyNo, pDesc, pStart, pExpire, debt, companyNo, cName) Table Format: Client & PolicyDetailsCompany Client clientNo cName CN 34 CN 44 Karen HatfieldSimon Hauss PolicyDetailsCompany clientNo policyNo pDesc pStart pExpire debt companyNo cName CN 34 CN 34 PN 12 PN 15 Covers holder for full dental treatmentCovers holder for full home insurance 1 Sep 991 Aug 99 1 Sep 001 Aug 00 lb 50 lb 60 CN 100 CN 110 Dave's DentalHomes Cover CN 44 CN 44 CN 44 PN 12 PN 20 PN 15 Covers holder for full dental treatmentCovers holder for full car insuranceCovers holder for full home insurance 12 Oct 012 Dec 0112 Jan 01 12 Oct 022 Dec 0212 Jan 02 lb 50 lb 75 lb 60 CN 100 CN 115 CN 110 Dave's DentalCareful CarsHomes Cover Second Normal Form (2 ND) A relation that is in first normal form and every non-primary-key attribute is fully functionally dependant on the primary key This normal form applies to relations with composite keys (relations with a primary key composed of two or more attributes). If the relation had only a single attribute primary key it would already be in second normal form. To reach 2 NF on our data we need to create new relations so that the non-primary key attributes are removed along with a copy of the part of the primary key that they are full functionally dependant. Client (clientNo, cName) Policy (clientNo, policyNo, pStart, pExpire, ) DetailsCompany (policyNo, pDesc, debt, companyNo, cName) Table Format: Client, Policy & DetailsCompany Client clientNo cName CN 34 CN 44 Karen HatfieldSimon Hauss Policy clientNo policyNo pStart pExpire CN 34 CN 34 PN 12 PN 15 1 Sep 991 Aug 99 1 Sep 001 Aug 00 CN 44 CN 44 CN 44 PN 12 PN 20 PN 15 12 Oct 012 Dec 0112 Jan 01 12 Oct 022 Dec 0212 Jan 02 DetailsCompany policyNo pDesc debt companyNo cName PN 12 PN 15 PN 20 Covers holder for full dental treatmentCovers holder for full home insuranceCovers holder for full home insurance lb 50 lb 60 lb 75 CN 100 CN 110 CN 115 Dave's DentalHomes CoverCareful Cars Third Normal Form (3 NF) 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 The normalisation from 2 NF to 3 NF involves the removal of any transitive dependencies. If a transitive dependency exists we should remove the transitive dependent attribute from the relation by placing the attribute in a new relation along with a copy of the determinant. In our data all the non-primary key attributes in DetailsCompany are functionally dependent on the primary key, with the exception of cName, as this is also dependent on companyNo.

This is an example of a transitive dependency as the attribute cName is dependent on one or more non-primary key attributes i.e. companyNo. So to remove this transitive dependency we create two new relations called Details and Company... Client (clientNo, cName) Policy (clientNo, policyNo, pStart, pExpire) Details (policyNo, pDesc, debt, companyNo) Company (companyNo, cName) Table Format: Client, Policy, Details & Company Client Company companyNo cName CN 100 CN 110 CN 115 Dave's DentalHomes CoverCareful Cars clientNo cName CN 34 CN 44 Karen HatfieldSimon Hauss Policy clientNo policyNo pStart pExpire CN 34 CN 34 PN 12 PN 15 1 Sep 991 Aug 99 1 Sep 001 Aug 00 CN 44 CN 44 CN 44 PN 12 PN 20 PN 15 12 Oct 012 Dec 0112 Jan 01 12 Oct 022 Dec 0212 Jan 02 Details policyNo pDesc debt companyNo PN 12 PN 15 PN 20 Covers holder for full dental treatmentCovers holder for full home insuranceCovers holder for full home insurance lb 50 lb 60 lb 75 CN 100 CN 110 CN 115.