Pg. 01 خطأ! استخدم علامة التبويب “الصفحة الرئيسية” لتطبيق Heading 1 على

Pg. 01

خطأ! استخدم علامة التبويب “الصفحة الرئيسية” لتطبيق Heading 1 على النص الذي ترغب في أن يظهر هنا.

College of Computing and Informatics

Project

Deadline: Sunday 12/12/2021@ 23:59

[Total Mark for this Project is 10]

Project

Deadline: Sunday 12/12/2021@ 23:59

[Total Mark for this Project is 10]

Fundamentals of Databases

IT403

Fundamentals of Databases

IT403

Instructions:

You must submit two separate copies (one Word file and one PDF file) using the Assignment Template on Blackboard via the allocated folder. These files must not be in compressed format.

It is your responsibility to check and make sure that you have uploaded both the correct files.

Zero mark will be given if you try to bypass the SafeAssign (e.g., misspell words, remove spaces between words, hide characters, use different character sets, convert text into image or languages other than English or any kind of manipulation).

Email submission will not be accepted.

You are advised to make your work clear and well-presented. This includes filling your information on the cover page.

You must use this template, failing which will result in zero mark.

You MUST show all your work, and text must not be converted into an image, unless specified otherwise by the question.

Late submission will result in ZERO mark.

The work should be your own, copying from students or other resources will result in ZERO mark.

Use Times New Roman font for all your answers.

Instructions:

You must submit two separate copies (one Word file and one PDF file) using the Assignment Template on Blackboard via the allocated folder. These files must not be in compressed format.

It is your responsibility to check and make sure that you have uploaded both the correct files.

Zero mark will be given if you try to bypass the SafeAssign (e.g., misspell words, remove spaces between words, hide characters, use different character sets, convert text into image or languages other than English or any kind of manipulation).

Email submission will not be accepted.

You are advised to make your work clear and well-presented. This includes filling your information on the cover page.

You must use this template, failing which will result in zero mark.

You MUST show all your work, and text must not be converted into an image, unless specified otherwise by the question.

Late submission will result in ZERO mark.

The work should be your own, copying from students or other resources will result in ZERO mark.

Use Times New Roman font for all your answers.

Student Details:

Name: ###

CRN: ###

ID: ###

Student Details:

Name: ###

CRN: ###

ID: ###

Project Instructions

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 about his/her chosen Project via the Blackboard (Email submission will not be accepted which will be awarded ZERO marks) 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.

You are advised to make your work clear and well presented; marks may be reduced for poor presentation. This includes filling your information on the cover page.

You MUST show all your work, and text must not be converted into an image, unless specified otherwise by the question.

Late submission will result in ZERO marks being awarded.

The work should be your own, copying from students or other resources will result in ZERO marks.

Use Times New Roman font

Learning Outcome(s):

LO 4

Design a database starting from the conceptual design to the implementation of database schemas.

LO 3

Create Entity-Relationship model, Relational model, and write SQL queries.

Learning Outcome(s):

LO 4

Design a database starting from the conceptual design to the implementation of database schemas.

LO 3

Create Entity-Relationship model, Relational model, and write SQL queries.

Project I

Database System for a cancer registry

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.

10 Marks

10 Marks

Your Project

Title of Your Project

3 Marks

3 Marks

ER Diagram

1.5 Marks

1.5 Marks Tables before the Normalization

1.5 Marks

1.5 Marks Tables after the normalization (At least in 3NF)

2 Marks

2 Marks Create the Normalized Tables and Populate them with at least 5 Rows

2 Marks

2 Marks Write the sample requested Queries & Execute them.