Data and Information Retrieval (Includes SQL coding);

Task 1: Database Design
(This task is worth
2
0 marks)
Introduction
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
and
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.
Order
ID.
Supplie
r
ID.
Client Name
Client
Address
Date
Equipment
Qty
Unit
Price
Total
Price
CON
–
2237
16
8
Coventry
Building
Services Ltd
Units 2
–
4, Binley
Industrial
Estate, CV3 2WL
14/12/2014
Butterfly valve
2
£5.00
£99.00
¾ Locknut
6
£1.50
Sch 40 Blk Pipe
4
£20.00
CON
–
3664
527
Allied
Construction
Ltd
34, Lythalls La
Industrial
Estate
, CV6 6RG
16/01/2015
Thin Stranded Copper
Wire
6
£6.00
£36.00
CON
–
2356
169
Ricoh Builds
Ltd
Unit 12,
Stoneleigh Park,
CV8 2UV
12/02/2015
Sch 40 Blk Pipe
3
£20.00
£280.00
4x8x3/4 Cos Plywood
2
£10.00
¾ EMT
2
£50.00
Duplex Ivy Rec
1
£100.00
CON
–
1234
032
Grand Designs
Ltd
32
–
34, Bilton
Industrial
Estate
, CV3 5YB
16/04/2015
Sch 40 Blk Pipe
1
£20.00
£23.00
¾ Locknut
2
£1.50
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
four
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
.
Activity 1:
Put data in
First Normal Form
:
Remove Repeating Elements or Groups of
Elements in Data
Activity 2:
Put data
Second Normal Form: Remove Partial Dependencies on a
Concatenated Key in Data
Activity 3
: Put data in Third Normal Form: Remove Dependencies on Non
–
Key Attributes /
Final Database Design
Activity 4
: Create
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.
Evi
dence
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
st
, 2
nd
and 3
rd
) was applied to the inventory table. For activity
4 you also need to produce an ER diagram showing your normalised entities.
Marking Criteria
€“
Task 1
Achieve 8
Marks
(40%)
Achieve Over 14
Marks
(70%)
€¢
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.
€¢
For each
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
orrect cardinalities.
€¢
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
for achieve
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
is in.
€¢
For each normalisation step an explanation and
jus
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
the correct
cardinalities.
€¢
A detailed relational model showing the
relations (tables) corresponding to the identified
entities, their attributes, key fields and any
foreign key fields clearly indicated.

Data and Information Retrieval (Includes SQL coding);

Task 1: Database Design
(This task is worth
2
0 marks)
Introduction
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
and
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.
Order
ID.
Supplie
r
ID.
Client Name
Client
Address
Date
Equipment
Qty
Unit
Price
Total
Price
CON
–
2237
16
8
Coventry
Building
Services Ltd
Units 2
–
4, Binley
Industrial
Estate, CV3 2WL
14/12/2014
Butterfly valve
2
£5.00
£99.00
¾” Locknut
6
£1.50
Sch 40 Blk Pipe
4
£20.00
CON
–
3664
527
Allied
Construction
Ltd
34, Lythalls La
Industrial
Estate
, CV6 6RG
16/01/2015
Thin Stranded Copper
Wire
6
£6.00
£36.00
CON
–
2356
169
Ricoh Builds
Ltd
Unit 12,
Stoneleigh Park,
CV8 2UV
12/02/2015
Sch 40 Blk Pipe
3
£20.00
£280.00
4x8x3/4 Cos Plywood
2
£10.00
¾” EMT
2
£50.00
Duplex Ivy Rec
1
£100.00
CON
–
1234
032
Grand Designs
Ltd
32
–
34, Bilton
Industrial
Estate
, CV3 5YB
16/04/2015
Sch 40 Blk Pipe
1
£20.00
£23.00
¾” Locknut
2
£1.50
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
four
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
.
Activity 1:
Put data in
First Normal Form
:
Remove Repeating Elements or Groups of
Elements in Data
Activity 2:
Put data
Second Normal Form: Remove Partial Dependencies on a
Concatenated Key in Data
Activity 3
: Put data in Third Normal Form: Remove Dependencies on Non
–
Key Attributes /
Final Database Design
Activity 4
: Create
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.
Evi
dence
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
st
, 2
nd
and 3
rd
) was applied to the inventory table. For activity
4 you also need to produce an ER diagram showing your normalised entities.
Marking Criteria
–
Task 1
Achieve 8
Marks
(40%)
Achieve Over 14
Marks
(70%)
•
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.
•
For each
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
orrect cardinalities.
•
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
for achieve
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
is in.
•
For each normalisation step an explanation and
jus
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
the correct
cardinalities.
•
A detailed relational model showing the
relations (tables) corresponding to the identified
entities, their attributes, key fields and any
foreign key fields clearly indicated.

Data and Information Retrieval (Includes SQL coding);

Task 1: Database Design
(This task is worth
2
0 marks)
Introduction
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
and
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.
Order
ID.
Supplie
r
ID.
Client Name
Client
Address
Date
Equipment
Qty
Unit
Price
Total
Price
CON
–
2237
16
8
Coventry
Building
Services Ltd
Units 2
–
4, Binley
Industrial
Estate, CV3 2WL
14/12/2014
Butterfly valve
2
£5.00
£99.00
¾” Locknut
6
£1.50
Sch 40 Blk Pipe
4
£20.00
CON
–
3664
527
Allied
Construction
Ltd
34, Lythalls La
Industrial
Estate
, CV6 6RG
16/01/2015
Thin Stranded Copper
Wire
6
£6.00
£36.00
CON
–
2356
169
Ricoh Builds
Ltd
Unit 12,
Stoneleigh Park,
CV8 2UV
12/02/2015
Sch 40 Blk Pipe
3
£20.00
£280.00
4x8x3/4 Cos Plywood
2
£10.00
¾” EMT
2
£50.00
Duplex Ivy Rec
1
£100.00
CON
–
1234
032
Grand Designs
Ltd
32
–
34, Bilton
Industrial
Estate
, CV3 5YB
16/04/2015
Sch 40 Blk Pipe
1
£20.00
£23.00
¾” Locknut
2
£1.50
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
four
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
.
Activity 1:
Put data in
First Normal Form
:
Remove Repeating Elements or Groups of
Elements in Data
Activity 2:
Put data
Second Normal Form: Remove Partial Dependencies on a
Concatenated Key in Data
Activity 3
: Put data in Third Normal Form: Remove Dependencies on Non
–
Key Attributes /
Final Database Design
Activity 4
: Create
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.
Evi
dence
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
st
, 2
nd
and 3
rd
) was applied to the inventory table. For activity
4 you also need to produce an ER diagram showing your normalised entities.
Marking Criteria
–
Task 1
Achieve 8
Marks
(40%)
Achieve Over 14
Marks
(70%)
•
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.
•
For each
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
orrect cardinalities.
•
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
for achieve
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
is in.
•
For each normalisation step an explanation and
jus
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
the correct
cardinalities.
•
A detailed relational model showing the
relations (tables) corresponding to the identified
entities, their attributes, key fields and any
foreign key fields clearly indicated.