Project 1 ERD and its description

Entities and relationship diagram and description of Mom and Pop Johnson Video Store database

This is the design that you can use to complete your project 2 based on the description and requirement in project 2 description. However, you are allowed to make any modification you feel that they are proper or could enhance the functionality of the database or simply you found the design had fraud. No matter how do you change the design the final implementation should be functionally correct and theoretically makes sense. For example, no M:N issue reoccurs.

Secondly this design does not include physical mapping, meaning attributes need to be mapped to columns with proper datatype. Certain non-key attributes may be required check constraint for its value validation, such as type_distributed in movie_list.

Business analysis of the project:

  1. Generally, movies are released to public regardless vendor distribution is available or not. Therefore, movies entity should list all the movies available to general public.
  2. Distributors (vendors) have their own inventory lists to list movies they are selling to their buyers, such as Mom & Pop Video Store. Such inventory entity is a subset of movies listed in movies entity. Itshould list all the movies distributors sell with proper type (DVD or Video or both) associated with each distributed movie.
  3. Mom & Pop Video Store buy their movies from the vendors by placing orders containing the selection from inventory list. Each order may contain more than one items. The movies purchased should be entered in the renting list (see next).
  4. Mom & Pop Video Store has its own list of movies for their renting business with price per rent. This list is a subset of the inventory distributors sell. This renting list serves as a selection list for the store customers to rent their selected movie(s).Since most likely there are more than one copies for each individual renting title in the store, there should be additional entity (title_copies) to list every copy of the individual movies. In fact, this title_copies should be used for renting transactions because customers take one copy of each movie they rent. Customers and title_copies entities have Many to many relationship to be resolved. The associative entity could be used to record renting transactions as well.
  5. Customers are the people who rent movies from Mom & Pop Video Store. They should have an account in the store and their renting records should be collected.
  6. Actors and Movie Awards are independent entities. Each actor may act in more than one movies and each movie may have more than one actor. Awards have the similar situation as Actors. These many to many relationship need to be resolved by introducing associated entity.

Table 1. Entities and relationship of Mom and Pop Johnson Video Store.

Entities / Attributes / Keys / Explanation
Movies / Movie_id
Title
discription / Movie_id is primary key . / This entity is used to store movie data related to movie itself, such as title or content description.
  1. Actors played in movie cannot store here because movie and actors are M:N relationship. Need additional associative entity (actor_in_movies) to establish two 1:N relationships to deal with the case.
Movies to actor_in_movies
Actors to actor_in_movies
  1. Awards and movies have the same relationship(M:N) as above. Movie_awarded working the same way as actor_in_movie.

Movie_awarded / Winning_id
Movie_id
Award_id / Winning_id is primary key / Movie_id and Award_id are foreign key to reference to the attributes in corresponding parent entities.
Awards / Award_id
Description
Awarded_date / Award_id is primary key
Actor_in_movies / Actor_in_movie__id
Actor_id
Movie_id / Actor_in_movie__id
Is primary key / Actor_id and Movie_id are foreign key to reference to the attributes in corresponding parent entities.
Actors / Actor_id
Firstname
Lastname
Gender
contact / Actor_id is primary key
Distribution_list / List_id
Movie_id
Distributor_id
Type_distributed
Inventory_quantity / List_id is primary key
Note: you should consider use check constriant on Type_distributed
To limit values as DVD or VEDIO. / Movie_list stores data for every movie available on the market (must be in movies entity ) based their released type (DVD or Vedio).
The movies in any types are distributed by a distributor listed in distributors.
Quantity of each available type of movies are stored based on type and distributors.
Movie_id is a foreign key that references movies.
Distributors / Distributor_id
Description
Location
contact / Distributor_id is primary key / Distributor data is stored in this entity
Orders / Order_id
Description
Total_items
Total_payment
Tax
Order_status
Date_ordering
Order_completed_date
Order_completed_by / Order_id is primary key / Any orders for DVD or VEDIO should be stored here and each record is uniquely identified by order_id.
Order_items / Item_id
Order_id
List_id
copy_number
unit_price
item_total_price / Item_id is primary key / Item_id identifies each item in a particular order. Any item ordered should be in an order. Any items ordered must be available in distribution_list.
Order items are listed in order_items entity that must references order_id in orders and also it should reference distribution_list
Multiple copies of the same item should be only in one record.
Order_id is a foreign key that references orders entity.
List_id is a foreign key that references distribution_list.
customers / Customer_id
Firstname
Lastname
Address
Card_approval
Rent_limit
Overdue_notified / Customer_id is primary key
Renting_titles / Title_id
Movie_id
In_store
Available_for_rent
Rent_price
Type_distributed / Title_id is primary key / The movies should be available (listed in movies)
Movie_id is foreign key references to movies
The movies should be also in store, meaning purchased. This is better controlled at application level. Not in database level so we do not add any additional relationship to other related entity, such as order_items
Title_copies / Copy_id
Title_id
Available / Copy_id is primary key / Each renting title may have one or more copies. This entity is a child of renting_titles to record each copy of the given title of renting movie.
Title_id is foreign key that references renting_titles.
Movie availability should be always checked.
Movie_renting / Rent_record_id
Copy_id
Customer_id
Rented_date
Returned_date
Overdue
Rent_fee
Overdue_fee / Rent_record_id is primary key / customers and title_copies have M:N relationship. Therefore, movie_renting is used to resolve the issue to create two 1:N relationships, respectively.
Copy_id and customer_id are the foreign keys to title_copies and customers, respectively.

Figure 1 ERD – Mom and Pop Johnson Video Store database. The ERD was made using CA Erwin. The drawing notations are slightly different from what is described in the textbook. Below is a note to explain how to read it.

Outside of the boxes are entity names, ids are primary key shown in upper box or foreign keys, doted and solid lines indicate the relationship (1:N). Black dot side indicates N. Any attributes with FK indicate they are foreign keys.

This figure is to further show the entity groups that store the data based on business processes. The chart is identical to the one above.

1