Below is a database relation. Apply 1 st, 2nd, and 3rd normalization rules to th

Question

Below is a database relation. Apply 1 st, 2nd, and 3rd normalization rules to the data contained in this relation and determine what level of normalization this relation is in. If it is not in 3rd normal form create new relations necessary to make it adhere to the rules of 3 rd normal form. Assume the new schema will need to store more information about the manufacturer than just the name. Deliverables: ER diagram in 3rd normal form. A script to create the database relations (tables) including all applicable primary and foreign keys and the referential integrity relationships between them. At the end of the script remember to use the describe command for each table you created. A script to insert the data into the tables. At the end of the script remember to use the SELECT * FROM each table you created. Printed output of: o The table creation script(s) o The output of the describe command for each table. o The insertion script(s). o The output of the SELECT * FROM each table.

Car ID Car Make Care Model Car Vin Car Color Cam Mileage 1 Ford Mustang J8379793 Red 98274 2 Acura Integra W1348654 Blue 23541 3 Nissan Altima A837483 White 6432 4 Ford Taurus J1738492 White 82452 5 Nissan Altima J839845 Black 53424 6 Nissan Maxima J4679138 Red 74515 7 Ford Taurus T2948333 Silver 57322 14 Chevrolet Aveo C4568138 Light blue 65321 15 Chevrolet Cobalt C8897652 Dark blue 74526

Explanation / Answer

1NF (First Normal Form) Rules

·         Each table cell should contain single value.

·         Each record needs to be unique.

The above table in 1NF-

1NF Exmple

Car ID

(Primary key)

Car Make

Care Model

Car Vin

Car Color

Cam Mileage

1

Ford

Mustang

J8379793

Red

98274

2

Acura

Integra

W1348654

Blue

23541

3

Nissan

Altima

A837483

White

6432

4

Ford

Taurus

J1738492

White

82452

5

Nissan

Altima

J839845

Black

53424

6

Nissan

Maxima

J4679138

Red

74515

7

Ford

Taurus

T2948333

Silver

57322

A KEY is a value used to uniquely identify a record in a table. A KEY could be a single column or combination of multiple columns.

2NF (Second Normal Form) Rules

·         Rule 1- Be in 1NF

·         Rule 2- Single Column Primary Key

It is clear that we can't move forward to make our simple database in 2nd Normalization form unless we partition the table above.

Foreign Key references primary key of another Table!It helps connect your Tables

·         A foreign key can have a different name from its primary key

Table 1

Foreign KeY

Care Model ID

Car Color

Cam Mileage

1

Red

98274

2

Blue

23541

Table 2

Care Model ID

Care Model

1

Mustang

2

Integra

3

Altima

4

Taurus

A transitive functional dependency is when changing a non-key column , might cause any of the other non-key columns to change

Consider the table 1. Changing the non-key column Full Name , may change Salutation.

3NF (ThirdNormal Form) Rules

·         Rule 1- Be in 2NF

·         Rule 2- Has no transitive functional dependencies

To move our 2NF table into 3NF we again need to need divide our table.

Table 1

Foreign Key

Care Model ID

Car Color

Cam Mileage

1

Red

98274

2

Blue

23541

Table 2

Care Model ID

Care Model

1

Mustang

2

Integra

3

Altima

4

Taurus

Table 3

Mileage_ID

Cam Mileage

1

98274

2

23541

We have again divided our tables and created a new table which stores Mileage.

There are no transitive functional dependencies and hence our table is in 3NF

In Table 3 Mileage ID is primary key and in Table 1 Milelage ID is foreign to primary key in Table 3

Now our little example is in a level that cannot further be decomposed to attain higher forms of normalization. In fact it is already in higher normalization forms. Separate efforts for moving in to next levels of normalizing data are normally needed in complex databases.

Car ID

(Primary key)

Car Make

Care Model

Car Vin

Car Color

Cam Mileage

1

Ford

Mustang

J8379793

Red

98274

2

Acura

Integra

W1348654

Blue

23541

3

Nissan

Altima

A837483

White

6432

4

Ford

Taurus

J1738492

White

82452

5

Nissan

Altima

J839845

Black

53424

6

Nissan

Maxima

J4679138

Red

74515

7

Ford

Taurus

T2948333

Silver

57322

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.