3B

Review the Tiny College case using the Free Access to Current Technology or FACT, at the end of Chapter 7 in the textbook (pp. 340–358) from the perspective of a database administrator assigned to build this database. Select one of the entities shown in the ERD for Tiny College's database in Figure P7.56 on page 341. What data types would you use for the attributes in this entity? How would you code a CREATE TABLE statement to implement this entity as a table in the database?

Continue examining the Tiny College case at the end of Chapter 7 in the textbook (pp. 340-358), but this time from the perspective of a manager in the organization.

What is one example of a report from the database that a manager might want to receive? What data items would appear on the report, and what attributes and entities would these data items come from? How would you code a SQL SELECT query to retrieve this information?

The CIS Department at Tiny College maintains the Free Access to Current Technology (FACT) library of e-books. FACT is a collection of current technology e-books for use by faculty and students. Agreements with the publishers allow patrons to electronically check out a book, which gives them exclusive access to the book online through the FACT website, but only one patron at a time can have access to a book. A book must have at least one author but can have many. An author must have written at least one book to be included in the system but may have written many. A book may have never been checked out but can be checked out many times by the same patron or different patrons over time. Because all faculty and staff in the department are given accounts at the online library, a patron may have never checked out a book or they may have checked out many books over time. To simplify determining which patron currently has a given book checked out, a redundant relationship between BOOK and PATRON is maintained. The ERD for this system is shown in Figure P7.56 and should be used to answer the next several problems. For Problems 57-109, a figure of the correct output is provided for each problem. If the output of the query is very large, only the first several rows of the output are shown.

The BOOK table will look something like below. I have filled the same with dummy data.

;

The query that displays the book title, cost and year of publication for every book in the system with the results sorted by book title is as below

SELECT BOOK_TITLE, BOOK_COST, BOOK_YEAR FROM BOOK ORDER BY BOOK_TITLE.

The results of the query will be as below