Database System for a cancer registry<\/p>\n
Consider a database system for a Cancer Registry. The data requirements are summarized as follows:<\/p>\n
1. Patients:<\/p>\n
Patients are tracked by a unique patient ID.<\/p>\n
Personal information includes first name, last name, phone number, Date of Birth, and sex.<\/p>\n
The combination of the Patient\u2019s first and last name must be unique.<\/p>\n
Patient\u2019s sex is a mandatory field.<\/p>\n
Patient can be registered at any clinic.<\/p>\n
2. Cancer:<\/p>\n
Type of cancer is tracked by a unique ID.<\/p>\n
Cancer is categorized as either Oral, Lung, Breast, Liver, Blood, Bone, Pancreatic, Cervical, or Prostrate.<\/p>\n
Stage of cancer can be Stage I, Stage II, or Stage III<\/p>\n
Stage of cancer detection.<\/p>\n
Treatment given: Chemotherapy only, Surgery with chemotherapy, Surgery only or Radiation therapy.<\/p>\n
Patient_id is an FK which references the ID of the Patient.<\/p>\n
Clinic_id is an FK which references the ID of the Clinic that patient register.<\/p>\n
Patient information must be stored at one clinic.<\/p>\n
Patient Date of detection of Cancer.<\/p>\n
3. Clinic:<\/p>\n
Clinics are tracked by unique clinic ID.<\/p>\n
Each clinic has a name and location.<\/p>\n
The combination of the clinic\u2019s name and location must be unique.<\/p>\n
4. Employees:<\/p>\n
Employees are tracked by unique Employee ID.<\/p>\n
Clinic_id is a FK which references the Clinic ID which they work at.<\/p>\n
Employees must work at a single Clinic.<\/p>\n
Personal information includes Fname, Lname and sex.<\/p>\n
The combination of an employee\u2019s first and last name must be unique.<\/p>\n
Queries:<\/p>\n
List the first and last name of all patients who had blood cancer.<\/p>\n
List names and location of clinics reported cases of lung cancer.<\/p>\n
Find the names of patients whose age is above 50 years and had oral cancer since 10\/10\/2010.<\/p>\n
List the names of employees who are working in clinics, which do not have breast cancer case.<\/p>\n
ER Diagram <\/p>\n
1.5 Marks<\/p>\n
1.5 Marks Tables before the Normalization <\/p>\n
All schemas before normalization:<\/p>\n
Patient(patientID,FirstName,LastName,PhoneNumber,dateOfBirth)<\/p>\n
Clinic(ClinicID,name,location)<\/p>\n
Cancer(CancerTypeID,Category,Stage,Treatment,patientID (fk),clinicID (fk),cancerDate)<\/p>\n
Employee(EmployeeID,clinicID (fk),fname,lname,sex)<\/p>\n
Tables after the normalization (At least in 3NF) <\/p>\n
Create the Normalized Tables and Populate them with at least 5 Rows <\/p>\n
Create Tables SQL<\/p>\n
CREATE TABLE [Cancer](<\/p>\n
[CancerTypeID] [int] IDENTITY(1,1) NOT NULL,<\/p>\n
[PatientID] [int] NOT NULL,<\/p>\n
[ClinicID] [int] NOT NULL,<\/p>\n
[Category] [nvarchar](70) NULL,<\/p>\n
[Stage] [nvarchar](30) NULL,<\/p>\n
[Treatment] [nvarchar](30) NULL,<\/p>\n
[CancerDate] [nchar](10) NULL,<\/p>\n
CONSTRAINT [PK_Cancer] PRIMARY KEY CLUSTERED <\/p>\n
(<\/p>\n
[CancerTypeID] ASC,<\/p>\n
[PatientID] ASC,<\/p>\n
[ClinicID] ASC<\/p>\n
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]<\/p>\n
) ON [PRIMARY]<\/p>\n
CREATE TABLE [Clinic](<\/p>\n
[ClinicID] [int] IDENTITY(1,1) NOT NULL,<\/p>\n
[Name] [nvarchar](50) NULL,<\/p>\n
[Location] [nvarchar](50) NULL,<\/p>\n
CONSTRAINT [PK_Clinic] PRIMARY KEY CLUSTERED <\/p>\n
(<\/p>\n
[ClinicID] ASC<\/p>\n
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]<\/p>\n
) ON [PRIMARY]<\/p>\n
CREATE TABLE [Employee](<\/p>\n
[EmployeeID] [int] IDENTITY(100,10) NOT NULL,<\/p>\n
[ClinicID] [int] NULL,<\/p>\n
[Fname] [nvarchar](15) NULL,<\/p>\n
[Lname] [nvarchar](15) NULL,<\/p>\n
[sex] [nchar](10) NULL,<\/p>\n
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED <\/p>\n
(<\/p>\n
[EmployeeID] ASC<\/p>\n
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]<\/p>\n
) ON [PRIMARY]<\/p>\n
CREATE TABLE [Patient](<\/p>\n
[PatientID] [int] IDENTITY(1,1) NOT NULL,<\/p>\n
[FirstName] [nvarchar](15) NULL,<\/p>\n
[lastName] [nvarchar](15) NULL,<\/p>\n
[PhoneNumber] [nchar](10) NULL,<\/p>\n
[dateOfBirth] [date] NULL,<\/p>\n
[Sex] [nchar](10) NULL,<\/p>\n
CONSTRAINT [PK_Patient] PRIMARY KEY CLUSTERED <\/p>\n
(<\/p>\n
[PatientID] ASC<\/p>\n
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]<\/p>\n
) ON [PRIMARY]<\/p>\n
CREATE UNIQUE INDEX uq_patient<\/p>\n
ON patient(firstname, lastname);<\/p>\n
CREATE UNIQUE INDEX uq_clinic<\/p>\n
ON clinic(name, location);<\/p>\n
CREATE UNIQUE INDEX uq_employee<\/p>\n
ON employee(fname, lname);<\/p>\n
Insert Tables SQL<\/p>\n
INSERT [Cancer] ([CancerTypeID], [PatientID], [ClinicID], [Category], [Stage], [Treatment], [CancerDate]) VALUES (1, 1, 1, N’Lung’, N’Stage I’, N’Chemotherapy ‘, N’1\/1\/2010 ‘)<\/p>\n
GO<\/p>\n
INSERT [Cancer] ([CancerTypeID], [PatientID], [ClinicID], [Category], [Stage], [Treatment], [CancerDate]) VALUES (2, 2, 1, N’Blood’, N’Stage II’, N’Surgery ‘, N’1\/1\/2011 ‘)<\/p>\n
GO<\/p>\n
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 ‘)<\/p>\n
GO<\/p>\n
INSERT [Cancer] ([CancerTypeID], [PatientID], [ClinicID], [Category], [Stage], [Treatment], [CancerDate]) VALUES (4, 4, 1, N’oral ‘, N’Stage II’, N’Surgery ‘, N’10\/10\/2010’)<\/p>\n
GO<\/p>\n
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 ‘)<\/p>\n
GO<\/p>\n
INSERT [Clinic] ([ClinicID], [Name], [Location]) VALUES (1, N’Clinic 1′, N’Jeddah’)<\/p>\n
GO<\/p>\n
INSERT [Clinic] ([ClinicID], [Name], [Location]) VALUES (2, N’Clinic 2′, N’Alkhobar’)<\/p>\n
GO<\/p>\n
INSERT [Clinic] ([ClinicID], [Name], [Location]) VALUES (3, N’Clinic 3′, N’Alkhobar’)<\/p>\n
GO<\/p>\n
INSERT [Clinic] ([ClinicID], [Name], [Location]) VALUES (4, N’Clinic 4′, N’Jeddah’)<\/p>\n
GO<\/p>\n
INSERT [Clinic] ([ClinicID], [Name], [Location]) VALUES (5, N’Clinic 5′, N’Alkhobar’)<\/p>\n
GO<\/p>\n
INSERT [Employee] ([EmployeeID], [ClinicID], [Fname], [Lname], [sex]) VALUES (110, 1, N’Mohamed’, N’Ali’, N’male ‘)<\/p>\n
GO<\/p>\n
INSERT [Employee] ([EmployeeID], [ClinicID], [Fname], [Lname], [sex]) VALUES (120, 2, N’Hassan’, N’Ahmad’, N’male ‘)<\/p>\n
GO<\/p>\n
INSERT [Employee] ([EmployeeID], [ClinicID], [Fname], [Lname], [sex]) VALUES (130, 1, N’Fawzi’, N’Moaaz’, N’male ‘)<\/p>\n
GO<\/p>\n
INSERT [Employee] ([EmployeeID], [ClinicID], [Fname], [Lname], [sex]) VALUES (140, 4, N’Hoda’, N’Hassan’, N’Female ‘)<\/p>\n
GO<\/p>\n
INSERT [Employee] ([EmployeeID], [ClinicID], [Fname], [Lname], [sex]) VALUES (150, NULL, N’Adel’, N’Mostafa’, N’male ‘)<\/p>\n
GO<\/p>\n
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 ‘)<\/p>\n
GO<\/p>\n
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 ‘)<\/p>\n
GO<\/p>\n
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 ‘)<\/p>\n
GO<\/p>\n
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 ‘)<\/p>\n
GO<\/p>\n
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 ‘)<\/p>\n
GO<\/p>\n
Write the sample requested Queries & Execute them. <\/p>\n
List the first and last name of all patients who had blood cancer.<\/p>\n
select firstname,lastname from patient where patientid in(select patientid from cancer where category=’blood’)<\/p>\n
List names and location of clinics reported cases of lung cancer.<\/p>\n
select name, location from clinic where clinicid in (select clinicid from cancer where category=’Lung’)<\/p>\n
Find the names of patients whose age is above 50 years and had oral cancer since 10\/10\/2010.<\/p>\n
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′)<\/p>\n
List the names of employees who are working in clinics, which do not have breast cancer case.<\/p>\n
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’)<\/p>\n","protected":false},"excerpt":{"rendered":"
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\u2019s first and last name must […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[10],"class_list":["post-79255","post","type-post","status-publish","format-standard","hentry","category-research-paper-writing","tag-writing"],"_links":{"self":[{"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/posts\/79255","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/comments?post=79255"}],"version-history":[{"count":0,"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/posts\/79255\/revisions"}],"wp:attachment":[{"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/media?parent=79255"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/categories?post=79255"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/tags?post=79255"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}