-
1NF
- First Normal Form
- The value stored at the intersection of each row and column must be a scalar value and a table must not contain any repeating columns.
-
2NF
- Second Normal Form
- Every non-key column must depend on the entire primary key.
- First Normal Form
- The value stored at the intersection of each row and column must be a scalar value and a table must not contain any repeating columns.
-
3NF
- Third Normal Form
- Every non-key column must depend only on the primary key.
- Second Normal Form
- Every non-key column must depend on the entire primary key.
- First Normal Form
- The value stored at the intersection of each row and column must be a scalar value and a table must not contain any repeating columns.
-
Benefits of Normalization page 295
-
not 1NF(page 296)
- more than 1 piece of data in a column(repeating values in a column)
- table contains repeating columns
-
not 2NF
- when there are values on one column that are dependent on the values in another column
- example: a table with vendor_name, invoice_number, item_desc
- The invoice_number depends on the vendor_name. There could be many rows with identical pairs of vendor_name and invoice_number
- There should be an vendor_name and invoice_number table.
-
applying 2NF see page 298-299
A table has columns: invoice_id, vendor_name, invoice_number, invoice_seq, item_desc
- vendor_name and invoice_number depend on invoice_id so should be moved to a new table(Invoices).
- Invoices: invoice_number, vendor_name, invoice_id
- Invoice_Line_Item: invoice_id, invoice_seq, item_desc
-
Write a procedure that will create a team
CREATE TABLE team(
TeamId INT AUTO_INCREMENT PRIMARY KEY ,
TeamName varchar(20) NOT NULL,
Points INT DEFAULT 0
);
- DROP PROCEDURE IF EXISTS AddTeam;
- DELIMITER $$
- CREATE PROCEDURE AddTeam(
- IN NameOfTeam VARCHAR(20),
- IN Points INT )
- BEGIN
- DECLARE Temp VARCHAR(20);
- SELECT TeamName INTO Temp
- FROM Team
- WHERE TeamName=NameOfTeam
- LIMIT 1;
- IF Temp IS NULL THEN
- SELECT "Adding New Team";
- INSERT INTO Team (TeamName,
- Points)
- VALUE (NameOfTeam, Points);
- SELECT * From Team;
- ELSE
- SELECT Temp "Already Exists";
- END IF;
- END $$
- DELIMITER ;
CALL AddTeam("Toronto",10);
-
Write a procedure to add a fan(FanName varchar(40) with TeamId(INT)
- DROP PROCEDURE IF EXISTS
- createFanForTeamId;
- DELIMITER $$
- CREATE PROCEDURE createFanForTeamId(
- IN FanName_u VARCHAR(40),
- IN TeamId_u INT )
- BEGIN
- DECLARE Temp VARCHAR(40);
- SELECT FanName INTO Temp
- FROM fan
- WHERE FanName=FanName_u
- LIMIT 1;
- IF Temp IS NULL THEN
- SELECT "Adding New fan";
- INSERT INTO fan (FanName, TeamId)
- VALUE (FanName_u, TeamId_u);
- SELECT * From fan;
- ELSE
- SELECT Temp "Already Exists";
- END IF;
- END $$
-
procedure layout
- DROP PROCEDURE IF EXISTS procName;
- DELIMITER $$
- CREATE PROCEDURE ProcName
- (
- IN FanName_u VARCHAR(40),
- IN TeamId_u INT
- )
- BEGIN
- DECLARE Temp VARCHAR(40);
- SELECT...
- IF... THEN
- ...........
- ELSE
- ...............
- END IF;
- END $$
- DELIMITER ;
-
How to create a new database
- DROP DATABASE IF EXISTS db_Name;
- CREATE DATABASE db_Name;
- USE db_Name;
-
how to create a new table
- DROP TABLE IF EXISTS fan;
- CREATE TABLE fan
- (
- FanId INT PRIMARY KEY
- AUTO_INCREMENT,
- FanName VARCHAR(40),
- TeamId INT,
- FOREIGN KEY (TeamId)
- references team(TeamId)
- );
-
create Tournament table that has composite PK (TournName, GameId)
GameId is a FK
- DROP TABLE IF EXISTS Tournament;
- CREATE TABLE Tournament
- (
- TournamentName VARCHAR(30),
- GameId INT,
- PRIMARY KEY
- (TournamentName,GameId),
- FOREIGN KEY (GameId)
- REFERENCES game(GameId)
- );
-
CREATE TABLE team
(
TeamId INT AUTO_INCREMENT
PRIMARY KEY,
TeamName varchar(20) NOT NULL,
Points INT DEFAULT 0
);
How do you add rows to the team table?
INSERT INTO team VALUES (NULL,'Toronto',24);
- OR
- INSERT INTO items
- (item_id,title,artist,unit_price)
- VALUES
- (1,'Umami In Concert','Umami',17.95),(2,'Race Car Sounds','The Ubernerds',13);
-
Reasons for Data Normalization
- Eliminate/Minimize Data Redundancy
- Preserve of Data Integrity
- –When a record is added, updated, or deleted, the data in the database remains consistent.
- Normalization simplifies database maintenance,
- but can result in slower searchers
-
In practice, data is normalized to ...
3NF.
-
First Normal Form
Goal
Rule
- Goal: Ensure that each individual record stores the values that belong to exactly one distinct item of data.
- Rule: -Store a single value in each column of each record.
- -Eliminate repeated columns or groups of columns.
-
What do 1NF Rules Mean?
- Store a single value in each field of each record.
- Eliminate repeated columns or groups of columns.
- –If there are repeated (groups of) columns, then use additional rows rather than additional columns.
- –Easier to add a row to a table than a column.
- –Adding a column requires redesign of the table.
- -Adding a row does not require redesign.
-
Name|Height|Date|Exercise|
WtRep1|WtRep2|WtRep3
The WtRep columns are repeated and each contains two data values (encoded as a string) so this table is not 1NF.
-
Name|Height|Date|Exercise|
WtRep1|WtRep2|WtRep3
Problems and Limitations
- each record can contain exactly three sets of exercises.
- In order to accommodate more sets, the table would have to be extended to include more WtRep columns – but how many WtRep columns are be needed?
- In order to record fewer exercise sets, the record must be padded with ‘empty’ values such as 0/0 or NULL.
- Some queries may be hard to express – e.g. What is the maximum weight bench-pressed by Chris during the month of June?
-
Name|Height|Date|Exercise|
WtRep1|WtRep2|WtRep3
How can we add more sets of exercises?
Wrong answer: Add more columns.
- Add a Set column
- Name|Height|Date |Exercise |Set*|WtRep
- Fred |5’ 10” |Jun 3|Leg Press| 1 |120|15
- Fred |5’ 10” |Jun 3|Leg Press| 2 |150|10
Table now 1NF.
-
Name|Height|Date |Exercise |Set*|WtRep
Fred |5’ 10” |Jun 3|Leg Press| 1 |120|15
Fred |5’ 10” |Jun 3|Leg Press| 2 |150|10
What are the problems/limitations of 1NF?
- Contains duplicate values (wastes space),
- Is error prone (can lead to inconsistent data if duplicates updated incorrectly).
-
Second Normal Form (2NF)
Purpose:
- Solve problems of 1NF by making sure that non-key fields of a table are dependent on the entire primary key.
- What does this mean?
- A primary key uniquely identifies a record. I.e. if you know the primary key, you can find the record.
- In the case of a composite primary key, some fields of a record may be uniquely determined by knowing only part of the primary key.
- This suggests that the table can be split into two or more separate tables.
-
Name|Height|Date |Exercise |Set*|WtRep
Fred |5’ 10” |Jun 3|Leg Press| 1 |120|15
Fred |5’ 10” |Jun 3|Leg Press| 2 |150|10
Fred |5’ 10” |Jun 3|Leg Press| 3 |120|5
Chris |5’ 8” |Jun 3|Bench Pr| 1 |100|5
Chris |5’ 8” |Jun 3|Bench Pr| 2 | 80 |10
Question: What is the primary key?
Which values depend on the entire primary key?
What does Height depend on?
- What is the primary key?
- Name, Date, Exercise, Set
- Which values depend on the entire primary key?
- Wt, Rep
- What does Height depend on?
- Name (i.e. only part of primary key)
-
Name|Height|Date |Exercise |Set*|WtRep
Fred |5’ 10” |Jun 3|Leg Press| 1 |120|15
Fred |5’ 10” |Jun 3|Leg Press| 2 |150|10
Fred |5’ 10” |Jun 3|Leg Press| 3 |120|5
Chris |5’ 8” |Jun 3|Bench Pr| 1 |100|5
Chris |5’ 8” |Jun 3|Bench Pr| 2 | 80 |10
How do we redesign table to 2NF?
- Name|Height
- Fred |5’ 10”
- Chris |5’ 8”
- Name|Date |Exercise |Set*|WtRep
- Fred |Jun 3|Leg Press| 1 |120|15
- Fred |Jun 3|Leg Press| 2 |150|10
- Fred |Jun 3|Leg Press| 3 |120|5
- Chris |Jun 3|Bench Pr| 1 |100|5
- Chris |Jun 3|Bench Pr| 2 | 80 |10
- This is 2NF – but we should modify tables to use better primary key. I.e. use an INT id rather than name. (Why?)
-
Third Normal Form
Ensure that no non-key column can be derived from another column (or from a combination of other columns).
|
|
