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.
Supplier
ID.
Client Name   Client
Address
Date  Equipment  Qty  Unit
Price
Total
Price
CON – 2237  168  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.

Evidence

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.

Task 2  –   Database Development (This task i s  worth 20 marks)
A Company that specialises in the manufacture of commercial passenger and freighter
aircraft wishes to develop an Oracle based Database System. To this end, an E – R diagram
given below has been produced. The current data owned  by the company is in a
spreadsheet.

Using the E- R diagram above and the data in the table below, you are required to implement
ORACLE SQL statements to perform the following tasks.

1. Implement the appropriate Oracle SQL statements to create the database tables shown in
the E – R diagram above. You must define and implement appropriate data types. You should
also implement constraints where appropriate.

2. Implement the appropriate O racle SQL statements to store the data in the given
spreadsheet into the appropriate tables that you created in 1 above.

Implement the Oracle SQL statements to retrieve the information in  queries stated in tasks 3
to 6   below. Each list or output should contain headings that are meaningful and user friendly.

3. Display the average, minimum and maximum price  for  all the aircraft bought after 01 – jan -2012.

4. Display the purchase order number, date, airline name, address, airline country for airlines
where  the  total cost of an order is   less than  10,000 million pounds.  Your results should be  in
descending alphabetical order  based on the airline code.

5.   Display how many aircraft  were ordered by British Airways in total and by each different
airplane type .

6. Produce a list of all the orders from all the  airlines. The list should show the  airline   code,
followed by the order number, followed by the code of the aircraft ordered, followed by the
quantity of aircraft ordered and then the total cost of that or der. The list should be arranged
by airline code in descending order.

7. Display the order details, airline details and the aircraft details where more than 10 aircraft
of a specific type were ordered.

Update the tables based on the   information   in T asks 8 and 9.

8. We have discovered an error in our  spreadsheet. The  C800 aircraft should have been 100
million not 80 million please update the database based on this change.

9. Air Coventry LTD has changed its name to Coventry University Airways please update the
information.

Evidence

No marks will be awarded for statements that have only been word- processed. You are
required to run your SQL statements and to include each output immediately after its SQL
statement.     You should have a brief description of the SQL statements and the outputs.

purchase_order_no  date   airline_code  airline_name  airline_address  airline_city   airline_country  aircraft_code  aircraft_type

aircraft_quantity  aircraft_price £  total_order price  £
689  28/04/2012   BA07  British
Airways PLC
Waterside, PO Box 365,
Harmondsworth, UB7
0GB
London   United
Kingdom
C800  CU –  800
Commuter
14  £80,000,000.00  £1,120,000,000
C8000   CU –  8000
Stratocruiser
8  £229,000,000.00   £1,832,000,000
C9000   CU –  9000
Mesocruiser
5   £300,000,000.00   £1,500,000,000
C24  CU –  24
Slipstream
14  £210,000,000.00   £2,940,000,000
C8  CU –  8X
Heavy Lifter
7   £100,000,000.00   £700,000,000
789  24/06/2011   UA09  United
Airlines
77 W. Wacker Drive,
Chicago, IL 60601,
United States
Chicago  United States  C10  CU –  10
Exolinear
4  £400,000,000.00   £1,600,000,000
C900  CU –  900
Commuter
30  £190,000,000.00   £5,700,000,000
C8000   CU –  8000
Stratocruiser
8  £229,000,000.00   £1,832,000,000
800  30/07/2013   AI06   Air India  Air- India Building,
Nariman Point,
Mumbai, 400 021
Mumbai  India  C80   CU –  80
Cloud
Hopper
9   £86,000,000.00  £774,000,000
C6  CU –  6X
Rapid Lifter
11  £280,000,000.00   £3,080,000,000
C22   CU –  22
Executive Jet
32  £110,000,000.00   £3,520,000,000
898  06/06/2012   AC05  Air Coventry
LTD
Coventry University,
Priory Street, Coventry,
CV1 5FB
Coventry   United
Kingdom
C5  CU –  X5
Spaceplane
6  £600,000,000.00   £3,600,000,000
C24  CU –  24
Slipstream
12  £210,000,000.00   £2,520,000,000
C800  CU –  800
Commuter
25  £80,000,000.00  £2,000,000,000
C10  CU –  10
Exolinear
6  £400,000,000.00   £2,400,000,000

900  03/11/2012   RM04  Royal Mail
Group
100 Victoria
Embankment, London,
EC4Y 0HQ
London   United
Kingdom
C8  CU –  8X
Heavy Lifter
11  £100,000,000.00   £1,100,000,000
C6  CU –  6X
Rapid Lifter
25  £280,000,000.00   £7,000,000,000
C80   CU –  80
Cloud
Hopper
8   £86,000,000.00  £688,000,000
900  03/11/2014   IR01   Iran Air   1  Valiasr Street,
Tehran.
Tehran   Iran   C80  CU –  80
Cloud
Hopper
10  £86,000,00.00   £860,000,000
Tas k  3  –  Poster of ethics associated with a medical database (This task is worth 30  Marks)

A hospital is considering producing a database  from  patient data it has collected over
the past 20 years to analysis itself and to sell  to other interested parties.  You sho uld
create an A3   sized poster to describe ethics issues that the hospital should consider
before creating, analysis and making available this database.  There are various
documents on the web and moodle on how the create a poster using Powerpoint.
Please e xplore these before you start.

Evidence

You should create you’re A3 sized poster in Powerpoint and save it as a pdf document for
submission with your report.  Your poster should identify ethical factors that need to   be
considered when developing and  analysis  such a medical database, offer recommendations
to the hospital  and drawn conclusions.

Marking Criteria  –  Task 3
Achieve 12   Marks  (40%)    Achieve Over 21 Marks  (70%)
•  The poster contains an introduction and a
restricted set of objectives.

•  The  poster contains consideration of
some of required ethics factors that need
to be considered.

•  An attempt made to produce
recommendations related this medical
database.

•  An attempt is made to draw conclusions.

•  Some research from academic resources
to sup port the findings on the poster.

•  Effort is made to reference the poster.

•  The poster is fairly clear and stays within
A3 size requirement.

•  The poster has a well set out introduction
and a clearly thought out set of objectives.

•  The poster contains consideration of most of
the required ethical factors.

•  Clear and well thought out recommendations
are related to creating the medical database.

•  Some research from academic resources to
support the findings of the poster.

•  The poster is referenced clearly.

•  The poster is clear and easy to follow and
stays within the A3 size requirement.

Task 4  –  Recommendation System (This task is worth 30  Marks)

You are asked to build a ‘recommendation’ system for Amazon. You should tailor each
recommendation to the  cust omer, their previous purchases, searchers and other relevant
information that you can use to identify the interest of this customer. You have two main
jobs:

On the client side:
1. Explain to the client the type of data generated, collected and what  it will be used for.
2. Explain the expected benefits and limitation of such a system.
3. Explain the process which you will use to analyse the data at your disposal.