Management Information Systems, 13TH ED.
MANAGING THE DIGITAL FIRM
Kenneth C. Laudon ● Jane P. Laudon
continued
Learning Track 1: Database Design, Normalization, and Entity-
Relationship Diagramming
is Hands-On Guide will show you how to design a relational database system for a small busi-
ness using normalization and entity–relationship diagrams. e system we will be developing is
Information Gathering
Your first step is to gather information about how the new system will be used, what information
the user needs, how a new system can speed up and simplify operations, as well as how the system
could help the business to grow. A database is a model not only of reality but also of the future. If
there is a need to know information which is not yet stored anywhere or does not currently exist,
room for this data should be included in the system design.
Designing the Database: A Conceptual Schema
To begin developing a conceptual schema of the system, Clarence and Clarissa (and perhaps their
employees) need to describe their business. You will need to look at any paperwork generated
by the business, starting with those index cards, which contain a lot of information about each
Chapter 6: Foundations of Business Intelligence: Databases and
Information Management
Chapter 6 Learning Track 1 2
continued
package such as QuickBooks, and study what they do with it. ere is often informal written infor–
mation that is also good to know about—do the staff at HisNHers keep their own notes about
anything?
e HisNHers Salon now receives payment immediately after the service is performed. But with
the proper design of a database, a billing system could be integrated. Targeted marketing could
be performed whenever a new product (a new purple hair rinse for all the gray-haired customers)
becomes available.
For example, at HisNHers, both Clarissa and Clarence often referred to “products.” Only after
Clarence said that they might like to keep a “product” inventory and Clarissa mentioned that a
customer made an appointment for a “product” did you realize that Clarence meant items such
Entities, Relationships, and Normalization
Once a cohesive view of the system is established, the entity classes (tables) need to be defined
along with their attributes (fields). Review the discussion of entities, attributes, and relational data-
bases in Chapter 7. Your requirements analysis showed that two main documents are central to
HisNHers’ operation—Clarissa’s index cards and the salon’s appointment book.
Chapter 6 Learning Track 1 3
continued
e appointment book contains the date and time of the appointment, the customer’s name, phone
number, service requested (e.g., hair cut, beard trim, frosting) and staff member who will do the
job.
ese documents indicate the need for both a CUSTOMER Table and an APPOINTMENT Table.
But, what other tables are needed? What information belongs in each table? How should the
tables be linked together? It is important to start slowly and carefully, building on what is obvious.
At HisNHers, Clarissa’s index cards contain the beginnings of the Customer entity. e entity
known as the CUSTOMER Table will contain demographic data about the person as well as a
notes where special “conversational” or unusual items about the customer can be stored. An initial
breakdown of the attributes of this entity (or putting it more simply, the fields for the CUSTOMER
Table) along with their type (as represented in Access) and size are:
FIELD NAME TYPE SIZE
CUSTID Long Integer 4
FNAME Text 20
MIDNAME Text 15
Chapter 6 Learning Track 1 4
continued
While HisNHers doesn’t currently have all of this customer demographic information, provid-
ing for these fields will allow the system to grow and meet Clarence’s desire for targeted marketing
and a possible billing system. e last field in the CUSTOMER Table is the “old customer number.”
Discussions with the staff and studying the appointment book indicate that another entity would
be the APPOINTMENT Table. It would include the following attributes (fields).
FIELD NAME TYPE SIZE
CUSTID Long Integer 4
SERVICE_ID Long Integer 4
e Appointment Book shows that customers make appointments. In this sentence lies the begin-
ning of the table relationships in the HisNHers database. e ER diagram which shows the rela-
tionship between these initial two tables is:
As the system develops, we will add entities and information to this ER diagram until the entire
conceptual schema for the database is illustrated.
Chapter 6 Learning Track 1 5
continued
have a one–to-one or one–to-many relationship. A small number 1 next to the key means that a
table is on the one side of a relationship. A small infinity symbol indicates that the table is on the
many side of a relationship. For example, one customer makes many appointments.
Because the CUSTOMER Table will be linked to the APPOINTMENT Table, it will not be neces-
sary to include either the customer name or phone number in the APPOINTMENT Table. e
If a field from the CUSTOMER Table, like the phone number, were to be included in the
APPOINTMENT Table, it would have to be entered each time an appointment was made. Also, if
the phone number were to change, each record for that customer in the APPOINTMENT Table
Chapter 6 Learning Track 1 6
continued
For any one CUSTOMER record there may be many related APPOINTMENT records. is is
indicated on the ER diagram by the small 1 next the CUSTID field in the CUSTOMER Table and
the infinity symbol next to the CUSTID field in the APPOINTMENT Table. Similarly, the small
1 next to the SERVICE_ID field in the SERVICE Table is linked to the SERVICE_ID field in the
e service (Hair Cut, Perm, etc.) could have been an attribute (a field) of the APPOINTMENT
Table but the SERVICE itself has an attribute of Duration (e.g., Hair Cut 1¼2 hour, Coloring 2
hours). Whenever an attribute itself has attributes, it must become an entity unto itself. If it does
Each SERVICE may or may not include a Product (e.g., haircuts require no product, but a hair frost–
ing requires a colorant). Once again, the attribute of Product itself has attri butes (e.g., product type,
Chapter 6 Learning Track 1 7
e attributes of the APPOINTMENT entity consist almost entirely of foreign keys which link it to
information contained in other entities. It is on the many side of these relationships.
e conceptual schema for the HisNHers Salon now includes five separate entities: CUSTOMER,
Determining Key Fields
To ensure that each record of an entity class is unduplicated, it must contain a field which is its
unique identifier or key. Often referred to as the primary key, this field becomes the link from one
entity to another. When the primary key of one entity is used as a field in a table to which it links,
Chapter 6 Learning Track 1 8
create a composite key of two or more fields in a table so that first name and last name could be
joined into one field. But in this case such a composite key would not ensure a unique record (e.g.,
Maria Jones and her daughter, Maria Jones). e CUSTOMER entity requires the creation of a new
attribute to be used as the primary key.
Often a composite key of date and time is a good choice for an entity that is time based, since it
precludes duplicates. For the HisNHers database, the APPOINTMENT Table could use such a key
If you have the luxury of being able to set up a new primary key, one of the best choices is to allow
the system to create an automatically numbering field for each record. e CUSTID field in the
CUSTOMER entity is such a field. is will ensure that keys are unique and unduplicated. It is
seldom necessary for the user to even be aware of the contents of a primary key field. e job of the
Chapter 6 Learning Track 1 9
Reviewing the Design
After the “final” conceptual schema has been developed and the ER diagram includes all of the
entities with their keys, attributes, and relationships, it is time for a de sign review. All of the prin–
cipal stakeholders in the system should study it to be sure that the necessary information is includ-
Developing the User Interface: Forms and Reports
e user interface includes menus, data entry forms, and reports. In short, despite your beautiful
ER diagram, the interface is what the user will think of when he or she thinks of the database. e
tools to create these objects vary significantly among database packages. In Microsoft Access, the
development of both entry forms and reports can be quickly accomplished via wizards that create
basic objects and then allow them to be customized.
Development of reports for the system can teach you much about the design of the system, and it is
critical to have sample reports examined by the user. If the owners of HisNHers wanted to see the
customer’s hair color on a report, you would soon realize that there is no field for this information.
Chapter 6 Learning Track 1 10
While it’s possible to change the database to include a field for hair color, this type of oversight
should have been caught at an earlier stage of the design process.
e owners of HisNHers wish to have a daily appointment list which shows not just the custom-
er and appointment time but also the provider, the service requested, and any special product that