At the end of the last chapter, you had written a snapshot description of the business that required a database. This snapshot included all the general functions of the business, and included some of the rules defining the information that must be stored in the database. An example snapshot is included in this chapter as the starting point for the next step in database design.
We are still working with the table design. Information stored in the tables will be considered later.
When you have finished this chapter, you will
- Understand the concepts of
- database entities,
- data dependencies,
- table relationships,
- fields and records and
- business rules.
- Be able to identify the entities in a sample database.
- Know the difference between the following table relationships:
- One-to-many and
- Understand the relationships between the entities in a sample base.
- Apply this knowledge to your own database to identify your database tables.
What you may need for this chapter:
- Your analysis of the database use, requirements and restrictions that you completed while working through Chapter 1.
Analysing your snapshot description
This chapter will work through the bus company snapshot description that appeared in the previous chapter. You can then try the same procedure on your own. The essential steps you will consider are described on this page and the following 2 pages.
An entity is "something you want to store the data about". An entity is simply a "thing" that is distinctly identified. Each individual occurrence of a particular entity has its own properties that have different values from other occurrences of the same entity. As you will see below, we can consider a "bus" an entity, with each bus in the fleet having different properties. Bus is an entity within the database, and registration number, seating capacity and rate of payment are attributes (properties) of the entity. "Entity" does not always mean the same as "table", BUT often where you have identified something as an entity, it will in fact appear in your database as a table.
To identify entities for the XYZ Bus Company database, start by isting all the nouns in the description. These will contain the things that are major importance, and help you to identify the information you want to store in your database.
Nouns For the XYZ Bus Company are highlighted in bold text below. For clarity, only the first occurrence of a noun is highlighted.
The XYZ Bus Company is based in the town of Lincoln. It operates a small fleet of buses from the base to the towns of Paree, Glen Fawkes, Nottingham and Mawson. Customers may book a bus for trips to any of these towns at rates that are determined by the distance, size of the bus, and amount of time the bus waits at the destination to return to base. When they make a booking, customers are given a confirmation slip along with the receipt for their deposit. This confirmation slip records details of the booking: the date, time of departure, destination, the amount owing on their account and required payment date. If the customer is new to the company, their name, contact details and preferred method of payment are also recorded at the time of booking the bus.
The XYZ Bus Company employs several drivers, some of whom are not yet qualified to drive all buses, although each driver is permitted to drive at least one of the buses. Drivers and buses are allocated to a booking when the booking is made.
Customers must pay a 25% deposit at the time of booking a bus, but the balance may be charged to their account and invoiced at set periods throughout the year. This is usually done on a monthly basis, when all customers are notified of all outstanding payments. All of the company's financial details are handled in other databases, so the company's database only contains an indication that a customer owes money, rather than amounts and payment details.
All these things will be stored somewhere in the XYZ Bus Company database, because these details are important to the company's business. The next step involves identifying groupings within these things.
Think about whether each of these describes another noun on your list.