Advanced Management Console

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

AMC DATABASE SYSTEM1
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

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

AMC DATABASE SYSTEM3
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

AMC DATABASE SYSTEM4
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

AMC DATABASE SYSTEM5
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

AMC DATABASE SYSTEM6
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

AMC DATABASE SYSTEM7
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