Re: CS614 Assignment 1 Solution and Discussion
Assignment No. 1
Semester: Spring 2020
CS614 – Data Warehousing
Total Marks: 20
June 01, 2020
After completing this assignment, the students will be able to:
• Identify Database entities from a given scenario
• Understanding and designing system’s constraints from given scenario
• Understand the database table structure
• Normalize a database table up to 2nd Normal Form (2NF)
Please read the following instructions carefully before submitting assignment:
It should be clear that your assignment will not get any credit if:
o Assignment is submitted after due date.
o Submitted assignment does not open or file is corrupt.
o Assignment is copied (From internet/ to from students).
o Assignment is submitted other than word format (.doc, .docx).
XYZ polyclinic is a well-reputed clinic providing good medical facilities in a posh area of Lahore. They also have a facility to admit a patient if the treatment requires. XYZ polyclinic was working manually for the last 10 years. You are hired by XYZ polyclinic management to design a database by reading the system’s requirements. Management of XYZ polyclinic decides to improve their system that would lead to convert the Database system to Data Warehouse for XYZ polyclinic in the future.
The organization is interested in storing name, patient’s CNIC, address, city, zip, province, country, phone number, referred by, admission date, and discharge date for storing patient’s information in the database. The referred by (would contain doctor’s id), admission date and discharge date attributes are only used in case of patient’s admission in a specific room. From the doctor’s point of view, they are interested to store doctor name, CNIC of doctor, address, city, zip, province, country, doctor’s phone number, area of specialization. We must store the treatment information that is suggested by the doctor for a specific patient which includes prescribed medicines. Medicine details must contain medicine id, name, dosage, and potency as attributes along with reference of treatment that is suggested by a doctor. If a patient would require getting admit in polyclinic then the room type (executive/common), phone extension and charges per day are going to be stored in the system as room attributes.
Some additional constraints about the system are as under;
- One patient is referred to one/more doctor at a time.
- Multiple patients would be examined by the doctor in a day
- One patient may have multiple visits to a single doctor
- Room capacity/facilities for attendants would not be handled at this time
- One patient may use many medicines as suggested by doctor
- Every time visit to a doctor may result in a change of medicine or admitted to the polyclinic
TASK to Perform and Submit:
Identify relevant entities, primary keys, foreign keys, proper attributes and relations as per 2NF for the above scenario and provide database/schema/table-structure in MS-Word format that is Normalized up to 2nd Normal Form.
- There’s no need to implement the solution using any DBMS
- Attribute Names should be clearly mentioned
Your assignment must be uploaded on VULMS on or before June 01, 2020. While June 02, 2020 will be a bonus day for assignment submission. After the bonus day, no assignment would be entertained via email.