CS614 Assignment 1 Solution and Discussion


  • Cyberian's Gold

    Re: CS614 Assignment 1 Solution and Discussion
    Assignment No. 1
    Semester: Spring 2020
    CS614 – Data Warehousing
    Total Marks: 20

    Due Date:
    June 01, 2020

    Objectives:
    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)

    Instructions
    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).

    Assignment

    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.

    Database Requirements:

    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.

    Important Note:

    • There’s no need to implement the solution using any DBMS
    • Attribute Names should be clearly mentioned
      Deadline:
      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.

  • Cyberian's Gold

    CS614 ASSIGNEMNT NO 01 Solution
    411dc679-4c14-45ed-9524-f7df373c44de-image.png

    b4d345a9-d296-4b5b-9ad6-b9523459976c-image.png

    3e85fea8-a27a-4b26-9590-829227619cde-image.png

    21deaaf2-238b-4330-9ebe-07a3eace6d92-image.png

    425d51f3-a1df-44b8-879b-dbb3021df40f-image.png

    52e2773b-a944-404c-9e8f-5d61cd61cc0b-image.png

    b9d3fc5b-95b8-42e2-868f-a650c5667191-image.png

    29a35db4-8f48-4045-a80f-0412997b7af7-image.png

    CS614 assignment Solution spring 2020.xlsx



Quiz 100% Result Quiz 100% Result
| |