Hello, if you have any need, please feel free to consult us, this is my wechat: wx91due
Assignment 1 Specification
You are expected to solve all tasks individually without cooperating with the other students.
Suppose it is suspected that you have received assistance from another person to complete the tasks. In that case, the matter will be investigated as an alleged breach of the UOW College Academic Integrity and Student Conduct Policy by the Procedure for Managing Alleged Student Misconduct. As part of this investigation, you may be required to undergo an oral examination to verify your understanding of the assessment content.
5. Task 4 (5 marks)
Transform a conceptual schema and create tables in MySQL
In this task, you must use a virtual machine with MySQL. All technical details on how to start and how to use a virtual machine have been explained and practised in Lab 1, task 2 and task 3.
Consider a conceptual schema given below:
1. Perform a step of logical database design and transform a conceptual schema given above into a collection of relational schemas. Use an association method for the implementation of generalization.
Show your work for each step of the process and use the template provided.
2. Next, use the relational schemas obtained in the previous step to create an SQLscript solution4.sql with CREATE TABLE statements that implement the relational schemas.
Your CREATE TABLE statements must enforce the following types of columns in the relational tables.
- All columns that contain dates must be of type DATE.
- All item codes must be a fixed-size sequence of 8 characters.
- All order numbers are sequences of 10 digits.
- All discounts applied are fractions in a range (0,1) with 1 position after
- the decimal point.
- All prices are real numbers (floating-point numbers) in a range 0.00 to 99999.99 with 2 positions after the decimal point.
- The types of all other columns in the relational tables are up to you. However, the types must make sense. For example, a city or country name of type integer will not get a lot of appreciation from a person evaluating your solution.
Note, that you MUST use only CREATE TABLE statements and no other statement of SQL You can find a lot of information about the implementation of CREATE TABLE statements in a presentation 09 SQL - Data Definition Language (DDL) and in Cookbook, How to use data definition and basic data manipulation statements of SQL, Recipe 4.1 How to create and how to alter the relational tables?
You can use either graphical user interface SQL Developer or command-line interface MySQL to implement your script.
3. When your script is ready connect to the command line interface MySQL and drop all relational tables created so far.
Next, process the script solution4.sql implemented in the previous step. Processing of the script must create a report. The report from the processing of a script solution4.sql
If the processing of the file returns errors, then you must eliminate the errors. Processing of your script must show NO ERRORS. A solution with errors is worth no marks.
ERROR 1050 (42S01): Table '…' already exists
Please, remember that such a message counts as an error in processing the script and that a solution with errors is worth no marks.
To create a report, you must use a technique already practiced in Lab 1. You can also find more information about creating reports from the processing of SQL scripts in Cookbook Recipe 3 1 How to use "MySQL? Command based interface to MySQLin Cookbook, Recipe 3.1 How to use MySQL? Command based interface to MySQL database server? Step 4 How to save the results of SQL processing in a file?
You can find more information on how to display SQL statements while a script is processed in Cookbook, Recipe 3.1 How to use "MySQL? Command based interface to MySQL database server? Step 3 How to process SQL script?
A report that does not contain a listing of the processed SQL statements will score no marks; make sure that you connect to MySQL client with an option -v And again, … a report from the processing of your SQL script must contain NO ERRORS.
A file solution4.pdf showing the transformation of the UML into relational schemas.
A file solution4.rpt with a report from the processing of your SQL script solution4.sql.
The report must be created with the command line interface MySQL, the report MUST NOT include any errors, and the report must list all SQL statements processed.
Submission of a file with a different name and/or different extension and/or different type scores no marks.