Databases Code SQL statements for the following: CUSTOMER(CustomerID, FirstName,

Question

Databases

Code SQL statements for the following:

CUSTOMER(CustomerID, FirstName, LastName, Street, City, State, Zip, Phone)

RENTAL(RentalID, RentalDate,NumberOfDays, RentalAmount, Deposit, Tax, TotalAmount, CustomerID, ReturnDate, AmountReturned)

LINEITEM(RentalID, CostumeID, Rate, Deposit)

COSTUMETYPE(TypeID, Description, Photo, DailyRentalRate, DepositRate, ReplacementCost)

COSTUME(CostumeID, Size, Availability, DatePurchased, CostumeType)

Using the tables, write the SQL code to represent each of the following transactions. Use Start Transaction and Commit to group the SQL statements into logical transactions.

A. Show the complete transaction for adding two new “star wars: Ewok” costumes on October 30, 2017 to your database, one size large, and one size small. Both of these costumes are now available for rental. Be sure to add the costumetype as part of this transaction.

B. Show the complete transaction for one of your customers placing a rental on October 31, 2017 for the two new costumes added in A.

C. Create a simple transaction log to represent the actions of the two previous transactions

Explanation / Answer

SQL statements for the following:

1.   CUSTOMER(CustomerID, FirstName, LastName, Street, City, State, Zip, Phone)

CREATE TABLE CUSTOMER (

CustomerID     INT      PRIMARY KEY,

FirstName      VARCHAR(15),

LastName      VARCHAR(15),

Street           CHAR(30),

City               CHAR(25),

State             CHAR(25),

Zip               INTEGER,

Phone          INTEGER   );

CREATE TABLE RENTAL(

RentalID     INT        PRIMARY KEY,

RentalDate    DATE,                                    // DATE is a datatype in SQL

NumberOfDays   INT,

RentalAmount      INT,

Deposit                 INT,

Tax                       INT,

TotalAmount         INT,

CustomerID         INT,

ReturnDate           DATE,

AmountReturned    INT,

FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID)

);

CREATE TABLE LINEITEM (

RentalID      INT   FOREIGN KEY REFERENCES RENTAL(RentalID),

CostumeID    INT      PRIMARY KEY,

Rate               INT,

Deposit       INT    );

CREATE TABLE COSTUMETYPE (

TypeID      INT      PRIMARY KEY,

Description        VARCHAR(30),

Photo             VarBinary(MAX),

DailyRentalRate          INT,

DepositRate               INT,

ReplacementCost         INT

)’

CREATE TABLE COSTUME (

CostumeID        INT    PRIMARY KEY,

Size                   INT,

Availability         VARCHAR(10),

DatePurchased      DATE,

CostumeType         VARCHAR(20) );

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

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