WordPress.com



/*Phisycal Data modelby: Kudzo Gasonu*/--USE master;--GO--ALTER DATABASE KudzoIncRental SET SINGLE_USER WITH ROLLBACK IMMEDIATE;--GOUSE MASTER;drop database KudzoIncRental;gocreate database KudzoIncRental;gouse KudzoIncRental;--for database diagram alter authorization on database::KudzoIncRental to sa;go--Creating tablesCreate table Customer (Customer_ID int identity (100,1) primary key,LastName varchar(20),FirstName varchar(20),MI varchar(1),DOB varchar(30),Phone_Number varchar (38),StreetName varchar(40),City varchar(43),State varchar(43),ZipCode varchar (5));Create table Item(Item_ID int identity (100,1)primary key,Quantity varchar (80),Unit_Price money,ItembyType varchar(55),);Create table Orders(Order_ID int identity(100,1) primary key,Quantity varchar(80),Unit_Price money ,Order_date date,Customer_ID int references Customer (Customer_ID),Item_ID int references Item (Item_ID),);Create table KudzoInc(Emp_ID int identity (100,1)primary key,LastName varchar(20),FirstName varchar(20) ,MI varchar(1),DOB varchar(30),Job_Title varchar(120),Phone_Number varchar (38) ,StreetName varchar(40),City varchar(43),State varchar(43),ZipCode varchar (5),Order_ID int references Orders (Order_ID),);create table Movie(--Movie_ID int identity (100,1)primary key not null,Movie_Title varchar(99),Fiction_Action varchar(1),Animate varchar (95),Comedy varchar(93),Movie_Rental_date date,Movie_Returned_date date,Item_ID int references Item (Item_ID),);create table VideoGame(VG_Title varchar(77),VG_Rental_Date date,VG_Returned_Date date,Item_ID int references Item (Item_ID),);INSERT INTO Item (Quantity, Unit_Price, ItembyType) VALUES('2', '$3.99', 'VideoGame'),('1', '$3.99', 'Movie'),('3', '$3.99', 'Movie'),('2', '$3.99', 'VideoGame'),('2', '$3.99', 'VideoGame'),('1', '$3.99', 'Movie'),('3', '$3.99', 'Movie'),('2', '$3.99', 'VideoGame'),('1','$10.99','Video Game'),('2','$9.99','video Game'),('1','$12.99','Movie'),('1','$05.99','Movie'),('3','$13.99','Movie'),('2','$10.99','Movie'),('2','$9.99','Movie'),('1','$12.99','Movie'),('1','$05.99','Video Game'),('1','$13.99','Video Game'), ('1','$10.99','Movie'), ('2','$9.99','Movie'), ('1','$12.99','Video Game'), ('1','$06.99','Movie'); ALTER TABLE MovieADD CONSTRAINT Fiction_ActionYN CHECK(Fiction_Action= 'Y' or Fiction_Action= 'N' or Animate= 'Y' or Animate = 'N' or Comedy ='Y' or Comedy='N');--creating join tableCREATE Table Customer_To_Item(Customer_ID int references Customer(Customer_ID),Item_ID int references Item(Item_ID),primary key (Customer_ID, Item_ID),);--Creating INDEXCREATE INDEX Index_Customertable_fullname ON Customer(LastName,FirstName);CREATE INDEX Index_KudzoInctable_fullname ON KudzoInc(LastName, FirstName);CREATE INDEX Index_Ordertable_Order ON Orders(Order_date);CREATE INDEX Index_Videogametable_Games ON VideoGame(VG_Title);CREATE INDEX Index_Movietable_Movies ON Movie(Movie_Title);--drop unique index (TO BE REMEMBER TO DELETE)--DROP INDEX Index_Movietable_Movies ON Movie;--GO-- creating unique indexCREATE UNIQUE INDEX Index_Customertable_lastname ON Customer(LastName);CREATE UNIQUE INDEX Index_KudzoInctable_lastname ON KudzoInc(LastName);CREATE UNIQUE INDEX Index_KudzoInctable_Job ON KudzoInc(Job_Title);GO-- #1 Stored Procedures--go--drop procedure load_Customer_data;GOcreate procedure load_Customer_data@LastName varchar(20),@FirstName varchar(20),@MI varchar(11),@DOB varchar(30),@Phone_Number varchar (38),@StreetName varchar(40),@City varchar(43),@State varchar(43),@ZipCode varchar (5)ASBEGIN--to avoid repeating same same data in the same tabledeclare @Customer_ID int;declare @check int;if @check is nullbegininsert into Customer (LastName,FirstName, MI,DOB, Phone_Number, StreetName, City,State, ZipCode) VALUES (@LastName,@FirstName,@MI, @DOB, @Phone_Number, @StreetName, @City,@State, @ZipCode);set @Customer_ID =(SCOPE_IDENTITY());ENDset @check= (select 1 from Customer where FirstName=@FirstName and MI=@MI and LastName=@LastName and Phone_Number=@Phone_Number and StreetName=@StreetName and City=@City and State=@State and ZipCode=@ZipCode);END;goexec load_Customer_data 'McRoy','John','J','10/12/1992', '814-265-4558', '300 campus Dr' , 'Bradford', 'PA', '16701';exec load_Customer_data 'Lively','Jeffrey','L', '05/28/1967','079-4255-7168','74 Trinity Crescent','WHISTON','UK','10405';exec load_Customer_data 'Lovely','Joe', '' ,'02/18/1996','030-5555-6868','30 Trinity Crescent','London','UK','06720';exec load_Customer_data 'Valentine','Felicia','G', '04/24/1991','248-562-2884','Bartlett Ave','Southfield','MI','48235';exec load_Customer_data 'Feely','Jermaine','L', '06/13/1997','209-669-5317','4049 Freed Dr.','Turlock','CA','95380';exec load_Customer_data 'Anderson','Daniel','A','11/10/1996','267-458-9356', '456 Quincy Street' , 'Philadelphia', 'PA', '19108';exec load_Customer_data 'Spivey','Lena','J', '06/28/1997','610-364-1616','4451 Stone Lane','Chester','PA','19013';exec load_Customer_data 'Ramirez','William','J' ,'07/01/1976','310-812-4275','630 Meadowbrook Mall Road','El Segundo','CA','90245';exec load_Customer_data 'James','Jones','G', '04/24/1991','412-402-6796','3377 Chandler Hollow Road','Pittsburgh','PA','15219';exec load_Customer_data 'Ward','Howard','M', '7/31/1997','815-410-9973','848 Lowland Drive','Chicago','IL','60606'; go -- #2 Stored Procedures go create procedure load_Orders@Quantity varchar(80),@Unit_Price money ,@Order_date date,@customer_ID int,@Item_Id intASBEGINdeclare @Order_ID int;declare @check int;if @check is nullbegininsert into Orders (Quantity,Unit_Price, Order_date,customer_ID,Item_Id) VALUES (@Quantity,@Unit_Price, @Order_date,@customer_ID,@Item_Id);set @Order_ID =(SCOPE_IDENTITY());ENDEND;goexec load_Orders '1','$10.99','05/15/2014','101','100';exec load_Orders '2','$9.99','01/08/2015','102','101';exec load_Orders '1','$12.99','03/05/2015','103','102';exec load_Orders '1','$05.99','01/08/2016','104','103';exec load_Orders '3','$13.99','09/23/2016','105','104';exec load_Orders '2','$10.99','04/08/2017','106','105';exec load_Orders '2','$9.99','11/05/2018','107','106';exec load_Orders '1','$12.99','05/08/2018','108','109';exec load_Orders '1','$05.99','04/09/2016','109','110';exec load_Orders '1','$10.99','02/20/2017','110','107';go--loading Movie table with datagocreate procedure Load_Movie@Movie_Title varchar(99),@Fiction_Action varchar(1),@Animate varchar (95),@Comedy varchar(93),@Movie_Rental_date date,@Movie_Returned_date date,@Item_ID intas begininsert into Movie (Movie_Title,Fiction_Action ,Animate ,Comedy ,Movie_Rental_date,Movie_Returned_date,Item_ID) values(@Movie_Title,@Fiction_Action ,@Animate ,@Comedy ,@Movie_Rental_date,@Movie_Returned_date,@Item_ID)set @Item_ID = (SCOPE_IDENTITY());ENDGOEXEC Load_Movie'Black Panther','Y','N','Y','04/15/2018','06/10/2018','101'; EXEC Load_Movie'Justice league','Y','N','N','01/08/2015','08/20/2016','102';EXEC Load_Movie'Maona','Y','Y','Y','03/05/2017','07/10/2017','105';EXEC Load_Movie'Rampage','Y','N','N','01/08/2015','08/21/2016','106';EXEC Load_Movie'X-Men: Dark Phoenix','Y','N','N','09/23/2017','10/10/2017','104';EXEC Load_Movie'Isle of Dogs','Y','Y','Y','03/08/2018','03/24/2018','110';EXEC Load_Movie'Sweet Country','N','N','N','03/05/2018','10/10/2018','111';EXEC Load_Movie'Paddington','N','Y','Y','02/08/2018','04/21/2018','112';EXEC Load_Movie'The Endless','N','N','Y','04/10/2018','04/30/2018','113';EXEC Load_Movie'Love After Love','N','N','Y','02/08/2018','04/21/2018','114';go--STORE PROCEDURE TO LOAD VIDEOGAMEgocreate procedure Load_VideoGame@VG_Title varchar(77),@VG_Rental_Date date,@VG_Returned_Date date,@Item_ID intas begininsert into VideoGame (VG_Title,VG_Rental_Date,VG_Returned_Date,Item_ID) values(@VG_Title,@VG_Rental_Date,@VG_Returned_Date,@Item_ID)set @Item_ID = (SCOPE_IDENTITY());END go EXEC Load_VideoGame 'Minecraft','02/02/2017','10/10/2017',100; EXEC Load_VideoGame 'Grand Theft Auto','03/03/2015','05/06/2016',103; EXEC Load_VideoGame'Portal','06/09/2014','08/10/2014',104; EXEC Load_VideoGame'Call of Duty','10/30/2007','12/03/2009',107; EXEC Load_VideoGame'Need for Speed','02/13/2009','04/10/2010',108; EXEC Load_VideoGame'God of War','05/03/2018','05/06/2018',109; EXEC Load_VideoGame'Super Mario Odyssey','08/09/2017','08/30/2017',116; EXEC Load_VideoGame'Horizon Zero Dawn','05/20/2017','12/03/2017',117; EXEC Load_VideoGame'The Technomancer','02/13/2016','03/10/2016',118; EXEC Load_VideoGame'Overwatch','07/23/2016','08/10/2016',119; go --drop procedure load_KudzoInc_Data;--go-- #3 Stored Procedures GOcreate procedure load_KudzoInc_Data@LastName varchar(20) OUTPUT,@FirstName varchar(20) ,@MI varchar(11),@DOB varchar(30),@Job_Title varchar(120) OUTPUT,@Phone_Number varchar (38) ,@StreetName varchar(40),@City varchar(43),@State varchar(43),@ZipCode varchar (5),@order_ID intASBEGINdeclare @emp_ID int;declare @check int;set @emp_id =(select emp_ID from KudzoIncWHERE LastName=@LastName and FirstName = @FirstName);if @emp_ID is NULLBEGININSERT INTO KudzoInc(LastName,FirstName,MI,DOB,Job_Title,Phone_Number,StreetName,City,State,ZipCode,order_ID) VALUES (@LastName,@FirstName,@MI,@DOB,@Job_Title,@Phone_Number,@StreetName,@City,@State,@ZipCode,@order_ID);set @emp_ID = (SCOPE_IDENTITY());ENDset @check= (select 1 from KudzoInc where FirstName=@FirstName and MI=@MI and LastName=@LastName and DOB=@DOB and Phone_Number=@Phone_Number and StreetName=@StreetName and City=@City and State=@State and ZipCode=@ZipCode);END;--INSERT DATA INTO KUDZOINc include the order id to keep tracking of shippinggo exec load_KudzoInc_Data 'Timbo','Frank','S','07/19/1978','Manager','818-430-0279','4361 Edsel Road','Sherman Oaks','CA','91403','100';exec load_KudzoInc_Data 'Rojas','Kathleen','C','01/25/1982','Cashier','319-868-6911','896 Progress Way','Morning Sun','IA','52640','102';exec load_KudzoInc_Data 'Hamblin','Patrick','T', '05/02/1988','QC','816-324-0196','2237 Traders Alley','Savannah','MO','64485','108';go --('Danny','A','Parker','07/03/1975','847-644-4220','3668 Vine Street','Chicago','IL','60605'); -- #4 Stored Procedures GOcreate procedure Load_Customer_Orders@LastName varchar(20),@FirstName varchar(20),@MI varchar(11),@DOB varchar(30),@Phone_Number varchar (38),@StreetName varchar(40),@City varchar(43),@State varchar(43),@ZipCode varchar (5),@Quantity varchar(80),@Unit_Price money ,@Order_date date,@Customer_ID int OUTPUT,@Order_ID int OUTPUTAS BEGIN set @Order_ID=(select Order_ID from Orders where Quantity = @Quantity and Unit_Price = @Unit_Price and Order_date = @Order_date); if @Order_ID is null BEGIN insert into Orders(Quantity,Unit_Price,Order_date) values (@Quantity,@Unit_Price,@Order_date); set @Order_ID=(scope_identity()); END set @Customer_ID=(select Customer_ID from Customer where LastName=@Lastname and FirstName=@FirstName AND MI = @MI AND DOB=@DOB AND Phone_Number =@Phone_Number AND StreetName=@StreetName AND City=@City AND State = @State AND ZipCode = @ZipCode ); if @Customer_ID is NULL BEGIN insert into Customer(LastName, FirstName,MI,DOB, Phone_Number,StreetName,City,State,ZipCode) values(@LastName, @FirstName,@MI,@DOB, @Phone_Number,@StreetName,@City,@State,@ZipCode); set @Customer_ID=(scope_identity()); END END;godeclare @Order_ID int, @Customer_ID int;EXEC Load_Customer_Orders 'Doe','Joseph','M','05/28/1983','079-4255-7168','2622 Trinity RD','WHISTON','PA','19405','2','$11.99','02/13/2015',@Customer_ID OUTPUT, @Order_ID OUTPUT;select @Order_ID as 'Order_ID',@Customer_ID as 'Customer_ID';EXEC Load_Customer_Orders 'Sean', 'Mira','J','07/22/1990','215-4255-6161','2622 N 31st','CHESTER','PA','19606','1','$08.99','05/11/2017',@Customer_ID OUTPUT, @Order_ID OUTPUT;select @Order_ID as 'Order_ID',@Customer_ID as 'Customer_ID';GO-- #1 VIEW FOR THE JOIN TABLE (Customer to Item)goCREATE VIEW v_joinTableASSELECT customer.LastName, customer.FirstName,Customer.MI,Customer.DOB,Customer.Phone_Number,Customer.StreetName,Customer.City,Customer.State,Customer.ZipCode,Customer_ID = ISNULL(Customer.Customer_ID,Item.Item_ID),Item.ItembyType, Item.Quantity, Item.Item_IDFROMCustomerFULL JOIN ItemONCustomer.Customer_ID = Item.Item_ID;go --#2 view for ordergo create View v_OrderCustomerasselect Orders.Quantity,orders.Unit_Price,orders.Order_date,Order_ID =ISNULL(Orders.Order_ID,Customer.Customer_ID),Customer.LastName, customer.FirstName,Customer.MI,Customer.DOB,Customer.Phone_Number,Customer.StreetName,Customer.City,Customer.State,Customer.ZipCodefrom Orders inner join Customer on orders.Order_ID= Customer.Customer_ID;go-- #3 view for selectioning Customergo create view v_Customer_contact as select LastName,FirstName,Phone_Number, ZipCode From Customer; go --# 4 view rely item table and movie table go create view v_item as select Movie.Movie_Title, Movie.Fiction_Action,Movie.Animate,Movie.Movie_Rental_Date,Movie.Movie_Returned_date,Movie_ID = ISNULL(Movie.Item_ID,Item.Item_ID),Item.ItembyType, Item.Quantity, Item.Item_IDFROM Movie Full join Item on Movie.Item_ID = Item.Item_ID; go--use master;--drop database KudzoIncRental--GO-- #5 Stored Procedures--(JOIN TABLE Customer to Item)GOcreate procedure load_Customer_Item@LastName varchar(20),@FirstName varchar(20),@MI varchar(11),@DOB varchar(30),@Phone_Number varchar (38),@StreetName varchar(40),@City varchar(43),@State varchar(43),@ZipCode varchar (5),@Quantity varchar(80),@Unit_Price money,@ItembyType varchar (55)ASBEGIN--to avoid repeating same same data in the same tabledeclare @Customer_To_Item int;declare @Item_ID int;declare@Customer_ID int;declare @checkCustomer int;set @checkCustomer= (select 1 from Customer where FirstName=@FirstName and MI=@MI and LastName=@LastName and DOB=@DOB and Phone_Number=@Phone_Number and StreetName=@StreetName and City=@City and State=@State and ZipCode=@ZipCode);if @checkCustomer is nullbegininsert into Customer ( LastName,FirstName, MI, DOB, Phone_Number, StreetName, City,State, ZipCode) VALUES (@LastName,@FirstName,@MI, @DOB, @Phone_Number, @StreetName, @City,@State, @ZipCode) set @Customer_ID=( scope_identity()); INSERT INTO Item ( Quantity,Unit_Price,ItembyType)values( @Quantity,@Unit_Price,@ItembyType);SET @Item_ID = (scope_identity());INSERT INTO Customer_To_Item( Customer_ID, Item_ID)values( @Customer_ID, @Item_ID);SET @Customer_To_Item = (scope_identity());ENDEND;go exec load_Customer_Item 'Wilson', 'John','M','11/02/1990','610-422-7111','360 Tree Lane','Sharon Hill','PA','19110','2', '$3.99', 'VideoGame';exec load_Customer_Item 'Huse','Robert', 'K','12/10/1995','812-743-3820','2792 Heliport Loop','Monroe City','IN','47557','1', '$10.99', 'Movie';exec load_Customer_Item 'Adobe', 'Emmanuel','F','10/08/1991','610-522-6116','120 Sharon Ave','Sharon Hill','PA','19105','2', '$05.99', 'Movie';exec load_Customer_Item 'Tango','Bob', 'G','02/11/1996','412-953-7833','857 Heliport Loop','Jersey City','IN','56157','1', '$11.99', 'Video Game';-- #1 a query baseed store procedure that accepts input and produce results based on the inputgocreate procedure GetOrder@Order_ID intasselect Order_ID, Quantity,Unit_Price ,Order_date,Customer.Customer_ID from Orders JOIN Customer ON Customer.Customer_ID = Orders.Order_ID where Order_ID =@Order_IDGOEXEC GetOrder @Order_ID ='101';-- #2 Stored procedure that loads data into a join table and the two tables it joins. Gocreate procedure loadcustomer@order_id intASBEGINselect Orders.order_id, Customer.Customer_ID, Item.Item_ID from Orders JOIN Customer on Customer.Customer_ID = Orders.Customer_ID JOIN Item ON Item.Item_ID = Orders.Item_ID --WHERE Orders.order_id = @order_idENDexec loadcustomer '100';-- #3 A stored procedure that updates data in your database based on input. gocreate procedure updateKudzoInc@Emp_ID int,@LastName varchar(20),@FirstName varchar(20) ,@MI varchar(11),@DOB varchar(30),@Job_Title varchar(120),@Phone_Number varchar (38) ,@StreetName varchar(40),@City varchar(43),@State varchar(43),@ZipCode varchar (5)asbegin update KudzoInc set LastName = @LastName,FirstName = @FirstName,MI = @MI,DOB = @DOB,Job_Title= @Job_Title,Phone_Number = @Phone_Number,StreetName = @StreetName,City = @City,State = @State, ZipCode = @ZipCode WHERE Emp_ID=@Emp_ID END go exec updateKudzoInc '101', 'Embiid' ,'Joel', 'L','05/12/1994', 'Cashier','215-873-46-23','201 N 44th street','Philadelphia','PA','19104' ; ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download