Advanced Database Managment Systems Assignment Spring 2022

Assignment Guidance

The learning outcomes assessed in this assignment are as follows:

•   It is the student’s responsibility to familiarise themselves with the NCC Education Academic Misconduct policy.

•   Any assignment submitted after the submission deadline, without prior approval, will be given 0% and the student will be referred.

•   The late assignment submission may be marked as a referral attempt, but only a maximum mark of 40% can be awarded for that particular assessment.

•   If a student requests an extension to the official submission date this must be done in

writing to the Programme Manager at uclan@nccedu.comat least five full UK working days before the official submission date.

This request must be accompanied by supporting evidence.

•   This assignment is worth 40% of the overall final mark for the module.

•   All references should be cited in text and included in a reference section at the end of the report using the Harvard Referencing Scheme.

•   The Statement and Confirmation of Own Work must be completed and submitted with the assignment.

•   This assignment must be submitted by the due date and time as given on the front of this assignment.

Submission Requirements

Case Study: Tiptop Kitchens

Background

You have been asked to design and construct a database system for Tiptop Kitchens, a kitchen supplier based in London, United Kingdom. They supply kitchens fittings, furniture and appliances for both individual customers and organisations that own or manage large numbers of properties (such as local councils or housing associations).

Customers are therefore classified as: “Private Individual”, “Housing Association”, “Council”, “Private Company” or “Other” .

How the company organises its work:

Tiptop Kitchens supply a range of kitchens. Customers make orders for one or more of a particular kitchen in their range. Each kitchen model (aka ‘kitchen’) consists of a number of components. Components are classified by type and are supplied by companies located in different countries.

When a customer order is complete then a delivery date is organised. Note that a delivery might be for more than one order. Note also that some orders will need to be delivered to a different address to that of the customer’s main address (for example a Housing Associate would have their order delivered to the houses where they are having the kitchens fitted rather than their main office address.)

The system should be capable of storing all the information needed for Tiptop Kitchens to carry out their business.

Please Note Further details of the case study are shown in the documents below which give examples of data that can be taken as representative of a much larger data set.

Please note that the data as represented here is not necessarily in a normalised state and it is the job of you, the database developer, to organise the data in its most optimal state.

Document 1 (Customer Order Document Example 1)

This document is used to record the details of an order by a customer.

Document 2 (Customer Order Document Example 2)

Document 3 List of components of different kitchens models

This document is used to record which components are used for a particular kitchen model. Note that some kitchen models might use more than one component.

Document 4

This document is used to record information about components including their type code,

their manufacturer and the country of origin of their manufacturer.

Document 5

This is the description of the type codes for components.

Document 6

This document records relevant information about the countries where manufacturers are based. The Port attribute records where the manufacturer ships their components from. The Agent attribute records the name of the person working on behalf of Tiptop Kitchens in that country arranging paperwork such as export licenses.

Document 7 Delivery Information

This document records the details of a delivery.

(Please note: there is only one delivery per day)

Assignment

You are required to produce a report that addresses all of the following tasks:

Task 1 (20 marks)

Create an ER diagram (using UML notation) of the Tiptop Kitchens system. Please state any assumptions that you make.

Task 2 (10 marks)

Critically assess the normalisation you have undertaken to produce a set of relations for the scenario. You should discuss the process of normalisation in general and the specific way it has been applied in your work.

Task 3 (5 marks)

Create an entity listing for the Tiptop Kitchens system. You should show all the entities you have identified, their attributes and identify primary and foreign keys.

Task 4 (10 marks)

Create the relevant SQL CREATE TABLE statements for implementing the Tiptop Kitchens system in a DBMS of your choice (not MS Access). You should provide screen dumps of both the scripts and the  implemented tables  to show that the create statements  have worked.

Task 5 (4 marks)

Populate the database with the data shown in the case study.

Discuss the population of the database tables for the Tiptop Kitchens system (order of population, issues and resolutions and SQL used) supported by appropriate screen dumps.