Home

Client

Class Page

Teacher's Home

CS Dept.

WSU

---

Video Rental System

Detailed Design

We talked about two different design approaches.

1. A file based system.
2. A database based system.

While trying to decide which approach to take we considered two factors that were most important. Namely the time allowed and our combined experience in the above mentioned approaches. When we took the expertise inventory of our team of developers and did some number crunching to find out how much time is needed for each of the two approaches. We found that taking the file system approach would require time that would go beyond the deadline (the end of semester). The details can be found in the Management Plan . Therefore we decided to go with the database based design.

Our video rental system Hurrah is divided into two portions. The foundation is a database that will hold and store all the information. On top of this database we have a number of classes that provide the interface and the functionality needed to run the system.

Use Cases

Use Case1: Customer rent videotape

1. Customer enters into the video store.
2. Customer goes to the computer directly, and searches the video title and check if it is available.
3. Customer checks the video available, then picks up the video.
4. Customer goes to the counter and checks it out.
5. Customer pays then leave store.

Use case 2: check out video

6) Customer pays then leave store.

Use case 3:Customer reserve the video tape

2. Customer goes to the computer directly, and checks the category to see if the video is available.
3. Customer found all copies of the tape video checked out.
4. Customer goes to the counter and asked employee makes reservation.
5. Customer leaves store.

Use case 4:Customer reserve the videotape by call

1. Customer call the video store to make reservation for particular video
2. Employee record customer ID number, video ID number to make reservation.

Use case 5: late video

1. Customer drop off the video
2. Employee find the video is late, and assesses a fee based on how many days late the video is.
3. Employee calculate the total charge and add to the customer account

Use case 6: late video

1. Customer returns a video and tells employee it is late.
2. Employee accesses customer account and check how many days late the video is.
3. Employee calculates the charge due to late return
4. Customer pays the fee and leaves the store.

Use case 7: Add new video

1. Employee gets information there are no more copies of a particular video in the description field.
2. Some new video are bought by the store
3. Employee re-enter all of that video data
4. Employee put then on the shelf.

Use case 8: Remove extra video

1. Video store employee find there are too many video on the shelf of that type
2. Employee removes the number of copies of that tape, and note there are more copies of that video available in the description.
3. Employee remove the video tape from the shelf

Use case 9: statistic the video rental frequency

1. To statistic the video rental frequency for video titles.
2. To look up the frequency for a time period (in days) and the total times a title has been rented since it was put on the shelf.

Use case 10: teenage rent video tape

1. A teenager enter into store, pick up video " **** " and come near the counter
2. Employee access the account, and check a rental level
3. Employee found this account is hold by teenager's father, video " ****" is at the rental level of primary account, but is out of the teenager's rental level
4. Employee tell the teenager he can't rent videotape this level and explains reason
5. Teenager rent a suitable video and leave store

Use case 11: add new customer

1) To keep track of customers by a customer number.

2) To add new customers name, address, phone #, birthday, primary account holder and rental level by customer ID#.

Use case 12: delete customer

1. According to customer requirement to delete old the old account.

2) To delete the old customers name, address, phone #, birthday, primary account holder and rental level by customer ID#.

3) To add new customers name, address, phone #, birthday, primary account holder and rental level by customer ID#.

Use case 13: check rental and lease status

1. To check which employee in the system at which time.

2) To check which employee lease the which video to which customer.

Use case 14: employees' pass word

1. Use correct employee's password to access the particular employee.

The database will be created using MS Access 97. The database itself is divided into two parts. To see how it is divided look at the Overall Database Design Diagram. First piece of database is to represent the current state of affairs of the video store. An Entity-Relationshipor ER diagram is presented here for the curious mind.

The second piece of the database is for statistical and archival purposes. A diagram is presented to show the structure of the archival database.

General Approaches to Database (DB) Design

Client's requirements imply that designed application program is to fulfill two somewhat distinct tasks.

The first task is to keep track of information about materials that store currently owns (such as what movies are in store, how many copies of each movie is available and so on), about clients of the store, and about which videos are checked out and if video is checked out, who has rented it and which employee checked it out.

Second task is to allow to retrieve from the DB certain statistical/accounting information. For instance, database is required to store information allowing to determine movies rental frequency, that is how many times some particular movie was checked out during specified period of time. Supporting information of such kind implies storage of data about transactions that has already been "committed". Under the transaction here we understand sequence of events starting with video checking out and finishing by checking in and after that video is checked in transaction is considered to be "committed". This means that amount of information stored in DB is increased each time video is checked out and this information is to be stored for indefinitely long period of time.

Our solution here is to introduce in our design Log (or Secondary) subDB which include separate table dissociated from main database (i.e. there are no referential integrity constraints joining Main subDB and Secondary subDB) and storing information about transactions that have been committed. For how long the information is stored and in what way and how it can be used is to be user's decision.

Supporting of Log table dissociated from Main subDB allows, on the one hand, to store (if necessary) information about movies and videos that are not in the store any more, about employees who checked videos out but now quitted the store and so on. On the other hand, tracking of not-committed transaction separately from committed, obviously, allows significantly decreases the size of the table which is to be searched each time when video is checked out or checked in.

Overall Design of Main subDB

Overall design of Main subDB is shown in ER diagram.

Set of entities used in the design:

1. Movie : stores information about movies
2. MovieStar : stores information about actors and actresses
3. Video : stores information about physical tapes on which movies are stored
4. Customer : stores information about clients of the store
5. Employee : stores information about employees
6. Family : stores information about to what family account the client is referred
   The purpose of "Family" entity is to allow to refer group of related clients to joint account.

Relationships and Structural Constraints:

1. Movie/Video relationship:

• cardinality ratio: one to many;
• participation constraints: movie - partial, video - total;

1. Movie/Moviestar relationship:

• cardinality ratio: many to many;
• participation constraints: partial on both sides;

1. Family/Customer relationship:

• cardinality ratio: one to many;
• participation constraints: total on both sides;

1. Reserve relationship on Video/Customer:

• cardinality ratio: one to many;
• participation constraints: partial on both sides;

1. Rentals relationship on Video/Customer/Employee entities (Ternary relationship):

• cardinality ratios:
• 1:1 on Video side, i.e. if video is currently checked out, it can be checked out to only one customer through only one employee.
• 1:N on Customer side, i.e. if customer currently participates in Rentals relationship it implies that he/she rented at least one and up to many videos and could rent them through one or several employees.
• 1:M on Employee side, i.e. if employee currently participates in Rentals relationship it implies that he/she checked out at least one and up to many videos and could checked them to one or several customer.

Relational Design Overview

Relational design of Main subDB

Mapping of ER design to relational design results in relations that are in BCNF and 3NF with the exception for Rentals table which is in 2NF (non-prime "TransID " attribute determines combination value of "Customer" and "Employee" non-prime attributes). Normalization of "Rentals " is under consideration because of related coding and performance issues.

Secondary sub DB relational design

Log table is in 2NF and is designed to be split into two tables as shown on "Archival database" diagram.

Lookup Tables

Class Design

The classes link the user interface to the database. We have a class for each major table: movie, video, customer, family, employee, actor, rental, reserve, and stats. Each class has the table associated with it as its private data. The only way to access these tables is via the public functions of the classes. Each class is independent of one another; no class has access to the private data or functions of another class. We have done this to allow max code reusability so that we can plug any of these classes directly into another program without much, if any, code alteration. This also minimizes bugs caused by functions unintentionally altering tables they shouldn't have access to.

This design allows easy integration of our classes as well. By making each class self-contained and implementation independent, it's just a matter of linking the classes to the appropriate buttons and menus of the interface. And if we happen to find a major error in one class, it will have no effect on any other part of the program. We feel this structure is also easy to visualize (a movie class handling all movie needs, actor class handling all actor needs, etc.), so code maintenance in the future won't involve us having to relearn how our program flows. Maintenance will also be made easier by the fact that changing something in one class won't mean us having to rewrite the other classes as well.

Roughly speaking there is one class for each entity (or table) in our database. An Object Oriented Representation of the classes is available. Each class supports the functionality for the table with matching name in the database.

Class Details

Private: recordset object

Class will manipulate recordset - add new record, delete current, set current, etc., then pass the resulting recordset back to whichever data control is associated with it.

Note: SetRecSet(recordSet) MUST be called before you use this. Otherwise it has no idea which class's recordSet to use. After you are done you MUST use GetRecSet() to return the modified recordSet back to whichever class passed in its recordSet

SearchByString(fieldPosition, string) as RecordSet

Searches field at fieldPosition for the string, returns a recordSet with any matches

SearchByNumber(fieldPosition, number as long int) as RecordSet

Searches field at fieldPosition for the number, returns a recordSet with any matches

GetValueFromRecSet(recordset, selectedItem, fieldPosition, value as variant) as returnCode

Takes in a recordset and the position in the recordset of the selected item, extracts the contents in the fieldPosition field of the current record, returns it in value parameter. FieldPosition is an index into an array of fields. For instance, a recordset with 3 fields - the field positions of these are 0,1,2 (with 0 being the first field in the recordset).

Returncodes for ok or invalidPosition

SetCurByValue(unique value to search for, fieldPosition) as returnCode

Takes in a unique value (most likely something found in the primary key field) and the index position of the field containing the value. Searches the field for the value, sets the current record pointer to that record. Returns ok, noMatch, invalidPosition

GetValueFromCurrent(fieldPosition, value as variant) as returnCode

Returns from the current record the value sitting in the field at fieldPosition. Returns ok or invalidPosition

GetNumOfFields() as int

Returns # of fields in recordSet

SetRecSet(newRecordSet)

Sets the private recordset = to newRecordSet

GetRecSet() as recordSet

Passes private recordSet to caller

Public functions:

Add()

Adds new record to the recordset, opens up a new form with boxes/drop down boxes to add info for a new movie. Sets the data control's recordset = to recordset w/ new blank record. Button for adding actors, button calls Actor.EditMovieList(movieID). Code inside the add movie form includes Validate() which checks for empty input boxes, Save() which saves the user's input into the new record, and Cancel(). After save, clsMovie's recordset is set to the new recordset.

Edit()

Calls Movie.Search() which sets the current pointer = to the movie searched for. Then opens up the same form used for Add(), sets the data control's recordset = to new recordset. Input boxes now show the info for the current movie. Same Validate(), Save(), etc. used.

Delete()

Calls Movie.Search. Shows screen w/ movie name, Delete and Cancel buttons. On delete call GetValueFromCurrent(field position of number of copies out), if it's >0 tell the user he can't delete the movie yet. Otherwise, delete all copies of the movie and then delete the movie.

ShowSearchResults(recordset)

Shows the contents of the recordset passed to it. If there are no records, tells the user that and gives the option to search again or cancel. If there are records, shows them and allows them to click on the movie name they are looking for. On click calls GetValueFromRecSet(), which takes the recordset and the location in the recordset of the movie the user clicked on.

Note: movie, customer, family all have a similar function as this, but this function cannot be shared because the Search Again button needs to link to the search function of the appropriate record (if you are searching for a movie, Search Again needs to call the Movie.Search function).

ChangePrices()

Shows price category (new movie, old, sale price), and price associated with that category. Text boxes to input new price. Buttons for save, cancel. On save new price is written to price table.

Search()

Shows screen with box to type in movie name. Hit ok, strips 1^st 5 chars from entered name, passes them to SearchByName() which returns a recordset of all the matching movies. Call ShowSearchResults(), pass it the recordset returned by searchByName().

ShowInfo(userType)

Uneditable screen comes up showing movie info of current record. If user is a customer then hide the total revenue field.

SetRecSet(newRecordSet)

Sets the private recordset = to newRecordSet

GetRecSet() as recordSet

Passes private recordSet to caller

Public:

AddCopies()

Calls Movie.Search, then Movie.GetValueFromCurrent(field position of movie's ID) to get the movie ID to add copies for. Screen comes up with movie's name, prompts for a number of copies to add for the movie. Buttons for ok and cancel. On ok goes thru a for loop which each time adds a new record to the video table, adds movie ID, and sets status to I (in). Video ID field is autonum so it is automatically generated by Access each time. After the loop is done a box comes up listing the video ID's of the videos just added.

Delete()

Screen comes up asking for video ID. Call ValidID(), if ok then call SetCurByValue(video ID, field position of videoID) and call GetStatus(). If GetStatus() = O (out, checked out), then pop up an error message saying the video can't be deleted because it's currently checked out. Otherwise, if status = R, call Reserve.Remove(videoID) to delete the reservation for the video. Then delete the video.

SetStatus(1 char string) as returnCode

Set the status of the current video to the string. Returncode for ok or invalidStatus

SetRecSet(newRecordSet)

Sets the private recordset = to newRecordSet

GetRecSet() as recordSet

Passes private recordSet to caller

Private:

ValidID(ID) as Boolean

Returns true if there is a video id in the video table with the ID, returns false otherwise.

GetStatus() as string

Returns I for in, O for out, or R for reserved.

Employee:

Public:

SetRecSet(newRecordSet)

Sets the private recordset = to newRecordSet

GetRecSet() as recordSet

Passes private recordSet to caller

Add()

Input screen asks for employee info (name, birthday, etc.). Optional login and password.

Options to save, cancel. On save validate info. Only called by someone logged in as manager.

Edit()

Screen comes up showing drop down list of all employees in system, choose one, input boxes below drop down list fill with selected employee's info. Option to save, cancel.

On save validate info. Should only be called by someone logged in as manager.

ValidateLogin(ID, password)

If there is an ID = to the passed in ID and that ID's password is the same as the passed in password, return ok. Otherwise return invalidLogin.

Delete()

Screen comes up with drop down list of employees. User selects one, that becomes current record. Now hits Delete button, current record is deleted. Options to save, cancel.

ShowInfo()

Screen comes up with drop down list of employees. User selects one, uneditable info for the employee shows under the drop down list. Password field, social security # are not shown (should employee ID be hidden as well?).

Actor

SetRecSet(newRecordSet)

Sets the private recordset = to newRecordSet

GetRecSet() as recordSet

Passes private recordSet to caller

EditMovieList(movieID)

Screen with two lists comes up. List on left is list of all actors in the system. List on right is actors associated with movieID (gotten by querying movieActor table using supplied movieID). Button under all actor list for selecting an actor and adding it to the movie list. Movie list has a button for selecting an actor from the movie list and removing it. This function can be used for both adding actors to a new movie, or editing the list of actors from an existing movie.

Edit()

Screen comes up with drop down box of actors. User selects one, input box below the drop down box gets filled with actor name. User edits name, clicks save or cancel.

Delete()

Screen comes up with drop down box of actors. User selects one, that becomes the current record. User clicks delete. Take the actor id from the current record and delete all occurances of it inside the movieActor table. Now delete the actorID from the actor table. Options for Save, Cancel.

ListMoviesIn()

Screen comes up with drop down list of actors, user selects one, that becomes current record. Take actor id from current record, for each occurrence of it in the movieActor table take the associated movieID and append it to an string, ex:

Initial string:

"select movie.name from Movie where Movie.movieID = ""

You concatenate this:

" or " & movieID

After you've found all occurances of the movieID, query the movie table using the created string.

AddNewActor()

Mini pop up screen asking for first name and last name of actor. Concatenate what they give you into lastname, firstname format and then add it to Actor table. Options are ok and cancel.

Private:

AddToMovie(movieID, actorID)

Add a new record into movieActor table with movieID and actorID

RemoveFromMovie(movieID, actorID)

Remove the record containing movieID and actorID

Family

SetRecSet(newRecordSet)

Sets the private recordset = to newRecordSet

GetRecSet() as recordSet

Passes private recordSet to caller

Add()

Show input screen for info. Drop down box for state, rating. Buttons for save, cancel. On save: validate form data by checking for empty boxes/drop down boxes, check for setting max number of movies to < 0, etc. Strip 1^st five chars and store them in firstLetters field. Write info to db if everything's ok. On cancel: trans.rollback

Edit()

Call Family.Search(), get the current record set to the family you want to edit. Now the add screen comes up populated with info from current record. Same form/code used for both Add and Edit.

Delete()

Call Family.Search(), get current record set to family to delete. Call GetTotalVidsOut() to see if they have any movies out, if they do then don't delete the family and show an error message. If they don't have anything out, delete current record. If referential integrity and cascade delete are checked, all customers of family will be automatically deleted.

ShowSearchResults(recordset)

Shows the contents of the recordset passed to it. If there are no records, tells the user that and gives the option to search again or cancel. If there are records, shows them and allows user to click on the family name they are looking for. On click calls GetValueFromRecSet(), which takes the recordset and the location in the recordset of the movie the user clicked on. Buttons for Search Again, Cancel, Add New Family.

GetTotalVidsOut() as int

Returns # videos out for current family (just returns totalVidsOut field).

GetMaxVidsAllowed() as int

Returns max # videos current family can rent (just returns maxNumOfMovies field).

These two Gets will be useful for calculating the max # videos a customer can rent.

Search()

Prompt for either family last name or phone #, pass input to Base class's search functions. Use base class to search appropriate field, return results, call Family.ShowSearchResults to let user click on family to set that as current record.

ShowInfo()

Shows info for current family (phone #, address, total videos out, etc.). Also has list of all customers associated with this family. Shows name of primary account holder as well.

Customer

Add()

Input screen comes up with text boxes for entering input. Drop down boxes for max rating. All boxes and drop down lists are disabled (greyed out) until a variable called custFamID is set (this variable is declared inside the add form). This holds the family to associate the customer with. At top of screen is button called Get Family, which calls Family.Search(). Now how to set custFamID to the customer's family?...

ShowSearchResults(recordset)

Shows the contents of the recordset passed to it. If there are no records, tells the user that and gives the option to search again or cancel. If there are records, shows them and allows them to click on the customer name they are looking for. On click calls GetValueFromRecSet(), which takes the recordset and the location in the recordset of the movie the user clicked on. Use Base class to set the current customer = to selected one. Buttons for Search Again, Cancel.

Edit()

Call Customer.Search(), use Base class and all to set current Customer = to searched for customer. Now use Add's form to show text boxes populated with info from current customer. All validation and save functions are same as Add()'s.

ValidID(custID) as Boolean

Returns true if custID is in customer table, false otherwise

Delete()

Call Customer.Search(), use Base class to set current customer to one you want to delete. Now show pop up form showing customer name, button for delete, cancel. On delete, delete the current record.

ShowInfo()

Shows uneditable info about current customer (name, max ratings, etc.).

Rental

SetRecSet(newRecordSet)

Sets the private recordset = to newRecordSet

GetRecSet() as recordSet

Passes private recordSet to caller

CheckOutVideo()

Screen comes up prompting for customer ID, validates it, shows customer & family info. Checks to see if customer has late fees, if so then add them to subtotal of order. Check for max # movies this customer can check out. Employee enters videoID, if valid then video/movie info comes up. Check to see if customer's rating level is high enough to rent this movie. If no then display an error message and clear out video info, keeping customer info on screen. If rating level is high enough then proceed to check it out.

Details of checkout screen:

Private vars to the form:

LateFees

holds latefees of current customer, better than accessing cust's latefees field twice (to check for fees and then to add them to the subtotal), plus it can be used for printing the receipt. Initially set to 0, and reset to 0 after each transaction is over

TransID

Transaction id of current transaction. Initially set to -1, and reset to -1 after each transaction. Used to see if current movie being checked out needs a new transid generated in the log & rent tables. If transid != -1 then the transid is the id for all the movies being checked out by this customer.

Screen loads up w/ boxes:

CustID

Linked to famdb, all uneditable:

Fname

Lname

Maxrating

Numvidsallowed

Vidid

Linked to moviedb, all uneditable:

Moviename

Price

Rating

Category

Daysallowedout

Subtotal

Buttons

Another

Done

Cancel

Exit

Boxes tied to db should NOT automatically update the db, disable the update feature on the databound control for these boxes

Initially, all boxes greyed out except custID. Buttons greyout out too. When a customer id is entered, do a seek on custdb to see if there is a match. If not, pop up error message, empty out custid box, leave cursor sitting in box. If there is a match, take the familyid out of the record and do a seek on the familydb. If there is no match for the familyid (shouldn't ever happen), pop up a generic error message, call the cancel() button. Enable the family boxes, populate them with the values of the currfam record. Set the latefees var = to currcust's latefees.

Search button next to custid box, call customer.search. Pass back the custid of newly created cust? Probably not.

Enable subtotal, set value to 0. Check the latefees var to see if latefees > 0, if so then pop up a box saying the amount of latefees, buttons to pay now or pay later. Pay now adds the latefees to subtotal and sets the currcust's latefees to 0 in the custdb. Pay later just closes the pop up screen. Or better to just have a Pay Now? Question and vbyes and vbno, may run faster.

Trans.Record. Now enable the vidid box. You probably want a function that takes in a number of boxes and uses that to enable all the boxes, same thing for disabling them. Validate videoid, if no match then pop up generic message, don't touch customer info, empty videoid box, set cursor to vidid box. If match, then get the movieid from the vidcurrrecord and seek to its position in movieid. Enable rest of boxes. Enable/disable function should take two params, first box in array to work with and last box to work with. Populate boxes with info from currmoviedb. Enable buttons. Immediately check video's rating against max rating of customer, if rating is too high then pop up an error message, ask if Ok to rent or not. If ok, do nothing. Otherwise empty out video boxes.

On Another:

Don't touch customer boxes. Empty out video boxes, add price of movie to subtotal. Check transID, if it's -1 then create a new record in log1, write in custID, empID, date out. Set transID var in form to the newly created transID. Add new record to log2, using transid var. Now create a new record in rentdb using the transID var, videoid, etc. Set video status to out. Increment # of vids family has out, # vids cust has out, decrement # in max allowed to rent box on rent screen. Decrement # of copies available in movie table.

On done:

Change mouse cursor to hourglass. Show a "print receipt" form. Call Another's button's code. Now to print the receipt (rental.printreceipt()):

Print current date, current time, transID, empID, custID. You know the currRecord in the rental table is the last one inserted, so it must be for the current customer. Do a while loop that starts with the last record, prints video ID (how to get movie name?), date due, amount paid, end line, do until the next record's transID is different

Do

Print video id, date out, date due,...

Recordset.moveprevious

Until recordset[transid] <> transID of current transaction (in transID var)

Trans.commit

On cancel

Trans.rollback

On exit

Hide screen (don't unload because rent will be used often enough)

CheckIn()

Details of CheckIn():

Check in:

Private form vars:

TransID

Used to see the transID for video being checked in

LateFee

Holds calculated late fee, initially set to 0

Input box for vidID.

Show box for cust ID, customer first + last name, date out, date due

Validate video id by calling video.validID(videoID), should return true or false. If false then pop up error message, clear out video ID box. If ok, then do a seek on the rent table for the video ID, if no match then pop up error saying movie isn't checked out. If match then set the transID var = to transID of matching record. Seek on cust table using custID from curRecord in rent table. Seek on log2 table using transID. Write date in. Show customer information, date out & in in text boxes.

Now calculate late fee:

DateDue = dateAdd(date out + numOfDaysAllowed)

DaysLate = DateDiff(CurrDate - DateDue)

If daysLate > 0

LateFee = daysLate * pricePaid (pricepaid in log2's recordset[amountPaid] of curRec)

CurrCust's lateFee += lateFee (in case they have late fees from before)

Delete curRecord in rent table. Increment total copies available for movie using movieId from log2. Decrement # copies out for family and for customer (write functions to do this?). Set video's status to In.

Reserve

ReserveVideo()

Pop up screen asking for videoID, customer ID, drop down box holding time increments (probably 1/2 hour, 1 hour, 1 1/2 hour, 2 hour, etc...). Employee chooses length of time from the box, that time is added to the current system time and written to expireTime field in reserve table. Set the status for the video (in video.SetStatus) to R.

UnreserveVideo()

Pop up screen asking for video ID. Call video.validID(), if ok then set the status for the reserved video to I (in) and delete the reservation from the reserve table.

GetExpiredList() as recordset

Returns a recordset holding the videoID's of the videos whose expireTime is < current system time (ex: if expireTime is 4pm and current time is 5 pm, then the rental has expired).

ShowExpiredList()

Pop up screen with scrollable list of videos passed to it by GetExpiredList().

Stats

WriteToPrimaryLog(transID, custID, empID)

Check transID. If -1 then create new transID in Primary Log, write info there. Set transID var to newly created transID. Write needed values (including current date) to primary log.

WriteToSecondaryLog(transID, videoID, amountPaid, movieID)

Write info to secondary log table.

WriteDateIn(transID, need videoID?)

Fill date in field with current date

MovieFrequency(movieID, numOfDays) as int

Search through log2 and count # of occurances of movieID that happen between currDate and currDate - numOfDays. Return count.

Click to return to

Home Page	Client Page	Class Page	Teacher's Home	CS Dept.	WSU