CS database – cqu.edu.au

   
TASK 01 (a) RELATIONAL SCHEMA
Customer (CustomerID, CustomerName, CustomerAddress, CustomerContactNum)
Parcel (ParcelID, ParcelWeightInGrams, FromAddress, ToAddress, DateCollected, ParselValue, Charges, Status)
Delivered (DeliverdDate)
Returned (ReturnedDate)
Lost (LostEntryDate)
Invoice (InvoiceID,InvoiceDate, InvoiceAmount)
Claim (ClaimID, ClaimLodgedDate, ClaimSettledAmount, ClaimPaidDate)
Employee (EmployeeID, EmployeeName, EmployeeStartDate)
On the basis of relationship that exist between the entities, the table can be further broken down as;
Since one customer can order for multiple parcels therefore, 1 to many relationship exists between them.
· Parcel (ParcelID, ParcelWeightInGrams, FromAddress, ToAddress, DateCollected, ParselValue, Charges, Status,CustomerID)  Foreign Key:CustomerID
Since there is 1 to1 relationship between Parcel and Delivered, Parcel and Returned, Parcel and Lost.
· Delivered (ParcelID,DeliverdDate)   Primary Key: ParcelID
· Returned (ParcelID,ReturnedDate)  Primary Key: ParcelID
· Lost (ParcelID,LostEntryDate) Primary Key: ParcelID
Since 1 to 1 relation exists between Parcel and Claim.
· Claim (ClaimID, ClaimLodgedDate, ClaimSettledAmount, ClaimPaidDate,ParcelID) 
Foreign Key:parcelID
Since 1 to 1 relation exists between LostParcel and Claim.
· Lost (LostId,LostEntryDate, ParcelID) 
Primary Key: LostID
Foreign Key: ParcelID
Since there is 1 to 1 relationship between Parcel and Invoice 
1. Invoice (InvoiceID,InvoiceDate, InvoiceAmount, ParcelID)
Foreign Key:parcelID
Since there is 1 employee can be responsible for multiple Parcel. Therefore,
2. Parcel (ParcelID, ParcelWeightInGrams, FromAddress, ToAddress, DateCollected, ParselValue, Charges, Status,CustomerID, EmployeeID)
Foreign Key:CustomerID,EmployeeID
Since 1 to many relationship exists between Employee and 
· Delivered (DeliveredID, ParcelID,DeliverdDate, EmployeeID)  Primary Key: DeliveredID
· Returned (ReturnedID, ParcelID,ReturnedDate, EmployeeID) Primary Key: ReturnedID
· Lost (LostId,ParcelID,LostEntryDate,EmployeeID)  Primary Key: LostId
Foreign key:EmployeeID,ParcelID

TASK 01 (b) NORMALIZATION
1. Parcel (ParcelID, ParcelWeightInGrams, FromAddress, ToAddress, DateCollected, ParselValue, Charges, Status,CustomerID, EmployeeID)
In this one customer can order multiple parcels, therefore customerID is not enough to uniquely identify a tuple. There is no partial key dependency. The primary key is the only key that identifies the tuple, therefore, this table is 2NF.
For 3NF, there must be a transitive dependency, but in this table here is no attribute that can further identify a tuple uniquely, therefor it is already in 3NF
2. Delivered (DeliveredID, ParcelID,DeliverdDate, EmployeeID) 
In this table, there is no partial key dependency, therefore it is already in 2NF.
Every attribute is fully dependent on DeliveredID, there is no transitive dependency among the attributes because no one attribute can uniquely identify any other attribute. Therefore, we say it is already in 3NF

Don't use plagiarized sources. Get Your Custom Essay on
CS database – cqu.edu.au
Just from $13/Page
Order Essay

TASK 02 INTEGRITY CONSTRAINTS 
  
Attribute

Data   type

Integrity   constraint implemented

Error   message
 
DeliveredID

Autonumber

Primary Key

NULL
 
DeliverdDate

Date/Time

 
EmployeeID

Number

Foreign Key -Indexed

 
ParcelID

Number

Foreign Key -Indexed

  
Attribute

Data   type

Integrity   constraint implemented

Error   message
 
ParcelID

Autonumber

Primary Key

 
ParcelWeightInGrams

Number

Decimal upto 2 places

 
FromAddress

LongText

 
ToAddress

LongText

 
DateCollected

Date/Time

 
Status

Shortext

In LOOKUP,
Display Control=List Box
Row source Type= Value Type
RowSource = “Collected”;”Transit”;”Delivered”;”Returned”;”Lost”

 
EmployeeID,CustomerID

Number

 
Charges

Number

TASK 03IMPLEMENTATION REPORT
1. Any two interesting things/procedures you have learnt by doing this assignment.
How to add dropdown menu in status was one interesting thing to learn about. 
While creating relationship, how to link the primary and foreign key with each other.
2. Any one of the assignment tasks that was relatively difficult / complex to solve but has been successfully completed by you.
Running queries were the most difficult work.

— phpMyAdmin SQL Dump
— version 3.3.9
— http://www.phpmyadmin.net

— Host: localhost
— Generation Time: May 24, 2019 at 11:53 AM
— Server version: 5.5.8
— PHP Version: 5.3.5

SET SQL_MODE=”NO_AUTO_VALUE_ON_ZERO”;

/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;


— Database: `db2`

— ——————————————————–


— Table structure for table `claim`

CREATE TABLE IF NOT EXISTS `claim` (
  `ClaimID` int(11) NOT NULL AUTO_INCREMENT,
  `ClaimLodgedDate` datetime NOT NULL,
  `ClaimSettledAmount` int(11) NOT NULL,
  `ClaimPaidDate` date NOT NULL,
  `ParcelID` int(11) NOT NULL,
  PRIMARY KEY (`ClaimID`),
  KEY `ParcelID` (`ParcelID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;


— Dumping data for table `claim`

INSERT INTO `claim` (`ClaimID`, `ClaimLodgedDate`, `ClaimSettledAmount`, `ClaimPaidDate`, `ParcelID`) VALUES
(1, ‘2018-09-05 00:00:00’, 1500, ‘2018-09-05’, 5);

— ——————————————————–


— Table structure for table `customer`

CREATE TABLE IF NOT EXISTS `customer` (
  `CustomerID` int(11) NOT NULL AUTO_INCREMENT,
  `CustomerName` varchar(255) NOT NULL,
  `CustomerAddress` varchar(255) NOT NULL,
  `CustomerContactNum` int(255) NOT NULL,
  PRIMARY KEY (`CustomerID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;


— Dumping data for table `customer`

INSERT INTO `customer` (`CustomerID`, `CustomerName`, `CustomerAddress`, `CustomerContactNum`) VALUES
(1, ‘Patrick A Evans’, ‘3146  Hood Avenue, San Diego, 92123’, 619),
(2, ‘Russell T Foret’, ‘1855  Colony Street,Stamford, 06901’, 203),
(4, ‘Byron J Christianson’, ‘4412  Hide A Way Road, San Jose, 95118’, 2147483647),
(5, ‘Stanley D Alfonso’, ‘1234  West Virginia Avenue, Colonie, 12205’, 51833438),
(6, ‘Ned S Clifford’, ‘3321  Eagles Nest Drive, Woodland,95695’, 53090847),
(7, ‘Nelson B Kelley’, ‘2924  Stanton Hollow Road, Randolph,02368’, 78130881),
(8, ‘Lawrence M Jaffe’, ‘2426  John Calvin Drive, CULEBRA,00775’, 78745096);

— ——————————————————–


— Table structure for table `delivered`

CREATE TABLE IF NOT EXISTS `delivered` (
  `DeliveredID` int(11) NOT NULL AUTO_INCREMENT,
  `ParcelID` int(11) NOT NULL,
  `DeliveredDate` date NOT NULL,
  `EmployeeID` int(11) NOT NULL,
  PRIMARY KEY (`DeliveredID`),
  KEY `ParcelID` (`ParcelID`),
  KEY `EmployeeID` (`EmployeeID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;


— Dumping data for table `delivered`

INSERT INTO `delivered` (`DeliveredID`, `ParcelID`, `DeliveredDate`, `EmployeeID`) VALUES
(1, 8, ‘2019-05-22’, 4);

— ——————————————————–


— Table structure for table `employee`

CREATE TABLE IF NOT EXISTS `employee` (
  `EmployeeID` int(11) NOT NULL AUTO_INCREMENT,
  `EmployeeName` varchar(255) NOT NULL,
  `EmployeeStartDate` date NOT NULL,
  PRIMARY KEY (`EmployeeID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;


— Dumping data for table `employee`

INSERT INTO `employee` (`EmployeeID`, `EmployeeName`, `EmployeeStartDate`) VALUES
(1, ‘Tommy M Horton’, ‘2015-02-24’),
(2, ‘Howard V Giddings’, ‘2015-09-25’),
(3, ‘Vincent L Figueroa’, ‘2016-02-03’),
(4, ‘Nathan M Ross’, ‘2015-08-26’),
(5, ‘Jeff C Mixson’, ‘2015-03-14’),
(6, ‘Robert G Cornejo’, ‘2016-02-26’);

— ——————————————————–


— Table structure for table `invoice`

CREATE TABLE IF NOT EXISTS `invoice` (
  `InvoiceID` int(11) NOT NULL AUTO_INCREMENT,
  `ParcelID` int(11) NOT NULL,
  `InvoiceDate` date NOT NULL,
  `InvoiceAmount` int(11) NOT NULL,
  PRIMARY KEY (`InvoiceID`),
  KEY `ParcelID` (`ParcelID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;


— Dumping data for table `invoice`

INSERT INTO `invoice` (`InvoiceID`, `ParcelID`, `InvoiceDate`, `InvoiceAmount`) VALUES
(1, 4, ‘2017-08-11’, 1500),
(2, 5, ‘2018-12-07’, 2000),
(3, 6, ‘2019-05-30’, 2550),
(4, 7, ‘2018-05-08’, 2500),
(5, 3, ‘2018-02-02’, 1000),
(6, 8, ‘2019-05-22’, 1500);

— ——————————————————–


— Table structure for table `lost`

CREATE TABLE IF NOT EXISTS `lost` (
  `LostID` int(11) NOT NULL AUTO_INCREMENT,
  `ParcelID` int(11) NOT NULL,
  `LostEntryDate` date NOT NULL,
  `EmployeeID` int(11) NOT NULL,
  PRIMARY KEY (`LostID`),
  UNIQUE KEY `ParcelID` (`ParcelID`),
  KEY `EmployeeID` (`EmployeeID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;


— Dumping data for table `lost`

INSERT INTO `lost` (`LostID`, `ParcelID`, `LostEntryDate`, `EmployeeID`) VALUES
(1, 5, ‘2018-09-05’, 5);

— ——————————————————–


— Table structure for table `parcel`

CREATE TABLE IF NOT EXISTS `parcel` (
  `ParcelID` int(11) NOT NULL AUTO_INCREMENT,
  `ParcelWeightInGrams` decimal(11,2) NOT NULL,
  `FromAddress` varchar(2500) NOT NULL,
  `ToAddress` varchar(2500) NOT NULL,
  `DateCollected` date NOT NULL,
  `ParselValue` int(11) NOT NULL,
  `Charges` int(11) NOT NULL,
  `Status` enum(‘Collected’,’Transit’,’Delivered’,’Returned’,’Lost’) NOT NULL,
  `CustomerID` int(11) NOT NULL,
  `EmployeeID` int(11) NOT NULL,
  PRIMARY KEY (`ParcelID`),
  KEY `CustomerID` (`CustomerID`),
  KEY `EmployeeID` (`EmployeeID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;


— Dumping data for table `parcel`

INSERT INTO `parcel` (`ParcelID`, `ParcelWeightInGrams`, `FromAddress`, `ToAddress`, `DateCollected`, `ParselValue`, `Charges`, `Status`, `CustomerID`, `EmployeeID`) VALUES
(3, ‘200.00’, ‘2376  North Bend River Road, Somerset, 42501’, ‘4748  Bubby Drive,MORGANTOWN, 26501’, ‘2018-02-02’, 220, 1000, ‘Collected’, 4, 3),
(4, ‘352.00’, ‘3469  Roosevelt Road,Dodge City,67801’, ‘4269  Short Street,Austin,78723’, ‘2017-08-11’, 356, 1500, ‘Returned’, 6, 4),
(5, ‘198.00’, ‘111  Lawman Avenue,Alexandria,22301’, ‘2830  Hide A Way Road,Santa Clara,95050’, ‘2018-12-07’, 469, 2000, ‘Lost’, 2, 6),
(6, ‘146.00’, ’34  Sussex Court,Waco,76706′, ‘1855  Hillcrest Circle,Crystal,55429’, ‘2019-05-30’, 789, 2550, ‘Transit’, 7, 5),
(7, ‘436.00’, ‘1049  Atha Drive,Bakersfield,93311’, ‘1057  Froe Street,TALMO,30575’, ‘2018-05-08’, 965, 2500, ‘Collected’, 1, 2),
(8, ‘716.00’, ‘1057  Froe Street,TALMO,30575’, ‘1049  Atha Drive,Bakersfield,93311’, ‘2019-05-22’, 369, 1500, ‘Delivered’, 5, 5);

— ——————————————————–


— Table structure for table `returned`

CREATE TABLE IF NOT EXISTS `returned` (
  `ReturnedID` int(11) NOT NULL AUTO_INCREMENT,
  `ParcelID` int(11) NOT NULL,
  `ReturnedDate` date NOT NULL,
  `EmployeeID` int(11) NOT NULL,
  PRIMARY KEY (`ReturnedID`),
  KEY `ParcelID` (`ParcelID`),
  KEY `EmployeeID` (`EmployeeID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;


— Dumping data for table `returned`

INSERT INTO `returned` (`ReturnedID`, `ParcelID`, `ReturnedDate`, `EmployeeID`) VALUES
(1, 4, ‘2017-11-09’, 4);


— Constraints for dumped tables


— Constraints for table `claim`

ALTER TABLE `claim`
  ADD CONSTRAINT `claim_ibfk_1` FOREIGN KEY (`ParcelID`) REFERENCES `parcel` (`ParcelID`) ON DELETE CASCADE ON UPDATE CASCADE;


— Constraints for table `delivered`

ALTER TABLE `delivered`
  ADD CONSTRAINT `delivered_ibfk_1` FOREIGN KEY (`ParcelID`) REFERENCES `parcel` (`ParcelID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `delivered_ibfk_2` FOREIGN KEY (`EmployeeID`) REFERENCES `employee` (`EmployeeID`) ON DELETE CASCADE ON UPDATE CASCADE;


— Constraints for table `invoice`

ALTER TABLE `invoice`
  ADD CONSTRAINT `invoice_ibfk_1` FOREIGN KEY (`ParcelID`) REFERENCES `parcel` (`ParcelID`) ON DELETE CASCADE ON UPDATE CASCADE;


— Constraints for table `lost`

ALTER TABLE `lost`
  ADD CONSTRAINT `lost_ibfk_1` FOREIGN KEY (`ParcelID`) REFERENCES `parcel` (`ParcelID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `lost_ibfk_2` FOREIGN KEY (`EmployeeID`) REFERENCES `employee` (`EmployeeID`) ON DELETE CASCADE ON UPDATE CASCADE;


— Constraints for table `parcel`

ALTER TABLE `parcel`
  ADD CONSTRAINT `parcel_ibfk_1` FOREIGN KEY (`CustomerID`) REFERENCES `customer` (`CustomerID`),
  ADD CONSTRAINT `parcel_ibfk_2` FOREIGN KEY (`EmployeeID`) REFERENCES `employee` (`EmployeeID`);


— Constraints for table `returned`

ALTER TABLE `returned`
  ADD CONSTRAINT `returned_ibfk_1` FOREIGN KEY (`ParcelID`) REFERENCES `parcel` (`ParcelID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `returned_ibfk_2` FOREIGN KEY (`EmployeeID`) REFERENCES `employee` (`EmployeeID`) ON DELETE CASCADE ON UPDATE CASCADE;

Place your order
(550 words)

Approximate price: $22

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more
Live Chat+1(978) 822-0999EmailWhatsApp