Reference no: EM131967008

Database Comprehensive Assignment

INSTRUCTIONS: Read the scenario below and complete parts 1-3. You will need to use the attached MS Excel file which contains the data. After completing the assignment, rename and save the MS Access file.

Scenario:

BookFinder is an open marketplace for books online, a one-stop e-commerce search engine where you can search through more than 100 million new, used, rare, and out-of-print books for sale.

The site is produced by a team of high-tech librarians and programmers, working since 2004 to connect readers with the books they are looking for. They are part of their own audience; members of the BookFinder team are heavy readers and buy several dozen books every year using BookFinder. They also blog about their work, connecting readers and booksellers from around the world and supporting public access to a strong, diverse bookselling industry.

BookFinder was first developed in 2004 by then-19-year-old University of Denver undergraduate Charles Cook (as a personal Web site). Over the years, the site has grown to become one of the best online resources for book-related e-commerce, as evidenced by the great feedback received from users and the press. Whether customers collect rare books or buy cheap paperbacks to read, BookFinder is an unbiased marketplace and search engine.

Charles needs help developing a MS Access database based on the conceptual entity relationship diagram below:

Charles has an MS Excel document that contains the data for each entity so that it can be imported to MS Access (see attached file titled BookFinder.xls).

Part 1:

1. Create a Microsoft Access database.

2. Create the tables, fields, data types, primary key(s), and foreign key(s) for the database. Note: Make sure to select the appropriate data types for each data field.

3. Create the relationship(s) needed between the tables and enforce referential integrity.

4. Populate the database with the data provided in the Excel file.

Part 2:

1. Create a query that displays all authors who have written books that cost more than $8 and is a novel. The query must show authors' full name, book title, category, and book price. Save the query as 1 Authors.

2. Create a query that displays books that are priced between $5.00 and $10.00. The query result should only display the book title and the author's full name. Save query as 2 Prices.

3. Create a query that shows the count of books that are grouped by binding. Save query as 3 Count.

4. Create a query that displays the average price of all the books. Save the query as 4 Average.

5. Create an update query that increases all book prices by $1.00. Save the query as

5 Dollar.

Part 3:

Create a form that allows the user to enter new authors and bike information as shown below. Note: The interface should look identical to the image below (note that your price column values may differ). The formatting of data fields and labels will matter (e.g., organization, alignment, and spacing). Also include the button controls that will allow the user to go to the previous record, next record, and add new record. Rename the Author form to Author_YourLastName (e.g., Author_Abdelfattah).

Part 4:

Create a report of all the books purchased. This report will be grouped by authors. Sort the Date Purchased field by ascending order. Rename the report as your YourLastName_Report and the title of the report as YourLastName Book Purchase report. The report format should be identical to the image below (Note: your price column values may vary from this image).

Attachment:- Instructions-File.rar