Repeat steps below for the
Describe the ER schema:
A list of entities, attributes, and primary keys.
A list of relationships (unary, binary, ternary, etc.)
Participation and cardinality
constraints from business rules.
Represent the ER schema in an
Lucidchart as demonstrated in class
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.
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)
book is written by at least one
. Because there can possibly
be multiple authors for a book, this relationship is described by an attribut
; values a
Ms. Contract maintains author information even if they have not authored a
book as yet.
Each publishing company is iden
tified by a unique name
one or two
Each book must be u
eview by at least one publishing company, and this relationship is d
escribed by an expiration date.
There are several editors work
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
ontract for that book. There is an attribute date associated with each contract.
his is a ternary relationship.
Entities, Attributes and
, bokPubYear, bokPrice, bokPages)
, -autFirstName, -autLastName, autPhone)
, edtPhone, =countEditors)
to 1 or more
1 Author to 0 or more Books
1 Book to 1 or more Publisher
1 Publisher to 0
or more Books
Note: Inferred by best judgment
1 Publisher to
1 or more Editor
1 Book and 1 Publisher to 1 Author
1 Author and
– Note: Inferred by best judgment.
1 Author and 1 Publisher to 0 or more Books
– Note: Inferred by best judgment.
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
assigned by the f
irm and is required to store her
(in two fields – first name and last name)
and date of birth. If an employee is currently married to another employee of
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
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.,
, 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
e must store the
name and an
estimated cost of each project.
n employee can have many skills (preparing
, 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
may prepare requisitions for the
Refinery project and prepare requisitions as well as
(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.
Entities, Attributes and Primary Keys
Relationships, Attributes, Degrees, Participating Entities and Constraints