ISY1002/ISY103: AMC Database Project - Database Design and SQL Queries

This document details a database project for the Australian Manufacturing Company (AMC). It includes an Entity Relationship Diagram (ERD) and a normalized schema, representing the database's structure. The project further presents a relational schema with table structures, data samples for each table, and a comprehensive set of SQL queries. The SQL queries demonstrate data retrieval, manipulation, and other database operations. The project is completed with a personal report summarizing the project's key aspects and a bibliography of the references used. This project aims to provide practical experience in database modeling, normalization, and writing SQL statements, aligning with the course objectives of database design, implementation, and management.

Running head: AMC DATABASE SYSTEM
DATABASE SYSTEM OF AUSTRALIAN MANUFACTURING COMPANY
Name of the Student
Name of the University
Author Note

1AMC DATABASE SYSTEM
Table of Contents
Part A: AMC ERD.....................................................................................................................2
Part B: Normalised Schema.......................................................................................................3
Part C: Relational Schema Table:..............................................................................................3
AMCDB Database Schema..................................................................................................10
Table Structures of AMCDB...............................................................................................10
Data of the each tables:........................................................................................................16
Part D: SQL Queries:-..............................................................................................................26
Part E: Personal report:-...........................................................................................................32
Bibliography:............................................................................................................................34

2AMC DATABASE SYSTEM
Part A: AMC ERD
Figure 1: Entity Relationship Diagram AMC
(Source: Created by author)

3AMC DATABASE SYSTEM
Part B: Normalised Schema
Figure 2: Normalised Schema of AMC
(Source: created by author)
Part C: Relational Schema Table:
Table name: Department
Field Name Data Type Length Key Constraints
department_number int 11 Primary Key
department_name varchar 20

4AMC DATABASE SYSTEM
contact_no int 11
Table name: Employee
Field Name Data Type Length Key Constraints
employee_number int 11 Primary
first_name varchar 20
last_name varchar 20
address varchar 30
mobile_number int 11
TFN int 11
salary int 11
joining_date date No
department_number int 11 Foreign Key

5AMC DATABASE SYSTEM
Table Name: Fulltime
Field Name Data Type Length Key Constraints
employee_number int 11
Primary Key,
Foreign key
salary int 11
Table Name: Inventory
Field Name Data Type Length
Key
Constraints
product_number int 11
Primary Key,
Foreign key
store_number int 11
Primary Key,
Foreign key
orderd int 11
available int 11
Table Name: Order
Field Name Data Type Length
Key
Constraints
order_number int 11 Primary

6AMC DATABASE SYSTEM
product_number int 11 Foreign key
order_date date No
quantity int 11
patron_number int 11 Foreign Key
Table Name: Parttime
Field Name Data Type Length
Key
Constraints
employee_number int 11
Primary Key,
Foreign Key
hourly_rate int 11
Table Name: Patron
Field Name Data Type Length
Key
Constraints
patron_number int 11 Primary Key
first_name varchar 20
last_name varchar 20

7AMC DATABASE SYSTEM
phone_number int 11
address varchar 30
Table Name: Payslip
Field Name Data Type Length Key Constraints
slip_number int 11 Primary Key
hour_worked int 11
employee_number int 11 Foreign key
gross_pay int 11
Table Name: Product
Field Name Data Type Length Key Constraints
product_number int 11 Primary Key
product_title varchar 20
brand varchar 20
price int 11
description varchar 50
Table Name: Store
Field Name Data Type Length Key Constraints

8AMC DATABASE SYSTEM
store_number int 11 Primary key
store_name varchar 30
store_address varchar 30
contact_no int 11
fax_no int 11
Table Name: Storemanager
Field Name Data Type Length Key Constraints
manager_id int 11 Primary key
first_name varchar 20
last_name varchar 20
contact_no int 11
store_number int 11 Foreign key
Table Name: Supervisor
Field Name Data Type Length Key Constraints
supervisor_id int 11 Primary key

9AMC DATABASE SYSTEM
first_name varchar 20
last_name varchar 20
department_number int 11 Foreign key
Table name: Supplier
Field Name Data Type Length Key Constraints
supplier_id int 11 Primary key
supplier_name varchar 20
contact_no int 11
AMCDB Database Schema

10AMC DATABASE SYSTEM
Table Structures of AMCDB
Product Table:
Describe product;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| product_number | int(11) | NO | PRI | NULL | |
| product_title | varchar(20) | NO | | NULL | |
| brand | varchar(20) | NO | | NULL | |
| price | int(11) | NO | | NULL | |
| description | varchar(50) | NO | | NULL | |
+----------------+-------------+------+-----+---------+-------+
Inventory Table:
Describe inventory;
+----------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------+------+-----+---------+-------+
| product_number | int(11) | NO | PRI | NULL | |
| store_number | int(11) | NO | PRI | NULL | |
| orderd | int(11) | NO | | NULL | |
| available | int(11) | NO | | NULL | |
+----------------+---------+------+-----+---------+-------+
Employee Table:

11AMC DATABASE SYSTEM
Describe employee;
+-------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| employee_number | int(11) | NO | PRI | NULL | |
| first_name | varchar(20) | NO | | NULL | |
| last_name | varchar(20) | NO | | NULL | |
| address | varchar(30) | NO | | NULL | |
| mobile_number | int(11) | NO | | NULL | |
| TFN | int(11) | NO | | NULL | |
| salary | int(11) | NO | | NULL | |
| joining_date | date | NO | | NULL | |
| department_number | int(11) | NO | MUL | NULL | |
+-------------------+-------------+------+-----+---------+-------+
Department Table:
Describe department;
+-------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| department_number | int(11) | NO | PRI | NULL | |
| department_name | varchar(20) | NO | | NULL | |
| contact_no | int(11) | NO | | NULL | |
+-------------------+-------------+------+-----+---------+-------+
Store Table: