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

<< Database Concepts 6th Edition By kroenke Test Bank Dosage Calculations 3rd Canadian Edition by Amy Pickar-Abernethy Test bBank >>
Product Code: 222
Availability: In Stock
Price: $24.99
Qty:     - OR -   Add to Wish List
Add to Compare

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

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 systems 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 clientserver architecture and what are the advantages of this

approach? Compare the clientserver architecture with two other architectures.

3.2 Compare and contrast the two-tier clientserver architecture for traditional DBMSs with the

three-tier clientserver 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 IBMs DB2 system to

identify their support for the following:

(a) clientserver 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.76.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 systems 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.326.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 198090.

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.416.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 todays 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 clients

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 clients 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 EntityRelationship 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 EntityRelationship model and the EntityRelationship 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 students

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 EntityRelationship 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 entityrelationship 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 visitors 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 suscept

Write a review

Your Name:


Your Review: Note: HTML is not translated!

Rating: Bad           Good

Enter the code in the box below:



 

Once the order is placed, the order will be delivered to your email less than 24 hours, mostly within 4 hours. 

If you have questions, you can contact us here