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.