atabase Design Using ER/EER Data Model Suppose that you find a job in the Human

Question

atabase Design Using ER/EER Data Model

Suppose that you find a job in the Human Resources Department of The DreamCompany.
You are responsible for building a database to store various information about the employees,
departments, and so on. In this part of the project, you will complete a conceptual design using
ER/EER data model for a much simplifed HR database based on the following set of requirements:

The employees work in departments for some projects.

An employee can work on multiple projects that may be participated by several departments.

Any employee can only be either a salariedEmp or an hourlyEmp but not both. An employee is uniquely identified by a ssn. We should record dob(date of birth), name, and address of the employee. Name is composed of Fname, Minit, and Lname.

A salariedEmp has a monthly salary and an hourlyEmp has a rate of hourPay.

An employee may have dependents (described by name, and relationship). Dependents of different employees may have the same names.

Each department, identified by deptNum, has deptName and one or more locations. The numEmp (number of employees) can be regarded as a calculated attribute of the department.

A department has a manager who is also a salaried employee.

A project is identified by projName and projNum together. It has a description (projDesc).

Please draw a ER/EER diagram

(only answer below )

Specify the domains of each attribute listed by relations. If you have special considerations such as Nullness and uniqueness, please point out.

Add semantic checks. SSN must be exactly nine digits, and hourPay must be at least $7.50.

Convert your ER/EER diagram into relational tables.

Specify the entity constraints (by underlining) and referential integrity constraints (by FK-PK relationship arrows).

Explanation / Answer

An entity– relationship display (ER show) is a methodical method for depicting and characterizing a business procedure. An ER show is ordinarily actualized as a database. The principle parts of E-R display are: element set and relationship set.

Here are the geometric shapes and their importance in an E-R Diagram –

Square shape: Represents Entity sets.

Ovals: Attributes

Precious stones: Relationship Set

Lines: They connect ascribes to Entity Sets and Entity sets to Relationship Set

Twofold Ellipses: Multivalued Attributes

Dashed Ellipses: Derived Attributes

Twofold Rectangles: Weak Entity Sets

Twofold Lines: Total cooperation of a substance in a relationship set

An example E-R Diagram:

E-R Diagram

Multivalued Attributes: A characteristic that can hold different qualities is known as multivalued trait. We speak to it with twofold circles in an E-R Diagram. E.g. A man can have in excess of one telephone numbers so the telephone number trait is multivalued.

Determined Attribute: An inferred trait is one whose esteem is dynamic and got from another characteristic. It is spoken to by dashed ovals in an E-R Diagram. E.g. Individual age is an inferred trait as it changes after some time and can be gotten from another characteristic (Date of birth).

E-R outline with multivalued and determined traits:

Multivalued and determined property

Add up to Participation of an Entity set:

A Total investment of a substance set speaks to that every element in element set must have no less than one relationship in a relationship set. For instance: In the beneath chart every school must have no less than one related Student.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.