Scribd
Upload
183 views2 pages

Database Normalization to 3NF Guide

1. The document describes normalizing a table to 3NF by creating separate tables for students, advisors, and course registrations. 2. The original table is split into three tables - Student, Advisor, and Registration tables. 3. Code is provided to drop and create these tables in the database named ADVISOR_SCHEDULE, with primary keys and foreign keys defined to link the tables.

Uploaded by

Kiran Zaman
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
183 views2 pages

Database Normalization to 3NF Guide

1. The document describes normalizing a table to 3NF by creating separate tables for students, advisors, and course registrations. 2. The original table is split into three tables - Student, Advisor, and Registration tables. 3. Code is provided to drop and create these tables in the database named ADVISOR_SCHEDULE, with primary keys and foreign keys defined to link the tables.

Uploaded by

Kiran Zaman
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

1.

Create and Normalize the following given table’s up to 3-NF via Code:
2. Name of a database must be ADVISOR_SCHEDULE

First normal form:

Student# AdvID AdvName AdvRoom Class#


123 123A James 555 102-B
123 123A James 555 104-9
124 123B Smith 467 209-0
124 123B Smith 467 102-B

Second normal form:

Student table
Student# AdvID AdvName AdvRoom
123 123A James 555
124 123B Smith 467
Registration table
Student# Class#
123 102-B
123 104-9
124 209-0
124 102-B

Third normal form:

Student table
Student# AdvID
123 123A
124 123B
Registeration table
Student# Class#
123 102-B
123 104-9
124 209-0
124 102-B
Advisor table
AdvID AdvName AdvRoom
123A James 555
123B Smith 467

Coding:

DROP DATABASE IF EXISTS `ADVISOR_SCHEDULE`;


CREATE DATABASE IF NOT EXISTS `ADVISOR_SCHEDULE`;
USE `ADVISOR_SCHEDULE`;

Drop TABLE IF EXISTS `ADVISOR_SCHEDULE`.`Advisor`;


CREATE TABLE IF NOT EXISTS `ADVISOR_SCHEDULE`.`Advisor`
(
`AdvID` varchar(20),
PRIMARY KEY(`AdvID`),
`AdvName` varchar(30),
`AdvRoom` varchar(30)
);

Drop TABLE IF EXISTS `ADVISOR_SCHEDULE`.`student`;


CREATE TABLE IF NOT EXISTS `ADVISOR_SCHEDULE`.`student`
(
`student` varchar(20),
PRIMARY KEY (`student`),
`AdvID` varchar(20),
CONSTRAINT `fk_AdvID` FOREIGN KEY (`AdvID`) REFERENCES
`ADVISOR_SCHEDULE`.`Advisor` (`AdvID`)
);

Drop TABLE IF EXISTS `ADVISOR_SCHEDULE`.`Registeration`;


CREATE TABLE IF NOT EXISTS `ADVISOR_SCHEDULE`.`Registeration`
(
`student` varchar(20),
`class` varchar(20),
PRIMARY KEY(`class`),
CONSTRAINT `fk_student` FOREIGN KEY (`student`) REFERENCES
`ADVISOR_SCHEDULE`.`student` (`student`)
);

You might also like