BUDT 703 Fall 2022 Homework #1 – Entity-Relationship (ER) Model

Due by 11:59pm, Monday, September 19^th, 2022

Note: Some business descriptions will not be captured in the model.

Share your final model with your TAs on the Lucidchart.

Insert your final ER diagram downloaded from the Lucidchart onto this document.

This file name must be renamed toHW1_YourLastName_YourFirstName.docxfor submission.

Repeat steps below for theTerps Consultantdesign.

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 attribute role; values are primary-author, illustrator, translator, etc.

·Ms. Contract maintains author information even if they have not authored any book as yet.

·Each publishing company is identified by a unique name with one or two phone numbers.

·Each book must be under-review by at least one publishing company, and this relationship is described by an expiration date.

·There are several editors working 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 signs a contract for that book. There is an attribute date associated with each contract.
– Note: This is a ternary relationship.

Example Answers:

ER Schema:

Entities, Attributes and Primary Keys

Book (
bokISBN
, bokPubYear, bokPrice, bokPages)

Author (
autName
, -autFirstName, -autLastName, autPhone)

Publisher (
pubName
, pubPhone[1..2])

Editor (edtName, 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: binary relationship

1 Publisher to 1 or more Editors

1 Editor to 1 Publisher

Sign (conDate): 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 300 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 firm and is required to store her or his 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 identifiers. We must store the name and an estimated cost of each project. An 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 project. Each skill is assigned an identifier, and we must store a short description of each skill. (For example, an employee Murphy 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: