Compare and contrast the three phases of database design

ADDITIONAL EXERCISES

Part2

Tutorial 6: Q1 (also explain the typical artifacts at the end of each phase),Q7-11 (SQL and screen shots)

1- Compare and contrast the three phases of database design.

7-Write an SQL select statement to list all double or family rooms with a price less than $255 per night in ascending order of price. If you have not successfully constructed the full tablesHotel,Room,GuestandBookingand fill the relevant date as required in the previous practical, you could replace your table construction and data via thisSQL script.

Students may try out these two simplified queries first if they wish:

o List all rooms with a price less than $255 per night.

o List all double or family rooms with a price less than $255 per night.

8- What is the possible maximum total revenue per night from all double rooms?
NOTE: We recall that aggregate functions such asCOUNT(),SUM()are applied to each group if theGROUP BYclause is present, or applied to the whole set of records ifGROUP BYclause is absent.

9- List the price and type of all rooms at the Marriott hotel in London.
HINT: UseJOINfor tablesHotelandRoom.

10-List the number of rooms in each hotel.
HINT: Use aggregate functionCOUNT(), and also make use of theGROUP BYclause.

11-Write an SQL query to update the price of all rooms by 5%.
NOTE: We recall from last week’s practical that the syntax for usingUPDATEcan be found

a. http://www.w3schools.com/sql/sql_update.asp

b. http://www.techonthenet.com/sql/update.php

Tutorial 7: Q1, Q6, Q8-Q9 (with screenshots for the output results of the SQL).

1- Describe the purpose of normalizing data.

6-Readprac7script1.sqland draw alogicaldata model to illustrate the design. This file can be found under the prac link on vUWS. It may be helpful to refer to the sample data used inprac7script2.sql.

8- Write an SQL query to show the average customer balance for each area code.

9- Write an SQL query to show all customers and the products they have purchased. The result should be the same as the following:

CUS_LNAME CUS_FNAME PRODUCT

————— ————— ———-

Hazal Ali 11QER/31

Hazal Ali BRT-345

Wang Phan BRT-345

Wang Phan PB101

Wang Phan BRT-345

Wang Phan LZQ202

Wang Phan PB101

Tutorial 8: Q4

4- Use the functional dependency notation discussed in lectures (eg X → B) to summarize the functional dependencies one would expect in a typical business application for the following attributes (we note that each staff member may be allocated different amount of time to a different project):

Then normalise the relation into the Third Normal Form (3NF) according to the derived functional dependencies.

Tutorial 9: Q1, Q4

1- To be in 2NF a relation must first be in 1NF and then every non-primary-key attribute must be fully functionally dependent on the primary key. What are the characteristics of the relation below that prevent it from being considered as meeting the requirements of Second Normal Form?

The primary key of the relation is a composite key made up of attributes AA, BB ,CC and DD

4- Normalize the following dependency diagram into second normal form (2NF) and third normal form (3NF). Provide reasoning and show all your work. Draw the global relation diagram for the 3NF only.

Tutorial 10: Q2, Q5

2- The consistency and reliability aspects of transactions are due to the “ACIDity” properties of transactions. Discuss each of these properties and how they relate to the concurrency control and recovery mechanisms. Give examples to illustrate your answers.

For the schedule of transactions T₁and T₂depicted below, what kind of problem is there that is caused by concurrency?