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.
ER Diagram
1.5 Marks
1.5 Marks Tables before the Normalization
All schemas before normalization:
Patient(patientID,FirstName,LastName,PhoneNumber,dateOfBirth)
Clinic(ClinicID,name,location)
Cancer(CancerTypeID,Category,Stage,Treatment,patientID (fk),clinicID (fk),cancerDate)
Employee(EmployeeID,clinicID (fk),fname,lname,sex)
Tables after the normalization (At least in 3NF)
Create the Normalized Tables and Populate them with at least 5 Rows
Create Tables SQL
CREATE TABLE [Cancer](
[CancerTypeID] [int] IDENTITY(1,1) NOT NULL,
[PatientID] [int] NOT NULL,
[ClinicID] [int] NOT NULL,
[Category] [nvarchar](70) NULL,
[Stage] [nvarchar](30) NULL,
[Treatment] [nvarchar](30) NULL,
[CancerDate] [nchar](10) NULL,
CONSTRAINT [PK_Cancer] PRIMARY KEY CLUSTERED
(
[CancerTypeID] ASC,
[PatientID] ASC,
[ClinicID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [Clinic](
[ClinicID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Location] [nvarchar](50) NULL,
CONSTRAINT [PK_Clinic] PRIMARY KEY CLUSTERED
(
[ClinicID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [Employee](
[EmployeeID] [int] IDENTITY(100,10) NOT NULL,
[ClinicID] [int] NULL,
[Fname] [nvarchar](15) NULL,
[Lname] [nvarchar](15) NULL,
[sex] [nchar](10) NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [Patient](
[PatientID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](15) NULL,
[lastName] [nvarchar](15) NULL,
[PhoneNumber] [nchar](10) NULL,
[dateOfBirth] [date] NULL,
[Sex] [nchar](10) NULL,
CONSTRAINT [PK_Patient] PRIMARY KEY CLUSTERED
(
[PatientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE UNIQUE INDEX uq_patient
ON patient(firstname, lastname);
CREATE UNIQUE INDEX uq_clinic
ON clinic(name, location);
CREATE UNIQUE INDEX uq_employee
ON employee(fname, lname);
Insert Tables SQL
INSERT [Cancer] ([CancerTypeID], [PatientID], [ClinicID], [Category], [Stage], [Treatment], [CancerDate]) VALUES (1, 1, 1, N’Lung’, N’Stage I’, N’Chemotherapy ‘, N’1/1/2010 ‘)
GO
INSERT [Cancer] ([CancerTypeID], [PatientID], [ClinicID], [Category], [Stage], [Treatment], [CancerDate]) VALUES (2, 2, 1, N’Blood’, N’Stage II’, N’Surgery ‘, N’1/1/2011 ‘)
GO
INSERT [Cancer] ([CancerTypeID], [PatientID], [ClinicID], [Category], [Stage], [Treatment], [CancerDate]) VALUES (3, 3, 2, N’Blood’, N’Stage III’, N’Radiation therapy’, N’1/1/2013 ‘)
GO
INSERT [Cancer] ([CancerTypeID], [PatientID], [ClinicID], [Category], [Stage], [Treatment], [CancerDate]) VALUES (4, 4, 1, N’oral ‘, N’Stage II’, N’Surgery ‘, N’10/10/2010’)
GO
INSERT [Cancer] ([CancerTypeID], [PatientID], [ClinicID], [Category], [Stage], [Treatment], [CancerDate]) VALUES (5, 5, 4, N’Breast’, N’Stage II’, N’Radiation therapy’, N’1/1/2015 ‘)
GO
INSERT [Clinic] ([ClinicID], [Name], [Location]) VALUES (1, N’Clinic 1′, N’Jeddah’)
GO
INSERT [Clinic] ([ClinicID], [Name], [Location]) VALUES (2, N’Clinic 2′, N’Alkhobar’)
GO
INSERT [Clinic] ([ClinicID], [Name], [Location]) VALUES (3, N’Clinic 3′, N’Alkhobar’)
GO
INSERT [Clinic] ([ClinicID], [Name], [Location]) VALUES (4, N’Clinic 4′, N’Jeddah’)
GO
INSERT [Clinic] ([ClinicID], [Name], [Location]) VALUES (5, N’Clinic 5′, N’Alkhobar’)
GO
INSERT [Employee] ([EmployeeID], [ClinicID], [Fname], [Lname], [sex]) VALUES (110, 1, N’Mohamed’, N’Ali’, N’male ‘)
GO
INSERT [Employee] ([EmployeeID], [ClinicID], [Fname], [Lname], [sex]) VALUES (120, 2, N’Hassan’, N’Ahmad’, N’male ‘)
GO
INSERT [Employee] ([EmployeeID], [ClinicID], [Fname], [Lname], [sex]) VALUES (130, 1, N’Fawzi’, N’Moaaz’, N’male ‘)
GO
INSERT [Employee] ([EmployeeID], [ClinicID], [Fname], [Lname], [sex]) VALUES (140, 4, N’Hoda’, N’Hassan’, N’Female ‘)
GO
INSERT [Employee] ([EmployeeID], [ClinicID], [Fname], [Lname], [sex]) VALUES (150, NULL, N’Adel’, N’Mostafa’, N’male ‘)
GO
INSERT [Patient] ([PatientID], [FirstName], [lastName], [PhoneNumber], [dateOfBirth], [Sex]) VALUES (1, N’ALi’, N’Ahmad’, N’0556497668′, CAST(N’1970-01-01′ AS Date), N’male ‘)
GO
INSERT [Patient] ([PatientID], [FirstName], [lastName], [PhoneNumber], [dateOfBirth], [Sex]) VALUES (2, N’Hend’, N’Ali’, N’0538974562′, CAST(N’1985-01-01′ AS Date), N’female ‘)
GO
INSERT [Patient] ([PatientID], [FirstName], [lastName], [PhoneNumber], [dateOfBirth], [Sex]) VALUES (3, N’Kinda’, N’Mamdouh’, N’0556478995′, CAST(N’1990-01-01′ AS Date), N’female ‘)
GO
INSERT [Patient] ([PatientID], [FirstName], [lastName], [PhoneNumber], [dateOfBirth], [Sex]) VALUES (4, N’Youssef’, N’Mamdouh’, N’0553216547′, CAST(N’1968-01-10′ AS Date), N’male ‘)
GO
INSERT [Patient] ([PatientID], [FirstName], [lastName], [PhoneNumber], [dateOfBirth], [Sex]) VALUES (5, N’Mostafa’, N’ALI’, N’0555899632′, CAST(N’1999-01-01′ AS Date), N’MALE ‘)
GO
Write the sample requested Queries & Execute them.
List the first and last name of all patients who had blood cancer.
select firstname,lastname from patient where patientid in(select patientid from cancer where category=’blood’)
List names and location of clinics reported cases of lung cancer.
select name, location from clinic where clinicid in (select clinicid from cancer where category=’Lung’)
Find the names of patients whose age is above 50 years and had oral cancer since 10/10/2010.
select firstname,lastname from patient where DATEDIFF(hour,dateOfBirth,GETDATE())/8766 > 50 and patientid in (select patientid from cancer where category=’oral’ and cancerdate>=’10/10/2010′)
List the names of employees who are working in clinics, which do not have breast cancer case.
select employee.fname,employee.lname from employee inner join clinic on employee.ClinicID=clinic.clinicid where Clinic.clinicid in (select clinicid from cancer where category=’Breast’)