Database Management Systems

© 2007 by Jerry Post

Database Exercises

Chapter 1 2 3 4 5 6 7 8 9 10 11 12

1. Research the distributed database capabilities of Oracle. At a minimum, specify how the DBMS identifies other databases and how it handles locking and concurrency issues.
2. Assuming your DBMS cannot generate distributed safe keys automatically write a procedure to generate key values based on a location.
3. Create a sample XML document (without the dtd) for a simple order form (Order, OrderItems). Research the syntax of XQuery to write the command to list all of the items ordered for a specific OrderID value.
4. You have the following distributed databases. You are working for an accounting firm with headquarters in Madrid and major offices in London, Paris, and Frankfort. Many of the client companies have offices in three or four of these cities. Some clients are smaller and work with a single office. The accounting teams in the various offices need to share documents with teams in the other offices when they are working for the same client. Each office maintains a database of working papers, spreadsheets, questions, answers, and workflow data for the team. It also tracks billable hours for each employee and client. You need to get a list of all employees who have worked for a particular client in the last month; along with hours worked. Based on the communication speeds and table sizes, design the best performing query to answer this question. Could the database and query performance be improved by changing the distributed design?

   Location	Link Speed	Tables	Sizes
   London	53 kbps	Contact(ContactID, Name, ClientID, Title, Phone) Employee(EmployeeID, Name, Phone, Title) WorkHours(WorkID, EmployeeID, ClientID, Date, Hours)	5,000 rows 2,300 rows 3,000,000 rows
   Paris	1.544 mbps	Contact(ContactID, Name, ClientID, Title, Phone) Employee(EmployeeID, Name, Phone, Title) WorkHours(WorkID, EmployeeID, ClientID, Date, Hours)	10,000 rows 1,000 rows 20,000,000 rows
   Frankfort	128 kbps	Contact(ContactID, Name, ClientID, Title, Phone) Employee(EmployeeID, Name, Phone, Title) WorkHours(WorkID, EmployeeID, ClientID, Date, Hours)	7,000 rows 3,500 rows 30,000,000 rows
   Madrid (HQ)	local	Client(ClientID, Name, Lead contact, Main city) Employee(EmployeeID, Name, Phone, Title) Project(ProjectID, ClientID, StartDate, Topic)	20,000 rows 10,000 rows 1,000,000 rows

5. A company has two satellite offices about 100 miles apart in the same state. Main data processing is handled at a data center about 1000 miles away. Both offices are connected to the main site with a T1 (1.544 mbps) line. A manager wants you to develop a database to be used by employees at both offices. The database will track customer interactions. Each local employee (about 10 at each location) will track his or her clients (about 50 active clients per employee at any point in time), and the data will be reviewed by the manager once a month. What is the best distributed design for this database?
6. You are hired as a consultant to a firm with plants in several different states. At best, the plants are connected by a T1 line at 1.544 mbps, a few are running ISDN connections at 128 kbps. The application maintains detailed data about each plant’s operations, as well as prices, rules, and procedures created at the headquarters. The application performs complex processing for each plant and generates large amounts of data that is used by both the plants and specialists at headquarters. The company wants the application to run on an Oracle database, with processing handled by Visual Basic. Set up the basic structure of the database. Which parts should be distributed? Which parts centralized?
7. Find at least two major tools that support data-driven Web sites and explain how they separate database code from page layout and design.

Sally’s Pet Store

8. Sally is planning to add a second store. Write a plan that describes how the data will be shared. How will you control and monitor the new system? Which tools will you add?
9. Sally wants to connect to some of the breeders so that she can get up-to-date information on their animals—including health and genealogy records. Explain how you would set up a system to enable this sharing of data.
10. Create a Web site so Sally can let potential customers search for animals based on category and breed.
11. Use the tables for a different DBMS, or create them if necessary. Try to connect your primary DBMS to the new tables. Write a query that pulls data from both DBMSs.

Rolling Thunder Bicycles

12. Rolling Thunder is planning to expand to a second location across the country. How should the database be distributed? Where should each table be stored? Which tables should be replicated, and how should the data changes be reconciled?
13. Rolling Thunder is planning to expand by sending sales representatives around the country to various bike shops. They will have laptops to configure and take new orders. But, most bike shops will not have Internet connections, so the system will have to work offline. Describe how this system will work. What security provisions will be needed?
14. Build a Web form that enables customers to check on the progress of their bicycle orders.
15. Create a second copy of your database running on a second computer. Create a link from the first database to the copy. Write a query that combines data from at least one table in each database.

Corner Med

16. The company owners basically want to franchise the operations. The headquarters will run database operations for all of the local clinics. Describe how you will configure the database to support this operational process. List any potential problems you might encounter.
17. Using one DBMS, research the capabilities for replicating data. Build at least one replica of a table, make changes to both copies, and synchronize the copies. Describe how the system handles generated keys.
18. What data concurrency issues are likely to arise with this database? Discuss how concurrency issues might differ if the system is run locally versus from a central server that supports multiple sites.