Assignment 1 Solution

Question #1




Consider the following data. Arrows show the functional dependency.










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.

 

Write the tables
Place the tables in 3rd normal form (if necessary)
Create ERD based on the normalized tables
Write a script to create a database. Your script should create the tables and ensures that all constraints are set properly.
Here is some information to create your tables




Data Item (Column Name)
Type
Restriction
ProductId
Numeric – Integer


ItemNum
Numeric – Integer
Not null
QuantityUsed
Numeric – Integer
= 0
ItemDescription
Character – Up to 200


ProductDescription
Character – Up to 200


ReceiptNumber
Numeric – Integer
Not null
QuantitySold
Numeric – Integer
= 0
SalesDate
Date





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
Each patient is being treated for one sickness 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




Change the ERD to tables
Place the tables in 3rd normal form (if necessary)
Revise the given ERD based on the normalized tables (if necessary)
Write a script to create a database. Your script should create the tables and ensures that all constraints are set properly.



Here is some information to create your tables using SQL. Depending on your normalization process, some of the following fields may not be in your final normalized table.




Data Item (Column Name)
Type
restrictions
PatientID
Numeric – Integer


PhysID
Numeric – Integer


RoomNo
Numeric – Integer
= 100 and <= 999
AdmitDate
Date


PatientName
Character -- Up to 50
Not null
PatientAddress
Character -- Up to 200
Not null
RoomPhone
Character -- Up to 8


HospitalStayDays
Numeric – Integer
= 0
RoomRate
Numeric – Decimal

10 with 2 decimals
= 30.00 and < =100.00
AmountOwing
Numeric – Decimal

10 with 2 decimals


PhysName
Character -- Up to 50
Not null
PhysDept
Numeric – Integer


DeptSupervisorId
Numeric – Integer


TreatId
Numeric – Integer


TreatDesc
Character -- Up to 200


TreatCost
Numeric – Decimal

10 with 2 decimals
= 50.00






Question #3




Create the tables related to the following ERD




Question #4




Create the tables related to the following ERD




Question #5




Create ERD based on the following tables. The underlines attributes are primary keys. The links are connection between primary keys and foreign keys

Question #6




Create ERD based on the following tables. The underlines attributes are primary keys. The links are connection between primary keys and foreign keys