Video Rental System
Overall Design
This document briefly describes our design for the video rental system Hurrah that we are developing for our clients Happy Time Video Rental.
Two possible choices for the design of Hurrah that we discussed and analyzed were
- A file based system.
- 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.
Database Design Overview
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-Relationship or ER diagram is presented here for the curious mind.
The second piece of the database is for statistal 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:
- Movie : stores information about movies
- MovieStar : stores information about actors and actresses
- Video : stores information about physical tapes on which movies are stored
- Customer : stores information about clients of the store
- Employee : stores information about employees
- 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:
- Movie/Video relationship:
- cardinality ratio: one to many;
- participation constraints: movie - partial, video - total;
- Movie/Moviestar relationship:
- cardinality ratio: many to many;
- participation constraints: partial on both sides;
- Family/Customer relationship:
- cardinality ratio: one to many;
- participation constraints: total on both sides;
- Reserve relationship on Video/Customer:
- cardinality ratio: one to many;
- participation constraints: partial on both sides;
- 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
Set of auxiliary tables will be used to support Main subDB. These tables are designed to contain lists of predetermined value for some attributes of relations constituting Main subDB.
These tables are shown on "Lookup Tables" diagram.
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. An Object Oriented Representation of the classes is available. 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 maximum 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.
Click to return to
| Home Page |
Client Page |
Class Page |
Teacher's Home |
CS Dept. |
WSU |
|
|
|
|
|
|
