College of Computing and Informatics
[Total Mark for this Project is 10]
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
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
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.
In this file, you will find two database project ideas. You must choose one of them
to design and implement.
You can work on this project as a group (minimum 3 and maximum 4 students).
Each group member must submit the project individually with all group member
names mentioned in the cover page.
This project worth 10 marks and will be distributed as in the following:
Design the database, following an ER model.
Tables before Normalization.
Tables after Normalization
Use MySQL or any other database to create the normalized tables and
populate your tables with at least 5 rows.
e) Execute the requested sample queries.
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:
All schemas before normalization.
All schemas after normalization.
All SQL statements of:
Ã¢â€“Âª Creating tables.
Ã¢â€“Âª Inserting data in tables.
e) 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
Database System for a cancer registry
design to the
model, and write
Consider a database system for a Cancer Registry. The data requirements are
summarized as follows:
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.
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
Patient information must be stored at one clinic.
Patient Date of detection of Cancer.
1. Clinics are tracked by unique clinic ID.
2. Each clinic has a name and location.
3. The combination of the clinicÃ¢â‚¬â„¢s name and location must be unique.
1. Employees are tracked by unique Employee ID.
2. Clinic_id is a FK which references the Clinic ID which they work at.
3. Employees must work at a single Clinic.
4. Personal information includes Fname, Lname and sex.
5. The combination of an employeeÃ¢â‚¬â„¢s first and last name must be unique.
1. 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
List the names of employees who are working in clinics, which do not have
breast cancer case.
Database system for a Wholesale Management
design to the
Consider a database system for a Wholesale Management System. The data
requirements are summarized as follows:
o Maintain the details of stock like their id, name, quantity.
o Maintain the details of buyers from which manager must buy the stock
like buyer id, name, address, stock id to be bought.
o Details of customers i.e. name, address, id.
o Defaulters list of customers who have not paid their pending amount
after the due date So List of payment paid or pending.
model, and write
o The stock that is to buy if quantity goes less than a particular amount.
o Profit calculation for a month.
o Quantity cannot be sold to a customer if the required amount is not
present in stock and date of delivery should be maintained up to which
stock can be provided.
1. List of payment paid or pending customers.
2. Find the Defaulters list of customers who have not paid their pending amount.
3. Find the details of customers name, address, id.
4. Find Query to get information of employee where employee is not assigned to
List the stock that is to buy if quantity goes less than a particular amount.
Title of Your Project
a) ER Diagram
b) Tables before the Normalization
c) Tables after the normalization (At least in 3NF)
d) Create the Normalized Tables and Populate them with at least 5
e) Write the sample requested Queries & Execute them.
Purchase answer to see full