|
Prior Exam
Name: __________________________________ |
70-455 |
Make sure you answer all parts of each question.
1. You are the database administrator for the Carnegie Library System. The database contains the following information.
Double underlining indicates a primary key that is also a foreign key.
Branch(BranchID, Address, City)
Book(Call#, Seq#, BranchID, PatronID, DueDate)
BookTitle(Call#, Title)
TitleAuthor(Call#, AuthorName)
TitleSubject(Call#, Subject)
Patron(PatronID, Name, Address, BranchID, FinesDue)
Seq# is a unique number for each copy of the same book.
BranchID in Patron refers to the branch where a person got his/her library card.
The row totals are as follows:
|
Table |
# Rows |
|
Branch |
19 |
|
Book |
125,000,000 |
|
BookTitle |
12,500,000 |
|
TitleAuthor |
17,500,000 |
|
TitleSubject |
50,000,000 |
|
Patron |
350,000 |
Currently, the database is centralized and standard disk storage is being used.
The Director of the Library System has come to you because they are experiencing performance problems. Patrons are calling for the return of the card catalog system saying that it would be faster than the computer system that they use to locate their books.
1A. Describe a method that would modify the way the data is stored, keep the database centralized, and improve performance. (10 points)
1B. Describe how you could use a distributed database, without replicating any of the data, to improve performance. How and where would you distribute the data? What are the drawbacks to your suggested distribution? Which drawbacks could you eliminate by replicating the data? What data would you replicate? What new concerns would be raised? (25 points)
1C. After spending large sums of money to implement a RAID-5 disk subsystem to increase performance and reliability, the Director tells you that she thinks eliminating the database logging could increase performance even more and wouldn't cause problems because RAID-5 guarantees recovery from disk failure. Evaluate this statement and provide arguments for retaining the database log. (10 points)
The Director has read a few books on SQL and written the following query. She wants a count of the number of over-due books on the subjects related to procrastination checked out by people who have more than $5 in fines. Currently, she manually counts the books by person that are returned. She complains to you that it "never seems to finish." You also discover that the last time she ran the query was when there were complaints from almost every branch about the performance of the database.
SELECT *
FROM Branch as Br, Book as B, BookTitle as BT, TitleAuthor as TA,
TitleSubject as TS, Patron as P
WHERE Br.BranchID = B.BranchID AND B.Call# = BT.Call# AND
BT.Call# = TA.Call# AND BT.Call# = TS.Call# AND
B.PatronID = P.PatronID AND (B.Call#, Seq#) IN
(SELECT Call#, Seq#
FROM Book
Where DueDate < Today) AND
B.Call# IN
(SELECT Call#
FROM TitleSubject
WHERE Subject LIKE "%PROCRAST%")
AND
P.PatronID IN
(SELECT PatronID
FROM Patron
WHERE FinesDue > 5.00)
ORDER BY P.NAME
1D. Explain why this query suffers from performance problems. (10 points)
The Director decided to write this query after reading an article in Libraries Today that mentioned data mining and how libraries were barely scratching the surface when it came to getting useful information about their patrons.
1E. Briefly describe how the library could use data mining to get useful information. Would you recommend that a data warehouse also be established? Why or why not? (15 points)
2. You have just attended a vendor presentation on a new relational database management system. The salesperson claimed that they have totally eliminated the need for locking.
Rather than having to resort to locking to maintain concurrency control, the salesperson claims that their DBMS has a new transaction monitor that tracks all currently executing transactions and evaluates newly submitted transactions against those currently running. As long as the new transaction contains only SELECTs, it is started immediately. If the new transaction has any UPDATEs, INSERTs or DELETEs, the affected rows are compared against changes being completed by currently executing transactions. If the rows are different, the change is started. If there is a conflict, the new transaction must wait until the current one completes. When multiple transactions are waiting to change the same row, they are processed in a FIFO (first in first out) basis. All changes are immediately applied to the database, and all queries retrieve whatever data is in the database.
2A. Briefly discuss the effectiveness of the "transaction monitor" and give an example of a problem that could occur. Do you agree with the claim that locking has been "totally eliminated?" (15 points)
2B. Does this approach use deadlock prevention? Why? If not, does it require deadlock detection and recovery? Why? (10 points)
Logging performance has been increased in two ways. First, log records are stored in main memory, which eliminates the substantial delays associated with writing to the disk. Second, a separate log area in memory is maintained for each transaction. Immediate logging is used. If the transaction is committed, the log space is cleared. If the transaction is rolled back, the log entries are used to UNDO the transaction and then the log space is cleared. Because only main memory is used, unnecessary disk space isn’t wasted.
2C. Does this approach require checkpointing? Why or why not? (10 points)
2D. Describe a use of the log files, other than transaction rollback, that seems to have been overlooked by the software vendor. (15 points)
3. Below is documentation for a department store's bridal registry system.
A bridal registry is a method where a bride and groom go to a store and select items that they would like to receive as wedding presents. They then notify the people invited to their wedding at which store they have registered. The wedding guests can then go to the store and review the registry in order to select items that are listed on it. The advantage of this is that the bride and groom are given gifts that they would like to receive and the guests are able to get a gift that is desired. In the past, bridal registries have been used for items like fancy china dishware, crystal glasses and stemware, and sterling silverware. Guests could then buy something as inexpensive as a set of salt and pepper shakers or as expensive as several place settings of sterling silver. While still used in the fine china and silver departments of stores, bridal registries are increasingly also being used for things found in other departments of the store, like furniture, hardware, and power tools.
A department store has a bridal registry. This registry keeps information about the bride, the products that the store carries, and the products for which each bride registers. Some products include several related items: for example, dish sets, include plates, specialty dishes, and serving bowls. Brides typically register for a large number of products, and many brides register for the same products. A bride may also register for several of the same product but may register for different colors or types of each product. For example, a bride may register for bath towels, which typically come in sets (bath towel, hand towel, wash cloth), and may register for 4 blue bath towels, 4 blue/white striped bath towels, 8 blue hand towels, and 16 white wash cloths. The registry also must keep track of what registered items have been purchased for the bride so that a current list showing items registered for but not yet purchased can be produced for friends and family who come to the store. Information kept about brides includes name and finance's name, address and phone number. Product information includes product number, description, and store department in which the product can be found.
Brides (and grooms) come into the store and register for items that they would like to receive as wedding gifts, for example, a china pattern or sterling silver dinnerware set. The couple then notifies people who are invited to the wedding that they are "registered" at the store. Individuals wishing to purchase something for the couple then come into the store and are given a copy of the registry that shows items that have not yet been purchased. They can then purchase items on the list which then have to be marked as having been bought. For example, someone may come in and buy only one place setting of the bride's china. If eleven other people do the same thing, the bride will receive twelve full place settings of new china.
3. Assume the department store company has multiple locations throughout several adjoining states. Further assume, that even though someone registers at one location, friends and family may go to any of the store’s locations. Describe two methods of distributing the database. The first should include replication, and the second should not have any replication. Give four advantages and four disadvantages for each proposed distribution, and recommend the way the database should be distributed.