代做Advanced Database Managment Systems Assignment Spring 2022代做数据库编程

日期：2024-06-08 05:36

Advanced Database Managment Systems

Assignment

Spring 2022

Assignment Guidance

The learning outcomes assessed in this assignment are as follows:

Learning Outcome	Description	Assessment Criteria
LO5	Demonstrate a practical understanding of advanced SQL.	• Shape for ER diagram • Relationships for ER diagram •    Correct use of notation (UML, SQL, Data Dictionary) • Appropriate screen dumps • Presentation of report
LO2	Design and implement a database system for a given business scenario using advanced data modelling techniques.

•   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

You must read and understand NCC Education’s policy on ‘Academic Misconduct’ . You must complete the attached form. ‘Statement and Confirmation of Own Work’ . You must attach the completed form. to your assignment.

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.

Task 6 (16 marks)

Create the following queries. You should provide screen dumps to show each query being run and the output of the query.

a)  Write a query that selects the kitchens models being ordered by Hanson Housing Association.

b)   Write a query that selects the customer details for all customer types of “Private Individual” .

c)   Write a query that selects all the customer details for customers who are having deliveries on the 30th January 2022.

d)   Write a query that shows all components for a Standard Continental Style. kitchen.

e)   Write a query that shows all components that will be delivered to West Estate, Ilford, Essex, IG7 2TT.

f)    Write a query that produces the  components from Sweden that are used in a Standard Continental Style kitchen.

g)  Write a query that shows all components with a component type of ‘Furniture’ that are part of orders by Jane Petty.

h)  Use SQL to produce the information that could be used as the basis for the customer order document shown in document 1.

Task 7 (5 marks)

Create a set of screen dumps showing all of the data in each table – the data must be ordered by the primary key.

Task 8 (15 marks)

Critically assess the issues that will need to be addressed with regard to transaction management for the new database system. You should consider what support for transactions will be needed, concurrency control and recovery.

Task 9 (10 marks)

Produce a reflective overview of how the database and queries you have created have met the requirements of the business. Some of the issues you could discuss include how the data model reflects the structure of data used by the business; the utility of the queries you have created; and how all the parts of the assignment constitute a usable system. You should also reflect on any future improvements that could help the business.

The remaining 5 Marks will be awarded for the presentation of the report.