CS614 Data Warehousing Assignment No 01 spring 2011

[Vuhelper]

Assignment No. 01
SEMESTER SPRING 2011
CS614- Data Warehousing
Total Marks: 20

Due Date: 20/04/2011
Instructions
Please read the following instructions carefully before submitting assignment:
It should be clear that your assignment will not get any credit if:
o The assignment is submitted after due date.
o The submitted assignment does not open or file corrupt.
o The assignment is copied.
o There is not mentioned the student Id in the assignment File or
name of file is other than student ID.

GOOD LUCK

Marks: 20

Question 1: [10 marks]
You are given the following data.

Patient No, Patient First Name, Patient Last Name, 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.

1. Normalize the given data in to 1st then 2nd and 3rd normal form. Give justification of conversion in to 1st then 2nd and 3rd normal form and the anomalies because of which you transformed it in to another normal form.

Question 2: [10 marks]

It is believed that Classical SDLC is not suitable for Data warehouse environment. In your point of view why it is not suitable. Secondly what changes you suggest to be made in Classical SDLC to make it suitable for data warehouse. Give reasons to support your ideas?



Note: Try to write answer to the point and in your own words.


Uploading Instructions:

Upload your assignment as a word file. It should have name [YourRollNo.doc]

Deadline
Your assignment must be uploaded/submitted at or before Wednesday April 20, 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 )