Project Instructions & Marking Distribution
This project worth 10 marks and will be distributed as in the following:
Design the database, following an ER model. (3 marks)
Tables before Normalization. (1.5 marks)
Tables after Normalization (1.5 marks)
Use MySQL or any other database to create the normalized tables and
populate your tables with at least 5 rows. (2 marks)
Execute the requested sample queries. (2 marks)
Each student must submit one report containing the following:
ER Diagram.
All schemas before normalization.
All schemas after normalization.
All SQL statements of:
Creating tables.
Inserting data in tables.
All requested queries/results.
Screenshots from MySQL (or any other software you use) of all the tables after population and queries results.
Consider a database system for a Cancer Registry. The data requirements are summarized as follows:
1. Patients:
Patients are tracked by a unique patient ID.
Personal information includes first name, last name, phone number, Date of Birth, and sex.
The combination of the Patient’s first and last name must be unique.
Patient’s sex is a mandatory field.
Patient can be registered at any clinic.
2. Cancer:
Type of cancer is tracked by a unique ID.
Cancer is categorized as either Oral, Lung, Breast, Liver, Blood, Bone, Pancreatic, Cervical, or Prostrate.
Stage of cancer can be Stage I, Stage II, or Stage III
Stage of cancer detection.
Treatment given: Chemotherapy only, Surgery with chemotherapy, Surgery only or Radiation therapy.
Patient_id is an FK which references the ID of the Patient.
Clinic_id is an FK which references the ID of the Clinic that patient register.
Patient information must be stored at one clinic.
Patient Date of detection of Cancer.
3. Clinic:
Clinics are tracked by unique clinic ID.
Each clinic has a name and location.
The combination of the clinic’s name and location must be unique.
4. Employees:
Employees are tracked by unique Employee ID.
Clinic_id is a FK which references the Clinic ID which they work at.
Employees must work at a single Clinic.
Personal information includes Fname, Lname and sex.
The combination of an employee’s first and last name must be unique.
Queries:
List the first and last name of all patients who had blood cancer.
List names and location of clinics reported cases of lung cancer.
Find the names of patients whose age is above 50 years and had oral cancer since 10/10/2010.
List the names of employees who are working in clinics, which do not have breast cancer case.
Answer Sheet
ER Diagram
Tables before the Normalization
Tables after the normalization (At least in 3NF)
Create the Normalized Tables and Populate them with at least 5 Rows
Write the sample requested Queries & Execute them.
The post Project Instructions & Marking Distribution This project worth 10 marks and will appeared first on PapersSpot.