The purpose of the assignment is to test your skills in developing a small database using Database Management System (DBMS) and to apply the knowledge that you learn on manipulating queries.

REQUIREMENT / ASSIGNMENT QUESTION

Create a database with the tables listed below. Choose appropriate data types for each attribute.
Doctor (DoctorID, DoctorName, DepartmentName, HospitalID, City, Salary)
Patient (PatientID, PatientName, Sex, Birthdate, City, Phone, Disease, Bed, Room, HospitalID)
Hospital(HospitalID, HospitalName)
Treatment (DoctorID, PatientID, TypeofTreatment)

Using Microsoft Access (or any other DBMS tool), create the tables (using DDL SQL), and enter sample data (using DDL SQL or GUI). 10 records per table is enough. The data must satisfy the following constraint:
* there are at least 3 hospitals
* each bed of any room can only be assigned to one patient
* each room has no more than 3 patients
* each doctor only works in one hospital
* for each doctor, he or she should be in the same hospital with all the patients treated by him or her

Make sure you create the relationship between tables, also using DDL SQL. Then, write DML SQL queries that answer the questions below (one query per question) and run them.

1. Retrieve the name and address of patients who live in Kuala Lumpur and treated by Dr. Anwar.
2. Retrieve the name and salary of all doctors who can treat disease “Cancer” and who earn more than RM55,000.
3. Retrieve the name, sex and age of every patient in room 307 at Sungai Buloh hospital.
4. Retrieve the name, age and phone number of every female patient.
5. Retrieve the name of every doctor who has at least one male patient older than 75.
6. Dr. Ken is retiring, move all his patients to some other doctors and delete existing records relating to him. Make sure that all constraints are satisfied.

You should hand in a printout of:
1. Each query used to create the tables.
2. Each table which shows table name, the fields and the sample records.
3. The relationships between tables.
4. Each query in “SQL View”, so that the grader can see your SQL code.
5. The result of each query.

The purpose of the assignment is to test your skills in developing...