Question

1 2 3 4 5 6 7 8 1. Following English statements 1 to 10 provide the data and information requirements necessary to develop a computer-based information system for labor and employee management. The company has 40 plants located in 30 states and approximately 90.000 employees. Each plant is divided into departments and further subdivided into work stations. There are 99 departments and 503 work stations in the company. In each department there is an on-line time clock at which employees report their arrival and departure. A work task is associated with one of 20 different job types. Each of the job types can be performed at each of the plants. During a given day an employee may perform more than one work task, each associated with a different job type, and each can be performed at a different work station. Each work station has an on-line data entry device at which an employee reports activity on a work task. 9 There are five worker unions represented in the company, and every employee belongs to exactly one union. Although the size of the company remains stable, about 20 percent of the employees le ave each year and are replaced by new personnel. (a) (15 points) Draw an E-R diagram (note: no extension EE-R needed) in Silberschatz's (min,max) notation for designing the given statements. NB1: Don't use attributes (but see task 1.(b) below). Use "O" as value of min in each pair (min,max) you use (i.e., assume participation always to be partial). Always define max. NB2: Use only the statements from the list above that are relevant to the construction of the E-R diagram. (As you might note, some statements are concemed with how certain data instances are defined or collected, and therefore are conceptually irrelevant.) 10

Answer 1

(a) ERD

Please find below the steps to create ERD:

Step 1: List the entities:

• Plant
• Department
• Workstation
• Employee
• Jobtypes
• Job Task
• WorkUnion

Step 2: List the relationship between the entities:

• Plant has many Departments where total departments are 99 among all 40 plants.
• Department has many Workstation upto 503 totals.
• Department has many employees.
• A JobTask is associated with one JobType where as a JobType can have many JobTasks.
• Plant can have many JobTypes (upto 20) and all JobTypes can be performed at all Plants(upto 40)
• Employee can work on many JobTasks. JobTask can be performed at different Workstation.
• Employee belongs to one WorkUnion, one WorkUnion can have many Employees.

Step 3: Create the ERD as per given instruction:

(1,99) Department divided into Plant (1,1) has (1,40) (1,503) (1,5) has WorkUnion performed at (1,M) performed at WorkStation (1,M) (1,5) (1,20) (1,20) Job Type (1,20 associated with (1,M) works on Job Task (1,M) belongs to (1,M) Employee (1,M)

(b) ERD attributes:

Let’s assign the attributes to entity as per given instruction:

Plant

Composite attribute: PlantLocation (StreetAddress, City, State, Zipcode)

Multivalued Attribute: Facilities(Multiple facilities that are given in plant)

Other Attribute(s): PlanName

Department

Composite attribute: WorkHandled(WorkType , WorkName)

Multivalued Attribute: Functions(Multiple functions that are performed in the department)

Other Attribute(s): Description

Workstation

Composite attribute: Location(StreetNo, City, State, Zipcode)

Multivalued Attribute: ContactPhone(Multiple numbers)

Other Attribute(s): StationName

Employee

Composite attribute: Name(FirstName, LastName)

Multivalued Attribute: PhoneNumber

Other Attribute(s): Email

Jobtypes

Composite attribute: Type(Primary, Secondary)

Multivalued Attribute: SkillRequired

Other Attribute(s): Description

Job Task

Composite attribute: TaskToBeDone(Task1, Task2, Task3)

Multivalued Attribute: SkillsRequired

Other Attribute(s): TaskName

WorkUnion

Composite attribute: Location(Street, City, State, ZipCode)

Multivalued Attribute: Specialities

Other Attribute(s): Description

(c) ERD with attributes:

Please find below the primary key for all the entities:

• Plant: PlantID
• Department: DepartmentID
• Workstation: WorkstationID
• Employee: EmployeeID
• Jobtypes: TypeID
• Job Task: TaskID,TypeID
• WorkUnion: UnionID

Updated ERD with attributes added in it:

City State Description WorkType Zipcode Name StreetAddress DepartmentID WorkName PlantLocation (WorkHandled) Facilities Name (1,99) Functionals Specialities Department UnionName divided into Description PlantID Plant (1,1) (1,1) City UnionID (1,40) has (Location ) has (1,5) State WorkUnion performed at (1,503) (1,M) (1,5) (1,M) WorkStation PhoneNumber (1,20) performed at Station Name Job Type (20) associated with WorkstationID TypeID (Location ) State belongs to Street Description Zipcode City Skills Required (Type ) (1,20) (1,M) (1,1) (1,M) works on Job Task Employee Email Primary Type (1,M) SecondaryType TaskID Task ToBe Done Employeeld TypeID Phone Number Skills Required (Name) SubTask TaskName Main Task FirstName LastName

ERD Notations:

Entity Primary Attribute MultiValued Composite Attribut