The is based on the Erehwon Fabrications case study presented in the attached

document below. Use this information to answer the four questions following.

Read the Erehwon Fabrications case study $($attached below$).$ Using the information provided, start to identify the entity types and associated attributes. Use your analysis of the case study to create six $(6)$ user views:

Two $(2)$ specific to the job function of the Personnel Officer

Two $(2)$ specific to the job function of the Line Supervisor.

Two $(2)$ specific to the job function of the Managing Director.

The six views created must be different and appropriate to the job function of the individual that can access the view. This may be done textually or in a format. This work should be done in a Microsoft Word document. Record any assumptions that you make in specifying the views.

Create a conceptual data model that supports these user views and the rest of the case study. This may be done in a drawing tool such as Microsoft Visio rather than Oracle Data Modeler

Create a logical data model for a relational schema using Oracle Data Modeler or other drawing tool such as Microsoft Visio. This may also be done in a Microsoft Word document showing the relations

Create a physical $($relational$)$ database design using Oracle Data Modeler or other drawing tool such as Microsoft Visio. A DDL script is not acceptable.

Assignment $5$ Case Study

Erehwon Fabrications is a custom manufacturing facility specializing in many

different product lines.

Plant

The manufacturing plant has a number of different assembly lines, each of which

consists of a set of workstations. At a workstation, parts are either manufactured or

included in an item being assembled. Each assembly line is known by an ID number

and a name, e$.$g$.,3=$ Wood Furniture. There are a different number of workstations

per assembly line, depending on what is being manufactured. Workstations are

identified by a number within an assembly line, e$.$g$.,3,1$ would be the first

workstation in assembly line $3.$

Staff

Erehwon Fabrications has a Managing Director who has overall responsibility for

the operation of the facility. The Managing Director maintains control over the use

of resources $($including staff, equipment, and materials$)$ for the cost$$effective

delivery of finished products to the sales warehouse.

A Personnel Officer is responsible for ensuring that the appropriate number and

type of staff are allocated to each assembly line. The information stored for each

staff member includes a staff number, name $($first and last$),$ full address, telephone

number, date of birth, gender, SIN, position, and current salary. It also includes

each member$$s qualifications $/$ certifications $($which includes date achieved, type,

and name of institution $/$ organization granting it$).$ The qualifications $/$ certifications

determine which assembly lines and$/$or workstations the staff member can be

assigned to$.$

Each assembly line has a Line Supervisor, who is responsible for overseeing the day$$

to$$day operation. The Line Supervisor must ensure that all resources $($equipment

and materials$)$ are used effectively in the manufacturing process. The Managing

Director works closely with the Line Supervisors to ensure efficient running of the

facility.

The Line Supervisor is also responsible for setting up a weekly staff rotation and

must ensure that the assembly line has the correct number and type of staff on duty.

Products

Each product being manufactured has a list of required materials and equipment

being used. A stock of materials and equipment is maintained in an adjacent

storehouse. These are drawn through requisitions from each assembly line,

authorized by the Line Supervisor.

Finished products are immediately shipped out to the sales warehouse $($a separate

facility$).$

Materials

The storehouse maintains a stock of the materials used in the manufacturing

processes. Details include a stock number, description, weight, quantity in stock,

reorder level, and cost per unit.

Equipment

Equipment required at a workstation can change depending on the product being

manufactured. They are also subject to wear and tear, so occasionally need to be

replaced. Details include the same items as materials, but also the expected life and

servicing requirements.

Suppliers

The materials and equipment in the storehouse are provided by various suppliers.

Details for a supplier include a name and number, address, email, and telephone

and fax numbers.