1.6.

2 Database Design for a University Organization

To illustrate the design process, let us examine how a database for a university
could be designed. The initial specification of user requirements may be based
on interviews with the database users, and on the designer's own analysis of
the organization. The description that arises from this design phase serves as the
basis for specifying the conceptual structure of the database. Here are the major
characteristics of the university.
Theuniversity is organized into departments. Each department is identified
by a unique name (dept name), is located in a particular building, and has a
budget.
" Each department has a list of courses it offers.Each
course has associated with
it a course id, title, dept name, and credits, and may also
prerequisites. have have associated
Instructors are identified by their unique ID. Each
ciated department (dept name), and salary. instructor has name, asso
ote Students are identified by their
ated major department unique ID. Each student has a name, an
associ
(dept_name), and tot.cred (total credit hours the student
earned thus far).
 Practice Exercises 33

Knowledge-discovery techniques attempt to discover automatically statisti

cal rules and patterns fromdata. The field ofdata mining combines knowledge
discovery techniques invented by artificial inteligence researchers and sta
tistical analysts, with efficient implementation techniques that enable them
tobe used on extremely large databases.
" There are four differenttypes of database-system users, differentiated by the
way they expect to interact with the system. Different types of user interfaces
have been designed for the different types of users.

Review Terms

Database-management
(DBMS)
system o Data-definition language

Database-system applications Data-manipulation language

d File-processing systems o Query language
" Data inconsistency Metadata
Consistency constraints Application program
" Data abstraction Normalization
" Instance
Data dictionary
" Schema
Storage manager
o Physical schema Query processor
oLogical schema " Transactions
Physical data independence o Atomicity
" Data models o Failure recovery
o Entity-relationship m o d e l o
o Concurrency control
o Relational data models
oObject-based data model Two- and three-tier database archi
tectures
o Semistructured data model " Data mining
" Database languages " Database administrator (DBA)

Practice Exercises

1.1 This chapter has described several major advantages of a database system.
What are two disadvantages?
1.2 List five ways in which the type declaration system of alanguage such as
Java or C++ differs from the data definition language used in a database.
 34
Chapter 1 Introduction
take in setting up a database for
1.3 List six major steps that you would
particular enterprise. information that a university would main-
i 1.4 List at least 3different types of
tain, beyond those listed in Section 1.6.2. YouTube. Consider each
Sritdb similar to
1.5 Suppose you want to build a video site
disadvantages of keeping data inthea
listed in Section 1.2, as
ehe points ofeach of these points to
system. Discussthe relevance
file-processing
orage of actual video data, and to mnetadataabout the video,such as title,
viewed it.
the user who uploaded it. tags, and which users
Keyword queries used in Web search are quite different from database
0 of the way the
queries. List key differences between the two, in terms
queries are specified, and in terms of what is the result of a query.
Exercises
1.7 List four applications you have used that most likely employed adatabase
system to store persistent data.
1.8 List four significant differences between a file-processing system and a
DBMS.

1.9 Explain the concept of physical data indeperndence, and its importance in
database systems.
1.10 List five responsibilities of a database-management system. For each re
sponsibility, explain the problems that would arise if the responsibility
were not discharged.
1.11 List at least two reasons why database systems support data manipulation
using a declarative query language such as SQL, instead of just providing
a a library of C or C++ functions to carry out data
manipulation.
1.12 Explain what problems are caused by the design of the table in Figure 1.4.
1.13 What are five main functions of a database administrator?
1.14 Explain the difference between two-tier and three-tier architectures. Which
is better suited for Web applications? Why?
1.15 Describe at least 3 tables that might be used to store
social-networking system such as Facebook. information in a
Tools
There are a large number of commercial
ones include: IBM DB2 database systems in use today. The major
ierosoftsQL Server
Toformix
(www.miicbrosoft
(www. m.com/.com/softswq),are/data/db2),
Oracle (www.oracle.com),
Sybase (www.sybase.com), and IBM
(www.ibm.com/software/data/informix).
Some of these systems are available
 Practice Exercises 53

" Nullvalue " Referential integrity constraint

" Database schema " Schema diagram
" Database instance
Query language
" Relation schema
o Procedural language
" Relation instance
" Keys oNonprocedural language
Operations on relations
oSuperkey o Selection of tuples
oCandidate key
o Selection of attributes
o Primary key
o Natural join
Foreign key oCartesian product
Referencing relation o Set operations
o Referenced relation
Relational algebra

Practice Exercises
2.1 Consider the relational database of Figure 2.14. What are the appropriate
oe n primary keys?
2.2 Consider the foreign key constraint from the dept name attribute of instructor
to the department relation. Give examples of inserts and deletes to these
relations, which can causea violation of the foreign key constraint.
2.3 Consider the time slot relation. Given that a particular time slot can meet
more than once in a week, explain why day and start timne are part of the
primary key of this relation, while end time is not.
2.4 In the instance of instructor shown in Figure 2.1, no two instructors have
the same name. From this, can we conclude that name can be used as a
superkey (or primary key) of instructor?
2.5 What is the result of first performing the cross product of student and
advisor, and then performing a selection operation on the result with the
predicate sid =ID? (Using the symbolic notation of relational algebra, this
lnot si query can be written as Osid=ID(student x advisor).)

blaodet o h employee (person.name, street, city)

orks (person_nanme, company_name, salary)
company (company name, city)
Figure 2.14 Relational database for Exercises 2.1, 2.7, and 2.12.
54 Relational Model
Chapter 2 Introduction tothe
name. branchcity,a5sets)
street, cUstomer city)
branchbranch
(customer name,customer
amount)
CusBomer branchname,
(loan.number,
loan (customer name,loan number)balance)
borrover(account.number, branch name,
dCCOunt name,accountnumber)
depositor(customer 2.13.
2.8, 2.9, and
database for Exercises
igure2.15 Banking result of a relational
use the
expressions, which For each expression,
2.6 Consider the following inputtoanother operation.
algebra operation as the
expression does.
explain in words what the
student
a. Oyear> 2009(takes) M
D. year>2009(takes Mstudent)
StudentMtkes)
C.
D,name, course.id( an expression in the
2.14. Give
2.7 Consider the relational database of Figure queries:
relational algebra to express eaeach of the following
the names of all employees who liye in city Miami".
a. Find
all employees whose salary is greater than $100,000.
b. Find the names of whose
C. Find the names of all emplovees who live in "Miami and
salary is greater than $100,000.
an expression in the rela
2.8 Consider the bank database of Figure 2.15. Give
at tional algebra for each of the following queries.
a. Find the names of all branches located in "Chicago".
in branch "Down
b. Find the names of all borrowers who have a loan
town".

Exercises
2.9 Consider the bank database of Figure 2.15.
a. What are the appropriate primary keys?
b. Given your choice of primary keys, identify appropriate foreign keys.
2.10 Consider the advisor relation shown in Figure 2.8, with sid as the primary
key of advisor. Suppose a student can have more than one advisor. Then,
would sid still be a primary key of the advisor relation? If not, what should
the primary key of advisor be?
2.11 schema.
Describe the differences in meaning between the terms relation and relation
 Bibliographical Notes 55

2.12 Consider the relational database of Figure 2.14. Give an expression in the
relational algebra to express each of the following queries:
a. Find the names of all employees who work for "First Bank Corpora
tion".
b. Find the names and cities of residence of all employees who work for
"First Bank Corporation":.
C. Find the names, street address,and cities of residence of all employees
who work for "First Bank Corporation" and earn more than $10,000.
2.13 Consider the bank database of Figure 2.15. Give an expression in therela
tional algebra for each of the following queries:
a Find allloan numbers with aloan value greater than $10,000.
b. Find the names of all depositors who have an account with a value
greater than $6,000.
C. Find the names of all depositors who have an account with a value
greater than $6,000 at the "Uptown" branch..
2.14 List two reasons why null values might be introduced into the database.
2.15 Discuss the relative merits of procedural and nonprocedural languages.

Bibliographical Notes
E.E Codd of the TBM San Jose Research Laboratory proposed the relational model
in the late 1960s (Codd [1970). This work led to the prestigious ACM Turing
Award to Codd in 1981 (Codd [1982]).
After Codd published his original paper, several research projects were formed
with the goal of constructing practical relational database systems, including
System Rat the IBM San Jose Research Laboratory, Ingres at the University of
California at Berkeley, and Query-by-Example at the IBM T. J. Watson Research
Center.
Many relational database products are now commercially available. These
include íBM's DB2 and Informix, Oracle, Sybase, and Microsoft SQL Server. Open
source relational database systems include MysQL and PostgresQL. Microsoft
Access is a single-user database product that is part of the Microsoft Office suite.
Atzeni and Antonellis [1993], Maier [1983], and Abiteboul et al. [1995] are
texts devoted exclusively to the theory of the relational data model.
 Exercises 319

Exercises

7.14 Explain the distinctions among the terms primary key, candidate key, and
superkey.
7.15 Construct an E-R diagram for a hospital with a set of patients anda set of
medical doctors. Associate with each patient a log of the various tests and
examinations conducted.
7.16 Construct appropriate relation schemas for each of the E-R diagrams in
Practice Exercises 7.1 to 7.3.
informa
7.17 Extend the E-R diagram of Practice Exercise 7.3 to track the same
tion for allteams in a league.
7.18 Explain the difference between a weak and a strong entity set.
simply adding
7.19 We can convert any weak entity set to a strong entity set bysets?
appropriate attributes. Why, then, do we have weak entity
online bookstore.
7.20 Consider the E-R diagram in Figure 7.29, which models an
a. List the entity sets and their primary keys.
downloadable video
b. Suppose the bookstore adds Blu-ray discs andin one or both formats,
toits collection. The same item may be present
this addition,
with differing prices. Externd the E-R diagram to model
ignoring the effect on shopping baskets.
C. Now extend the E-R diagram,using generalization, to model the case
contain any combination of books,
where a shopping basket may video.
Blu-ray discs, or downloadable
company toprovide to its dealers to
7.21 Design adatabase for an automobile records
assist them in maintaining customer and dealer inventory and to
assist sales staff in ordering cars. number (VIN). Each
Each vehicle is identified bya vehicle identificationbrand offered by the
individual vehicle is a particular model of a particular
Jaguar of Tata Motors).
company (e.g., the XF is a model of the car brand but an individual
Each model can be offered with a variety of options,
available options. The database
car may have only some (or none) of the brands, and options, as well as
needs to store information about models,
information about individual dealers, customers, and cars.
relational schemas,
Your design should include an E-R diagram, a set of constraints.
and a list of constraints, including primary-key and foreign-key
7.22 Design a database foraworld-wide package delivery company (e.g., DHL
o o r FedEX). The database must be able to keep track of customers (who ship
do both.
items) and customers (who receive items); some customers may
 E-R Model
320 Chapter 7 Database Design and the
publisher
author
1ane
address
address phone
URL URL
Customer
email
name

written_by Apublished by> pneaddress

phone

book number
ISBN V shovping_basket basket_of
title
year contains basket_id
price

number
warehouse
Code
stocks addressot
phone

Figure 7.29 E-R diagram for Exercise 7.20.

Each package must be identifiable and trackable, so the database must

be able to store the location of the package and its history of locations.
Locations include trucks, planes, airports, and warehouses.
o e Your design should include an E-R
and alist of constraints, including diagram,and
primary-key
a set of relational schemas,
(eoduM 7.23 Design a database for an airline. The foreign-key constraints.
ubivibt tomers and their reservations, flights anddatabase must keep track of cus
their status,seat assignments On
individual flights, and the schedule and routing of future
ow bs anog Your design should flights.
and alist of constraints, include an E-R diagram, a set of relational
schemas,
including primary-key and foreign-key constraints.
7.24 In Section 7.7.3, we
ure 7.27a) using binary represented a ternary relationship (repeated in Fig
ku the alternative shown inrelationships, as shown in Figure 7.27b.
Figure 7.27c. Discuss the relative meritsConsider
odekon two alternative representations of a ternary relationship by of these
fob vet ships. binary relation
 Bibliographical Notes 321

7.25 Consider the relation schemas shown in Section 7.6, which were generated
from the E-R diagram in Figure7.15. For eachschema, specify what foreign
key constraints, if any, should be created.
7.26 Design ageneralization-specialization hierarchy for a motor vehicle sales
company. The company sells motorcycles, passenger cars, vans, and buses.
Justify your placement of attributes at each level of the hierarchy. Explain
why they should not be placed at a higher or lower level.
7.27 Explain the distinction between condition-defined and user-defined con
straints. Which of these constraints can the system check automatically?
Explain your answer.
7.28 Explain the distinction between disjoint and overlapping constraints.
7.29 Explain the distinction between total and partial constraints.
 1.2 An Example 7

Users/Programmers

Database
System
Application Programs/OQueries

DBMS
Software Software to Process
Queries/Programs

Software to Access
Stored Data

Stored Database
Definition Stored Database
(Meta-Data)
Figure 1.1
Asimplified database
system environment.
 Chapter1 Databases and Database Users

STUDENT Major
Student number Class
Name
1
CS
Smith 17
2
CS
Brown 8

COURSE Course number Credit_hours

Course name
Department
CS1310
4 CS
Intro to Computer Science
CS3320 4 CS
Data Structures
MATH2410 3 MATH
Discrete Mathematics
CS3380 3 CS
Database

SECTION
Section_identifier Course_number Semester Year Instructor
85 MATH2410 Fall 07 King
92 CS1310 Fall 07 Anderson
102 CS3320 Spring 08 Knuth
112 MATH2410 Fall 08
Chang
119 CS1310 Fall 08 Anderson
135 C$3380 Fall 08 Stone

GRADE REPORT
Studentnumber Section_identifier |o <<<
Grade
17 112 B
17
119
8 85 A
92
102 B
8
135

PREREQUISITE
Figure 1.2
Course_number Prerequisite _number
CS3380
A database that stores CS3320
CS3380
student and course
infomation. CS3320
MATH2410
CS1310
RELATIONS Figure 1.3
An example of a
Relation_name No of columns
database catalog for
STUDENT 4 the database in
COURSE 4 Figure 1.2.
SECTION 5
GRADE REPORT 3

PREREQUISITE

COLUMNS

Column_name Data_type Belongs_to_relation

Name Character (30) STUDENT

Student_number Character (4) STUDENT

Class Integer (1) STUDENT
Major Major_type STUDENT
Course name nrCharacter (10) COURSEh
Course_number XXXXNNNN COURSE

Prerequisite_number XXXXNNNN PREREQUISITE

Note: Major_type is defined as an enumerated type with all known majors.

XXXXNNNN is used to define a type with four alphabetic characters followed by four numeric digits.
 Review Questions database, DBMS, database system, data-
terms: data,
end user, canned
view, DBA,end
1.1. Define the following dataindependence, user
base catalog program persistent object, meta-data, and
system,
deductive database
Iransaction,processingapplication.
transaction
databases? Briefly discuss each.
of actionsinvolve
1.2. What four maintypes database approach and|how it differs
the
1.3. Discuss the maincharacteristics of
from traditional file systems. the database designers?
the DBA and
1.4. What are the responsibilities of programmer in a
system ar
analyst and an application
1.5. Explain the roles of a
database system.
approach.
1.6. Discuss the advantages of using the DBMS
is desirable to develop customized database
1.7. Under what circumstances it
applications?

EXercisesdaget o bosslar toLTUl ntins

1.8. Identify some informal queries and update operations that you would expect
to apply to the database shown in Figure 1.2.
1.9. Discuss the various storage structures and search techniques used for effi
cient query procesing
1.10. Specifyallthe relationships among the records of the database shown in
Figure 1.2.
1.11. Give some additional views that may be needed by other user groups for the
database shown in Figure 1.2.
1.12. Cite some examples of integrity constraints that you think can apply to the
database shown in Figure 1.3.
1.13, How the abstraction process in the database approach is different from the
object-oriented and object-relational databases?
 Selected Bibliography

1.14. Consider Figure 1.2.

a. If the name of the CS' (Computer Science) Department changes to CSSE'
(Computer Science and Software Engineering) Department and the cor
responding prefix for the course number also changes, identify the col
umns in the databasethat would need to beupdated.
b. Can you restructure the columns in the COURSE, SECTION, and
PREREQUISITE tables so that only one column will need to be updated?
oncepls
Figure 2.1 STUDENT
Schema diagram for
the database in Major
Name Student number Class
Figure 1.2.
COURSE
Course_name Course_number Credit _hours Department
PREREQUISITE
Course_number Prerequisite_number
SECTION
Section identifier Course_number Semester Year Instructor

GRADE REPORT
Student _number| Section_identifier Grade
 Exercises 55

Finally, we classified DBMSs according toseveral criteria: data model, number of

users, number of sites, types of access paths, and cost. We discussed the availabil
ity of DBMSs and additional modules--from no cost in the form of open source
software to configurations that annualiy cost millions to maintain. We also
pointed out the variety of licensing arrangements for DBMS and related prod
ucts. The main classification of DBMSs is based on the data model. We briefly
discussed the main data models used in current commercial DBMSs.

Review Questions
2.1. Define the following terms: data model, database schema, database state,
internal schema, conceptual schema, external schema, data independence,
DDL, DML, SDL, VDL, query language, host language, data sublanguage,
database utility, catalog, client/server architecture, three-tier architecture,
and n-tier architecture.
2.2. What concepts do high-level or conceptual data models use? How are they
different from low-level or physical data models?
2.3. What is the difference between a database schema and a database state?

2.4. Describe the three-schema architecture. Why do we ned mappings among

schema levels? How do different schema definition languages support this
architecture?

2.5. What is the difference between logical data independence and physical data
independence? Which one is harder to achieve? Why?
2.6. Why low-level DMLs are also called record-at-a-time DMLs and high-level
DMLs are called set-at-a-time DMLs?
2.7. How forms-based interfaces are different from the menu-based interfaces?

2.8. Discuss the role of the precompiler in a DBMS environment.

2.9. What is the difference between the two-tier and three-tier client/server
he architectures?
2.10. What is the use of data dictionary in a database system?
2.11. What is the additional functionality incorporated in n-tier architecture
O(n>3)?

Exercises
2.12. Think of different users for the database shown in Figure 1.2. What types of
applications would each user need? To which user category would each
belong, and what type of interface would each need?
 Architecture
Concepts and
2 Database System
you arefamiliar, Design a
which
application with application, usingthe schema
2.13. Choose a database
database for that information and
of Fig.notation
and show asample
ures 1.2 and 2.1. What types of additional
schema? Think ofseveral users of constraints
Wouldyou liketo
represent inthe
for cach.
your
database, and design a view
number of sites over which the
2.14. How DBMS is classified on the basis of data-
base is distributed? Give example ofeach.
2.15. Consider the following database schema.Inthe Book tbl table, the ISBN
column must be unique to provide aguarantee uniqueness for aspecific col-
umn (to prevent two books from having the same 1SBN). ldentify the ol
umn or the group of columns in the other tables that must be unique across
all rows in the table.

Book_tbl
ISBN BookName Price
PublishingYear
Publishertbl
Publisher_ID PublisherName EmaillD Phone
Author_tbl
Author ID
AuthorName EmaillD
Phone

Selected Bibliography
Many database textbooks,
rishnan and Gehrke (2003),including Date (2004),
(1995), provide a discussionGarcia-Molina et al. Silberschatz et al. (2011),
Ramak
Tsichritzis and
Lochovsky
of the
various (2002, 2009), and Abiteboul et al.
and Klug (1978) and (1982) is an early database concepts
presented here.
was first Jardine (1977) present the textbook
on data
Nationalsuggested
in the models. Tsichritzis
s DBTG
Standards
tional data model andInstitute (ANSI)CODASYL three-schema
report (1971) architecture, which
proposed standardfor some of its report
and later in an American
possible ext(1975).
An
in-dept
is given inanalysis h
descriobjbinegct-oriented avaidatalabasesble described
e nsions
Many documents of the rela-
XML are is Codd (1990). The
Examples of database on the in Cattell et al. (2000).
tp:/ www.eti.com) utandilitiethes are the ETI
(htEmbarcadero Web, such ass XML (2005).
Technologies (http:datl abase Connect, Analyze and Transform tools
administration tool, DBArtisan,
Iw w.embarcadero.com). from
 ~

2
Figure 3. . for the COMPANY database. The diagrammatic notation is introduced gradually throughout
An ER schema d1ag':marized in Rgure 3.14. ..
this chapter and is s
 3.3 Entity Types, Entity Sets, Attributes, and Keys 65

Name = Sunco Oil

Name = John Smith

Address = 2311 Kirby

Houston, Texas 77001
c1 - --- Headquarters = Houston
Figure 3.3
Age=55 Two entities,
EMPLOYEE e 1 , and
COMPANY c1, and
Home_phone = 713-749-2630 President = John Smith their attributes.

Several types of attributes occur in the ER model: simple versus composite 1 single-
valued versus multivalued, and stored versus derived. First we define these attribute
types and illustrate their use via examples. Then we discuss the concept of a NULL
value for an attribute.

Composite versus Simple (Atomic) Attributes. Composite attributes can be

divided into smaller subparts, which represent more basic attributes with indepen-
dent meanings. For example, the Address attribute of the EMPLOYEE entity shown in
3
Figure 3.3 can be subdivided into Street_address, City, State, and Zip, with the values
'2311 Kirby', 'Houston', 'Texas', and '77001'. Attributes that are not divisible are
called simple or atomic attributes. Composite attributes can form a hierarchy; for
example, Street_address can be further subdivided into three simple component
attributes: Number, Street, and Apartment_number, as shown in Figure 3.4. The value
of a composite attribute is the concatenation of the values of its component simple
attributes.
Composite attributes are useful to model situations in which a user sometimes
refers to the composite attribute as a unit but at other times refers specifically to its

Address Figure 3.4

A hierarchy of
composite attributes.

Street_address City State Zip

Number Street Apartment_number

3
Zip Code is the name used in the United States for a five-digit postal code, such as 76019, which can be
extended to nine digits, such as 76019-0015. We use the five-digit Zip in our examples.
 Entity Type Name: EMPLOYEE COMPANY Figure 3.6
Name, Age, Salary Two entity types ,
Name, Headquarters, President
EMPLOYEE and CO~
PANY , and some
e1 • member entities of
C1 •
each.
(John Smith, 55, 80k) (Sunco Oil, Houston, John Smith)

e2 • C2 •
Entity Set:
(Extension) (Fred Brow~, 40, 30K) (Fast Computer, Dallas, Bob King)

83.
•
{Judy Clark, 25, 20K) •
•
•
•
•

4 Forthose familiar with XML, we should note that complex

attributes are similar to complex elements in XML
(see Chapter 13).
 Keys 69
3.3 Entity Types, Entity Sets , Attributes, and

(a)

00 ~R
e, Model, Year, {Color}
Registration (Number, State), Vehicle_id, Mak

CAR1
ertible, 2004 {red, black})
((ABC 123, TEXAS), TK629, Ford Mustang, conv

CAR2 Figure 3.7

ima, 4-door, 2005, {blue})
((ABC 123, NEW YORK), WP9872, Nissan Max The CAR entity type
with two key attributes,
CAR3 Registration and
, 4-door, 2002, {white, blue}) Vehicle_id. (a) ER
((VSY 720, TEXAS), TD729, Chrysler LeBaron
• diagram notation .
• (b) Entity set with three
• entities.
 71
3.3 Entity Types, Entity Sets, Attributes, and Keys

DEPARTMENT

Manager_start_da te

Figure 3.8
Preliminary design of
entity types for the
COMPANY database.
Some of the shown
attributes will be refined
into relationships.
 3.4 Relationship Types, Relationship Sets, Roles, and Structural Constraints 73

EMPLOYEE WORKS_FOR DEPARTMENT

Figure 3.9
Some insta'1ces in
the WORKS_FOR rela-
tionship set,
which represents a
relationship type
WORKS_FOR between
EMPLOYEE and
DEPARTMENT.
 3.9 Relationship Types of Degree Higher than Two 89

(a) SUPPLIER PROJECT

PART

(b)
N
PROJECT

(c)

SUPPLIER 1®N SUPPLY Ne1 PROJECT

e
N
1
1
PART

Figure 3.17
Temary relationship types. (a) The SUPPLY relationship. (b) Three binary relationships not
equivalent to SUPPLY. (c) SUPPLY represented as a weak entity type.
 ,il;;,i,Bi tWA..J.Ui

90 Chapter 3 Data Modeling Using the Entity- Relationship (ER) Model

Figure 3.18
Anoth8r e\ Gmplo of tcr-
narvJ \ '•·~" l'~L,
... 0 ~~ b.
")111Jry rcb-
ll0nship typGs.
 3.9 Relationship Types of Degree Higher than Two 91

Figure 3.19
A weak entity type

L-C_A_N_DI_DA_T_E_;----< 0 C_O_M_PA_N_Y___.
>--~_
INTERVIEW with a
ternary identifying
relationship type.

INTERVIEW JOB_OFFER
94 Chapter 3 Data Modeling Using the Entity-Relationship (ER) Model

COLLEGE
(1, 1)

(O,N)

(O,N)

(O,N)

(1, 1)

(O,N)
(1, 1)

Figure 3.20
An ER diagram for a UNIVERSllY
database schema.
 ------r.
e'
(
oter 3 Data Modeling Using the Entity- Relationship (ER) Model

Review Questions
. . The first step in the database design process is the requirements collection
If
31
and analysis. Explain this step.
3.2. Discuss the two cases of the unknown category of NULL with the help of an
example.
3.3. Define the following terms: entity, attribute, attribute value, r~lationship
instance, composite attribute, multivalued attribute, derived attribute, com-
plex attribute, key attribute, and value set (domain).
3.4. What is an entity type? What is an entity set? Explain the differences among
an entity, an entity type, and an entity set.
3.5. Explain the difference between a single-valued attribute and a multi-valued
attribute.
3.6. What is a relationship type? Explain the differences among a relationsh ip
instance, a relationship type, and a relationship set.
3.7. What do you understand by the term ~'degree of a relationship type"? Explain
with example.
3.8. What does participation constraint specify? What are the two types of the
participation constraints?
3.9. Under what conditions can an attribute of a binary relationship type be
migrated to become an attribute of one of the participating entity types?
3.1o. When we think of relationships as attributes, what are the value sets of these
attributes? What class of data models is based on this concept?
3.11 . What is the significance of role names in the relationship? In what situa-
tions, roles names are essential?
3.12. What is the difference between weak entity types and strong entity types?
How is weak entity type identified?
3.13. Can an identifying relationship of a weak entity type be of a degree greater
than two? Give examples to illustrate your answer.
3.14. Discuss the conventions for displaying an ER schema as an ER diagram.
3.15. Discuss the naming conventions used for ER schema diagrams.

Exercises
3.16. Which combinations of attributes have to be unique for each individual
SECTION entity in the UNIVERSITY database shown in Figure 3.20 to enforce
each of the following miniworld constraints: -
a. During a particular semester and year, only one section can use a particu-
lar classroom at a particular DaysTime value.
 Exercises 97

b. During a particular semester and year, an instructor can teach only one
section at a particular DaysTime value.
c. During a particular semester and year, the section numbers for sections
offered for the same course must all be different.
Can you think of any other similar constraints?
3.17. Composite and multivalued attributes can be nested to any number of lev-
els. Suppose we want to design an attribute for a STUDENT entity type to
keep track of previous college education. Such an attribute will have one
entry for each college previously attended, and each such entry will be com-
posed of college name, start and end dates, degree entries (degrees awarded
at that college, if any), and transcript entries (courses completed at that col-
lege, if any). Each degree entry contains the degree name and the month and
year the degree was awarded, and each transcript entry contains a course
name, semester, year, and grade. Design an attribute to hold this informa-
tion. Use the conventions in Figure 3.5.
3.18. Show an alternative design for the attribute described in Exercise 1JZ-that
uses only entity types (including weak entity types, if needed) and relation-
ship types.
3.19. Consider the ER diagram in Figure 3.21, which shows a simplified schema
for an airline reservations system. Extract from the ER diagram the require-
ments and constraints that produced this schema. Try to be as precise as
possible in your requirements and constraints specification.
3.20. In Chapters 1 and 2, we discussed the database environment and database
users. We can consider many entity types to describe such an environment,
such as DBMS, stored database, DBA, and catalog/data dictionary. Try to
specify all the entity types that can fully describe a database system and its
environment; then specify the relationship types among them, and draw an
ER diagram to describe such a general database environment.
3.21. Design an ER schema for keeping track of information about votes taken in
the U.S. House of Representatives during the current two-year congress-
ional session. The database needs to keep track of each U.S. STATE's Name
(e.g., 'Texas', 'New York', 'California') and include the Region of the state
(whose domain is {'Northeast', 'Midwest', 'Southeast', 'Southwest', 'West'}).
Each C0NGRESS_PERS0N in the House of Representatives is described by
his or her Name, plus the District represented, the Start_date when the con-
gressperson was first elected, and the political Party to which he or she
belongs (whose domain is {'Republican', 'Democrat', 'Independent',
'Other'}). The database keeps track of each BILL (i.e., proposed law),
including the Bill_name, the Date_ot_vote on the bill, whether the bill
,· Passed_or_failed (whose domain is {'Yes', 'No'}), and the Sponsor (the
congressperson(s) who sponsored-that is, proposed-the bill). The data-
. base also keeps track of how each congressperson voted on each bill (domain
of Vote attribute is {'Yes', 'No', 'Abstain', 'Absent'}). Draw an ER schema
diagram for this application. State clearly any assumptions you make.
98 Chapter 3 Data Modeling Using the Entity- Relationship (EA) Model

FUGHT_LEG

FARE

Notes:
SEAT A LEG (segment) is a nonstop portion of a flight.
A LEG_INSTANCE is a particular occurrence
of a LEG on a particular date.
Figure 3.21
An ER diagram for an AIRLINE database schema.

3.22. Design an ER diagram for an IT training group database that will meet the
information needs for its training program. Clearly indicate the entities,
relationships and the key constraints. The description of the environment is
as follows: The company has 12 instructors and can handle up to 100 train-
ees for each training session. The company offers 5 advanced technology
courses, each of which is taught by a team of 2 or more instructors. Each
►
Exercises 99

instructor is assigned to a maximum of two teaching teams or may be

assigned to do research. Each trainee undertakes one Advanced technology
course per training session.
3.23. Consider the ER diagram shown in Figure 3.22 for part of a BANK database.
Each bank can have multiple branches, and each branch can have multiple
accounts and loans.
a. List the strong (nonweak) entity types in the ER diagram.
b. Is there a weak entity type? If so, give its name, partial key, and identify-
ing relationship.
c. What constraints do the partial key and the identifying relationship of the
weak entity type specify in this diagram?
d. List the names of all relationship types, and specify the (min, max)
constraint on each participation of an entity type in a relationship type.
Justify your choices.

Figure3.22
An ER diagram for a BANK database schema.
r 100
a ua

Chapter 3 Data Modeling Using the Entity- Relationship (ER) Model

DEPARTMENT
EMPLOYEE 1----<

Figure 3.23
Patt of an ER diagram
for a COMPANY PHONE
database.

e. List concisely the user requirements that led to this ER schema design.
f. Suppose that every customer must have at least one account but is
restricted to at most two loans at a time, and that a bank branch cannot
have more than 1,000 loans. How does this show up on the (min, max)
constraints?
3.24. Consider the ER diagram in Figure 3.23. Assume that an employee may
work in up to two departments or may not be assigned to any department.
Assume that each department must have one and may have up to three
phone numbers. Supply (min, max) constraints on this diagram. State clearly
any additional assumptions you make. Under what conditions would the
relationship HAS_PHONE be redundant in this example?
3.25. Consider the ER diagram in Figure 3.24. Assume that a course may or may
not use a textbook, but that a text by definition is a book that is used in some
course. A course may not use more than five books. Instructors teach from
two to four courses. Supply (min, max) constraints on this diagram. State
clearly any additional assumptions you make. If we add the relationship
ADOPTS, to indicate the textbook(s) that an instructor uses for a course,
should it be a binary relationship between INSTRUCTOR and TEXT, or a
ternary relationship among all three entity types? What (min, max) con-
straints would you put on the relationship? Why?

Figure 3.24
Part of an ER diagram
for a COURSES
database. INSTRUCTOR.,___-< COURSE

TEXT
►
Exercises 101

3.26. Consider an entity type SECTION in a UNIVERSITY database, which describes

the section offerings of co urse s. The attributes of SECTION are
Section_number, Semester, Year, Course_number, Instructor, Room_no (where
section is taught), Building (where section is taught) , Weekdays (domain is
the possible combinations of weekdays in which a section can be offered
{'MWF', 'MW', 'TT', and so on}), and Hours (domain is all possible
time periods during which sections are offered {'9-9:50 A.M.', '10-10:50
A.M .', ... , '3:30- 4:50 P.M.', '5:30-6:20 P.M. ', and so on}). Assume that
Section_number is unique for each course within a particular semes-
ter/year combination (that is, if a course is offered multiple times during
a particular semester, its section offerings are numbered I, 2, 3, and so
on). There are several composite keys for section, and some attributes
are components of more than one key. Identify three composite keys,
and show how they can be represented in an ER schema diagram.
3.27. Cardinality ratios often dictate the detailed design of a database. The cardi-
nality ratio depends on the real-world meaning of the entity types involved
and is defined by the specific application. For the following binary relation-
ships, suggest cardinality ratios based on the common-sense meaning of the
entity types. Clearly state any assumptions you make.
Entity 1 Cardinality Ratio Entity 2
1. STUDENT _____ SOCIAL_SECURITY_CARD
2. STUDENT ____ TEACHER
3. CLASSROOM ____ WALL
4. COUNTRY CURRENT_PRESIDENT
5. COURSE TEXTBOOK
6. ITEM (that can be found ORDER
in an order)
7. STUDENT CLASS
8. CLASS INSTRUCTOR
9. INSTRUCTOR OFFICE
10. EBAY_AUCTION_ITEM EBAY_BID

3.28. Consider the ER schema for the MOVIES database in Figure 3.25.
Assume that MOVIES is a populated database. ACTOR is used as a generic term
and includes actresses. Given the constraints shown in the ER schema, respond
to the following statements with True, False, or Maybe. Assign a response of
Maybe to statements that, although not explicitly shown to be True, cannot be
proven False based on the schema as shown. Justify each answer.
a. There are no actors in this database that have been in no movies.
b. There are some actors who have acted in more than ten movies.
c. Some actors have done a lead role in multiple movies.
d. A movie can have only a maximum of two lead actors.
 · h· (ER) Model
102 Chapter 3 Data Modeling Using the Entity- Relations ip

MOVIE
ACTOR

2 N

M
PRODUCER
Figure 3.25
An ER diagram for a MOVIES database schema.

e. Every director has been an actor in some movie.

f. No producer has ever been an actor.
g. A producer cannot be an actor in some other movie.
h. There are movies with more than a dozen actors.
i. Some producers have been a director as well.
j. Most movies have one director and one producer.
k. Some movies have one director but several producers.
I. There are some actors who have done a lead role, directed a movie, and
produced a movie.
m. No movie has a director who also acted in that movie.
3.29. Given the ER schema for the MOVIES database in Figure 3.25, draw an
instance diagram using three movies that have been released recently.
Draw instances of each entity type: MOVIES, ACTORS, PRODUCERS,
DIRECTORS involved; make up instances of the relationships as they exist
in
reality for those movies.
>
Laboratory Exercises 103

3.30. Illustrate the UML diagram for Exercise 3.16. Your

UML design should
observe the following requirements:
a. A student should have the ability to compute his/her GPA
and add or
drop majors and minors.
b. Each depar tment should be able to add or delete course
s and hire or ter-
minate faculty.
c. Each instructor should be able to assign or change a student's
grade for a
course.
Note: Some of these functions may be spread over multip le classes.

Laboratory Exercises
3.31. Consider the UNIVERSITY database described in Exerc
ise 3.16. Build the ER
n or
schema for this database using a data modeling tool such as ERwi
Rational Rose.
3.32. Consider a MAIL_ORDER database in which employees
take orders for parts
from customers. The data requirements are summarized as follows:
■ The mail order company has employees, each identi
fied by a unique em-
ployee number, first and last name, and Zip Code.
■ Each customer of the company is identified by a uniqu
e customer number,
first and last name, and Zip Code.
■ Each part sold by the .company is identified by a
unique part number, a
part name, price, and quantity in stock.
■ Each order placed by a customer is taken by an emplo
yee and is given a
or
unique order number. Each order contains specified quantities of one
ship
more parts. Each order has a date of receipt as well as an expected
date. The actual ship date is also recorded.
build
Design an entity-relationship diagram for the mail order database and
the design using a data modeling tool such as ERwin or Ration al Rose.

3.33. Consider a MOVIE database in which data is record

ed about the movie
industry. The data requirements are summarized as follows:
■ Each movie is identified by title and year of release.
Each movie has a
ied
length in minutes. Each has a production company, and each is classif
and so forth) .
under one or more genres (such as horror, action, drama,
it.
Each movie has one or more directors and one or more actors appear in
Each movie also has a plot outline. Finally, each movie has zero or more
quotable quotes, each of which is spoken by a particular actor appearing
in the movie.
■ Actors are identified by name and date of birth and appea
r in one or more
movies. Each actor has a role in the movie.
104 Chapter 3 Data Modeling Us
,
t
s
ing the Entity-Relationship (ER
} Model

·
• Dir ectors arc also 1c· lent'1ficd by name and date of birth and direct one
more movies. tt is pos . d' or
sible for a director to act m . .
a movie (m du mg one
that he or she may also direct
).
• Production companies are ide
ntified by name an~ each has an
production company produces one address. A
or more movies.
Design an entity- relationship dia
gram for the ~o vie da~abase an
design using a data mo deling too d enter the
l such as ERwm or Rational Rose.
3.34. Consi der a CO NF ERENCE _REVI EW database in
their resear ch papers for consid which researchers submit
eration. Reviews by reviewers are
for use in the paper selection pro re~ord~d
cess. The datab ase system caters
to reviewers who record answers pnmarily
to evaluation qu est ions for each
review and make recommendati paper they
ons regarding wheth er to accept
the paper. The data requirement or reject
s are summarized as follows:
■ Authors of papers are uniquely
identified by e-mail id. First an d
are also recorded. last names
■ Each paper is assigned a unique
identifier by the system and is de
by a title, abstract, and the name scribed
of the electronic file containing
■ A paper may have multiple the pap er.
authors, but one of the authors is
the contact author. designated as
■ Reviewers of papers are un
iquely identified by e-mail add
viewer's first name, last name, ph ress. Each re-
one number, affiliation, and top
terest are also recorded. ics of in-
■ Each paper is assigned
between two and four reviewers
each paper assigned to him or her . A reviewer rates
on a scale of 1 to 10 in four cat
technical merit, readability, origin egories:
ality, and relevance to the con
Finally, each reviewer provides ference.
an overall recommendation reg
each paper. arding
■ Each review contains two types
of written comments: one to be
the review committee only and seen by
the other as feedback to the autho
r(s).
Design an entity-relationship dia
gram for the CONFERENCE_REV
base and build the design using IEW data-
a data modeling tool such as
Rational Rose. ER wi n or
3.35. Co~side_r the. ER d~agra
m for the AIRLINE database sho
Build this design usmg a dat wn in Figure 3.21 .
a modeling tool such as ERwin
or Rational Rose.

Selected Bibliography
The enti~y-relati~nship model
was introduced by Chen (1976)
appears m Sch~1dt and Swens , and related work
on (1975), Wiederhold and Elm
Senko (1975). Smee then, nume asri (1979), an d
st rous modifications to the ER
sugge ed. We have incorporated mo del have been
some of these in ou r presentat
ion. Structural
 3.8 Example of Other Notation: UML Class Diagrams 85

-- ----- -
r_of_employ
_., ____ _____ _ DEPARTMENT
Departme (O,N)

Controlled
(1, 1) Project
Project
PROJECT
(1,N)

Number

(1, 1) Dependent

Figure 3.15
ER diagrams for the company schema, with structural constraints specified using
(min, max) notation and role names.
 Review Questions 135

4.8 Summary
To this chapter we discussed extensions to the ER model that improve its repre
sentational apabilities. We called the resulting model the enhanced ER or EER
model. We presented the concept of asubclass and its superclass and the related
mechanism of attribute/relationship inheritance. We saw how it is sometimes
necessary to create additional classes of entities, either because of additional spe
cifc attributes or because of specific relationship types. We discussed two main
processes for defining superclass/subclasshierarchies and lattices:specialization
and generalization.
Next, we showed how to display these new constructs in an EER diagram. We also
discussedthe various types of constraints that may apply to specialization or gener
alization. The two main constraints are total/partial and disjoint/overlapping. We
discused the concept of acategory or union type, which is asubset of the union of
twoor more classes, and we gave formal definitions of all the concepts presented.
We introduced some of the notation and terminology of UML for representing
specialization and generalization. In Section 4.7, we briefly discussed the discipline
of knowledge representation and how it is related to semantic data modeling. We
also gave an overview and summary of the types of abstract data representation
concepts:classification and instantiation, identification, specialization and gener
alization, and aggregation and association. We saw how EER and UML concepts
are related to each of these.

Review Questions
4.1. Explain the concept of subclass and superclass with the help of an example.
What do we call a class/subclass relation?
4.2. Define the following terms: superclass of a subclass, superclass/subclass rela
tionship, IS-A relationship, specialization, generalization, category, specific
(local) attributes, and specific relationships.
4.3. Discuss the concept of type inheritance.
4.4. Discuss user-defined and predicate-defined subclasses, and identify the dif
ferences between the two.
4.5. Explain disjointness and completeness constraints on specialization.
4.6. List some of the insertion and deletion rules that apply to specialization (and
generalization) as aconsequence of the constraints specified on thenm.
4.7. What is the difference betweena specialization hierarchy and a specializa
tion lattice?
4.8. What is the difference between specialization and generalization? Why do
we not display this difference in schema diagrams?
136 Chapter 4 The Enhanced Entity-Relationship (EER) Model

4.9. How does a category differ from a regular shared subclass? What is a cate
gory used for? Illustrate your answer with examples.
4.10. For each of the following UML terms (see Sections 3.8 and 4.6),
discuss the
corresponding term in the EER model, if any: object, class, association, aggre
gation, generalization, multiplicity, attributes, discriminator, link, link attri.
bute, reflexive association, and qualified association.
4.11. Discuss the main differences between the
grams and UML class diagrams by comparing how
notation for EER schema dia
represented in each. common concepts are
4.12. How are specialization and
generalization used to refine the conceptual
schemas during conceptual database design?
4.13. What aggregation feature is missing from the
model be further enhanced to support it? EER model? How can the EER
4.14. What are the main similarities and
modeling techniques and knowledgedifferences between conceptual database
4.15. One commonly used definition of representation techniques?
alization. Explain. ontology is a specification of a conceptu

Exercises
4.16. The conceptual database
design should be considered as an
ment process until the most iterative refine
that can help to guide the suitable design is reached. List the guidelines
design
4.17. Consider the BANK ER process for EER concepts.
it is necessary to schema in Figure 3.21, and
keep
track of different types of suppose that
(SAVINGS_ACCTS,
HOME_LOANS, ... ).CHECKING_ACCTS, .. ) and LOANS ACCOUNTS
(CAR_LOANS,
each ACCOUNT's Suppose that it is also desirable to keep track of
and each LOAN's TRANSACTIONS (deposits, withdrawals,
PAYMENTS; both of
and time. Modify the these checks, ..)
BANK schema, using ERinclude
cialization and generalization.
the amount, date,
and EER
the additional requirements. State any assumptions you concepts of spe
make about
4.18. The following
narrative describes asimplified version of the
Olympic facilities planned for the summer Olympics. Draw anorganization of
that shows the entity types, attributes,relationships, and EER diagram
this application. State any
assumptions
divided into sports complexes. you make. The Olympic specializations for
and multisport types. Sports complexes are divided intofacilities are
Multisport
nated for each sport with conmplexes have areas of the complexone-sport
alocation desig
on). Acomplex has a indicator (e.g., center, NE corner, and
area, and so on. Each location, chief organizing individual, total so
complex holds a series of events (e.g.,the occupied
track sta
 Exercises 137

dium may hold many different races). For each event there is a planned date,
duration, number of participants, number of officials, and so on. A roster of
all officials will be maintained together with the list of events each official
will be involved in. Different equipment is needed for the events (e.g., goal
posts, poles, parallel bars) as well as for maintenance. The two types of facil
For
ities (one-sport and multisport) will have different types of information.
doodeach type, the number of facilities needed is kept, together with an approxi
mate budget.
4.19. Identify allthe important concepts represented in the library database case
study described below. In particular, identify the abstractions of classifica
tion (entity types and relationship types), aggregation, identification, and
specialization/generalization.Specify (min, max) cardinality constraints
whenever possible. List details that will affect the eventual design but that
have no bearing on the conceptual design. List the semantic constraints sep
arately. Draw an EER diagram of the library database.
nCase Study: The Georgia Tech Library (GTL) has approximately 16,000
members,100,000 titles, and 250,000 volumes (an average of 2.5 copies per
book).About 10% of the volumes are out on loan at any one time. The librar
available when
ians ensure that the books that members want to borrow are
how
the members want to borrow them. Also, the librarians must know
many copies of each book are in the library or out on loan at any given time.
and
are A catalog of books is available online that lists books by author, title,
subject area. For each title in the library, a book description is kept in the
The refer
d catalog; the description ranges from one sentence to several pages.
ence librarians want to be able to access this description when members
request information about a book. Library staff includes chief librarian,
departmental associate librarians, reference librarians, check-out staff, and
library assistants.
al Books can be checked out for 21 days. Members are allowed to have only
four
s five books out at a time. Members usually return books within three to
weeks. Most members know that they have one week of grace before a
ad notice is sent to them, so they try to return books before the grace period
books.
ends. About 5% of the members have to be sent renminders to return
Most overdue books are returned within amonth of the due date. Approxi
mately 5% of the overdue books are either kept or never returned. The most
active members of the library are defined as those who borrow books at
least ten times during the year. The top 1% of membership does 15% of the
borrowing, and the top 10% of the membership does 40% of the borrowing.
About 20% of the members are totally inactive in that they are members
a who never borrow.
To become a member of the library, applicants fill out a form including their
SSN,campus and home mailing addresses, and phone numbers. The librari
ans issue a numbered, machine-readable card with the member's photo on it.
This card is good for four years. A month before a card expires, a notice is
 Entity-Relationship (EER) Model
pter 4 The Enhanced
auto
Professors at the institute are considered
sent to amember for renewal. joins the institute, his or her
maticmenmbers. When a new facultymember and alibrary card is mailed
information is pulled from the employee records
are allowed to check out books for
to hisor her campus address. Professors grace period. Renewal notices to
three-month intervals and have a two-week
professors are sent to their campus address.
reference books, rare books.
Thelibrary does not lend some books, such as lent
between books that can be
and maps. The librarians must differentiate
librarians have a list of some
and those that cannot be lent. In addition,the
such as rare or out
books they are interested in acquiring but cannot obtain, not been
of-print booksand books that were lost or destroyed but have of books that
replaced. The librarians must have a system that keeps track
acquiring. Some
cannot be lent as well as books that they are interested in
as a means
books may have the same title; therefore, the title cannot be used
Standard Book
of identification. Every book is identified by its International Two
Number (ISBN), a unique international code assigned to all books.
books with the same title can have different ISBNs if they are in different
of
languages or have diferent bindings (hardcover or softcover). Editions
the same book have different ISBNs.

The proposed database system must be designed to keep track of the mem
bers, the books, the catalog, and the borrowing activity.
4.20. Design a database to keep track of information for an art museum. Assume
that the following requirements were collected:
The museum has a collection of ART_OBJECTS. Each ART OBJECT has a
unique ld no, an Artist (if known), a Year (when it was created, if known),
a Title, and a Description. The art objects are categorized in several ways, as
discussed below.
ART_OBJECTS are categorized based on their type. There are three main
types--PAINTING, SCULPTURE, and STATUE-plus another type called
OTHERto accommodate objects that do not fall into one of the three main
types.
APAINTING has aPaint_type (oil, watercolor, etc.), material on which
it is Drawn_on (paper, canvas, wood, etc.), and Style (modern,
abstract, etc.).
ASCULPTURE or a statue has a Material from which it was created (wood,
stone, etc.), Height, Weight, and Style.
Anart object in the OTHERcategory has aType(print, photo, etc) and Style.
ART_OBJECTS are categorized as either PERMANENT_COLLECTION (objects
that are owned by the museum) and BORROWED. Information captured
about objects in the PERMANENT_COLLECTION includes
Date_acquired, Status (on display, on loan, or stored), and Cost. Informa
tion captured about BORROWED objects includes the Collection from
which it was borrowed, Date_borrowed, and Date_returned.
 Exercises 139

Information describing the country orculture of Origin (Italian, Egyptian,

American, Indian, and so forth) and Epoch (Renaissance, Modern,
Ancient, and so forth) is captured for each ART_OBJECT.
The museum keeps track of ARTIST information, if known: Name,
DateBorn (if known), Date_died (if not living), Country_of_origin, Epoch,
Main_style, and Description. The Name is assumed to be unique.
Different EXHIBITIONS Occur, each having a Name, Start_date, and End
date. EXHIBITIONS are related to all the art objects that were on display
during the exhibition.
Information is kept on other COLLECTIONS with which the museum inter
acts; this information includes Name (unique), Type (museum, personal,
etc.), Description,Address, Phone, and current Contact_person.
Draw an EER schema diagram for this application. Discuss any assumptions
you make, and then justify your EER design choices.
4.21. Figure 4.12 showsan example of an EER diagram for asmall-private-airport
database; the database is used to keep track of airplanes, their owners, air
port employees, and pilots. From the requirements for this database, the fol
lowing information was collected: Each AIRPLANE has aregistration number
[Reg#l, is of aparticular plane type [OF_TYPE, and is stored in a particular
hangar [STORED_IN]. Each PLANE_TYPE has a model number (Model], a
capacity [Capacityl, and a weight [Weight]. Each HANGAR has a number
[Number, a capacity Capacity], and a location [Location]. The database also
keepstrack of the OWNERs of each plane [OWNS] and the EMPLOYEES who
have maintained the plane [MAINTAIN], Each relationship instance in OWNS
relates an AIRPLANE to an OWNER and includes the purchase date [Pdate].
Each relationship instance in MAINTAIN relates an EMPLOYEE to a service
record [SERVICE]. Each plane undergoes service many times; hence, it is
related by [PLANE_SERVICE) to a number of SERVICE records. A SERVICE
record includes as attributes the date of maintenance [Date], the number of
hours spent on the work [Hours], and the type of work done Work code. We
use a weak entity type [SERVICE] to represent airplane service, because the
airplane registration number is used to identify a service record. An OWNER
is either a person or a corporation. Hence, we use a union type (category)
[OWNER] that is a subset of the union of corporation (CORPORATION] and
person (PERSON] entity types. Both pilots [PILOT] and employees
[EMPLOYEE] are subclasses of PERSON. Each PIL0T has specific attributes
license number [Lic num) and restrictions (Restr]; each EMPLOYEE has spe
cificattributes salary (Salary] and shift worked (Shift). All PERSON entities in
the database have data kept on their Social Security number (Ssn), name
(Namel, address [Address), and telephone number (Phone]. For CORPORATION
entities, the data kept includes name [Name], address (Address), and telephone
number (Phone). The database also keeps track of the types of planes each
pilot is authorized to fly (FLIES]and the types of planes each employee can do
maintenance work on [WORKS_ON). Show how the SMALL_AIRPORT EER
140
Chapter 4 The Enhanced Entity-Relationship (EER) Model

Salary Shift

Model)Capacity Weight WORKS_ON EMPLOYEE

PLANETYPE MAINTAIN

Restr (Lic_num
M
M N
FLIES PILOT
OF_TYPE
Date Workcode
N
Date/workcode SERVICE Hours
Reg#

1
AIRPLANE PLANE_SERVICE

Pdate

STOREDIN OWNS OWNER

M N
1

HANGAR CORPORATION Ssn PERSON

Number (Location Name Phne Name Phone
Capacity Address Address
Figure 4.12
EER schema for a SMALL_ARPORT database.

schema in Figure 4.12 may be represented in UML notation. (Note: We have

not discussed how to represent categories (union types) in UML, so you do
not have to map the categories in this and the following question.)
4.22. Show how the UNIVERSITY EER schema in Figure 4.9 may be
UML notation. represented in