QuestionQuestion

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.

Solution PreviewSolution Preview

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.

Create table queries

CREATE TABLE [HOSPITAL](
[HOSPITALID] number PRIMARY KEY NOT NULL,
[HOSPITALNAME] text NULL
);


CREATE TABLE [DOCTOR](
[DOCTORID] number PRIMARY KEY NOT NULL,
[DOCTORNAME] text NULL,
[DEPARTMENTNAME] text NULL,
[HOSPITALID] number NULL,
[CITY] text NULL,
[SALARY] number NULL,
FOREIGN KEY (HOSPITALID) REFERENCES HOSPITAL(HOSPITALID)
);


CREATE TABLE [PATIENT](
[PATIENTID] number PRIMARY KEY NOT NULL,
[PATIENTNAME] text NULL,
[SEX] text NULL,
[BIRTHDATE] [datetime] NULL,
[CITY] text NULL,
[PHONE] text NULL,
[DISEASE] text NULL,
[BED] number NULL,
[ROOM] number NULL,
[HOSPITALID] number NULL,
FOREIGN KEY (HOSPITALID) REFERENCES HOSPITAL(HOSPITALID)
);

CREATE TABLE [TREATMENT](
[DOCTORID] number NULL,
[PATIENTID] number NULL,
[TYPEOFTREATMENT] text NULL,
FOREIGN KEY (DOCTORID) REFERENCES DOCTOR(DOCTORID),
FOREIGN KEY (PATIENTID) REFERENCES PATIENT(PATIENTID)
);...
$6.50 for this solution

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Database Development Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats