Sample Application Data Files
"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 Table | COLUMN | 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 Table | COLUMN | 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 UserRoleID column is related to the primary key of the UserRoles table.
The Directors Table
The Directors table stores all movie directors. Directors contains the columns listed in Table G.3.Table G.3. The Directors Table | COLUMN | 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 Table | COLUMN | 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
- FilmID related to primary key in Films table
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 Table | COLUMN | 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
- RatingID related to primary key in FilmsRatings table
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 Table | COLUMN | 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
- FilmID related to primary key in Films table
- ActorID related to primary key in Actors table
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 Table | COLUMN | 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
- FilmsID related to primary key in Films table
- DirectorID related to primary key in Directors table
The FilmsRatings Table
The FilmsRatings table lists all movie ratings. FilmsRatings contains the columns in Table G.8.Table G.8. The FilmsRatings Table | COLUMN | 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 Table | COLUMN | 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
- FilmID related to primary key in Films table
The MerchandiseOrders Table
The MerchandiseOrders table stores online merchandise order information. MerchandiseOrders contains the columns in Table G.10.Table G.10. The MerchandiseOrders Table | COLUMN | 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
- ContactID related to primary key in Contacts table
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 Table | COLUMN | 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
- OrderID related to primary key in MerchandiseOrders table
- ItemID related to primary key in Merchandise table
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 Table | COLUMN | 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
|
لطفا منتظر باشید ...
