Database Systems A Practical Approach to Design, Implementation, And Management SIXth Edition Global Edition By Thomas Connolly – Solution Manual

$25.00

Description

COMPLETE TEXT B00K SOLUTION WITH ANSWERS

 

 

ORDER WILL BE DELIVER WITHIN FEW HOURS

 

 

SAMPLE QUESTIONS

 

 

Chapter 1 Introduction to Databases

Review Questions

1.1 List four government sectors in your country that use database systems..

1.2 Discuss each of the following terms:

(a) data

(b) database

(c) database management system

(d) database application program

(e) data independence

(f) security

(g) integrity

(h) views

1.3 Describe the role of database management systems (DBMS) in the database approach. Discuss why knowledge of

DBMS is important for database administrators.

1.4 Describe the main characteristics of the database approach and contrast it with the file-based approach.

1.5 Describe the five components of the DBMS environment and discuss how they relate to each other.

1.6 Discuss the roles of the following personnel in the database environment:

(a) data administrator

(b) database administrator

(c) logical database designer

(d) physical database designer

(e) application developer

(f) end-users

1.7 Discuss the three generations of DBMSs.

1.8 Why are views an important aspect of database management systems?

Exercises

1.9 Interview some users of database systems. Which DBMS features do they find most useful and why? Which DBMS

facilities do they find least useful and why? What do these users perceive to be the advantages and

disadvantages of the DBMS?

1.10 A database approach addresses several of the problems and challenges associated with the traditional file-based

approach. Using a DBMS to control how data is shared with different applications and users, through applications

such as views, has a number of advantages. However, the implementation of a database approach has its own

challenges, such as expense. Discuss the various costs associated with the implementation of a

database approach.

1.11 Study the DreamHome case study presented in Section 11.4 and Appendix A.

(a) In what ways would a DBMS help this organization?

(b) What do you think are the main objects that need to be represented in the database?

(c) What relationships do you think exist between these main objects?

(d) For each of the objects, what details do you think need to be stored in the database?

(e) What queries do you think are required?

1.12 Study the Wellmeadows Hospital case study presented in Appendix B.3.

(a) In what ways would a DBMS help this organization?

(b) What do you think are the main objects that need to be represented in the database?

(c) What relationships do you think exist between these main objects?

(d) For each of the objects, what details do you think need to be stored in the database?

(e) What queries do you think are required?

1.13 Discuss what you consider to be the three most important advantages for the use of a DBMS for a company

like DreamHome and provide a justification for your selection. Discuss what you consider to be the three most

important disadvantages for the use of a DBMS for a company like DreamHome and provide a justification for your

selection.

1.14 Organizations have a vital need for quality information. Discuss how the following database roles relate to each

other.

(a) Data Administrator

(b) Database Administrator

(c) Database Designer

(d) Application Developer

(e) End-Users

 

 

Chapter 2 Database Environment

Review Questions

2.1 E xplain the concept of database schema and discuss the three types of schema in a database.

2.2 What are data sublanguages? Why are they important?

2.3 What is a data model? Discuss the main types of data model.

2.4 Discuss the function and importance of conceptual modeling.

2.5 Describe the types of facility that you would expect to be provided in a multi-user DBMS.

2.6 Of the facilities described in your answer to Question 2.5, which ones do you think would not be needed in a

standalone PC DBMS? Provide justification for your answer.

2.7 Discuss the function and importance of the system catalog.

2.8 Discuss the differences between DDL and DML. What operations would you typically expect to be available in

each language?

2.9 Discuss the differences between procedural DMLs and nonprocedural DMLs.

2.10 Name four object-based data models.

2.11 Name three record-based data models. Discuss the main differences between these data models.

2.12 What is a transaction? Give an example of a transaction.

2.13 What is concurrency control and why does a DBMS need a concurrency control facility?

2.14 Define the term “database integrity”. How does database integrity differ from database security?

Exercises

2.15 A nalyze the DBMSs that you are currently using. Determine each system’s compliance with the functions that

we would expect to be provided by a DBMS. What type of language does each system provide? What type of

architecture does each DBMS use? Check the accessibility and extensibility of the system catalog. Is it possible to

export the system catalog to another system?

2.16 Write a program that stores names and telephone numbers in a database. Write another program that stores

names and addresses in a database. Modify the programs to use external, conceptual, and internal schemas. What

are the advantages and disadvantages of this modification?

2.17 Write a program that stores names and dates of birth in a database. Extend the program so that it stores the

format of the data in the database: in other words, create a system catalog. Provide an interface that makes this

system catalog accessible to external users.

2.17 Write a program that stores names and dates of birth in a database. Extend the program so that it stores the

format of the data in the database: in other words, create a system catalog. Provide an interface that makes this

system catalog accessible to external users.

2.18 A database approach uses different data models. Common database models include the relational model, the

network model and the hierarchical model. Which data model should be chosen under which circumstances

and why?

 

Chapter 3 Database Architectures and the Web

Review Questions

3.1 What is meant by the term ‘client–server architecture’ and what are the advantages of this

approach? Compare the client–server architecture with two other architectures.

3.2 Compare and contrast the two-tier client–server architecture for traditional DBMSs with the

three-tier client–server architecture. Why is the latter architecture more appropriate for the

Web?

3.3 How is an application server different from a file server?

3.4 What is a data warehouse? How is it different from an OLTP system?

3.5 What is a TP Monitor? What advantages does a TP Monitor bring to an OLTP environment?

3.6 Describe the features of a distributed database management system (DDBMS).

3.7 What technologies and standards are used to develop Web services and how do they relate

to each other?

3.8 What is a service-oriented architecture?

3.9 Describe the functions of a database manager?

3.10 What is Cloud computing?

3.11 Discuss the five essential characteristics of cloud computing.

3.12 Discuss the three main service models of cloud computing.

3.13 Compare and contrast the four main deployment models for the cloud.

3.14 What is the difference between Data as a service (DaaS) and Database as a service (DBaaS)?

3.15 Discuss the different architectural models for Database as a service.

3.16 Describe the main components in a DBMS.

3.17 Describe the internal architecture of Oracle.

Exercises

3.18 Examine the documentation sets of Microsoft SQL Server, Oracle, and IBM’s DB2 system to

identify their support for the following:

(a) client–server architecture

(b) Web services

(c) service-oriented architecture

3.19 Search the Web for a number of Web services other than the ones discussed in Section 3.2.

What do these services have in common? Identify whether the services access a database.

3.20 Based on the Oracle architecture described in section 3.7, examine the structure of two

other DBMSs of your choice. Describe features common to all three DMBSs.

 

Chapter 4 The Relational Model

Review Questions

4.1 Discuss each of the following concepts in the context of the relational data model:

(a) relation

(b) attribute

(c) domain

(d) tuple

(e) intension and extension

(f ) degree and cardinality.

4.2 Describe the relationship between mathematical relations and relations in the relational data model.

4.3 Describe the term “normalized reaction.” Why are constraints so important in a relational database?

4.4 Discuss the properties of a relation.

4.5 Discuss the differences between the candidate keys and the primary key of a relation. Explain what is meant by a

foreign key. How do foreign keys of relations relate to candidate keys? Give examples to illustrate your answer.

4.6 Define the two principal integrity rules for the relational model. Discuss why it is desirable to enforce these rules.

4.7 Define “views.” Why are they important in a database approach?

Exercises

The following tables form part of a database held in a relational DBMS:

Hotel (hotelNo, hotelName, city)

Room (roomNo, hotelNo, type, price)

Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)

Guest (guestNo, guestName, guestAddress)

where Hotel contains hotel details and hotelNo is the primary key;

Room contains room details for each hotel and (roomNo, hoteINo) forms the primary key;

Booking contains details of bookings and (hoteINo, guestNo, dateFrom) forms the primary key;

Guest contains guest details and guestNo is the primary key.

4.8 Identify the foreign keys in this schema. Explain how the entity and referential integrity rules apply to these relations.

4.9 Produce some sample tables for these relations that observe the relational integrity rules. Suggest some general

constraints that would be appropriate for this schema.

4.10 Analyze the RDBMSs that you are currently using. Determine the support the system provides for primary keys,

alternate keys, foreign keys, relational integrity, and views.

4.11 Implement the above schema in one of the RDBMSs you currently use. Generate two user-views that are

accessible and updatable as well as two other user-views that cannot be updated.

 

 

Chapter 5 Relational Algebra and Relational Calculus

 

Review Questions

5.1 What is the difference between a procedural and a nonprocedural language? How would you classify the relational

algebra and relational calculus?

5.2 Explain the following terms:

(a) tuple relational calculus

(b) domain relational calculus

5.3 Define the five basic relational algebra operations. Define the Join, Intersection, and Division operations in terms of

these five basic operations.

5.4 Discuss the differences between the five Join operations: Theta join, Equijoin, Natural join, Outer join, and

Semijoin. Give examples to illustrate your answer.

5.5 There are different types of join operations that can be used to retrieve data, based on different relations.

Describe the relation between theta and equal join.

5.6 Define the structure of a (well-formed) formula in both the tuple relational calculus and domain relational calculus.

5.7 What is the difference between existential and universal quantifiers in relational calculus? Give examples to

elaborate how the two are applied in a statement.

Exercises

For the following exercises, use the Hotel schema defined at the start of the Exercises at the end of Chapter 4.

5.8 Describe the relations that would be produced by the following relational algebra operations:

(a) PhotelNo(sprice . 50(Room))

(b) sHotel,hotelNo 5 Room.hotelNo(Hotel 3 Room)

(c) PhotelName(Hotel 1Hotel.hotelNo 5 Room.hotelNo(sprice . 50(Room)))

(d) Guest 5 (sdateTo $ ‘1-Jan-2007’(Booking))

(e) Hotel 2 Hotel.hotelNo 5 Room.hotelNo(sprice . 50(Room))

(f) PguestName, hotelNo(Booking 1 Booking.guestNo 5 Guest.guestNo Guest) 4 PhotelNo(scity 5 ‘London’(Hotel))

5.9 Provide the equivalent tuple relational calculus and domain relational calculus expressions for each of the relational

algebra queries given in Exercise 5.8.

5.10 Describe the relations that would be produced by the following tuple relational calculus expressions:

(a) {H.hotelName | Hotel(H) Ù H.city 5 ‘London’}

(b) {H.hotelName | Hotel(H) Ù ($R) (Room(R) Ù H.hoteINo = R.hoteINo Ù R.price > 50)}

(c) {H.hotelName | Hotel(H) Ù ($B) ($G) (Booking(B) Ù Guest(G) Ù H.hoteINo 5 B.hoteINo Ù

B.guestNo 5 G.guestNo Ù G.guestName 5 ‘John Smith’)}

(d) {H.hotelName, G.guestName, B1.dateFrom, B2.dateFrom | Hotel(H) Ù Guest(G) Ù Booking(B1) Ù

Booking(B2) Ù H.hoteINo 5 B1.hoteINo Ù G.guestNo 5 B1.guestNo Ù B2.hotelNo 5 B1.hotelNo Ù

B2.guestNo 5 B1.guestNo Ù B2.dateFrom  B1.dateFrom}

5.11 Provide the equivalent domain relational calculus and relational algebra expressions for each of the tuple relational

calculus expressions given in Exercise 5.10.

5.12 Generate the relational algebra, tuple relational calculus, and domain relational calculus expressions for the following

queries:

(a) List all hotels.

(b) List all single rooms with a price below £20 per night.

(c) List the names and cities of all guests.

(d) List the price and type of all rooms at the Grosvenor Hotel.

(e) List all guests currently staying at the Grosvenor Hotel.

(f) List the details of all rooms at the Grosvenor Hotel, including the name of the guest staying in the room, if the

room is occupied.

(g) List the guest details (guestNo, guestName, and guestAddress) of all guests staying at the Grosvenor Hotel.

5.13 Using relational algebra, produce a report of all employees from the IT and planning departments who are born

after 1990.

The following tables form part of a database held in an RDBMS:

Employee (empNo, fName, lName, address, DOB, sex, position, deptNo)

Department (deptNo, deptName, mgrEmpNo)

Project (projNo, projName, deptNo)

WorksOn (empNo, projNo, dateWorked, hoursWorked)

where Employee contains employee details and empNo is the key.

Department contains department details and deptNo is the key. mgrEmpNo identifies the

employee who is the manager of the department. There is only one manager for

each department.

Project contains details of the projects in each department and the key is projNo (no

two departments can run the same project).

and WorksOn contains details of the hours worked by employees on each project, and empNo/

projNo/dateWorked form the key.

Formulate the following queries in relational algebra, tuple relational calculus, and domain relational calculus.

5.14 List all employees.

5.15 List all the details of employees who are female and born after 1990.

5.16 List all employees who are not managers and are paid more than $1500.

5.17 Produce a list of the names and addresses of all employees who work for the IT department.

5.18 Produce a list of the names of all employees who work on the SCCS project.

5.19 Produce a complete list of all managers who are due to retire this year, in alphabetical order of surname.

Formulate the following queries in relational algebra.

5.20 Find out how many managers are female.

5.21 Produce a report of all projects under the IT department.

5.22 Using the union operator, retrieve the list of employees who are neither managers nor supervisors. Attributes to

be retrieved are first name, last name, position, sex and department number.

5.23 List the total number of employees in each department for those departments with more than 10 employees.

Create an appropriate heading for the columns of the results table.

The following tables form part of a Library database held in an RDBMS:

Book (ISBN, title, edition, year)

BookCopy (copyNo, ISBN, available)

Borrower (borrowerNo, borrowerName, borrowerAddress)

BookLoan (copyNo, dateOut, dateDue, borrowerNo)

where Book contains details of book titles in the library and the ISBN is the key.

BookCopy contains details of the individual copies of books in the library and copyNo is the

key. ISBN is a foreign key identifying the book title.

Borrower contains details of library members who can borrow books and borrowerNo is

the key.

BookLoan contains details of the book copies that are borrowed by library members and

copyNo/dateOut forms the key. borrowerNo is a foreign key identifying the

borrower.

Formulate the following queries in relational algebra, tuple relational calculus, and domain relational calculus.

5.24 List all book titles.

5.25 List all borrower details.

5.26 List all book titles published in the year 2012.

5.27 List all copies of book titles that are available for borrowing.

5.28 List all copies of the book title Lord of the Rings that are available for borrowing.

5.29 List the names of borrowers who currently have the book title Lord of the Rings on loan.

5.30 List the names of borrowers with overdue books.

Formulate the following queries in relational algebra.

5.31 How many copies of ISBN “0-321-52306-7” are there?

5.32 How many copies of ISBN “0-321-52306-7” are currently available?

5.33 How many times has the book title with ISBN “0-321-52306-7” been borrowed?

5.34 Produce a report of book titles that have been borrowed by “Peter Bloomfield.”

5.35 For each book title with more than three copies, list the names of library members who have borrowed them.

5.36 Produce a report with the details of borrowers who currently have books overdue.

5.37 Produce a report detailing how many times each book title has been borrowed.

5.38 Analyze the RDBMSs that you are currently using. What types of relational language does the system provide?

For each of the languages provided, what are the equivalent operations for the eight relational algebra operations

defined in Section 5.1?

 

Chapter 6 SQL: Data Manipulation

 

Review Questions

6.1 Briefly describe the four basic SQL DML statements and explain their use.

6.2 Explain the importance and application of the WHERE clause in the UPDATE and DELETE statements.

6.3 Explain the function of each of the clauses in the SELECT statement. What restrictions are imposed on these clauses?

6.4 What restrictions apply to the use of the aggregate functions within the SELECT statement? How do nulls affect

the aggregate functions?

6.5 How can results from two SQL queries be combined? Differentiate how the INTERSECT and EXCEPT commands work.

6.6 Differentiate between the three types of subqueries. Why is it important to understand the nature of subquery

result before you write an SQL statement?

Exercises

For Exercises 6.7–6.28, use the Hotel schema defined at the start of the Exercises at the end of Chapter 4.

Simple queries

6.7 List full details of all hotels.

6.8 List full details of all hotels in London.

6.9 List the names and addresses of all guests living in London, alphabetically ordered by name.

6.10 List all double or family rooms with a price below £40.00 per night, in ascending order of price.

6.11 List the bookings for which no dateTo has been specified.

Aggregate functions

6.12 How many hotels are there?

6.13 What is the average price of a room?

6.14 What is the total revenue per night from all double rooms?

6.15 How many different guests have made bookings for August?

Subqueries and joins

6.16 List the price and type of all rooms at the Grosvenor Hotel.

6.17 List all guests currently staying at the Grosvenor Hotel.

6.18 List the details of all rooms at the Grosvenor Hotel, including the name of the guest staying in the room, if the

room is occupied.

6.19 What is the total income from bookings for the Grosvenor Hotel today?

6.20 List the rooms that are currently unoccupied at the Grosvenor Hotel.

6.21 What is the lost income from unoccupied rooms at the Grosvenor Hotel?

Grouping

6.22 List the number of rooms in each hotel.

6.23 List the number of rooms in each hotel in London.

6.24 What is the average number of bookings for each hotel in August?

6.25 What is the most commonly booked room type for each hotel in London?

6.26 What is the lost income from unoccupied rooms at each hotel today?

Populating tables

6.27 Insert rows into each of these tables.

6.28 Update the price of all rooms by 5%.

General

6.29 Investigate the SQL dialect on any DBMS that you are currently using. Determine the system’s compliance with

the DML statements of the ISO standard. Investigate the functionality of any extensions that the DBMS supports.

Are there any functions not supported?

6.30 Demonstrate that queries written using the UNION operator can be rewritten using the OR operator to produce

the same result.

6.31 Apply the syntax for inserting data into a table.

Case Study 2

For Exercises 6.32–6.40, use the Projects schema defined in the Exercises at the end of Chapter 5.

6.32 List all employees from BRICS countries in alphabetical order of surname.

6.33 List all the details of employees born between 1980–90.

6.34 List all managers who are female in alphabetical order of surname, and then first name.

6.35 Remove all projects that are managed by the planning department.

6.36 Assume the planning department is going to be merged with the IT department. Update employee records to

reflect the proposed change.

6.37 Using the UNION command, list all projects that are managed by the IT and the HR department.

6.38 Produce a report of the total hours worked by each female employee, arranged by department number and

alphabetically by employee surname within each department.

6.39 Remove all project from the database which had no employees worked..

6.40 List the total number of employees in each department for those departments with more than 10 employees.

Create an appropriate heading for the columns of the results table.

Case Study 3

For Exercises 6.41–6.54, use the Library schema defined in the Exercises at the end of Chapter 5.

6.41 List all book titles.

6.42 List all borrower details.

6.43 List all books titles published between 2010 and 2014.

6.44 Remove all books published before 1950 from the database.

6.45 List all book titles that have never been borrowed by any borrower.

6.46 List all book titles that contain the word ‘database’ and are available for loan.

6.47 List the names of borrowers with overdue books.

6.48 How many copies of each book title are there?

6.49 How many copies of ISBN “0-321-52306-7” are currently available?

6.50 How many times has the book title with ISBN “0-321-52306-7” been borrowed?

6.51 Produce a report of book titles that have been borrowed by “Peter Bloomfield.”

6.52 For each book title with more than three copies, list the names of library members who have borrowed them.

6.53 Produce a report with the details of borrowers who currently have books overdue.

6.54 Produce a report detailing how many times each book title has been borrowed.

 

 

Chapter 7 SQL: Data Definition

 

Review Questions

7.1 What are the main SQL DDL statements?

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

7.3 What are the privileges commonly granted to database users?

7.4 Discuss the advantages and disadvantages of views.

7.5 Discuss the ways by which a transaction can complete.

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

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?

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

does SQL support?

7.9 Describe how the access control mechanisms of SQL work.

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.

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) dateFrom and dateTo must be greater than today’s date.

(e) The same room cannot be double-booked.

(f) The same guest cannot have overlapping bookings.

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 1 January

  1. Delete all bookings before 1 January 2013 from the Booking table.

7.13 Assume that all hotels are loaded. Create a view containing the cheapest hotels in the world.

7.14 Create a view containing the guests who are from BRICS countries.

7.15 Give the users Manager and Director 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.

7.17 Consider the following view defined on the Hotel schema:

CREATE VIEW HotelBookingCount (hoteINo, bookingCount)

AS SELECT h.hoteINo, COUNT(*)

FROM Hotel h, Room r, Booking b

WHERE h.hoteINo 5 r.hotelNo AND r.roomNo 5 b.roomNo

GROUP BY h.hotelNo;

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

queries would be mapped on to a query on the underlying base tables.

(a) SELECT *

FROM HotelBookingCount;

(b) SELECT hoteINo

FROM HotelBookingCount

WHERE hoteINo =’H001’;

(c) SELECT MIN(bookingCount)

FROM HotelBookingCount;

(d) SELECT COUNT(*)

FROM HotelBookingCount;

(e) SELECT hoteINo

FROM HotelBookingCount

WHERE bookingCount > 1000;

(f) SELECT hoteINo

FROM HotelBookingCount

ORDER BY bookingCount;

7.19 Assume that we also have a table for suppliers:

Supplier (supplierNo. partNo, price)

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

CREATE VIEW SupplierParts (partNo)

AS SELECT DISTINCT partNo

FROM Supplier s, Part p

WHERE s.partNo 5 p.partNo;

Discuss how you would maintain this as a materialized view and under what circumstances you would be able to

maintain the view without having to access the underlying base tables Part and Supplier.

7.20 Analyze three different DBMSs of your choice. Identify objects that are available in the system catalog. Compare

and contrast the object organization, name scheme, and the ways used to retrieve object description.

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 Use the view you created in exercise 7.13 to discuss how you would improve the performance of the SQL

command.

7.23 You are contracted to investigate queries with degraded performance to improve them. Based on the schemas

created in previous exercises, discuss the criteria to decide for or against indexing.

Case Study 2

For Exercises 7.24 to 7.40, use the Projects schema defined in the Exercises at the end of Chapter 5.

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 characters ‘M’ or ‘F’.

(b) position must be one of ‘Manager’, ‘Team Leader’, ‘Analyst’, or ‘Software Developer’.

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

7.25 Create a view consisting of projects managed by female managers and ordered by project number.

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*

FROM EmpProject;

(b) SELECT projNo

FROM EmpProject

WHERE projNo = ‘SCCS’;

(c) SELECT COUNT(projNo)

FROM EmpProject

WHERE empNo = ‘E1’;

(d) SELECT empNo, totalHours

FROM EmpProject

GROUP BY empNo;

General

7.28 Consider the following table:

Part (partNo, contract, partCost)

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:

CREATE VIEW ExpensiveParts (partNo)

AS SELECT DISTINCT partNo

FROM Part

WHERE partCost > 1000;

Discuss how you would maintain this as a materialized view and under what circumstances you would be able to

maintain the view without having to access the underlying base table Part.

 

Chapter 8 Advanced SQL

 

Review Questions

8.1 Advanced SQL deals with SQL/PSM and PL/SQL. What led to the introduction of SQL/PSM?

8.2 Describe the general structure of a PL/SQL block.

8.3 Describe the control statements in PL/SQL. Give examples to illustrate your answers.

8.4 Describe how the PL/SQL statements differ from the SQL standard. Give examples to illustrate your answers.

8.5 What are SQL cursors? Give an example of the use of an SQL cursor.

8.6 How is a procedure different from a function?

8.7 Discuss the differences between BEFORE, AFTER, and INSTEAD OF triggers. Give examples to illustrate your

answers.

8.8 Rows can be changed after they have been fetched through a cursor. How can this event be stopped?

8.9 Discuss the advantages and disadvantages of database triggers.

Exercises

For the following questions, use the Hotel schema from the Exercises at the end of Chapter 4.

8.10 List all hotels in the capital cities of BRICS countries. .

8.11 Create a database trigger for the following situations:

(a) The price of all double rooms must be greater than £100.

(b) The price of double rooms must be greater than the price of the highest single room.

(c) A booking cannot be for a hotel room that is already booked for any of the specified dates.

(d) A guest cannot make two bookings with overlapping dates.

(e) Maintain an audit table with the names and addresses of all guests who make bookings for hotels in London

(do not store duplicate guest details).

8.12 Create an INSTEAD OF database trigger that will allow data to be inserted into the following view:

CREATE VIEW LondonHotelRoom AS

SELECT h.hotelNo, hotelName, city, roomNo, type, price

FROM Hotel h, Room r

WHERE h.hotelNo 5 r.hotelNo AND city 5 ‘London’

8.13 Not all modern DBMSs are embedded with SQL/PSM features. Investigate a DBMS of your choice to determine

if it is SQL/PSM compliant. Discuss situations in the DreamHome project that require trigger creation.

 

Chapter 9 Object-Relational DBMSs

Review Questions

9.1 Discuss the general characteristics of advanced database applications.

9.2 Discuss why the weaknesses of the relational data model and relational DBMSs may make them unsuitable for

advanced database applications.

9.3 Describe strategies used in ORDB designs to map classes to relations.

9.4 What functionality would typically be provided by an ORDBMS?

9.5 Compare and contrast the different collection types.

9.6 What is a typed table? How is it different from other table types?

9.7 Discuss how references types and object identity can be used.

9.8 Compare and contrast procedures, functions, and methods.

9.9 Discuss the extensions required for query processing and query optimization to fully support

the ORDBMS.

9.10 Discuss the collection types available in SQL:2011.

9.11 What are the security problems associated with the introduction of user-defined methods?

Suggest some solutions to these problems.

Exercises

9.12 Investigate one of the advanced database applications discussed in Section 9.1, or a similar

one that handles complex, interrelated data. In particular, examine its functionality and the

data types and operations it uses. Map the data types and operations to the object-oriented

concepts discussed in Appendix K.

9.13 Analyze one of the RDBMSs that you currently use. Discuss the object-oriented features

provided by the system. What additional functionality do these features provide?

9.14 Analyze the RDBMSs that you are currently using. Discuss the object-oriented facilities provided

by the system. What additional functionality do these facilities provide?

9.15 Discuss key issues to consider when converting a relational database to an object relational

database.

9.16 DreamHome has contracted you to help them with changing their system from a relational

database to an object relational database. Produce a presentation highlighting the approaches

and key processes that will be involved.

9.17 Create an insert trigger that sets up a mailshot table recording the names and addresses of all

guests who have stayed at the hotel during the days before and after New Year for the past

two years.

9.18 Investigate the relational schema and object relational schema used in the EasyDrive School of

Motoring case study. What are the key differences between the two?

9.19 Create an object-relational schema for the DreamHome case study documented in Appendix A.

Add user-defined functions that you consider appropriate. Implement the queries listed in

Appendix

A using SQL:2011.

9.20 Create an object-relational schema for the University Accommodation Office case study

documented

in Appendix B.1. Add user-defined functions that you consider appropriate.

9.21 Create an object-relational schema for the EasyDrive School of Motoring case study

documented

in Appendix B.2. Add user-defined functions that you consider appropriate.

9.22 Create an object-relational schema for the Wellmeadows case study documented in Appendix

B.3. Add user-defined functions that you consider appropriate.

9.23 You have been asked by the Managing Director of DreamHome to investigate and prepare

a report on the applicability of an object-relational DBMS for the organization. The

report should compare the technology of the RDBMS with that of the ORDBMS, and

should address the advantages and disadvantages of implementing an ORDBMS within

the organization, and any perceived problem areas. The report should also consider the

applicability of an object-oriented DBMS, and a comparison of the two types of system

for DreamHome should be included. Finally, the report should contain a fully justified set of

conclusions on the applicability of the ORDBMS for DreamHome.

 

Chapter 10 Database System Development Lifecycle

 

Review Questions

10.1 Discuss the interdependence that exists between DSDLC stages.

10.2 What do you understand by the term “system mission”? Why is it important during system development?

10.3 Describe the main purpose(s) and activities associated with each stage of the database system development

lifecycle.

10.4 Discuss what a user view represents in the context of a database system.

10.5 Discuss the main approaches to database design. Discuss the contexts where each is appropriate.

10.6 Compare and contrast the three phases of database design.

10.7 What are the main purposes of data modeling and identify the criteria for an optimal data model?

10.8 Identify the stage(s) in which it is appropriate to select a DBMS and describe an approach to selecting the “best”

DBMS.

10.9 Application design involves transaction design and user interface design. Describe the purpose and main activities

associated with each.

10.10 Discuss why testing cannot show the absence of faults, only that software faults are present.

10.11 What is the difference between the prototyping approach and the database systems development lifecycle?

Exercises

10.12 Assume you have been contracted to develop a database system for a university library. You are required to use

a systems development lifecycle approach. Discuss how you are going to approach the project. Describe user

groups that will be involved during the requirement analysis. What are the key issues that need to be answered

during fact finding?

10.13 Describe the process of evaluating and selecting a DBMS product for each of the case studies described in

Appendix

B.

10.14 Assume that you are an employee of a consultancy company that specializes in the analysis, design, and implementation

of database systems. A client has recently approached your company with a view to implementing

a database system but they are not familiar with the development process. You have been assigned the task to

present an overview of the Database System Development Lifecycle (DSDL) to them, identifying the main stages

of this lifecycle. With this task in mind, create a slide presentation and/or short report for the client. (The client

for this exercise can be any one of the fictitious case studies given in Appendix B or some real company identified

by you or your professor).

10.15 This exercise requires you to first gain permission to interview one or more people responsible for the development

and/or administration of a real database system. During the interview(s), find out the following information:

(a) The approach taken to develop the database system.

(b) How the approach taken differs or is similar to the DSDL approach described in this chapter.

(c) How the requirements for different users (user views) of the database systems were managed.

(d) Whether a CASE tool was used to support the development of the database system.

(e) How the DBMS product was evaluated and then selected.

(f ) How the database system is monitored and maintained.

 

Chapter 11 Database Analysis and the DreamHome Case Study

 

Review Questions

11.1 Briefly discuss the objectives of interviews in a database development project.

11.2 Describe how fact-finding is used throughout the stages of the database system development lifecycle.

11.3 For each stage of the database system development lifecycle identify examples of the facts captured and the

documentation

produced.

11.4 A database developer normally uses several fact-finding techniques during a single database project. The five

most commonly used techniques are examining documentation, interviewing, observing the business in

operation, conducting

research, and using questionnaires. Describe each fact-finding technique and identify the

advantages and disadvantages of each.

11.5 What are the dangers of not defining mission objectives and a mission statement for a database system?

11.6 What is the purpose of identifying the systems boundary for a database system?

11.7 How do the contents of a users’ requirements specification differ from a systems specification?

11.8 Database development and fact finding are inseparable. One factor for the success of the development processes

if user involvement. What is the role played by users in the database development process?

Exercises

11.9 Assume that your friend is currently employed by a multinational consultancy company that deals with database

analysis and development in Tanzania. His first assignment is to carry out a fact-finding mission for a client that

intends to develop a database system for their company to control their daily business transactions. Your friend

has decided to ask you for advice.

Task: Prepare a brief note that would help your friend successfully choose the best fact-finding technique. For each

technique, outline the factors crucial for realizing quality facts. The note should also detail issues to be avoided or

taken care of for each tool to succeed.

The client for this exercise and those that follow can be any one of the fictitious case studies given in Appendix B

or some real company identified by you or your professor.

11.10 Assume that you are an employee of a consultancy company that specializes in the analysis, design, and

implementation

of database systems. A client has recently approached your company with a view to

implementing a database system.

Task: You are required to establish the database project through the early stages of the project. With this task

in mind, create a mission statement and mission objectives and high-level systems diagram for the client’s

database system.

11.11 Assume that you are an employee of a consultancy company that specializes in the analysis, design, and

implementation

of database systems. A client has recently approached your company with a view to

implementing

a database system. It has already been established that the client’s database system will support

many different groups of users (user views).

Task: You are required to identify how to best manage the requirements for these user views. With this task in

mind, create a report that identifies high-level requirements for each user view and shows the relationship

between

the user views. Conclude the report by identifying and justifying the best approach to managing the

multi-user view requirements.

 

 

Chapter 12 Entity–Relationship Modeling

 

Review Questions

12.1 Why is the ER model considered a top-down approach? Describe the four basic components of the ER model.

12.2 Describe what relationship types represent in an ER model and provide examples of unary, binary, ternary, and

quaternary relationships.

12.3 The ER model uses a number of notations and tags to represent different concepts. Outline how the basic ER

components are represented in an ER diagram.

12.4 Describe what the multiplicity constraint represents for a relationship type.

12.5 What are integrity constraints and how does multiplicity model these constraints?

12.6 How does multiplicity represent both the cardinality and the participation constraints on a relationship type?

12.7 Provide an example of a relationship type with attributes.

12.8 Distinguish between the Entity—Relationship model and the Entity—Relationship diagram.

12.9 Describe how fan and chasm traps can occur in an ER model and how they can be resolved.

Exercises

12.10 Create an ER model for each of the following descriptions:

(a) Each company operates four departments, and each department belongs to one company.

(b) Each department in part (a) employs one or more employees, and each employee works for one department.

(c) Each of the employees in part (b) may or may not have one or more dependents, and each dependent

belongs to one employee.

(d) Each employee in part (c) may or may not have an employment history.

(e) Represent all the ER models described in (a), (b), (c), and (d) as a single ER model.

12.11 Assume you have been contracted by a university to develop a database system to keep track of student registration

and accommodation records. The university courses are offered by faculties. Depending on the student’s

IQ, there are no limitations to how many courses a student can enroll in. The faculties are not responsible for

student accommodation. The university owns a number of hostels and each student is given a shared room key

after enrollment. Each room has furniture attached to it.

(a) Identify the main entity types for the project.

(b) Identify the main relationship types and specify the multiplicity for each relationship. State any assumptions

that you make about the data.

(c) Using your answers for (a) and (b), draw a single ER diagram to represent the data requirements for the project.

12.12 Read the following case study, which describes the data requirements for a DVD rental company. The DVD

rental company has several branches throughout the United States. The data held on each branch is the branch

address made up of street, city, state, and zip code, and the telephone number. Each branch is given a branch

number, which is unique throughout the company. Each branch is allocated staff, which includes a Manager. The

Manager is responsible for the day-to-day running of a given branch. The data held on a member of staff is his

or her name, position, and salary. Each member of staff is given a staff number, which is unique throughout the

company. Each branch has a stock of DVDs. The data held on a DVD is the catalog number, DVD number,

title, category, daily rental, cost, status, and the names of the main actors and the director. The catalog number

uniquely identifies each DVD. However, in most cases, there are several copies of each DVD at a branch, and

the individual copies are identified using the DVD number. A DVD is given a category such as Action, Adult, Children,

Drama, Horror, or Sci-Fi. The status indicates whether a specific copy of a DVD is available for rent. Before

borrowing a DVD from the company, a customer must first register as a member of a local branch. The data

held on a member is the first and last name, address, and the date that the member registered at a branch. Each

member is given a member number, which is unique throughout all branches of the company. Once registered,

a member is free to rent DVDs, up to a maximum of ten at any one time. The data held on each DVD rented

is the rental number, the full name and number of the member, the DVD number, title, and daily rental, and the

dates the DVD is rented out and returned. The DVD number is unique throughout the company.

(a) Identify the main entity types of the DVD rental company.

(b) Identify the main relationship types between the entity types described in part (a) and represent each

relationship

as an ER diagram.

(c) Determine the multiplicity constraints for each relationships described in part (b). Represent the multiplicity

for each relationship in the ER diagrams created in part (b).

(d) Identify attributes and associate them with entity or relationship types. Represent each attribute in the ER

diagrams created in (c).

(e) Determine candidate and primary key attributes for each (strong) entity type.

(f ) Using your answers to parts (a) to (e), attempt to represent the data requirements of the DVD rental company

as a single ER diagram. State any assumptions necessary to support your design.

12.13 Create an ER model for each of the following descriptions:

(a) A large organization has several parking lots, which are used by staff.

(b) Each parking lot has a unique name, location, capacity, and number of floors (where appropriate).

(c) Each parking lot has parking spaces, which are uniquely identified using a space number.

(d) Members of staff can request the sole use of a single parking space. Each member of staff has a unique number,

name, telephone extension number, and vehicle license number.

(e) Represent all the ER models described in parts (a), (b), (c), and (d) as a single ER model. Provide any assumptions

necessary to support your model.

The final answer to this exercise is shown as Figure 13.11.

12.14 Create an ER model to represent the data used by the library.

The library provides books to borrowers. Each book is described by title, edition, and year of publication, and is

uniquely identified using the ISBN. Each borrower is described by his or her name and address and is uniquely

identified using a borrower number. The library provides one or more copies of each book and each copy is

uniquely identified using a copy number, status indicating if the book is available for loan, and the allowable loan

period for a given copy. A borrower may loan one or many books, and the date each book is loaned out and is

returned is recorded. Loan number uniquely identifies each book loan.

The answer to this exercise is shown as Figure 13.12.

 

Chapter 13 Enhanced Entity–Relationship Modeling

 

Review Questions

13.1 What are the key differences between the ER and EER models?

13.2 Describe situations that would call for an enhanced entity—relationship in data modeling.

13.3 Describe and illustrate using an example the process of attribute inheritance.

13.4 What are the main reasons for introducing the concepts of superclasses and subclasses into an ER model?

13.5 Describe what a shared subclass represents and how this concept relates to multiple inheritance.

13.6 Describe and contrast the process of specialization with the process of generalization.

13.7 Describe the UML notation used to represent superclass/subclass relationships.

13.8 Describe and contrast the concepts of aggregation and composition and provide an example of each.

Exercises

13.9 Consider whether it is appropriate to introduce the enhanced concepts of specialization/generalization, aggregation,

and/or composition for the case studies described in Appendix B.

13.10 The features of EER and ER models can co-exist in the same diagram, What situations lead to this co-existence?

Analyze the case presented in question 12.11 and redraw the ER diagram as an EER diagram with the additional

enhanced concepts.

13.11 Introduce specialization/generalization concepts into the ER model shown in Figure 13.11 and described in Exercise

12.13 to show the following:

Figure 13.11 Parking lot ER model was described in Exercise 12.13.

(a) The majority of parking spaces are under cover and each can be allocated for use by a member of staff for a

monthly rate.

(b) Parking spaces that are not under cover are free to use and each can be allocated for use by a member of

staff.

(c) Up to twenty covered parking spaces are available for use by visitors to the company. However, only members

of staff are able to book out a space for the day of the visit. There is no charge for this type of booking,

but the member of staff must provide the visitor’s vehicle license number.

The final answer to this exercise is shown as Figure 17.11.

13.12 The library case study described in Exercise 12.14 is extended to include the fact that the

library has a significant stock of books that are no longer suitable for loaning out. These books

can be sold for a fraction of the original cost. However, not all library books are eventually

sold as many are considered too damaged to sell on, or are simply lost or stolen. Each

book copy that is suitable for selling has a price and the date that the book is no longer to

be loaned out. Introduce enhanced concepts into the ER model shown in Figure 13.12 and

described in Exercise 12.14 to accommodate this extension to the original case study.

The answer to this exercise is shown as Figure 17.12.

Book

Borrower Book Loan

Book Copy

ISBN {PK}

title

edition

yearPublished

borrowerNo {PK}

name

address

loanNo {PK}

dateOut

dateReturned

copyNo {PK}

status

loanPeriod

Provides

Borrows

1..1

1..1

Is

1..1 1.. *

1.. *

1.. *

Figure 13.12 Library ER model was described in Exercise 12.14.

 

Chapter 14 Normalization

 

Review Questions

14.1 Describe the purpose of normalizing data.

14.2 Discuss the alternative ways that normalization can be used to support database design.

14.3 How does normalization eradicate update anomalies from a relation?

14.4 Describe the concept of functional dependency.

14.5 What are the main characteristics of functional dependencies that are used for normalization?

14.6 Describe how a database designer typically identifies the set of functional dependencies associated with a relation.

14.7 Describe factors that would influence the choice of normalization or ER modeling when designing a database.

14.8 Why is normalization regarded as a bottom-up design approach? How does it differ from ER modeling?

14.9 Describe the two approaches to converting an UNF table to 1NF relation(s).

14.10 The second normal form (2NF) is realized by removing partial dependencies from 1NF relations. Briefly describe

the term “partial dependency.”

14.11 Describe the concept of transitive dependency and describe how this concept relates to 3NF. Provide an example

to illustrate your answer.

14.12 Discuss how the definitions of 2NF and 3NF based on primary keys differ from the general definitions of 2NF

and 3NF. Provide an example to illustrate your answer.

Exercises

14.13 Normalization is an important concept for database professionals. Whether you are the designer, database

analyst or administrator, it is useful for designing, situation verification as well as performance tuning. What are

the basic issues to be aware of before carrying out the normalization process?.

14.14 Examine the Patient Medication Form for the Wellmeadows Hospital case study (see Appendix B) shown in

Figure 14.18.

(a) Identify the functional dependencies represented by the attributes shown in the form in Figure 14.18. State

any assumptions that you make about the data and the attributes shown in this form.

(b) Describe and illustrate the process of normalizing the attributes shown in Figure 14.18 to produce a set of

well-designed 3NF relations.

(c) Identify the primary, alternate, and foreign keys in your 3NF relations.

476 | Chapter 14 Normalization

Figure 14.18 The Wellmeadows Hospital Patient Medication Form.

14.15 The table shown in Figure 14.19 lists sample dentist/patient appointment data. A patient is given an appointment

at a specific time and date with a dentist located at a particular surgery. On each day of patient appointments, a

dentist is allocated to a specific surgery for that day.

(a) The table shown in Figure 14.19 is susceptible to update anomalies. Provide examples of insertion, deletion,

and update anomalies.

(b) Identify the functional dependencies represented by the attributes shown in the table of Figure 14.19. State

any assumptions you make about the data and the attributes shown in this table.

(c) Describe and illustrate the process of normalizing the table shown in Figure 14.19 to 3NF relations. Identify

the primary, alternate, and foreign keys in your 3NF relations.

Exercises | 477

Figure 14.19 Table displaying sample dentist/patient appointment data.

14.16 An agency called Instant Cover supplies part-time/temporary staff to hotels within Scotland. The table shown in

Figure 14.20 displays

sample data, which lists the time spent by agency staff working at various hotels. The National

Insurance Number (NIN) is unique for every member of staff.

(a) The table shown in Figure 14.20 is susceptible to update anomalies. Provide examples of insertion, deletion,

and update anomalies.

(b) Identify the functional dependencies represented by the attributes shown in the table of Figure 14.20. State

any assumptions that you make about the data and the attributes shown in this table.

(c) Describe and illustrate the process of normalizing the table shown in Figure 14.20 to 3NF. Identify primary,

alternate, and foreign keys in your relations.

Figure 14.20 Table displaying sample data for the Instant Cover agency.

14.17 A company called FastCabs provides a taxi service to clients. The table shown in Figure 14.21 displays some

details of client bookings for taxis. Assume that a taxi driver is assigned to a single taxi, but a taxi can be assigned

to one or more drivers.

(a) Identify the functional dependencies that exist between the columns of the table in Figure 14.21 and identify

the primary key and any alternate key(s) (if present) for the table.

(b) Describe why the table in Figure 14.21 is not in 3NF.

(c) The table shown in Figure 14.21 is susceptible to update anomalies. Provide examples of how insertion, deletion,

and modification anomalies could occur on this table.

Figure 14.21 Table displaying sample data for FastCabs.

14.18 Applying normalisation to 3NF on the table shown in Figure 14.21 results in the formation of the three 3NF

tables shown in Figure 14.22.

(a) Identify the functional dependencies that exist between the columns of each table in Figure 14.22 and identify

the primary key and any alternate and foreign key(s) (if present) for each table.

(b) Describe why storing the FastCabs data across three 3NF tables avoids the update anomalies described in

Exercise 14.17(b).

(c) Describe how the original table shown in Figure 14.21 can be re-created through relational joins between

primary key and foreign keys columns of the tables in Figure 14.22.

Figure 14.22 Tables (in 3NF) displaying sample data for FastCabs.

14.19 Students can lease university flats and some of the details of leases held by students for places in university flats

are shown in Figure 14.23. A place number (placeNo) uniquely identifies each single room in all flats and is used

when leasing a room to a student.

(a) Identify the functional dependencies that exist between the columns of the table in Figure 14.23 and identify

the primary key and any alternate key(s) (if present) for the table.

(b) Describe why the table in Figure 14.23 is not in 3NF.

(c) The table shown in Figure 14.23 is susceptible to update anomalies. Provide examples of how insertion,

deletion, and modification anomalies could occur on this table.

Figure 14.23 Table displaying sample data for university accommodation.

10003

10259

10364

10566

11067

11169

leaseNo

01/09/2010

01/09/2011

01/09/2011

01/09/2011

01/09/2012

01/09/2012

startDate

30/06/2011

30/06/2012

30/06/2012

30/06/2012

30/06/2013

30/06/2013

finishDate

78

88

89

102

88

78

placeNo

B017706

B017706

B013399

B012124

B034511

B013399

bannerID

B017706

B013399

B012124

B034511

Jane

Tom

Karen

Steven

Watt

Jones

Black

Smith

bannerID fName IName

F56

F78

F79

flatNo

34 High Street, Paisley

111 Storrie Road, Paisley

120 Lady Lane, Paisley

flat Address

78

88

89

102

F56

F78

F78

F79

placeNo flatNo

Figure 14.24 Tables (in 3NF) displaying sample data of university accommodation.

14.20 Applying normalisation to 3NF on the table shown in Figure 14.23 results in the formation of the four tables

shown in Figure 14.24.

(a) Identify the functional dependencies that exist between the columns of each table in Figure 14.24 and identify

the primary key and any alternate and foreign key(s) (if present) for each table.

(b) Describe why storing the university accommodation data across four 3NF tables avoids the update anomalies

described in Exercise 14.19(b).

(c) Describe how the original table shown in Figure 14.23 can be re-created through relational joins between

primary key and foreign keys columns of the tables in Figure 14.24.

 

Chapter 15 Advanced Normalization

Review Questions

15.1 What is the overall purpose of using inference rules and Armstrong’s axioms while developing a database?

15.2 What conditions would make a 3NF relation violate Boyce-Codd Normal Form (BCNF)?

15.3 Discuss the purpose of Boyce–Codd Normal Form (BCNF) and discuss how BCNF differs from 3NF. Provide an

example to illustrate your answer.

15.4 Describe the concept of multi-valued dependency and discuss how this concept relates to 4NF. Provide an

example to illustrate your answer.

15.5 Describe the concept of join dependency and discuss how this concept relates to 5NF. Provide an example to

illustrate your answer.

Exercises

15.6 On completion of Exercise 14.14, examine the 3NF relations created to represent the attributes shown in the

Wellmeadows Hospital form shown in Figure 14.18. Determine whether these relations are also in BCNF. If not,

transform the relations that do not conform into BCNF.

15.7 On completion of Exercise 14.15, examine the 3NF relations created to represent the attributes shown in the

relation that displays dentist/patient appointment data in Figure 14.19. Determine whether these relations are

also in BCNF. If not, transform the relations that do not conform into BCNF.

15.8 BCNF is a stronger form of 3NF. Thus, every relation in BCNF is also in 3NF. However, a relation in 3NF is not

necessarily in BCNF. Using example to emphasize your case, describe the situations when a 3NF relation is in

BCNF and when it is not.

15.9 The relation shown in Figure 15.11 lists the students (studentName) enrolled in a postgraduate program

at Mzumbe University. Every student is required to provide information concerning names of sponsor(s)

(sponsorName),

at least two referees (refereeName), and three supervisors (supervisorName). Assume that

sponsors, referees and supervisors are not allowed to be related to more than one student.

Figure 15.11 Student Registrations

(a) Describe why the relation shown in Figure 15.11 is not in 4NF.

(b) The relation shown in Figure 15.11 violates normalization principles. What are the problems users are likely

to face?

(c) Describe how you would normalize the relation shown in Figure 15.11 to 4NF.

15.10 The relation shown in Figure 15.12 describes hospitals (hospitalName) that require certain items (itemDescription),

which are supplied by suppliers (supplierNo) to the hospitals (hospitalName). Furthermore, whenever

a hospital (h) requires a certain item (i), and a supplier (s) supplies that item (i), and the supplier (s) already

supplies at least one item to that hospital (h), then the supplier (s) will also supply the required item (i) to the

hospital (h). In this example, assume that a description of an item (itemDescription) uniquely identifies each type

of item.

(a) Describe why the relation shown in Figure 15.12 is not in 5NF.

(b) Describe and illustrate the process of normalizing the relation shown in Figure 15.12 to 5NF.

 

Chapter 16 Methodology—Conceptual Database Design

Review Questions

16.1 Describe the purpose of a design methodology.

16.2 Describe the main phases involved in database design.

16.3 Identify important factors in the success of database design.

16.4 Discuss the important role played by users in the process of database design.

16.5 Describe the main objective of conceptual database design.

16.6 What is the purpose of conceptual database design? How is it utilized while designing a database?

16.7 How would you identify entity and relationship types from a user’s requirements specification?

16.8 How would you identify attributes from a user’s requirements specification and then associate the attributes with

entity or relationship types?

16.9 Using enhanced modeling concepts in the conceptual design is optional. Describe the possible EER concepts that

can be presented in a conceptual model.

16.10 How would you check a data model for redundancy? Give an example to illustrate your answer.

16.11 Discuss why you would want to validate a conceptual data model and describe two approaches to validating a

conceptual data model.

16.12 Identify and describe the purpose of the documentation generated during conceptual database design.

Exercises

The DreamHome case study

16.13 Create a conceptual data model for the Branch user views of DreamHome documented in Appendix A.

Compare your ER diagram with Figure 13.8 and justify any differences found.

16.14 Analyze the DreamHome case study and examine if there are situations that call for enhanced modeling. Present

the enhanced data model of the case.

The University Accommodation Office case study

16.15 Describe how you will approach the task of creating the conceptual model for university accommodation

documented

in Appendix

B.1.

16.16 Create a conceptual data model for the case study. State any assumptions necessary to support your design.

Check that the conceptual data model supports the required transactions.

The EasyDrive School of Motoring case study

16.17 Analyze the EasyDrive School of Motoring case study documented in Appendix B.2 and prepare a data dictionary

detailing all the key conceptual issues.

16.18 Create a conceptual data model for the case study. State any assumptions necessary to support your design.

Check that the conceptual data model supports the required transactions.

The Wellmeadows Hospital case study

16.19 Identify user views for the Medical Director and Charge Nurse in the Wellmeadows Hospital case study

described in Appendix B.3.

16.20 Provide a users’ requirements specification for each of these user views.

16.21 Create conceptual data models for each of the user views. State any assumptions necessary to support your

design.

 

Chapter 17 Methodology—Logical Database Design

for the Relational Model

Review Questions

17.1 Describe the steps used to build a logical data model.

17.2 Describe the rules for deriving relations that represent:

(a) strong entity types;

(b) weak entity types;

(c) one-to-many (1:*) binary relationship types;

(d) one-to-one (1:1) binary relationship types;

(e) one-to-one (1:1) recursive relationship types;

(f) superclass/subclass relationship types;

(g) many-to-many (*:*) binary relationship types;

(h) complex relationship types;

(i) multi-valued attributes.

Give examples to illustrate your answers.

17.3 Discuss how the technique of normalization can be used to validate the relations derived from the conceptual

data model.

17.4 Database design is quite complex and important. Discuss the role played by users during the design process.

17.5 Describe database design language (DBDL). Discuss how it is used to derive relations during the logical database

design phase.

17.6 Describe the purpose of merging data models. Discuss the difference between local logical data model and global

logical data model.

17.7 Why is it important to check the logical data model for future growth?

Exercises

17.8 Derive relations from the following conceptual data model shown in Figure 17.10.

The DreamHome case study

17.9 Create a relational schema for the Branch user view of DreamHome based on the conceptual data model produced

in Exercise 16.13 and compare your schema with the relations listed in Figure 17.5. Justify any differences found.

The University Accommodation Office case study

17.10 Create and validate a logical data model from the conceptual data model for the University Accommodation Office

case study created in Exercise 16.16.

The EasyDrive School of Motoring case study

17.11 Create and validate a logical data model from the conceptual data model for the EasyDrive School of Motoring

case study created in Exercise 16.18.

The Wellmeadows Hospital case study

17.12 Create and validate the local logical data models for each of the local conceptual data models of the

Wellmeadows Hospital case study identified in Exercise 16.21.

17.13 Merge the local data models to create a global logical data model of the Wellmeadows Hospital case study. State

any assumptions necessary to support your design.

The Parking Lot case study

17.14 Present the relational schema mapped from the Parking Lot EER model shown in Figure 17.11 and described in

Exercises 12.13 and 13.11.

17.16 The ER diagram in Figure 17.13 shows only entities and primary key attributes. The absence of recognizable

named entities or relationships is to emphasize the rule-based nature of the mapping rules described previously

in Step 2.1 of logical database design.

Answer the following questions with reference to how the ER model in Figure 17.13 maps

to relational tables.

(a) How many relations will represent the ER model?

(b) How many foreign keys are mapped to the relation representing X?

(c) Which relation(s) will have no foreign key?

(d) Using only the letter identifier for each entity, provide appropriate names for the relations

mapped from the ER model.

(e) If the cardinality for each relationship is changed to one-to-one with total participation for

all entities, how many relations would be derived from this version of the ER model?

 

Chapter 18 Methodology—Physical Database Design

for Relational Databases

 

Review Questions

18.1 Physical database design depends much on the logical and conceptual design. Discuss the validity of this statement.

18.2 Discuss why it is important to analyze transactions before implementing the index.

18.3 Describe the purpose of the main steps in the physical design methodology presented in this chapter.

18.4 Describe the 80/20 rule and how it is used in the physical database model.

Exercises

The DreamHome case study

18.5 In Step 4.3 we chose the indexes to create in Microsoft Office Access for the query transactions listed in

Appendix

A for the StaffClient user views of DreamHome. Choose indexes to create in Microsoft Office Access

for the query transactions listed in Appendix A for the Branch user views of DreamHome.

18.6 Repeat Exercise 18.5 using Oracle as the target DBMS.

18.7 Create a physical database design for the logical design of the DreamHome case study (described in Chapter 17)

based on the DBMS that you have access to.

18.8 Implement this physical design for DreamHome created in Exercise 18.7.

The University Accommodation Office case study

18.9 Based on the logical data model developed in Exercise 17.10, create a physical database design for the University

Accommodation Office case study (described in Appendix B.I) based on the DBMS that you have access to.

18.10 Implement the University Accommodation Office database using the physical design created in Exercise 18.9.

The EasyDrive School of Motoring case study

18.11 Based on the logical data model developed in Exercise 17.11, create a physical database design for the EasyDrive

School of Motoring case study (described in Appendix B.2) based on the DBMS that you have access to.

18.12 Based on the physical design you just created for EasyDrive School of Motoring, describe critical performance

challenges

that can be foreseen.

The Wellmeadows Hospital case study

18.13 Based on the logical data model developed in Exercise 17.13, create a physical database design for the

Wellmeadows

Hospital case study (described in Appendix B.3) based on the DBMS that you have access to.

18.14 Implement the Wellmeadows Hospital database using the physical design created in Exercise 18.13.

 

Chapter 19 Methodology—Monitoring and Tuning

the Operational System

Review Questions

19.1 Describe the purpose of the main steps in the physical design methodology presented in this chapter.

19.2 Under what circumstances would you want to denormalize a logical data model? Use examples

to illustrate your

answer.

19.3 What factors can be used to measure efficiency?

19.4 Discuss how partitioning and denormalization improve query efficiency.

19.5 What are the benefits of partitioning a relation?

Exercises

19.6 Analyze the conceptual and logical model for the DreamHome case study presented in figures 19.1(a) and 19.1(b)

respectively. Suggest areas that require either denormalization or partitioning. Make any necessary constructive

assumptions to justify your case.

 

Chapter 20 Security and Administration

Review Questions

20.1 Explain the purpose and scope of database security.

20.2 The scope of database security extends beyond just DBMS controls. Discuss the role of the database administrator

in database security and recovery.

20.3 Explain the following in terms of providing security for a database:

(a) authorization;

(b) authetication;

(c) access controls;

(d) views;

(e) backup and recovery;

(f ) integrity;

(g) encryption;

(h) RAID technology.

20.4 Distinguish between role and privilege in the context of object and systems privilege.

20.5 Describe the security challenges associated with the use of DBMSs on the web.

20.6 Describe the approaches for securing DBMSs on the Web.

20.7 Describe cloud computing services and the databases within their context.

20.8 Describe any specific security measures for databases in mobile applications and devices.

20.9 What tasks are associated with data administration?

20.10 What tasks are associated with database administration?

Exercises

20.11 Examine any DBMS used by your organization and identify the security measures provided.

20.12 Identify the types of security approach that are used by your organization to secure any DBMSs that are

accessible

over the Web.

20.13 Consider the DreamHome case study described in Chapter 11. List the potential threats that could occur and

propose countermeasures to overcome them.

20.14 Consider the Wellmeadows Hospital case study described in Appendix B.3. List the potential threats that could

occur and propose countermeasures to overcome them.

20.15 Investigate whether data administration and database administration exist as distinct functional areas within your

organization. If identified, describe the organization, responsibilities, and tasks associated with each functional area.

20.16 Describe the use and potential uses of cloud computing services by your organization and list the potential

threats, implications, and countermeasures.

20.17 Analyze the security features of three DBMSs and discuss the similarities and differences between them.

20.18 You are contracted to deploy database security for the university accommodation system. Describe how you will

approach the project.

 

 

Chapter 21 Professional, Legal, and Ethical Issues in Data

Management

Review Questions

21.1 Explain the motives behind organizational ethics and legislations.

21.2 Describe business situations in which an individual’s or businesses’ behavior would be considered:

(a) illegal and unethical;

(b) legal but unethical;

(c) illegal but ethical.

21.3 Explain the role of the organizational code of conduct in relation to local and international legislation.

21.4 Describe the importance of IT governance and its relationship to legal and ethical practice in organizations.

21.5 Explain how an international business can be influenced by laws from a foreign land and how it may face liability

from its customers for reasons beyond its control.

21.6 Explain the legal risks to an international business in data management.

21.7 Describe how an entity or individuals outside and within organizations can have legitimate power to access

corporate

data and, therefore, influence how it is managed.

21.8 Describe some of the legal challenges faced in protecting Intellectual property and promoting innovation with

emerging technology.

Review Questions | 665

Exercises

21.9 Suppose that you are a data administrator in a large European pharmaceutical manufacturer that has significant

sales and marketing efforts in Europe, Japan, and the United States. What data management issues would you

have to be most concerned with?

21.10 Suppose that you have just joined a large financial services company as the head of IT and are asked to create

a formal code of ethics for IT. What steps would you take to research this task, and what resources would you

consider?

21.11 Access Peter Neumann’s “Inside Risk” article archives for the Communications of the ACM (visit www.csl.sri.

com/users/neumann/insiderisks.html). Summarize, in a few paragraphs, a recent article from these archives dealing

with legal and/or ethical issues related to IT.

21.12 Access the ACM Code of Ethics and Professional Conduct and the BCS Code of Conduct and Code of Good

Practice. When comparing the two, discuss elements that are emphasized more (or less) in one code than

another.

21.13 Access the Singapore Computer Society’s Code of Conduct. (www.scs.org.sg/code_of_conduct.php). Compare

this code with either the ACM or BCS code and note any differences.

21.14 Consider the DreamHome case study described in Chapter 11. Produce a report for the Director of the

company outlining the legal and ethical issues that need to be considered and make any recommendations that

you think are appropriate.

21.15 Consider the case studies described in Appendix B. Produce a report for each case study outlining the legal and

ethical issues that need to be considered and make any recommendations you think appropriate.

21.16 Suppose you are the Chief Information Officer for a public University in Europe. What data management issues

would you have to be concerned with and what governance policies and procedures would you have to consider

putting in place to ensure you comply with legal standards?

21.17 Describe the IT governance mechanisms that could be implemented in your organization to improve how IT and

data is managed.

21.18 Describe with examples how legal frameworks in place to support transnational management of data can prove

ineffective in protecting the privacy of business or consumers.

21.19 Most of the legislations described in this chapter were triggered by a problematic situation facing the society.

Analyze the situations in your country and identify problems that, in one way or another, forced the introduction

of the available known legislation.

21.20 IT governance plays a vital role in ensuring accountability and decision rights. Proper management and use of IT

resources helps organizations meet their objectives. Critically analyze how IT governance mechanisms are applied

in an organization of your choice.

 

 

Chapter 22 Transaction Management

Review Questions

22.1 Discuss why data inconsistency and data loss might occur. Why is it necessary to manage concurrent transactions?

22.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 answer.

22.3 Describe, with examples, the types of problem that can occur in a multi-user environment when concurrent access

to the database is allowed.

22.4 Give full details of a mechanism for concurrency control that can be used to ensure that the types of problem

discussed in Question 22.3 cannot occur. Show how the mechanism prevents the problems illustrated from occurring.

Discuss how the concurrency control mechanism interacts with the transaction mechanism.

22.5 Explain the concepts of serial, nonserial, and serializable schedules. State the rules for equivalence of schedules.

22.6 What role does the DBMS transaction manager subsystem play in a multi-user system?

22.7 Discuss the types of problem that can occur with locking-based mechanisms for concurrency control and the

actions that can be taken by a DBMS to prevent them.

22.8 Why would two-phase locking not be an appropriate concurrency control scheme for indexes? Discuss a more

appropriate locking scheme for tree-based indexes.

22.9 What is a timestamp? How do timestamp-based protocols for concurrency control differ from locking-based

protocols?

22.10 Describe the basic timestamp ordering protocol for concurrency control. What is Thomas’s write rule and how

does this affect the basic timestamp ordering protocol?

22.11 Describe the advantages of nested transaction models.

22.12 Discuss the difference between pessimistic and optimistic concurrency control.

22.13 Discuss the types of failure that may occur in a database environment. Explain why it is important for a multi-user

DBMS to provide a recovery mechanism.

22.14 Discuss how the log file (or journal) is a fundamental feature in any recovery mechanism. Explain what is meant

by forward and backward recovery and describe how the log file is used in forward and backward recovery.

What is the significance of the write-ahead log protocol? How do checkpoints affect the recovery protocol?

22.15 Compare and contrast the nested transaction and multi-level transaction models.

22.16 Discuss the following Oracle recovery concepts:

(a) instance recovery

(b) point-in-time recovery

(c) standby database

(d) flashback technology

Exercises

22.17 Analyze any three DBMSs of your choice. Prepare a presentation on the contrasting features and approaches in

the area of concurrent control protocol, backup and recovery mechanisms and support for advanced transaction

models.

22.18 For each of the following schedules, state whether the schedule is serializable, conflict serializable, view serializable,

recoverable, and whether it avoids cascading aborts:

(a) read(T1, balx), read(T2, balx), write(T1, balx), write(T2, balx), commit(T1), commit(T2)

(b) read(T1, balx), read(T2, baly), write(T3, balx), read(T2, balx), read(T1, baly), commit(T1), commit(T2), commit(T3)

(c) read(T1, balx), write(T2, balx), write(T1, balx), abort(T2), commit(T1)

(d) write(T1, balx), read(T2, balx), write(T1, balx), commit(T2), abort(T1)

(e) read(T1, balx), write(T2, balx), write(T1, balx), read(T3, balx), commit(T1), commit(T2), commit(T3)

22.19 Draw a precedence graph for each of the schedules (a) to (e) in the previous exercise.

22.20 (a) Explain what is meant by the constrained write rule and explain how to test whether a schedule is conflict

serializable under the constrained write rule. Using the previous method, determine whether the following

schedule is serializable:

S = [R1(Z), R2(Y), W2(Y), R3(Y), R1(X), W1(X), W1(Z), W3(Y), R2(X), R1(Y), W1(Y), W2(X), R3(W),

W3(W)]

Where Ri(Z)/Wi(Z) indicates a read/write by transaction i on data item Z.

(b) Would it be sensible to produce a concurrency control algorithm based on serializability? Justify your answer. How

is serializability used in standard concurrency control algorithms?

22.21 (a) Discuss how you would test for view serializability using a labeled precedence graph.

(b) Using the previous method, determine whether the following schedules are conflict serializable:

(i) S1 = [R1(X), W2(X), W1(X)]

(ii) S2 = [W1(X), R2(X), W3(X), W2(X)]

(iii) S3 = [W1(X), R2(X), R3(X), W3(X), W4(X), W2(X)]

22.22 Produce a wait-for graph for the following transaction scenario and determine whether deadlock exists:

TRANSACTION

DATA ITEMS LOCKED

BY TRANSACTION

DATA ITEMS TRANSACTION

IS WAITING FOR

T1 x2 x1, x8

T2 x3, x10 x7

T3 x8 x4

T4 x7 x1

T5 x1, x5 x3

T6 x4, x9

22.23 Write an algorithm for shared and exclusive locking. How does granularity affect this algorithm?

22.24 Write an algorithm that checks whether the concurrently executing transactions are in deadlock.

22.25 Using the sample transactions given in Examples 22.1, 22.2, and 22.3, show how timestamping could be used to

produce serializable schedules.

22.26 Figure 22.22 gives a Venn diagram showing the relationships between conflict serializability, view serializability,

two-phase locking, and timestamping. Extend the diagram to include optimistic and multiversion concurrency

control. Further extend the diagram to differentiate between 2PL and strict 2PL, timestamping without Thomas’s

write rule, and timestamping with Thomas’s write rule.

22.27 Analyze the database schema for the DreamHome case study and describe the queries that should be grouped

together in a transaction.

22.28 Would it be correct to say that the work of maintaining database consistency and availability requires more than

just DBMS functionalities? Discuss the role of the DBA in database backup and recovery.

 

 

Chapter 23 Query Processing

Review Questions

23.1 How are query processing and query optimization related?

23.2 How does query processing in relational systems differ from the processing of low-level query languages for

network and hierarchical systems?

23.3 What are the typical phases of query processing?

23.4 What are the main phases of query processing?

23.5 What is the difference between conjunctive and disjunctive normal form?

23.6 Discuss how a DBMS reduces the search space.

23.7 State the transformation rules that apply to:

(a) Selection operations

(b) Projection operations

(c) Theta join operations

23.8 State the heuristics that should be applied to improve the processing of a query.

23.9 What types of statistics should a DBMS hold to be able to derive estimates of relational algebra operations?

23.10 Under what circumstances would the system have to resort to a linear search when implementing a Selection

operation?

23.11 What are the main strategies for implementing the Join operation?

23.12 Contrast cost estimation and heuristic rules with regard to query optimization.

23.13 Discuss the difference between linear and nonlinear relational algebra trees. Give examples to illustrate your answer.

23.14 What are the advantages and disadvantages of left-deep trees?

23.15 Describe how Oracle implements rule-based and cost-based optimization approaches.

Exercises

23.16 Using the three strategies cited in Example 23.1, describe the phases involved in query processing. In each of the

identified phases, briefly indicate what the output will be.

23.17 Using the Hotel schema given at the start of the Exercises at the end of Chapter 4, determine whether the

following

queries are semantically correct:

(a) SELECT r.type, r.price

FROM Room r, Hotel h

WHERE r.hotel_number 5 h.hotel_number AND h.hotel_name 5 ‘Grosvenor Hotel’ AND r.type . 100;

(b) SELECT g.guestNo, g.name

FROM Hotel h, Booking b, Guest g

WHERE h.hoteINo 5 b.hoteINo AND h.hotelName 5 ‘Grosvenor Hotel’;

(c) SELECT r.roomNo, h.hoteINo

FROM Hotel h, Booking b, Room r

WHERE h.hoteINo 5 b.hoteINo AND h.hoteINo 5 ‘H21’ AND b.roomNo 5 r.roomNo AND type 5 ‘S’

AND b.hoteINo 5 ‘H22’;

23.18 Again using the Hotel schema, draw a relational algebra tree for each of the following queries and use the

heuristic

rules given in Section 23.3.2 to transform the queries into a more efficient form. Discuss each step

and state any transformation rules used in the process.

(a) SELECT r.roomNo, r.type, r.price

FROM Room r, Booking b, Hotel h

WHERE r.roomNo 5 b.roomNo AND b.hoteINo 5 h.hoteINo AND

h.hotelName 5 ‘Grosvenor Hotel’ AND r.price . 100;

(b) SELECT g.guestNo, g.guestName

FROM Room r, Hotel h, Booking b, Guest g

WHERE h.hoteINo 5 b.hoteINo AND g.guestNo 5 b.guestNo AND h.hoteINo 5 r.hoteINo AND

h.hotelName 5 ‘Grosvenor Hotel’ AND dateFrom .5 ‘1-Jan-08’ AND dateTo ,5 ‘31-Dec-08’;

23.19 Using the Hotel schema, assume the following indexes exist:

  • a hash index with no overflow on the primary key attributes, roomNo/hoteINo in Room;
  • a clustering index on the foreign key attribute hoteINo in Room;
  • a B+-tree index on the price attribute in Room;
  • a secondary index on the attribute type in Room.

nTuples(Room) 5 10,000 bFactor(Room) 5 200

nTuples(Hotel) 5 50 bFactor(Hotel) 5 40

nTuples(Booking) 5 100,000 bFactor(Booking) 5 60

nDistincthotelNo(Room) 5 50

nDistincttype(Room) 5 10

nDistinctprice(Room) 5 500

minprice(Room) 5 200 maxprice(Room) 5 50

nLevelshotelNo(I) 5 2

nLevelsprice(I) 5 2 nLfBlocksprice(I) 5 50

(a) Calculate the cardinality and minimum cost for each of the following Selection operations:

S1: roomNo51 Ù hotelNo5‘H001’(Room)

S2: type5‘D’(Room)

S3: hotelNo5‘H02’(Room)

S4: price>100(Room)

S5: type5‘S’ Ù hotelNo5‘H03’(Room)

S6: type5‘S’ Ú price , 100(Room)

(b) Calculate the cardinality and minimum cost for each of the following Join operations:

J1: Hotel hotelNo Room

J2: Hotel hotelNo Booking

J3: Room roomNo Booking

J4: Room hotelNo Hotel

J5: Booking hotelNo Hotel

J6: Booking roomNo Room

(c) Calculate the cardinality and minimum cost for each of the following Projection operations:

PI: hotelNo(Hotel)

P2: hotelNo(Room)

P3: price(Room)

P4: type(Room)

P5: hotelNo, price(Room)

23.20 Modify the block nested loop join and the indexed nested loop join algorithms presented in Section 23.4.3 to

read (nBuffer − 2) blocks of the outer relation R at a time, rather than one block at a time.

 

Chapter 24 Distributed DBMSs—Concepts and Design

Review Questions

24.1 Explain what is meant by a DDBMS and discuss the motivation in providing such a system.

24.2 Compare and contrast a DDBMS with distributed processing. Under what circumstances would you choose a

DDBMS over distributed processing?

24.3 Discuss why processes used to design a centralized relational database are not the same as those for a distributed

relational database.

24.4 Discuss the advantages and disadvantages of a DDBMS.

24.5 What is the difference between a homogeneous DDBMS and a heterogeneous DDBMS? Under what circumstances

would such systems generally arise?

24.6 What are network protocols and why are they important?

24.7 What functionality do you expect in a DDBMS?

24.8 What is a multidatabase system? Describe a reference architecture for such a system.

24.9 One problem area with DDBMSs is that of distributed database design. Discuss the issues that have to be

addressed with distributed database design. Discuss how these issues apply to the global system catalog.

24.10 What are the differences between horizontal and vertical fragmentation schemes?

24.11 Describe alternative schemes for fragmenting a global relation. State how you would check for correctness to

ensure that the database does not undergo semantic change during fragmentation.

24.12 What are the differences between query optimization processes under centralized and distributed DBMSs. Why

is the latter thought to be complex?

24.13 A DDBMS must ensure that no two sites create a database object with the same name. One solution to this

problem is to create a central name server. What are the disadvantages with this approach? Propose an

alternative approach that overcomes these disadvantages.

24.14 What are parallel database management systems? Describe their architecture and the way they are applied in

organizations.

Exercises

A multinational engineering company has decided to distribute its project management information at the regional level

in mainland Britain. The current centralized relational schema is as follows:

Employee (NIN, fName, lName, address, DOB, sex, salary, taxCode, deptNo)

Department (deptNo, deptName, managerNIN, businessAreaNo, regionNo)

Project (projNo, projName, contractPrice, projectManagerNIN, deptNo)

WorksOn (NIN, projNo, hoursWorked)

Business (businessAreaNo, businessAreaName)

Region (regionNo, regionName)

where Employee contains employee details and the national insurance number NIN is the key.

Department contains department details and deptNo is the key. managerNIN identifies the employee who is

the manager of the department. There is only one manager for each department.

Project contains details of the projects in the company and the key is projNo. The project manager

is identified by the projectManagerNIN, and the department responsible for the project by

deptNo.

WorksOn contains details of the hours worked by employees on each project and (NIN, projNo) forms

the key.

Business contains names of the business areas and the key is businessAreaNo.

Region contains names of the regions and the key is regionNo.

Departments are grouped regionally as follows:

Region 1: Scotland Region 2: Wales Region 3: England

Information is required by business area, which covers: Software Engineering, Mechanical Engineering, and

Electrical Engineering. There is no Software Engineering in Wales and all Electrical Engineering departments are in England.

Projects are staffed by local department offices.

As well as distributing the data regionally, there is an additional requirement to access the employee data either by

personal information (by HR) or by work related information (by Payroll).

24.15 Draw an Entity–Relationship (ER) diagram to represent this system.

24.16 Using the ER diagram from Exercise 24.15, produce a distributed database design for this system, and include:

(a) a suitable fragmentation schema for the system;

(b) in the case of primary horizontal fragmentation, a minimal set of predicates;

(c) the reconstruction of global relations from fragments.

State any assumptions necessary to support your design.

24.17 Repeat Exercise 24.16 for the DreamHome case study documented in Appendix A.

24.18 Repeat Exercise 24.16 for the EasyDrive School of Motoring case study documented in Appendix B.2.

24.19 Prepare an action plan for the process of distributing the Wellmeadows case study database documented in

Appendix B.3.

24.20 In Section 24.5.1, when discussing naming transparency, we proposed the use of aliases to uniquely identify

each replica of each fragment. Provide an outline design for the implementation of this approach to naming

transparency.

24.21 Compare a distributed DBMS to which you have access against Date’s twelve rules for a DDBMS. For each rule

with which the system is not compliant, give reasons why you think there is no conformance to this rule.

 

 

Chapter 25 Distributed DBMSs—Advanced Concepts

Review Questions

25.1 In a distributed environment, locking-based algorithms can be classified as centralized, primary copy, or distributed.

Compare and contrast these algorithms.

25.2 There can be many types of failures in a distributed environment. Discuss how failure due to network partition is

handled by the distributed DBMS.

25.3 Outline two alternative two-phase commit topologies to the centralized topology.

25.4 Describe the term “non-blocking” and explain how it is related to two-phase and three-phase commit protocols.

25.5 Describe the protocol used to recover two-phase and three-phase commit in a distributed environment.

25.6 Specify the layers of distributed query optimization and detail the function of each layer.

25.7 Discuss the costs that need to be considered in distributed query optimization and discuss two different cost models.

25.8 Describe the distributed query optimization algorithms used by R* and SDD-1.

25.9 Briefly describe the distributed functionality of Oracle11g.

Exercises

25.10 You are the systems analyst for DreamHome. One of your responsibilities is to ensure that every business transaction

performs to the desired standards at all the sites. You have currently received complaints from site managers

that customers are experiencing difficulties in accomplishing their transactions. Enough details about the nature of

the failure of their transactions have not been provided to you. You are therefore required to prepare an investigation

plan that shall be approved by your manager. Indicate in your plan the possible cause of problems and how

you are going to approach the problem. Your plan should also guarantee that such problems will not occur again.

25.11 Give full details of the centralized two-phase commit protocol in a distributed environment. Outline the algorithms

for both coordinator and participants.

25.12 Give full details of the three-phase commit protocol in a distributed environment. Outline the algorithms for both

coordinator and participants.

25.13 Analyze the database application deployed at an organization of your choice and discover whether it is distributed

or centralized. Advice accordingly.

25.14 Consider five transactions T1, T2, T3, T4, and T5 with:

  • T1 initiated at site S1 and spawning an agent at site S2
  • T2 initiated at site S3 and spawning an agent at site S1
  • T3 initiated at site S1 and spawning an agent at site S3
  • T4 initiated at site S2 and spawning an agent at site S3
  • T5 initiated at site S3.

TRANSACTION

DATA ITEMS LOCKED BY

TRANSACTION

DATA ITEMS TRANSACTION IS

WAITING FOR

SITE INVOLVED

IN OPERATIONS

T1 x1 x8 S1

T1 x6 x2 S2

T2 x4 x1 S1

T2 x5 S3

T3 x2 x7 S1

T3 x3 S3

T4 x7 S2

T4 x8 x5 S3

T5 x3 x7 S3

Exercises | 873

The locking information for these transactions is shown in the following table.

(a) Produce the local wait-for graphs (WFGs) for each of the sites. What can you conclude from the local

WFGs?

(b) Using the example transactions, demonstrate how Obermarck’s method for distributed deadlock detection

works. What can you conclude from the global WFG?

 

 

Chapter 26 Replication and Mobile Databases

Review Questions

26.1 Explain the importance of data replication.

26.2 Identify the benefits of using replication in a distributed system.

26.3 Provide examples of typical applications that use replication.

26.4 Compare and contrast eager with lazy replication.

26.5 Describe the CAP theorem.

26.6 Compare and contrast the different types of data ownership models available in the replication environment.

Provide an example for each model.

26.7 Discuss the functionality required of a replication server.

26.8 Describe different ways of implementing a replication architecture.

26.9 Discuss how mobile database support the mobile worker.

26.10 Describe the functionality required of mobile DBMS.

26.11 Discuss the issues associated with mobile DBMSs.

26.12 Discuss the main replication schemes.

Exercises

26.13 The East African countries are working to implement a single visa system for tourists visiting a member country.

The visitors will be required to apply for a visa for one member country and once they have it, they will be

allowed to visit the other countries. You are contracted as a consultant to study and propose the appropriate

architecture or model to be used. You are required to prepare a technical presentation on the possible deployment

approaches. For each of the identified approach, discuss potential technological and operational challenges.

Your presentation should focus on approaches such as centralized, distributed, and mobile database.

26.14 You are requested to undertake a consultancy on behalf of the managing director of DreamHome to investigate

how mobile database technology could be used within the organization. The result of the investigation should

be presented as a report that discusses the potential benefits associated with mobile computing and the issues

associated with exploiting mobile database technology for an organization. The report should also contain a fully

justified set of recommendations proposing an appropriate way forward for DreamHome.

26.15 In Section 26.3.1 we discuss a majority consensus protocol and describe how secondary sites can form a new

epoch set, which is a majority of secondary sites. We left a detailed protocol to establish a new primary site as an

exercise. The exercise is to elaborate a protocol that an epoch set of secondary sites has to run to nominate a

new primary site based on the following information:

– Majorities overlap and hence there must be at least one member of the old epoch set in the new epoch set;

– If the member of the old and new epoch is the original primary copy, the site might still be out of date;

– If the new epoch does not contain the leader of the old epoch, the selection of the new primary copy may

depend on some parameters like available disk space or the site that is close to a consistent state.

26.16 Read one or more of the following papers and consider how the proposals address specific issues that arise in a

replicated environment:

Agrawal D., Alonso G., Abbadi A.E., and Stanoi I. (1997). Exploiting atomic broadcast in replicated databases

(extended abstract). In Proc 3rd Int. Euro-Par Conf. on Parallel Processing, Springer-Verlag, 496-503.

Gifford D.K. (1979). Weighted voting for replicated data. In Proc. 7th ACM Symp. on Operating Systems Principles.

ACM, 150-162.

Gray J., Helland P., O’Neil P.E., Shasha D., Jagadish H.V., and Mumick I.S, eds (1996). The dangers of replication

and a solution. Proceedings of the 1996 ACM SIGMOD International Conference on Management of Data. Montreal,

Quebec, Canada: ACM Press; 173-182.

Kemme B. and Alonso G. (2000). A new approach to developing and implementing eager database replication

protocols. ACM Trans. Database Syst. 25, 333-379.

Kemme B. and Alonso, G. (2000). Don’t be lazy, be consistent: Postgres-R, a new way to implement database

replication. VLDB ‘00: Proc. 26th Int. Conf. on Very Large Data Bases, Morgan.

Kemme B., Jimenez-Peris R., and Patino-Martinez, M. (2010). Database replication. In Synthesis Lectures on Data

Management. 2, 1-153.

Pedone F., Wiesmann M., Schiper A., Kemme B., and Alonso G. (2000). Understanding replication in databases

and distributed systems. ICDCS, 464-474.

Vogels W. (2009). Eventually consistent. Commun ACM, 52, 40-44.

Wiesmann M., Schiper A., Pedone F., Kemme B., and Alonso G. (2000). Database replication techniques: a three

parameter classification. Proc. 19th IEEE Symp. on Reliable Distributed Systems. IEEE Computer Society.

Chapter 27 Object-Oriented DBMSs—Concepts and Design

Review Questions

27.1 Describe the three generations of DBMSs.

27.2 Compare and contrast the different definitions of object-oriented data models.

27.3 Describe the main modeling component of the functional data model.

27.4 How does an ORDBMS differ from an OODBMS?

27.5 Contrast object-oriented data modeling with conceptual data modeling.

27.6 How does this single-level storage model affect data access?

27.7 Describe the main strategies that can be used to create persistent objects.

27.8 What is pointer swizzling? Describe the different approaches to pointer swizzling.

27.9 Describe the differences between various database benchmarks.

27.10 Discuss why version management may be a useful facility for some applications.

27.11 Discuss why schema control may be a useful facility for some applications.

27.12 Describe the different architectures for an OODBMS.

27.13 List the advantages and disadvantages of an OODBMS.

27.14 Describe how relationships can be modeled in an OODBMS.

27.15 Describe the different modeling notations in UML.

27.16 How does a sequence diagram from a class diagram?

27.17 Describe how use case diagrams are utilized in a database design.

Exercises

27.18 For the DreamHome case study documented in Appendix A, suggest attributes and methods that would be

appropriate for Branch, Staff, and PropertyForRent classes.

27.19 Produce use case diagrams and a set of associated sequence diagrams for the DreamHome case study documented

in Appendix A.

27.20 Produce use case diagrams and a set of associated sequence diagrams for the University Accommodation Office

case study documented in Appendix B.1.

27.21 Produce use case diagrams and a set of associated sequence diagrams for the Easy Drive School of Motoring case

study documented in Appendix B.2.

27.22 Analyze any three DBMSs and discover how object-oriented features are implemented. Prepare a critical report

on the contrasting and common features provided by them.

27.23 You have been asked by the Managing Director of DreamHome to investigate and prepare a report on the

applicability of an OODBMS for the organization. The report should compare the technology of the RDBMS

with that of the OODBMS, and should address the advantages and disadvantages of implementing an OODBMS

within the organization, and any perceived problem areas. Finally, the report should contain a fully justified set of

conclusions on the applicability of the OODBMS for DreamHome.

27.24 For the relational Hotel schema in the Exercises at the end of Chapter 4, suggest a number of methods that may

be applicable to the system. Produce an object-oriented schema for the system.

27.25 Analyze the University Accommodation Office case study documented in appendix B.1 and advice whether it is

justifiable to adopt an object-oriented approach.

27.26 For the relational Library schema in the Exercises at the end of Chapter 5, suggest a number of methods that

may be applicable to the system. Produce an object-oriented schema for the system.

27.27 Produce a use case diagram and a set of associated sequence diagrams to illustrate your understanding of the

single visa system for East African countries case presented in Exercise 26.13.

27.28 Produce an object-oriented database design for the University Accommodation Office case study presented in

Appendix B.1. State any assumptions necessary to support your design.

27.29 Produce an object-oriented database design for the EasyDrive School of Motoring case study presented in

Appendix B.2. State any assumptions necessary to support your design.

27.30 Produce an object-oriented database design for the Wellmeadows Hospital case study presented in Appendix B.3.

State any assumptions necessary to support your design.

27.31 Repeat Exercises 27.22 to 27.28 but produce a schema using the functional data model. Diagrammatically

illustrate each schema.

27.32 Using the rules for schema consistency given in Section 27.4.3 and the sample schema given in Figure 27.11,

consider each of the following modifications and state what the effect of the change should be to the schema:

(a) adding an attribute to a class;

(b) deleting an attribute from a class;

(c) renaming an attribute;

(d) making a class S a superclass of a class C;

(e) removing a class S from the list of superclasses of a class C;

(f) creating a new class C;

(g) deleting a class;

(h) modifying class names.

 

Chapter 28 Object-Oriented DBMSs—Standards and Systems

Review Questions

28.1 Discuss the main concepts of the ODMG Object Model. Give an example to illustrate each of the concepts.

28.2 Describe ODMG-ODL. How does it differ from ODMG-OML?

28.3 Describe the process of mapping conceptual design to logical (object-oriented) design.

28.4 How does the ODMG GROUP BY clause differ from the SQL GROUP BY clause? Give an example to illustrate

your answer.

28.5 How does the ODMG aggregate functions differ from the SQL aggregate functions? Give an example to illustrate

your answer.

28.6 Describe the architecture and components of ObjectStore.

28.7 Briefly discuss how the ODMG C++ language binding works.

Exercises

28.8 Map the object-oriented database design for the Hotel case study produced in Exercise 27.22 to the ODMG

ODL and then show how the following queries would be written in OQL:

(a) List all hotels.

(b) List all single rooms with a price below £20 per night.

(c) List the names and cities of all guests.

(d) List the price and type of all rooms at the Grosvenor Hotel.

(e) List all guests currently staying at the Grosvenor Hotel.

(f ) List the details of all rooms at the Grosvenor Hotel, including the name of the guest staying in the room, if the

room is occupied.

(g) List the guest details (guestNo, guestName, and guestAddress) of all guests staying at the Grosvenor Hotel.

Compare the OQL answers with the equivalent relational algebra and relational calculus expressions of Exercise 5.12.

28.9 Map the object-oriented database design for the Project case study produced in Exercise 27.23 to the ODMG

ODL and then show how the following queries would be written in OQL:

(a) List all employees.

(b) List all the details of employees who are female.

(c) List the names and addresses of all employees who are managers.

(d) Produce a list of the names and addresses of all employees who work for the IT department.

(e) Produce a list of the names of all employees who work on the SCCS project.

(f ) Produce a complete list of all managers who are due to retire this year, in alphabetical order of surname.

(g) Find out how many employees are managed by “James Adam.”

(h) Produce a report of the total hours worked by each employee.

(i ) For each project on which more than two employees worked, list the project number, project name, and the

number of employees who work on that project.

(j ) List the total number of employees in each department for those departments with more than 10 employees.

Create an appropriate heading for the columns of the results table.

Compare the OQL answers with the equivalent relational algebra and relational calculus expressions of Exercise 5.

28.10 Map the object-oriented database design for the Library case study produced in Exercise 27.24 to the ODMG

ODL and then show how the following queries would be written in OQL:

(a) List all book titles.

(b) List all borrower details.

(c) List all book titles published in the year 2012.

(d) List all copies of book titles that are available for borrowing.

(e) List all copies of the book title “Lord of the Rings” that are available for borrowing.

(f ) List the names of borrowers who currently have the book title “Lord of the Rings” on loan.

(g) List the names of borrowers with overdue books.

(h) How many copies of ISBN “0-321-52306-7” are there?

(i ) How many copies of ISBN “0-321-52306-7” are currently available?

(j) How many times has the book title with ISBN “0-321-52306-7” been borrowed?

(k) Produce a report of book titles that have been borrowed by “Peter Bloomfield”.

(l ) For each book title with more than three copies, list the names of library members who have borrowed

them.

(m) Produce a report with the details of borrowers who currently have books overdue.

(n) Produce a report detailing how many times each book title has been borrowed.

Compare the OQL answers with the equivalent relational algebra and relational calculus expressions of Exercise 5.

28.11 Using a DBMS of your choice, create an object-oriented database of the ODMG-ODL schema produced in

question 28.8.

28.12 Map the object-oriented database design for the University Accommodation Office case study produced in

Exercise 27.26 to the ODMG ODL.

28.13 Map the object-oriented database design for the EasyDrive School of Motoring case study produced in

Exercise 27.27 to the ODMG ODL.

28.14 Map the object-oriented database design for the Wellmeadows case study produced in Exercise 27.28 to the

ODMG ODL.

Chapter 29 Web Technology and DBMSs

 

Review Questions

29.1 Discuss each of the following terms:

(a) Internet, intranet, and extranet

(b) World Wide Web

(c) HyperText Transfer Protocol (HTTP)

(d) HyperText Markup Language (HTML)

(e) Uniform Resource Locators (URLs)

29.2 What are web-based applications? How are they different from web services?

29.3 Discuss the advantages and disadvantages of the Web as a database platform.

29.4 Why is universal data access utilized? Briefly describe the role of each technology involved in the process?

29.5 Describe static and dynamic web pages. How are dynamic web pages created?

29.6 Discuss the following approaches to persistence:

(a) Container-Managed Persistence (CMP)

(b) Bean-Managed Persistence (BMP)

(c) JDBC

(d) SQLJ

(e) JDO

(f) JPA

29.7 Discuss the differences between ASP and JSP.

29.8 How does JDBC differ from SQLJ?

29.9 Discuss the components of Oracle’s Web Platform.

Exercises

29.10 Web database applications can be expensive. Prepare a rough budget indicating all cost areas for establishing and

maintaining the University Accommodation Office system.

29.11 The University Accommodation Office system is going to be moved to a web database driven application. Prepare a

proposal of requirements that the management has to meet.

29.12 Using an approach to Web–DBMS integration, create a series of forms that display the base tables of the

DreamHome case study.

29.13 Using the sequence diagram for the DreamHome case study, create web forms and scripted pages that will be

used by customers to complete their transactions.

29.14 Create Web pages to display the results of the queries given in Appendix A for DreamHome.

29.15 Repeat Exercises 29.12 to 29.14 for the Wellmeadows case study.

29.16 Create Web pages to display the results of the queries given in Chapter 6, Exercises 6.7–6.28.

29.17 Using any Web browser, look at some of the following Web sites and discover the wealth of information held

there:

(a) W3C http://www.w3.org

(b) Microsoft http://www.microsoft.com

(c) Oracle http://www.oracle.com

(d) IBM http://www.ibm.com

(e) Sun (Java) http://java.sun.com

(f) WS-I http://www.oasis-ws-i.org/

(g) JDOCentral http://db.apache.org/jdo/jdocentral.html

(h) OASIS http://www.oasis-open.org

(i ) XML.com http://www.xml.com

(j) GemStone http://www.gemstone.com

(k) Objectivity http://www.objectivity.com

(l) ObjectStore http://www.objectstore.net

(m) ColdFusion http://www.adobe.com/products/coldfusion/

(n) Apache http://www.apache.org

(o) mySQL http://www.mysql.com

(p) PostgreSQL http://www.postgresql.com

(q) Perl http://www.perl.com

(r) PHP http://www.php.net

29.18 You have been asked by the Managing Director of DreamHome to investigate and prepare a report on the feasibility

of making the DreamHome database accessible from the Internet. The report should examine the technical

issues, the technical solutions, address the advantages and disadvantages of this proposal, and any perceived

problem areas. The report should contain a fully justified set of conclusions on the feasibility of this proposal for

DreamHome.

 

Chapter 30 Semistructured Data and XML

Review Questions

30.1 What is semistructured data? Discuss the differences between structured, semistructured, and structured data.

Give examples to illustrate your answer.

30.2 Describe the key characteristics of the Object Exchange Model (OEM).

30.3 What is XML and how does XML compare to SGML and HTML?

30.4 Discuss the technologies associated with XML.

30.5 What is the difference between a well-formed XML document and a valid XML document?

30.6 Briefly describe each of the following technologies:

(a) DOM and SAX;

(b) Namespaces;

(c) XSL and XSLT;

(d) XPath;

(e) XPointer;

(f ) XLink;

(g) XHTML;

(h) SOAP;

(i) WSDL;

(j) UDDI.

30.7 Describe the differences between the Document Object Model (DOM) and the Object Exchange Model (OEM).

30.8 Describe the differences between the Document Type Definition (DTD) and the XML Schema.

30.9 Discuss how the combination of XML and XML Schema may not provide the support for semantic interoperability

that we require and how the proposals for RDF and RDF Schema may be more appropriate.

30.10 Briefly describe the W3C proposals for XQuery and the specifications that make up the language.

30.11 Describe the RDF data model.

30.12 Discuss how Oracle supports XML.

30.13 Describe how relations can be mapped to an XML document.

30.14 Briefly describe the purpose and content of the SQL 2011 standard.

30.15 Discuss how XML can be transferred into a database.

30.16 What is a native XML database?

Exercises

30.17 Create an XML document for each of the relations shown in Figure 4.3.

30.18 Explain the role of the key concepts in the XML documents you created in Exercise 30.17.

30.19 Now for each of the documents created above, create an appropriate DTD and XML Schema. Use name-spaces

where appropriate to reuse common declarations. Try to model multiplicity, primary and foreign keys, and alternate

keys, where appropriate. What can you conclude from these tests?

30.20 In Example 30.8, we created an XML Query Data Model for the XML document of Figure 30.22(a). Now create

an XML Query Data Model for the corresponding XML Schema of Figure 30.22(b).

30.21 Create an XML Query Data Model for each of the XML documents created previously.

30.22 Create an XML document and XML Schema for the Hotel Schema given in the Exercises at the end of Chapter 4.

Now attempt to write XQuery expressions for Exercises 6.7–6.26.

30.23 Create an XML document and XML Schema for the Projects Schema given in the Exercises at the end of Chapter 5.

Now attempt to write XQuery expressions for Exercises 6.32–6.40.

30.24 Create an XML document and XML Schema for the Library Schema given in the Exercises at the end of Chapter 5.

Now attempt to write XQuery expressions for Exercises 6.41–6.54.

30.25 For any DBMS that you have access to, investigate the XML functionality supported by the DBMS.

30.26 For any DBMS that supports XML, transfer the XML documents created in Exercise 30.17 into the database.

Examine the structure of the relations created.

Chapter 31 Data Warehousing Concepts

Review Questions

31.1 Discuss what is meant by the following terms when describing the characteristics of the data in a data warehouse:

(a) subject-oriented;

(b) integrated;

(c) time-variant;

(d) nonvolatile.

31.2 Discuss how online transaction processing (OLTP) systems differ from data warehousing systems.

31.3 Discuss the main benefits and problems associated with data warehousing.

31.4 Data warehouse architecture consists of many components. Explain the role of each component shown in Figure 31.1.

31.5 Describe the main functions of the following components in a data warehousing environment:

(a) Metadata repository;

(b) Temporal database;

(c) ETL tools;

(d) Parallel DMBSs;

(e) Enterprise warehouse.

31.6 Describe the processes associated with data extraction, cleansing, and transformation tools.

31.7 Describe the specialized requirements of a database management system suitable for use in a data warehouse

environment.

31.8 Discuss how parallel technologies can support the requirements of a data warehouse.

31.9 Describe real-time and near-real-time data warehouse. What are the challenges in realizing an RT/NRT warehouse?

31.10 Discuss the main tasks associated with the administration and management of a data warehouse.

31.11 Discuss how data marts differ from data warehouses and identify the main reasons for implementing a data mart.

31.12 Describe the features of Oracle that support the core requirements of data warehousing.

Exercises

31.13 Oracle supports data warehousing by producing a number of required functional tools. Analyze three more

DBMSs that provide data warehousing functionalities. Compare and contrast the functionalities provided by

different vendors and write a technical report describing the strengths and weaknesses of each DBMS when it

comes to features, capability, usability and appropriateness. Conclude your report by recommending one DBMS.

31.14 The purpose of this exercise is to present a scenario that requires you to act in the role of business intelligence

(BI) consultant for your university (or college) and produce a report to guide management on the opportunities

and issues associated with the business intelligence.

The scenario The senior management team of a university (or college) have just completed a five-year

plan to implement university-wide computing systems to support all core business processes such as a student

management information system, payroll and finance system, and resource management including class timetabling

system. Accompanying this expansion in the use of computer systems has been a growing accumulation

of transactional data about the university business processes, and senior management are aware of the potential

value of the information hidden within this data. In fact, senior management of the university have a long-term

goal to provide key decision makers throughout the university with BI tools that would allow them to monitor

key performance indicators (KPIs) on their desktops. However, senior management acknowledge that there are

many milestones that need to be put in place to achieve this goal. With this in mind, it is your remit to assist

management in identifying the technologies and the barriers and opportunities that exist for the university to put

in place the necessary infrastructure to ultimately deliver BI to key decision makers.

Undertake an investigation, using initially the material presented in this chapter, and then supplement this information,

using external sources such as vendor Web sites (e.g., www.ibm.com, www.microsoft.com, www.oracle

.com, www.sap.com) or data warehouse/BI Web sites (e.g., www.information-management.com, www.tdwi.org,

www.dwinfocenter.org) to investigate one (or all) of the following three tiers of the data warehouse environment

namely: source systems and the ETL process; data warehouse and OLAP; end-user BI tools. Compile a

report for senior management that details for each tier:

(a) The purpose and importance (including the relationship to the other tiers);

(b) Opportunities and benefits;

(c) Associated technologies;

(d) Commercial products;

(e) Problems and issues;

(f ) Emerging trends.

 

 

Chapter 32 Data Warehousing Design

 

Review Questions

32.1 Discuss the activities associated with initiating an enterprise data warehouse (EDW) project.

32.2 Compare and contrast the approaches taken in the development of an EDW by Imon’s Corporate Information

Factory (CIF) and Kimball’s Business Dimensional Lifecycle.

32.3 Discuss the main principles and stages associated with Kimball’s Business Dimensional Lifecycle.

32.4 Discuss the concepts associated with dimensionality modeling.

32.5 What are the advantages offered by the dimensional model?

32.6 Discuss the phased approach used in the DM stage of Kimball’s Business Dimensional Lifecycle.

32.7 Discuss the criteria used to select the business process in Phase I of the DM stage of Kimball’s Business

Dimensional

Lifecycle.

32.8 Identify the particular issues associated with the development of an enterprise data warehouse.

32.9 How does Oracle Warehouse Builder assist the user in data warehouse development and administration?

Exercises

Please note that all of the exercises listed refer to the DM shown in Figure 32.2.

32.10 Identify three types of analysis that the DM can support about property sales.

32.11 Identify three types of analysis that the DM cannot support about property sales.

32.12 Discuss how you would change the DM to support the queries identified in Exercise 32.11.

32.13 What is the granularity of the fact table shown in the property sales DM?

32.14 What is the purpose of the fact table and dimension tables shown in the property sales DM?

32.15 Identify an example of a derived attribute in the fact table and describe how it is calculated. Are there any others

that you could suggest?

32.16 Identify two examples of natural and surrogate keys in the property sales DM and discuss the benefits associated

with using surrogate keys in general.

32.17 Identify two possible examples of SCD in the property sales DM and discuss the types of change (Type 1 or

Type 2) that each represents.

32.18 Identify the dimensions that make the property sales DM a star schema, rather than a snowflake schema.

32.19 Select one dimension from the DM to demonstrate how you would change the DM into a snowflake schema.

32.20 Examine the bus matrix for a university shown in Figure 32.12. The university is organized as schools such as

the School of Computing, School of Business Studies, and each school has a portfolio of programs and modules.

Students apply to the university to join a program but only some of those applications are successful. Successful

applicants enroll in university programs, which are made up of six modules per year of study. Student attendance

at module classes is monitored, as well as student results for each module assessment.

(a) Describe what the matrix shown in Figure 32.12 represents.

(b) Using the information shown in the bus matrix of Figure 32.12, create a first-draft, high-level dimensional model

to represent the fact tables and dimensions tables that will form the data warehouse for the university.

(c) Using the information in Figure 32.12 produce a dimensional model as a star schema for the student module

results business process. Based on your (assumed) knowledge of this business process as a current or past

student, add a maximum of five (possible) attributes to each dimension table in your schema. Complete your

star schema by adding a maximum of 10 (possible) attributes to your fact table. Describe how your choice of

attributes can support the analysis of student results.

32.21 Examine the dimensional model (star schema) shown in Figure 32.13. This model describes part of a database

that will provide decision support for a taxi company called FastCabs. This company provides a taxi service to

clients who can book a taxi either by phoning a local office or online through the company’s Web site.

The owner of FastCabs wishes to analyze last year’s taxi jobs to gain a better understanding of how to resource

the company in the coming years.

(a) Provide examples of the types of analysis that can be undertaken, using the star schema in Figure 32.13.

(b) Provide examples of the types of analysis that cannot be undertaken, using the star schema in Figure 32.13.

(c) Describe the changes that would be necessary to the star schema shown in Figure 32.13 to support the

following analysis. (At the same time consider the possible changes that would be necessary to the transaction

system providing the data.)

  • Analysis of taxi jobs to determine whether there is an association between the reasons why taxi jobs are

cancelled and the age of clients at the time of booking.

  • Analysis of taxi jobs according to the time drivers have worked for the company and the total number and

total charge for taxi jobs over a given period of time.

  • Analysis of taxi jobs to determine the most popular method of booking a taxi and whether there are any

seasonal variations.

  • Analysis of taxi jobs to determine how far in advance bookings are made and whether there is an association

with the distance traveled for jobs.

  • Analysis of taxi jobs to determine whether there are more or less jobs at different weeks of the year and

the effect of public holidays on bookings.

(d) Identify examples of natural and surrogate keys in the star schema shown in Figure 32.13 and describe the

benefits associated with using surrogate keys in general.

(e) Using examples taken from the dimensional model of Figure 32.13, describe why the model is referred to as a

“star” rather than a “starflake” schema.

(f ) Consider the dimensions of Figure 32.13 and identify examples that may suffer from the slowly changing

dimension problem. Describe for each example, whether type I, II, or III would be the most useful approach

for dealing with the change.

 

Chapter 33 OLAP

Review Questions

33.1 Discuss what online analytical processing (OLAP) represents.

33.2 Discuss the relationship between data warehousing and OLAP.

33.3 Describe OLAP applications and identify the characteristics of such applications.

33.4 What key characteristics are used to categorize different OLAP servers?

33.5 Describe Codd’s rules for OLAP tools.

33.6 Describe the architecture, characteristics, and issues associated with each of the following categories of OLAP tools:

(a) MOLAP

(b) ROLAP

(c) HOLAP

(d) DOLAP

33.7 Discuss how OLAP functionality is provided by the ROLLUP and CUBE functions of the SQL standard.

33.8 OLAP implementation faces many challenges. Discuss critical OLAP implementation issues and how they are

addressed

at present.

Exercises

33.9 You are asked by the Managing Director of DreamHome to investigate and report on the applicability of OLAP

for the organization. The report should describe the technology and provide a comparison with traditional

querying and reporting tools of relational DBMSs. The report should also identify the advantages and disadvantages,

and any problem areas associated with implementing OLAP. The report should reach a fully justified set of

conclusions on the applicability of OLAP for DreamHome.

33.10 Investigate whether your organization (such as your university/college or workplace) has invested in OLAP technologies

and, if yes, whether the OLAP tool(s) forms part of a larger investment in business intelligence technologies.

If possible, establish the reasons for the interest in OLAP, how the tools are being applied, and whether the

promise of OLAP has been realized.

 

Chapter 34 Data Mining

Review Questions

34.1 Discuss what data mining represents.

34.2 Provide examples of data mining applications.

34.3 Describe how the following data mining operations are applied and provide typical examples for each:

(a) predictive modeling,

(b) database segmentation,

(c) link analysis,

(d) deviation detection.

34.4 Describe the main aims and phases of the CRISP-DM model.

34.5 What are the roles of the main components of business intelligence?

34.6 Discuss the relationship between data warehousing and data mining.

34.7 Describe the Oracle Data Mining environment.

Exercises

34.8 Consider how a company such as DreamHome could benefit from data mining. Discuss, using examples, the data

mining operations that could be most usefully applied within DreamHome.

34.9 Investigate whether your organization (such as your university/college or workplace) has invested in data mining

technologies and, if so, whether the data mining tool(s) forms part of a larger investment in business intelligence

technologies. If possible, establish the reasons for the interest in data mining, how the tools are being applied, and

whether the promise of data mining has been realized.

 

 

 

Reviews

There are no reviews yet.

Be the first to review “Database Systems A Practical Approach to Design, Implementation, And Management SIXth Edition Global Edition By Thomas Connolly – Solution Manual”

Your email address will not be published. Required fields are marked *