New as at Feb 15
2002: Opening screen, task selector form, reports module
General changes
introduced on Feb 15th 2002:
visual appearance of forms up-dated, plus other detailed changes as
noted elsewhere
VIDEO RENTAL STORE
SIMULATION–
- PROJECT DESCRIPTION
CONTENTS.
1. PROJECT
REQUIREMENTS.
2. SYSTEM
DESIGN.
3. APPLICATION DESCRIPTION.
1. PROJECT REQUIREMENTS.
These can be summarized as follows: -
1.1. General Demonstrator project involving
a fully automated information system for a video rental store, which also sells
confectionery products (chips, popcorn, sweets, cigarettes and cigars. The
store works based on a membership system in which new customers must first
register as members before renting any videos or purchasing any confectionery
products.
It includes some of the major functions which would be needed in a real store application.
1.2.
Context and Scope. This is summarised in the diagram below:-
2. SYSTEM DESIGN
2.1 GENERAL.
All the information is stored in the system in
the form of tables to which records are added or deleted, or up-dated, at run
time.
Some of the tables have primary and foreign
keys - according to where they fit in the overall hierarchy for the application
as a whole, as defined in the Entity Relationship Diagram (link at bottom of
this page).
The Personal Oracle 7 version used for building this application does not accept dates with 4-digit year values in DATE fields in tables.
On the other hand this application DOES
accept dates with 4-digit year values.
This is taken care of by additional columns in all tables which have to store dates,
in conjunction with the functions and procedures in the DATES.pll library
which is attached to all form modules having to display dates or process
them in any way.
The list of tables, along with a brief description
of the functions of the table and the name of its associated Oracle form module
for each one, is as follows:-
Table Name Functions Associated
Oracle form module
Customers Stores
customer names , contact info , enrolment date CLIENTS
Employees As for
Customers plus pay rate, hire & termination dates, notes EMPLOYEES
Security Level Stores the
security levels recognised for the different levels of SECLEV
store
employees
Security Stores
security level designation, user names and passwords None at present
for
all employees
Suppliers Stores
info for confectionery product suppliers – name and SUPPLIER
contact info
Movies Stores
info about the movie titles in the store – actors, author, MOVIES
subject,
number of copies, date first received in the store; plus id’s
(foreign keys) for distributor, rating, type,
price group
Distributor Stores
info for movie distributors – name and
contact info DISTRIBS
Movierating Stores
recognised audience categories e.g parental guidance MOV-RATI
X – rated etc.
Videocategories Stores
recognised type descriptions for movies
e.g comedy, VIDCATS
action,
documentary etc
Videosections Stores the
prices for the recognised categories in terms of VID_SECS
“new release”,
“regular” etc.
Moviecopies Stores info
for each copy of every movie title – copy number, MOVICOPS
whether
rented out, condition; plus id’s (foreign keys) for title number
and
product id. The MOVICOPS form module is
used for registering
additional
copies of titles already in the store.
Confections Stores
details of the confectionery products stocked – name, CONFECTS
number
in stock, cost price, sell price, number on order,
re-order
level; plus id (foreign key) for supplier, product id.
Products This is
used for storing all the allocated product id’s for both movies None
and confectionery products. In the application
as a whole, each copy
of
every movie for rental plus every confectionery product for sale is
assigned a unique product id.
Invoices This
stores just general info about each purchase transaction i.e. None
the
total amount, date, paid or un-paid; plus id’s (foreign keys)
for
the customer and the employee involved
Items Each individual invoice has many items. An
individual item in an ITEMS
invoice can be one of four basic transaction
types i.e. a movie rental,
movie return, confectionery product purchase,
confectionery product
return. For each such item, the relevant information
is entered and
stored (for example, for a movie rental: the
product id, number of days,
price per day, total, title); plus id (foreign
key) for the type of transaction.
See also “Movierentals” below.
Transactiontypes Stores the transaction types recognised in the system (see also “Items” TRANTYPE
above).
Movierentals Stores
the info about each movie currently rented out – when rented, None at present
rental
days paid for, whether currently marked as late or not; plus id’s
(foreign
keys) for invoice and item. Obviously, a multitude of reports
based on current rentals is possible.
A similar multitude of reports based on the
ITEMS table, described
above, could provide the cumulated information
for the months, years
and decades of business since the store started
operations, or for any
selected time period
Overdue Stores
info about movies which are late i.e. out after more days than paid NOVERDUE
for. This gets updated automatically whenever
the user opens the
NOVERDUE
form module to retrieve info for follow-up, such as phoning a
customer who has a movie which is late if
another customer wants the same
title when there are no more in the store.
Defective_Products This stores info about confectionery products returned for defects. None at present
New section on REPORTS
module added Feb 15th 2002:-
Report_Index These store reports about movie rentals between defined dates and times REPORTS
Movie_Reports of the day, with the dates and times being entered using drop-down list
boxes (not possible with Oracle Reports 2.5). Currrently (as at February
15th 2002) three types of report are available i.e. rentals by title, rentals
by category (comedy, action, horror etc.) and rentals for all movies. With
this system the user first creates the report in the REPORTS form module,
then when running Oracle Reports 2.5 basically all the user has to do is to
SELECT * FROM REPORT_ID …., after viewing the list in Report_Index
(using a pop-up LOV in the REPORTS form module) to select the correct
ID number. Having done this the user then adjusts the layout and writes the
format triggers in Oracle Reports, in the usual way, before running the report
and saving it.
There are some additional tables, which are
used exclusively for temporary storage of information at run time; these
are shown in the Entity Relationship Diagram (link at bottom of this page
In the above table, each of the form modules
named in the RIGHT hand column has one block, which in turn “owns” base
table named in the LEFT hand column.
For example, the CLIENTS form module has one block with Customers as the base
table.
2.2 OPERATION OF THE FORM MODULES; CODE THEREIN.
Detailed information, which can be accessed
either by clicking on the links below or
from the Entity Relationship
Diagram, is currently available for the following form modules:-
Some of the form modules manipulate add records
to and / or manipulate more than one table – for instance the ITEMS form
module, which has one block with ITEMS as the base table, adds records to the
INVOICES , MOVIERENTALS and ITEMS tables and also updates the MOVIECOPIES,
OVERDUE and CONFECTIONS tables.
In the same way, a particular table “owned” by
the block in a form module may be manipulated by additional form modules when
these are used; an example which is now obvious is the MOVIECOPIES table which
may have records added to it when the MOVIES form module is used (to add more
movie titles to the system) and when the MOVICOPS form module is used to add
more copies to the system, for a movie title which is already in the system.
Other tables -
i.e. those not “owned” by a base table block attached to a form module
- get manipulated by other form modules. For instance, the
Products table gets a new record added to it whenever a new confectionery
product is entered into the system through the
CONFECTS form module, or when a new movie copy is added through the
MOVICOPS form module. In these instances, having the table “owned” by a block in a form module serves no useful
purpose - because there is no purpose
in displaying the records. What is the use in
having a form to display product id’s for the sake of it - when the same
information is available, when wanted, elsewhere?
3.
APPLICATION DESCRIPTION / USER GUIDE
This application is a
working application, set up and available for demonstration at any time on the
author’s laptop computer.
New para. about
VIDEOS and SELFORM modules added Feb 15 2002
The user opens up the VIDEOS form module (the welcome screen),
which closes automatically after about 1 second and then automatically opens
the SELFORM form module. The SELFORM
module displays explanatory text, and push-buttons which the user clicks to
access all the other functions in the application.
The information on this web
site is intended to be a general introduction, augmented by some detail
concerning the parts which the author considers to be the most important and
which fulfill certain important “core”
functions – for instance, the Entity
Relationship Diagram. With respect to the form modules, the ITEMS form module
performs the major transaction processing function, is the core unit for
querying to get reports, and is the
most complex. Most of the other parts
are relatively simple and can be demonstrated working; further, the ITEMS form
module would be no use if these other
parts did not work.
It is not proposed to undertake a detailed description of every single part of the
application here, with the idea that
the reader should be obligated to spend time reading it - when he / would be able see and understand
the main features in the course of a demonstration lasting 15 minutes or so, in
addition to seeing proof that it does what the author claims it does.
NOTE: For CLIENTS form module, click
on the link in the CUSTOMERS table
For
ITEMS form module, click on the
link in the ITEMS table
For NOVERDUE form module,
click on the link in the OVERDUE table
For REPORTS form module,
click on the link in the REPORT_INDEX table