Microsoft Office Tutorials and References
In Depth Information
DATABASE DESIGN RULES
a certain time, or wants to know the agent
s fee for a certain band. Similarly, the agent may want to see a list
of all band members and the instrument each person plays, or a list of all bands that have three members.
Suppose that you have talked to the agent and have observed the agency
s business operation. You
conclude that your database needs to reflect the following facts:
1. A booking is an event in which a certain band plays in a particular club on a particular date,
starting and ending at certain times, and performing for a specific fee. A band can play more
than once a day. The Heartbreakers, for example, could play at the East End Cafe in the
afternoon and then at the West End Cafe on the same night. For each booking, the club pays the
talent agent. The agent keeps a five percent fee and then gives the remainder of the payment to
2. Each band has at least two members and an unlimited maximum number of members. The agent
notes a telephone number of just one band member, which is used as the band
number. No two bands have the same name or telephone number.
3. No members of any of the bands have the same name. For example, if one band has a member
named Sally Smith, there is no Sally Smith in another band.
4. The agent keeps track of just one instrument that each band member plays. For the purpose of
are considered an instrument.
5. Each band has a desired fee. For example, the Lightmetal band might want $700 per booking,
and would expect the agent to try to get at least that amount.
6. Each nightclub has a name, an address, and a contact person. The contact person has a
telephone number that the agent uses to call the club. No two clubs have the same name, contact
person, or telephone number. Each club has a target fee. The contact person will try to get the
agent to accept that fee for a band
7. Some clubs feed the band members for free; others do not.
s database on your own. Ask
yourself: What are the entities? Recall that business databases usually have Customer, Employee, and
Inventory entities, as well as an entity for the event that generates revenue transactions. Each entity becomes a
table in the database. What are the relationships between entities? For each entity, what are its attributes?
For each table, what is the primary key?
Before continuing with this tutorial, you might try to design the agency
Six Database Design Rules
Assume that you have gathered information about the business situation in the talent agency example. Now
you want to identify the tables required for the database and the fields needed in each table. Observe the
following six rules:
Rule 1: You do not need a table for the business. The database represents the entire business. Thus, in the
example, Agent and Agency are not entities.
Rule 2: Identify the entities in the business description. Look for typical things and events that will become
tables in the database. In the talent agency example, you should be able to observe the following entities:
Things: The product (inventory for sale) is Band. The customer is Club.
Events: The revenue-generating transaction is Bookings.
You might ask yourself: Is there an Employee entity? Isn
t Instrument an entity? Those issues will be
discussed as the rules are explained.
Rule 3: Look for relationships between the entities. Look for one-to-many relationships between entities.
The relationship between those entities must be established in the tables, using a foreign key. For details, see
the following discussion in Rule 4 about the relationship between Band and Band Member.
Look for many-to-many relationships between entities. Each of these relationships requires a third entity
that associates the two entities in the relationship. Recall the many-to-many relationship from the college
database scenario that involved Student and Section entities. To display the enrollment of specific students in
specific sections, a third table would be required. The mechanics of creating such a table are described in
Rule 4 during the discussion of the relationship between Band and Club.