IT Application: Database Analysis and Design Drug Trial Laboratory A research laboratory is running several drug trials on healthy volunteers to check whether drugs have side effects. Each drug has a...

IT Application: Database Analysis and Design

Drug Trial Laboratory

A research laboratory is running several drug trials on healthy volunteers to check whether drugs have side effects. Each drug has a unique name. Each trial involves exactly one drug and several volunteers (who take the drug and report if they had any side effects). For each volunteer in each trial it needs to be recorded whether the volunteer had any side effects, and if yes, what those side effects were (there could be several side effects experienced by the same person, for example headache, dry mouth, and fever). It is important that side-effects are described using some standard terminology, so that the laboratory can report what proportion of volunteers had the same side effects. For example, the researchers may tell you that headache should always be recorded as `headache' and not sometimes as `pain in the head' and sometimes as `sore head'. There is however no fixed pre-defined set of possible side effects, because new effects can always be discovered (for example the drug may turn people a bright green colour). For simplicity, assume that each volunteer takes part in at most one drug trial. Data stored about volunteers is their National Insurance number, name, age, gender, address and telephone number.

• 
  

  
  Analyse
  the processing requirement of Laboratory using data flow diagrams up to level 1.

  
  




• 
  

  
  Draw
  an initial ERD diagram to meet the requirements of scenario, clearly state the primary keys, foreign keys and any other appropriate attributes.

  
  


• 
  

  
  Create
  data dictionary identifying the data type and all fields

  
  


• 
  

  
  Normalise
  your tables to 3^rd
  Normal form explaining in details, each stage

  
  


• 
  

  
  Draw
  final ERD to meet all requirements