Assume each of your 5 customers rents 2 movies each. Create 5 different transact

ID: 3822852 • Letter: A

Question

Assume each of your 5 customers rents 2 movies each. Create 5 different transactions that will record each customer renting 2 different movies. Use commit on all instances. Do use Rollback on one of the transactions and see what happens.

Okay, so these are my tables:

CREATE TABLE customer
(
cust_id INT (6) UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(20) NOT NULL,
address_1 VARCHAR(20) NOT NULL,
address_2 VARCHAR(20) NOT NULL,
cc_number INT(16) NOT NULL,
cc_expdate DATE NOT NULL,
member_since DATE NOT NULL,
member_exp DATE NOT NULL,
PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

CREATE TABLE movie
(
movie_id INT (6) UNSIGNED NOT NULL,
title varchar (20) NOT NULL,
release_date DATE NOT NULL,
rating varchar (20) NOT NULL,
qty Int (6) not null,
cust_id INT (6) UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (movie_id),
FOREIGN KEY (cust_id) REFERENCES Customer (cust_id)
) ENGINE=InnoDB;

CREATE TABLE transaction
(
trans_ID INT (6) UNSIGNED NOT NULL AUTO_INCREMENT,
rental_date DATE NOT NULL,
return_date DATE NOT NULL,
rental_fee INT NOT NULL,
cust_id INT (6) UNSIGNED NOT NULL,
movie_id INT (6) UNSIGNED NOT NULL,
PRIMARY KEY (trans_ID),
FOREIGN KEY (cust_id) REFERENCES Customer (cust_id),
FOREIGN KEY (movie_id) REFERENCES movie (movie_id)
) ENGINE=InnoDB;

and these are my values:

'CUSTOMER' VALUES
INSERT INTO `Customer` VALUES ('145888','David','Holder','435 Main Street','Houston, TX 77236','8325','2018-02-09','1995-10-09','2018-06-05');
INSERT INTO `Customer` VALUES ('236459','Morgan','Freeman','4887 Old Town Road','Nashoba, OK 45484','1258','1998-12-30','2017-06-07','2019-08-19');
INSERT INTO `Customer` VALUES ('578984','Sheryl','Moore','7654 Pine St.','Spring, TX 45678','6958','2020-03-28','2015-12-18','2030-09-14');
INSERT INTO `Customer` VALUES ('458796','Tiffany','Temple','786 Oak Rd.','Humble, TX 45785','9545','2016-04-24','2013-06-19','2021-04-15');
INSERT INTO `Customer` VALUES ('963584','Adrian','Garza','238 Maple Ct.','Dallas, TX 45818','8745','2019-05-08','1945-05-10','2019-07-04');

'MOVIE' VALUES
INSERT INTO   `movie` VALUES ('000001','Friday the 13th','1980-05-09','R','10','145888');
INSERT INTO   `movie` VALUES ('000002','Urban Cowboy','1980-06-06','R','9','236459');
INSERT INTO `movie` VALUES ('000003','Airplane!','1980-07-04','R','11','578984');
INSERT INTO   `movie` VALUES ('000004','Caddyshack','1980-07-25','R','8','458796');
INSERT INTO   `movie` VALUES ('000005','Prom Night','1980-08-15','R','13','963584');

How do I go about creating transactions? What's the code that would match my values? It doesn't matter who rents what, just that they rent 2. Also, should the transaction table have "cust_ID" and "movie_ID" as foreign keys (as I have them) or are they supposed to be primary keys - the instructions don't say to add a "transaction_ID"? This is all new to me!

Explanation / Answer

Hi,

You can consider adding a movie_type column in movie table. A movie type may have 2 different movies(Example- Movie_type='comedy' may have 2 movies 'abc' and 'def' ). In the transaction table you can insert transactions using movie_type and not with movie _id. The solutions looks weird but I can see only this solution to the requirement.

So the new tables should look like this-

movie_type_id varchar(20),

PRIMARY KEY (movie_id),
FOREIGN KEY (cust_id) REFERENCES Customer (cust_id),

foreign_key (movie_type_id)references Movie_type(movie_type_id)
) ENGINE=InnoDB;

CREATE TABLE transaction
(
trans_ID INT (6) UNSIGNED NOT NULL AUTO_INCREMENT,
rental_date DATE NOT NULL,
return_date DATE NOT NULL,
rental_fee INT NOT NULL,
cust_id INT (6) UNSIGNED NOT NULL,
movie_type_id INT (6) UNSIGNED NOT NULL,
PRIMARY KEY (trans_ID),
FOREIGN KEY (cust_id) REFERENCES Customer (cust_id),
foreign_key (movie_type_id)references Movie_type(movie_type_id)
) ENGINE=InnoDB;

CREATE TABLE MOVIE_TYPE(MOVIE_TYPE_ID varchar(10),

MOVIE_TYPE_VAL varchar(20));

Hence, if you insert a record in transaction table with movie type(assuming each movie type has 2 movies associated to it), we can see that in 5 different transactions that will record each customer renting 2 different movies.

Insert for Moviee type table-

insert into movie_type('101','HORROR');

insert into movie_type('102','COMEDY');

insert into movie_type('103','DRAMA');

insert into movie_type('104','OLD');

insert into movie_type('105','NEW');

Insert statements for Transaction table-

insert into transaction values('1000001','2018-02-09','1995-10-09',45000,'145888','101');

insert into transaction values('1000002','2018-02-09','1995-10-09',45000,'963584','102');

insert into transaction values('1000003','2018-02-09','1995-10-09',45000,'236459','103');

insert into transaction values('1000004','2018-02-09','1995-10-09',45000,'578984','104');

insert into transaction values('1000005','2018-02-09','1995-10-09',45000,'458796','105');

Please try this out and let me know if any challenges.

Regards,

Vinay Prakash Singh

Navigate

Assume each of the following numbers in scientific notation and write the number

Assume each sample is taken from a normally distributed population and construct

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

Assume each of your 5 customers rents 2 movies each. Create 5 different transact

Question

Explanation / Answer

Related Questions

Navigate