7086CEM Data Management Systems

7086CEM Assignment Help

Data Management Systems Assignment help

Part A. Conceptual modelling
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 in tonnes. Scaffolding can be traditional, aluminium or 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.

1. 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.
(20%)

2. Generate, with justification, relational tables from the ER diagram. Indicate
clearly the names of the tables, the attributes, the primary keys and the
foreign keys.
(5%)

Guidance: i) Create the ER diagram and clearly identify any identifiers, and indicate
the cardinality of relationships and the nature of the associations (mandatory or
optional). ii) Generate tables and include primary and foreign keys. Use the schema
notation; you do not have to produce SQL statements.

Example of table generation in schema form:
Course(courseId, courseName)
Student (studentId, name, courseId*)