Database Storage & Design Chapter 7 Homework Accounts Select Access These Views Now Revoke

Databas – Part III

Chapter 7 SQL: Data Definition

Review Questions

7.1 Describe the eight base data types in SQL.

7.2 Discuss the functionality and importance of the Integrity Enhancement Feature (IEF).

Required data: NOT NULL of CREATE/ALTER TABLE.

Domain constraint: CHECK clause of CREATE/ALTER TABLE and CREATE

7.3 Discuss each of the clauses of the CREATE TABLE statement.

The clauses are (see Section 7.3.2):

7.4 Discuss the advantages and disadvantages of views.

See Section 7.4.7.

7.5 Describe how the process of view resolution works.

7.6 What restrictions are necessary to ensure that a view is updatable?

33

7.7 What is a materialized view and what are the advantages of a maintaining a materialized view

rather than using the view resolution process?

See Section 7.4.8.

7.8 Describe the difference between discretionary and mandatory access control. What type of

control mechanism does SQL support.

7.9 Discuss how the Access Control mechanism of SQL works.

Databas – Part III

34

Exercises

Answer the following questions using the relational schema from the Exercises at the end of Chapter 4.

7.10 Create the Hotel table using the integrity enhancement features of SQL.

CREATE DOMAIN HotelNumber AS CHAR(4);

7.11 Now create the Room, Booking, and Guest tables using the integrity enhancement features of SQL

with the following constraints:

(a) Type must be one of Single, Double, or Family.

(b) Price must be between £10 and £100.

(c) roomNo must be between 1 and 100.

(d)

(e) The same room cannot be double booked.

(f) The same guest cannot have overlapping bookings.

CREATE DOMAIN RoomType AS CHAR(1)

CREATE TABLE Room(

roomNo RoomNumber NOT NULL,

Databas – Part III

35

CREATE DOMAIN GuestNumbers AS GuestNumber

CREATE TABLE Booking(

hotelNo HotelNumbers NOT NULL,

CONSTRAINT RoomBooked

CHECK (NOT EXISTS ( SELECT *

CONSTRAINT GuestBooked

7.12 Create a separate table with the same structure as the Booking table to hold archive records.

Using the INSERT statement, copy the records from the Booking table to the archive table

relating to bookings before 1st January 2013. Delete all bookings before 1st January 2013 from

the Booking table.

CREATE TABLE BookingOld( hotelNo CHAR(4) NOT NULL,

Databas – Part III

36

7.13 Create a view containing the hotel name and the names of the guests staying at the hotel.

CREATE VIEW HotelData(hotelName, guestName)

7.14 Create a view containing the account for each guest at the Grosvenor Hotel.

CREATE VIEW BookingOutToday

7.15 Give the users Manager and Deputy full access to these views, with the privilege to pass the

access on to other users.

7.16 Give the user Accounts SELECT access to these views. Now revoke the access from this user.

GRANT SELECT ON HotelData TO Accounts;

7.17 Consider the following view defined on the Hotel schema:

CREATE VIEW HotelBookingCount (hotelNo, bookingCount)

AS SELECT h.hotelNo, COUNT(*)

FROM Hotel h, Room r, Booking b

Databas – Part III

37

WHERE h.hotelNo = r.hotelNo AND r.roomNo b.roomNo

GROUP BY h.hotelNo;

For each of the following queries, state whether the query is valid and for the valid ones

should how each of the queries would be mapped onto a query on the underling base tables.

GROUP BY h.hotelNo;

(b) SELECT hotelNo

(c) SELECT MIN(bookingCount)

(d) SELECT COUNT(*)

(e) SELECT hotelNo

(f) SELECT hotelNo

Databas – Part III

7.19 Assume that we also have a table for suppliers:

and a view SupplierParts, which contains the distinct part numbers that are supplied by at

least one supplier:

7.20

compliance with the DDL statements in the ISO standard. Investigate the functionality of any

extensions the DBMS supports. Are there any functions not supported?

7.21 Create the DreamHome rental database schema defined in Section 4.2.6 and insert the tuples

shown in Figure 4.3.

7.22 Using the schema you have created above, run the SQL queries given in the examples from

Chapter 6.

7.23 Create the schema for the Hotel schema given at the start of the exercises for Chapter 4 and

insert some sample tuples. Now run the SQL queries that you produced for Exercises 6.7

6.28.

Databas – Part III

39

Case Study 2

7.24 Create the Projects schema using the integrity enhancement features of SQL with the

following constraints:

(a) sex must be one of the single

(c) hoursWorked must be an integer value between 0 and 40.

CREATE SCHMEA Projects;

CREATE DOMAIN TableKey AS CHAR(5);

CREATE DOMAIN Name AS VARCHAR(20);

CREATE TABLE Employee (

empNo TableKey NOT NULL,

fName Name NOT NULL,

CREATE TABLE Department (

CREATE TABLE Project (

projNo TableKey NOT NULL,

CREATE TABLE WorksOn (

empNo EmpNo NOT NULL,

Databas – Part III

40

7.25 Create a view consisting of the Employee and Department tables without the address, DOB,

and sex attributes.

7.26 Create a view consisting of the attributes empNo, fName, lName, projName, and hoursWorked

attributes.

7.27 Consider the following view defined on the Projects schema:

CREATE VIEW EmpProject(empNo, projNo, totalHours)

AS SELECT w.empNo, w.projNo, SUM(hoursWorked)

FROM Employee e, Project p, WorksOn w

WHERE e.empNo _ w.empNo AND p.projNo _ w.projNo

GROUP BY w.empNo, w.projNo;

(a) SELECT *

General

7.28 Consider the following table:

which represents the cost negotiated under each contract for a part (a part may have a different

price under each contract). Now consider the following view ExpensiveParts, which

contains the distinct part numbers for parts that cost more than £1000:

Databas – Part III

41