MySQL辅导：IAB130 Databases辅导R编程、R调试、调试R

MySQL，，，，，。

Project Overview

The IAB130 project gives you an opportunity to apply all the concepts and skills you acquire in the unit to a “realistic” database design scenario and reflect on the data requirements of an organisation.

The submission is divided into two parts due at different times during the semester. These parts will cover:

A. Design of a Database
B. Creation and Use of a Database

Both parts are based on the Iconic Clothes Online Store.

The Task for Part B

You have been provided with the correct solution to Part A, where it was your role to design a new database based on the requirements of the Iconic Clothes Online Store. In Part B it is your task to implement the correct solution.

You were given a copy of the original Iconic Clothes Online Store database, in a file titled iconic_dump.sql to complete Part A.

In this part you will build a new version of the database by:

Reflecting on the differences between the model Part A solution and your own;
Creating a script that will upgrade the existing version of the database to the new version without losing any data; and,
Specifying indexes, views and security features that would optimize the new database for use.

The Scenario – The Iconic Clothes Online Store

Iconic Clothes currently operates a website through which they sell a number of items that are shipped to addresses throughout Australia. They want to expand their operations by selling their clothes to other retailers in Australia. You have now agreed upon a new database design with Iconic Clothes and they wish for you to implement this solution. To implement the solution, they require you to create an SQL script to update their database to the new model, without losing any data. The script needs to be compatible with MySQL Server 5.7.

They are engaging software developers to make the necessary adjustments to their website so that it works with the new version of the database and can make use of the expanded functionality. This is outside of the scope for your project.

Finally, in addition to the script they require, they would also like you to provide the necessary commands to optimize elements of their database. In particular, they would like you to create a number of views to generate summary data to be used in internal reports. The views they would like created, and other settings they would like configured are outlined in the final tasks in this project.

Provided below and on the next page are:

A copy of the entity relationship model for the new database design; and,
A normalised relational model that was constructed based on the agreed entity relationship model.

The New Iconic Clothes Database Relational Model

Category (categoryID, categoryName)
Product (productID, productName, productDescription, currentUnitPrice, inStock, season, categoryID)
Customer (customerID, firstName, lastName, streetNo, street, suburb, postcode, state, emailAddress)
CustomerPhoneNo (customerID, phoneNumber)
Orders (orderID, customerID, orderDateTime, paymentStatus, streetNo, street, suburb, postcode, state)
OrderContents (orderID, productID, productUnitPrice, productQuantity)
Salesperson (salespersonID, firstName, lastName, phoneNumber, emailAddress, supervisorID)
Partner (partnerID, companyName, streetNo, street, suburb, postcode, state, firstName, lastName, phoneNumber, emailAddress, managerID)
Purchase (purchaseID, partnerID, salespersonID, productID, purchaseDateTime, paymentStatus, streetNo, street, suburb, postcode, state, productUnitPrice, productQuantity, discount)

Foreign Keys

Product (categoryID) is dependent on Category (categoryID)
CustomerPhoneNumber (customerID) is dependent on Customer (customerID)
Order (customerID) is dependent on Customer (customerID)
OrderContents (productID) is dependent on Product (productID)
OrderContents (orderID) is dependent on Order (orderID)
Salesperson (supervisorID) is dependent on Salesperson (salespersonID)
Partner (managerID) is dependent on Salesperson (salespersonID)
Purchase (partnerID) is dependent on Partner (partnerID)
Purchase (salespersonID) is dependent on Salesperson (salespersonID)
Purchase (productID) is dependent on Product (productID)

Other Constraints

It is assumed that all primary keys must be unique
The quantity of a product in an order or purchase must be at least 1.
The attribute paymentStatus, which exists in two tables, can only have the value of paid and unpaid
The attribute inStock, which exists in the product table, can only have the value of yes or no

Part B Tasks

Part B requires you to complete a number of sequential tasks to fulfill the requir⁮†⁥⁉⡮⁨⁯⥬⁬⁩†⹳⁩⁯⁥⁡†‍⁯ ⁬⁥⁩††⁮†⁥⁡⡧⁥†⥮⸠㱡⽮㹴൨੥†⁯⁮⁥㰠㹵䐠⹤⁥䵳⁥‍⁩䌾⁡⹩⁰䱴⁡⁩⁤⁥⁩䥳䑴⁩†⁡⹥†⁶⁳⁷⁯⁹⁴⁩⁬⁐†⁭†⁤ⱥ⁡⁴⁲⁥⁷‼‾⁩⁩⡮††⁥†⁥⁥⁲⁲⤠⁩⁥⁮⸊㰠⼠㸼൬੩㰾⽐㹶൩੤㱩㍧†㵶≩⵮㐠≴㹨⁳㑩㱣⼠㍥㹣൵ੲ㱩㹹䌠†⁴⁢⁰‮ ‼‼†Ⱬ‭†⁨‼†⁪†⁦†⁴†⁬⁩⁨⹧⁲䍡⁲⁯⁬⁲⁮⁳⁵⁩†⁡⁮⁲⁁⸾㱆⽯㸠൥੡㱣㸠⁮⁹⁤⁨†⁦⁲⁩†⁷⁥㩥㰮⽧㸠൰ੲ㱯㹤൥ਠ⁡†⁳⁣㱲㹮䅳⹨⁯䕴⁴䅨⁶⁴⁲䍴⁅㱡⽮㹥൸ੰ⁬⁡⁩⁮㰠㹩䉣⹨†⁴⁯⁬⁨⁳㰠⽳㹴൥ਠ㱴⽨㸠൴੨㱥㍤⁩㵥≲ⴠ㕨≡㹳⁴㕬㱥⼠㍭㹰ൡ੣㱴㹯䥮⁦⁡†⁴ⱳ⁥䨩††䅤ⱳ†⁩⁩⁥⁥⁳†⁥䵳䰠⁴⸠⁡⁥⁅⁥⁯⁤⁸⁡⁮⁯⁳†‍⁨⁤⁥㩣㱥⽳㹢൥ੴ㱷㹮ഠੴ⁨⁥†⁳㱯㹴䅩⹯⁮⁵䨠⁩⁩⁐⁁⁮⁨⁳⁲⁤䐠䕴⁹㰠⽐㹴ഠੂ‬†⁴⁨㱥㹹䉯⹵†⁬䩤⁣⁯†⁲⁴⁹⁥⁧⁥䑥䕲⁲㱡⽴㹮൳੨⁩⁰†⁡㱮㹴䍨⹥†⁣䅴†⁵†⁮⁯⁥⁧⁡⁯†䑤䕥‼㰼⽰㹦ഠ੹⁯⁵†⁡㱲㹷䑯⹲⁫⁩䅮⁦⁲⁴⁴⁹†††††䑬䕵⁯㱥⽩㹥൲ਠ㱰⽡㹮൥ੲ㰠㹮䄠⁥䩷⁲⁤䄠☠㬠⁯†‮ †⁳ⸯ㱨⼳㸍

An SQL script is a set of SQL commands saved as a .sql file. If you are already running mysql, you can execute an SQL script file using the source command. Write an SQL script that upgrades the existing database to match the relational model provided to you, without losing any data. These SQL statements in the script must be provided in the correct order.

Marks will be awarded for the following:

Creating new tables, including attributes and associated domain constraints (2 Marks)
Updating existing tables, including attributes and associated domain constraints (2 Marks)
Primary and Foreign Keys in the new database (2 Marks)
All existing data is preserved and/or appropriately updated by the script (1 Mark)
The script starts with the commands provided below with the blanks filled in (1 Mark)

Please note the following:

This task will be marked by examining if the database matches the proposed relational model using deductive marking, not by marking each command separately.
If the submission is not an SQL Script or is any way corrupted, you will achieve 0 marks for this task.
If there are commands in the script (commands are separated by semicolons - ;) that do not run due to syntax errors, part marks will be awarded based on the commands that do run.
You must start your code with USE DATABASE Iconic and include all of our code below.
You must not hard code tuples to be reinserted in your code. Use the combined INSERT INTO SELECT command only. There are examples of this in the start of the script provided to you.

Task 3

Iconic Clothes require you to create a number of views that can be used to view summary data. This summary data is needed to help monitor the performance of the business.