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.
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.
PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET AN AMAZING DISCOUNT 🙂