The project should include the following items

1
The project should include the following items

1. Graphical process model containing 3 to 10
   activities/process steps.
2. An Entity/Object Pool with 3 to 5 entities.
3. An Entity Relationship Matrix for the entities
   identified in step 2.
4. A fully refined or attributed Logical Data Model
   normalized to third normal form.
5. A data dictionary for the logical model created
   in step 4.
6. An Access 2007 Database created by transforming
   the logical model in step 4 into a physical
   design. Each table in the database should
   contains at least 4 records.
7. At least two Forms for the database created in
   step 6. The forms at a minimum should enable
   record addition, deletion, modification.
8. At least two Queries for the database created in
   step 6.
9. At least two Reports for the database created in
   step 6.

PART 1
P RT 2
2
1. Graphical process model containing 3 to 10
activities/process steps.
Manage Video Rental Business
Customer data collection form
1.0 Input Customer Information
3.0 Track Customer Rentals
Customer address and preference information
Customer history and status
Emails
5.0 Advertise and Track Specials to New and
Repeat Customers
Returned movies
Postal mailing
2.0 Manage Video Inventory
Inventory with shelf location
New movies
4.0 Order New Movies
Movie order form
Studio movie release info
3
2. An Entity/Object Pool with 3 to 5 entities.

• Movie
• Western
• Action and Adventure
• Comedy
• Documentary
• Movie Studio
• Customer
• Movie Rating

4
3. An Entity Relationship Matrix for the
entities identified in step 2.
5
Fully Attributed/Refined Model Example
MOVIE RENTAL
MOVIE
CUSTOMER
Is Rented By
Movie (Fk1) Copy Customer (Fk2) Rent
Date Return Date Late Status
Movie Title Length Movie Type MPAA Rating
Customer Last Name First Name Cust
Address Cust Phone
Rents
Is Distributed by
(TYPE)
WESTERN
Movie ASPCA Rating
MOVIESTUDIO
MOVIE DISTRIBUTOR
Distributor Name Movie (Fk1) Studio
Name(Fk2) Release Date Contact Name Contact
Phone
Studio Name Address
Makes
6
5.0 A data dictionary for the logical model
created in step 4.

• Movie-An item that is available to rent, a motion
  picture or television production.
• Movie -Unique identifier of the item available
  for rent.
• Title-The name of the item available for rent.
• Length-The running time in minutes of the item
  available for rent.
• Movie Type-The genre or classification associated
  with the items available for rent. Valid values
  are Action and Adventure, Animation , Christmas
  , Comedy , Comic Book-Based , Documentary, Drama,
  Family, Horror, Martial Arts, Musical, Science
  Fiction and Fantasy , Silent Movie, Sports, War
  Movie, and Western .
• MPAA Rating-Motion Picture Association of America
  evaluation. Valid values are G, PG, PG-13 R,
  and NC-17.
• Western-A genre or type of Movie/Television
  production.
• ASPCA Rating-American Society for the Prevention
  of Cruelty to Animals evaluation.
• Movie Rental-An instance of a Movie being rented
  by a customer.
• Movie (Fk1)- Unique identifier of the item
  available for rent.
• Copy -The sequence number of the item available
  for rent. Used to differentiate multiple copies
  of a Movie.
• Customer (Fk2)-Unique identifier of an
  individual authorized to rent a Movie.
• Rent Date-The date a Movie is rented by a
  Customer.
• Return Date-The date a rented Movie is to be
  returned to the store for restocking.
• Late Status-A status code identifying if the
  rental item has not been returned by the Return
  Date.

7
5.0 A data dictionary for the logical model
created in step 4. (cont)

• Customer-the patron who rents Movies.
• Customer -Unique identifier of a customer.
• Last Name-the Customers surname.
• First Name-the Customers given name.
• Customer Address-the location (postal) where a
  Customer may be communicated with.
• Cust Phone -the area code and telephone number
  of the Customer. If Address is outside of the
  USA, then a country code is required.
• Movie Studio-a company that makes and produces
  motion pictures/television shows.
• Studio Name-Unique identifier of a Movie Studio.
• Address-the location (postal) where a Movie
  Studio may be communicated with.

8
5.0 A data dictionary for the logical model
created in step 4. (cont)

• Movie Distributor
• Distributor Name- Unique identifier of the
  company that distributes movies for the Studios.
• Movie (Fk1)-Unique identifier of the item
  available for rent.
• Release Date-The date that the rental item is
  available for rent.
• Studio Name(Fk2)-Unique identifier of a Movie
  Studio.
• Contact Name-the identification of a person at a
  Movie Studio to whom communication should be
  directed.
• Contact Phone -the area code and telephone
  number of the Movie Studio Contact. If Address
  is outside of the USA, then a country code is
  required.

9
6.0 An Access 2007 Database created by
transforming the logical model in step 4 into a
physical design

• See Access File Manage Video Rental v1.accdb

10
First Normal Form - (1NF)

• Every key and non-key attribute of an entity must
  be single valued
• No entity instance can have multiple values for a
  given attribute
• i.e., The No Repeat Rule
• A violating entity is corrected by removing
  repeating or multivalued attributes to another,
  dependent (child) entity

11
First Normal Form - Example
RESTAURANT
REST NAME
ADDRESS
PHONE
EMPLOYEE NAME

REST NAME ADDRESS PHONE
EMPLOYEE NAME
BURGER KING
123 NORTH ST
123-2345
JOHN, SUE, LISA
TACO HOUSE
345 126TH PLACE
765-8907
MARY, BILL
FISH COMPANY
77 SUNSET AVE
395-5682
ED, SAM, JOSE, RICK
RESTAURANT
EMPLOYEE
REST NAME
EMPLOYEE NAMEREST NAME
ADDRESS
employs
PHONE
POSITION
12
Second Normal Form - (2NF)

• An entity that is in first normal form and each
  non-key attribute is dependent on the entire
  primary key
• No non-key attribute instance can be determined
  by knowing just part of an entity instances key
• A violating entity is corrected by removing to a
  parent entity any attributes that depend on only
  a subset of the primary key

13
Second Normal Form - Example
RESTAURANT ORDER
REST NAME
SUPPLIER NAME
ORDER ITEM
SUPPLIER PHONE
REST NAME SUPPLIER NAME ORDER ITEM
SUPPLIER PHONE
BURGER KING
SAM'S PRODUCE
BEEF
123-2345
TACO HOUSE
SALSA INC.
PEPPERS
765-8907
FISH COMPANY
SAM'S PRODUCE
SNAPPER
123-2345
fills
14
Third Normal Form - (3NF)

• An entity that is in second normal form and each
  non-key attribute is only dependent on the entire
  primary key and nothing other than the key
• No non-key attribute instance can be determined
  by knowing the value of another non-key attribute
  for the same instance
• A violating entity is corrected by removing to a
  parent entity any attributes exhibiting
  transitive dependencies (non-key attributes that
  not only depend on the whole key but also on
  other non-key attributes)

15
Sample Information Model
16
Categorization Example
WESTERN
SUSPENSE
COMEDY
MALE
FEMALE
(MOVIETYPE)
(SEX)
MOVIE
PERSON
COMPLETE CATEGORIZATION
INCOMPLETE CATEGORIZATION (there are other movie
types such as Documentary)
17
Planning Model Example
MOVIE
CUSTOMER
Is Rented By
/ Rents
Is Produced In/
Produces
(TYPE)
STUDIO
WESTERN
18
Key-Based Model Example
MOVIE RENTAL
MOVIE
CUSTOMER
CUSTOMER
MOVIE
MOVIE (FK1) CUSTOMER (FK2) DATE
Is Rented By
Rents
Is Produced by
MOVIE
(TYPE)
STUDIO
PRODUCTION
STUDIO ID (FK2) MOVIE (FK1)
STUDIO ID
WESTERN
Funds
MOVIE
19
Non-Specific ConnectionRelationships

• A relationship where one instance of an entity
  may relate to 0, 1, M instances of a second
  entity and one instance of the second entity may
  relate to 0, 1, M instances of the first entity
• For Example
• A Customer Rents at 0,1,M Movies, and Each Movie
  is Rented by 0, 1, M Customers

MOVIE
CUSTOMER
Movie Number
Cust Number
rents/is rented by
Name
Name
Rating
Address
Rental Rate
Status Code
20
Specific Connection Relationships

• A Parent - Child relationship that resolves a
  non-specific relationship to capture additional
  detail
• A relationship where one instance of an entity
  (Parent) may relate to 0, 1, M instances of the
  second entity (Child), and the Child entity is
  related to one and only one instance of the
  Parent entity
• For Example

• A CUSTOMER Rents a specific copy of a MOVIE

Movie Rental Record
Customer
Movie Number Movie Copy Id Rental Date Cust
Number (FK)
Cust Number
rents according to
Name
Address
Status Code
21
Movie Genres

• Action and Adventure
• Animation
• Christmas
• Comedy
• Comic Book-Based
• Documentary
• Drama
• Family
• Horror

• Martial Arts
• Musicals
• Science Fiction and Fantasy
• Silent Movies
• Sports
• War Movies
• Westerns