A3-Ting Wei Chien(Willy)

.docx

School

Santa Clara University *

*We aren’t endorsed by this school

Course

443

Subject

Computer Science

Date

Jun 11, 2024

Type

docx

Pages

Uploaded by CaptainMorningDonkey44

CS443 - Assignment 3 Question #1 NAME:TING WEI CHIEN(WILLY) Consider the following Table All information have been placed in one table. Arrows show the functional dependency. You are required to place this table in 3 rd normal form. The arrows in this question indicated the determination of two attributes. For example, the arrow that goes ProductID to ProductDescription indicates that ProductID determines the ProductDescription. This in turn means that ProductId can be considered as primary key for ProductDescription. a) Write the tables Table 1 : Receipt ( Receipt Number , Sales Date) Table 2 : Product ( Product Id , Product Description) Table 3 : Item ( Item Number , Item Description) Table 4 : Sold ( Receipt Number , Product Id , Quantity Sold) Table 5 : Used ( Item Number , Product Id , Quantity Used) b) Place the tables in 3 rd normal form (if necessary) It’s already in 3 NF. c) Create ERD based on the normalized tables

Question #2 Consider the following ERD Where - PatientId: It is the identification number of each patient - PatientName: It is the name of the patient

- Patientddr: It is the address of the patient - AdmitDate: It is the date when the patient is admitted to the hospital - AmounOwing: The amount the patient owes based on his/her sickness after being discharged - RoomNo: it is the room where the patient is kept in the hospital - RoomPhone: The phone number in the patient’s room - HospitalStayDays: Number of days the patient would be in the hospital for treatment. - RoomRate: The rate charged for every day the patient is in the room In the second table: - PhysId: It is the identification number of each physician - PhyName: It is the name of each physician - PhysDept: It is the department id where physician works - DeptSupervisorId: It is the id of the physician who is in change of managing the PhyDept. For example, suppose physician x works in department y. DeptSupervisorId is the id of the physician (not necessarily physician x) who in managing department y. - TreatId is a number that represents the type of treatment the physician can do - TreatDesc and TreatCost are Treatment description and treatment cost  Each patient is assigned one doctor, but a doctor can have many patients  There may be more than one patient in a room but each patient is kept in one room only  There is only one phone number in each room in the hospital  Each doctor can do only do one treatment, but a treatment can be done by many doctors  The treatment cost is fixed for each treatment  Each doctor works in only one department, but a department can have many doctors  Each department has 1 supervisor. This supervisor is just one of the physicians who works in that department  A Patient is charged based on the treatment cost and number of days in hospital Note that not all the rooms in the hospital has patient at a particular time but all patient must be is some rooms. Further, only some of the physicians are supervising the departments in the hospital; however, all departments must be managed by some physicians. You may make any other assumption you think is necessary but you have to be very specific and realistic. You can add other assumptions but you are not allowed to change the above assumptions Do the following a) Change the ERD to tables Table 1 : Physicians ( Phys ID , PhyName, PhysDept, DeptSuperviosorld, TreatCost, TrearDesc, TreatId) Table 2 : Patient ( Patient ID , Phys ID* , RoomPhone, RoomNo, RoomRate, Amount Owing, AdminDate, PatientAddress, PatientName, HospitalStaydays) b) Place the tables in 3 rd normal form Table 1 : Physicians ( Phys ID , PhyName, PhysDept* , TreatId* ) Table 2 : Patient ( Patient ID , RoomNo* , Phys ID* , AdminDate, PatientAddress, PatientName, HospitalStaydays) Table 3 : Treat ( Treat Id , TreatCost, TrearDesc) Table 4 : Room ( Room No , RoomPhone, RoomRate) Table 5: Department ( PhysDept , DeptSuperviosorlD* )

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version