Mock Midterm Examination Answer Key

1

Faculty Of Computer Studies

M359 - Form A

Relational databases: theory and practice

Mock Midterm Examination Answer Key

This Mock exam is based on the Fall – 2011-2012 MTA

Date……..

(

2

) Hours

Time Allowed:

(11 )

Number of Exam Pages:

Instructions:

1. This exam consists of three parts:

Part I: 5 obligatory Multiple Choice

Questions (MCQ), each worth 2 marks for a total of 10 marks

Part II: 6 short essay questions of

which you can choose any 5. Each question is worth 8 marks for a total of 40 marks.

Part III: 6 problems of which you

can choose any 4. Each problem is worth 12.5 marks for a total of 50 marks.

2. Write all your answers in the answer booklet and not in this questions booklet.

2

Part I (MCQ)

Part I: answer ALL of the following 5 Multiple Choice Questions (MCQ), each worth 2

marks for a total of 10 marks.

1. Which of the following is true about unproductive maintenance?

a. entails making changes to a program due new requirements for that program

b. is due to data dependence

c. the problem improves when there are many application programs

d. the problem occurs mainly in relational database management systems rather than in the old file systems approach

2. Which of the following is not true about the client-multiserver system?

a. the user process needs to serially connect to multiple database servers b. suffers from a lack of a global view of the data

c. the writer of the user process does not need to know the names of the databases involved

d. each client can connect to only one server

3. Which of the following is true about a domain of discourse?

a. provides the context in which we understand the meaning of the data b. is also known as the universe of discourse

c. helps users identify the things discussed

d. all of the above

4. Which of the following will be considered a valid relational table? a. a table that has duplicate tuples

b. a table whose rows have multiple values in each field

c. a table with multiple data types appearing in a single column

d. a table with only a single field

5. Sometimes a tutor has to leave and is replaced by another tutor later on. Which option would you choose when deleting a tutor tuple in a TUTORS relation that is referenced by a foreign key in a COURSES relation?

a. delete the records of all courses taught by the tutor b. disallow the deletion

c. substitute a default value for the deleted tutor in all course records that refer to it

3 Part II (essay)

Part II: Answer ANY 5 of the following 6 short essay questions. Each question is

worth 8 marks for a total of 40 marks.

1. Briefly explain the difference between data and information.

Data is a term used to describe any recordable facts about the world whereas information is said to be meaningful data after interpretation.

2. Briefly explain the purpose of the three-schema architecture and its components. Illustrate your answer using a diagram.

The purpose of the three schema architecture is to separate user views of the data from its physical representation. It consists of the external schemas, the logical schema, and the storage schema, the logical to external schema mappings, and the logical to storage schema mapping.

3. Briefly explain why we need to add an assumption section to the Conceptual Data Model (CDM) and how are those assumptions used?

We need the assumptions section to record the working assumptions made during the analysis. The assumptions are used to clear up ambiguities in the requirements with the customer, if possible, and are left as documentation in case the ambiguities could not be cleared.

4. Briefly explain how can a 1:1 relationship in which both entity types have mandatory participation constraints be represented relationally?

By posting the primary key of one entity type to the other entity type and specifying a uniqueness constraint (an alternate key) on the posted key. We also need to specify a general constraint to ensure that the mandatory constraint is enforced.

4 It means that If a relation B has a foreign key that references a candidate key in

relation A, then every value of the foreign key appearing in B must equal an existing value of the referenced candidate key in A

6. Briefly explain what is meant by a lossless decomposition and how can it be guaranteed?

Lossless decomposition is the process of breaking down a relation into more than one relation such that their mutual join will yield the original relation exactly. It can be guaranteed by following Heath’s theorem which states that the following

decomposition steps will guarantee the lossless join property:

a. project the relation over all attributes that appear on either side of the a functional dependency

b. project the relation over all its attributes except for the right-hand side attributes of the functional dependency

5

Part III (Problems)

Part III: Answer ANY 4 of the following 6 problems. Each problem is worth 12.5

marks for a total of 50 marks.

1. Develop a Conceptual Data Model (CDM) for a supermarket database, including the E-R diagram, entity types, additional constraints and assumptions (limitations are not needed). The database will keep information about customers, orders, and products. For each customer we need to keep a unique customer ID, Name, telephone and

address. Each customer may make many orders. Each order contains a unique order ID, the customer who made the order, the order date, and a number of lines each having the details of a certain item line. For each item line, the product code, quantity ordered, and sale price per unit is recorded. For each product the unique product code, its name, description and quantity on hand (available quantity) are recorded.

Entity Types:

Customer (CustID, Name, Tel, Address)

Orders (OrderID, Date) // note: the CustID is implied by the E-R diagram

Line-Item (OrderID, LineNum, Quantity, Price) // note: the Product code is implied by the E-R diagram

Products (ProductCode, Name, Description, QOH) Additional Constraints:

c.1 Line item is a weak entity type dependent on Orders. Matching Orders and Line Items entities must have the same OrderID.

Assumptions:

a.1 We assume that each order must have exactly one customer.

Customer Orders

Products Line Items

Makes

Consists of

6 A B A B R R A B A B R R

a.2 We assume that each order must have at least one line item. a.3 We assume that each line item must have exactly one product. a.3 We assume that products can exist which have never been ordered.

2. For each of the following E-R diagrams answer the two following questions:

a. Give representative occurrence diagrams for each of the above E-R diagrams.

b. Convert the above E-R diagrams into equivalent E-R diagrams with no relationships having

many:many degree.

7 Entity types:

A(a1, a2) B(b1, b2)

4. for the following Family relations database:

Person (PersonID, Name, DateofBirth, Gender) Parents (PersonID, FatherID, MotherID)

Write relational algebra expression for each of the following queries:

a. Get the names and dates of birth of all males

project (select person where gender = 'M') over name, DateofBirth

b. Get the ID of each person with the ID of his/her father and the date of birth of the person

project (Parents join Person) over PersonID, FatherID, DateofBirth

5. Consider the following relation and functional dependencies

Registration (studentID, Coursecode, date, StudentName, Fees)

FD1: StudentID StudentName

FD2: StudentID, CourseCode, Date Fees

a. Determine the primary key of this relation

StudentID, CourseCode, Date

relation A a1: a1domain a2: a2domain primary key: a1 relation R a1: a1domain b1: b1domain

primary key a1, b1

foreign key a1 references A foreign key b1 references B alternate key b1

constraint (project A over a1) difference

(project R over a1)) is empty

8

b. To which normal form does this relation comply? Explain why?

1NF only because:

1. It has atomic attribute values and has non duplicates

2. It is not in 2NF because StudentName is not fully functionally dependent on the primary key (it only depends on StudentID, which is part of the key)

c. Normalize the above relation to the next higher normal form

Registration1 (StudentID, CourseCode, Date, Fees) Students (StudentID, StudentName)

6. Consider the following hospital ER diagram and relational headings:

The Hospital relational headings

Team (TeamCode, TelephoneNo, StaffNo) ConsistsOf (StaffNo, TeamCode)

Doctor (StaffNo, DoctorName, Position) Specialist (StaffNo, Specialism)

Patient (PatientId, PatientName, Gender, Height, Weight, StaffNo, WardNo) Ward (WardNo, WardName, NumberOfBeds)

Nurse (StaffNo, NurseName, WardNo) Supervises (StaffNo, Supervisor)

Treatment (StaffNo, PatientID, StartDate, Reason)

Prescription (PrescriptionNo, Quantity, DailyDosage, StaffNo, PatientId, StartDate, DrugCode) Drug (DrugCode, DrugName, Type, Price)

9 a. Write a SQL query to list prescription number, the staff number, and the patient id

together with a new column called total_dosage that is the product of quantity multiplied by the daily dosage.

SELECT PrescriptionNo, StaffNo, PatientID, Quantity*DailyDosage AS

total_dosage

FROM prescription

b. Write a SQL query to list the patient ID and patient name and staff number of the treating doctor for all male patients whose weight to height ratio is below 2.0 and whose name starts with the letter M

SELECT patient_id, patient_name, staff_no, height/weight FROM patient

WHERE height/weight < 3 AND gender ='M'

AND patient_name like 'M%'

c. Write a SQL query to find the names of all female patients who are treated by a doctor whose position is consultant, along with the name of the doctor and the name of the ward and in which the patient is being treated. Formulate your query as a restriction of a Cartesian product of the relations involved. Do not use the key word JOIN in your query.

SELECT patient_name, doctor_name, ward_name, position FROM doctor, patient, ward

WHERE doctor.staff_no = patient.staff_no AND patient.ward_no = ward.ward_no AND position = 'consultant'

AND gender = 'F'

d. Write a SQL query to get for every patient the patient id and the highest daily dosage this patient receives from any drug (i.e. regardless of the drug). Only include patients who take more than one drug.

SELECT patient_id, max(daily_dosage) from prescription GROUP BY patient_id

11

Reference Sheet Properties of Functional Dependencies:

Property 1: combining functional dependencies

If A B and A C, then A B, C

Property 2: extending determinants

If A

Property 3: transitivity

If A B and B C then A C

Property 4: augmentation

If A B, then A, C B, C Normal Forms:

 1NF: A relation is in first normal form (1NF) if and only if it has no duplicate tuples

and in each tuple, each value of every attribute is a single value.

 2NF: A relation is in second normal form (2NF) if and only if every non-primary

key attribute is fully functionally dependent on the primary key.

 3NF: A relation is in third normal form (3NF) if and only if it is in 2NF and no

nonprimary key attribute is transitively dependent on the primary key.

: Definition

An attribute A is transitively dependent (TD) on a set of attributes X in a relation R if there is a set of attributes Y such that all the following properties hold:

TD(i) X Y and Y A. TD(ii) It is not true that Y X.

TD(iii) A is not an attribute of either X or Y.

We included TD(ii) to rule out the situation where Y is an alternate key.

 BCNF: A relation is in Boyce–Codd normal form (BCNF) if and only if each

irreducible determinant of a non-trivial FD is a candidate key.

Definitions:

A determinant A in A B is irreducible if there is no proper subset S of A such that S B

A trivial FD is one in which the right hand side is a subset of the left hand side

Definition:

Weak Entity type:

11

: General forms of relational algebra expressions

select:

select <relation> where <selection condition>

project:

project <relation> over <attribute list>

combining select and project:

project (select <relation> where <selection condition>) over <attribute list>

alias:

<alias name> alias (<relational algebra expression>) join:

<relation1> join <relation2> rename:

<relation> rename (<old attribute1 name> as <new attribute1 name>, <old attribute2 name> as <new attribute2 name>, ... )

join with renaming:

<relation> join (<relation> rename (<old attribute1 name> as <new attribute1 name>, <old attribute2 name> as <new attribute2 name>, ... ))

divide:

divide <relation1> by <relation2>

union:

<relation1> union <relation2> intersection:

<relation1> intersection <relation2> difference:

<relation1> difference <relation2> times:

<relation1> times <relation2> general constraint: