top of page
Search
realcode4you
Nov 21, 20218 min read

Database Designing Help | Designing Netflix Database Using MSSQL



Creating Tables

CREATE TABLE City(
CityId		numeric(10)	NOT NULL,
CityName	varchar(32)	NOT NULL,
CONSTRAINT City_CityId_PK PRIMARY KEY (CityId));

CREATE TABLE State(
StateId		numeric(2)	NOT NULL,
StateName	varchar(20)	NOT NULL,
CONSTRAINT State_StateId_PK PRIMARY KEY (StateId));

CREATE TABLE ZipCode(
ZipCodeId	numeric(10)	NOT NULL,
ZipCode		varchar(5)	NOT NULL,
StateId		numeric(2),
CityId		numeric(10),
CONSTRAINT ZipCode_ZipCodeId_PK PRIMARY KEY (ZipCodeId),
CONSTRAINT ZipCode_StateId_FK FOREIGN KEY (StateId) REFERENCES State(StateId),
CONSTRAINT ZipCode_CityId_FK FOREIGN KEY (CityId) REFERENCES City(CityId));


CREATE TABLE Membership(
MembershipId		numeric(10)	NOT NULL,
MembershipType		varchar(128)	NOT NULL,
MembershipLimitPerMonth	numeric(2)	NOT NULL,
MembershipMonthlyPrice	numeric(5,2)	NOT NULL,
MembershipMonthlyTax	numeric(5,2)	NOT NULL,
MembershipDVDLostPrice	numeric(5,2)	NOT NULL,
CONSTRAINT Membership_MembershipId_PK PRIMARY KEY (MembershipId));

CREATE TABLE Member(
MemberId		numeric(12)	NOT NULL,
MemberFirstName		varchar(32)	NOT NULL,
MemberLastName		varchar(32)	NOT NULL,
MemberInitial		varchar(32),
MemberAddress		varchar(100)	NOT NULL,
MemberAddressId		numeric(10)	NOT NULL,
MemberPhone		varchar(14),
MemberEMail		varchar(32)	NOT NULL,
MemberPassword		varchar(32)	NOT NULL,
MembershipId		numeric(10)	NOT NULL,
MemberSinceDate		DATETIME		NOT NULL,
CONSTRAINT Member_MemberId_PK PRIMARY KEY (MemberId),
CONSTRAINT Member_MemberAddId_FK 
	FOREIGN KEY (MemberAddressId) REFERENCES ZipCode(ZipCodeId),
CONSTRAINT Member_MembershipId_FK
	FOREIGN KEY (MembershipId) REFERENCES Membership);

CREATE TABLE Payment(
PaymentId		numeric(16)	NOT NULL,
MemberId		numeric(12)	NOT NULL,
AmountPaid		numeric(8,2)	NOT NULL,
AmountPaidDate		DATETIME		NOT NULL,
AmountPaidUntilDate	DATETIME		NOT NULL,
CONSTRAINT Payment_PaymentId_PK PRIMARY KEY (PaymentId),
CONSTRAINT Payment_MemberId_FK FOREIGN KEY (MemberId) REFERENCES Member(MemberId));

CREATE TABLE Genre(
GenreId			numeric(2)	NOT NULL,
GenreName		varchar(20)	NOT NULL,
CONSTRAINT Genre_GenreId_PK PRIMARY KEY (GenreId));


CREATE TABLE Rating(
RatingId		numeric(2)	NOT NULL,
RatingName		varchar(10)	NOT NULL,
RatingDescription	varchar(255)	NOT NULL,
CONSTRAINT Rating_RatingId_PK PRIMARY KEY (RatingId));


CREATE TABLE Role(
RoleId			numeric(2)	NOT NULL,
RoleName		varchar(20)	NOT NULL,
CONSTRAINT Role_RoleId_PK PRIMARY KEY (RoleId));


CREATE TABLE MoviePerson(
PersonId		numeric(12)	NOT NULL,
PersonFirstName		varchar(32)	NOT NULL,
PersonLastName		varchar(32)	NOT NULL,
PersonInitial		varchar(32),		
PersonDateOfBirth	DATETIME,
CONSTRAINT MoviePerson_PersonId_PK PRIMARY KEY (PersonId));

CREATE TABLE DVD(
DVDId			numeric(16)	NOT NULL,
DVDTitle		varchar(32)	NOT NULL,
GenreId			numeric(2)	NOT NULL,
RatingId		numeric(2)	NOT NULL,
DVDReleaseDate		DATETIME		NOT NULL,
TheaterReleaseDate	DATETIME,
DVDQuantityOnHand	numeric(8)	NOT NULL,
DVDQuantityOnRent	numeric(8)	NOT NULL,
DVDLostQuantity		numeric(8)	NOT NULL,
CONSTRAINT DVD_DVDId_PK PRIMARY KEY (DVDId),
CONSTRAINT DVD_GenreId_FK FOREIGN KEY (GenreId) REFERENCES Genre(GenreId),
CONSTRAINT DVD_RatingId FOREIGN KEY (RatingId) REFERENCES Rating(RatingId));

CREATE TABLE RentalQueue(
MemberId		numeric(12)	NOT NULL,
DVDId			numeric(16)	NOT NULL,
DateAddedInQueue	DATETIME		NOT NULL,
CONSTRAINT RentalQueue_MemberId_DVDId_PK PRIMARY KEY (MemberId,DVDId),
CONSTRAINT RentalQueue_MemberId_FK FOREIGN KEY (MemberId) REFERENCES Member(MemberId),
CONSTRAINT RentalQueue_DVDId_FK FOREIGN KEY (DVDId) REFERENCES DVD(DVDId));

CREATE TABLE MoviePersonRole(
PersonId		numeric(12)	NOT NULL,
RoleId			numeric(2)	NOT NULL,
DVDId			numeric(16)	NOT NULL,
CONSTRAINT MoviePersonRole_PK PRIMARY KEY (PersonId,DVDId,RoleId),
CONSTRAINT MoviePersonRole_PersonId_FK FOREIGN KEY (PersonId) REFERENCES MoviePerson(PersonId),
CONSTRAINT MoviePersonRole_DVDId_FK FOREIGN KEY (DVDId) REFERENCES DVD(DVDId),
CONSTRAINT MoviePersonRole_RoleId_FK FOREIGN KEY (RoleId) REFERENCES Role(RoleId));

CREATE TABLE Rental(
RentalId		numeric(16)	NOT NULL,
MemberId		numeric(12)	NOT NULL,
DVDId			numeric(16)	NOT NULL,
RentalRequestDate	DATETIME		NOT NULL,
RentalShippedDate	DATETIME,
RentalReturnedDate	DATETIME,
CONSTRAINT Rental_RentalId_PK PRIMARY KEY (RentalId),
CONSTRAINT Rental_MemberId_FK FOREIGN KEY (MemberId) REFERENCES Member(MemberId),
CONSTRAINT Rental_DVDId_FK FOREIGN KEY (DVDId) REFERENCES DVD(DVDId));

Insert Data Into Tables

use NetFlix
DECLARE @AddYears int = Datepart(YEAR, getdate()) - 2005;

INSERT INTO Membership(MembershipId,MembershipType,MembershipLimitPerMonth,
		MembershipMonthlyPrice,MembershipMonthlyTax,MembershipDVDLostPrice) 
	VALUES(1,'3 DVDs at-a-time',99,17.99,1.79,25.00);
INSERT INTO Membership(MembershipId,MembershipType,MembershipLimitPerMonth,
		MembershipMonthlyPrice,MembershipMonthlyTax,MembershipDVDLostPrice) 
	VALUES(2,'2 DVDs at-a-time',4,11.99,1.19,25.00);
INSERT INTO State(StateId,StateName) VALUES (1,'California');
INSERT INTO State(StateId,StateName) VALUES (2,'Delaware');
INSERT INTO State(StateId,StateName) VALUES (3,'Florida');
INSERT INTO State(StateId,StateName) VALUES (4,'Georgia');
INSERT INTO State(StateId,StateName) VALUES (5,'Iowa');
INSERT INTO State(StateId,StateName) VALUES (6,'New Jersey');
INSERT INTO State(StateId,StateName) VALUES (7,'New York');
INSERT INTO State(StateId,StateName) VALUES (8,'Maryland');
INSERT INTO State(StateId,StateName) VALUES (9,'Pennsylvania');
INSERT INTO State(StateId,StateName) VALUES (10,'Texas');

INSERT INTO City(CityID,CityName) VALUES (1,'Sacramento');
INSERT INTO City(CityID,CityName) VALUES (2,'Ewing');
INSERT INTO City(CityID,CityName) VALUES (3,'New York');
INSERT INTO City(CityID,CityName) VALUES (4,'Palm Coast');
INSERT INTO City(CityID,CityName) VALUES (5,'Harrisburg');
INSERT INTO City(CityID,CityName) VALUES (6,'York');
INSERT INTO City(CityID,CityName) VALUES (7,'Jacksonville');
INSERT INTO City(CityID,CityName) VALUES (8,'Atlanta');
INSERT INTO City(CityID,CityName) VALUES (9,'Washington');
INSERT INTO City(CityID,CityName) VALUES (10,'Austin');
INSERT INTO City(CityID,CityName) VALUES (11,'Wilmington');
INSERT INTO City(CityID,CityName) VALUES (12,'Columbia');
INSERT INTO City(CityID,CityName) VALUES (13,'Marbury');
INSERT INTO City(CityID,CityName) VALUES (14,'Los Angeles');
INSERT INTO ZipCode(ZipCodeId,ZipCode,CityId,StateId) VALUES (1,'94203',1,1);
INSERT INTO ZipCode(ZipCodeId,ZipCode,CityId,StateId) VALUES (2,'08628',2,6);
INSERT INTO ZipCode(ZipCodeId,ZipCode,CityId,StateId) VALUES (3,'10001',3,7);
INSERT INTO ZipCode(ZipCodeId,ZipCode,CityId,StateId) VALUES (4,'32035',4,3);
INSERT INTO ZipCode(ZipCodeId,ZipCode,CityId,StateId) VALUES (5,'17100',5,9);
INSERT INTO ZipCode(ZipCodeId,ZipCode,CityId,StateId) VALUES (6,'17400',6,9);
INSERT INTO ZipCode(ZipCodeId,ZipCode,CityId,StateId) VALUES (7,'32099',7,3);
INSERT INTO ZipCode(ZipCodeId,ZipCode,CityId,StateId) VALUES (8,'30029',8,4);
INSERT INTO ZipCode(ZipCodeId,ZipCode,CityId,StateId) VALUES (9,'30673',9,4);
INSERT INTO ZipCode(ZipCodeId,ZipCode,CityId,StateId) VALUES (10,'73301',10,10);
INSERT INTO ZipCode(ZipCodeId,ZipCode,CityId,StateId) VALUES (11,'19850',11,2);
INSERT INTO ZipCode(ZipCodeId,ZipCode,CityId,StateId) VALUES (12,'50057',12,5);
INSERT INTO ZipCode(ZipCodeId,ZipCode,CityId,StateId) VALUES (13,'20658',13,8);
INSERT INTO ZipCode(ZipCodeId,ZipCode,CityId,StateId) VALUES (14,'90080',14,1);
INSERT INTO ZipCode(ZipCodeId,ZipCode,CityId,StateId) VALUES (15,'90019',14,1);
delete from Member

INSERT INTO Member(MemberId,MemberFirstName,MemberLastName,MemberAddress,
	MemberAddressId,MemberEMail,MemberPassword,MembershipId,MemberSinceDate)
	VALUES (1,'Will','Smith','101 Will Street',1,'will.smith@mib.com','W1ll1m!',1,DATEADD(YEAR, @AddYears, '02/01/2004'));
INSERT INTO Member(MemberId,MemberFirstName,MemberLastName,MemberAddress,
	MemberAddressId,MemberEMail,MemberPassword,MembershipId,MemberSinceDate)
	VALUES (2,'John','Gore','45 5th Ave',2,'john45@yahoo.com','J0HnGoRe',2,DATEADD(YEAR, @AddYears, '02/02/2004'));
INSERT INTO Member(MemberId,MemberFirstName,MemberLastName,MemberAddress,
	MemberAddressId,MemberEMail,MemberPassword,MembershipId,MemberSinceDate)
	VALUES (3,'Mike','Sawicki','10 Penn Blvd',3,'mikesawicki@aol.com','Saw13ki',2,DATEADD(YEAR, @AddYears, '02/09/2004'));
INSERT INTO Member(MemberId,MemberFirstName,MemberLastName,MemberAddress,
	MemberAddressId,MemberEMail,MemberPassword,MembershipId,MemberSinceDate)
	VALUES (4,'Ramesh','Mandadi','9 Avelon Apt',4,'ramesh1@yahoo.com','Ram3sh',1,DATEADD(YEAR, @AddYears, '02/11/2004'));
INSERT INTO Member(MemberId,MemberFirstName,MemberLastName,MemberAddress,
	MemberAddressId,MemberEMail,MemberPassword,MembershipId,MemberSinceDate)
	VALUES (5,'Frank','Cruthers','1129 Jackson Rd',5,'franky@aol.com','qW1est',2,DATEADD(YEAR, @AddYears, '02/12/2004'));
INSERT INTO Member(MemberId,MemberFirstName,MemberLastName,MemberAddress,
	MemberAddressId,MemberEMail,MemberPassword,MembershipId,MemberSinceDate)
	VALUES (6,'Rich','Sentveld','1001 Plainsboro Rd',6,'richard@aol.com','R1chArd',1,DATEADD(YEAR, @AddYears, '02/12/2004'));
INSERT INTO Member(MemberId,MemberFirstName,MemberLastName,MemberAddress,
	MemberAddressId,MemberEMail,MemberPassword,MembershipId,MemberSinceDate)
	VALUES (7,'George','Lemon','29456 Upper Ferry Rd',7,'glemon@aol.com','ImG3m0n',2,DATEADD(YEAR, @AddYears, '02/14/2004'));
INSERT INTO Member(MemberId,MemberFirstName,MemberLastName,MemberAddress,
	MemberAddressId,MemberEMail,MemberPassword,MembershipId,MemberSinceDate)
	VALUES (8,'Haby','Tanjung','5 River Rd',8,'ht@hasby.com','T1c00l',1,DATEADD(YEAR, @AddYears, '02/15/2004'));
INSERT INTO Member(MemberId,MemberFirstName,MemberLastName,MemberAddress,
	MemberAddressId,MemberEMail,MemberPassword,MembershipId,MemberSinceDate)
	VALUES (9,'Cathy','Carol','59 Eaton Twon',9,'carol@hotmail.com','C9thC3ty',2,DATEADD(YEAR, @AddYears, '02/15/2004'));
INSERT INTO Member(MemberId,MemberFirstName,MemberLastName,MemberAddress,
	MemberAddressId,MemberEMail,MemberPassword,MembershipId,MemberSinceDate)
	VALUES (10,'Marji','Keho','2365 Merci Blvd',10,'marji.keho@merci.com','M1rjii',1,DATEADD(YEAR, @AddYears, '02/19/2004'));
INSERT INTO Member(MemberId,MemberFirstName,MemberLastName,MemberAddress,
	MemberAddressId,MemberEMail,MemberPassword,MembershipId,MemberSinceDate)
	VALUES (11,'Clint','Eastwood','90 Hill Rd',11,'eastwoodc@gmail.com','Clint12',1,DATEADD(YEAR, @AddYears, '02/19/2004'));
INSERT INTO Member(MemberId,MemberFirstName,MemberLastName,MemberAddress,
	MemberAddressId,MemberEMail,MemberPassword,MembershipId,MemberSinceDate)
     VALUES (12,'Bruce','Willis','1740 Erford Rd',12,'brucewillis@aol.com','YgDm3n',1,DATEADD(YEAR, @AddYears, '02/21/2004'));
INSERT INTO Member(MemberId,MemberFirstName,MemberLastName,MemberAddress,
	MemberAddressId,MemberEMail,MemberPassword,MembershipId,MemberSinceDate)
	VALUES (13,'Yong','Lee','1 Yong-Yong Park',13,'yonglee@lee1.com','Y0ngY0ng',1,DATEADD(YEAR, @AddYears, '02/28/2004'));
INSERT INTO Member(MemberId,MemberFirstName,MemberLastName,MemberAddress,
	MemberAddressId,MemberEMail,MemberPassword,MembershipId,MemberSinceDate)
     VALUES (14,'Jerald','Parekh','3434 Campus Rd',14,'jer9439@hotmail.com','C0sm0s',2,DATEADD(YEAR, @AddYears, '02/29/2004'));
INSERT INTO Member(MemberId,MemberFirstName,MemberLastName,MemberAddress,
	MemberAddressId,MemberEMail,MemberPassword,MembershipId,MemberSinceDate)
   VALUES (15,'Kristy','Hanifin','67 Jackob Creek Rd',15,'kristine@aol.com','ImKr1sty',1,DATEADD(YEAR, @AddYears, '02/29/2004'));
INSERT INTO Payment(PaymentId,MemberId,AmountPaid,AmountPaidDate,AmountPaidUntilDate)
	VALUES (1,1,19.78,DATEADD(YEAR, @AddYears, '02/01/2004'),DATEADD(YEAR, @AddYears, '03/01/2004'));
INSERT INTO Payment(PaymentId,MemberId,AmountPaid,AmountPaidDate,AmountPaidUntilDate)
	VALUES (2,2,19.78,DATEADD(YEAR, @AddYears, '02/02/2004'),DATEADD(YEAR, @AddYears, '03/02/2004'));
INSERT INTO Payment(PaymentId,MemberId,AmountPaid,AmountPaidDate,AmountPaidUntilDate)
	VALUES (3,3,13.18,DATEADD(YEAR, @AddYears, '02/09/2004'),DATEADD(YEAR, @AddYears, '03/09/2004'));
INSERT INTO Payment(PaymentId,MemberId,AmountPaid,AmountPaidDate,AmountPaidUntilDate)
	VALUES (4,4,19.98,DATEADD(YEAR, @AddYears, '02/11/2004'),DATEADD(YEAR, @AddYears, '03/11/2004'));
INSERT INTO Payment(PaymentId,MemberId,AmountPaid,AmountPaidDate,AmountPaidUntilDate)
	VALUES (5,5,13.18,DATEADD(YEAR, @AddYears, '02/12/2004'),DATEADD(YEAR, @AddYears, '03/12/2004'));
INSERT INTO Payment(PaymentId,MemberId,AmountPaid,AmountPaidDate,AmountPaidUntilDate)
	VALUES (6,6,19.78,DATEADD(YEAR, @AddYears, '02/12/2004'),DATEADD(YEAR, @AddYears, '03/12/2004'));
INSERT INTO Payment(PaymentId,MemberId,AmountPaid,AmountPaidDate,AmountPaidUntilDate)
	VALUES (7,7,13.18,DATEADD(YEAR, @AddYears, '02/14/2004'),DATEADD(YEAR, @AddYears, '03/14/2004'));
INSERT INTO Payment(PaymentId,MemberId,AmountPaid,AmountPaidDate,AmountPaidUntilDate)
	VALUES (8,8,19.78,DATEADD(YEAR, @AddYears, '02/15/2004'),DATEADD(YEAR, @AddYears, '03/15/2004'));
INSERT INTO Payment(PaymentId,MemberId,AmountPaid,AmountPaidDate,AmountPaidUntilDate)
	VALUES (9,9,13.18,DATEADD(YEAR, @AddYears, '02/15/2004'),DATEADD(YEAR, @AddYears, '03/15/2004'));
INSERT INTO Payment(PaymentId,MemberId,AmountPaid,AmountPaidDate,AmountPaidUntilDate)
	VALUES (10,10,19.78,DATEADD(YEAR, @AddYears, '02/19/2004'),DATEADD(YEAR, @AddYears, '03/19/2004'));
INSERT INTO Payment(PaymentId,MemberId,AmountPaid,AmountPaidDate,AmountPaidUntilDate)
	VALUES (11,11,19.78,DATEADD(YEAR, @AddYears, '02/19/2004'),DATEADD(YEAR, @AddYears, '03/19/2004'));
INSERT INTO Payment(PaymentId,MemberId,AmountPaid,AmountPaidDate,AmountPaidUntilDate)
	VALUES (12,12,19.98,DATEADD(YEAR, @AddYears, '02/21/2004'),DATEADD(YEAR, @AddYears, '03/21/2004'));
INSERT INTO Payment(PaymentId,MemberId,AmountPaid,AmountPaidDate,AmountPaidUntilDate)
	VALUES (13,1,25.00,DATEADD(YEAR, @AddYears, '02/21/2004'),DATEADD(YEAR, @AddYears, '03/01/2004'));
INSERT INTO Payment(PaymentId,MemberId,AmountPaid,AmountPaidDate,AmountPaidUntilDate)
	VALUES (14,13,19.78,DATEADD(YEAR, @AddYears, '02/28/2004'),DATEADD(YEAR, @AddYears, '03/28/2004'));
INSERT INTO Payment(PaymentId,MemberId,AmountPaid,AmountPaidDate,AmountPaidUntilDate)
	VALUES (15,14,13.18,DATEADD(YEAR, @AddYears, '02/29/2004'),DATEADD(YEAR, @AddYears, '03/29/2004'));
INSERT INTO Payment(PaymentId,MemberId,AmountPaid,AmountPaidDate,AmountPaidUntilDate)
	VALUES (16,15,19.78,DATEADD(YEAR, @AddYears, '02/29/2004'),DATEADD(YEAR, @AddYears, '03/29/2004'));
INSERT INTO Genre(GenreId,GenreName) VALUES (1,'Action');
INSERT INTO Genre(GenreId,GenreName) VALUES (2,'Adventure');
INSERT INTO Genre(GenreId,GenreName) VALUES (3,'Comedy');
INSERT INTO Genre(GenreId,GenreName) VALUES (4,'Crime');
INSERT INTO Genre(GenreId,GenreName) VALUES (5,'Drama');
INSERT INTO Genre(GenreId,GenreName) VALUES (6,'Epics');
INSERT INTO Genre(GenreId,GenreName) VALUES (7,'Musicals');
INSERT INTO Genre(GenreId,GenreName) VALUES (8,'Science Fiction');
INSERT INTO Genre(GenreId,GenreName) VALUES (9,'War'); 
INSERT INTO Genre(GenreId,GenreName) VALUES (10,'Western'); 
INSERT INTO Genre(GenreId,GenreName) VALUES (11,'Romance'); 
INSERT INTO Genre(GenreId,GenreName) VALUES (12,'Horror'); 
INSERT INTO Rating(RatingId,RatingName,RatingDescription) VALUES (1,'G','General Audiences. All ages admitted.');
INSERT INTO Rating(RatingId,RatingName,RatingDescription) VALUES (2,'PG','Parental Guidance suggested. Some material
 may not be suitable for children.');
INSERT INTO Rating(RatingId,RatingName,RatingDescription) VALUES (3,'PG-13','Parent strongly cautioned. Some material may be inappropriate for children under 13.');
INSERT INTO Rating(RatingId,RatingName,RatingDescription) VALUES (4,'R','Restricted. Under 17 requires accompanying parent or adult guardian.');
INSERT INTO Rating(RatingId,RatingName,RatingDescription) VALUES (5,'NC-17','No one 17 and under admitted.');
INSERT INTO Role(RoleId,RoleName) VALUES (1,'Actor');
INSERT INTO Role(RoleId,RoleName) VALUES (2,'Actoress');
INSERT INTO Role(RoleId,RoleName) VALUES (3,'Director');
INSERT INTO Role(RoleId,RoleName) VALUES (4,'Producer');
INSERT INTO Role(RoleId,RoleName) VALUES (5,'Screenwriter');

INSERT INTO MoviePerson(PersonId,PersonFirstName,PersonLastName,PersonDateOfBirth)
	VALUES (1,'Bill','Murry','09/21/1950');
INSERT INTO MoviePerson(PersonId,PersonFirstName,PersonLastName,PersonDateOfBirth)
	VALUES (2,'Steven','Spielberg','12/18/1946');
INSERT INTO MoviePerson(PersonId,PersonFirstName,PersonLastName,PersonDateOfBirth)
	VALUES (3,'Tom','Hanks','07/09/1956');
INSERT INTO MoviePerson(PersonId,PersonFirstName,PersonLastName,PersonDateOfBirth)
	VALUES (4,'Leonardo','DiCaprio','11/11/1974');
INSERT INTO MoviePerson(PersonId,PersonFirstName,PersonLastName,PersonInitial,PersonDateOfBirth)
	VALUES (5,'Night','Shyamalan','M','08/06/1970');
INSERT INTO MoviePerson(PersonId,PersonFirstName,PersonLastName,PersonDateOfBirth)
	VALUES (6,'Bruce','Willis','03/19/1955');
INSERT INTO MoviePerson(PersonId,PersonFirstName,PersonLastName,PersonDateOfBirth)
	VALUES (7,'Catherine','Zeta-Zones','09/20/1969');
INSERT INTO MoviePerson(PersonId,PersonFirstName,PersonLastName,PersonDateOfBirth)
	VALUES (8,'Clint','Eastwood','05/31/1930');
INSERT INTO MoviePerson(PersonId,PersonFirstName,PersonLastName,PersonDateOfBirth)
	VALUES (9,'Sean','Penn','08/17/1960');
INSERT INTO MoviePerson(PersonId,PersonFirstName,PersonLastName,PersonDateOfBirth)
	VALUES (10,'Kevin','Bacon','06/29/1958');
INSERT INTO MoviePerson(PersonId,PersonFirstName,PersonLastName,PersonDateOfBirth)
	VALUES (11,'Irvin','Kreshner','04/29/1923');
INSERT INTO MoviePerson(PersonId,PersonFirstName,PersonLastName,PersonDateOfBirth)
	VALUES (12,'Sean','Connery','09/25/1930');
INSERT INTO MoviePerson(PersonId,PersonFirstName,PersonLastName,PersonDateOfBirth)
	VALUES (13,'Kim','Basinger','12/08/1953');

INSERT INTO DVD(DVDId,DVDTitle,GenreId,RatingId,DVDReleaseDate,
		TheaterReleaseDate,DVDQuantityOnHand,DVDQuantityOnRent,DVDLostQuantity)
	VALUES (1,'Groundhog Day',3,2,'01/22/2002','01/22/2001',9,0,1);
INSERT INTO DVD(DVDId,DVDTitle,GenreId,RatingId,DVDReleaseDate,
		TheaterReleaseDate,DVDQuantityOnHand,DVDQuantityOnRent,DVDLostQuantity)
	VALUES (2,'The Terminal',5,2,'11/23/2003','01/22/2003',9,1,0);
INSERT INTO DVD(DVDId,DVDTitle,GenreId,RatingId,DVDReleaseDate,
		TheaterReleaseDate,DVDQuantityOnHand,DVDQuantityOnRent,DVDLostQuantity)
	VALUES (3,'Catch Me If You Can',5,3,'05/06/2003','01/04/2002',10,0,0);
INSERT INTO DVD(DVDId,DVDTitle,GenreId,RatingId,DVDReleaseDate,
		TheaterReleaseDate,DVDQuantityOnHand,DVDQuantityOnRent,DVDLostQuantity)
	VALUES (4,'The Sixth Sense',12,4,'03/28/2000','05/01/1999',9,1,0);
INSERT INTO DVD(DVDId,DVDTitle,GenreId,RatingId,DVDReleaseDate,
		TheaterReleaseDate,DVDQuantityOnHand,DVDQuantityOnRent,DVDLostQuantity)
	VALUES (5,'Pale Rider',10,4,'11/19/1997','11/15/1985',0,1,0);
INSERT INTO DVD(DVDId,DVDTitle,GenreId,RatingId,DVDReleaseDate,
		TheaterReleaseDate,DVDQuantityOnHand,DVDQuantityOnRent,DVDLostQuantity)
	VALUES (6,'Mystic River',5,4,'01/08/2004','05/08/2003',10,0,0);
INSERT INTO DVD(DVDId,DVDTitle,GenreId,RatingId,DVDReleaseDate,
		TheaterReleaseDate,DVDQuantityOnHand,DVDQuantityOnRent,DVDLostQuantity)
	VALUES (7,'Never Say Never Again',1,1,'10/17/2000','05/08/1983',10,0,0);

INSERT INTO MoviePersonRole(PersonId,RoleId,DVDId) VALUES(1,1,1);
INSERT INTO MoviePersonRole(PersonId,RoleId,DVDId) VALUES(2,3,2);
INSERT INTO MoviePersonRole(PersonId,RoleId,DVDId) VALUES(2,3,3);
INSERT INTO MoviePersonRole(PersonId,RoleId,DVDId) VALUES(3,1,2);
INSERT INTO MoviePersonRole(PersonId,RoleId,DVDId) VALUES(3,1,3);
INSERT INTO MoviePersonRole(PersonId,RoleId,DVDId) VALUES(4,1,3);
INSERT INTO MoviePersonRole(PersonId,RoleId,DVDId) VALUES(5,3,4);
INSERT INTO MoviePersonRole(PersonId,RoleId,DVDId) VALUES(5,1,4);
INSERT INTO MoviePersonRole(PersonId,RoleId,DVDId) VALUES(5,4,4);
INSERT INTO MoviePersonRole(PersonId,RoleId,DVDId) VALUES(6,1,4);
INSERT INTO MoviePersonRole(PersonId,RoleId,DVDId) VALUES(7,2,2);
INSERT INTO MoviePersonRole(PersonId,RoleId,DVDId) VALUES(8,1,5);
INSERT INTO MoviePersonRole(PersonId,RoleId,DVDId) VALUES(8,3,5);
INSERT INTO MoviePersonRole(PersonId,RoleId,DVDId) VALUES(8,4,5);
INSERT INTO MoviePersonRole(PersonId,RoleId,DVDId) VALUES(8,3,6);
INSERT INTO MoviePersonRole(PersonId,RoleId,DVDId) VALUES(8,4,6);
INSERT INTO MoviePersonRole(PersonId,RoleId,DVDId) VALUES(9,1,6);
INSERT INTO MoviePersonRole(PersonId,RoleId,DVDId) VALUES(10,1,6);
INSERT INTO MoviePersonRole(PersonId,RoleId,DVDId) VALUES(11,3,7);
INSERT INTO MoviePersonRole(PersonId,RoleId,DVDId) VALUES(12,1,7);
INSERT INTO MoviePersonRole(PersonId,RoleId,DVDId) VALUES(13,2,7);


INSERT INTO Rental(RentalId,MemberId,DVDId,RentalRequestDate,RentalShippedDate,RentalReturnedDate)
 VALUES (1,1,4,DATEADD(YEAR, @AddYears, '02/02/2004'),DATEADD(YEAR, @AddYears, '02/02/2004'),DATEADD(YEAR, @AddYears, '02/09/2004'));
INSERT INTO Rental(RentalId,MemberId,DVDId,RentalRequestDate,RentalShippedDate,RentalReturnedDate)
 VALUES (2,1,6,DATEADD(YEAR, @AddYears, '02/02/2004'),DATEADD(YEAR, @AddYears, '02/02/2004'),DATEADD(YEAR, @AddYears, '02/09/2004'));
INSERT INTO Rental(RentalId,MemberId,DVDId,RentalRequestDate,RentalShippedDate,RentalReturnedDate)
 VALUES (3,1,3,DATEADD(YEAR, @AddYears, '02/02/2004'),DATEADD(YEAR, @AddYears, '02/02/2004'),DATEADD(YEAR, @AddYears, '02/09/2004'));
INSERT INTO Rental(RentalId,MemberId,DVDId,RentalRequestDate,RentalShippedDate)
 VALUES (4,5,4,DATEADD(YEAR, @AddYears, '02/15/2004'),DATEADD(YEAR, @AddYears, '02/15/2004'));
INSERT INTO Rental(RentalId,MemberId,DVDId,RentalRequestDate,RentalShippedDate)
 VALUES (5,5,5,DATEADD(YEAR, @AddYears, '02/15/2004'),DATEADD(YEAR, @AddYears, '02/15/2004'));
INSERT INTO Rental(RentalId,MemberId,DVDId,RentalRequestDate,RentalShippedDate,RentalReturnedDate)
 VALUES (6,15,1,DATEADD(YEAR, @AddYears, '02/12/2004'),DATEADD(YEAR, @AddYears, '02/12/2004'),DATEADD(YEAR, @AddYears, '02/21/2004'));
INSERT INTO Rental(RentalId,MemberId,DVDId,RentalRequestDate,RentalShippedDate)
 VALUES (7,9,2,DATEADD(YEAR, @AddYears, '02/19/2004'),DATEADD(YEAR, @AddYears, '02/19/2004'));
INSERT INTO Rental(RentalId,MemberId,DVDId,RentalRequestDate,RentalShippedDate)
 VALUES (8,8,2,DATEADD(YEAR, @AddYears, '02/20/2004'),DATEADD(YEAR, @AddYears, '02/21/2004'));
INSERT INTO Rental(RentalId,MemberId,DVDId,RentalRequestDate,RentalShippedDate)
 VALUES (9,1,4,DATEADD(YEAR, @AddYears, '02/20/2004'),DATEADD(YEAR, @AddYears, '02/20/2004'));
INSERT INTO Rental(RentalId,MemberId,DVDId,RentalRequestDate)
 VALUES (10,15,7,DATEADD(YEAR, @AddYears, '02/29/2004'));

INSERT INTO RentalQueue(MemberId,DVDId,DateAddedInQueue)
 VALUES (12,5,DATEADD(YEAR, @AddYears, '02/22/2004'));

Practice Set

Directions:

1. Create two sessions connected as the same user to a transactional DBMS.

  • SQL Server Note: you will need to open two separate query windows instead of two separate sessions.

2. Create two tables :

CREATE TABLE Deadlocker1 (x INTEGER);

CREATE TABLE Deadlocker2 (x INTEGER);

[Take a screenshot to show your results and paste it here.]


3. Insert two rows in the tables with different data values.

INSERT INTO Deadlocker1 VALUES(1);

INSERT INTO Deadlocker1 VALUES(2);

INSERT INTO Deadlocker2 VALUES(1);

INSERT INTO Deadlocker2 VALUES(2);

  • Note for Oracle and other RDBMS with autocommit off: Commit your transaction.

[Take a screenshot to show your results and paste it here.]


Optionally check the data in the two tables by doing a simple select.


4. In the first session start a transaction by updating the first Deadlocker1 table. This procures an exclusive lock on the Dedlocker1 table, on the row with x=1.


UPDATE Deadlocker1 SET x=3 where x=1;

  • SQL Server Note: you will need to use transactions to create a deadlock. To do this before the update add line BEGIN TRAN TRAN1

[Take a screenshot to show your results and paste it here.]


5. In the second session start a new transaction by updating the Deadlocker2 table, updating the second row (where x=2), placing a lock on the second row of the Deadlocker2 table. Also update the Deadlocker1 table, trying to set the first column (which is now locked by the other transaction) 


UPDATE Deadlocker2 SET x=3 where x=2;
UPDATE Deadlocker1 SET x=4 where x=1;

  • Oracle Note: In Oracle Developer you will see ScriptRunner Task window running once you run the above commands.

  • SQL Server Note: you will need to use transactions to create a deadlock. To do this before the update add line BEGIN TRAN TRAN2

[Take a screenshot to show your results and paste it here.]


6. At this point, the second session should be trying to execute the query. Let us take a look at the locks.

  • In Oracle SQL Developer: Under Tools select “Monitor Sessions” and connect as user SYSTEM. A Session window will open. Look for the User who you logged in as (you can filter by the column) and note the UPDATE command running (under the Command Column).

[Take a screenshot to show your results and paste it here.]


  • In Microsoft SQL Server: Open the Activity Monitor (One way is to hold down CTRL-ALT-A)

It is also available as an icon in the toolbar at the top. Expand the processes window. You should see one of the tasks suspended. That is the one that has the lock (examine the wait type and wait response).


[Take a screenshot to show your results and paste it here.]


7. Now go back to the first session window, which is running the first transaction, and update the second Deadlocker2 table, updating the row where X=2.


UPDATE Deadlocker2 SET x=5 where x=2;


[Take a screenshot to show your results and paste it here.]


8. You will see an error message, which should tell you that the transaction was deadlocked for one of your transactions. Take a screenshot of this and attach it here.


[Take a screenshot to show your results and paste it here.]


9. You can unlock the second session window by committing the transaction.


10. [Optional] Explain which session was the deadlock victim and why this occurred. It may be helpful to create a little sequence table, and consider what is happening at each step, identifying when the deadlock occurs.


11. [Optional]: If you would like you can try the same thing with shared locks, by selecting the rows instead of updating them. You shouldn’t get a deadlock in a DBMS that supports shared locks. In a DBMS with only binary locks you would get a deadlock.


12. [Optional] For a more advanced variation you can create a three-session deadlock. Hint: This requires three rows in Deadlocker and multiple Deadlocker tables.



Contact Us

Looking to hire expert which can do your Database Homework, Database Designing Help, Database Project Help, Database Assignment Help. Send requirement Details directly at:

realcode4you@gmail.com



412 views0 comments

Comments

bottom of page