Develop a Presentation Layer Entity-Relationship (ER/EER) model for building a database for the Indian Hill Company described in the following narrative. Indian Hill Company is a factory manufacturing miscellaneous spare parts for the farm equipment industry. The ER diagram should be fully specified, with unique identifier(s) and other attributes for each entity type, and relationship(s) among the entity types. The narrative is complete. However, if you discover any ambiguities in the narrative, make up reasonable assumptions to com- plete the story and state the assumptions made. Note that no assumption you make can contradict the specifications contained in the narrative. Also, business rule(s) not incorporated in the ERD, if any, must be explicitly stated as semantic integrity constraint(s). A business rule captured in the ERD should not be restated as a Semantic Integrity Constraint and vice versa. You should also list any ambiguities/conflicts in the stated specifications.
Caution: Do not read any extra meanings into the story and make it more complicated than the simple one given below.
The factory has several departments. A department may have many employees but must have at least seven. 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. For an employee to be the manager of a department, that employee must belong to that particular department. If a department is closed down, all employees of that department are laid off.
A department may have many machines, and every machine is assigned to a specific department. A machine may go for maintenance numerous times. Maintenance is per- formed on a machine only once on a given day. Some machines are so new that they may not have gone for maintenance yet. Maintenance tasks are outsourced to contractors. Every contractor performs at least one maintenance task, often more. If a contractor quits,
the association of that contractor with its maintenance tasks are temporarily suspended; after all, the maintenance task itself cannot go away! A maintenance task is often done by one contractor but may sometimes involve up to three contractors. When a machine is retired from service, all the associated maintenance records are erased.
Products are produced on machines. A product can be an assembly of several different components (products) or a single piece. Also, a product cannot be a component of more than one product. A product cannot be a component of itself. Every product (component) goes through one or more machines for appropriate production operations. Likewise, sev- eral products may go through a particular machine for a production operation. If a product is deleted (due to obsolescence), all operations on that product can be discarded. Operations have precise specifications; so, every operation of a product on a machine is specified by a designer.
Designers design the products and/or specify production operations. Some designers may design more than one product; others may specify more than one operation, and some may do both. Of course, all designers are employees of the factory. Operators, who are also employees of the factory, operate the machines. Due to multiple shifts, several operators will 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 except when it is out for maintenance. The same employee cannot be a designer as well as an operator. The fac- tory also has employees other than designers and operators.
The ER model should capture employee’s name, which will include first name, last name, and middle initial [o]. It should also capture gender, address, and salary [o]. An employee number uniquely identifies an employee. Likewise, department number [o], department name [o], type, and location [o] must be captured. The department number and department name are both unique identifiers of a department. (Note that it is enough if one of these two is present for any particular department.) Every machine will have a unique machine num- ber. It will also have other attributes, like name of machine, type [o], and vendor’s name [o].
When a machine goes for maintenance, the maintenance date for that machine must be captured since a maintenance activity is identified by the date of maintenance for each machine. The attributes of maintenance activity are time taken and cost. A product is iden- tified by its component ID. Component name, description [o] must also be recorded. It should be possible to compute the number of components in a product. When a component goes through machining operation, the starting time [o] and completion time [o] for each product on every machine must be captured, from which the hours of machining operation for a particular product in a specific machine can be computed. The information about designer includes his/her qualifications [o], specialization field, and experience [o] in years. Operators, who are responsible for operating the machines, belong to a labor union [o] and have certain skill sets (at least one skill) associated with them. Contractors are identified by their names. Additional attributes captured for a contractor are experience and expertise.
Note: [o] indicates optional attribute; where specification of deletion rule is missing, the default value of Restrict should be considered first; if found problematic, an alternative may be suggested and used.
Hint: No more than nine entity types are needed to complete this design. It is possible to model all but two business rules in the ER diagram, if the ER modeling grammar is fully employed; otherwise, a few more business rules may have to be stated in the semantic integrity constraints.
