Mike's Movie Magic

Mike has been operating a small sole trader "hire" business for movies on DVDs and blue ray disks (with customers visiting his store to physically hire DVDs and blue ray disks). Since opening his business three months ago, the "membership" (customers) of his business has grown rapidly and he is now struggling to keep track of the "hires" on a spreadsheet based system. Mike currently has approximately one thousand DVD and blue ray disks in his store and has multiple copies of many movies. When studios release new movies that Mike thinks will be popular for hire he typically will purchase ten to fifteen copies of the same movie across DVD and blue ray formats.

Mike charges the same rate for DVD and blue ray hire, and has three categories of hire:

- Daily (most recent movie release) $4.50 per day
- Three day (slightly less recent/popular movie releases) $3.50 for the 3 day period
- Weekly (all other movies) $2.50 for the week

Mike is aware that you have some expertise in accounting information systems and has asked for your assistance to design a logical database structure for his business.

Required:

1) Visualise the computer data entry screens that could be used to capture data for the following business events in Mike's business:

a) Adding a new "member" (so they are able to hire movies)
b) Adding a new "movie" (so it can be hired by members)
c) Rental of "movies" (this screen could include a listing of multiple movies as a customer may hire several movies at a time)
d) Return of rented "movies" (once again, this screen could include a listing of multiple movies).

Draw a representation of one of these data entry screens (indicate to which of the business events, a, b, c, or d, your screen relates)

2) List the "data attributes" you think you would need to capture/store for each of these four business events. What entities do you think would be involved in these business events? Allocate the data attributes to these entities (tables). (Note: I expect that, at this stage, your tables may have "issues" in terms of their "logical" structure; that is, the tables have not yet been "normalized".)

3) List your tables (from requirement 2) using the notation method shown to you during class. Normalise these tables to third normal form (3NF), explaining in detail each step in the process that you have undertaken in reaching your set of 3NF tables.

4) Based upon the 3NF tables that you have produced above, present the logical data structure for your database as a Data Structure Diagram using the notation method shown to you during class.

5) For three (3) possible reports (*)that could be generated from your "logical" database design, explain:

a) what information you would include on that report
b) in general terms, the purpose of that report, and
c) how you have used the "links" between the tables in your database to extract the required information for that report.