Microsoft Office Tutorials and References
In Depth Information
DATABASE DESIGN RULES
As an analyst building a database, you should consider the relationship of each entity to the other entities you
have identified. For example, a college database might contain entities for Student, Course, and Section to
contain data about each. A relationship between Student and Section could be expressed as
An analyst also must consider the cardinality of any relationship. Cardinality can be one-to-one,
oneto-many, or many-to-many:
In a one-to-one relationship, one instance of the first entity is related to just one instance of the
In a one-to-many relationship, one instance of the first entity is related to many instances of the
second entity, but each instance of the second entity is related to only one instance of the first.
In a many-to-many relationship, one instance of the first entity is related to many instances of
the second entity, and one instance of the second entity is related to many instances of the first.
For a more concrete understanding of cardinality, consider again the college database with the Student,
Course, and Section entities. The university catalog shows that a course such as Accounting 101 can have
more than one section: 01, 02, 03, 04, and so on. Thus, you can observe the following relationships:
The relationship between the entities Course and Section is one-to-many. Each course has many
sections, but each section is associated with just one course.
The relationship between Student and Section is many-to-many. Each student can be in more
than one section, because each student can take more than one course. Also, each section has
more than one student.
Thinking about relationships and their cardinalities may seem tedious to you. However, as you work
through the cases in this text, you will see that this type of analysis can be valuable in designing databases. In
the case of many-to-many relationships, you should determine the tables a given database needs; in the case
of one-to-many relationships, you should decide which fields the tables need to share.
An attribute is a characteristic of an entity. You identify attributes of an entity because attributes become a
s fields. If an entity can be thought of as a noun, an attribute can be considered an adjective that
describes the noun. Continuing with the college database example, consider the Student entity. Students have
names, so Last Name would be an attribute of the Student entity and therefore a field in the Student table.
First Name would be an attribute as well. The Student entity would have an Address attribute as another field,
along with Phone Number and other descriptive fields.
Sometimes it can be difficult to tell the difference between an attribute and an entity, but one good way is
to ask whether more than one attribute is possible for each entity. If more than one instance is possible, but
you do not know the number in advance, you are working with an entity. For example, assume that a student
could have a maximum of two addresses
one for home and one for college. You could specify attributes
Address 1 and Address 2. Next, consider that you might not know the number of student addresses in
advance, meaning that all addresses have to be recorded. In that case, you would not know how many fields
to set aside in the Student table for addresses. Therefore, you would need a separate Student Addresses table
(entity) that would show any number of addresses for a given student.
DATABASE DESIGN RULES
As described previously, your first task in database design is to understand the logic of the business situation.
Once you understand this logic, you are ready to build the database. To create a context for learning about
database design, look at a hypothetical business operation and its database needs.
Example: The Talent Agency
Suppose you have been asked to build a database for a talent agency that books musical bands into
nightclubs. The agent needs a database to keep track of the agency
s transactions and to answer day-to-day
questions. For example, a club manager often wants to know which bands are available on a certain date at