Repeat steps below for the
Terps
Consultant
design.
1.
Describe the ER schema:
i.
A list of entities, attributes, and primary keys.
ii.
A list of relationships (unary, binary, ternary, etc.)
and attributes
.
iii.
Participation and cardinality
constraints from business rules.
2.
Represent the ER schema in an
ER diagram
using
Lucidchart as demonstrated in class
.
Example: Publisher
Ms. Contract would like you to design a conceptual schema for her agency using the entity-relationship model. She represents authors and publishing companies
,
and helps them draw up contracts.
The business
descriptions
include:
•
Each book is described by a
unique ISBN, year of publication, sales price, and
number of pages.
•
Each author is described by a unique name
(recorded as first name and last name)
and
a
phone number.
•
Each
book is written by at least one
author
. Because there can possibly
be multiple authors for a book, this relationship is described by an attribut
e
role
; values a
re primary-author,
illustrator, translator
, etc.
•
Ms. Contract maintains author information even if they have not authored a
ny
book as yet.
•
Each publishing company is iden
tified by a unique name
with
one or two
phone number
s
.
•
Each book must be u
nder
-r
eview by at least one publishing company, and this relationship is d
escribed by an expiration date.
•
There are several editors work
ing
for each publisher
, and each publisher has at least one editor.
Number of editors should be available.
•
However, editors are
not independent of publishers.
Therefore, each editor is identified by a combination of a unique name and the publisher name
, and a phone number
.
•
For each book that is finally completed and published by a publisher
, there is a single author who sign
s a
c
ontract for that book. There is an attribute date associated with each contract.
–
Note
:
T
his is a ternary relationship.
Example Answers:
ER Schema:
Entities, Attributes and
Primary Key
s
Book (
b
ok
ISBN
, bokPubYear, bokPrice, bokPages)
Author
(
a
ut
Name
, -autFirstName, -autLastName, autPhone)
Publisher
(
p
ub
Name
, pubPhone[1..2])
Editor
(
e
dt
Name
, edtPhone, =countEditors)
Relationships,
Attributes,
Degrees
,
Participating Entities
and Constraints
Write
(role)
:
binary relationship
1
Book
to 1 or more
Author
1 Author to 0 or more Books
Review
(expDate)
:
binary relationship
1 Book to 1 or more Publisher
1 Publisher to 0
or more Books
–
Note: Inferred by best judgment
.
Work
:
b
inary relationship
1 Publisher to
1 or more Editor
s
1 Editor
to 1
Publisher
Sign
(
conD
ate)
:
ternary relationship
1 Book and 1 Publisher to 1 Author
1 Author and
1
Book
to
0 or
1
Publisher
– Note: Inferred by best judgment.
1 Author and 1 Publisher to 0 or more Books
– Note: Inferred by best judgment.
ER Diagram:
![]()
Terps
Consultant
Terps Consultant
is a
consultant firm with approximately 3
00 employees. A database is required to keep track of all employees, their skills, projects assigned, and departments worked in. Every employee has a unique
identifier
assigned by the f
irm and is required to store her
or h
is
full
name
(in two fields – first name and last name)
and date of birth. If an employee is currently married to another employee of
Terps Consultant,
the date of marriage and who is married to whom must be stored; however, no record of marriage is required if an employee’s
spouse is not also an employee.
There are 11 different departments, each with a unique
identifier and
name. Each department has a phone number.
An employee reports to exactly one department.
Many employees can work on a project. An employee can work on many projects (e.g.,
Capital
Refinery,
Metro
Transportation
, and so on) but can only be assigned to at most one project in a given city. For each city, we are interested in
the state it locates and
its population. Projects are distinguished by
unique
project
identifier
s
. W
e must store the
name and an
estimated cost of each project.
A
n employee can have many skills (preparing
project management
,
auditing
, and so on), but she or he may use only a given set of skills on a particular project. Employees use each skill that they possess in at least one p
roject. Each skill is assigned an identifier
, and we must store a short description of each skill. (For example, an employee M
urphy Diamonds
may prepare requisitions for the
Capital
Refinery project and prepare requisitions as well as
auditing
for
Metro
Transportation
.)
Answers:
(Note: ER model should not include any FK.
State any assumption that you believe you have to make in order to develop a complete model.
)
ER Schema:
Entities, Attributes and Primary Keys
Relationships, Attributes, Degrees, Participating Entities and Constraints
ER Diagram:
BUDT 703
HW 1
1