Reference no: EM132400046

DBS201 - Introduction to Database Design and SQL - Seneca College

Normalization
(UNF,1NF)
Objective:
• Students create the Un-normalized Form (UNF) relation from a user view.
• Students identify a Multi-valued Dependency(a.k.a. Repeating Group )
• Students create the First Normal Form (1NF) relation from the UNF.

Instructions:

Step 1: Create UNF Relation from a User View. The goal here is to create a single relation for the data found in the user view. The method used is:
- Examine the user view
- Identify all attributes
- Describe the user view using DBDL

Here is an example of a user view. This reports lists the customers of the Premiere Corporation.

A. Examine the user view: As you examine this report, you can see that it contains a title, a line of column headings and the body of the report. Each line in the body of the report contains data about a particular customer. For example we can see that in the first line of the body of the report, there is data about Customer 124. This customer's name is Sally Adams and she lives at 482 Oak in Lansing, Michigan (MI). The Sales Representative who calls on Sally Adams is Sales Rep Number 03.
B. Identify all attributes: The attributes (or characteristics) of a User View can often be found by simply looking at the column headings. In our Customer Report we see that we have the following attributes: Customer Number, Customer Name, Street, City, State, Zip Code and Sales Representative Number.
C. Describe the relation using DBDL: Database Design Language (DBDL) is a standardized way of describing relations of a relational database. You describe a relation by:
a. Choose an appropriate name for the relation. We chose to name our relation CUSTOMER because each line in the report describes customer data.
b. List the attributes you found in the user view inside square brackets, giving each attribute a suitable attribute name. Note: calculated fields or derived fields should not be included in the DBDL
c. Determine which attribute would be suitable as a primary key and underline that attribute.
The DBDL for the relation resulting from our Customer user view would look as follows:

CUSTOMER [ CustNo, CustName, CustStreet, CustCity, CustSt, CustZip, CustRep ]

Now you try it. Examine the following report:

What type of data does each line in the report represent?

What attributes can you identify from the user view?

What would be a suitable name for the UNF relation?

Which attribute would be suitable as a primary key?

Describe the UNF relation using DBDL:

Step 2: Recognize Multi-valued Dependencies.

For example, looking at the following User View, we see that for each Customer number, it is possible to have multiple values for the order number and order date attributes. Therefore the order number and date are a multi-valued dependency.

Identify multi-valued dependencies in DBDL by placing brackets around them. For example the DBDL for this User View would look like this:

CUSTOMER [ CustNo, CustName, ( OrderNum, OrderDate ) ]

Notice the brackets around the OrderNum and Orderdate attributes. This quickly and easily identifies a multi-valued dependency to someone who is reading the DBDL.

Common Mistake: A common mistake is to incorrectly identify repeating data as a multi-valued dependency. For example, the previous report could also have been printed in the following way:

Step 3: Create 1NF relations from UNF.

Therefore, the process of taking a relation from UNF to 1NF, involves resolving the multi-valued dependencies.

Method:
- Choose a primary key for the multi-valued dependancy.
- Identify the primary key of the multi-valued dependancy by underlining it or writing (PK) .
- Rewrite the DBDL by removing the paranthesis and concatenating the original primary key with the primary key of the multi-valued dependency.
- Rewrite the DBDL with the two-part Primary Key and include all the non-key attributes.

Attachment:- Normalization.rar