ISY1002/ISY103: Database Management for Business - Australian Manufacturing Company - IT Assignment Help

Assignment Task:

Unit Learning Outcome

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

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

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

Australian Manufacturing Company (AMC) requires a database system to manage their stores, employees, products, suppliers, patrons and orders. 

The following information has been gathered about current business activities of AMC. 

• AMC has many stores in different cities across Australia. Each store has unique store number and other details like name, contact details (phone number, email address and fax number) and location details (postal address composed of street number, suburb, state and postcode).
• There are many departments within each store, for example, HR, accounts, finance and sales etc. each department is uniquely identified by department number. AMC also records department title, phone number and email address.
• Each store has a number of employees. Each department is supervised by a department supervisor and each store is managed by a store manager who both are employees of AMC. Each department has a single supervisor only i.e. the department supervisor is also the supervisor for all the employees work within that department.
• Each employee is referenced by unique employee number, name (first and last names), address, mobile number, email, TFN, salary and joining date. An employee’s salary is recorded differently as per his or her employment type. Employees can be employed as fulltime or casual basis. For a fulltime staff, annual salary is recorded and hourly rate for a casual staff.
• Each store creates payslips for all employees work in that store on a weekly basis. As a minimum payslip number, no of hours worked, employee detail, store detail and gross pay are recorded.
• AMC sales different products like fashion, health, beauty, electronics etc. Each product is characterised by product number, name, brand, description and price.
• Each product can be supplied by one or more supplier and each supplier can supply one or many products. AMC also keeps appropriate details of their suppliers.
• Each store maintains an inventory of their products available for sale. The quantity of each product available in store and the quantity ordered are kept.
• AMC patron visits the store and shops in the store. Each customer is characterised by customer number, name (first and last names), mobile number and address.
• A patron may place orders. An order contains one or many products at a time. A store keeps track of order number, order date, product(s) ordered and quantity ordered for each product. 

1. Part A: Entity Relationship Diagram (20 marks)

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, Visio or try www.gliffy.com (Search for ERD gliffy to get started). 

2. Part B: Normalisation 

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 be normalise all entities in 3NF. Provide the reasons for any relation that is not maintained in 3NF.  

3. Part C: Relational schema and MySQL database 

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. 

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. 

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. 

This ISY1002: IT Assignment has been solved by our IT 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.