RELATIONAL DATABASE

Relational Database: BookStore

Relational Database: BookStore

A relational database is a type of table design that promotes data integrity. A relational database is created using tables with primary and foreign keys. These keys link tables so that all information is consistent across the entire database. For instance, a customer may have many orders. The customer's private information is held in a table separate from the order information. A relational database setup with referential integrity prohibits the deletion of a customer record without first deleting the associated orders. This creates data integrity by eliminating orphaned records.

When designing a database, you have to make decisions regarding how best to take some system in the real world and model it in a database. This consists of deciding which tables to create, what columns they will contain, as well as the relationships between the tables. While it would be nice if this process was totally intuitive and obvious, or even better automated, this is simply not the case. A well-designed database takes time and effort to conceive, build and refine.

The benefits of a database that has been designed according to the relational model are numerous. Some of them are:

Data entry, updates and deletions will be efficient.

Data retrieval, summarization and reporting will also be efficient.

Since the database follows a well-formulated model, it behaves predictably.

Since much of the information is stored in the database rather than in the application, the database is somewhat self-documenting.

Changes to the database schema are easy to make.

The goal of this article is to explain the basic principles behind relational database design and demonstrate how to apply these principles when designing a database using Microsoft Access. This article is by no means comprehensive and certainly not definitive. Many books have been written on database design theory; in fact, many careers have been devoted to its study. Instead, this article is meant as an informal introduction to database design theory for the database developer.

Scenario

A small sized bookstore has been keeping track of its business mainly on paper. The owner is planning to grow her business. She would like to improve the bookkeeping, and as a result provide better service to customers, through a state-of-the-art database system. As she is not familiar with recent technology and tools, she would like to hire a college student who would develop such a system over the next summer.

The new system will help the bookstore maintain details about books, publishers, customers, receipts/payments, etc. A book may be a textbook, a novel, a comic, a children's book, or a cookbook. Publishers are the suppliers of the books. The bookstore buys books from different publishers. Typical customers of the store are libraries, institutions, or individuals such as students, faculty, and others. Customers may open an account with the store if they wish and receive a customer number. The bookstore is planning to build a website that will allow customers to perform certain activities on-line.

When customers place an order on-line, they can pay either by a major credit card or store ...