COMP518 Database and Information Systems - Assignment 1

Learning outcomes:

1. Critical understanding of the nature of relational
2. Develop a critical understanding of querying a relational
3. Design large-scale database Assessment’s purpose:
4. Express queries to a relational database by using relational
5. Design an (Enhanced) Entity-Relationship

Failure in the assessment may be compensated for by higher marks in other components of the module. Marking of subquestions will be based on the marking descriptors of the University’s Code of Practice on Assessment.

Standard UoL penalty applies for late submission in accordance with the Universitys Code of Practice on Assessment. The last possible date of submission is at 17:00, on Tuesday, November 8th, because a feedback will be given afterwards.

Please be aware of the University guidelines on plagiarism and collusion. Please be sure that you have read the following page related to academic integrity before you submit your solutions:

https://canvas.liverpool.ac.uk/courses/62776/pages/assessment-related-information.

Total: 100 marks

Get Your Customize Task on any subject starting 10$/Page

Order Now Whatsapp Now

Question One (40%=40 marks)

Consider the following relational database schema in an insurance company.

• Driver(driverId, name, address)
• Car(carId, model, year)
• Accident(accidentNumber, date, location)
• Report(driverId, carId, accidentNumber,  damageAmount) Write the relational algebra expressions for the following

1. (10 marks) Find the drivers who have participated in at least one accident where the amount of the damage is at least Show the ids of those drivers.
2. (10 marks) Find the names of drivers, who do not have an
3. (10 marks) Find the drivers who have the same name as some other Show the names of the drivers.
4. (10 marks) Find the ids of the cars that participated in all accidents located in ’Liver- pool’.

Question Two (60% = 60 marks)

Problem Description

A regular task of Liverpool School of Medicine is to invent new medicines and study their clinical effects by performing tests. Suppose that you’re hired by the School of Medicine to design a database system which keeps track of information about these medical tests. After several meetings with some School staff members, you collect the following specifications for the database.

For every new medicine, the School follows several different medical procedures in order to test and study the medicine from different aspects. It is required by the School regulation that a valid test record must include information about the tester, the testee, the medical procedure, the medical lab where the test takes place, the test date, and the test result.

Each new medicine is given a unique identification number and a unique name, and the active ingredient should be noted. For the sake of simplicity, lets consider only four types of medicines: liquid, pills, drops and injections. Each type of medicine has their own information. For example, some information that may be included for the liquid is the ml per bottle and if sugar is added, for the pills is how many pills are in each box and if they are dissolvable, for the drops is the number of drops per bottle and their usage (eye, ear, nose), and for the injections is the ml per bottle and where to be injected (muscle, spinal cord etc). You may figure out something reasonable for each type of medicines by yourself.

Each medicine should be tested by at least 3 different medical procedures. Each medical procedure has a unique identification number and a unique name and involves one or several medicines. For each medical procedure, the dosage of each medicine that is used in the procedure should me included in the database. Moreover, each medical procedure may be performed more than once.

A tester is a well-trained staff member of the School, so they have a unique staff number, an office address, and an email. Let’s assume that School staff are also U.K. residents, so each of them has a unique National Insurance Number and a home address in the U.K.. A testee could simply be any U.K. resident, so they also have a unique National Insurance Number and a home address in the U.K.. The database should record the gender and the age of each testee because this information may be used in statistical analysis. Additionally, a testee may be allergic to several medicines and it is important that the database stores allergy information, in order to prevent hazardous applications of tests. Note that a tester may also serve as a testee if they want to.

Information of a medical lab includes the name, address, phone number of the lab, and a list of medical procedures that the lab is capable of conducting; it is possible that this list is empty. It should be noted that not every lab has facilities to conduct all the medical procedures, but for each medical procedure there is always a medical lab that is capable of conducting it.

Finally, the School has the following rule: each testee should participate in at least 10 tests.

Task

Based on the information above, draw an E-R or E-E-R diagram that models as many concepts and constraints as possible. You should use  the  notations  introduced  in  the  lectures and in the textbook.

Note

Use only binary relationships.