Worksheet One - Identify and Model Entities

Name: _________________________________  Score: ________

Carefully read each of the following case studies.  Neatly complete a worksheet table for each case study.

Case One - Video Store

I'm the owner of a small video store. We have over 4,000 video items (tapes and DVDs) that we need to keep track of.

Each of our videos has an item number. For each movie, we need to know the title and category (e.g. comedy, suspense, drama, action, war, or sci-fi). Yes, we do have multiple copies of many of our movies. We give each movie a specific id, and then track which movie a video contains. A video may be either VHS format or a DVD. We always have a copy of a single, specific movie. Our videos are very long, and we don't have any movies that require multiple videos.

We are frequently asked for movies starring specific actors. John Wayne and Katherine Hepburn are always popular. So we'd like to keep track of the star actors appearing in each movie. Not all of our movies have star actors. Customers like to know each actor's "real" birth name and date of birth. We track only actors who appear in the movies in our inventory.

We have lots of customers. We only rent videos to people who have joined our "video club". To belong to our club, they must have good credit. For each club member, we’d like to keep his or her first and last name, current phone number, and current address. And, of course each club member has a membership number.

Then we need to keep track of what videos each customer currently has checked out. A customer may check out multiple videos at any given time. We just track current rentals. We don't keep track of any rental histories.
 

Entity name Brief description of the entity The attributes for this entity
     
     
     
     
     
     

Case Two - Central Hospital

At Central Hospital and Long Term Care Center, we are very proud that we treat every patient with respect. Each patient has his or her own private room.  We identify the rooms by their numbers and put on the door of the room on a clipboard, any special information about the patient's needs - diets, upcoming tests, medication.  We also keep other important data on a clipboard, like their age, sex, and who is their doctor.

We always refer to each patient as Mr. (or Mrs. or Miss depending on the person) and their last name.  However, if they want to be called their first name or a nickname we will be glad to please them. We want to know who their "next of kin" are and a phone number of family to contact if needed.

Some patients are with us only a few days recovering from various items.  Others may have much longer terms of  stay.

Equipment can be moved from one room to another based upon needs.  We have a variety of exercise and treatment equipment along with other important medical items like IVs and oxygen.  Also, we have a variety of beds that can be used to maximize a patients comfort and recovery.

Every doctor who works here has a specialty and may or may not do surgery.  By law we must know what schools they went to and when they graduated.  We also need their office and home phone numbers and their e-mail addresses.
 

Entity name Brief description of the entity The attributes for this entity
     
     
     
     
     
     

Case Three - Regional Court System

Our Regional Court System keeps information on Judges and what cases they are assigned to.  For each judge we need to keep track of his or her name, number of years of experience and a comment on areas of expertise.  Each case is given a unique number and a name based upon the parties involved.  There are several important dates for each trial - the date that the offense first occurred, the date the trial is initiated, the date that jury selection is completed, the trial date that the trial started and the concluding date of the trial.  Some trials are criminal and some are civil.

The parties involved need to be listed.  This should include the lawyers, the defendant and other key individuals. For all of these parties information is needed about their complete names, address and other contact information - phone, fax, etc.  For the defendant information about date of birth, residence, education and native language are needed.  The lawyers who handle the case must be listed for the firm or agency.

Each trial needs to have been assigned to a courtroom for specific dates.  That courtroom should also have assigned to it for specific dates the recorder, security office and clerk.
 

Entity name Brief description of the entity The attributes for this entity