Staff No.
Name
Address
TelNo
Position
Shift
Ward No
Ward Name
Location
Week Beginning
Charge Nurse
Charge Nurse No
Tel.Ext
S123 S0988
Razia Aslam
23A-XYZ 24-A/zzz
0512233445 052998877
Nurse Nurse
Late Late Ward 1 cardic Block E 1/1/1998 Sunbail S011 7711
Un-Normalized Record
First Normal Form
Staff No. Name Address TelNo Position Shift Ward No Ward 1 Ward 1 Ward Name Location Week Beginning Charge Nurse Charge Nurse No S011 S011 Tel.Ext
S123 S0988
Razia Aslam
23A-XYZ 24-A/zzz
0512233445 052998877
Nurse Nurse
Late Late
cardic cardic
Block E Block E
1/1/1998 1/1/1998
Sunbail Sunbail
7711 7711
We select the Staff No; Ward No and Charge Nurse No. as primary key. y y y Insertion Anomaly: Insertion Anomaly is present in the 1NF because a new Item cannot be includes it is issued to a Ward. Deletion Anomaly: Deletion Anomaly is also present in the 1NF because if we want to delete any Item we must lose some information. Updation Anomaly: Updation anomaly is also present in the 1NF because if an Item code is changed there are several places it will be changed.
All the three kinds of anomalies are present in the First Normal Form so we will step forward in the Second Normal Form.
Second Normal Form Staff No Name, Address, Telephone No, Position Ward No Ward Name, Location Charge Nurse, Tel Ext Charge Nurse No Staff No; Ward No; Cahrge Nurse No Position, Location, Tel Ext, Week Beginning, Shift
Staff No. S123 S0988
Name
Address
TelNo
Position
Shift
Ward No
Ward1 Ward1
Locatio n Block E Block E
Week Beginning 1/1/1998 1/1/1998
Charge Nurse No
S011 S011
Razia Aslam
23A-XYZ 24-A/zzz
051-2233445 052-998877
Nurse Nurse
Late Late
Ward No
Ward1 Ward1
Ward Name
cardic cardic
Charge Nurse No
S011 S011
Charge Nurse
Sunbail Sunbail
Tel Ext
7711 7711
Now it has also three anomalies so we further move towards 3NF.
Third Normal Form Staff No Name, Address, Telephone No, Position Ward Name, Location Ward No Charge Nurse No Charge Nurse, Tel Ext Staff No; Ward No; Cahrge Nurse No Week Beginning, Shift
Staff No.
Shift
Info Table Week Beginning Ward No
Charge Nurse No
S123 S0988
Late Late
Ward1 Ward1
1/1/1998 1/1/1998
S011 S011
Ward Table Ward No
Ward1 Ward1
Charge Nurse Table Location
Block E Block E
Ward Name
cardic cardic
Charge Nurse No
S011 S011
Charge Nurse
Sunbail Sunbail
Tel Ext
7711 7711
Staff Table Staff No.
S123 S0988 Name Razia Aslam Address 23A-XYZ 24-A/zzz TelNo 051-2233445 052-998877 Position Nurse Nurse
StudentID Vu001 Vu001 Vu002 Vu003 Vu003 Vu003 Vu004 Vu004
Specialization Name SE AI AI SE Networks AI SE AI Faizan Faizan Zafar Ahmad Ahmad Ahmad Fahad Fahad
Specialization Semester Fall2009 Spring2010 Spring2010 Fall2009 Spring2010 Fall2010 Fall2010 Spring2011
Semester Start-Date 05-07-09 05-02-10 05-02-10 05-07-09 05-02-10 05-07-10 05-07-10 05-02-11
Semester End-Date 31-12-09 30-06-10 30-06-10 31-12-09 30-06-10 31-12-10 31-12-10 30-06-11
Specialization Supervisor Prof Majid Prof Saeed Prof Saeed Prof Majid Prof Wajid Prof Saeed Prof Majid Prof Saeed
It is already in 1NF so we move so 2NF.
Second Normal Form
Name Student ID Specialization Specialization Semester Student ID, Specialization Semester Start-Date, Semester End Date, Specialization Supervisor
Student-ID Vu001 Vu001 Vu002 Vu003 Vu003 Vu003 Vu004 Vu004 Specialization SE AI AI SE Networks AI SE AI Semester Start-Date 05-07-09 05-02-10 05-02-10 05-07-09 05-02-10 05-07-10 05-07-10 05-02-11 Semester End-Date 31-12-09 30-06-10 30-06-10 31-12-09 30-06-10 31-12-10 31-12-10 30-06-11 Specialization Supervisor Prof Majid Prof Saeed Prof Saeed Prof Majid Prof Wajid Prof Saeed Prof Majid Prof Saeed
Specialization Table Specialization
SE AI AI SE Networks AI SE AI
Student Table Student-ID
Vu001 Vu001 Vu002 Vu003 Vu003 Vu003 Vu004 Vu004
Specialization Semester
Fall2009 Spring2010 Spring2010 Fall2009 Spring2010 Fall2010 Fall2010 Spring2011
Name
Faizan Faizan Zafar Ahmad Ahmad Ahmad Fahad Fahad
It also has the anomalies so we further move to 3NF.
Third Normal Form
Name Student ID Specialization Specialization Semester Semester Start Date Semester End Date Semester Start-Date, Specialization Supervisor Student ID, Specialization
Info Table
Student-ID Vu001 Vu001 Vu002 Vu003 Vu003 Vu003 Vu004 Vu004 Specialization SE AI AI SE Networks AI SE AI Semester Start-Date 05-07-09 05-02-10 05-02-10 05-07-09 05-02-10 05-07-10 05-07-10 05-02-11 Specialization Supervisor Prof Majid Prof Saeed Prof Saeed Prof Majid Prof Wajid Prof Saeed Prof Majid Prof Saeed
Specialization Table Specialization
SE AI AI SE Networks AI SE AI
Student Table Student-ID
Vu001 Vu001 Vu002 Vu003 Vu003 Vu003 Vu004 Vu004
Specialization Semester
Fall2009 Spring2010 Spring2010 Fall2009 Spring2010 Fall2010 Fall2010 Spring2011
Name
Faizan Faizan Zafar Ahmad Ahmad Ahmad Fahad Fahad
Semester Duration Table Semester Start-Date
05-07-09 05-02-10 05-02-10 05-07-09 05-02-10 05-07-10 05-07-10 05-02-11
Semester End-Date
31-12-09 30-06-10 30-06-10 31-12-09 30-06-10 31-12-10 31-12-10 30-06-11
P.O. No
Date
Item ID.
Item Name
Supplie r ID.
Supplier Name
Supplier Address
Quotatio n No.
Payment Mode
Inden t No.
Quant ity Order
Units
2101 0034
1/1/98 6/1/99
01 016 0054
Tea Shuttle Cog
013 003
Tapal Co. Jamal Irons
Sargodha Road Railway road
03 08
Cash Credit
0210 1086
5 6 3
Kg Num Doz
This Table has all three anomalies so remove these anomalies.
First Normal Form
P.O. No
Date
Item ID.
Item Name
Supplie r ID.
Supplier Name
Supplier Address
Quotation No.
Payment Mode
Indent No.
Quant ity Order
Units
2101 0034 0034
1/1/98 6/1/99 6/1/99
01 016 0054
Tea Shuttle Cog
013 003 003
Tapal Co. Jamal Irons
Sargodha Road Railway road Railway road
03 08 08
Cash Credit Credit
0210 1086 1086
5 6 3
Kg Num Doz
We select the Item ID; Supplier I.D and Indent No as primary key. y y y Insertion Anomaly: Insertion Anomaly is present in the 1NF because a new Item cannot be includes it is issued to a Supplier. Deletion Anomaly: Deletion Anomaly is also present in the 1NF because if we want to delete any Item we must lose some information. Updation Anomaly: Updation anomaly is also present in the 1NF because if an Item code is changed there are several places it will be changed.
All the three kinds of anomalies are present in the First Normal Form so we will step forward in the Second Normal Form.
Second Normal Form
Item Name Item ID Supplier ID Supplier Name, Supplier Address P.O. No, Date, Quotation No; Payment Mode, Units Item Id, Supplier Id, Indent No Quantity Order
Purchase Order Table
P.O. No Date Item ID. Supplier ID.
013 003 003
Quotation No.
03 08 08
Payment Mode
Cash Credit Credit
Indent No.
0210 1086 1086
Quanti ty Order
5 6 3
Units
2101 0034 0034
1/1/98 6/1/99 6/1/99
01 016 0054
Kg Num Doz
Item ID.
Item Name
01 016 0054
Tea Shuttle Cog
STU _NU M 289 343
STU_ STU_ NAM MAJ E OR Jame s D. Smit h
DEPT _CO DE
DEPT _NA ME
DEPT _PHO NE
COLLE GE_NA ME
ADV _NA ME
ADV_ OFFI CE
ADV_B ADV_ UILDIN PHO G NE
STU _GP A
STU_ HOU RS 96
STU _CLA SS
Com CS puter Scie nce
Com 7644 puter Scie nce
Arts and Scienc es
Eva ns Ada ms
201
Noble Hall
7211
2.8 8
Juni or
STU_NU M
DEPT_NAM E
DEPT_PHON E
DEPT_COD E
ADV_NAM E
ADV_OFFIC E
ADV_BUILDIN G
ADV_PHON E
289343
Computer Science
7644
CS
Evans Adams
201
Noble Hall
7211
STU_NUM STU_NAME
STU_MAJOR
STU_GPA STU_HOURS
STU_CLASS COLLEGE_NAME
v289343
James D. Smith
Computer Science
2.88
96
Junior
Arts and Sciences
DEPT_NAME
DEPT_PHONE
DEPT_CODE
Computer Science
7644
CS
ADV_NAME
ADV_PHONE
ADV_OFFICE
ADV_BUILDING
Evans Adams
7211
201
Noble Hall
STU_NUM 289343
DEPT_CODE
ADV_NAME
CS
Evans Adams
