We Helped With This ACCESS Database Assignment: Have A Similar One?

Databases (Individual)

Your task in this assignment is to modify the Book database (books.accdb) available on Sakai to add the following functionality:

1.     Add a table to the database called Review. 

a.     The primary key of the table should be called ReviewID. The other fields in the table should be ReviewerName (text) and Rating (also text).

b.     Make the needed changes to the Book table so that Review table and the Book table can be linked:

i. The Book table needs a new field to be the foreign key ii. Add some records to the Review table. You can make up the data.

iii. Link the two tables together in the Relationships window. Each book is linked to one review, possibly many.

2.     Create 4 queries:

a.     Write a query that returns the Title of every book released before the 1st of March 2012.  Only the Title of the book should be returned by the query. Call the query “Query 1” and save it.

b.     Write a query that returns the Title and the Paperback identifier of every book which is either classified as a Horror Type (“HOR”) or Sci-Fi Type (“SFI”). Only the Title and the Paperback identifier of the book should be returned by the query. Call the query “Query 2” and save it.

c.     Write a query that returns the Title of every book written by anyone who has the first name of “Peter”. Only the Title of the book should be returned by the query. Call the query “Query 3” and save it.

d.     Write a query that returns a count of the number of book copies that meet the following conditions:

-  The book is written by “Toni Morrison”.     - The book is published by “Plume”.

-  A copy of the book is located at the “Henry Brentwood” branch of the library.

Only the count (e.g. CountOfTitle) should be returned by the query.  Call the query “Query 4” and save it.

3.     Create a report that displays all the books in the system. Sort the books into alphabetical order by name. The end of the report should show a total count of the number of books in the system. The top of the report

Databases (Individual) ³  INSS 300.101  ³  Fall 2016  ³  Page 1 of 2

should have a title, and the footer should show the date and number of pages. Name this report “Book Listing” and save it. Note the report name is not the same as the title that is at the top of the report.

a.     Use fonts, layout and colors to customize the look of the report. Assume the reports would be printed on landscape, letter sized paper.

          Notes:      

o    All queries and reports should work correctly even if more data is added to the tables, or removed. o       Make sure you delete all draft attempts in your database. 

o    Make sure that all your work is saved inside the database, do not export objects and save them as separate files. You should submit a single Access file as your assignment.

Project Due Date:

            December 14^th, 2016 (project must be submitted via Sakai prior to the start of class).

What to turn in:

            A digital copy of your single Access database file (submitted via Sakai as an attachment).

Databases (Individual) ³  INSS 300.101  ³  Fall 2016  ³  Page 2 of 2