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...




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


Sep 16, 2022
SOLUTION.PDF

Get Answer To This Question

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here