ISY103 Database Management for Business


ISY002/ISY103 Database Management for Business

Assessment Type

Group Assignment – Database Project, Report, PostgreSQL database Implementation, and Individual Video Presentation







Total Marks


Alignment with

Unit and Course

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)

Graduate Attributes



GA2: Collaboration

GA4: Critical Thinking &

Problem Solving

GA5: Flexibility


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

Assignment 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, including 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:

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. It is required students to use Crow’s Foot Notation to Create an Entity Relationship Diagram (ERD) to help you decide on the relationships.

Your entity relation diagram that models your database design should:

  1. Include all entities, relationships (including names) and attributes.
  2. Identify primary and foreign
  • Include cardinality/ multiplicity and show using crow’s feet notation.
  1. 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 Microsoft Visio or try (Search for ERD gliffy to get started).

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.

Part C: Relational schema and Implementation of the 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. Implementation of the PostgreSQL database, include to create tables, build relationships between tables, insert values into tables, and Using psql program to import the created relational database file, AMCDB.sql into PostgreSQL Database. (15 Marks)
  3. Create tables according to your relational schema created in step 1. (10 Marks)
  4. 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 sql.
  5. 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.
  6. 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.
  7. Insert values into tables, add at least five records into each table. (5 Marks)

Part D: SQL (24 marks)

Use the PostgreSQL Database that you created and imported the file AMCDB.sql 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:


SELECT lName, position

FROM Staff

WHERE salary > 20000;



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

Part E: Final Report Layout, SQL script and presentation (6 marks)

  1. Deliverables for Parts A, B, C, and D must be printed as a report with a cover sheet, which includes student’s name and student number, unit name, assignment name. See Administrative Details on page 10 for more details. Your report must include footer that includes page number, report must also be formatted so that it is well set out and easy to read.
  1. a) Each group must submit a single zip file on Moodle which contains all the assignment files as follows:
    • Single word file with deliverables for parts A – D,
    • The sql that can be used to restore your database should also be uploaded to Moodle. You can create the PostgreSQL database file that contains a list of SQL statements which can be used to restore/recreate the original database.

Part F: Individual Personal Report, Presentation slides and 5 minutes video presentation - Every group member needs to write/make this part individually, to add the video link to the title page of the presentation slide, and to submit all files through A3 Part 3 submission link to Moodle, also needs to submit presentation slide and video to Google drive for peers review. The video presentation will be marked by peers. (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)

  1. The individual Presentation slides should include information as follows:

The title page should include student’s name, ID and the link of your YouTube Video.

  • The 1st slide describes the project domain modelled by this database. One slide could be enough. The audience just need to know enough about the domain to understand the ERD.
  • The 2nd slide includes the complete ERD for this database.
  • The 3rd slide includes the illustration a single one - to - many relationship in the database. This slide should show both the relationship as shown in the ERD and its realizations in tables, with some example values in the tables.
  • The 4th slide shows one query and output of Part D in Group Project Outline.
  • The last slide shows your contributions, any challenging issues, or any future work.
  1. The video presentation + Q&A will be presented during class in week 11, around 5 to 10 minutes long for each video. (10 marks)


When to submit

Refer to the Unit Description for due date, extensions and late submission policy. b. What to submit

  1. A3 Part 1 Submission includes Part A and Part B as follows:

Part A

  • Copy and paste the ERD that you created using Microsoft Visio, or other software into your Word document.
  • Title your AMC ERD.

Part B:

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

  1. A3 Part 2 Submission includes Part C, Part D and Part E as follows:

Part C:

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


Field Name 








Varchar (50)



Varchar (50)



char (1)



Varchar (60)





Char (10)





  • Include a PostgreSQL database file called AMCDB.sql, which include create tables, insert values into tables; and any constrains. Use boygirl.sql file as an example to create tables, to insert values into each table, and to link tables using constrains of Foreign Key.
  • Use DESCRIBE command to describe table the structure of each table in your AMCDB database, e.g. \d customer;
  • Show the first five records in each of your tables as shown by the example below:

SELECT * FROM customer;

Part D:

(1)      The answer to each SQL query question must be clearly numbered and tabulated as shown in the example below.

Question 1:


SELECT lName, position FROM Staff



| lName | position |


| Brand | Manager |

 White | Manager | +-------+----------+

2 rows in set (0.03 sec)

  1. Add a heading immediately above the table to identify the question number.
  2. Include both 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.
  3. Do not use screen captures to display the SQL statement or the output. Copy the text of your SQL statement and also the output into the table.
  4. Edit the keywords of your SQL statement (if necessary) to appear in uppercase as shown by the example above. Field names and table names should be entered in upper or lowercase as they appear in the database schema.
  5. 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.
  6. Format both the SQL and also the Output in Courier New 10 or 11 point or smaller in order to avoid wrapping.
  7. Marks will be deducted where students fail to follow the requirements given above.

Part E:

  1. Final report including PART A, B, C, and D, plus the PostgreSQL database Script file called AMCDB.sql as described in PART C in the specification.

A3 Part 3 Submission includes Part F as follows:

Part F:

  1. Individual personal report, presentation slides and video presentation need to be submitted through A3 Part 3 submission link to Moodle.
  2. Presentation slides and video also need to be uploaded to Google drive for peers review.
  3. Individual Video presentation + Q&A in Week 11 class

Administrative Details:

 Deadlines: There will be NO extensions except where Calendar rules apply. Deadlines must be met, unless an acceptable medical certificate covers 25% or more of the lead-time between the assignment and the deadline. Other absences must be accounted for by substantiating documentation, for example a police accident report or statutory declaration. Any assessment activities missed for medical (or other acceptable substantiated) reasons must be completed by a new deadline that will be assigned in negotiation with the lecturer.

 The Penalty for Late Submission of assignments is 5% of the total marks for each day (or part thereof, including weekends) the assignment is late. For assignments received more than 7 days after the due date, the assignment will be assessed, but a zero mark will be awarded.

Electronic failure causing delay: In most work submissions you have a final deadline but it is recommended that you submit well before this time. Timely submission is your responsibility – last minute technical disaster will not be accepted as a reason for late submission. You are expected to have discipline in time management, to make back-ups, and always have the back- ups available for submission.

         Your report is to be presented as a hard copy. All assessment activities that are handed in must be presented to a professional standard and must include:

  1. A hard copy to be submitted to the designated lecturer or nominated alternative by the due time and date. It must have a title page which includes the student’s name and ID number, title of assignment and a signed statement that the work is exclusively the student’s own work and that there has been no plagiarism or collusion. Reports without a coversheet will not be accepted.
  2. A footer to be shown on every page that includes students’ name, ID number and page #.
  3. A soft copy of your report to be submitted on AIH Moodle. The same assignment deadlines apply to this submission format.

E-mailing of your assignment will be accepted only after consultation with the tutor. If emailing, through arrangement with the tutor, send the document as an e-mail attachment in MS Word or PDF format. The subject line in the e-mail must include student name. When sending e-mails please request an automated receipt. Where there is doubt as to whether the assessment activity was submitted on time, no claims for timely submission will be accepted if there is no receipt.

 Submissions not clearly identified with the author’s details as outlined in the points above will not be marked and may result in a 0 grade for that activity.

PLAGIARISM AND COLLUSION Any submitted work that contains plagiarism or collusion will be reported to the appropriate authorities according to the University policy and will receive an automatic zero ‘0’ mark for the assignment.

Final Report Structure:

Your final report must comprise the following parts:

  • Title Page o Report title
    • Names and Student IDs for group members
  • Table of Contents o Should be auto-generated from Heading Styles in MS Word.
  • Background o Requirements / Business Rules o Mission Statement / System Definition
    • DBMS Selection
  • ERD x2
    • Use Microsoft Visio or any other tool, such as to create the ER model. This is to show your design.
  • Normalised Schema o Normalised schema
    • Appropriate arguments provided on leaving each entity in normal form which you considered as optimal
  • Database Schema o Show each table design
    • Include to create tables, to insert values into each table and any constrains - the implemented database called AMCDB.sql file in PostgreSQL format.
    • Records for each table, by using select * from each_table;
  • Foreign Key Constraints o Show as screen captures for all tables with foreign keys, etc., by using DESCRIBE command to describe table the structure of each table in your PostgreSQL Database, e.g. \d customer;
  • X12 SQL Statements o Show statement and output formatted using Courier New
    • Statements should be similar to the examples given and may include any of the following elements:
      • WHERE clause with examples of various conditions (Like, Between, =, AND / OR, IN, etc.)
      • ORDER BY clause
      • Concatenation to combine values including alias
      • Expressions to calculate results
      • Nature join using 2 or 3 tables
      • Subquery
      • Functions to aggregate data (SUM, MIN, MAX, AVERAGE, COUNT) including sub-totals (GROUP BY)
    • Group Report o Challenges & Solutions
    • References o Harvard Style o If you do not have any than just reference the prescribed text.


Upload Document Document Unser Evaluion Get Money Into Your Wallet

Cite This work.

To export a reference to this article please select a referencing stye below.

Assignment Hippo (2022) . Retrive from

"." Assignment Hippo ,2022,

Assignment Hippo (2022) . Available from:

[Accessed 14/08/2022].

Assignment Hippo . ''(Assignment Hippo,2022) accessed 14/08/2022.

Want latest solution of this assignment

Want to order fresh copy of the Sample Template Answers? online or do you need the old solutions for Sample Template, contact our customer support or talk to us to get the answers of it.

Submit Your Assignment Here

AssignmentHippo Features

On Time Delivery

Our motto is deliver assignment on Time. Our Expert writers deliver quality assignments to the students.

Plagiarism Free Work

Get reliable and unique assignments by using our 100% plagiarism-free.

24 X 7 Live Help

Get connected 24*7 with our Live Chat support executives to receive instant solutions for your assignment.

Services For All Subjects

Get Help with all the subjects like: Programming, Accounting, Finance, Engineering, Law and Marketing.

Best Price Guarantee

Get premium service at a pocket-friendly rate at AssignmentHippo


  • Assignment Writing Guide
  • Essay Writing Guide
  • Dissertation Writing Guide
  • Research Paper Writing Guide


  • Accounts
  • Computer Science
  • Economics
  • Engineering

Client Review

I was struggling so hard to complete my marketing assignment on brand development when I decided to finally reach to the experts of this portal. They certainly deliver perfect consistency and the desired format. The content prepared by the experts of this platform was simply amazing. I definitely owe my grades to them.

Tap to Chat
Get instant assignment help