1 / 21

The project should include the following items

The project should include the following items. Graphical process model containing 3 to 10 activities/process steps. An Entity/Object Pool with 3 to 5 entities. An Entity Relationship Matrix for the entities identified in step 2.

brant
Download Presentation

The project should include the following items

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. The project should include the following items • Graphical process model containing 3 to 10 activities/process steps. • An Entity/Object Pool with 3 to 5 entities. • An Entity Relationship Matrix for the entities identified in step 2. • A fully refined or attributed Logical Data Model normalized to third normal form. • A data dictionary for the logical model created in step 4. • 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. • At least two Forms for the database created in step 6. The forms at a minimum should enable record addition, deletion, modification. • At least two Queries for the database created in step 6. • 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 MOVIE DISTRIBUTOR MOVIESTUDIO 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. (con’t) • Customer-the patron who rents Movies. • Customer #-Unique identifier of a customer. • Last Name-the Customer’s surname. • First Name-the Customer’s 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. (con’t) • 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. SUPPLIER RESTAURANT ORDER REST NAME SUPPLIER NAME ORDER ITEM PHONE # SUPPLIER NAME (FK1) 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 INCOMPLETE CATEGORIZATION (there are other movie types such as Documentary) COMPLETE CATEGORIZATION

  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) PRODUCTION STUDIO 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

More Related