Assignment 2

Q1: The description below and the ER diagram in the next page capture information about a vehicle manufacturer company, such as Tesla, Toyota, Honda, Volkswagen, etc.

A customer may approach the vehicle manufacturer primarily for two reasons.

(1) The first and perhaps most apparent reason would be to acquire a new vehicle.

(2) For those who have already purchased vehicles, they might return to the manufacturer

for regular maintenance or service upgrades.

Each client is expected to establish a single application account for managing their vehicles

through a mobile app. We may assume that each client may maintain a maximum of one

account, even though they may possess multiple vehicles.

With every transaction, as previously stated, the customer may either acquire a new vehicle

or initiate routine maintenance or a service upgrade. Routine maintenance or service upgrades

-    Every new vehicle will be added to the inventory at varying costs, as different seasons may present unique promotional opportunities.

-    A regular maintenance and/or service upgrade usually involve multiple vehicle’s parts. (i.e. replacing the old vehicle parts).

Please convert the following ER-diagram to tables and provide the answer in schema format. E.g., a relation R with attributes a1, a2 where a!  is the primary key is written as R(a1, a! ).

Q2:  In this ques1on, we would like to u1lize a rela1onal database to manage the details of a=endees in academic conferences. Each conference is iden1fied by a unique conference    code, accompanied by a conference name and a specific commencement date. (It can be

assumed that  only the commencement date need to be recorded)

In an academic conference, we have a list of par1cipants where each par1cipant can be

either students or academic staffs from various affilia1ons. These affilia1ons are denoted by

its unique codes, such as ‘HKU’ for University of Hong Kong, ‘HKLU’ for Lingnan University,

and so forth. Each par1cipant receives an invita1on via email, which contains a unique

invita1on code. Given that a dinner is part of the conference i1nerary, every par1cipant is

required to pre-select their meal preferences, which includes a main dish and dessert, prior

to their a=endance at the conference to enable smooth arrangements. Furthermore, to

keep a track of actual a=endance, we intend to record the number of par1cipants who were

present at the conference.

(i)   Please list out all reasonable func1onal dependencies based on the descrip1on above

and the a=ributes in the table below.

(ii)  Please decomposite the table below into BCNF.

Participants(participant_id, name, address, title, city,

address, affiliation_code, affiliation_name, conference_code,

conference_name, conference_date, invitation_code,

is_attended, dinner_id, dinner_date, dinner_description,

dish_id, dish_description, dessert_id, dessert_description)

Q3)

You are given the SQL files for all tables above. Please download the files from Moodle, and import the data into the MSSQL and answer the following ques1ons.

1) Write a query that list out the names and the revenues of the city that begin with ‘a’ and end with ‘a’. Please order the results by the revenues in descending order.

2) Write a query that list out the 1tles of the films that did not rent by any customers.

3) Write a query that list out the names of the customers who paid more rental fee than some customer in the same city.

4) Write a query that find out which films has been rented for more than five 1mes in the  same country. Please list out the 1tle of the films, the rental frequency and the country of the rental.

5) Write a query that list out the 1tles of films that has rented by someone by none of them is from China. (Note that: you need to make use of exists/not exists for this ques6on)