JDB SQL Reference

Appendix D. Videobiz Database

This section describes the database tables in the videobiz database. The following information is listed 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

Videobiz Schema

The following tables outline the database tables in the videobiz database.

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

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

**Table D-3 Customers table**
Column Name	Data Type	Length	Status	Description	Sample
`cust_id`	integer		primary key not null	Unique number code for each customer.	2
`last_name`	char	25	not null	Customer's last name.	Scott
`first_name`	char	20	not null	Customer's first name.	Alexander
`address1`	char	40		Customer's address.	5601 Wilson
`address2`	char	40		Additional address information.
`city`	char	25		City customer lives in.	Geneva
`state_prov`	char	10		State/Province.	NY
`postal_code`	char	10		Postal code.	10234
`phone`	char	15		Customer's telephone number.	515-221-4111
`cc_code`	char	4		Code for type of credit card. List in codes table.	VISA
`cc_number`	char	16		Number on credit card.	4000...
`cc_exp_month`	integer			Month of credit card expiration. 1=January, 12=December.	2
`cc_exp_year`	integer			Year of credit card expiration (4 digits).	1994
`member_date`	datetime			Date when customer became a member.	1991/05/30 00:00:00
`member_status`	char	1	not null	Current status of membership. Values include: (A)ctive, (I)nactive, (F)requent renter.	A
`num_rentals`	integer		not null	Total number of rentals customer has made.	105
`rent_amount`	float		not null	Total amount of money paid by customer.	175.00
`notes`	char	254		Comments about customer.	Likes ADV videos.

**Table D-4 Flag table**
Column Name	Data Type	Length	Status	Description	Sample
`yesno`	char	1		Flag used in the sample application.	Y

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

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

**Table D-7 Roles table**
Column Name	Data Type	Length	Status	Description	Sample
`title_id`	integer		primary key foreign key not null	Unique number code for each video title.	33
`actor_id`	integer		primary key foreign key not null	Unique number code for each actor.	87
`role`	char	40		Role the actor plays in the video.	Marianne

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

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

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

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