Great Deal! Get Instant $10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

Aim:To give you practical experience in database modelling, normalization and writing SQL statements to query a relational databaseAssignment Summary:The project specification details provided in this...

1 answer below »
Aim:To give you practical experience in database modelling, normalization and writing SQL statements to query a relational databaseAssignment Summary:The project specification details provided in this document and you must use only given specifications to come up with your solution. Make appropriate assumptions where ever required.Please make sure that your group must meet your lecturer/tutor at least twice to update the progress of your assignment.Students require to submit 5 minutes individual video presentation, incuding slides to Moodle.Background Information for Sample Database Project: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.The proprietors of AMC have appointed your group as Database System Consultant to analyse, design and develop conceptual data model and physical database. They want a software/online application in future to be built on the database you design so that the operation of the AMC is greatly improved.Your created models must accommodate the provided requirements. It is understandable that the provided information may not sufficient to create model that centralise all the data appropriately. You are free to make assumptions about their business activities if require. Make sure you record your assumptions.System Requirements:The system is a prototype system and as such is not a full production version. You will be required to enter a representative sample data into your tables in order to test the design and operation of your database.Project Specification: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 cro
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 (5 marks)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 be normalise all entities in 3NF. Provide the reasons for any relation that is not maintained in 3NF.3. Part C: Relational schema and PostgreSQL database (20 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. (5 Marks)2. Using PostgreSQL, 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 XXXXXXXXXXMarks)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 tabl
name, fiel
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 PostgreSQL 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, positionFROM StaffWHERE salary > 20000;Output:+-------+----------+| lName | position |+-------+----------+| Brand | Manager || White | Manager | XXXXXXXXXX2 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 PostgreSQL Command Prompt window; choose SQL statement and Output, 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 $ XXXXXXXXXXmarks)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 XXXXXXXXXXmarks)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 , Geo 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/ XXXXXXXXXXmarks)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: Individual Personal Report, and 5 minutes video presentation, including presentation slides - Every group member needs to write/make this part individually and add to the documentation/submission (25 marks)1. Make a 5 minutes individual video presentation, including presentation slides, and Write a page to the AMC proprietors as a personal reflection that describes your experience building the database, and your contribution to your group project. 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. (15 marks)2. Students are also required to present your work in the class. The video presentation +Q&A can be around 5 to 10 minutes long. (10 marks)6. Part F: Report Layout, SQL script and presentation (6 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 membe
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, PostgreSQL script notepad file and individual 5 minutes video presentation, including slides).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:Create a text version of the database. Create PostgreSQL file that contains a list of SQL statements which can be used to restore/recreate the original database.7. Submissiona. When to submitRefer to the Unit Description for due date, extensions and late submission policy.b. What to submitPart A(1) Copy and paste the ERD that you created using Word, Visio, or other software into your Word document.(2) Title your AMC ERD.Part B:
Include in your word document normalised schema and discussion on which normal form each relation is in. Provide the reasons for any relation that is not maintained in 3NF.Part C: (1) Include the relational schema in your documentation. The following is sample tabular format on relational schema. You are free to add more columns if required.Table Name:Field NameData typeLengthKey Constraints(3) Provide database schema of your AMCDB in your word document. Title your AMCDB Database Schema. Below is sample. Arrange the tables so that the lines joining tables do not cross over (if possible). Copy and paste your diagram into your Word document as illustrated by the following example:DreamHome Database Schema:
(4) Use the DESCRIBE command to describe the structure of each table in your AMCDB database. Do not use screen captures. The following is an example of how each table should be documented:Describe Branch;+----------+-------------+------+-----+---------+-------+| Field ​| Type ​| Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| branchNo | char(4) ​| NO | PRI | NULL ​| ​|| street | varchar(16) | YES | ​| NULL ​| ​|| city ​| varchar(10) | YES | ​| NULL ​| ​|| postcode | char(8) ​| YES | ​| NULL ​| ​|+----------+-------------+------+----- XXXXXXXXXX5) Show the first five records in each of your tables as shown by the example below:SELECT * FROM Branch;+----------+--------------+----------+----------+| branchNo | street ​| city ​| postcode |+----------+--------------+----------+----------+| B002 ​| 56 Clover Dr | London | NW10 6EU || B003 ​| 163 Main St | Glasgow | G11 9QX || B004 ​| 32 Manse Rd | Bristol | BS99 1NZ || B005 ​| 22 Deer Rd | London | SW1 4EH || B007 ​| 16 Argyll St | Aberdeen | AB2 3SU |+----------+--------------+--- XXXXXXXXXX6) Use Courier New 8 or 9 point to ensure that the columns fit between the margins. Columns should not wrap if this can be avoided.(7) Use screen captures to show referential integrity constraints as shown by the following example for the propertyForRent table in the dream home database:
Answered Same Day May 28, 2021

Solution

Neha answered on May 29 2021
138 Votes
59198/ER diagram.png
59198/Presentation.pptx
AMC Database
Submitted By:-
Tables
Patrons – Details about the patrons
Employee- Details about the employees
Supplier- Details about the suppliers
Shop – Details about the Shops
Department- Details about the department related with shop
Payslip- Details about the salary of employees with employee id
Inventory- Details about the available stock in inventory with product id
OrderDetails- details about the orders placed by the customers with product id and customer id
Product- details about the products
Entity Relationship Diagram
Relational Schema
Patron (PatronId, FirstName, LastName, Address, Phone)
Shop (ShopId, Name, PhoneNo, Email, Fax, Street, Subu
, State, Postcode)
Supplier (SupplierId, Name, Address,Phone,Email)
Product (ProductId, Name, Brand, Description, Price, SupplierId)
Inventory (InventoryId, ShopId, ProductId, Available, Ordered)
Department (DepartmentId, Name, ShopId, Email, Phone)
Employee (EmployeeId, FirstName, LastName, PhoneNo, Email, Address, TFN, Salary, JoiningDate, DepartmentId, EmployType)
OrderDetail (OrderId, Date, ProductId, Quantity, PatronId)
PaySlip (PayslipId, ShopId, hours, employeeId, grosspay)
Normalization
The database normalization can be defined as a technique which is used to organise the data present in the database in predefined format. It is helpful in storing the data in the table and remove the redundancy of data insertion combination and update it sector. The data is stored in tabular form and also remove the replicate values. The database which can be used for the AMC company is very complex and contains multiple tables. While designing the database I tried to eliminate all the possible redundancies and the data dependencies make sense. To reach the 3rd normal form which is the best we need to make sure that the database is in first and second normal form.
First normal form
Each column contains single value and no column name is same in a single table which makes it to satisfy the conditions of first normal form.
Second normal form
While designing the database hi tried to remove all the partial dependencies so that it can achieve the second normal form of the normalization. The partial dependency can be stated as an attribute of the table which is dependent on a part of primary key but not on the complete Prime key.
Third Normal Form
As there is no transitive dependency among the table prove that the database is in 3rd normal form rest of there is no data to plication in the table and it has achieved the data integrity.
What I learnt
The AMC database contains multiple tables and has a complex design. It was very difficult to find out all the required tables and the relationship among them. This database contains 9 table and every table is related with each other. It is difficult to find out the religious trip and normalise the table's into the 3rd normal form to stop to have a proper database we should at least achieve the 3rd normal form for the database which ensures that there is no transitive dependency and no redundancy.
Thank You
59198/Report.docx
Part A: ERD for AMC
Part B: Normalisation
The database normalization can be defined as a technique which is used to organise the data present in the database in predefined format. It is helpful in storing the data in the table and remove the redundancy of data insertion combination and update it sector. The data is stored in tabular form and also remove the replicate values. The database which can be used for the AMC company is very complex and contains multiple tables. While designing the database I tried to eliminate all the possible redundancies and the data dependencies make sense. To reach the 3rd normal form which is the best we need to make sure that the database is in first and second normal form.
First normal form
Each column contains single value and no column name is same in a single table which makes it to satisfy the conditions of first normal form.
Second normal form
While designing the database hi tried to remove all the partial dependencies so that it can achieve the second normal form of the normalization. The partial dependency can be stated as an attribute of the table which is dependent on a part of primary key but not on the complete Prime key.
Third Normal Form
As there is no transitive dependency among the table prove that the database is in 3rd normal form rest of there is no data to plication in the table and it has achieved the data integrity.
Part C: Relational Schema
Patron (PatronId, FirstName, LastName, Address, Phone)
Shop (ShopId, Name, PhoneNo, Email, Fax, Street, Subu
, State, Postcode)
Supplier (SupplierId, Name, Address,Phone,Email)
Product (ProductId, Name, Brand, Description, Price, SupplierId)
Inventory (InventoryId, ShopId, ProductId, Available, Ordered)
Department (DepartmentId, Name, ShopId, Email, Phone)
Employee (EmployeeId, FirstName, LastName, PhoneNo, Email, Address, TFN, Salary, JoiningDate, DepartmentId, EmployType)
OrderDetail (OrderId, Date, ProductId, Quantity, PatronId)
PaySlip (PayslipId, ShopId, hours, employeeId, grosspay)
Table Patron
        Column Name
        Data Type
        Length
        Key...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here