Final Project Report
Video Rental System
Objective:
Business Rules:
- A store can have one or more employees.
- An employee may or may not manages one store.
- A employee may or may not have one or more employee job history.
- A employee is assigned an user id for login.
- An user id associated with one and only one employee.
- A employee may or may not creates one or more customers.
- A customer can be created by one and only one employee.
- A employee may or may not change one or more rental item types.
- A rental item type can be changed by one and only one employee.
- A rental item type may or may not have one or more rental items.
- A rental item can be of one and only one rental item type.
- A vendor may or may not placed one or more purchase orders.
- A Purchase order can contains one or more Purchase order detail lines.
- A Customer rental table resolves the many to many relationships between the Customer and Rental item table.
- A rental item may or may not be rented by one or more customers.
- A customer may or may not rent one or more rental items.
- And Customer rental history table also resolves the many to many relationships between the Customer and Rental item.
- A customer may or may not have one or more rental items.
- A rental item may or may not have one or more customers.
ER Diagram:
Data Dictionary:
Normalization:
Before Normalization the “CUSTOMER” table contained the “RENTAL RETURN DATE” AND “ITEM RENT PAY”. Since the Customer can rent more than one item this caused data anomalies. There were multiple entries for one customer.
Note: All the table attributes are not included for the ease of description. The required and important attributes are used to show the normalization.
The dependencies before normalization
First Normal Form:
CUS_ID – (CUS_ADDRESS, CUS_PHONE, CUS_NAME)
ITEM_ID – ( ITEM_SHORT_DESC,ITEM_NAME)
CUS_ID+ITEM_ID – (CUS_ADDRESS, CUS_PHONE, CUS_NAME, ITEM_RENT_PRICE, ITEM_NAME, RENTAL_DATE, RENTAL_DUE_DATE)
Second Normal form:
CUS_ID – (CUS_ADDRESS, CUS_PHONE, CUS_NAME)
...This is a preview of the whole essay
Normalization:
Before Normalization the “CUSTOMER” table contained the “RENTAL RETURN DATE” AND “ITEM RENT PAY”. Since the Customer can rent more than one item this caused data anomalies. There were multiple entries for one customer.
Note: All the table attributes are not included for the ease of description. The required and important attributes are used to show the normalization.
The dependencies before normalization
First Normal Form:
CUS_ID – (CUS_ADDRESS, CUS_PHONE, CUS_NAME)
ITEM_ID – ( ITEM_SHORT_DESC,ITEM_NAME)
CUS_ID+ITEM_ID – (CUS_ADDRESS, CUS_PHONE, CUS_NAME, ITEM_RENT_PRICE, ITEM_NAME, RENTAL_DATE, RENTAL_DUE_DATE)
Second Normal form:
CUS_ID – (CUS_ADDRESS, CUS_PHONE, CUS_NAME)
ITEM_ID – ( ITEM_SHORT_DESC,ITEM_NAME)
CUS_ID + ITEM_ID – (RENTAL_DATE, RENTAL_DUE_DATE)
CUSTOMER AND RENTAL ITEM TABLES:
CUSTOMER RENTAL HISTORY:
Third Normal Form:
Since there are no transitive dependencies the third normal form is automatically achieved.
Normalized tables:
Purchase Order table:
The Purchase Order table has fields “ITEM_ID” and “ ITEM_QTY” attributes. Since a purchase order can contain more than one item, each purchase order will have multiple entries.
Similarly the Purchase order has “VEND_NAME” and “VEND_ID”. Each Vendor can place more than one purchase order. There will be repetition of the “VEND_NAME” and other vendor related details. This will cause data anomalies.
Dependencies:
PUR_ORDER_ID - (PUR_ORDER_DATE, PUR_RECEIVED, PUR_RECEIVED_DATE,PUR_ORDER_DETAILS_ID)
ITEM_ID – ITEM_NAME
VEND_ID – VEND_NAME
PUR_ORDER_ID + ITEM_ID+ VEND_ID – ( PUR_ORDER_DATE, PUR_RECEIVED, PUR_RECEIVED_DATE, ITEM_NAME, VEND_NAME, ITEM_QTY,PUR_ORDER_DETAIL_ID)
Second Normal Form:
PUR_ORDER_ID - (PUR_ORDER_DATE, PUR_RECEIVED, PUR_RECEIVED_DATE)
ITEM_ID – ITEM_NAME
VEND_ID – VEND_NAME
PUR_ORDER_DETAIL_ID – (PUR_ORDER_ ID + ITEM_QTY + ITEM_ID )( Transitive dependency)
PURCHASE ORDER:
RENTAL ITEM AND VENDOR:
TRANSITIVE DEPENDENCY:
3rd NORMAL FORM:
Since the PUR_ORDER_DETAIL_ID is dependent on PURCHASE_ORDER_ID the table is formed with the following constraints.
PUR_ORDER_ID - (PUR_ORDER_DATE, PUR_RECEIVED, PUR_RECEIVED_DATE)
ITEM_ID – ITEM_NAME
VEND_ID – VEND_NAME
PUR_ORDER_DETAIL_ID + PUR_ORDER_ ID - (ITEM_QTY + ITEM_ID)
SQL Queries:
User authentication module:
Query to authenticate :
select [emp_id],[is_admin],[user_id],[user_login_id] from [USER] where user_login_id=@uid and user_pwd=@pwd;
@uid= user id
@pwd = password entered
Update user after login:
update [user] set user_last_login=@ulastlogin where user_id=@uid",
@ulastlogin – the current date time
@uid – the current authenticated user
Store management module:
List all stores:
select [store_id] as StoreId,[store_address1] as Address1, [store_address2] as Address2,[store_city] as City,[store_state] as State,[store_zip] as Zip,[store_phone] as Phone ,[store_fax] as Fax,[store_manager] as Manager from [store]
Insert a new store:
insert into [store] store_address1,store_address2,store_city, store_state,store_zip,store_phone,store_fax,store_manager) values (@add1,@add2,@city,@state,@zip,@phone,@fax,@manager);
Update a store:
update [store] set store_address1=@add1,store_address2=@add2, store_city=@city,store_state=@state,store_zip=@zip,store_phone=@phone,
store_fax=@fax where [store_id]=@sid;
Employee module:
Select employees
select [emp_id] as Id,[emp_fname] as FirstName,[emp_lname] as LastName,[emp_ssn] as SSN,[emp_phone] as Phone,[emp_address1] as Address1 ,[emp_address2] as Address2,[emp_city] as City,[emp_zip] as Zip; ,[emp_state] as State,[store_id] as Store from [employee]
Insert employee:
insert into [employee] (emp_fname,emp_lname,emp_ssn, emp_address1,emp_address2,emp_city,emp_state,emp_zip,emp_phone,store_id) values (@fname,@lname,@ssn,@add1,@add2,@city,@state,@zip,@phone,@sid)
@fname
@lname
@ssn
@add1
@add2
@city
@state
@zip
@phone
@sid
Update Employee:
update [employee] set emp_fname=@fname,emp_lname=@lname, emp_ssn=@ssn, emp_address1=@add1,emp_address2=@add2,emp_city=@city,emp_state=@state,emp_zip=@zip,emp_phone=@phone,store_id=@sid where [emp_id]=@empid
@fname", empLNameTextBox.Text.Trim()));
@lname", empFNameTextBox.Text.Trim()));
@ssn", empSSNTextBox.Text.Trim()));
@add1", empAdd1TextBox.Text.Trim()));
@add2", empAddr2TextBox.Text.Trim()));
@city", empCityTextBox.Text.Trim()));
@state", empStateTextBox.Text.Trim()));
@zip", empZipTextBox.Text.Trim()));
@phone", empPhoneTextBox.Text.Trim()));
@sid", Convert.ToInt32(empStoreCBox.SelectedValue)));
@empid", OleDbType.Integer));
command.Parameters["@empid"].Value = empid;
Employee history:
Select for a given employee:
select
[emp_line_num] as [No]
,[emp_comp_name] as CompanyName
,[emp_strt_dte] as StartDate
,[emp_end_dte] as EndDate
,[emp_job_title] as JobTitle
from [employee_history]
where emp_id = @empid;
insert employee history for a given employee:
select max(emp_line_num) as maxid from employee_history where emp_id=@empid group by emp_id";
insert into [employee_history] emp_id,emp_line_num,emp_comp_name, emp_strt_dte, emp_end_dte,emp_job_title,created_date) values (@empid,@lno, @cmp,@str, @end,@title,@crdt);
@empid
@lno
@cmp
@str
@end
@title
@crdt
update employee history for a given employee:
update [employee_history] set emp_comp_name=@cmp, emp_strt_dte=@str, emp_end_dte=@end,emp_job_title=@title where [emp_id]=@empid and [emp_line_num]=@lno
@cmp
@str
@end
@title
@empid
@lno
Vendor
Select
select [vend_id] as Id
,[vend_name] as Name
,[vend_cfname] as FirstName
,[vend_clname] as LastName
,[vend_address1] as Address1
,[vend_address2] as Address2
,[vend_city] as City
,[vend_state] as State
,[vend_zip] as Zip
,[vend_phone] as Phone
,[vend_fax] as Fax
from [vendor]
insert
insert into [vendor] (vend_name,vend_cfname,vend_clname,
vend_address1,vend_address2,vend_city,vend_state,vend_zip,vend_phone,vend_fax) values (@nme,@fname,@lname,@add1,@add2,@city,@state,@zip,@phone,@fax)
@nme
@fname
@lname
@add1
@add2
@city
@state
@zip
@phone
@fax
update
update [vendor] set vend_name=@nme,vend_cfname=@fname, vend_clname=@lname, vend_address1=@add1,vend_address2=@add2,vend_city=@city,vend_state=@state,vend_zip=@zip,vend_phone=@phone,vend_fax=@fax where [vend_id]=@vid
@nme
@fname
@lname
@add1
@add2
@city
@state
@zip
@phone
@fax
@vid
Rental items
Select
SELECT [ITEM_ID] as RentalItemId,[ITEM_NAME] as VideoName, [ITEM_SHORT_DESC] as VideoShortDesc,[ITEM_LONG_DESC] as VideoLongDesc, [ITEM_RUNTIME] as VideoRunTime,[ITEM_LANG] as VideoLanguage, [ITEM_RATING] as VideoRating,[ITEM_RELEASE_DATE] as VideoReleaseDate, [ITEM_TOTAL_STOCK] as TotalStock,[ITEM_CURRENT_STOCK] as CurrentStock, [ITEM_TYPE_DESC] as RentalItemType FROM (RENTAL_ITEM INNER JOIN RENTAL_ITEM_TYPE ON RENTAL_ITEM.ITEM_TYPE_ID=RENTAL_ITEM_TYPE.ITEM_TYPE_ID)
Insert
insert into [rental_item] (item_name,item_short_desc,item_long_desc, item_type_id,item_runtime,item_lang,item_rating,ITEM_RELEASE_DATE,created_date) values (@nme,@sdesc,@ldesc,@itype,@runtime,@lng,@rating,@rdate,@dte)
@nme
@sdesc
@ldesc
@itype
@runtime
@lng
@rating
@rdate
@dte
Update:
update [rental_item] set item_name=@nme,item_short_desc=@sdesc,item_long_desc=@ldesc,
item_release_date=@rdate,item_rating=@rating, item_runtime=@runtime, item_lang=@lng where [item_id]=@itid
@nme
@sdesc
@ldesc
@rdate
@rating
@runtime
@lng
@itid
Purchase order
Select
SELECT PURCHASE_ORDER.PUR_ORDER_ID AS Id, PURCHASE_ORDER.PUR_ORDER_DATE as OrderDate,VENDOR.VEND_NAME as VendorName,PURCHASE_ORDER.PUR_RECEIVED as Received, PURCHASE_ORDER.PUR_RECEIVED_DATE as ReceivedDate
FROM (PURCHASE_ORDER INNER JOIN VENDOR ON PURCHASE_ORDER.VEND_ID = VENDOR.VEND_ID)
Select purchase order detail for a given purchase order
SELECT PURCHASE_ORDER_DETAIL.PUR_ORDER_DETAIL_ID as LineNo,
RENTAL_ITEM.ITEM_NAME as ItemName,
PURCHASE_ORDER_DETAIL.ITEM_QTY as Qty
FROM (PURCHASE_ORDER_DETAIL INNER JOIN
RENTAL_ITEM ON PURCHASE_ORDER_DETAIL.ITEM_ID = RENTAL_ITEM.ITEM_ID)
where PURCHASE_ORDER_DETAIL.PUR_ORDER_ID = @poid order by PURCHASE_ORDER_DETAIL.PUR_ORDER_DETAIL_ID
insert purchase order
insert into [purchase_order] (pur_order_date,vend_id)"
values (@pdate,@vend_id)
@pdate
@vend_id
select max(pur_order_id) as maxid from purchase_order";
insert into [purchase_order_detail] pur_order_id,pur_order_detail_id, item_id,item_qty) values (@poId,@pdId,@itemId,@qty)
@poId
@pdId
@itemId
@qty
Receive purchase order
update [purchase_order] set [pur_received]=@rec,[pur_received_date]=@recdate where pur_order_id=@pid
@rec
@recdate
@pid"
select [item_total_stock],[item_current_stock] from rental_item where item_id=@itemid
update [rental_item] set [item_current_stock]=@cStock ,[item_total_stock]=@tStock where item_id=@itemid";
@cStock
@tStock
@itemid
Purchase order Reports
SELECT VENDOR.VEND_NAME as VendorName, po.PUR_ORDER_DATE as PODate
FROM (PURCHASE_ORDER po INNER JOIN
VENDOR ON po.VEND_ID = VENDOR.VEND_ID)
where po.pur_order_date between #{@stdate}# and #{@endate}#"
@stdate
@endate
customer rental report
SELECT CUSTOMER.CUS_FNAME as CustomerFirstName,
CUSTOMER.CUS_LNAME as CustomerLastName,
CUSTOMER_RENTAL.RENTAL_DATE as RentalDate,
RENTAL_ITEM.ITEM_NAME as ItemName,
RENTAL_ITEM_TYPE.ITEM_TYPE_DESC as ItemDescription
FROM (((CUSTOMER_RENTAL INNER JOIN
CUSTOMER ON CUSTOMER_RENTAL.CUS_ID = CUSTOMER.CUS_ID) INNER JOIN
RENTAL_ITEM ON CUSTOMER_RENTAL.ITEM_ID = RENTAL_ITEM.ITEM_ID) INNER JOIN
RENTAL_ITEM_TYPE ON RENTAL_ITEM.ITEM_TYPE_ID = RENTAL_ITEM_TYPE.ITEM_TYPE_ID)
where CUSTOMER_RENTAL.RENTAL_DATE between #{@stdate}# and #{@endate}#",
@stdate
@endate