An equipment company wishes to create a database to support the hiring of tools and machinery to clients. The company has three types of equipment: power tools, such as drills and vacuum cleaners, plants such as excavators and scaffolding. Each piece of equipment is identified by a number. Power tools are described by their model and the voltage they use, whereas plants are classified by their model and their size tonnes. can be traditional, aluminium fibreglass; in addition, its width can be single or double. A large piece of equipment may be composed of smaller pieces of equipment.
The company has various outlets and each has staff including a manager and several senior technicians who are responsible for supervising the work of allocated groups of technicians. A supervision record is also kept for a specific date. All employees are identified by their number, name, date of birth (DOB) and address. Furthermore, a record is kept on their employment records and their qualifications.
Each outlet has a stock of equipment that may be hired by clients for varying periods of time, from a minimum of four hours to a maximum of six months. Each hire agreement between a client and the company is uniquely identified by using a hire number. Each client is identified by a number and a name. The company insists that each client must take out insurance cover for each equipment hire period. Each insurance cover is identified by a unique insurance number and includes the description of the insurance. The company wishes to keep a record of the member of staff who was in charge of a specific hire agreement. Each piece of equipment is checked for faults when it is returned by the client, and the faults/defects/damage recorded. The company keeps a record of the hire history of each client.
- Create an ER diagram for the above scenario and indicate the cardinality of relationships and the nature of the associations (mandatory or optional). You should allocate adequate attributes to the entities of interest, especially the identifiers.