+1(978)310-4246 credencewriters@gmail.com
  

College of Computing and Informatics
Annual
Report
FY [YEAR]
[Total Mark for this Project is 10]
Student Details:
Name: ###
ID: ###
CRN: ###
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.
Project Instructions
Pg. 01
Project Instructions
•
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:
a)
b)
c)
d)
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)
e) 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:
a)
b)
c)
d)
ER Diagram.
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.
Project Instructions
Pg. 02
•
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
Project I
Pg. 03
Learning
Outcome(s):
Project I
Database System for a cancer registry
LO 4
Design a
database starting
from the
conceptual
design to the
implementation
of database
schemas.
LO 3
Create EntityRelationship
model, Relational
model, and write
SQL queries.
Consider a database system for a Cancer Registry. The data requirements are
summarized as follows:
1. Patients:
1.
Patients are tracked by a unique patient ID.
2.
Personal information includes first name, last name, phone number, Date of
Birth, and sex.
3.
The combination of the Patient’s first and last name must be unique.
4.
Patient’s sex is a mandatory field.
5.
Patient can be registered at any clinic.
2. Cancer:
1.
Type of cancer is tracked by a unique ID.
2.
Cancer is categorized as either Oral, Lung, Breast, Liver, Blood, Bone,
Pancreatic, Cervical, or Prostrate.
3.
Stage of cancer can be Stage I, Stage II, or Stage III
4.
Stage of cancer detection.
5.
Treatment given: Chemotherapy only, Surgery with chemotherapy, Surgery
only or Radiation therapy.
6.
Patient_id is an FK which references the ID of the Patient.
7.
Clinic_id is an FK which references the ID of the Clinic that patient
register.
8.
Patient information must be stored at one clinic.
9.
Patient Date of detection of Cancer.
Project I
Pg. 04
3. Clinic:
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.
4. Employees:
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.
Queries:
1. List the first and last name of all patients who had blood cancer.
2.
List names and location of clinics reported cases of lung cancer.
3.
Find the names of patients whose age is above 50 years and had oral cancer
since 10/10/2010.
4.
List the names of employees who are working in clinics, which do not have
breast cancer case.
Project II
Pg. 05
Learning
Outcome(s):
Project II
Database system for a Wholesale Management
System
LO 4
Design a
database starting
from the
conceptual
design to the
implementation
of database
schemas.
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.
LO 3
Create EntityRelationship
model, Relational
model, and write
SQL queries.
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.
SQL Queries:
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.
Project II
Pg. 06
4. Find Query to get information of employee where employee is not assigned to
the department.
5.
List the stock that is to buy if quantity goes less than a particular amount.
Your Project
Pg. 07
Your Project
10 Marks
Title of Your Project
a) ER Diagram
3 Marks
Your Project
Pg. 08
b) Tables before the Normalization
1.5 Marks
Your Project
Pg. 09
c) Tables after the normalization (At least in 3NF)
1.5 Marks
Your Project
Pg. 10
d) Create the Normalized Tables and Populate them with at least 5
Rows
2 Marks
Pg. 11
Your Project
e) Write the sample requested Queries & Execute them.
2 Marks

Purchase answer to see full
attachment

  
error: Content is protected !!