COIT20247 Database Design and Development Solution

MYSQL Homework Help, MYSQL Assignment Help, Database Homework Help

Implementation in MySQL Workbench

use parcel1;

CREATE TABLE Customer

(

CustomerID INT NOT NULL,

CustomerName VARCHAR(50) NOT NULL,

CustomerContactNum NUMERIC(10) NOT NULL,

PRIMARY KEY (CustomerID)

);

CREATE TABLE Cust_Add

(

StreetAddress VARCHAR(50) NOT NULL,

City VARCHAR(25) NOT NULL,

PostCode VARCHAR(50) NOT NULL,

CustomerID INT NOT NULL,

FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)

);

CREATE TABLE Employee

(

EmployeeID INT NOT NULL,

EmployeeName VARCHAR(50) NOT NULL,

EmployeeStartDate VARCHAR(50) NOT NULL,

PRIMARY KEY (EmployeeID)

);

CREATE TABLE Parcel

(

ParcelID INT NOT NULL,

ParcelWeightsInGrams INT NOT NULL,

DateCollected DATE NOT NULL,

ParcelValue INT NOT NULL,

Charges INT NOT NULL,

Status CHAR(10) NOT NULL,

CustomerID INT NOT NULL,

EmployeeID INT NOT NULL,

PRIMARY KEY (ParcelID),

FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),

FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)

);

CREATE TABLE ParcelFromAddress

(

StreetAddress VARCHAR(50) NOT NULL,

City VARCHAR(50) NOT NULL,

PostCode VARCHAR(50) NOT NULL,

ParcelID INT NOT NULL,

FOREIGN KEY (ParcelID) REFERENCES Parcel(ParcelID)

);

CREATE TABLE ParcelToAddress

(

StreetAddress VARCHAR(50) NOT NULL,

City VARCHAR(50) NOT NULL,

PostCode VARCHAR(50) NOT NULL,

ParcelID INT NOT NULL,

FOREIGN KEY (ParcelID) REFERENCES Parcel(ParcelID)

);

CREATE TABLE Invoice

(

InvoiceID INT NOT NULL,

InvoiceDate DATE NOT NULL,

InvoiceAmount INT NOT NULL,

ParcelID INT NOT NULL,

PRIMARY KEY (InvoiceID, ParcelID),

FOREIGN KEY (ParcelID) REFERENCES Parcel(ParcelID)

);

CREATE TABLE Delivered

(

DeliveredDate DATE NOT NULL,

ParcelID INT NOT NULL,

EmployeeID INT NOT NULL,

PRIMARY KEY (ParcelID, EmployeeID),

FOREIGN KEY (ParcelID) REFERENCES Parcel(ParcelID),

FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)

);

CREATE TABLE Returned

(

ReturnedDate DATE NOT NULL,

ParcelID INT NOT NULL,

EmployeeID INT NOT NULL,

PRIMARY KEY (ParcelID, EmployeeID),

FOREIGN KEY (ParcelID) REFERENCES Parcel(ParcelID),

FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)

);

CREATE TABLE Lost

(

LostEntryDate DATE NOT NULL,

Lostid INT NOT NULL,

EmployeeID INT NOT NULL,

ParcelID INT NOT NULL,

PRIMARY KEY (Lostid, EmployeeID, ParcelID),

FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID),

FOREIGN KEY (ParcelID) REFERENCES Parcel(ParcelID)

);

CREATE TABLE Claim

(

ClaimID INT NOT NULL,

ClaimLodgedDate DATE NOT NULL,

ClaimSettledAmount INT NOT NULL,

ClaimPaidDate DATE NOT NULL,

EmployeeID INT NOT NULL,

ParcelID INT NOT NULL,

Lostid INT NOT NULL,

FOREIGN KEY (EmployeeID, ParcelID, Lostid) REFERENCES Lost(EmployeeID, ParcelID, Lostid)

);

Showing tables

Parcel Table

Employee Table

Parcel table

MYSQL Tutorials

• Create MySQL Database
• Create Table in MySQL
• Insert data to MySQL
• Mysql Queries
• Update data in MYSQL
• Delete data in MySQL
• Default Database
• Primary Key
• Foreign Keys
• Views
• Export MySQL Database
• Import data into Mysql
• Create Website with MySQL

MYSQL Sample Assignments

• COIT20247 Database Design and Development
• Relational schema and MySQL database
• Mysql Project
• ITICT107A Introduction to Databases
• MySql Assignment Question

MYSQL Sample Solutions

• Solution - COIT20247 Database Design
• Solution - Relational schema and MySQL
• Solution - Mysql Project

Testimonials

Very affordable projects!! And that to submit before deadlines. Thanks for helping me in my database project and raising my grades. I have been able to secure good marks in my internal assessment only because of you. Read More