IT 244 Cancer Registry Database

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.

Instructions

• Design the database, following an ER model.
• Normalize the tables ( each table should be in 3NF at least)
• Use MySQL or any other database to create the normalized tables and populate your tables with at least 5 rows.
• Execute the sample requested queries.