Task 1: Database Design
(This task is worth
Condor Building Services Ltd is a long established construction supplier for construction
companies based in Coventry and the surrounding area. The company specialise in
supplying construction equipment such as piping, timber, wiring, plumbing UPVC frames
doors and masonry. As construction demand is slowly picking up Condor has regular orders
for equipment to meet the needs of its client base. In order to support this, an orders book,
see table below
, is maintained in which equipment orders for its clie
nts are recorded. For
each order the client’s details, date, equipment, quantity, unit price and overall price of the
order is logged.
Estate, CV3 2WL
Sch 40 Blk Pipe
34, Lythalls La
, CV6 6RG
Thin Stranded Copper
Sch 40 Blk Pipe
4x8x3/4 Cos Plywood
Duplex Ivy Rec
, CV3 5YB
Sch 40 Blk Pipe
Currently there is no database being used for managing the orders in the table above. It is
therefore necessary to convert the orders table into a set of database relations by applying
the process of normalisation to determine the correct relations.
This task is split up into
activities that will ask you to normalise the current data
inventory to third normal form to produce appropriate relations (tables) for the database. You
will then be asked to create an ER diagram of the normalised relations
Put data in
First Normal Form
Remove Repeating Elements or Groups of
Elements in Data
Second Normal Form: Remove Partial Dependencies on a
Concatenated Key in Data
: Put data in Third Normal Form: Remove Dependencies on Non
Key Attributes /
Final Database Design
an ER diagram of the normalised relations
derived from tasks 1 to 3.
Show the attributes for each entity, identifier attribute(s) and the correct relationships that
exist between the entities taking care to show the correct relationship cardinalities.
Submit your answers to the activities (1 to 4) in a report section. The report section should
contain diagrams and brief written explanations providing evidence how the normalisation
process for each normal form (1
) was applied to the inventory table. For activity
4 you also need to produce an ER diagram showing your normalised entities.
Achieve Over 14
Evidence of partially correct normalisation
applied to the identified database relations
for achieving each normal form. The
relations produced for each normal form
should show their attributes and key fields
and be populated with some data to
illustrate the normal form the database is in.
activity a brief explanation of
design decisions should be provided.
A Simple ER diagram showing the
normalised entities identified their attributes,
and identifiers. The diagram should show
appropriate relationships between the
entities with partially c
A simple relational model showing the
relations (tables) corresponding to the
identified entities, their attributes and key
fields clearly indicated.
Evidence of normalisation correctly applied to
the identified database relations
each normal form. The relations produced for
each normal form should show their attributes
and key fields and be populated with data to
correctly illustrate the normal form the database
For each normalisation step an explanation and
tification of how and why it was applied.
A detailed ER diagram showing all the entities
correctly identified from the problem
description, their attributes, and identifiers. The
diagram should show the correct relationships
between all the entities with
A detailed relational model showing the
relations (tables) corresponding to the identified
entities, their attributes, key fields and any
foreign key fields clearly indicated.