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

Page 1 of 7
IB9HP0
UNIVERSITY OF WARWICK
Paper Details
Paper Code: IB9HP0_A
Paper Title: DATA MANAGEMENT
Exam Period: April 2023
Exam Rubric
Time Allowed: 2 Hours
Exam Type: Standard Examination
Approved Calculators: Not Permitted
Instructions
You have 2 hours exactly to answer the following questions. No notes are allowed at any time. The
exam is a straight attempt, meaning no penalty is given for wrong answers.
The exam is structured in six parts. 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
Page 2 of 7
IB9HP0
Part A (20 Marks)
The following logical schema is provided for a social media platform (primary keys
underlined; foreign keys are provided in italics):
users(user_id, username, email, password, date_of_birth, location)
posts(post_id, user_id, post_text,date_time)
comments(comment_id,post_id,user_id, comment_text, date_time)
likes(like_id,post_id,user_id)
a. Provide the ER diagram (entities, attributes, relationships as well as the cardinality of
the relationships) based on the definition of the given keys provided on the logical
schema.
(10 Marks)
b. You are given the information that the product manager would like to add the ability
for the user to quote a post (a post that references another post). How does your ER
diagram look after that
(5 Marks)
c. You are given the information that an analytics consultant suggested that there needs
to be information in the database tracking as to whether a post or a comment was
deleted. How would you alter the logical schema to capture that information Do you
need to create new tables
(5 Marks)
(Continued…)
Page 3 of 7
IB9HP0
Part B (15 Marks)
A car rental company rents out cars to customers, with each car having a unique identifier
called a VIN (Vehicle Identification Number) and a rental rate attached to it. Each car also has
a make, model, year, and mileage. For each booking there can be a discount rate. The
company has employees working in multiple rental locations, with each location having an
address, phone number, and manager. A customer can rent a car by making a reservation,
specifying the rental location, rental period, and car type. After the rental period, the
customer can return the car to the rental location. The customer can also leave a review of
the rental experience by adding a star rating and text for the positive and negative aspects.
a. Provide the E-R diagram depicting the entities and the relationships provided in the
above scenario using relationship sets.
(8 Marks)
b. Transfer the E-R diagram to a logical design. You need to explain how you translate the
relationship to the logical design.
(3 Marks)
c. Provide the SQL query to generate a list of all rental agreements including customer
name, car make and model, rental start and end dates as well as total cost. How would
you incorporate the discount rate
(4 Marks)
(Continued…)
Page 4 of 7
IB9HP0
Part C (10 Marks)
The following table (TBL1) is given. The field order number is the primary key (highlighted in
bold).
Order number category brand Total price with shipping
1231 Κ VW 50
1232 L AUDI 51
1233 M SKODA 50
1234 K PORCHE 51
1235 L MERCEDES 52
1236 M PORCHE 63
… … …. …
Provide the following SQL statements
a. Calculate the minimum average shipping cost by each category and brand (assuming
that last column displays the total cost with shipping) for those orders where total
price is above 50.
(2 Marks)
b. Calculate the difference between the most expensive and least expensive order for
TBL1 including shipping by brand. Keep only those where the difference is greater
than 20.
(3 Marks)
c. How many orders have been filed in total only for brands AUDI, VW and SKODA
(3 Marks)
d. How many orders have a shipping price that is more than the average price
(2 Marks)
(Continued…)
Page 5 of 7
IB9HP0
Part D (25 Marks)
The following table (expenses) is given
Order_number Customer _name items_ordered total_price payment_method
0001 John Smith shirt, pants,
shoes
2300 USD, Card
0002 Emma Johnson Jackets, socks 140 USD
plus 4,000
shipping
Paypal
0003 M. Lee Socks 4200 GBP Card
0004 Kim, Sarah Gloves 500 USD plus
132 shipping
Card rejected,
used Paypal
0005 Chen, John Various parts
including gloves
and socks.
2200 USD Pending
a. Explain the concept of database normalization and discuss the benefits of normalization
in terms of database design and maintenance.
(5 Marks)
b. 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)
c. Answer the following questions using the corresponding SQL statements
i. How many customers have bought socks with Paypal
(2 Marks)
ii. What is the total revenue (by item) that the company received using Paypal
(2 Marks)
iii. What is the total revenue that the company got per item per payment method
(1 Mark)
(Continued…)
Page 6 of 7
IB9HP0
Part E (15 Marks)
The following table (households) is provided where the values represent the number of households
in a given year by income band.
Household_type Year 0-750 751-2000 2001-3500 3500-
A 2019 87 93 10 20
B 2019 19 71 62 27
C 2019 6 52 45 79
A 2021 48 56 1 41
B 2021 99 64 33 54
C 2021 24 5 28 32
a. Is HOUSEHOLDS 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 all columns apart from household type and year are
values of the attribute Covid cases from the HOUSEHOLDS entity. Name and describe the
operation that you will perform and what will the new table look like. Do you need to
define any new keys
(5 Marks)
c. After transformation provide the SQL statement to calculate the difference between the
minimum and maximum cases for each household type and year. Can this query be
executed If so, explain why. Is there an alternative way without transformation
(5 Marks)
(Continued…)
IB9HP0
Part F (15 Marks)
The following dplyr pipe sequence is given
1 flights %>%
2 filter(month == 1, day == 1) %>%
3 select(origin, dest, dep_delay, arr_delay) %>%
4 group_by(origin, dest) %>%
5 summarise(avg_dep_delay = mean(dep_delay, na.rm = TRUE),
6 avg_arr_delay = mean(arr_delay, na.rm = TRUE))%>%
7 filter(avg_dep_delay > 0, avg_arr_delay > 0) %>%
8 arrange(desc(avg_dep_delay))
a. Provide the logical design equivalence from the dplyr pipe sequence.
(5 Marks)
b. Using the above pipe sequence. provide the SQL equivalent for the whole pipe
sequence. Explain your thought process in doing so.
(5 Marks)
c. Explain what will happen to the SQL output and the ordering of the records if line 7 is
modified as follows:
filter(avg_dep_delay > 0 | avg_arr_delay > 0)
Will the ordering of the records be the same
(5 Marks)
End of Paper
Page 7 of 7