ISY1002 - Database Management for Business - Structured Query Language (SQL) - Database Project Report and Presentation Assessment Answer

Assessment Task:

ISY1002 Database Management Report Writing Assessment Answer

Unit Learning Outcome

ULO2: Show an understanding of the fundamentals of database design, implementation, management, and maintenance, including consideration of appropriate data security

ULO3: Implement a relational database using the Structured Query Language (SQL) Data Definition Language (DDL)

ULO4: Demonstrate an ability to query a relational database using Structured Query Language (SQL) and Data Manipulation Language (DML)

Aim:

To give you practical experience in database modelling, normalization and writing SQL statements to query a relational database

Project Specification:

1. Part A: Entity Relationship Diagram

1. Your first task is to study the Case study requirements and gather as much knowledge to list all the main Entities and attributes.

2. Create an Entity Relationship Diagram (ERD) to help you decide on the relationships.

Your entity relation diagram that models your database design should:

i. Include all entities, relationships (including names) and attributes.

ii. Identify primary and foreign keys.

iii. Include cardinality/ multiplicity and show using crow’s feet or UML notation.

iv. Include participation (optional / mandatory) symbols if applicable.

The ERD should be created as part of a Microsoft Word document. Hand-drawn diagrams will not be accepted. It is recommended that you complete your ERD using Draw.io

2. Part B: Normalisation

1. Normalise all entities and relationships in optimal normal form. Provide the normalised schema and discuss the level of normalisation achieved for each relation. You goal should normalise all entities in 3NF. Provide the reasons for any relationship that is not maintained in 3NF.

3. Part C: Relational schema and MySQL database

1. Create a 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.

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.

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.

3. Add at least three records into each table.

4. Part D: SQL

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.

Question 1:

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.

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

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

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.

This ISY1002: Database Management Assessment has been solved by our Database Management experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.