CS614 Data Warehousing Assignment No 01 spring 2011

[13mera7]

Student Id: Mc090406871
1N Form:
 Hospital Table
 Patient No
 Patient First Name
 Patient Last Name
 Patient Address
 Sex
 Tel No
 DOB
 Marital Statu
 Patient Address
 Sex
 Tel No
 DOB
 Marital Status
 Date Reg
 Next of Kin Name
 Relationship
 Next of Kin Address
 Next of Kin Tel No
 Local's Doctor's Name
 Clinic No
 Doctor's Address
 Doctor's Tel No





2N Form:
We decompose the OPD table into three tables that is Patient, Kin, Doctor as below:
To transform the table OPD into 2NF we move the columns Patient No, Patient First Name, Patient Last Name, Patient Address, Sex, Tel No, DOB, Marital Status, Date Reg to a
new table called Patient. The column Patient No becomes the primary key of this
new table.
To transform the table OPD into 2NF we move the columns Next of Kin Name, Relationship, Next of Kin Address, Next of Kin Tel No to a new table called Kin. The column Patient No becomes the primary key of this new table.
To transform the table OPD into 2NF we move the columns Local's Doctor's Name, Clinic No, Doctor's Address, Doctor's Tel No to a new table called Doctor. The column Patient No becomes the primary key of this new table.

Patient Table
 Patient No
 Patient First Name
 Patient Last Name
 Patient Address
 Sex
 Tel No
 DOB
 Marital Status
 Date Reg
 Kin Table
 Patient No
 Next of Kin Name
 Relationship
 Next of Kin Address
 Next of Kin Tel No

Doctor Table

 Patient No
 Local's Doctor's Name
 Clinic No
 Doctor's Address
 Doctor's Tel No


3N Form:
The above tables are in 3NF, because there is no transitive dependency. For a relational table to be in third normal form (3NF) all columns must be dependent only upon the primary key. More formally, a relational table is in 3NF if it is already in 2NF and every non-key column is non transitively dependent upon its primary key

Q No2:
The classical system development life cycle (SDLC) does not work in the world of the DSS analyst. The SDLC assumes that requirements are known at the start of the design (or at least can be discovered). However, in the world of the DSS analyst, requirements are usually the last thing to be discovered in the DSS development life cycle


Shani khan
sirf.13mera7@yahoo.com
03342599630
MCS(Islmia Universty Bahwal Pur)
MBA(Virtual Universty )