1. (10 points) Write SQL code to create the tables that implements the following design in a database named Library:

- Include the drop statements and GO at appropriate places

- ISBN is a Primary Key in the BookInfo table but should not be an IDENTITY column

- AccountCreated is a column that stores the date when the Readers account was created

2. (18 points) Choose appropriate data types for each column and include the following as comments in your sql file:

a) Allow NULL values for the Memo, ReaderMemo, ReturnDate, ActualReturnDate columns. Explain why these columns have been chosen to allow NULL values. (4)

b) For the columns that are of type varchar, vary the length depending on the data to be stored. Explain your reasoning for choosing a certain length: varchar(10) vs varchar(100) for example. (6)

c) Describe the four relationships that exists in the relational database. Identify the foreign keys that form each relationship. Use HW4 solution for relationship description as a guide. (8)

3. (12 points) Include four checks and two default values:

a) ISBN should be between 10 and 14 characters in length

b) BookPrice, CopiesNo and BookPages should be greater than 0

c) The default date for BorrowDate should be todays date (use GETDATE())

d) The default value for CopiesNo should be 1

4. (10 points) Create the necessary non-clustered indexes for these tables. Explain your choices by following the rules for creating such indexes discussed in class/book.

5. (5 points) Create a sequence with a cycle that starts at 1 and increments by 1 with a maxvalue of 100000. Use this sequence for BorrowID when inserting data in the BorrowerInfo table.

6 (10 points) Write insert into statements that add rows to the database tables that you created. Make up data that makes sense.

a) Add two rows of data in the BookCategory and ReaderInfo tables.

b) Add three rows to the BookInfo and Books tables

- One BookInfo & Books belonging to Category 1 with 4 copies

- Two BookInfo & Books belonging to Category 2 with 2 copies each

c) Add five rows to the BorrowerInfo table where Reader 1 has returned all the borrowed books and Reader 2 has not returned any borrowed books

- one row for Reader 1 who borrowed a book belonging to Category 1

- one row for Reader 1 who borrowed another book belonging to Category 2

- one row for Reader 2 who borrowed the same book from Category 1 that Reader 1 borrowed

- two rows for Reader 2 who borrowed two books belonging to Category 2

7. (5 points) Write a SELECT statement that joins the five tables and retrieves all the information about the two Readers and the Books they borrowed.

8. (5 points) Write a summary query (using aggregate function and GROUP BY) to answer the question: How many books did each reader borrow?

9. (15 points) Write 5 insert into statements to verify the five check constraints. The insert into statements should generate errors for entering

a) ISBN values that have characters less than 10 and greater than 14

b) Negative values for BookPrice, CopiesNo and BookPages

Important: Comment out the statements written for #9 before you submit.

10. (10 points) After your script is complete, execute the script in its entirety and make sure it runs without any errors with the two queries returning appropriate data. If your script runs successfully, you get 10 points.

Bookinfo BookCategory 9 BookCategorylD ISBN BookCategorylD BookName BookAuthor BookPublisher BookPubDate BookPages BookPrice CopiesNo Memo BookCategoryName Memo Borrowerlnfo Readers 9 ReaderNo Books ReaderNo BooklD BorrowDate ReturnDate ActualReturnDate Memo ReaderName AccountCreated ReaderMemo ISBN BookinDate Memo