CSC 321/621 – Spring 2019
Test 1 – Take Home Portion
You may access textbooks and course materials but the WORK SUBMITTED MUST
BE DONE BY YOU AND YOU ALONE! No communication regarding these questions
with anyone real or virtual. No Exceptions. I reserve the right to ask you to explain
your responses orally if I have a question about your response.
I will try to clarify any questions you have. Send your questions in e-mail. I’ll broadcast a
response as quickly as possible. Please submit your responses on paper (printed or
handwritten) at the start of class on Tuesday. The deadline is 12:30pm next Tuesday, 2/19/19.
Setup:
Download the following files: Resources ? Test 1 Materials ? {yadkin.db, make-yadkin.sql} from Sakai.
You will need to open the yadkin.db database in SQLite Studio to write and check queries. The file named
make-yadkin.sql contains the SQL code used to create the database schema. You will only need to open it in
a text editor, not in SQLite Studio.
Explanations regarding Yadkin:
- Regarding the author table. The attribute“SEQUENCE_NUMBER”has to do with the order that the
authors are listed by when a book has multiple authors. Values can be 1, 2, …, for“first author”,“second
author”, … - The ISBN values are not realistic but don’t worry about it …
- The invent table name is short for“inventory.”It tracks the books available at a branch. Not all books
are available at all branches; furthermore, some books may not be available at any branch. - The book table has an attribute named PAPERBACK that will contain‘Y’if the book is available as a
paperback. The value may also be‘N’to indicate it’s a Hardback book or null to indicate unknown.
I. (10 pts) Open make-yadkin.sql in a text editor and/or use sqlite to answer the following questions. - List the names of the tables in the yadkin database along with the primary key of each table.
- List the foreign keys in the database, identifying both the source of the foreign key and the referenced
table and column(s). - Use the results of the previous two questions to sketch the database schema. That is, create a
rectangle for each table, identifying the attributes and identifying primary key with an underscore and
drawing lines between tables to represent foreign keys.
II. (30 pts) For each of the prompts below, write an SQL query to retrieve data from or to modify the
“yadkin”database.
In your response, label each query clearly and precede each one with a very brief description of what you
believe your query does. For example you might write:“This query uses a natural join to relate books to
their authors. It then selects those authors who have written more than 10 books.”
Prompts:
a. List the titles of all books published by a publisher located in New York (‘NY’). Don’t repeat
any titles.
b. List the titles of the books that have exactly two authors. Don’t repeat any titles.
c. Find the average price of all the paperback books that are included in the book table. Paperback
books have a value of‘Y’in the“paperback”attribute of book.
d. List the titles of the books in the book table that are available in both paperback AND hardback
form.
e. List the names of the books that are out of stock at all branches (a book is out of stock if the
units_on_hand in inventory = 0 at all stores or if just doesn’t show up in the inventory table).
f. List the name(s) of the branch with the smallest number of employees (might not be unique).
g. List the name of every branch along with the total value of the inventory at that branch. The value
of the inventory is determined by the units_on_hand times the book_price.
h. List the name of the branch with the highest inventory value. Your query should work correctly
even if the data in the tables changes.
i. In a single table, list the name of every branch along with a list of all the publishers (from
publisher) and the number of books from that publisher that the branch has in inventory, even if it
is zero. HINT: If there are 5 branches and 10 publishers in the database, the resulting table should
contain 5 x 10 = 50 rows.
j. List the name of every branch along with the name of the lowest price book currently in inventory
at that branch. A book is in inventory at a branch if the units_on_hand for that branch is greater
than 0.
III. SQL View, etc. exercises (10 pts)
i. You may have discovered that the inventory table is“incomplete”in that not every book is listed in the
inventory table for every branch. Let’s fix that. Write an INSERT INTO statement that will make the
inventory table complete. That is, if there is a book that is not listed in the inventory table for a branch
then insert a value of 0 for UNITS_ON_HAND for that book at that branch. After your query executes
the number of rows in inventory should be exactly the product of the number of rows in BOOK and the
number of rows in BRANCH.
ii. Write the SQL statement to create a view on the Yadkin database named book_info containing, for each
unique ISBN, a single row made up of the book_title, publisher_name, book_price, and a count of the
number of authors of the book.
(Grad students only)
iii. Let’s say that instead of“a count of the number of authors”of each book we wanted to create a view that
contained a list of the last names of all the authors for each book. So we want to see, for each unique
ISBN, a single row made up of the book_title, publisher_name, book_price, and a list of the last names of
the authors. Explain why this would be a difficult task to do in SQL. Could a recursive query be used?
Would that be all you need? You don’t need to write any SQL for this question, just your ideas.