联系我们： 手动添加方式: 微信>添加朋友>企业微信联系人>13262280223 或者 QQ: 1483266981

IB9HP0
Page 1 of 8
University of Warwick
January 2021
Data Management
Instructions
This is an online examination
Time allowed: 2 hours

You have two hours exactly to answer the following questions. The exam is a straight attempt, meaning
no penalty is given for wrong answers.
The exam is structured in six parts which contain an uneven number of questions. Each part is weighted
as follows:
Part A 20 Marks
Part B 15 Marks
Part C 10 Marks
Part D 25 Marks
Part E 15 Marks
Part F 15 Marks
To achieve full marks for each part you need to provide a full and justified answer to each question. For
questions related with coding, you need to explain the reasoning and the intuition behind each answer.
Answers which do not have full justification, will not score the full marks.
IB9HP0
Page 2 of 8
Part A (20 Marks)
The following logical schema is provided (primary keys underlined, foreign keys in italics):
TB1(col111, col112, col113, col114)
TB2(col211, col111, col222, col223, col224, col225)
TB3(col311, col312, col313, col314)
a. Provide the ER diagram (entities, attributes, relationships as well as the cardinality of the
relationships) based on the definition of primary and foreign keys provided on the logical
schema.
(10 Marks)
b. You are given the information that columns col311 and col111 are the same. How does your
ER diagram look after that
(5 Marks)
c. You are given the information that col223 is multivalued and contains at most three values (v1,
v2, v3) separated by comma. How does the logical schema look after that information
(5 Marks)
(Continued…)
IB9HP0
Page 3 of 8
Part B (15 Marks)
An airline flight is recognised by a unique flight number and involves registering the departure
airport, the destination airport, the make of the aircraft as well as the date that the flight is
scheduled. A passenger is booked to a flight using a unique booking reference. Each passenger is
identified by a passenger code, first name, last name, gender and food preference. Each booking
contains information about the date of the booking, the seat class, the price of the booking as well
as the channel through which the booking was made. Each channel is identified by a unique code,
the contact person details (first name, last name), and carries its own (fixed) fee percentage. The
channel facilitates the booking.
a. Provide the E-R diagram depicting the entities and the relationships provided in the above
scenario. Using relationship sets, explain how you determined the cardinality of these relations.
(10 Marks)
b. Transfer the E-R diagram to a logical design. You need to explain how you transferred each
relationship to the logical design.
(5 Marks)
(Continued…)
IB9HP0
Page 4 of 8
Part C (10 Marks)
The following table (TBL1) is given. cola is the primary key (highlighted in bold).
cola colb colc cold
1231 A 40 50
1232 B 50 51
1233 C 34 50
1234 D 32 51
1235 E 43 52
1236 E 42 53
… … …. …
Provide the following SQL statements
a. Calculate the average for columns colc and the sum for cold
(2 Marks)
b. Calculate the total number of records for each instance of the colb
(2 Marks)
c. How many unique records of colb exist
(1 Mark)
d. How many records exist on the table where colb is either A or E and colc is less than or
equal to 100 and cold is greater than colc
(5 Marks)
(Continued…)
IB9HP0
Page 5 of 8
Part D (25 Marks)
The following table (employees) is given:
employeeID name address salary job_cover
0001 John Smith 23 Rampant
Street, NR5 2FD,
Norwich
23,000 USD,
plus 4,000
pension
frontdesk,
backoffice,
auxiliary
0002 Maria Verena
Jones
28 Duke Street,
CV5 2FD,
Coventry
14,000 USD frontdesk
0003 Jason Cleeland 14 Farm House,
CV3 2NG,
Coventry
42,000 EUR backoffice
0004 Maria Josepha
Palangos
23 St Stephens
Street, NR1 3SA,
Norwich
5,000 USD plus
1,332 pension
frontdesk
0005 Veronica Chloe
Jones
NULL 22,000 USD plus
1,450 pension
auxiliary
Where name, address and salary are composite attributes and functional dependencies of
employeeID.
a. Normalize the above table to another table (or tables) that correspond to the highest possible
normal form. Justify your answer, using functional dependencies.
(15 Marks)
b. Provide the logical schema of the new table or tables.
(5 Marks)
c. Answer the following questions using the corresponding SQL statements:
i. How many employees are stationed in Coventry
(2 Marks)
(Continued…)
IB9HP0
Page 6 of 8
Part D Continued
ii. What is the total salary cost (including pension) that the company paid in USD
(2 Marks)
iii.What is the total salary cost (excluding pension) that the company paid per job_cover
function
(1 Mark)
(Continued…)
IB9HP0
Page 7 of 8
Part E (15 Marks)
The following table (TABLE) is provided:
col111 col112 col113
A peek 123
A show 111
B peek 111
B show 112
C peek 10
D show 11
a. Is TABLE normalized if so, in which normal form Does it need to be transformed Explain
your answer using functional dependencies.
(5 Marks)
b. You are given the information that the values peek and show are attributes of the TABLE. Name
and describe the operation that you will perform and what the new table will look like.
(5 Marks)
c. Provide the SQL statement to calculate the difference between peek and show. Can this query
be executed If so, explain why. Is there an alternative way without transformation
(5 Marks)
(Continued…)
IB9HP0
Page 8 of 8
Part F (15 Marks)
The following dplyr pipe sequence is given:
1 dataset1 %>%
2 select(col1,col2,col3) %>%
3 filter(col1>3) %>%
4 group_by(col1) %>%
5 summarise(totaln = sum(col2)) %>%
6 left_join(dataset2) %>%
7 select(va3, totaln)
a. Provide the logical design equivalence from the dplyr pipe sequence.
(5 Marks)
b. Using the above pipe sequence provide the SQL equivalent for up to line 3.
(5 Marks)
c. Provide the SQL equivalent of the complete pipe sequence (line 1 to line 7).
(5 Marks)
End of Paper