CREATE TABLE CUST( customerNumber char(8) NOT NULL, lastName namesDT NOT NULL, f
ID: 3732583 • Letter: C
Question
CREATE TABLE CUST(
customerNumber char(8) NOT NULL,
lastName namesDT NOT NULL,
firstName namesDT NOT NULL,
streetNumber char(5) NOT NULL,
streetLine1 streetNamesDT NOT NULL,
streetLine2 streetNamesDT NULL,
city cityNamesDT NOT NULL,
state stateNamesDT NOT NULL,
zipCode zipCodesDT NULL,
driversLicense char(10) NULL,
dateofBirth smalldatetime NULL
);
CREATE TABLE DEALER(
dealerNumber char(4) NOT NULL,
dealerName varchar(30) NOT NULL,
streetNumber char(5) NOT NULL,
streetLine1 streetNamesDT NOT NULL,
streetLine2 streetNamesDT NULL,
city cityNamesDT NOT NULL,
state stateNamesDT NOT NULL,
zipCode zipCodesDT NULL,
openDate smalldatetime NOT NULL
);
CREATE TABLE VEHICLE(
vehicleCode char(7) NOT NULL,
Year smalldatetime NOT NULL,
Make varchar(15) NOT NULL,
Model varchar(15) NULL,
trimLine varchar(4) NULL,
dateModelIntroduced smalldatetime NOT NULL,
MSRP smallmoney NOT NULL
);
CREATE TABLE INVENTORY(
inventoryCode char(7) NOT NULL,
dealerNumber char(4) NOT NULL,
vinNumber char(17) NOT NULL,
newOrUsed char(1) NOT NULL,
dateArrivedAtDealership smalldatetime NOT NULL,
dateAvailableInStock smalldatetime NULL,
dateSold smalldatetime NULL,
listedPrice smallmoney NOT NULL,
exteriorColor varchar(10) NOT NULL,
interiorColor varchar(10) NOT NULL,
vehicleCode char(7) NOT NULL,
availableForSale bit NOT NULL
);
CREATE TABLE NEWVEH(
inventoryCode char(7) NOT NULL,
MSRP smallmoney NOT NULL,
invoicePrice smallmoney NOT NULL,
dateShipped smalldatetime NOT NULL
);
CREATE TABLE USEDVEH(
inventoryCode char(7) NOT NULL,
purchaseDate smalldatetime NOT NULL,
purchasePrice smallmoney NOT NULL,
Mileage int NOT NULL,
BlueBookValue smallmoney NOT NULL,
tradeInFlag bit NOT NULL
);
CREATE TABLE TESTDRIVE(
testDriveCode char(7) NOT NULL,
customerNumber char(8) NOT NULL,
dateOfTestDrive smalldatetime NOT NULL,
durationOfTestDrive decimal NULL,
inventoryCode char(7) NOT NULL,
);
CREATE TABLE SALES(
inventoryCode char(7) NOT NULL,
customerNumber char(8) NOT NULL,
salesPrice smallmoney NOT NULL,
salesDate smalldatetime NOT NULL,
taxAmount smallmoney NOT NULL,
paymentMethod char(1) NOT NULL
);
CREATE TABLE AVAILOPTIONS(
optionCode char(4) NOT NULL,
optionCost smallmoney NOT NULL,
optionMSRP smallmoney NULL,
optionDescription varchar(200) NULL
vehicleCode char(7) NOT NULL
);
CREATE TABLE INSTOPTIONS(
inventoryCode char(7) NOT NULL,
optionCode char(4) NOT NULL,
dealerInstalledFlag bit NOT NULL
);
CREATE TABLE CUSTVEH(
customerNumber char(8) NOT NULL,
vinNumber char(17) NOT NULL,
vehicleCode char(7) NOT NULL,
firstSeenDate smalldatetime NOT NULL
);
CREATE TABLE SERVREC(
serviceRecordNumber char(8) not null,
customerNumber char(8) not null,
vinNumber char(17) not null,
dealerNumber char(4) not null,
typeOfService char(3) not null,
dateBroughtIn smalldatetime not null,
Mileage int not null,
dateServiceCompleted smalldatetime null
dateBilled smalldatetime null,
totalServiceCost smallmoney null
);
CREATE TABLE ROUTSERV(
serviceRecordNumber char(8) not null,
serviceTypeCode char(3) not null,
listPrice smallmoney not null,
discount smallmoney not null
);
CREATE TABLE REPSERV(
serviceRecordNumber char(8) not null,
partsCost smallmoney not null,
laborHours decimal not null,
laborCost smallmoney not null
QUESTION:
TRANSACTIONS:
Write TRANSACTION examples with INSERT statement as follows:
A new record in inventory is added for new vehicle – insert must succeed for both INVENTORY and NEWVEH
tables, otherwise it should be rolled back
A new record in inventory is added for used vehicle – insert must succeed for both INVENTORY and USEDVEH
tables, otherwise it should be rolled back
A sale is recorded, record should be successfully inserted in SALES and [available for sale] should be updated in
INVENTORY. If either fails, the operation should be rolled back.
A service record is created for a repair service – record should be successfully inserted in SERVREC and
REPSERV both. If either fails, the operation should be rolled back.
A service record is created for a repair service – record should be successfully inserted in SERVREC and
ROUTSERV both. If either fails, the operation should be rolled back.
Explanation / Answer
Below are the transaction for each scenario. All the transaction consist of insertion of records into 2 tables, if either fails the transaction rollsback otherwise it commits.
a)
BEGIN TRAN
INSERT INTO INVENTORY VALUES ('IN-4', 'D-1', 'VIN-101', 'N', '2018-02-13', '2018-03-13',
'2018-03-19', 1200.00, 'Royal Blue', 'Black', 'VH10', 0);
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN 10
END
INSERT INTO NEWVEH VALUES ('IN-4', 1500.00, 1350.00, '2018-03-22');
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN 11
END
COMMIT TRAN
GO
b)
BEGIN TRAN
INSERT INTO INVENTORY VALUES ('IN-5', 'D-2', 'VIN-102', 'U', '2018-02-11', '2018-03-11',
'2018-03-15', 500.00, 'Red', 'Black', 'VH11', 0);
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN 10
END
INSERT INTO USEDVEH VALUES ('IN-5', '2018-03-11', 500.00, 20, 600.00 ,0);
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN 11
END
COMMIT TRAN
GO
c)
BEGIN TRAN
INSERT INTO SALES VALUES ('IN-6', 'CM-1', 1500.00, '2018-03-20', 100.00, 'C');
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN 10
END
INSERT INTO INVENTORY VALUES ('IN-6', 'D-2', 'VIN-104', 'N', '2018-02-11', '2018-03-11',
'2018-03-15', 500.00, 'Red', 'Black', 'VH11', 0);
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN 11
END
COMMIT TRAN
GO
d)
BEGIN TRAN
INSERT INTO SERVREC VALUES ('SR-101', 'CM-1', 'VIN-102', 'D-2', 'PER', '2018-03-15', 20,
'2018-03-18', '2018-03-19', 84.00);
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN 11
END
INSERT INTO REPSERV VALUES ('SR-101', 34.00, 5, 15.00);
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN 11
END
COMMIT TRAN
GO
e)
BEGIN TRAN
INSERT INTO SERVREC VALUES ('SR-102', 'CM-2', 'VIN-102', 'D-2', 'PER', '2018-03-15', 20,
'2018-03-18', '2018-03-19', 84.00);
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN 11
END
INSERT INTO ROUTSERV VALUES ('SR-102', 'PER', 23.00, 6.00);
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN 11
END
COMMIT TRAN
GO