Question

Part 2: You will create five tables in your ColonialAdventureTours database. Please do not write your own SQL Commands for this task, use data found in the following Colonial_create.txt file and copy and paste the commands into MySQL workbench. Then add Primary key, Foreign key, and not null constraints appropriately. Then run your codes. Note: Remember that since you enforced referential integrity (foreign key constraints) that you must create the "primary" tables before you can create the "related" tables in the relationship. [Create tables in right orders].

Answer 1

   CREATE TABLE Packages (Package_Id int PRIMARY KEY,Package_Number int);

   CREATE TABLE Package_Dimensions (Dimension_Id int Primary Key, Package_Id int REFERENCES Packages (Package_Id),Height int,Width int,Weight int,Length int);

   CREATE TABLE Status_Codes ( Status_Code int Primary Key,Description varchar(100));

   CREATE TABLE Exception_Codes (Exception_Code int Primary Key,Description varchar(100));

   CREATE TABLE Package_Statuses (Package_Status_Id int Primary Key, Status_Id int REFERENCES Status_Codes (Status_Code), Package_Id int REFERENCES Packages (Package_Id),Status_Code int, Status_Date date, Exception_Code int REFERENCES Exception_Codes (Exception_Code));

   CREATE TABLE Limit_Codes (Limit_Code int Primary Key,Description varchar(100));

   CREATE TABLE Allowable_Limits (Limit_Id int Primary Key, Limit_Code int REFERENCES Limit_Codes (Limit_Code), Date_Active date, Date_Inactive date, Limit_Value int);

Inserting Data

INSERT INTO Packages values (103,1011);
INSERT INTO Packages values (104,1010);
INSERT INTO Packages values (105,1010);
INSERT INTO Packages values (106,1010);
INSERT INTO Packages values (107,1012);
Select * from Packages;

INSERT INTO Package_Dimensions VALUES (6,103,10,20,800,40);
INSERT INTO Package_Dimensions VALUES (4,104,10,20,800,40);
INSERT INTO Package_Dimensions VALUES (2,105,10,20,800,40);
INSERT INTO Package_Dimensions VALUES (1,106,10,20,800,40);
INSERT INTO Package_Dimensions VALUES (3,107,10,20,800,40);
Select * from Package_Dimensions;

INSERT INTO Status_Codes VALUES (1,'The package Status is Passive');
INSERT INTO Status_Codes VALUES (2,'The package Status is Hold Back');
INSERT INTO Status_Codes VALUES (3,'The package Status is Not Done');
INSERT INTO Status_Codes VALUES (5,'The package Status is Done');
INSERT INTO Status_Codes VALUES (6,'The package Status is Delay');
Select * from Status_Codes;

INSERT INTO Exception_Codes VALUES (1,'The package Has Seat Errors');
INSERT INTO Exception_Codes VALUES (2,'The package Has Condition Errors');
INSERT INTO Exception_Codes VALUES (3,'The package Has Connection Errors');
INSERT INTO Exception_Codes VALUES (4,'The package Has abc Errors');
INSERT INTO Exception_Codes VALUES (5,'The package Has def Errors');
Select * from Exception_Codes;

INSERT INTO Limit_Codes VALUES (1,'This Limit Code has abc Features');
INSERT INTO Limit_Codes VALUES (2,'This Limit Code has def Features');
INSERT INTO Limit_Codes VALUES (3,'This Limit Code has ghi Features');
INSERT INTO Limit_Codes VALUES (4,'This Limit Code has jkl Features');
INSERT INTO Limit_Codes VALUES (5,'This Limit Code has mno Features');
Select * from Limit_Codes;

INSERT INTO Package_Statuses VALUES (1,101,100,4,'2019/09/04',3);
INSERT INTO Package_Statuses VALUES (4,104,100,3,'2019/01/04',6);
INSERT INTO Package_Statuses VALUES (6,107,100,6,'2019/09/04',2);
Select * from Package_Statuses;

;

INSERT INTO Allowable_Limits VALUES (2,1,'2019/09/04','2019/09/08',10);
INSERT INTO Allowable_Limits VALUES (3,2,'2019/07/03','2019/07/12',8);
INSERT INTO Allowable_Limits VALUES (4,3,'2019/01/02','2019/01/09',7);
INSERT INTO Allowable_Limits VALUES (5,4,'2019/09/08','2019/09/09',4);
INSERT INTO Allowable_Limits VALUES (6,5,'2019/09/09','2019/09/11',3);
Select * from Allowable_Limits;

if you still have any Problem regarding this question please comment and if you like my code please appreciate me by thumbs up thank you.........