1
Page
Normalizing Database Design
ASSIGNMENT: 3
IT-244
Database Systems
Faculty of
Computer Science
Technology
and
Information
U N I V E R S I T Y O F G U J R AT
ASSIGNMENT #:03
_______________________________________________________________
_____
You are required to Normalize the database design up to 2rd
Normal Form.
Submission Date: 08-June-2012
NOTE:
1. Assignment should be in hard copy format.
2. Plagiarism will not be tolerated.
3. This is an INDIVIDUAL Assignment.
4. Submission of assignment after the due date, will direct
to exercise marks deduction policy.
____________________________________________________________________
Objective
The objective of this assignment is to make students a hand on experience of Normalization.
To learn and understand basic concepts of Normalization.
Submission Format
1. Assignment can only be submitted in well structured printed format and also in Soft
form
Throughout the normalization process, make certain that you identify the primary key by
underlining the field(s) that constitutes the primary key (when you build new tables, also
italicize any foreign keys that you identify -- this will help you verify that you are completing
the normalization process properly). It is also important that you show and explain your
work and the logic that helps me to understand why you made the changes that you made for
each stage of normalization (see the template uploaded). Make certain that you show the
progression from lower levels of normalization to the higher levels. In other words, do not
jump from an unnormalized table to the highest level of normalization (I want to see the logic
and stages that you progressed through in completing the project).
What to return in: Return in documentation showing your work for each step of the
process. In other words, show each table at each stage of normalization. Make certain that
you label the stage of the process that you are showing. In addition, label each table and
provide a description of the assumptions that you made in bringing the table(s) to the
specified level of normalization. It is critical that you show not only the tables, but also the
description of your assumptions and related logic.
Assignment Questions
Question No: 1
Below is hospital data on staff rotation. Assume that the staff can change wards in oneweek.
The charge nurse is unique from the regular staff. Normalize the table below intoits 3 rd
normal form. Show all your solutions
Staff
No.
Name
Address
TelNo
Possiti
on
Shift
S123
Razia
Late
Aslam
0512233445
052998877
Nurse
S0988
23AXYZ
24A/zzz
Nurse
Late
Ward
No
Ward
Name
Loc
atio
n
Week
Begi
nning
Char
ge
Nurs
e
Charg
e
Nurse
No
Tel.
Ext
Ward1
C a r
d i c
Blo
ck
E
1/1/
1998
Sun
bail
S011
7711
Question No: 2
Normalize the below table up to 3rd normal form.
Student-ID
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
Guidelines / Instructions
Understand the scenario on the basis of given data only.
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
The relation is already in 1st normal form and has composite Primary Key.
Dont convert the relation in to 3rd normal form directly.
Show each step. (First 2nd normal form then 3rd normal form).
Question No: 3
Purchase Order Form
P.O.
No
Date
Item
ID.
2101
1/1/98
01
0034
6/1/99
Item
Name
Supplier
ID.
Supplier
Name
Supplier
Address
Quotation
No.
Payment
Mode
Indent
No.
Quantity
Order
Units
Tea
013
Cash
0210
Kg
Shuttle
003
Sargodha
Road
Railway
road
03
016
Tapal
Co.
Jamal
Irons
08
Credit
1086
Num
0054
Cog
Doz
Guidelines / Instructions
Understand the scenario on the basis of given data only.
Dont convert the relation in to 3rd normal form directly.
Show each step. (First 2nd normal form then 3rd normal form).
Question No: 4
The table listed below is designed to store data representing students in a college environment
The following constraints apply:
A student can have only one major.
Each adviser can advise many students in the advisers major field, but a student
has only one adviser (a faculty member in the students major discipline).
Faculty are assigned to departments, each faculty is assigned to only one
department.
Attribute Name
Sample Value
Meaning
STU_NUM
289343
Unique identifier for a student
STU_NAME
James D. Smith
Obvious
STU_MAJOR
Computer Science
Obvious
DEPT_CODE
CS
Unique identifier for a department
DEPT_NAME
Computer Science
Obvious
DEPT_PHONE
7644
Department Secretarys Extension
COLLEGE_NAME
Arts and Sciences
Administrative Unit for a department
ADV_NAME
Evans Adams
Name of students adviser
ADV_OFFICE
201
Room Number of students adviser
ADV_BUILDING
Noble Hall
Building of students advisers office
ADV_PHONE
7211
Students advisers phone extension
STU_GPA
2.88
Students Grade Point Average
STU_HOURS
96
Credit Hours Completed
STU_CLASS
Junior
Students Classification
Deliverables:
You will be handing in the following:
A set of normalized data table definitions illustrating 3rd normal form.
Also create the resultant tables by using SQL. A file containing SQL schema also
submitted.
A SQL table description is fine, or create the table by using SQL description as
above (denoting primary and foreign keys)
Ensure you note table names, field names, data types, primary/foreign keys, auto
increment fields, etc. as part of your design clearly.
________________________________________________________________
