Database Management System Project: Australian Manufacturing Company

This document presents a database management system project developed for the Australian Manufacturing Company (AMC). It includes an Entity Relationship Diagram (ERD) illustrating the database structure, a normalized schema to reduce data redundancy, and a relational schema defining the tables, fields, data types, keys, and constraints. The database schema, named AMCDB, encompasses tables such as Patron, Orders, Employee, Fulltime, Casual, Department, Inventory, Payslip, Product, Store, Suppliers, Supplier_has_product, and Orders_has_product, each described with its respective fields and data types. The document further details the table structures, data, and referral integrity constraints, along with example SQL queries to interact with the database. This comprehensive project provides a practical example of database design and implementation for business needs.

Running head: DATABASE MANAGEMENT SYSTEM
Database Management System
Name of the Student
Name of the University
Author Note

1DATABASE MANAGEMENT SYSTEM
Table of Contents1
AMC ERD..................................................................................................................................2
Normalised Schema...................................................................................................................3
Relational Schema Table............................................................................................................3
AMCDB Database Schema........................................................................................................9
Table Structure...........................................................................................................................9
Referral Integrity Constraints...................................................................................................25
Queries.....................................................................................................................................26

2DATABASE MANAGEMENT SYSTEM
AMC ERD
Figure 1: AMC Entity Relationship Diagram
Source: created by author

3DATABASE MANAGEMENT SYSTEM
Normalised Schema
Figure 2: AMC Normalised ERD
Source: created by author
Relational Schema Table
1. Patron

4DATABASE MANAGEMENT SYSTEM
Field Name Data type Length Key Constraints
Cnumber varchar 10 Primary
Fname varchar 50
Lname varchar 50
Phone int 10
Address varchar 50
2. Orders
Field Name Data type Length Key Constraints
Sl no int 11 Primary
Onumber varchar 10
Odate date
Customer_cnumber varchar 10 Primary, Foreign
3. Employee

5DATABASE MANAGEMENT SYSTEM
Field Name Data type Length Key Constraints
Enumber varchar 10 Primary
Fname varchar 50
Lname varchar 50
Address varchar 100
Mobile int 10
Email varchar 50
Tfn int 10
Join date
Department_Dnumber int 100 Primary, Foreign
Store_Snumber int 100 Primary, Foreign
4. Fulltime
Field Name Data type Length Key Constraints
Salary int 11
Designation varchar 45
Employee_Enumber varchar 10 Primary, Foreign
5. Casual
Field Name Data type Length Key Constraints
Hourlyrate int 11
Employee_Enumber varchar 10 Primary, Foreign
6. Department

6DATABASE MANAGEMENT SYSTEM
Field Name Data type Length Key Constraints
Dnumber int 100 Primary
Dname varchar 50
Email varchar 50
Phone int 10
7. Inventory
Field Name Data type Length Key Constraints
Store_Snumber int 100 Primary, Foreign
Product_Prnumber int 10 Primary, Foreign
Available int 11
Ordered int 11
8. Payslip
Field Name Data type Length Key Constraints
Employee_Enumber varchar 10 Primary, Foreign
Hours varchar 10
Grosspay int 10
9. Product

7DATABASE MANAGEMENT SYSTEM
Field Name Data type Length Key Constraints
Prnumber int 10 Primary key
Prtype varchar 50
Name varchar 50
Brand varchar 50
Description varchar 50
Price int 10
10. Store
Field Name Data type Length Key Constraints
Snumber int 100 Primary
Sname varchar 50
PhoneNumber int 50
Email varchar 50
Fax int 50
Street_no varchar 10
Suburb varchar 50
State varchar 50
Postcode int 6
11. Suppliers

8DATABASE MANAGEMENT SYSTEM
Field Name Data type Length Key Constraints
Spid int 11 Primary
Name varchar 50
Phone int 10
12. Supplier_has_product
Field Name Data type Length Key Constraints
Suppliers_spid int 11 Primary, Foreign
Product_prnumber int 10 Primary, Foreign
13. Orders_has_product
Field Name Data type Length Key Constraints
Order_sl no int 11 Primary, Foreign
Product_prnumber int 10 Primary, Foreign
Quantity int 11

9DATABASE MANAGEMENT SYSTEM
AMCDB Database Schema
Figure 3: Relational Schema of AMCDB
Source: created by author
Table Structure
1. Patron

10DATABASE MANAGEMENT SYSTEM
Describe Patron;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| Cnumber | varchar(10) | NO | PRI | NULL | |
| Fname | varchar(50) | NO | | NULL | |
| Lname | varchar(50) | NO | | NULL | |
| Phone | int(10) | NO | | NULL | |
| Address | varchar(50) | NO | | NULL | |
+---------+-------------+------+-----+---------+-------+
2. Orders
Describe Orders;
+------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+----------------+
| Sl no | int(11) | NO | PRI | NULL | auto_increment |
| Onumber | varchar(10) | NO | | NULL | |
| Odate | date | NO | | NULL | |
| Customer_Cnumber | varchar(10) | NO | PRI | NULL | |
+------------------+-------------+------+-----+---------+----------------+
3. Employee

11DATABASE MANAGEMENT SYSTEM
Describe Employee;
+--------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| Enumber | varchar(10) | NO | PRI | NULL | |
| Fname | varchar(50) | NO | | NULL | |
| Lname | varchar(50) | NO | | NULL | |
| Address | varchar(100) | NO | | NULL | |
| Mobile | int(10) | NO | | NULL | |
| Email | varchar(50) | NO | | NULL | |
| Tfn | varchar(10) | NO | | NULL | |
| Join | date | NO | | NULL | |
| Department_Dnumber | int(100) | NO | PRI | NULL | |
| Store_Snumber | int(100) | NO | PRI | NULL | |
+--------------------+--------------+------+-----+---------+-------+
4. Fulltime
Describe Fulltime;
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| Salary | int(11) | YES | | NULL | |
| Designation | varchar(45) | YES | | NULL | |
| Employee_Enumber | varchar(10) | NO | PRI | NULL | |
+------------------+-------------+------+-----+---------+-------+
5. Casual