Considering that you developed the following database design from last project a

Question

Considering that you developed the following database design from last project assignment:

1. Write the proper SQL Code to create all tables shown in the design above.

2. Remember to include the Null/NOTNULL, Primary Key, and Foreign Key constraints, and use the proper data type with the proper size.

3. Take a snap shot of Oracle showing each table been created successfully

4. Write the proper code to insert at least three lines in each table.

5. Create a view that displays supplier name from the Supplier table and Product Name from the product table.

Explanation / Answer

1)

CREATE TABLE (
`lngSupplierID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
strSupplierName varchar(20) not null,
strSupplierAddressStreet varchar(20) not null,
strSupplierAddressCity varchar(20) not null,
strSupplierAddressCountry varchar(20) not null,
strSupplierAddressPostCode varchar(10) not null,
strSupplierPhoneNo varchar(12) not null,
strSupplierFaxNo varchar(12) null,
strPaymentTerms varchar(10) null
);

CREATE TABLE tblProducts (
`lngProductID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
strProductName varchar(20) not null,
chkInStock boolean not null,
longUnitsInStock long not null,
curProductUnitPurchasePrice long not null,
curProductUnitSalePrice long not null,
lngSupplierID INT not null FOREIGN KEY
);

CREATE TABLE tblLink_OrderProduct (
`lngOrderID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
lngProductId INT NOT NULL FOREIGN KEY,
intProductQuantity INT NOT NULL,
curTotalProductSaleCost LONG NOT NULL,
dteArrangeDeliveryDate DATETIME NOT NULL,
tmeArrangeDeliveryTime TIME NOT NULL,
chkProductDelivered BOOLEAN NOT NULL,
dteActualDeliveryDate DATETIME NULL,
tmeActualDeliveryTime TIME NULL
);

CREATE TABLE tblOrders(
`lngOrderID` INT NOT NULL FOREIGN KEY,
lngCustomerId INT NOT NULL FOREIGN KEY,
dteDateOrderPlaced DATETIME NOT NULL,
tmeTimeOrderPlaced TIME NOT NULL,
lngOrderTotalProductNo LONG NOT NULL,
chkOrderCompleted BOOLEAN NOT NULL,
dteDateOrderCompleted DATETIME NULL,
memAnyAdditionalInfo varchar(20) NULL
);

CREATE TABLE tblCustomers(
lngCustomerId INT NOT NULL PRIMARY KEY,
strCustomerFirstName varchar(10) NOT NULL,
strCustomerLastName varchar(10) NOT NULL,
strCustomerAddressStreet varchar(10) NOT NULL,
strCustomerAddressCity varchar(10) NOT NULL,
strCustomerAddressCountry varchar(10) NOT NULL,
strCustomerAddressPostCode varchar(10) NOT NULL,
strCustomerContactPhoneNo varchar(10) NOT NULL,
);

4)

INSERT INTO tblSuppliers VALUES ('SUPP1','SUPPST1','SUPPCITY1','SUPPCOUNTRY1','123456','1234567890','','')
INSERT INTO tblSuppliers VALUES ('SUPP2','SUPPST2','SUPPCITY2','SUPPCOUNTRY2','123456','1234567890','','')
INSERT INTO tblSuppliers VALUES ('SUPP1','SUPPST3','SUPPCITY3','SUPPCOUNTRY3','123456','1234567890','','')

INSERT INTO tblProducts VALUES ('PROD1',TRUE,3,10.10,20.20,1)
INSERT INTO tblProducts VALUES ('PROD2',FALSE,4,20.20,30.30,2)
INSERT INTO tblProducts VALUES ('PROD3',TRUE,2,30.30,40.40,3)

INSERT INTO tblLink_OrderProduct VALUES (1,1,10.10,'01/01/2015','20:20',FALSE,'','')
INSERT INTO tblLink_OrderProduct VALUES (1,1,10.10,'01/01/2015','20:20',FALSE,'','')
INSERT INTO tblLink_OrderProduct VALUES (3,1,1,10.10,'01/01/2015','20:20',FALSE,'','')

INSET INTO tblOrders VALUES (1,1,'01/01/2015','20:20',1,FALSE,'','')
INSET INTO tblOrders VALUES (2,2,'01/01/2015','20:20',1,FALSE,'','')
INSET INTO tblOrders VALUES (3,3,'01/01/2015','20:20',1,FALSE,'','')

INSERT INTO tblCustomers VAULES ('CUSTFN1','CUSTLN1','CUSTST1','CUSTCT1','CUSTCNT1','123456','1234567890')
INSERT INTO tblCustomers VAULES ('CUSTFN2','CUSTLN2','CUSTST2','CUSTCT2','CUSTCNT2','123456','1234567890')
INSERT INTO tblCustomers VAULES ('CUSTFN3','CUSTLN3','CUSTST3','CUSTCT3','CUSTCNT3','123456','1234567890')

5) SELECT s.strSupplierName as SupplierName, p.strProductName as ProductName FROM tblSuppliers s, tblProducts p where s.lngSupplierID=p.lngSuplierId;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.