Database Management System Assignment 2022

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

DATABASE MANAGEMENT SYSTEM
1
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

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

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

DATABASE MANAGEMENT SYSTEM
4
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

DATABASE MANAGEMENT SYSTEM
5
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

DATABASE MANAGEMENT SYSTEM
6
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

DATABASE MANAGEMENT SYSTEM
7
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