Question 1

Database (DB) History and Design

(a)  DB history and applications:

(i)  List five of the main milestones in DB history, each milestone associated with the respective decade.

(ii)  List five of the main DB applications.  For the most traditional DB application,

explain how this impacted on DB technology. [10 marks — basic]

(b)  DBMS architecture and abstraction: Explain the layered architecture and the concept of data abstraction. Refer to the three schemas in data abstraction and relate them to the architecture of a DB system environment. [8 marks — medium]

(c)  Referential integrity constraint:

(i)  Explain the constraint. In your explanation, refer to keys (primary keys and foreign keys). Show an example with two tables, and the example shall illustrate when the constraint is satisfied, and when not.

(ii)  Show a DB operation that can lead to a problem, i.e. the referential integrity constraint is not satisfied.

(iii)  If a problem occurs, then the DBMS has two options: reject the operation or apply a compensation strategy. Explain briefly the three strategies for compensation. [7 marks — medium]

Question 2

SQL

Given the following schema of a database:

#  Database  Schema :

#  This   i s  a  schema  f o r   s t o r i n g   data  about  wines  and  wine  bars .

wine  maker ( MakerId ,  Name,   Region ) ;

#  Primary  Key :   ( MakerId )

#  Examples  f o r  Name:   Krug ,   Pommery ,   Chateau  Mouton   Rothschild ,   . . .

#  Examples  f o r   Region :  Champagne ,   Languedoc ,   Mosel ,   . . .

wine ( WineId ,   MakerId ,  Year ,   Colour ) ;

#  Primary  Key :   ( WineId )

#  MakerId :   r e f e r s   t o  wine  maker . MakerId

g r a p e  v a r i e t i e s ( WineId ,   GrapeVariety ,   Percentage )

#  Primary  Key :   ( WineId )

#  Note :  a  wine  can  c o n t a i n  more  than  one  grape   v a r i e t y               #  Percentage   i n d i c a t e s  how  much  o f   the   v a r i e t y   i s   i n   the  wine

wine  bar ( BarId ,  Name,   City ,   Postcode ) ;

#  Primary  Key :   ( BarId )

w i n e  b a r  s t o c k ( BarId ,   WineId ,   NumberOfBottles ,   U n i t P r i c e ,   Temperature ) ; #  Primary  Key :   ( BarId ,   WineId )

#  WineId :   r e f e r s   t o  wine . WineId

#  BarId :   r e f e r s   t o   wine  bar . BarId

Provide SQL expressions for the text queries (parts (a) to (d)) and relational algebra query (part (e)). For all queries, make the join conditions an explicit part of the WHERE clause. Consider using views (or nested queries) where the query formulation becomes complex.

(a)  Show the names of wine bars that are in the city of Moscow and stock wines from the region of Stellenbosch. [5 marks — basic]

(b)  Show a list (table) where each tuple in the list shows the name of the wine bar, the minimum unit price and the maximum unit price for champagnes it sells from wine maker ’Pommery’ .

For example, for a row of the list might be:

’Covent Garden Wine Bar’ l 35 l 270

(c)  Show the name of wine bars that contain wines from the region Bordeaux costing between 30 and 50 and wines from the region Rioja costing between 20 and 40. [5 marks — medium]

(d)  Show all wine bars (their name, city, and postcode) that only sell wine containing at least two grape varieties. [5 marks — advanced]

(e)  Given the following relational algebra (RA) expressions:

expensive cph wines :=

7(8(wine bar, BarId, wine bar stock, BarId), City = ”Copenhagen”) n 7(8(wine bar, BarId, wine bar stock, BarId), UnitPrice = 1000)

wine name region :=

n(8(wine maker, MakerId, wine, MakerId), (WineId, Name, Region))

expensive wine name region :=

n(8(expensive cph wines,WineId, wine name region, WineId), (Name,Region))

Provide the SQL expressions for the RA expressions. [5 marks — advanced]

Question 3

Database Design and Entity-Relationship Model (ERM)

Scenario: A luxury-oriented wellness hotel wants to improve their data management.

The hotel offers wellness packages (massage, sauna, swimming pool) that are sold to customers. There are two main requirements: The new system shall help with the planning of staff for the wellness sessions, and, importantly, the new system shall help to get some positive reviews from customers. A review is either negative or positive.

For planning purpose, the hotel aims at selling the wellness package (WP) when the customer books a room. Customers can also upgrade at check-in, but then, the WP is 20% more expensive than if the customer had booked the WP together with the room.

It is likely that the customer will not be able to book at check-in. This leads to customers being disappointed, even though the customer knew from the web-site that an early booking is required. This led to negative reviews on review sites such as booking.com.

The hotel wants the new DB system to improve this situation. There should be a query facility that shows customers who wanted to book a WP at check-in, and then, when being told that it was required to book in advance, submitted a negative review. The hotel pays a company for a service to inject a response into the review system. There are several responses that shall be automatically launched.

For a customer with a negative but still factual review, the response is: “Thank you for your review. We apologise for having disappointed you. There are excellent offers on our web site.” For a customer leaving a very negative review: “We regret that your stay was not as expected.” Importantly, there shall be also a response to positive reviews: “Thank you very much for your supportive review.”

In summary, the new DB system shall support the usual booking scenario, while capturing the requirements regarding wellness packages and customer reviews.

(a)   (i)  Describe the DB design steps (independent from the scenario). For each step,

explain briefly which methodology/concepts are related to the step.

(ii)  Describe in general (independent from the scenario) the steps of the mapping

process when mapping the conceptual to a logical model. Refer to steps such as key migration and normalisation. Explain the purpose of normalisation; there is no need to describe the normal forms. [5 marks — medium]

(b)   (i)  Specify some design assumptions (at least three, not more than five) that justify/- explain your design.

(ii)  Provide an ERM (ER Model) for the scenario above.

Describe the model in textual form (as practised in the course work).  An ER diagram is not required.

(iii)  Map your ER model to the relational model.  Explain each mapping step.  For

each foreign key (FK), clearly show to which primary key (PK) the FK refers to. You do not need to provide a CREATE TABLE command, but you need to specify the respective foreign key information. Also, where applies, explain the type or domain of attributes. [12 marks — medium]

(c)  Enhanced entity-relationship model:

(i)  Describe briefly the main concepts of the E-ERM.

(ii)  Explain briefly the two main relational options for mapping concepts of the E-ERM

to the relational model.

(iii)  Apply the E-ERM to the wellness hotel scenario. Show where and how a concept of the E-ERM could be applied. Consider participation constraints where useful. [8 marks — medium]

(b)  DB Admin:

The SQL Command ”Grant” is used by the DB admin to assign privileges to users.

Show the structure of the command for assigning system privileges and privileges on objects.

Provide four examples (two for system privileges, two for privileges on objects). [6 marks — medium]

(d)  Datawarehouse (DWH) technology:

(i)  Explain the three main schemas applied in DWH design.

(ii)  The ERM is based on three main concepts: entities, relationships and attributes.

What are the main concepts of DWH design, and how are those related to the concepts of the ERM? [7 marks — medium]