Now that you've reviewed the important database fundamentals, let's walk through the tables used in the Orange Whip Studios application (the database you'll be using throughout this book). For your convenience, the created and populated Access MDB file is on the accompanying CD-ROM. NOTE Tables and table creation scripts for additional databases can be found on the book web site at http://www.forta.com/books/032122367. The database is made up of 12 tables, all of which are related. These relationships are graphically shown in Figure 5.13. Figure 5.13. Many database applications allow relationships to be defined and viewed graphically. NOTE What follows isn't a complete definition of the tables; it's a summary intended to provide a quick reference that will be of use to you when building the applications. You might want to bookmark this page for future reference. See Appendix G, "Sample Application Data Files," for a more thorough description of the tables used. The Films Table The Films table contains the movies list. The primary key for this table is the FilmID column. This table contains a single foreign key: Table 5.2. The Films TableCOLUMN | DATA TYPE | DESCRIPTION AND SIZE |
---|
FilmID | Numeric | Unique ID for each movie; can be populated manually when rows are inserted or automatically (if defined as an Auto Number field) | MovieTitle | Text | Movie title | PitchText | Text | Movie pitch text; the tag line | AmountBudgeted | Numeric, currency | Amount budgeted for movie (may not be equal to the actual cost plus expenses) | RatingID | Numeric | ID of associated rating in the FilmRatings table | Summary | Memo or long text | Full movie summary stored in a variable-length text field (to enable longer summaries) | ImageName | Text | Filename of associated image (if there is one) | DateInTheaters | Date | Expected movie release date |
The Expenses Table The Expenses table contains the expenses associated with any movies listed in the Films table. Table 5.3. The Expenses TableCOLUMN | DATA TYPE | DESCRIPTION AND SIZE |
---|
ExpenseID | Numeric | Unique ID for each expense; can be populated manually when rows are inserted or automatically (if defined as an Auto Number field) | FilmID | Numeric | ID of associated movie | ExpenseAmount | Numeric, or currency | Expense amount | Description | Text | Expense description | ExpenseDate | Date | Expense date |
The primary key for this table is the ExpenseID column. This table contains a single foreign key: The Directors Table The Directors table contains the list of directors. This table is related to the Films table via the FilmsDirectors table. Table 5.4. The Directors TableCOLUMN | DATA TYPE | DESCRIPTION AND SIZE |
---|
DirectorID | Numeric | Unique ID for each director; can be populated manually when rows are inserted or automatically (if defined as an Auto Number field) | FirstName | Text | Director's first name | LastName | Text | Director's last name |
The primary key for this table is the DirectorID column. This table contains no foreign keys. The FilmsDirectors Table The FilmsDirectors table is used to relate the Films and Directors tables (so as to associate directors with their movies). Table 5.5. The FilmsDirectors TableCOLUMN | DATA TYPE | DESCRIPTION AND SIZE |
---|
FDRecID | Numeric | Unique ID for each row; can be populated manually when rows are inserted or automatically (if defined as an Auto Number field) | FilmID | Numeric | ID of associated movie | DirectorID | Numeric | ID of associated director | Salary | Numeric, or currency | Director's salary |
The primary key for this table is the FDRecID column. This table contains two foreign keys: The Actors Table The Actors table contains the list of actors. This table is related to the Films table via theFilmsActors table. Table 5.6. The Actors TableCOLUMN | DATA TYPE | DESCRIPTION AND SIZE |
---|
ActorID | Numeric | Unique ID for each actor; can be populated manually when rows are inserted or automatically (if defined as an Auto Number field) | NameFirst | Text | Actor's first name | NameLast | Text | Actor's last name | Age | Numeric | Actor's age | NameFirstReal | Text | Actor's real first name | NameLastReal | Text | Actor's real last name | AgeReal | Numeric | Actor's real age (this one actually increases each year) | IsEgomaniac | Bit or Yes/No | Flag specifying whether actor is an egomaniac | IsTotalBabe | Bit or Yes/No | Flag specifying whether actor is a total babe | Gender | Text | Actor's gender (M or F) |
The primary key for this table is the ActorID column. This table contains no foreign keys. The FilmsActors Table The FilmsActors table is used to relate the Films and Actors tables (so as to associate actors with their movies). Table 5.7. The FilmsActors TableCOLUMN | DATA TYPE | DESCRIPTION AND SIZE |
---|
FARecID | Numeric | Unique ID for each row; can be populated manually when rows are inserted or automatically (if defined as an Auto Number field) | FilmID | Numeric | ID of associated movie | ActorID | Numeric | ID of associated actor | IsStarringRole | Bit or Yes/No | Flag specifying whether this is a starring role | Salary | Numeric or currency | Actor's salary |
The primary key for this table is the FARecID column. This table contains two foreign keys: The FilmsRatings Table The FilmsRatings table contains a list of film ratings used in the Films table (which is related to this table). Table 5.8. The FilmsRatings TableCOLUMN | DATA TYPE | DESCRIPTION AND SIZE |
---|
RatingID | Numeric | Unique ID for each rating; can be populated manually when rows are inserted or automatically (if defined as an Auto Number field) | Rating | Text | Rating description |
The primary key for this table is the RatingID column. This table contains no foreign keys. The UserRoles Table The UserRoles table defines user security roles used by secures applications. This table isn't related to any of the other tables. Table 5.9. The UserRoles TableCOLUMN | DATA TYPE | DESCRIPTION AND SIZE |
---|
UserRoleID | Numeric | Unique ID of user roles; can be populated manually when rows are inserted or automatically (if defined as an Auto Number field) | UserRoleName | Text | User role name (title) | UserRoleFunction | Text | User role description |
The primary key for this table is the UserRoleID column. This table contains no foreign keys. The Contacts Table The Contacts table contains a list of all contacts (including customers). Table 5.10. The Contacts TableCOLUMN | DATA TYPE | DESCRIPTION AND SIZE |
---|
ContactID | Numeric | Unique ID for each contact; can be populated manually when rows are inserted or automatically (if defined as an Auto Number field) | FirstName | Text | Contact first name | LastName | Text | Contact last name | Address | Text | Contact address | City | Text | Contact city | State | Text | Contact state (or province) | Zip | Text | Contact ZIP code (or postal code) | Country | Text | Contact country | Email | Text | Contact email address | Phone | Text | Contact phone number | UserLogin | Text | Contact login name | UserPassword | Text | Contact login password | MailingList | Bit or Yes/No | Flag specifying whether this contact is on the mailing list | UserRoleID | Numeric | ID of associated security level |
The primary key for this table is the ContactID column. This table contains a single foreign key: The Merchandise Table The Merchandise table contains a list of merchandise for sale. Merchandise is associated with movies, so this table is related to the Films table. Table 5.11. The Merchandise TableCOLUMN | DATA TYPE | DESCRIPTION AND SIZE |
---|
MerchID | Numeric | Unique ID for each item of merchandise; can be populated manually when rows are inserted or automatically (if defined as an Auto Number field) | FilmID | Numeric | ID of associated movie | MerchName | Text | Item name | MerchDescription | Text | Item description | MerchPrice | Numeric or currency | Item price | ImageNameSmall | Text | Filename of small image of item (if present) | ImageNameLarge | Text | Filename of large image of item (if present) |
The primary key for this table is the MerchID column. This table contains a single foreign key: The MerchandiseOrders Table The MerchandiseOrders table contains the orders for movie merchandise. Orders are associated with contacts (the buyer), so this table is related to the Contacts table. Table 5.12. The MerchandiseOrders TableCOLUMN | DATA TYPE | DESCRIPTION AND SIZE |
---|
OrderID | Numeric | Unique ID of order (order number); can be populated manually when rows are inserted or automatically (if defined as an Auto Number field) | ContactID | Numeric | ID of associated contact | OrderDate | Date | Order date | ShipAddress | Text | Order ship to address | ShipCity | Text | Order ship to city | ShipState | Text | Order ship to state (or province) | ShipZip | Text | Order ship to ZIP code (or postal code) | ShipCountry | Text | Order ship to country | ShipDate | Date | Order ship date (when shipped) |
The primary key for this table is the OrderID column. This table contains a single foreign key: The MerchandiseOrdersItems Table The MerchandiseOrdersItems table contains the individual items within an order. Order items are associated with an order and the merchandise being ordered, so this table is related to both theMerchandiseOrders and Merchandise tables. Table 5.13. The MerchandiseOrdersItems TableCOLUMN | DATA TYPE | DESCRIPTION AND SIZE |
---|
OrderItemID | Numeric | Unique ID of order items; can be populated manually when rows are inserted or automatically (if defined as an Auto Number field) | OrderID | Numeric | ID of associated order | ItemID | Numeric | ID of item ordered | OrderQty | Numeric | Item quantity | ItemPrice | Numeric or currency | Per-item price |
The primary key for this table is the OrderItemID column. This table contains two foreign keys: TIP Many database applications, including Microsoft Access and Microsoft SQL Server, provide interfaces to map relationships graphically. If your database application supports this feature, you might want to use it and then print the output for immediate reference. |