CC5051 Databases
Coursework Assignment 2023/24
The coursework assignment is an individual assessment weighted 100% of the marks for the module. It is designed mainly to assess the practical aspects of the module. It requires the student to analyse and modelling a database based on a given case study. You are asked to provide a appropriate database design documentation detailing the modelling process.
Case study – Goldsmith Antique Associates
Goldsmith Antique Associates is an established antique dealer’s business located in east London. They have a shop on the high street but most of their business comes from locating items for customers and dispatching these by mail anywhere in the world. Their reputation depends on their knowledge of their customers’ special interests, and being able to locate items that will satisfy their requirements.
Your job is to build web-based database system that will help in keeping track of customers’ orders, their special interests and the items that might satisfy those special interests. Now, the firm has a set of manual files for customer records, item records and sales.
You have been given various items from the files to examine.
1. Customer Record Sheets
The customer record sheet looks as though it started off quite orderly as a set of type written documents but has then had handwritten additions added. King Smith the office manager tells you:
‘The trouble is that we get extra information with nowhere to put it, so I tend to scribble things on the sheets. The notes section is usually about the customers’ special interests but sometimes I have to add another address there. A lot of our clients are very rich and have many addresses. They usually have an address where they would prefer their goods sent but this may be different from where they live or where they want the bill sent. It would be good if we could have something on the new system that we would sort this out. Maybe something that showed different address types or where they wanted things sent.’
Customer Name: Severain Riccardi
Address: 7 Halsall Road, Barnes, London . find out this custmomer’s postcode Mark S.
Tel: 020 980 88888 Notes:
Severain is interested in French painting from the Impressionists onwards.
He has another address 89 Rue de Balzac, Paris, France. This is where he usually is. Don’t know the telephone for this. Important, all items should be sent to the London address.
Customer Name: Amanda O’Brian
Address: 89 Andover Park, West Drayton Tel: 090 998 7654 Notes:
Amanda is interested in items from Japan and China.
Customer Name: Paul Goodman
Address: c/o PO Box 89 London E1 Tel 9383 8938383 Notes:
Above is billing address. Don’t send goods there.
Mr. Goodman is interested in all types of militaria.
2. Item Records
Item records are a set of card index files. As shown below. King Smith had the following to say:
‘The biggest headache we have is with keeping track of items. The major headache with items is that we file them by category. So, we have categories like ‘Militaria’, ‘Art’ and ‘Equestrian’ amongst many more. The trouble is that some items fall into more than one category. For example, the Duke of Wellington’s sword would be ‘Militaria’. A painting of the Duke of
Wellington would be ‘Militaria’ and ‘Art’ whereas a painting of the Duke on his horse would be ‘Militaria’, ‘Art’ and ‘Equestrian’. What we do now is we copy out the cards and put them into each category. This involves a lot of duplication. If you could design a system that got round us having to do this then you would earn my sincere gratitude’.
‘‘Provenance’ refers to the files of documentary evidence proving the history of the item. These are kept in a safe. The new system will just need to hold a file number referring to these’.
item Reference: K098
Item Description. Samuri Sword c16th
Owner. Mr Yukio Kirosawa
Provenance. See file 991
Category. Militaria
Valuation: £3000
Notes: This item also classified under Japanese items
Item Reference K77
Item Description. Duelling Pistols Webb and Sons 1768
Owner. F W King
Provenance. see file 988
Category. Militaria
Valuation: £7000
Notes: Item also classified under Eighteenth Century English
Item Reference K78
Item Description. 1766 English Wall Clock
Owner.F W King
Provenance. see file 999
Category. Horology
Valuation £900
Notes: Item also classified under Eighteenth Century English.
3. Sales Records
To keep track of sales (Sales Order and Sales Order Lines), records are kept. King Smith comments:
‘When we have information about items that we think might be of interest to a client, we inform them, and they will order from us either by sending in one of the order forms that we had sent out to them earlier or over the phone, in which case we fill in the order. The order is usually for several items as you can see from our order form below.’
Goldsmith Antique Associates
Sales Order Sheet
Date: 21/10/2023
Customer Name: Harvey Bendix
Customer Address: 1 Narrow Gauge Cottages, Ely, Cambs, England
Despatch Address (if different) 2 Woods Buildings, London E1
Item Reference Description Price Notes
D21 Victorian Commode 190
A99 Hatstand 20
R111 Various ceramics 300
K98 Sword, Japanese 2000
Total for Order: 2510.00 Dispatched Date: 10/25/10/2023
Signed: King Smith
Detailed Specifications
Study the case study carefully and then submit proper documentation for the modelling of the system that meets the requirements specified below:
1. Normalise data to 3rd NF with facts found from the Goldsmith Antique Associates case study. Provide the individual steps you took in the normalisation process. [20 marks]
2. Provide ERD model for Goldsmith Antique Associates which must be consistent with 3NF results. [10 marks]
3. Provide a Data Dictionary for the whole system. The data dictionary should include primary key and foreign keys, names, data types and descriptions of each attribute for each entity in the
entity relationship model. [10 marks]
4. Oracle database implementation and submit printed documentation for the following:
a) Create all the database tables in Oracle based on the design. Show table-creating scripts for each table with clear PK and FK definition. [10 marks]
b) Populate the tables with appropriate data using INSERT command and list its content using SELECT command. Data included in each table should meet the requirements specified below:
- minimum 5 rows of information for each table. [5 marks]
- yourself as a customer. [2 marks]
- enough data to return at least 2 rows in all queries. [3 marks]
5. Complete following SQL queries and show query scripts and screen-shot of outputs
[25 marks]
a) List customers whose name started with “S” or “D”
b) Find items owned by F.W. King
c) List customers interested in Militaria items
d) Find sales detail made by King Smith. The sales detail should include customer name, order ID, order date, items as well as price.
e) Report daily sales detail on 21/10/2023. The sales detail should include customer name, order ID, order date, order total, salesperson’s name.
6. produce two additional more complicated queries (Q7 and Q8) which you believe will be useful to the company. [10 marks]
• Consider extending your schema with either extra data columns or tables for this purpose.
• Give a clear textual description of what each of your two queries is supposed to show. Use your judgement as to what information the queries should contain and how they should be formatted.
7. List the five critical differences between SQL and NoSQL and prepare a comparison chart to show what sets the two apart. [5 marks]
Coursework Submission
Coursework will be due on Friday 15st December 2023 and should be submitted via weblearn.