Databases
Coursework Assignment 2022/23 autumn
This coursework is about the design and implementation of a product monitoring service for a retail outlet.
Study the ‘Mobile for You’ case study carefully and then submit the following:
- Draw an initial Entity-Relationship model, stating any assumptions you make.
- Normalize (to third normal form) the Sales Form. Show all the steps of normalization clearly. State any assumptions. Now draw an Entity-Relationship diagram for these normalized entities.
- Give the Relational Schema (set of tables) that you will create.
- Implement and submit printed documentation for the following:
(a) Create the tables you have identified for the ‘Mobile for You’ database and populate them with appropriate test data of your choice.
(b) Implement all the product information queries and all the sales process queries. You should provide, for each query, a separate Oracle script AND the resulting output. Number the queries Q1, Q2, Q3, Q4, Q5, Q6.
(c) Now produce two additional queries (Q7 and Q8) which you believe will be useful to the company.
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.
You need to upload one document in PDF to Web
Your work must be in a PDF file named CC5051-yourname.pdf. This document must contain the following:
Section 1:
- The E-R model with attributes and relationships. You can draw the model using any tool you wish, including hand-drawn.
- The results of your normalization – show all normal form stages.
- Include any supplementary descriptions/notes/assumptions/discussion to strengthen your proposed design of the system.
Section 2:
- Your CREATE TABLE commands for the tables in your system.
- All the INSERT commands you have used to place data into those tables.
- SQL code for Q1 and output
- SQL code for Q2 and output
- SQL code for Q3 and output
- SQL code for Q4 and output
- SQL code for Q5 and output
- SQL code for Q6 and output
- SQL code for Q7 with output and rationale
- SQL code for Q8 with output and rationale.
- Include any supplementary descriptions/notes/assumptions/discussion to strengthen your implementation of the system.
The Mobile for You Case Study
Mobile for You has been in operation for just six months and looks set to be a successful business. Mobile for You is a retail outlet which offers mobile and accessories at very competitive prices. The shop is sited in a locality which ensures consistently high sales.
Products fall into three categories: Smart phone, Tablet, and Accessories such as Cases, Chargers, Wearables, and Fitness Tracker etc. Smart phone and Tablet can be described in terms of OS, processor speed, memory capacity, screen size, camera, power, colour, and price.
Since the shop is small – it is staffed by five sales staff and has two managers – it is crucial that only those products which can be sold quickly are stocked. It is the job of one of the managers, Dave John, to oversee the ordering and stocking of product items. What Dave requires is the ability to monitor the sales of given products.
The salesmen work on a commission basis which is calculated by adding the number of sales made for the day and multiplying by two.
In view of the fact that the shop has been so busy meeting the high level of sales, as this is what earns money, they have neglected to develop an adequate internal system for monitoring the products. Ideally Dave would like to be able to have a system which could very easily provide him with answers to both product information queries and also queries about the sales process itself.
Some of the product information queries could be as follows:
Q1. what product models have a power capacity of at least 5000 mAh.
Q2. find the model number and price of all products (of any type) in ‘Blue’ color.
Q3. find the model numbers of all smart phone with more than 6GB memory.
Some of the sales process queries could be:
Q4. details of sales that are handled by an individual salesperson.
Q5. list of tablets which have been sold on a particular day.
Q6. details of the purchases a particular customer has made.
Since the shop has not been running long, there are only a few mechanisms in place. The Sales Form shown below is one of them.
