Data Model for Variant Classification and COVID-19 Testing

Variant Classification

Question 1 (20 marks)

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 

(i) entities, with entities name, relevant attributes and identifier,  (10 marks)

(ii) relationships with maximum and minimum cardinalities and relationship name.  (10 marks)

Question 2 (25 marks)

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.

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.  (8 marks) 

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

(c) (i) For each MVD in answer to part a), state whether it is subsumed. 

(ii) 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).  (8 marks) 

(d) Assemble your BCNF and 4NF tables and constraints clearly by giving each table and the referential integrity constraint(s), if any, in the forms described below:

Tables in this form: 

TABLE-NAME (col-name1, col-name2, ..., col-namen) 

a. indicate (PK) against the primary key and 
b. indicate (FK) against the foreign keys, if any. 

Referential integrity constraint(s), if any, together with the child table in this form: 

TABLE-NAMEx.col-namex1 ...col-namexn must exist in 
TABLE-NAMEy.col-namey1...col-nameyn.  (6 marks)