Video Rental ERD

 Documents

 3 views
of 7
All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
Description
Entity-Relationship Modeling Robert W. Weeks bob.weeks@cox.net, http://members.cox.net/bob.weeks Introduction When designing a business information system, it's important to start with a good database design. A good design makes it possible-even easy-to answer the questions the business needs answered, and to generate the reports the business needs. A poor design makes these tasks difficult or impossible. Often, poor database designs-along with the data already entered and the programs, forms,
Share
Tags
Transcript
  Entity-Relationship Modeling Robert W. Weeks bob.weeks@cox.net,http://members.cox.net/bob.weeks Introduction When designing a business information system, it's important to start with a good database design. A good design makes it possible-even easy-to answer the questions the business needs answered, and to generate the reports the business needs. A poor design makes these tasks difficult or impossible. Often, poor database designs-along with the data already entered andthe programs, forms, and reports already written-are discarded in favor of a better design, resulting in lost time and money.   The goal of entity-relationship design is two-fold: to understand our data, and to design a database that properly representsthis data. In the beginning, however, don't think of tables and databases. Instead, we concentrate on entities and attributes,and the relationships between them.   To use entity-relationship design, you need a good understanding of the business situation involved. You need to be able toidentify the things that the business needs to track. You must also be able to identify the relationships between thesethings. In other words, you need to know about the business situation. Generally, you'll obtain this information through your own knowledge of the business, through interviews with the users and managers, by examining the reports and forms the business already uses, and through other techniques. It's likely that during the model-building process you'll learn additionalthings about the business and clarify the meanings of the data the business uses.   Pine Valley Video Tape Club As an example of entity-relationship design, consider the case of the Pine Valley Video Tape Club, which is seeking toimplement its records on a computer. The club has members (customers) and a collection of tapes available for rental. The purpose of the system is to track membership, tape inventory, and tape rental activity.   For each member, the club wants to keep track of these data items:   ã A unique identification or membership number    ã The member's name   ã The member's address   ã The date that the member joined the club   ã The tapes the member has rented, along with the date that each tape is due   In real life, the club would need members' city, state, zip code, telephone number, credit card number, and perhaps other information.   For each tape, the club wants to keep track of these items:   ã The tape's identification number, which is printed on the bar code applied to each tape. It's possible that the clubmay have more than one copy of any movie title.   ã The tape's title   ã The tape's rating (G, PG-13, R, and so forth)   ã The tape's rental charge (popular movies and recent releases may be more expensive to rent)   ã The date the club purchased the tape   ã The purchase price the club paid for the tape   ã The supplier (who the club bought the tape from)   ã The supplier's address    When a member rents a tape, the club needs to keep track of these items:   ã The date of the rental   ã The due date for return of the tape   ã The date the tape was returned   ã The rental fee   Members must return rented tapes in four days, and each member has a limit of five tapes rented out at any time.   Here are some sample questions that the club wants to be able to answer:   ã List the names of the member who joined the club in a particular month.   ã Members often ask for G-rated movies for family viewing. List tapes with a G (or any other) rating.   ã Produce a list of G-rated movies again, but this time include only those movies that the customer has not yet rented. ã How many copies do we have of each of our tapes?   ã List the tapes that are currently rented.   ã List the tapes that are currently rented for each member.   ã List the overdue tapes.   ã Are there any members who have yet to rent a tape?   Of course, other questions will arise as the business begins to use the database.   Creating Entities Let's examine this situation using the first rule for entity-relationship design:   Start with entities. These are the things   that are important enough that the organization has given them names   or identification numbers .   Entities are the things that a business keeps track of (although entities can also be events or actions). Examples of entitiesthat might be found in businesses include customers (identified by customer number), suppliers (supplier number), members(membership number), students at WSU (social security number), checking or savings account (account number), parts (partnumber), repair orders (work order number), purchase orders (PO number), and invoices (invoice number). In our example,two entities that immediately stand out are the club members and the tapes that the club owns. When naming entities, it'straditional to use singular nouns such as Member  and Tape for names.   Entities have attributes , which are individual facts or characteristics about an entity. For example, the specifications for thevideo tape club system indicate that each member has an identification number, name, address, date of membership, and thevideo tape rented along with the rental date. These pieces of information are attributes for the Member  entity. Attributes arenot usually broken down into smaller components; they are the atomic unit of data in a table.   An entity is an abstraction that stands for all the occurrences or  instances of what the entity is tracking. An instance of theentity Member  is a person, while an instance of the entity Tape represents one of the tapes the club owns.   When identifying entities, remember the second rule for good design:   An entity should have a significance of its own. Even if we ignore everything else in the database, the entity shouldstill be important on its own.   In our example, a member (an instance of the Member  entity) is important even if the member has yet to rent a tape . For example, a customer might enter the store and apply for club membership, but might not choose a tape to rent that day. Wewould still want to keep track of this member so that we have the information on hand when the member returns and doesrent a tape. Similarly, a tape is important to track  even if it hasn't been rented  .After choosing entities, draw them using the notation illustrated.   A third rule is:   Make sure that the entity has a  primary key .    The Member  entity represents all the people who are members of the video tape club. There must be a way to distinguishone member from another. The primary key serves this purpose.   The primary key is an attribute (or combination of two or more attributes) that is guaranteed to be unique for eachoccurrence or instance of an entity. In other words, we need to be able to distinguish each member or each tape in some way.For the Member  entity, we might be tempted to make the  Name attribute the primary key. The video club, however, wouldnot have to have very many members before there would be two people with the same name, two Mike Smiths , for example.Therefore, we assign a unique identification number (call it MemberID ) to each member. Some sort of system will generateunique MemberIDs as we add new members.   For the Tape entity, a candidate for the role of primary key is the Title attribute. All copies of a movie, however, have thesame title, so the Title attribute by itself can't serve as a primary key. Create an attribute called TapeID and let it serve as the primary key. As we receive tapes from our suppliers, we will generate unique TapeIDs for each tape.   After deciding which attributes will serve as primary keys, underline them in the diagram as follows:   Creating Relationships At this point, apply some tests to your design. One test that your design must eventually pass is:   There should be no multi-valued attributes. Make sure that each attribute of an entity is such that each instance of the entity can have at most one value for that attribute-not a list of values.   A multi-valued attribute is an attribute where for each instance of an entity, there could be more than one value. Ask thesequestions: For each instance of entity Member  could there be more than one member number? More than one member name? More than one address? More than one membership date? More than one video tape rented? The answer to allquestions except the last is no. Each member could have more than one tape rented at any time (club rules allow up to five).The TapeID,    DateRented, DateDue, DateReturned, and  FeePaid  attributes, therefore, could be a list of values-a multi-valued attribute situation that we need to avoid. The solution is to model the rental of tapes through a relationship . We saythat the multi-valued attribute implies a relationship with another entity.   In this situation we want to create a relationship called  Rental  which links members with the tapes they have rented. In moreexplicit terms, the relationship  Rental  connects instances of the Member  entity with instances of the Tape entity. Instances of the  Rental  relationship represent times when a certain member has rented a certain tape. Each time the store rents a tape, wecreate another instance of the  Rental  relationship.   Since the relationship modeled by the relationship between Member  and Tape tracks the tapes that members have rented, wecan now remove the TapeID, DateRented, DateDue, DateReturned, and  FeePaid  attributes from the Member  entity.   When creating a relationship, show it as a diamond connected with lines to the two entities that it relates. Include asattributes of the relationship facts about instances of the relationship. In our example, an instance of the  Rental  entityrepresents a video tape rental, that fact that a certain member rented a certain tape on a certain day, paid a certain price, andreturned in on a certain day. Attributes that we need to keep track of include  DateRented  ,  DateDue ,  DateReturned, and  FeePaid  (the dollar amount charged for this rental). These attributes belong to the  Rental  relationship because they ariseonly when a member rents a tape.Don't include other attributes such as the primary keys of the entities that the relationship connects. This seems like areasonable thing to do, because when we rent a tape we need to know which tape was rented ( TapeID ) and which member rented it ( MemberID ). These facts, however, are automatically captured because the  Rental  relationship connects oneinstance of  Member  with one instance of  Tape .   A relationship will have a cardinality (sometimes called  functionality ). The cardinality gives us additional information aboutthe relationship between two entities. You can determine the cardinality by answering two questions: First, given a singleinstance of entity A, how many instances of entity B could it be related to? In our example, given a particular member, howmany tapes could the member rent? The answer is an unlimited number of tapes, or  many . You may remember that clubrules limit members to five tapes checked out at a time, but we're considering the situation over a long period of time.    Now, consider the reverse question: Given a particular tape, how many members could it be rented by? Again, the answer ismany. (Of course, a particular tape can be in the hands of just a single member at a time. Again, consider the situation over along time frame.)    Because the answer to both questions is many, we say the cardinality is many-to-many or that we have a many-to-manyrelationship. On the entity-relationship diagram, write the letter  M  next to each entity. We can also write this cardinality asM:M.   Many-to-many relationships are not always the case. Consider for a moment the situation where club members might purchase tapes. The entities and relationships are very similar to the rental example, except that a tape can be purchased justa single time. A purchase of a tape might be represented by an relationship called  Purchase instead of   Rental  . In this case, amember might purchase many tapes (an instance of  Member  is related to many instances of  Tape ). A single tape, however,can be sold just once (an instance of  tape can be related to just a single instance of  Member  ). We say we have a one-to-many relationship (one member to many tapes). On the entity-relationship diagram, place the number 1 next to the Member  entity,and the letter M next to the Tape entity.   When specifying a one-to-many relationship, make sure that you are clear as to which side is the one side and which is the many side. From Member  to Tape , the cardinality is one-to-many, written as 1:M. From Tape to Member  , the cardinality ismany-to-one or M:1.   After making sure there are no multi-valued attributes, we're ready to consider another rule of entity-relationship modeling:   Eliminate redundancy.   Redundancy is one of the worst enemies of effective database design. Redundancy costs in several ways: If there isredundant data, then someone had to enter the same fact more than once, when once is sufficient. If someone made a typingmistake when entering one of the instances of a redundant fact, you may not know which version of the fact is correct. It isdifficult to update redundant data, as you must find and change all occurrences of a fact you need to update. If you fail toupdate all occurrences you will have inconsistencies, which means that the same question returns two or more differentanswers. For example, if a bank stores a customer's address in two places and updates only one when the customer moves,the bank is storing two addresses for the customer. Which is correct? Finally, customers may be asked to supply data such astheir address more than once.   Often, you'll easily see redundancy when you look at the entity-relationship diagrams. Other times, it is more difficult tosee. One way to find redundancy is to consider labels that we might (manually) create for labeling the instances of an entity.Would we have to type the same data repeatedly when creating these labels? Suppose we create labels for the video tapes.On these labels, we'd type the TapeID , Title ,  Rating  , and  RentalCharge attributes. For a popular movie that the club ownsmany copies of, we'd have to type the Title ,  Rating  , and  RentalCharge attributes values many times, even though theseattributes are the same for each copy of the tape.   Another way of looking at this is to consider how many times do we need to record that the title  Forrest Gump is associatedwith (or is dependent on) TapeID   32435 ? We need to record this relationship just once. If the club, however, has more thanone copy of   Forrest Gump , this relationship or dependency will appear redundantly.   To remove redundancy from the design, create a new entity to hold the redundant attributes, and create a relationship between the srcinal entity and the new one. For the tape club, we'll create a new entity called Catalog  that has the attributes CatalogID , Title ,  Rating  , and  RentalCharge . This entity is the club's catalog of tapes available for rental. If a member wantsto see a listing of all the tapes available for rental, this is the list to look at. It's like the (old-fashioned) card catalog in alibrary.   There is a relationship between Tape and Catalog  called  Describes , meaning that an occurrence of  Catalog  describes anoccurrence of  Tape , or that an occurrence of  Tape is described by an occurrence of  Catalog  . The cardinality from Catalog  to Tape is one-to-many, as one instance of  Catalog  could be related to many instances of  Tape (one occurrence of the tapecatalog could describe many copies of a movie). From Tape to Catalog  the cardinality is many-to-one, as each occurrence of  Tape is related to just one instance of  Catalog  (each video tape, no matter how many copies the club has, is described by asingle occurrence of  Catalog  ).   Precise definitions are important. If someone asks how many movies does our store have, where do we look to find theanswer? Do we count instances of  Tape or instances of  Catalog? What exactly is a movie? An instance of  Tape represents avideocassette that the store owns. An instance of  Catalog  represents a movie that has been produced. The store may have100 copies of   Forrest Gump available for rental. That translates to 100 instances of the Tape entity, but only one instance in Catalog  .   So far, our entity-relationship design doesn't track the tape supplier information, which consists of the purchase date of thetape, the purchase price, the supplier's name, and the supplier's address. One question we need to answer is where to includethis information. There are two candidate entities for holding this information, Tape and Catalog  . The way to resolve thisquestion is to ask what does the club actually purchase from the suppliers? Is it a description of a tape in a catalog, or is it a
Related Search
We Need Your Support
Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

Thanks to everyone for your continued support.

No, Thanks