Relational schema and MySQL database Question

Relational schema and MySQL database (35 marks)

1. Create relational schema that matches ERD and normalised schemas you created in Part A and B. The schema should include suitable data validation, integrity and referential integrity constraints, for example, types and length of attributes, primary and foreign keys etc. Make sure you follow standards, naming conventions and notation. (10 Marks)

2. Using MySQL, you are required to develop a demonstration prototype system (database) that stores data for AMC stores, employees, products, suppliers, patrons and orders. Create a new database called AMCDB. Create tables according to your relational schema created in step 1. (20 Marks)

Follow a standard naming convention for table names and also field names. Avoid using spaces and any special characters in table and field names. Use underscore case or use camelCase to separate parts of a name.

You are required to be consistent in tables’ name, fields’ name, data types, field size and key constraints with relational schema created in step 2.

3. Add at least three records into each table. (5 Marks)

4. Part D: SQL (24 marks)

Use the AMCDB database that you created in MySQL to design and execute SQL queries that answer the following questions. Number your answers to each question clearly. The answer to each question must be tabulated as shown in the example below and include the SQL statement and also the output that is produced when you execute the statement in your database. The output includes the records that are listed and also the message that appears when you run the SQL statement.

For example:

Question 1:

{`
    SQL: SELECT lName, position

    FROM Staff


    WHERE salary > 20000;

    Output: +-------+----------+

    | lName | position |

    +-------+----------+

    | Brand | Manager |

    | White | Manager |

    +-------+----------+
    `}

2 rows in set (0.03 sec)

Do not use screen captures to display the SQL statement or the output. You should right-click on the MySQL Command Prompt window; choose Mark and then press the [Enter] key to Copy and then Paste into your Word document that includes the answers to all questions.

Format and indent the clauses in your SQL statements for better readability and understanding as shown in the example above. Statements must be syntactically and semantically correct.

Format both the SQL and also the Output in Courier New

10 or 11 point.

1. List the full name of Patrons, phone number and address in the descending order of customer number. The full name is comprised of first and last name joined with single space. Use the alias Customer full name for the composed column heading. (2 marks)

2. List all the products having price less than $100. (2 marks)

3. List the product number, product title, quantity ordered, unit price and total amount per product (unit price * quantity ordered) for order having order number 1005. (3 marks)

4. List name, full address and fax number of stores that has the word ‘George’ anywhere in the street component of address. Your query should consider the case sensitivity of the street as well i.e. the stores with words ‘George’, ‘GEORGE’, ‘george’, ‘GeorGe’ etc. in street should be returned by your query too. (2 marks)

5. List all the orders having at least two different products ordered. (2 marks)

6. List customer number, name and address of all the customers who has not placed any order. (2 minutes)

7. Retrieve first name, last name and employee number of all the employees along with department name and store name where they work. (3 marks)

8. For each store, display store name and number of employees works in account department. (2 marks)

9. List all the orders placed for financial year 2018 i.e. orders placed after 30/06/2017 and before 01/07/2018. (2 marks)

10. Retrieve customer number and the total number of orders placed by each customer. (2 marks)

11. For each order, retrieve the order number, order date and number of products ordered in it. Sort the output in descending order of order number. (2 marks)

5. Part E: Personal Report - Every group member needs to write this part individually and add to the documentation (4 marks)

1. Write a page to the AMC proprietors as a personal reflection that describes your experience building the database. You can discuss any challenges / difficulties that you experienced or solutions that you found. Comment on any limitations and / or strengths of your database design. Comment on whether your database meets all the system requirements as specified in Project Specification. Avoid making excuses or comments that reflect negativity. Include an acknowledgement of all students you have spoken to about the assignment.

6. Part F: Report Layout, SQL script and presentation (7 marks)

1. Deliverables for Parts A, B, C, D and E must be printed as a report with a cover sheet attached. See Administrative Details on page 10 for more details. Your report must include header and footer that include all the group members’ name and student number, unit name, assignment name, and page numbers. Your report must be checked for spelling and grammar. Your report must also be formatted so that it is well set out and easy to read.

a) Each group must submit a single zip file on Moodle which contains all the assignment files (single word file with deliverables for parts A – D, separate personal reports for each group member, MYSQL backup script notepad file and presentation).

b) The SQL that can be used to restore your database should also be uploaded to Moodle. You can create the SQL for your database as follows:

Use the mysqldump command to create a text version of the database. Use mysqldump to create SQL file that contains a list of SQL statements which can be used to restore/recreate the original database.

The syntax is as follows:

{`
    $ mysqldump -u [uname] -p[pass] [dbname] > [backupfile.sql]

    WHERE:

    [uname] Your database username

    [pass] The password for your database

    [dbname] The name of your database

    [backupfile.sql] The filename for your database backup
    `}

You do not need to log on to MySQL. For example, from the DOS command prompt, the syntax is:

e:\xampp\mysql\bin>mysqldump -u root MyDB > e:\MyDB_bak.sql

This will create the SQL file that can be used to restore your database.

OR ---

You can save your script as you go to notepad.

You are also required to present your work in the class. The presentation can be around 5 to 10 minutes long.

MYSQL Tutorials

• Create MySQL Database
• Create Table in MySQL
• Insert data to MySQL
• Mysql Queries
• Update data in MYSQL
• Delete data in MySQL
• Default Database
• Primary Key
• Foreign Keys
• Views
• Export MySQL Database
• Import data into Mysql
• Create Website with MySQL

MYSQL Sample Assignments

• COIT20247 Database Design and Development
• Relational schema and MySQL database
• Mysql Project
• ITICT107A Introduction to Databases
• MySql Assignment Question

MYSQL Sample Solutions

• Solution - COIT20247 Database Design
• Solution - Relational schema and MySQL
• Solution - Mysql Project

Testimonials

Very affordable projects!! And that to submit before deadlines. Thanks for helping me in my database project and raising my grades. I have been able to secure good marks in my internal assessment only because of you. Read More