Question

--Creating Package Table

DROP TABLE Package_Dimensions;

DROP TABLE Package_Statuses;

DROP TABLE PACKAGES;

DROP TABLE Status_Codes;

DROP TABLE Exception_Codes;

DROP TABLE Allowable_Limits;

DROP TABLE Limit_Codes;

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

--Creating Package Dimensions Table:

CREATE TABLE Package_Dimensions (Dimension_Id int Primary Key, Package_Id dec (5,2),Height dec (5,2),Width dec (5,2),Weight dec (5,2),Length dec (5,2));

--Creating Package Statuses Table

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

--Creating Status Codes and Exception Codes Table:

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

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

--Creating Allowable Limts and Limt Codes Table:

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

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

--Adding Foreign Keys to Tables By Referring Main Tables:

ALTER TABLE Package_Dimensions ADD FOREIGN KEY (Package_Id) REFERENCES Packages (Package_Id);

ALTER TABLE Package_Statuses ADD FOREIGN KEY (Package_Id) REFERENCES Packages (Package_Id);

ALTER TABLE Package_Statuses ADD FOREIGN KEY (Status_Code) REFERENCES Status_Codes (Status_Code);

ALTER TABLE Package_Statuses ADD FOREIGN KEY (Exception_Code) REFERENCES Exception_Codes (Exception_Code);

ALTER TABLE Allowable_Limits ADD FOREIGN KEY(Limit_Code) REFERENCES Limit_Codes (Limit_Code);

--Inserting Values Into Tables :

INSERT INTO Status_Codes VALUES (4,'The package Status is Active');

INSERT INTO Exception_Codes VALUES (6,'The package Has Some Errors');

INSERT INTO Limit_Codes VALUES (8,'This Limit Code has some Features');

INSERT INTO Allowable_Limits VALUES (1,8,to_date('2019/09/04','YYYY/MM/DD'),to_Date('2019/09/08','YYYY/MM/DD'),10);

INSERT INTO Packages values (100,1010);

INSERT INTO Packages values (101,1001);

INSERT INTO Package_Dimensions VALUES (6,100,10,20,800,40);

INSERT INTO Package_Statuses VALUES (4,101,100,4,to_Date('2019/09/04','YYYY/MM/DD'),6);

--Displaying Data In the Tables:

SELECT * FROM Packages;

SELECT * FROM Allowable_Limits;

1. Insert at least five rows in each table
2. Alter at least one table to include a constraint on a column (e.g. BETWEEN 1 and 12, IN ('GA', 'SC', "TN'))
3. Run at least two queries to demonstrate that the constraint was defined properly.
4. Run updates on at least two tables
5. Use at least two queries to demonstrate that referential integrity is set up correctly for your tables.

Written in Oracle SQL

Answer 1

Inserting

--Insert at least five rows in each table

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');

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');

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');

INSERT INTO Allowable_Limits VALUES (2,1,to_date('2019/09/04','YYYY/MM/DD'),to_Date('2019/09/08','YYYY/MM/DD'),10);
INSERT INTO Allowable_Limits VALUES (3,2,to_date('2019/07/03','YYYY/MM/DD'),to_Date('2019/07/12','YYYY/MM/DD'),8);
INSERT INTO Allowable_Limits VALUES (4,3,to_date('2019/01/02','YYYY/MM/DD'),to_Date('2019/01/09','YYYY/MM/DD'),7);
INSERT INTO Allowable_Limits VALUES (5,4,to_date('2019/09/08','YYYY/MM/DD'),to_Date('2019/09/09','YYYY/MM/DD'),4);
INSERT INTO Allowable_Limits VALUES (6,5,to_date('2019/09/09','YYYY/MM/DD'),to_Date('2019/09/11','YYYY/MM/DD'),3);

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);

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);

INSERT INTO Package_Statuses VALUES (1,101,100,4,to_Date('2019/09/04','YYYY/MM/DD'),3);
INSERT INTO Package_Statuses VALUES (4,104,100,3,to_Date('2019/01/04','YYYY/MM/DD'),6);
INSERT INTO Package_Statuses VALUES (6,107,100,6,to_Date('2019/09/04','YYYY/MM/DD'),2);

Query:

alter table Status_Codes modify(Status_Code int check(Status_Code >= 1 and Status_Code<12));

Query:

/*Here i am insert wrong entery for validating that constant are properly working */
INSERT INTO Status_Codes VALUES (14,'The package Status is Passive');/*in this status code is wrong in Check constant*/
INSERT INTO Exception_Codes VALUES (5,'The package Has def Errors');/*this is already inserted */

/* Here update Packages Number where package id is 107*/
Select * from Packages;
Update Packages set Package_Number=1019 where Package_Id=107;
Select * from Packages;

Query:
INSERT INTO Package_Statuses VALUES (11,101,100,10,to_Date('2019/09/04','YYYY/MM/DD'),6);/*In this Status_Code is wrong in foreign key */
INSERT INTO Allowable_Limits VALUES (6,8,to_date('2019/09/09','YYYY/MM/DD'),to_Date('2019/09/11','YYYY/MM/DD'),3);/*in this Limit_Codes is not inserted in Limit_Codes table so referenceial work properly */

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.........