| "Orange Whip Studios" is a fictitious company used in the examples throughout this book. The various examples and applications use a total of 12 database tables, as described in the following sections. The Actors Table The Actors table contains a list of all the actors along with name, address, and other personal information. Actors contains the columns listed in Table G.1. Table G.1. The Actors TableCOLUMN | DATATYPE | DESCRIPTION |
|---|
ActorID | Numeric (Auto Number) | Unique actor ID | NameFirst | Text (50 chars) | Actor's (stage) first name | NameLast | Text (50 chars) | Actor's (stage) last name | Age | Numeric | Actor's (stage) age | NameFirstReal | Text (50 chars) | Actor's real first name | NameLastReal | Text (50 chars) | Actor's real last name | AgeReal | Numeric | Actor's real age | IsEgomaniac | Bit (Yes/No) | Egomaniac flag | IsTotalBabe | Bit (Yes/No) | Total babe flag | Gender | Text (1 char) | Gender (M or F) |
Primary Key Foreign Keys The Contacts Table The Contacts table stores all contacts, including mailing list members and online store customers. Contacts contains the columns listed in Table G.2. Table G.2. The Contacts TableCOLUMN | DATATYPE | DESCRIPTION |
|---|
ContactID | Numeric (Auto Number) | Unique contact ID | FirstName | Text (50 chars) | Contact first name | LastName | Text (50 chars) | Contact last name | Address | Text (100 chars) | Contact address | City | Text (50 chars) | Contact city | State | Text (5 chars) | Contact state | Zip | Text (10 chars) | Contact ZIP | Country | Text (50 chars) | Contact country | Email | Text (100 chars) | Contact email address | Phone | Text (50 chars) | Contact phone number | UserLogin | Text (50 chars) | Contact user login | UserPassword | Text (50 chars) | Contact login password | MailingList | Bit (Yes/No) | Mailing list flag | UserRoleID | Numeric | ID of the associated role |
Primary Key Foreign Keys The Directors Table The Directors table stores all movie directors. Directors contains the columns listed in Table G.3. Table G.3. The Directors TableCOLUMN | DATATYPE | DESCRIPTION |
|---|
DirectorID | Numeric (Auto Number) | Unique director ID | FirstName | Text (50 chars) | Director first name | LastName | Text (50 chars) | Director last name |
Primary Key Foreign Keys The Expenses Table The Expenses table lists the expenses associated with listed movies. Expenses contains the columns in Table G.4. Table G.4. The Expenses TableCOLUMN | DATATYPE | DESCRIPTION |
|---|
ExpenseID | Numeric (Auto Number) | Unique expense ID | FilmID | Numeric | Movie ID | ExpenseAmount | Currency (or numeric) | Expense amount | Description | Text (100 chars) | Expense description | Expense Date | Date Time | Expense date |
Primary Key Foreign Keys The Films Table The Films table lists all movies and related information. Films contains the columns in Table G.5. Table G.5. The Films TableCOLUMN | DATATYPE | DESCRIPTION |
|---|
FilmID | Numeric (Auto Number) | Unique movie ID | MovieTitle | Text (255 chars) | Movie title | PitchText | Text (100 chars) | Movie one-liner | AmountBudgeted | Currency (or numeric) | Movie budget (planned) | RatingID | Numeric | Movie rating ID | Summary | Memo (or text) | Movie plot summary | ImageName | Text (50 chars) | Movie poster image filename | DateInTheaters | Date Time | Date movie is in theaters |
Primary Key Foreign Keys The FilmsActors Table The FilmsActors table associates actors with the movies they are in. FilmsActors contains the columns in Table G.6. Retrieving actors with their movies requires a three-way join (Films, Actors, and FilmsActors). Table G.6. The FilmsActors TableCOLUMN | DATATYPE | DESCRIPTION |
|---|
FARecID | Numeric (Auto Number) | Unique film actor ID | FilmID | Numeric | Movie ID | ActorID | Numeric | Actor ID | IsStarringRole | Bit (Yes/No) | Is star flag | Salary | Currency (or numeric) | Actor salary |
Primary Key Foreign Keys The FilmsDirectors Table The FilmsDirectors table associates directors with their movies. FilmsDirectors contains the columns in Table G.7. Retrieving actors with their movies requires a three-way join (Films, Directors, and FilmsDirectors). Table G.7. The FilmsDirectors TableCOLUMN | DATATYPE | DESCRIPTION |
|---|
FDRecID | Numeric (Auto Number) | Unique films director ID | FilmID | Numeric | Movie ID | DirectorID | Numeric | Director ID | Salary | Currency (or numeric) | Director salary |
Primary Key Foreign Keys The FilmsRatings Table The FilmsRatings table lists all movie ratings. FilmsRatings contains the columns in Table G.8. Table G.8. The FilmsRatings TableCOLUMN | DATATYPE | DESCRIPTION |
|---|
RatingID | Numeric (Auto Number) | Unique rating ID | Rating | Text (50 chars) | Rating description |
Primary Key Foreign Keys The Merchandise Table The Merchandise table lists the movie-related merchandise for sale in the online store. Merchandise contains the columns in Table G.9. Table G.9. The Merchandise TableCOLUMN | DATATYPE | DESCRIPTION |
|---|
MerchID | Numeric (Auto Number) | Unique merchandise ID | FilmID | Numeric | Movie ID | MerchName | Text (50 chars) | Merchandise name | MerchDescription | Text (100 chars) | Merchandise description | MerchPrice | Currency (or numeric) | Merchandise price | ImageNameSmall | Text (50 chars) | Item's small image filename | ImageNameLarge | Text (50 chars) | Item's large image filename |
Primary Key Foreign Keys The MerchandiseOrders Table The MerchandiseOrders table stores online merchandise order information. MerchandiseOrders contains the columns in Table G.10. Table G.10. The MerchandiseOrders TableCOLUMN | DATATYPE | DESCRIPTION |
|---|
OrderID | Numeric (Auto Number) | Unique order ID | ContactID | Numeric | Buyer contact ID | OrderDate | Date Time | Order date | ShipAddress | Text (100 chars) | Ship to address | ShipCity | Text (50 chars) | Ship to city | ShipState | Text (5 chars) | Ship to state | ShipZip | Text (10 chars) | Ship to ZIP | ShipCountry | Text (50 chars) | Ship to country | ShipDate | Date Time | Ship date |
Primary Key Foreign Keys The MerchandiseOrdersItems Table The MerchandiseOrdersItems table contains the items in each order. MerchandiseOrdersItems contains the columns in Table G.11. Table G.11. The MerchandiseOrdersItems TableCOLUMN | DATATYPE | DESCRIPTION |
|---|
OrderItemID | Numeric (Auto Number) | Unique order item ID | OrderID | Numeric | Order ID | ItemID | Numeric | Ordered item ID | OrderQty | Numeric | Number of items ordered | ItemPrice | Currency (or numeric) | Item sale price |
Primary Key Foreign Keys The UserRoles Table The UserRoles table defines user security roles used by secured applications. UserRoles contains the columns in Table G.12. Table G.12. The UserRoles TableCOLUMN | DATATYPE | DESCRIPTION |
|---|
UserRoleID | Numeric (Auto Number) | Unique user role ID | UserRoleName | Text (20 chars) | Role name | UserRoleFunction | Text (75 chars) | Role purpose |
Primary Key Foreign Keys | 