Assignment 4 (Project)

Draft Solution

There are many possible designs. This solution is one of them. It all depends upon your assumptions.

There are two ways to look at this problem. If you think about a movie, a member in the video club, either borrows a movie and a movie has many copies, or a member borrows a replica of a movie. Depending upon how you look at this, you may get slightly different E-R diagrams. This applies for the CDs and video games too.

Since I did not mention costs in the assignment 4 handout (I just forgot to do it), we will not consider rental prices. Instead, we will assume that members borrow items from the club and they just pay a flat membership when they join. I don't think such video clubs exist but this is ok for the assignment. :-)

Apart from the specifications given in the handout, we assume that borrowed items have unique numbers and these numbers contain the kind of the concerned item. For example the first digit of the number may be 1, 2 or 3 to indicate respectively "movie", "CD" or "game". This is usually coded in the barcode. Duplicates of the same item have copy numbers. As I mentioned previously, duplicates can either be regarded as an entity set in itself, or a weak entity set attached to the entity set Movies, CDs, or Games. For simplicity, I chose the first option in the E-R diagram bellow. We can also assume that actors (in movies), directors, and artists (in CDs) can be identified uniquely with their "star names". Because it is not clear in the specifications, we can assume that the duration (1 day, 3 days, or 7 days) for borrowing an item is specified at borrow time by either the member or the clerk. We could of course associate the duration to the video category. You are free to do that assumption in your solution.

The E-R diagram bellow is a simple representation of our video store. It also contains a small list of attributes. You are free to add as many reasonable attributes as you wish ;-).

The multivalued attributes, like actors and artists, can either be represented by an entity attribute as above, or by an entity (actors for example) and a relationship (between actors and movies). The second solution is better if actors have many attributes because it reduces redundancies. However, in our case, we retain only the names which already are the keys. Thus, we do not need the entity and the relationship which would result later in the same table anyways.

Copy# and title, common to the three kinds of items, migrate up to the entity item. However, as we will see later, in the implementation, we may choose to push them back down to improve query efficiency.

The category for a CD is "rock", "classic", "country", "jazz", "sound track", etc., while the format for a game is "nintendo", "sega", "playstation", etc.

For the relationship borrow, bdate is the date the item was borrowed, duration is the borrow time chosen (1,3 or 7 days), and return_date is the date in which the return is done. This attribute is left empty until the item is returned. Hours and minutes are not reported here because we assume the date is the smallest time unit in this context. We can also incorporate hours and minutes in date if we wish, but that would complicate the time calculation later.

The following are the tables that we get from the above diagram by following the rules that generate tables from E-R diagrams given in the textbook.

Members(mid,mname,address,phone) Items(item#,copy#,title) Borrow(mid,item#,copy#,bdate,duration,return_date) Video_Games(gitem#,gcopy#,gformat,gversion) Music_CD(mitem#,mcopy#,mlabel,mcategory) Artists(mitem#,mcopy#,arname) Movie_Video(fitem#,fcopy#,ftype,fcategory,fformat,fdirector) Actors(fitem#,fcopy#,acname) Sound_Track(fitem#,fcopy#,mitem#,mcopy#)

fitem#, mitem#, gitem# and item# are all item numbers. I gave them different prefixes for clarity. The same applies for fcopy#, mcopy#, gcopy# and copy#.

Notice that these relations are not necessarily normalized. Indeed actor name for example is functionally dependent on item number and not actor number and copy number together. The same applies for fdirector, ftype and fcategory which are functionally dependent on part of the key of Movie_Video and mcategory dependent on only mitem.

After normalization, we can push down the title from the relation Item to the three relations for movies, CDs and games. This would avoid an extra join whenever we need the title of a movie for example. The following are the tables after normalization and schema manipulation.

Members(mid,mname,address,phone) Borrow(mid,item#,copy#,bdate,duration,return_date) Video_Games(gitem#,gcopy#,gtitle,gformat,gversion) Music_CD(mitem#,mcategory,mtitle) CD_copies(mitem#,mcopy#,label) Artists(mitem#,arname) Movie_Video(fitem#,ftype,fcategory,fdirector,ftitle) Video_copies(fitem#,fcopy#,fformat) Actors(fitem#,acname) Sound_Track(fitem#,mitem#)

The relation Items has been removed because it bacame redundant once we moved title down to the specialized relations (Movie_Video, Music_CD, Video_Games). Remember that item# contains the kind of item. It is also possible to add another attribute in Borrow to indicate which kind of item it is (ex: M for music, V for video and G for Game). However this is not necessary in our case since it is coded in the itme number. The C code program can easily check the first digit of the number if necessary.
Sound_Track retains only the item numbers and not the copy numbers anymore to reduce redundancies.

Some E-R diagrams may result in a normalized set of relations. However, this is not always the case and I recommend that you always check your tables for further improvement. Moreover, it is sometimes necessary to keep the tables partially denormalized depending on the queries you have to submit and the performance you want to get.

If we check the requirement specified in the project handout, we see that this database structure can answer them all.

The following requirements were given:

• A list of "bad" members (members who returned the borrowed items late with a given threshold) is printed regularly.
  This can be done by checking the dates in Borrow and joining Members. Needs data entered by user (threshold).
• A list of members who borrowed more than a given time t and returned the items on time. (for bonus for example)
  This can be done by checking the dates in Borrow and joining Members. Needs data entered by user (t)
• A report on individual member is available on demand with history and current borrowed item list.
  This can be done by joining Members with Borrow and the three item kinds (movie, CD, game). Needs data entered by user (mid)
• A list of new releases is published regularly and mailed to members.
  The new releases can be retrieved from Movie_Video while the members' address list is printed from Members.
• A list of most popular CDs, videos, and games.
  A join between Borrow and Movie_Video, Borrow and Music_CD, and a join between Borrow and Video_Games can do the trick.

• Find the club members who borrowed a given movie.
• Find the telephone numbers of members late in returning an item by more than 2 days, and the specification of the items borrowed.
• List the CDs by the same composer as the sound track of a given movie.
• List in alphabetical order all movie titles where a given actor is playing.
• List all members who are currently borrowing a game but are not borrowing a movie.
• List all the members who are currently borrowing a game and a movie.
• List the members that never borrowed a video game.
• List the items currently borrowed by a given member
• List of items borrowed more than a given time, grouped by item type (video, CD, game).

You are not obliged to use this design for your implementation. You can either use the above tables, or use the tables that you derived from your diagram. You can also change your design and improve the tables that you already have based on the above tables. Your implementation should be fine as long as the implementation answers the requirements stated in the project specs.

Remember, this is not a user interface project but rather a database project. Concentrate more on the database connections and keep the user interface simple. Even menues would suffice. Once your queries work and you have time, you can make the user interface nicer.

Good luck and have fun with the implementation.