MySQL代写：IAB130 Databases

发表于   |   分类于

代写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:

  1. Reflecting on the differences between the model Part A solution and your own;
  2. Creating a script that will upgrade the existing version of the database to the new version without losing any data; and,
  3. 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:

  1. A copy of the entity relationship model for the new database design; and,
  2. 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 requirements of the scenario. In this part you will build a new version of the database by:

  1. Reflecting on the differences between the agreed database design and the one you designed;
  2. Building a script that will upgrade the existing database to the new version without losing any data;
  3. Providing the commands needed to create the required views;
  4. Providing the commands needed to create appropriate indexes; and,
  5. Providing advice on the basic security measures that should be implemented.

Task 1

Discuss two major differences between the entity relationship diagram and/or relational model provided and your solutions to Tasks 2 and 4 in Part A.

For each difference you should state what the difference is, show the difference (e.g. provide a screenshot of the relevant part of the ERD), and explain which one better models the use case (or state that the difference has little impact on the functionality of the database). Do not discuss trivial differences such as the names of tables or attributes. Each difference should be explained in 150 words or less.

If there were no differences between the solutions you provided in Part A and the models provided to you in Part B, then you should discuss one alternative way of modelling the ternary relationship and the impact this would have on the proceeding relational model.

If you are working in a different pair to what you did in Part A, pick the solution of either partner in the new pair and state whose solution it was.

Task 2

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:

  1. Creating new tables, including attributes and associated domain constraints (2 Marks)
  2. Updating existing tables, including attributes and associated domain constraints (2 Marks)
  3. Primary and Foreign Keys in the new database (2 Marks)
  4. All existing data is preserved and/or appropriately updated by the script (1 Mark)
  5. 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.

Write the commands required to create the following four views:

  • A. Current Products View. List the name and current unit price of each product currently in season (2016). The list should be ordered by category name.
  • B. Category Summary View. List the total number of products that belong to each category, as well as the average price of products in that category. The list should be ordered by category name.
  • C. Most Popular Products View. List the name, current unit price, category name, and total number ordered (by customers only) for each product. The list should be ordered by the total number of times the product has been ordered (in descending order).
  • D. Most Popular Product Categories View. List the category name and ID of every category. Next to each category display the total quantity of products ordered from the category, and the total value of the products ordered (total quantity x unit price at time of sale) from each category.

Task 4

Currently the database only contains a small number of records, however the data contained within it is expected to grow significantly in the future. Creating indexes on commonly searched columns is a way performance issues can be minimized.

Provide the commands required to create an index on the following columns:

  • A. Email Address in the Customers table
  • B. Product Name in the Products table

Task 5

Iconic clothes have two employees, Jimmy and Amanda, that need to work directly with the MySQL database. Provide the commands required to grant or revoke access so the following security requirements are met:

  • A. User Jimmy must be able to add records to the ORDERS table
  • B. User Jimmy must be able to remove records from the ORDERS table
  • C. User Amanda is no longer allowed to add data to the ORDERS table
  • D. User Amanda is no longer allowed to delete records from the ORDERS table

Assume Jimmy and Amanda’s usernames are jimmy and amanda respectively.