Reports

Appendix B. Reports Sample Database

The documentation uses the vbizplus JDB database to illustrate Panther's reports facility. This appendix describes the tables in the vbizplus database by listing the following information for each table:

Column names.
Data type of each column.
Length of character columns.
Status of column detailing whether it is a primary or foreign key and whether it can accept null values.
Description of the data to be entered into the column.
Sample entry.

Vbizplus Schema

The following tables outline the database tables in the vbizplus database. A diagram of the schema appears in Figure B-1.

**Table B-1 Actors table**
Column name	Data type	Length	Status	Sample	Description
actor_id	integer		primary key not null	87	Unique number code for each actor
last_name	char	25	not null	Ullmann	Actor's last name or only name
first_name	char	20		Liv	Actor's first name

**Table B-2 Codes table**
Column name	Data type	Length	Status	Sample	Description
code_type	char	32	primary key not null	genre_code	Type of code. Corresponds to column name
code	char	4	primary key not null	ADV	Code value
dscr	char	40		Adventure	Description of code value

**Table B-3 Customers table**
Column name	Data type	Length	Status	Sample	Description
cust_id	integer		primary key not null	10	Unique number code for each customer
last_name	char	25	not null	Stephens	Customer's last name
first_name	char	20	not null	Darrin	Customer's first name
address1	char	40		937 Brewster	Customer's address
address2	char	40		Apt 7	Additional address information
city	char	25		Geneva	City customer lives in
state_prov	char	10		NY	State/Province
postal_code	char	10		10234	Postal code
phone	char	15		515-555-5668	Customer's telephone number
cc_code	char	4		MAST	Code for type of credit card. List in `codes` table
cc_number	char	16		5000...	Number on credit card
cc_exp_month	integer			10	Month of credit card expiration. 1=January, 12=December
cc_exp_year	integer			1997	Year of credit card expiration (4 digits)
member_date	datetime			1996/01/19 00:00:00	Date when customer became a member
member_status	char	1	not null	A	Current status of membership. Values include: (A)ctive, (I)nactive, (F)requent renter
num_rentals	integer		not null	4	Total number of rentals customer has made
rent_amount	float		not null	11.50	Total amount of money paid by customer
notes	char	254		Notify for ADV videos	Comments about customer

**Table B-4 Distributors table**
Column name	Data type	Length	Status	Sample	Description
distrib_id	integer		primary key not null	1	Unique number code for each distributor
distrib_name	char	20		Geneva	Distributor's name
address1	char	40		4201 Washington St	Distributor's street address
address2	char	40		NULL	Additional address information
city	char	25		Geneva	City in which the distributor is located
state_prov	char	10		NY	State/Province
postal_code	char	10		10234	Postal code
phone	char	15		515-555-7232	Distributor's telephone number

**Table B-5 Orders table**
Column name	Data type	Length	Status	Sample	Description
order_num	integer		primary key not null	1001	Unique number code for each order
distrib_id	integer		foreign key not null	1	Distributor who placed the order
order_date	datetime			1996/01/29 00:00:00	Date the distributor placed the order
ship_date	datetime			NULL	Date the order is shipped to the distributor
po_num	char	15		D1456	Purchase order number for the order

**Table B-6 Order_items table**
Column name	Data type	Length	Status	Sample	Description
order_num	integer		primary key foreign key not null	1001	Unique number code for each order
title_id	integer		primary key foreign key not null	78	Unique number code for each video title
price	float			23.50	Price of the video
qty	integer		not null	8	Quantity ordered
order_flag	char	1		O	Flag indicating the status of the order: O)rdered, B)ack-ordered, C)ataloged, S)hipped

**Table B-7 Pricecats table**
Column name	Data type	Length	Status	Sample	Description
pricecat	char	1	primary key not null	N	Unique letter code for each category
pricecat_dscr	char	40		New Release	Category description
rental_days	integer			2	Number of rentals days available in this category
price	float			2.50	Amount to be paid for rentals in this category
late_fee	float			2.00	Amount of late fee for rentals in this category

**Table B-8 Rentals table**
Column name	Data type	Length	Status	Sample	Description
cust_id	integer		primary key foreign key not null	3	Code identifying the customer for this rental
title_id	integer		primary key foreign key* not null	69	Code identifying the video title for this rental
copy_num	integer		primary key foreign key not null	2	Copy of this video being rented
rental_date	datetime		primary key not null	1996/02/07 19:56:00	Date/time the video was rented
due_back	datetime		not null	1996/02/09 00:00:00	Date the video is due back to avoid late fee
return_date	datetime			NULL	Actual date/time the video was returned; NULL until then
price	float		not null	2.50	Rental fee for video at time rental was made
late_fee	float		not null	2.00	Late fee per day for video at time rental was made
amount_paid	float		not null	2.50	Total amount paid on this rental as of current date
rental_status	char	1	not null	C	Status of rental. Values include (C)urrently out, Back and (P)aid, (B)alance is due
rental_comment	char	76		NULL	Comments about rental, if any
modified_date	datetime		not null	1996/02/07 19:56:00	Date this record was last modified
modified_by	integer		foreign key not null	2	Last user who modified record
*title_id is a foreign key from the tapes table, in combination with copy_num.

**Table B-9 Roles table**
Column name	Data type	Length	Status	Sample	Description
title_id	integer		primary key foreign key not null	33	Unique number code for each video title
actor_id	integer		primary key foreign key not null	87	Unique number code for each actor
role	char	40		Marianne	Role the actor plays in the video

**Table B-10 Tapes table**
Column name	Data type	Length	Status	Sample	Description
title_id	integer		primary key foreign key not null	33	Unique number code for each video title
copy_num	integer		primary key not null	1	Number identifying the copy of this video
status	char	1	not null	O	Code specifying the current status of this copy. Values include (A)vail able, (R)eserved, (O)ut, (I)nactive
times_rented	integer		not null	53	Number of times this copy has been rented

**Table B-11 Titles table**
Column name	Data type	Length	Status	Sample	Description
title_id	integer		primary key not null	33	Unique number code for each video title
name	char	60	not null	Scenes from a Marriage	Video title
genre_code	char	4		CLAS	Code specifying the video category. Values include: ADLT, ADV, CHLD, CLAS, COM, HORR, MUS, MYST, SCFI, TV, VID. See `codes` table
dir_last_name	char	25		Bergman	Director's last name
dir_first_name	char	20		Ingmar	Director's first name
film_minutes	integer			168	Length of the video
rating_code	char	4		PG	Rating code given the film by the Motion Picture Association of America. Values include: G, PG, PG13, R, NC17. See `codes` table
release_year	integer			1974	Year the film was released to movie theatres
pricecat	char	1	foreign key not null	G	Code taken from the `pricecats` table specifying the price category
order_price	float			28.00	Price to use to order the video
quantity_avail	integer			2	Number of video copies available

**Table B-12 Title_dscr table**
Column name	Data type	Length	Status	Sample	Description
title_id	integer		primary key foreign key not null	33	Unique number code for each video title
line_no	integer		primary key not null	1	Line number of the video description
dscr_text	char	76		Relationship of a couple...	Description of the video

**Table B-13 Users table**
Column name	Data type	Length	Status	Sample	Description
user_id	integer		primary key not null	3	Unique number code for each employee/ system user
logon_name	char	8		jack	User's logon name
password	char	8		go	User's password
last_name	char	25		Ryan	User's last name
first_name	char	20		Jack	User's first name
customer_flag	char	1		Y	Y allows access to customer subsystem
admin_flag	char	1		N	Y allows access to administrative subsystem
marketing_flag	char	1		Y	Y allows access to marketing sub system
frontdesk_flag	char	1		Y	Y allows access to front desk sub system

Figure B-1 Diagram of the vbizplus database.