Exam 1- ERD/EERD

Develop an ER/EER model for the Morehead, Inc. database based on the scenario below. If you discover while drawing the diagram that the scenario is incomplete, make up reasonable explanations to complete the story. Supply your explanations along with the diagram.

Note: The ERD/EERD should be fully specified with (you may refer Figure 4.35 and Figure 5.4):

o Entities and Supertype/Subtype entities: The proper number of entities and their nameo All needed attributes of each entity

o Primary key and Foreign keyo Relationship:

• Max. and Min. connectivities (cardinalities): If needed, you can put your justifications on your answer document about why you use the connectivity (cardinality)

• Make sure that each M:N relationship should be decomposed into two 1:M relationships

• Description (text) of relationship between entities (e.g., takes, advises, has, etc. in Figure 4.35)

• The correct number of relationships between entities

o Disjoint/overlappingconstraintso Subtype discriminators o Partial/totalcompleteness

• In drawing the model, use Crows foot model (notation)

• In drawing the diagram, you can use any (diagraming) software, such as Draw.io, Microsoft

  Visio, LucidChart, or even Microsoft Word.

• Copy and paste the diagram to Microsoft Word or convert them PDF and Submit the Word file or

  PDF file

o Include diagram ONLY, not entire software screeno Your submission should be readable

• Put your name on your document

• Due date: March 10 (Sunday) 11:59PM Eastern Time

  (Scenario Below)

  Morehead, Inc., a factory manufacturing automobile parts, wants to implement a database to manage its data. Your task is to design the database to capture the data specifications through the ER/EER modeling.

  The factory has several departments. A department may have many employees, but at least three. Every employee works for one and only one department. Every department has a manager only one manager per department. Clearly, a manager is an employee of the company; but, all employees are not managers. A department may have many machines, and every machine is assigned to a specific department.

  Products are produced on machines. Every product goes through one or more machines for appropriate production operations. Likewise, several products may go through a particular machine for some standard process. Designers design the products. Some well-qualified designers may design more than one product. All designers are employees of the factory. Operators, who are also employees of the factory, operate these machines. Due to multiple shifts, several operators operate the same machine. All operators are routinely assigned to work on only one machine and no operator is kept idle. A machine is never kept idle either. An employee cantbe both a designer and an operator.

The database should capture an employees name, gender, address, and salary. Social Security number (SSN) uniquely identifies an employee. Likewise, department number, department name, type, and location, must be captured. Every machine will have a unique machine number. It will also consist of other details like name of machine, type, and vendors name. A product isidentified by its product ID. Product name and description must also be recorded. When a product goes through machining, the starting time, completion time, and hours of machining for each product on every machine must be captured. The information about a designer includes his/her qualification, specialization field, and experience in years. Operators, who are responsible for operating the machines, belong to a labor union and have certain skill sets associated with them.