BookTown Store’s Relational Database Creation

Introduction

The relational database simplifies storage, manipulation, and retrieval of information from diverse sets of tables. The relational database comprises tables, forms, reports, and queries. This report explains the creation and the use of the relational database in tracking books, customers, and shipments of BookTown, a book retail store.

Database Creation

To create tables, the excel files for Books, Customers, and shipments were imported into access using the import wizard.

Entity-Relationship Diagram

The snapshot below displays the entity-relationship diagram created by dragging primary keys into secondary keys.

Query Construction

The queries were constructed using a simple query wizard located in the query wizard in the create tab.

Query 1- Stock levels

The query was created by selecting Book_title, Edition, and Current_Stock in the table of Books.

Query 2-Orders by Customer

  • In the simple query wizard, the table of customers was selected and fields of their first name, last name, shipment id, and the retail price was selected.
  • In the design view, the total record was added, the count was selected in the shipments field, and the sum was selected in the retail prices field as shown below.

Query 3-Books Shipped with Total

The query was created by selecting the Book_title, ISBN, Retail Price, and Book-id as a link between tables of shipment and books.

Query 4-Books Published Before 1990

  • Using a simple query wizard, the Book_title, Edition, and publication date.
  • In the design view, the publication data was typed <#01-Jan-90# on the criteria record and the field of the publication date as indicated below.
  • The outcome of the query of books published before 1990 are shown below

Query 5-Customers by Book

In the design view of the query, [Enter Book Title] was entered in the criteria field on the field of Book_Title as depicted below, and “unique values” were indicated “yes” in the properties sheet.

Creation of Forms

  • Using form wizard, all fields of Books, Customers, and Shipments’ tables were selected in respective steps.
  • Colored backgrounds were used to enhance the appeal of the forms

Books Form

Customers Form

Shipments Form

Creation of Reports

Reports of Books, Customers, Shipments, and Stock levels were created using report wizard by selecting appropriate fields.

Book report

Stock Levels Report

Conclusion

Tables, forms, reports, and queries are different parts of a relational database. They are very important in database management because they ease the storage, manipulation, and retrieval of data. The use of referential integrity in entity-relationships avoids redundancy of data entry, and thus, improving the accuracy and integrity of data.