联系我们： 手动添加方式: 微信>添加朋友>企业微信联系人>13262280223 或者 QQ: 1483266981I
Page 1 of 7
IB9HP0
UNIVERSITY OF WARWICK
Paper Details
Paper Code: IB9HP0
Paper Title: DATA MANAGEMENT
Exam Period: January 2022
Exam Rubric
Time Allowed: 2 hours
Exam Type: Standard Examination
Calculators may not be used.
Instructions
You have two 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
I
Page 2 of 7
IB9HP0
Part A (20 Marks)
The following logical schema is provided (primary keys underlined; foreign keys are
provided in italics):
flights(flightno, dep_airport_code, dest_airport_code, airline_id, aircraft_id)
airports(aiport_code, longitude, latitude,iata_code)
airlines(airline_id,name,callsign)
aircrafts(aircraft_id,make,engine_type)
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 columns dep_airport_code and dest_airport_code
are mapped to the airport_code field on the airports table as foreign keys. How does
your ER diagram look after that
(5 Marks)
c. You are given the information that a new field is provided by the merge of the
dep_airport_code and dest_airport_code. After that the fields dep_airport_code and
dest_airport_code are removed from the database. How does the logical schema look
after that information Does the airports table need any modification
(5 Marks)
(Continued…)
I
Page 3 of 7
IB9HP0
Part B (15 Marks)
An online store sells a range of products. Each product has a unique identifier called SKU and
has a price attached to it as well as a name, description, supplier and reviews from past
customers. A product has a supplier identified by a supplier number, name and address. A
customer can register for an account on the online store using an email as a username, a
password and the type of registration account (individual or corporate). The customer can
purchase the product by adding it in a shopping basket. After the purchase the customer can
leave a review for this particular product by adding a star rating as well as text for the positive
and the negative aspects of the product.
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
relationships to the logical design equivalent.
(3 Marks)
c. Provide the SQL query to generate the average star rating for all product SKUs in the
catalog. How would you treat products that have no reviews The final result should
include product name as well as price.
(4 Marks)
(Continued…)
I
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 total_price total_price_with_shipping
1231 Κ 40 50
1232 L 50 51
1233 M 34 50
1234 F 32 51
1235 A 43 52
1236 F 42 63
… … …. …
Provide the following SQL statements
a. Calculate the average shipping cost for each category (assuming that last column
displays the total_price 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 product category. Keep only those where the difference is
greater than 20.
(3 Marks)
c. How many orders have been filed in total
(3 Marks)
d. How many orders have a shipping price that is more than 25% of the original price
(2 Marks)
(Continued…)
I
Page 5 of 7
IB9HP0
Part D (25 Marks)
The following table (expenses) is given
expenseID name covers cost authorized
0001 John Smith transport,
subsistence,
Airfares
23,000 USD, HR
0002 Maria Schmidt Airfares 14,000 USD
plus 4,000
ticket change
IT
0003 Jason Cleeland equipment 42,000 GBP FINANCE
0004 Maria Josepha
Palangos
meals 500 USD plus
132 tips
IT
0005 Veronica Chloe
Jones
NULL 22,000 USD HR
a. Explain in simple terms the differences between the first, second and third normal form.
(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.
(10 Marks)
c. Provide the logical schema of the new table or tables.
(5 Marks)
d. Answer the following questions using the corresponding SQL statements
i. How many employees have claimed airfares authorized by IT
(2 Marks)
ii. What is the total cost (for any expense) that the company paid in USD
(2 Marks)
iii.What is the total expenses that the company paid per expense type per authorized
department
(1 Mark)
(Continued…)
I
Page 6 of 7
IB9HP0
Part E (15 Marks)
The following table (SONGS) is provided where the values represent the rank of this song on a given
year.
song 1991 1992 1993 1982 1995 2010 1997 1998 1999
A 30 87 93 10 20 57 57 29 39
B 82 19 71 62 27 80 39 4 53
C 90 6 52 45 79 28 23 58 11
D 52 48 56 1 41 63 69 30 59
E 10 99 64 33 54 15 34 64 21
F 20 24 5 28 32 1 95 2 56
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 all columns apart from song are values of the attribute
year from the SONGS 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 rank for each song. Can this query be executed If so, explain
why. Is there an alternative way without transformation
(5 Marks)
(Continued…)
I
Page 7 of 7
IB9HP0
Part F (15 Marks)
The following dplyr pipe sequence is given
1 cars %>%
2 group_by(color,brand_id) %>%
3
4
summarise(mprice = mean(price,na.rm=T) %>%
filter(color==”BLUE”) %>%
7 right_join(brands) %>%
8 select(brand_name, mprice)
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 if line 7 is modified so that right_join
becomes left_join.
(5 Marks)
End of Paper