CS614 Assignment
Tuesday, April 19, 2011 Posted In CS and IT Edit This
Assignment No. 01
SEMESTER SPRING 2011CS614- Data Warehousing
Due Date: 20/04/2011
Question 1: [10 marks]
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?
SEMESTER SPRING 2011CS614- Data Warehousing
Total Marks: 20
Question 1: [10 marks]
You are given the following data.
Patient No, Patient First Name, Patient Last Name, Patient Address, Mad, 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.
Solution:
1N Form:
Hospital Table
Patient No
Patient First Name
Patient Last Name
Patient Address
Mad
Tel No
DOB
Marital Statu
Patient Address
Mad
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, Mad, 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
Mad
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
Hospital Table
Patient No
Patient First Name
Patient Last Name
Patient Address
Mad
Tel No
DOB
Marital Statu
Patient Address
Mad
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, Mad, 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
Mad
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
