Conceptual Modeling – Suggested Solutions
Theory
Coronel, C., Morris, S. and Rob. P., Database Systems: Design, Implementation &
Management, Chapter 4, Selected Review Questions and Problems.
Review Questions:
Conceptual Design
The answers supplied for these review questions are based on the support material
supplied by the textbook where a less stringent view of a conceptual ERD and FK’s is
held. In looking at these sample solutions you should keep this in mind, and
remember for this unit you must not include FK’s on a CONCEPTUAL ERD.
1. What two conditions must be met before an entity can be classified as a weak
entity? Give an example of a weak entity.
To be classified as a weak entity, two conditions must be met:
1. The entity must be existence-dependent on its parent entity.
2. The entity must inherit at least part of its primary key from its parent entity.
For example, the (identifying) relationship depicted in the text’s Figure 4. 9 shows a
weak CLASS entity:
1. CLASS is clearly existence-dependent on COURSE. (You can’t have a database
class unless a database course exists.)
2. The CLASS entity’s PK is defined through the combination of CLASS_SECTION
and CRS_CODE. The CRS_CODE attribute is also the PK of COURSE.
The conditions that define a weak entity are the same as those for an identifying
relationship between an entity and its parent. In short, the existence of a weak entity
produces an identifying relationship. And if the entity is strong, its relationship to the
other entity is non-identifying. (Note the solid relationship line in the text’s Figure.)
Keep in mind that whether or not an entity is weak usually depends on the database
designer’s decisions. For instance, if the database designer had decided to use a
single-attribute as shown in the text’s Figure 4.8, the CLASS entity would be strong.
(The CLASS entity’s PK is CLASS_CODE, which is not derived from the COURSE
entity.) In this case, the relationship between COURSE and CLASS is non-identifying.
(Note the dashed relationship line in the text’s Figure 4.8) However, regardless of
how the designer classifies the relationship – non-identifying or identifying–
CLASS is always existence-dependent on COURSE.
Page 1 of 18
2. What is a strong (or identifying) relationship, and how is it depicted in a Crow’s
Foot ERD?
An identifying relationship exists when an entity is existence-dependent on another
entity and inherits at least part of its primary key from that entity. An identifying
relationship is shown as a solid line. In other words, an identifying relationship exists
when a weak entity is related to its parent entity. (Note the discussion in question 1.)
3. Given the business rule “an employee may have many degrees,” discuss its
effect on attributes, entities, and relationships. (Hint: Remember what a
multivalued attribute is and how it might be implemented.)
Suppose that an employee has the following degrees: BA, BS, and MBA. These
degrees could be stored in a single string as a multivalued attribute named
EMP_DEGREE in an EMPLOYEE table such as the one shown next:
EMP_NUM EMP_LNAME EMP_DEGREE
123 Carter AA, BBA
124 O’Shanski BBA, MBA, Ph.D.
125 Jones AS
126 Ortez BS, MS
Although the preceding solution has no obvious design flaws, it is likely to yield
reporting problems. For example, suppose you want to get a count for all employees
who have BBA degrees. You could, of course, do an “in-string” search to find all of the
BBA values within the EMP_DEGREE strings. But such a solution is cumbersome from
a reporting point of view. Query simplicity is a valuable thing to application developers
– and to end users who like maximum query execution speeds. Database designers
ought to pay some attention to the competing database interests that exist in the data
environment.
One – very poor – solution is to create a field for each expected value. This “solution is
shown next:
EMP_NUM EMP_LNAME EMP_DEGREE1 EMP_DEGREE2 EMP_DEGREE3
123 Carter AA BBA
124 O’Shanski BBA MBA Ph.D.
125 Jones AS
126 Ortez BS MS
This solution yields nulls for all employees who have fewer than three degrees. And if
even one employee earns a fourth degree, the table structure must be altered to
accommodate the new data value. (One piece of evidence of poor design is the need to
alter table structures in response to the need to add data of an existing type.) In
addition, the query simplicity is not enhanced by the fact that any degree can be listed
in any column. For example, a BA degree might be listed in the second column, after an
“associate of arts (AA) degree has been entered in EMP_DEGREE1.
One might simplify the query environment by creating a set of attributes that define the
data entry, thus producing the following results:
Page 2 of 18
EMP_NUM EMP_LNAME EMP_AA EMP_AS EMP_BA EMP_BS EMP_BBA EMP_MS EMP_MBA EMP_PhD
123 Carter X X
124 O’Shanski X X X
125 Jones X
126 Ortez X X
This “solution” clearly proliferates the nulls at an ever-increasing pace.
The only reasonable solution is to create a new DEGREE entity that stores each degree
in a separate record, this producing the following tables. (There is a 1:M relationship
between EMPLOYEE and DEGREE). Note that the EMP_NUM can occur more than
once in the DEGREE table. The DEGREE table’s PK is EMP_NUM + DEGREE_CODE.
This solution also makes it possible to record the date on which the degree was earned,
the institution from which it was earned, and so on.
Table name: EMPLOYEE
EMP_NUM EMP_LNAME
123 Carter
124 O’Shanski
125 Jones
126 Ortez
Table name: DEGREE
EMP_NUM DEGREE_CODE DEGREE_DATE DEGREE_PLACE
123 AA May-1999 Lake Sumter CC
123 BBA Aug-2004 U. of Georgia
124 BBA Dec-1990 U. of Toledo
124 MBA May-2001 U. of Michigan
124 Ph.D. Dec-2005 U. of Tennessee
125 AS Aug-2002 Valdosta State
126 BS Dec-1989 U. of Missouri
126 MS May-2002 U. of Florida
Note that this solution leaves no nulls, produces a simple query environment, and
makes it unnecessary to alter the table structure when employees earn additional
degrees. (You can make the environment even more flexible by naming the new entity
QUALIFICATION, thus making it possible to store degrees, certifications, and other
useful data that define an employee’s qualifications.)
4. What is a composite entity, and when is it used?
A composite entity is generally used to transform M:N relationships into 1:M
relationships. A composite entity, also known as a bridge entity, is one that has a
primary key composed of multiple attributes. The PK attributes are inherited from the
entities that it relates to one another.
Page 3 of 18
5. Suppose you are working within the framework of the conceptual model in Figure
Q4.5.
Figure Q4.5 The Conceptual Model for Question 5
a. Write the business rules that are reflected in it.
Even a simple ERD such as the one shown in Figure Q4.5 is based on many business
rules. Make sure that each business rule is written on a separate line and that all of its
details are spelled out. In this case, the business rules are derived from the ERD in a
“reverse-engineering” procedure designed to document the database design. In a real
world database design situation, the ERD is generated on the basis of business rules
that are written before the first entity box is drawn. (Remember that the business rules
are derived from a carefully and precisely written description of operations.)
Given the ERD shown in Figure Q4.5, you can identify the following business rules:
1. A customer can own many cars.
2. Some customers do not own cars.
3. A car is owned by one and only one customer.
4. A car may generate one or more maintenance records.
5. Each maintenance record is generated by one and only one car.
6. Some cars have not (yet) generated a maintenance procedure.
7. Each maintenance procedure can use many parts.
(Comment: A maintenance procedure may include multiple maintenance actions, in
the model shown above every maintenance action requires a part. If this was not the
case, which is a more realistic business rule, then the minimum cardinality at PART
for the “is written in” relationship would need to be zero:
Such a change would also mean that a unique key would be required for
MAINT_LINE which did not depend on PART, for example: maintenance_no and
maintenance_line_no)
Page 4 of 18
8. A part may be used in many maintenance records.
(Comment: Each time an oil change is made, an oil filter is used. Therefore, many oil
filters may be used during some period of time. Naturally, you are not using the same
oil filter each time – but the part classified as “oil filter” shows up in many
maintenance records as time passes.)
Note that the apparent M:N relationship between MAINTENANCE and PART has
been resolved through the use of the composite entity named MAINT_LINE. The
MAINT_LINE entity ensures that the M:N relationship between MAINTENANCE and
PART has been broken up to produce the two 1:M relationships shown in business
rules 9 and 10.
9. Each maintenance procedure generates one or more maintenance lines.
10. Each part may appear in many maintenance lines.
As you review the business rules 9 and 10, use the following two tables to show some
sample data entries. For example, take a look at the (simplified) contents of the
following MAINTENANCE and LINE tables and note that the MAINT_NUM 10001
occurs three times in the LINE table:
Sample MAINTENANCE Table Data
MAINT_NUM MAINT_DATE
10001 15-Mar-2006
10002 15-Mar-2006
10003 16-Mar-2006
Sample LINE Table Data
MAINT_NUM LINE_NUM LINE_DESCRIPTION LINE_PART LINE_UNITS
10001 1 Replace fuel filter FF-015 1
10001 2 Replace air filter AF-1187 1
10001 3 Tighten alternator belt NA 0
10002 1 Replace taillight bulbs BU-2145 2
10003 1 Replace oil filter OF-2113 1
10003 2 Replace air filter AF-1187 1
b. Identify all of the cardinalities.
The Crow’s Foot ERD, shown in Figure Q4.5, does not show cardinalities directly.
Instead, the cardinalities are implied through the Crow’s Foot symbols. You might
write the cardinality (0,N) next to the MAINT_LINE entity in its relationship with the
PART entity to indicate that a part might occur “N” times in the maintenance line
entity or that it might never show up in the maintenance line entity. The latter case
would occur if a given part has never been used in maintenance.
Page 5 of 18
Page 6 of 18
6. What is a recursive relationship? Given an example.
A recursive relationship exists when an entity is related to itself. For example, a
COURSE may be a prerequisite to a COURSE. (See Section 4.1.10, “Recursive
Relationships,” for additional examples).
7. How would you (graphically) identify each of the following ER components in a
Crow’s Foot model?
The answers to questions (a) through (d) are illustrated with the help of Figure Q4.7.
FIGURE Q4.7 Crow’s Foot ER Components
a. an entity
An entity is represented by a rectangle containing the entity name. (Remember that, in
ER modeling, the word “entity” actually refers to the entity set.)
b. the cardinality (0,N)
Cardinalities are implied through the use of Crow’s Foot symbols. For example, note
the implied (0,N) cardinality in Figure Q4.7.
c. a non-identifying relationship
A non-identifying relationship exists when the PK of the related entity does not contain
at least one of the PK attributes of the parent entity. For example, if the PK of a
COURSE entity is CRS_CODE and the PK of the related CLASS entity is
CLASS_CODE, the relationship between COURSE and CLASS is non-identifying.
(Note that the CLASS PK does not include the CRS_CODE attribute.)
Page 7 of 18
d. an identifying relationship
An identifying relationship exists when the PK of the related entity contains at least one
of the PK attributes of the parent entity. For example, if the PK of a COURSE entity is
CRS_CODE and the PK of the related CLASS entity is CRS_CODE +
CLASS_SECTION, the relationship between COURSE and CLASS is identifying.
(Note that the CLASS PK includes the CRS_CODE attribute.)
8. Discuss the difference between a composite key and a composite attribute. How
would each be indicated in an ERD?
A composite key is one that consists of more than one attribute. If the ER diagram
contains the attribute names for each of its entities, a composite key is indicated in the
ER diagram by the fact that more than one attribute name is underlined to indicate its
participation in the primary key.
A composite attribute is one that can be subdivided to yield meaningful attributes for
each of its components. For example, the composite attribute CUS_NAME can be
subdivided to yield the CUS_FNAME, CUS_INITIAL, and CUS_LNAME attributes. There
is no ER convention that enables us to indicate that an attribute is a composite attribute.
9. What two courses of action are available to a designer when he or she encounters
a multivalued attribute?
The discussion that accompanies the answer to question 3 is valid as an answer to this
question.
10. What is a derived attribute? Give an example.
A derived attribute is an attribute whose value is calculated (derived) from other
attributes. The derived attribute need not be physically stored within the database;
instead, it can be derived by using an algorithm. For example, an employee’s age,
EMP_AGE, may be found by computing the integer value of the difference between the
current date and the EMP_DOB. If you use MS Access, you would use INT((DATE() –
EMP_DOB)/365).
Similarly, a sales clerk’s total gross pay may be computed by adding a computed sales
commission to base pay. For instance, if the sales clerk’s commission is 1%, the gross
pay may be computed by:
EMP_GROSSPAY = INV_SALES*1.01 + EMP_BASEPAY
Or the invoice line item amount may be calculated by
LINE_TOTAL = LINE_UNITS*PROD_PRICE
11. How is a relationship between entities indicated in an ERD? Give an example,
using the Crow’s Foot notation.
Use Figure 4.7 as the basis for your answer. Note that many products show a distinction
between dashed and solid relationship lines, to indicate identifying and non-identifying
relationships.
Page 8 of 18
12. Discuss two ways in which the 1:M relationship between COURSE and CLASS can
be implemented. (Hint: Think about relationship strength.)
The relationship is implemented as identifying when the CLASS entity’s PK contains the
COURSE entity’s PK. For example,
COURSE(CRS_CODE, CRS_TITLE, CRS_DESCRIPTION, CRS_CREDITS)
CLASS(CRS_CODE, CLASS_SECTION, CLASS_TIME, CLASS_PLACE)
Note that the CLASS entity’s PK is CRS_CODE + CLASS_SECTION – and that the
CRS_CODE component of this PK has been “borrowed” from the COURSE entity.
(Because CLASS is existence-dependent on COURSE and uses a PK component from
its parent (COURSE) entity, the CLASS entity is weak in this identifying relationship
between COURSE and CLASS. The Crow’s Foot ERD shows an identifying relationship
as a solid line. (See Figure Q4.12a.)
Figure Q4.12a Identifying COURSE and CLASS Relationship
Sample data are shown next:
Table name: COURSE
CRS_COD
E
CRS_TITLE CRS-DESCRIPTION CRS_CREDITS
ACCT-211 Basic Accounting An introduction to accounting. Required
of all business majors.
3
CIS-380 Database
Techniques I
Database design and implementation
issues. Uses CASE tools to generate
designs that are then implemented in a
major database management system.
3
CIS-490 Database
Techniques II
The second half of CIS-380. Basic Web
database application development and
management issues.
4
Page 9 of 18
Table name: CLASS
CRS_CODE CLASS_SECTION CLASS_TIME CLASS_PLACE
ACCT-211 1 8:00 a.m. – 9:30 a.m. T-Th. Business 325
ACCT-211 2 8:00 a.m. – 8:50 a.m. MWF Business 325
ACCT-211 3 8:00 a.m. – 8:50 a.m. MWF Business 402
CIS-380 1 11:00 a.m. – 11:50 a.m. MWF Business 415
CIS-380 2 3:00 p.m. – 3:50 a.m. MWF Business 398
CIS-490 1 1:00 p.m. – 3:00 p.m. MW Business 398
CIS-490 2 6:00 p.m. – 10:00 p.m. Th. Business 398
The relationship is implemented as non-identifying when the CLASS entity’s PK does
not contain the COURSE entity’s PK. For example,
COURSE(CRS_CODE, CRS_TITLE, CRS_DESCRIPTION, CRS_CREDITS)
CLASS(CLASS_CODE, CRS_CODE, CLASS_SECTION, CLASS_TIME, CLASS_PLACE)
(Note that CRS_CODE is no longer part of the CLASS PK, but that it continues to serve
as the FK to COURSE.)
The Crow’s Foot ERD shows a non-identifying relationship as a dashed line. (See
Figure Q4.12b.)
Figure Q4.12b Non-identifying COURSE and CLASS Relationship
Given the non-identifying relationship depicted in Figure Q4.13b, the CLASS table
contents would look like this:
Table name: CLASS
CLASS_CODE CRS_CODE CLASS_SECTION CLASS_TIME CLASS_PLACE
21151 ACCT-211 1 8:00 a.m. – 9:30 a.m. T-Th. Business 325
21152 ACCT-211 2 8:00 a.m. – 8:50 a.m. MWF Business 325
21153 ACCT-211 3 8:00 a.m. – 8:50 a.m. MWF Business 402
38041 CIS-380 1 11:00 a.m. – 11:50 a.m.
MWF
Business 415
38042 CIS-380 2 3:00 p.m. – 3:50 a.m. MWF Business 398
49041 CIS-490 1 1:00 p.m. – 3:00 p.m. MW Business 398
49042 CIS-490 2 6:00 p.m. – 10:00 p.m. Th. Business 398
The advantage of the second CLASS entity version is that its PK can be referenced
easily as a FK in another related entity such as ENROLL. Using a single-attribute PK
makes implementation easier. This is especially true when the entity represents the “1”
side in one or more relationships. In general, it is advisable to avoid composite PKs
whenever it is practical to do so.
Page 10 of 18
13. How is a composite entity represented in an ERD, and what is its function?
Illustrate the Crow’s Foot model.
The label “composite” is based on the fact that the composite entity contains at least the
primary key attributes of each of the entities that are connected by it. The composite
entity is an important component of the ER model because relational database models
should not contain M:N relationships – and the composite entity can be used to break up
such relationships into 1:M relationships.
Avoid composite PKs whenever it is practical to do so. Note that the CLASS entity
structure shown in Figure Q4.12b is far better than that of the CLASS entity structure
shown in Figure Q4.12a. Suppose, for example, that you want to design a class
enrollment entity to serve as the “bridge” between STUDENT and CLASS in the M:N
relationship defined by these two business rules:
● A student can take many classes.
● Each class can be taken by many students.
In this case, you could create a (composite) entity named ENROLL to link CLASS and
STUDENT, using these structures:
STUDENT(STU_NUM, STU_LNAME …………..)
ENROLL(STU_NUM, CLASS_NUM, ENROLL_GRADE ………)
CLASS(CLASS_CODE, CRS_CODE, CLASS_SECTION, CLASS_TIME, CLASS_PLACE)
You might argue that a composite PK in ENROLL does no harm, since it is not likely to
be related to another entity in the typical academic database setting. Although that is a
good observation, you would run into a problem in the event that might trigger a
required relationship between ENROLL and another entity. In any case, you may
simplify the creation of future relationships if you create an “artificial” single-attribute PK
such as ENROLL_NUM, while maintaining the STU_NUM and CLASS_NUM as FK
attributes. In other words:
ENROLL(ENROLL_NUM, STU_NUM, CLASS_NUM, ENROLL_GRADE ………)
The ENROLL_NUM attribute values can easily be generated through the proper use of
SQL code or application software, thus eliminating the need for data entry by humans.
Let’s examine another example of the use of composite entities. Suppose that a trucking
company keeps a log of its trucking operations to keep track of its driver/truck
assignments. The company may assign any given truck to any given driver many times
and, as time passes, each driver may be assigned to drive many of the company’s
trucks. Since this M:N relationship should not be implemented, we create the composite
entity named LOG whose attributes are defined by the end-user information
requirements. In this case, it may be useful to include LOG_DATE, TRUCK_NUM,
DRIVER_NUM, LOG_TIME_OUT, and LOG_TIME_IN.
Note that the LOG’s TRUCK_NUM and DRIVER_NUM attributes are the driver LOG’s
foreign keys. The TRUCK_NUM and DRIVER_NUM attribute values provide the bridge
between the TRUCK and DRIVER, respectively. In other words, to form a proper bridge
between TRUCK and DRIVER, the composite LOG entity must contain at least the
primary keys of the entities connected by it.
Page 11 of 18
You might think that the combination of the composite entity’s foreign keys may be
designated to be the composite entity’s primary key. However, this combination will not
produce unique values over time. For example, the same driver may drive a given truck
on different dates. Adding the date to the PK attributes will solve that problem. But we
still have a non-unique outcome when the same driver drives a given truck twice on the
same date. Adding a time attribute will finally create a unique set of PK attribute values –
but the PK is now composed of four attributes: TRUCK_NUM, DRIVER_NUM,
LOG_DATE, and LOG_TIME_OUT. (The combination of these attributes yields a unique
outcome, because the same driver cannot check out two trucks at the same time on a
given date.)
Because multi-attribute PKs may be difficult to manage, it is often advisable to
create an “artificial” single-attribute PK, such as LOG_NUM, to uniquely identify
each record in the LOG table. (Oracle users can fill such an attribute via a sequence to
ensure that the system will generate unique LOG_NUM values for each record.) Note
that this solution produces a LOG table that contains two candidate keys: the designated
primary key and the combination of foreign keys that could have served as the primary
key.
While the preceding solution simplifies the PK definition, it does not prevent the creation
of duplicate records that merely have a different LOG_NUM value. Note, for example,
the first two records in the following table:
LOG_NUM LOG_DATE TRUCK_NUM DRIVER_NUM LOG_TIME_OUT LOG_TIME_IN
10015 12-Mar-2006 322453 1215 07:18 a.m. 04:23 p.m.
10016 12-Mar-2006 322453 1215 07:18 a.m. 04:23 p.m.
10017 12-Mar-2006 545567 1298 08:12 a.m. 09:15 p.m.
To avoid such duplicate records, you can create a unique index on TRUCK_NUM +
DRIVER_NUM + LOG_DATE + LOG_TIME_OUT.
Composite entities may be named to reflect their component entities. For example, an
employee may have several insurance policies (life, dental, accident, health, etc.) and
each insurance policy may be held by many employees. This M:N relationship is
converted to a set of two 1:M relationships, by creating a composite entity named
EMP_INS. The EMP_INS entity must contain at least the primary key components of
each of the two entities connected by it. How many additional attributes are kept in the
composite entity depends on the end-user information requirements.
14. Briefly, but precisely, explain the difference between single-valued attributes and
simple attributes. Give an example of each.
A single -valued attribute is one that can have only one value. For example, a person
has only one first name and only one social security number.
A simple attribute is one that cannot be decomposed into its component pieces. For
example, a person’s sex is classified as either M or F and there is no reasonable way to
decompose M or F. Similarly, a person’s first name cannot be decomposed into
meaningful components. (In contrast, if a phone number includes the area code, it can
be decomposed into the area code and the phone number. And a person’s name may
be decomposed into a first name, an initial, and a last name.)
Page 12 of 18
Single-valued attributes are not necessarily simple. For example, an inventory code
HWPRIJ23145 may refer to a classification scheme in which HW indicates Hardware,
PR indicates Printer, IJ indicates Inkjet, and 23145 indicates an inventory control
number. Therefore, HWPRIJ23145 may be decomposed into its component parts…
even though it is single-valued. To facilitate product tracking, manufacturing serial codes
must be single-valued, but they may not be simple.
For instance, the product serial number TNP5S2M231109154321 might be decomposed
this way:
TN = state = Tennessee
P5 = plant number 5
S2 = shift 2
M23 = machine 23
11 = month, i.e., November
09 = day
154321 = time on a 24-hour clock, i.e., 15:43:21, or 3:43 p.m. plus 21 seconds.
15. What are multivalued attributes, and how can they be handled within the database
design?
The answer to question 3 is just as valid as an answer to this question. You can
augment that discussion with the following discussion:
As the name implies, multi-valued attributes may have many values. For example, a
person’s education may include a high school diploma, a 2-year college associate
degree, a four-year college degree, a Master’s degree, a Doctoral degree, and various
professional certifications such as a Certified Public Accounting certificate or a Certified
Data Processing Certificate.
There are basically three ways to handle multi-valued attributes — and two of those three
ways are bad:
1. Each of the possible outcomes is kept as a separate attribute within the table. This
solution is undesirable for several reasons. First, the table would generate many
nulls for those who had minimal educational attainments. Using the preceding
example, a person with only a high school diploma would generate nulls for the
2-year college associate degree, the four-year college degree, the Master’s degree,
the Doctoral degree, and for each of the professional certifications. In addition, how
many professional certification attributes should be maintained? If you store two
professional certification attributes, you will generate a null for someone with only
one professional certification and you’d generate two nulls for all persons without
professional certifications. And suppose you have a person with five professional
certifications? Would you create additional attributes, thus creating many more nulls
in the table, or would you simply ignore the additional professional certifications,
thereby losing information?
Page 13 of 18
2. The educational attainments may be kept as a single, variable-length string or
character field. This solution is undesirable because it becomes difficult to query the
table. For example, even a simple question such as “how many employees have
four-year college degrees?” requires string partitioning that is time-consuming at
best. Of course, if there is no need to ever group employees by education, the
variable-length string might be acceptable from a design point of view. However, as
database designers we know that, sooner or later, information requirements are
likely to grow, so the string storage is probably a bad idea from that perspective, too.
3. Finally, the most flexible way to deal with multi-valued attributes is to create a
composite entity that links employees to education. By using the composite entity,
there will never be a situation in which additional attributes must be created within
the EMPLOYEE table to accommodate people with multiple certifications. In short,
we eliminate the generation of nulls. In addition, we gain information flexibility
because we can also store the details (date earned, place earned, etc.) for each of
the educational attainments. The (simplified) structures might look like those in
Figure Q4.16 A and B.
Figure Q4.16a The Ch04_Questions Database Tables
Figure Q4.16b The Ch04_Questions Relational Diagram
Page 14 of 18
By looking at the structures shown in Figures Q4.16a and Q4.16b, we can tell that the
employee named Romero earned a Bachelor’s degree in 1989, a Certified Network
Professional certification in 2002, and a Certified Data Processing certification in 2004. If
Randall were to earn a Master’s degree and a Certified Public Accountant certification later,
we merely add another two records in the EMP_EDUC table. If additional educational
attainments beyond those listed in the EDUCATION table are earned by any employee, all
we need to do is add the appropriate record(s) to the EDUCATION table, then enter the
employee’s attainments in the EMP_EDUC table. There are no nulls, we have superb
query capability, and we have flexibility. Not a bad set of design goals!
The database design on which Figures Q4.16a and Q4.16b are based is shown in Figure
Q4.16c.
Figure Q4.16c The Crow’s Foot ERD for the Ch04_Questions Database
Page 15 of 18
Using Tools to draw ERD
(i) Basic ERD showing only primary keys
The cardinality of the places relationship at the ORDER end could also be (1,N) rather than
(0,N) as illustrated – this is a business rule (“What constitutes a customer for the company”)
(ii) Complete ERD showing all attributes
Page 16 of 18
Practical Work
NOTE for ER diagrams:
● lines should be straight lines (although they may be stepped), and
● lines must not cross
Question 1 – note this conceptual model shows keys only
Page 17 of 18
Question 2 – note this Conceptual Model shows all attributes
Page 18 of 18