May 27, 202311 min read

Advance Database Management System Assignment Help | Sample Paper | Realcode4you

Question 1

Viruses have mutated and countries are sharing data to help control the spread of infection. For ease of communication, when variants are discovered, they are given a unique label, e.g., Epsilon. Each variant has one or more Pango (or parental) lineage e.g., B.1.427 and B.1.429. The Pango lineages of different variants may overlap. The points of mutations in the genes for each variant are also recorded, e.g., I4205V and D1183Y in the ORF1ab gene, and S13I, W152C, L452R in the spike protein's S-gene. Several variants have common mutation points, and it is useful to the scientific communities to know the variants that are similar to others.

A variant is given a classification when it becomes necessary, and the classifications are based on factors such as speed of transmission and number of cases. The categories are as follow: variant being monitored (VBM), variant of interest (VOI), variant of concern (VOC) and variant of high consequence (VOHC). For example, the Epsilon variant has been classified as VOC on March 19, 2021, as VOI on February 26, 2021 and June 29, 2021, and as VBM on September 21, 2021.

When a country detects case(s) of the variant, the date(s) and patient biodata, identified by a running case number for that country are tracked. Patient biodata include data such as nationality, gender, year of birth, vaccination history (dates and vaccines given), as well as patient symptoms (e.g., fever and runny nose). Each symptom has a general description. For example, a low-grade fever is described as any temperature 99.5°F (37.5°C) and 100.3°F (38.3°C). The scientific communities note that there are cases of reinfection, possibly from the same or different variants, and these cases are of special interest.

For each country, record the population size as well as percentage of population that has been vaccinated. Cases are also defined as local primary, local secondary (when traced to a local primary case) or imported cases. Imported cases have a history of travel from an affected county 14 days before the onset of infection. The travel history of such cases must be recorded.

Construct a conceptual (ER) model from the statements of requirements to represent the data model, showing

entities, with entities name, relevant attributes and identifier,
relationships with maximum and minimum cardinalities and relationship name.

State your assumption(s) for only data requirements that are not specified.

Question 2

Many countries set up screening facilities to conduct tests to detect infection. On any day, a person may go to only one screening facility to receive one or more tests. Test specimens from each screening facility are sent to one of the several labs assigned to it, and where one lab scientist performs diagnosis on the test specimen. A lab may serve more than one screening facility, and lab scientists are assigned to work in only one lab.

You are given the table:

TestDone (personId, testDate, testCode, testResult, screeningFacilityId, screenFacilityAddress, labId, labAddress, labScientist)

State your assumption(s) for only data requirements that are not specified.

(a) Formulate and list the functional and multi-valued dependencies.

(b) Draw dependency graph and propose candidate key(s) for the table. Show composite keys in brackets.

(c)

For each MVD in answer to part a), state whether it is subsumed.
Normalise the table to BCNF and 4NF by applying the BCNF approach covered in the course text. Show how you apply the steps to arrive at the tables and explain whether each derived table at each iteration is already in BCNF and 4NF (if applicable).

Question 3

(a) (i) Transform the ERD in Figure Q3(a)(i) into a logical model.

(ii) Refer to the description of the columns for the column definition, and then write the appropriate SQL statements to implement the tables, derived from the ERD in Figure Q3(a)(i). List your SQL statements in the order that they should be executed.

(iii) Use the data in Figure Q3(a)(iii) to populate the tables implemented in part (ii). List your SQL statements in the order that they should be executed.

(b) Formulate SQL queries to display the given resultsets

(i) Male patients above 50 years old, ordered by descending age and ascending caseNumber if the ages are the same.

(ii) Treatment and the daily dosage. Include patients who are not given any treatment. Order the resultset by caseNumber and drugCode.

Dose per day is computed using the formula:

(iii) Patients who have been treated with both AU02 and DR06.

(iv) Patients who have a negative reaction to the drug with code AU02. Use either non-correlated or correlate subquery. No mark will be awarded to answers using join.

(v) Patients who have completed treatment (that is, started on treatment and then have stopped consuming drug). The dates startedOn and endedOn are the first and last days when drugs are administered.

Ensure that the daily dosage does not exceed the max daily dosage whenever a new record is created.
Generate a running number for the new record if daily dosage does not exceed max daily dosage.
Print messages such as 'Error: Daily dosage exceeded' etc. where appropriate.

Test your triggers completely and show screenshots of the test queries and output. State any assumptions you made in implementing the trigger.

Question 4

(a) Demonstrate the basic techniques of concurrency control and their application.

Suppose the database including the trigger in Q3 has been implemented. A transaction, T1 running at transaction isolation level read uncommitted is attempting to insert a new case patient and a new treatment record (for the new case) prescribed with the drug AU02 and the values 0.5, 3, 1 for dosage, frequency and frequencyInterval respectively.

Concurrently, another transaction, T2 running at isolation level read committed is attempting to change the maxDailyDosage for AU02. Assume there is also a trigger which prevents the maxDailyDosage for a drug from being updated if there are patients who are still undergoing treatment with it (that is, dateStopped column is null).

Discuss what problem(s) may arise, and then suggest the lowest transaction isolations for T1 and T2 to avoid those problems. The transaction isolation levels for T1 and T2 need not be the same.

(b) Describe TWO permissions that should be given to doctors who prescribe treatments for patients. Describe TWO permissions that should not be given to doctors.

(c) Given Figure 4(c), explain what the database recovery procedure will do for the transactions T1 to T4. Explain also how the transaction log help ensure the ACID properties of T1 to T4.

Solution

Question 1

Question 2

a. Assumptions:

testCodes are types of test eg PCR or ART tests
testResults are either Positive/negative or inconclusive
Person is committed to one screening facility only on any day
One person can only do 1 unique test in a day. (cannot take 2 PCR or ART in a day)

FD1) personId, testDate → screeningFacilityId

FD2) screeningFacilityId → screenFacilityAddress yes

FD3) personId, testDate, testCode, labScientist → testResult, labScientist

FD4) labScientist → labId yes

FD5) labId → labAddress yes

MVD1) labId →→ labScientist (subsumed by FD4) yes

MVD2) personId →→ testCode (subsumed by FD2)

MVD3) personId →→testResult (subsumed by FD3)

MVD4) testCode →→ personId (subsumed by FD3)

FDs and MVDs

screeningFacilityId -> screenFacilityAddress (screening facility has one address)

personId, testDate -> screeningFacilityId (a person on any date can go to one screening facility only)

labId -> labAddress (lab has one address)

labScientistId -> labId (a lab scientist works in one lab)

labId ->-> labScientistId (each lab has one or more lab scientists working there)

personId, testDate, testCode -> labScientistId, testResult (a person’s test specimen identified by the person taking a type of test on a date, is diagnosed by one lab scientist who gives one result)

These MVDs are not subsumed because they record labs assigned to screening facility, and an assigned lab may not have received any test specimen from the screening facility

screeningFacilityId ->-> labId (one or more labs is assigned to a screening facility)

labId ->-> screenFacilityId (a lab can serve one or more screening facilities)

These MVDs are subsumed because they identify a test specimen which will be sent for diagnosis. Hence, it is subsumed by FD regarding the diagnosis.

personId, testCode ->-> testDate (a person can take the same test on multiple dates)

personId, testDate ->-> testCode (on a date, a person can take multiple tests)

personId ->-> testDate, testCode (a person has many test date and test code combinations)

c (i)

MVD1) labId →→ labScientist (subsumed by FD4)

MVD2) personId →→ testCode (subsumed by FD2)

MVD3) personId →→,testResult (subsumed by FD2)

c (ii)

Firstly we have to normalise the transitive dependency :

personId → screeningFacilityId

and

screeningFacilityId → screenFacilityAddress

normalise to:

personId → (screeningFacilityId,screenFacilityAddress)

show what happen to original table

labScientist → labId

and

labId → labAddress

normalise to:

labScientist → (labId,labAddress) not fully normalised yet

Next we do the iterations:

TestDone (personId, testDate, testCode, testResult, screeningFacilityId, screenFacilityAddress, labId, labAddress, labScientist) is not in BCNF because personId → (screeningFacilityId, screenFacilityAddress).

Move personId → (screeningFacilityId, screenFacilityAddress) to another table leaving a copy of screeningFacilityId as a FK in the original table.

Person(personId,screeningFacilityId,screenFacilityAddress)

personId is the PK and the only determinant hence Person is in BCNF yes

TestDone (personId, testDate, testCode, testResult, screeningFacilityId, screenFacilityAddress, labId, labAddress, labScientist) is not in BCNF because labScientist → (labId,labAddress).

Move labScientist → (labId,labAddress) to another table leaving a copy of labScientist as a FK in the original table.

Lab(labScientist,labId,labAddress)

labScientist is the PK and the only determinant hence Lab is in BCNF

TestDone (personId, testDate, testCode, testResult, labId, labAddress, labScientist) is in BCNF because the determinants personId, testDate, testCode, testResult are candidate keys

TestDone(personId, testDate, testCode, labScientist, testResult)

Start with explaining why original table is not in bcnf or 4nf, and identify the FD or MVD that is the cause. Then perform the transformation, and explain the transformation. Finally, explain whether the resulting tables (new and original) are in BCNF/4NF. Repeat until all tables in BCNF and 4NF.

Person(personId(PK),screeningFacilityId,screenFacilityAddress)

where Person.personId must exist in TestDone.personId.

Lab(labScientist(PK),labId,labAddress)

where Lab.labScientist must exist in TestDone.labScientist.

TestDone(personId(PK)(FK), testDate(PK), testCode(PK), labScientist(PK)(FK), testResult)

Where Person.personId must exist in TestDone.personId and Lab.labScientist must exist in TestDone.labScientist FK and PK incorrect

Resulting table

Lab(labId(PK), labAddress)

LabScientist(labScientistId(PK), labId(FK))

where LabScientist.labId must exist in Lab.labId

ScreeningFacility(screeningFacilityId(PK), screenFacilityAddress)

LabAssigned(screeningFacilityId(PK, FK), labId (PK, FK)) where

LabAssigned.screeningFacilityId must exist in ScreeningFacility.screeningFacilityId and LabAssigned.labId must exist in Lab.labId

Visit(personId(PK), testDate(PK), screeningFacilityId(FK)) where

Visit.screeningFacilityId must exist in ScreeningFacility.screeningFacilityId

Diagnosis (personId(PK, FK), testDate(PK, FK), testCode(PK), testResult, labScientistId(FK)) where

Diagnosis.personId, testDate must exist in Visit.personId, testDate and

Diagnosis.labScientistIdmust exist in LabScientist.labScientistId

Question 3

CasePatient(caseNumber(PK),patientAge,gender)

TreatmentGiven(caseNumber(PK1.1)(FK1.1),runningNumber(PK1.2),dateStarted,dateStopped,dosage,frequency,frequencyInterval,drugCode(FK2.1))

where TreatmentGiven.caseNumber must exist in CasePatient.caseNumber

and

where TreatmentGiven.drugCode must exist in Drug.drugCode.

TreatmentResponse(responseNumber(PK),dateRecorded,outcome,caseNumber(FK1.1),runningNumber(FK1.2))

where TreatmentResponse.caseNumber and TreatmentResponse.runningNumber must exist in TreatmentGiven.caseNumber And TreatmentGiven.caseNumber. as one FK constraint

Drug(drugCode(PK),maxDailyDosage)

--create CasePatient Table

CREATE TABLE CasePatient (

caseNumber int NOT NULL IDENTITY(1,1),

patientAge int NOT NULL,

gender char(1) NOT NULL,

CONSTRAINT casePatient_PK PRIMARY KEY(caseNumber),

CONSTRAINT patientAge_CK CHECK(patientAge > 0 AND patientAge < 150),

CONSTRAINT gender_CK CHECK(gender IN('M','F'))

);

--create Drug Table

CREATE TABLE Drug (

drugCode char(4) NOT NULL,

maxDailyDosage decimal(4,1) NOT NULL,

CONSTRAINT drugCode_PK PRIMARY KEY(drugCode),

CONSTRAINT drugCode_CK CHECK(drugCode LIKE '[A-Z][A-Z][0-9][0-9]'),

CONSTRAINT maxDailyDosage_CK CHECK(maxDailyDosage >= 0)

);

--create TreatmentGiven Table

CREATE TABLE TreatmentGiven (

caseNumber int NOT NUll,

runningNumber int NOT NULL,

dateStarted date NOT NULL,

dateStopped date NULL,

dosage decimal(5,2) NOT NULL,

frequency int NOT NULL,

frequencyInterval int NOT NULL,

drugCode char(4) NOT NULL,

CONSTRAINT treatmentGiven_PK PRIMARY KEY(caseNumber,runningNumber),

CONSTRAINT treatmentGiven_FK FOREIGN KEY(caseNumber)

REFERENCES CasePatient(caseNumber)

ONUPDATE NO ACTION

ONDELETE CASCADE,

CONSTRAINT date_CK CHECK(dateStopped >= dateStarted),

CONSTRAINT dosage_CK CHECK(dosage > 0),

CONSTRAINT frequency_CK CHECK(frequency > 0),

CONSTRAINT frequencyInterval_CK CHECK(frequencyInterval IN(1,7,14,30,90,180,365)),

CONSTRAINT treatmentGiven_FK2 FOREIGN KEY(drugCode)

REFERENCES Drug(drugCode)

ONUPDATE CASCADE

ON DELETE NO ACTION

);

--create TreatmentResponse Table

CREATE TABLE TreatmentResponse (

responseNumber int NOTNULL IDENTITY(1,1),

dateRecorded date NULL DEFAULT (Convert(date, getdate())),

outcome char(17) NOT NULL,

caseNumber int NOTNUll,

runningNumber int NOTNULL,

CONSTRAINT treatmentResponse_PK PRIMARY KEY(responseNumber),

CONSTRAINT outcome_CK CHECK(outcome IN('negative reaction','no change','slightly improved','improved','greatly improved')),

CONSTRAINT treatmentResponse_FK FOREIGN KEY(caseNumber,runningNumber)

REFERENCES TreatmentGiven(caseNumber,runningNumber)

ONUPDATE NO ACTION

ONDELETE NO ACTION

);

--this is the autogenerate running number for TreatmentGiven

CREATE TRIGGER runningNumber on TreatmentGiven

INSTEAD OF INSERT AS

BEGIN

SETNOCOUNT ON;

DECLARE

@caseNumber AS int,

@runningNumber AS int,

@dateStarted AS date,

@dateStopped AS date,

@dosage ASdecimal(5,2),

@frequency AS int,

@frequencyInterval AS int,

@drugCode AS char(4),

@rowCount AS int

-- get the inserted table values

SELECT

@caseNumber = caseNumber,

@runningNumber = runningNumber,

@dateStarted = dateStarted,

@dateStopped = dateStopped,

@dosage = dosage,

@frequency = frequency,

@frequencyInterval = frequencyInterval,

@drugCode = drugCode

FROM inserted;

--get the table row number

SELECT* FROM TreatmentGiven WHERE TreatmentGiven.caseNumber = @caseNumber;

SET @rowCount = @@ROWCOUNT

-- checking for running number

IF (@rowCount = 0)-- This is first time patient is given a treatment.

BEGIN

SET @runningNumber = 1

END

ELSE-- if notthe first time.

BEGIN

SET @runningNumber = @rowCount + 1

END

INSERT INTO TreatmentGiven (caseNumber,runningNumber,dateStarted,dateStopped,dosage,frequency,frequencyInterval,drugCode)

VALUES(@caseNumber, @runningNumber, @dateStarted, @dateStopped, @dosage, @frequency, @frequencyInterval, @drugCode)

PRINT 'Data successfully inserted!'

END

a) iii

--inserting CasePatient Block

BEGIN

INSERT INTO CasePatient (patientAge,gender)

VALUES (62,'M');

INSERT INTO CasePatient (patientAge,gender)

VALUES (58,'F');

INSERT INTO CasePatient (patientAge,gender)

VALUES (47,'M');

INSERT INTO CasePatient (patientAge,gender)

VALUES (60,'M');

INSERT INTO CasePatient (patientAge,gender)

VALUES (70,'M');

INSERT INTO CasePatient (patientAge,gender)

VALUES (56,'F');

INSERT INTO CasePatient (patientAge,gender)

VALUES (55,'F');

INSERT INTO CasePatient (patientAge,gender)

VALUES (74,'M');

INSERT INTO CasePatient (patientAge,gender)

VALUES (43,'F');

INSERT INTO CasePatient (patientAge,gender)

VALUES (45,'M');

INSERT INTO CasePatient (patientAge,gender)

VALUES (73,'F');

;

END

-- inserting Drugs Block

BEGIN

INSERT INTO Drug (drugCode,maxDailyDosage)

VALUES ('WA15', 7);

INSERT INTO Drug (drugCode,maxDailyDosage)

VALUES ('AU02',2.5);

INSERT INTO Drug (drugCode,maxDailyDosage)

VALUES ('DR06', 5);

INSERT INTO Drug (drugCode,maxDailyDosage)

VALUES ('MA01', 4);

INSERT INTO Drug (drugCode,maxDailyDosage)

VALUES ('PN05',1.2);

;

END

-- inserting TreatmentGiven Block

BEGIN

INSERT INTO TreatmentGiven (caseNumber,dateStarted,dateStopped,dosage,frequency,frequencyInterval,drugCode)

VALUES (1 , '01/01/2022', '01/04/2022', 0.5, 3, 1, 'AU02');

INSERT INTO TreatmentGiven (caseNumber,dateStarted,dateStopped,dosage,frequency,frequencyInterval,drugCode)

VALUES (1 , '01/05/2022', '01/19/2022', 5, 1, 7, 'DR06');

INSERT INTO TreatmentGiven (caseNumber,dateStarted,dateStopped,dosage,frequency,frequencyInterval,drugCode)

VALUES (2 , '01/03/2022', '01/10/2022', 0.5, 3, 1, 'AU02');

INSERT INTO TreatmentGiven (caseNumber,dateStarted,dateStopped,dosage,frequency,frequencyInterval,drugCode)

VALUES (4 , '01/05/2022', '01/11/2022', 0.5, 3, 1, 'AU02');

INSERT INTO TreatmentGiven (caseNumber,dateStarted,dateStopped,dosage,frequency,frequencyInterval,drugCode)

VALUES (4 , '01/13/2022', '01/13/2022', 2, 2, 1, 'MA01');

INSERT INTO TreatmentGiven (caseNumber,dateStarted,dateStopped,dosage,frequency,frequencyInterval,drugCode)

VALUES (4 , '01/21/2022', '01/23/2022', 0.5, 3, 1, 'AU02');

INSERT INTO TreatmentGiven (caseNumber,dateStarted,dateStopped,dosage,frequency,frequencyInterval,drugCode)

VALUES (5 , '01/15/2022', '01/16/2022', 0.5, 3, 1, 'AU02');

INSERT INTO TreatmentGiven (caseNumber,dateStarted,dateStopped,dosage,frequency,frequencyInterval,drugCode)

VALUES (5 , '01/17/2022', '01/17/2022', 5, 1, 7, 'DR06');

INSERT INTO TreatmentGiven (caseNumber,dateStarted,dateStopped,dosage,frequency,frequencyInterval,drugCode)

VALUES (5 , '01/24/2022', '01/26/2022', 2, 2, 1, 'MA01');

INSERT INTO TreatmentGiven (caseNumber,dateStarted,dosage,frequency,frequencyInterval,drugCode)

VALUES (6 , '01/16/2022', 5, 1, 7, 'DR06');

INSERT INTO TreatmentGiven (caseNumber,dateStarted,dosage,frequency,frequencyInterval,drugCode)

VALUES (7 , '01/16/2022', 5, 1, 7, 'DR06');

INSERT INTO TreatmentGiven (caseNumber,dateStarted,dateStopped,dosage,frequency,frequencyInterval,drugCode)

VALUES (8 , '01/17/2022', '01/20/2022', 0.5, 3, 1, 'AU02');

INSERT INTO TreatmentGiven (caseNumber,dateStarted,dateStopped,dosage,frequency,frequencyInterval,drugCode)

VALUES (8 , '01/21/2022', '01/21/2022', 5, 2, 7, 'DR06');

INSERT INTO TreatmentGiven (caseNumber,dateStarted,dosage,frequency,frequencyInterval,drugCode)

VALUES (8 , '01/29/2022', 2, 1, 1, 'MA01');

INSERT INTO TreatmentGiven (caseNumber,dateStarted,dateStopped,dosage,frequency,frequencyInterval,drugCode)

VALUES (11, '01/18/2022', '01/25/2022', 0.5, 3, 1, 'AU02');

INSERT INTO TreatmentGiven (caseNumber,dateStarted,dateStopped,dosage,frequency,frequencyInterval,drugCode)

VALUES (11, '01/26/2022', '01/26/2022', 5, 1, 7, 'DR06');

INSERT INTO TreatmentGiven (caseNumber,dateStarted,dosage,frequency,frequencyInterval,drugCode)

VALUES (11, '02/03/2022', 2, 2, 1, 'MA01');

INSERT INTO TreatmentGiven (caseNumber,dateStarted,dosage,frequency,frequencyInterval,drugCode)

VALUES (11, '02/05/2022', 5, 1,14, 'PN05');

END

-- inserting TreatmentResponse Block

BEGIN