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

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 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

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 PostgreSQL, you are required to develop a demonstration prototype system (database) that stores data for AMC stores, employees, products, suppliers, patrons and orders. Create 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 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.

4. Part D: SQL

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, 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 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. 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.

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.

5. List all the orders having at least two different products ordered.

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.

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

10. Retrieve customer number and the total number of orders placed by each customer.

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.