ER Diagram Questions

1. A Bank

A Bank has:

2. Google

Google stores information about each site that is registered in it. A site has a title, a customer who registered it, a short description, a file type and a web address. The date that the site was registered is also stored. The customer data that is stored is their name, address and payment type. Sites are not added to the database until they are paid for. Each website has a series of keywords that are registered for it. The number of hits that each site gets from searchers is also stored to allow Google to make up it's priority list properly.

3. Algonquin Turtles

In Algonquin provincial park, biologists complete many studies about wildlife. Last summer, one of the reptiles studied was the turtle. The intent of the study was to note how far the turtles were moving in the summer season and to track reproduction rates.

Co-op students were hired from the University of Guelph as research assistants. When they signed on to their job, they filled out a form containing their name, their home address, their Social Insurance Number and they were assigned an Employee Number.

The students lived at the park all summer. Every morning, they signed out in the office. They noted their employee number, the date, the time of their departure, where they intended to canoe to and when they expected to be back. This information was recorded for safety reasons - it would make it easy to track the students down if they were injured and didn't return.

The co-op students canoed around the park catching turtles. The first time the turtle was caught, it was tagged with a number around its rear leg. The student recorded the turtle's number, its gender, its general condition, the lake in which it was found, and the date and time of the tagging. The co-op students added their employee ID to the turtle's record because they were having a contest to see who could tag the most turtles.

The second and subsequent times that a turtle was caught, the students didn't re-record all of the previous information. Instead, they just noted the turtle's number from its tag, the date, time and location that the turtle was found. They also noted its general condition.

4. An Insurance Company

An insurance company provides life insurance for its clients. Each client has a policy. The policy has a policy number which is unique in the insurance company. The policy has a policy holder’s name which is the same as the client’s name. The policy holder’s address, Social Insurance Number, work phone number and home phone number is also stored. Each policy also has up to two beneficiaries (people who receive money when you die). The first and last names of the beneficiary are stored. Each policy has a premium, which is the amount of money that a person must pay to the insurance company every month.

Before the insurance company will guarantee an person’s insurance, the client needs to have a medical exam. The results of the exam (healthy/not), the doctors first and last name, the doctor’s phone number and address are all stored. As well, information is stored: smoking status, sex, age, weight and height are stored because all of these factors effect how long you will live and thus the amount of premiums you will have. 

Life insurance comes in a number of different categories: (1) Term policies – You are insured for part of your life. (eg. You are insured to age 88) If this option is chosen, the age that the policy stops must also be stored. (2) Whole Life – You are covered for an indefinite amount of time. (3) Renewable Policies – policy is only for a short term, but can be renewed. If this option is chosen then the number of times the policy has been renewed and the date of the next renewal must be stored.

Long ER Diagram Questions

5. ISP Service

Note: If a person wants to connect to the Internet they must be assigned a set of numbers that uniquely identify their computer to the rest of the Internet community. These numbers are referred to as IP addresses. No two connected computers may use the same number. To get an IP address, a customer goes to an Internet Service Provider or ISP (Roger with their cable modems is an example of an ISP). An ISP generally also handles your Internet traffic, including your e-mail. An IP can be permanently assigned to a computer, or a computer can "borrow" an IP address from a pool for the duration that it is connected to the internet.

Jennie Trout, Emily Carr and Mary Pickford were having coffee in an Internet Café, when they discovered that they were all unhappy with their local Internet Service Provider. It seems that the ISP providers in their area frequently crash or are taken off line for servicing. To respond to this need, the group has formed a company to provide reliable, fast ISP services. As they design their system, to avoid the problems that their local ISPs are experiencing, Jennie, Emily and Mary intend to design and test their system correctly using techniques from ICS4M0.

The new company is called Trout-Carr-Pickford Internet Providers (or TCPIP). Customers sign up via the web or the phone. They provide their name, the phone line that they will use to connect to Internet, and their mailing address. The customer is billed using their credit card. If the charges do not go through, the customer is rejected. Approved customers are given a username and password. The customers are then presented with some service options - (1) IP address services only, (2) IP services and email, (3) IP services, email and space for a web site, (4) IP address and space for a web-site. Each service option has a different cost.

Customers are sent a CD of software which helps them to dial-up TCPIP and connect to the Internet. The connection software accesses the TCPIP and requests the next available IP address. The IP address is temporarily assigned to that customer’s computer and they may then surf the net or download their e-mail from TCPIP. When the customer is disconnected, the IP address is returned to the queue to be reassigned to another customer. Because customers have unlimited access, the time that people stay connected is not important.

Billing is done monthly. Based on the services they have signed up for, a bill is calculated and is mailed to the customer. A copy of the bill is saved in the billing database. When customer payment is received, it is recorded in the billing database. Alternately, customers can sign up for automatic billing to their bank account. If this case, the bill and the bank account number is forwarded to the customer’s bank. The bill amount is still recorded in the billing database.

To cancel or change services, customers can either apply on-line or phone the Service Line. The customer’s service or billing options would be changed based on their request. When a customer wishes to cancel their services, the Service Line records the reason that they are leaving TCPIP in a database and deletes them from the customer database. Also, the service line can be used as a “Help Desk” for customers.

A weekly report summarizes the reasons people have stopped using TCPIP services  This helps TCPIP to see what they are doing wrong so they can correct it. Also, a monthly summary of the customer bills sent is produced. All of these reports are passed to the management.

6. Birdville Blood Donor Clinic

Canadian Blood Services runs Blood Donor Clinics across Canada. They have a series of central offices in major cities (eg. London, Timmis, Toronto, etc.). Teams of nurses and other technicians leave these central offices to do clinics in smaller communities, like Birdville. These clinics are spaced 56 days apart so that the donors in the small communities have had sufficient time to replace the blood they have given.

Before the arrival of the clinic team, a contract in the Birdville sees that posters are placed in various stores and in the high school. Posters are not placed in dentist offices because people cannot give blood for two days after seeing the dentist. An advertisement is placed in the local paper “Birdville Calls” about two weeks before the clinic. On the day of the clinic, the team arrives in Birdville at about 11:00 in the morning. They set up in the auditorium that is located next to the library (which just got a new computer system).

Donors start arriving at 1:00. When they arrive, they are given a brochure to read about who can and who cannot give blood. When the donor finishes reading the information, they are directed to the sign in area. At this point, if the donor has given blood before, a nurse takes their donor card and scans in the donor information. This information is electronically printed out on a form that the donor will take with them to all of the later booths. If the donor is a new donor, the nurse writes down their first name, last name, birth date, address, phone number, and the city where the clinic is located. The donor is given a “new donor” sticker to alert the clinic staff that the donor will need extra assistance. The new donor also gets a form to take with them to later booths.

Then, the donors have their haemoglobin tested. If it is too low, the donor cannot give blood. Whenever a donor is rejected from any stage of the donation process, their forms are destroyed. They are also added to the total number of donors who came out to the clinic. The clinic organiser also keeps track of the number of people who are rejected from each section of the clinic screening process.

Next, donors are instructed to read a confidential questionnaire about places they have been or things they have done that could affect their blood. When the questionnaire is filled out, the donors move on to a screening booth. Another nurse goes over the answers of the questionnaire with the donor and if all of the answers are fine, the donor’s blood pressure and temperature are taken. If their blood pressure and temperature are acceptable, the donor signs some forms and they are ready to give blood. 

The donor is given a few bags and labels that are coded with a number. The number is associated with the unit of blood that is taken. It is linked in a database to the donor. If the blood is discovered to have a disease later on, the donor will be notified and all blood products manufactured from that unit of blood can be destroyed.
Giving blood takes anywhere from 10 to 45 minutes. After that, donors rest 10 minutes or so. The donor then gets a cookie and a drink at the rest area from Sarah Soda (who tells them tales of snow shoeing and her paperclip collection). The donor’s card is stamped with date, so they can remember when they last gave blood. (This information is also recorded in the Canadian Blood Services Database).

The team packs things up at 8:00 in the evening and they return to Timmis. The clinic organizer makes sure that the local newspaper is told how many people came to the clinic and how many units of blood were collected.

7. Kincardine Shakespearean Theatre

The Kincardine Shakespearean Theatre (KST) opened their doors to audiences in June 2001. The KST has an acting company of about 20. Current plans are to perform on 2 Shakespearean plays and 1 Canadian play every year, however this is still subject to change. During the summer, there is a matinee on Sunday and Saturday and an evening performance on Tuesday, Friday and Saturday. The seating in the theatre is arranged into A, B, C and D categories. 

The KST has hired you to come up with an automated ticket system for their performances. Calendars outlining show times are mailed to people who request them and they are also distributed to local businesses for people to pick up. The calendars are also available on-line. Customers will be able to order tickets by phoning a toll-free number or by coming into the ticket office in person. 

While work sampling, you observed the following exchange between a clerk and customer in the Box Office.

Customer: I would like a group of 4 ‘A’ tickets for the matinee of the Tempest on July 5.
Clerk: Just wait a minute and I’ll find the diagram of the tickets available on that day… (finds diagram)… I’m afraid I don’t have any groups of 4 ‘A’ tickets left for that performance, however there is a group of 4 ‘B’ tickets available.
Customer: No, I’m afraid that won’t do. My uncle is coming and he hates sitting at the back of the theatre.
Clerk: Would you mind sitting apart? I have a group of 3 ‘A’ seat available and a single ‘A’ seat.
Customer: No, I couldn’t send one of us off to sit by themselves.
Clerk: What about that evening? I have a set of 4 ‘A’ seats available that night.
Customer: No, my wife always falls asleep if we go to the theatre at night.
Clerk: What about the matinee the next day?
Customer: No. My uncle is going on a business trip then.
Clerk: Is there another day that would work?
Customer: How about August 7th?
Clerk: I haven’t got any sets of 4 ‘A’ tickets available for the matinee that day, but I do have a 2 sets of ‘A’ tickets. Will that work?
Customer: Yes. That would be fine.
Clerk: Wonderful. If you could just tell me your first and last name?
Customer: George Smith.
Clerk: And your phone number please?
Customer: (519) 222-5555
Clerk: And how will you be paying for that?
Customer: With my VISA. (hands it over)
Clerk: Thank you. (Rings it through, passes back bill to be signed, returns receipt. Clerk then prints the tickets and hands them over. Finally, Clerk puts ‘X’s on appropriate seats on diagram).

If the customer books the ticket over the phone, the process is very similar, but the credit card information is taken orally. The Clerk must also record the mailing address of the customer. The receipt and tickets are put in an envelope and mailed to the customer. In addition, customers who book over 8 tickets either by phone or in person are given a 5% discount on their tickets. Family members of the theatre company’s cast and crew get a 20% discount.

KST requires the following reports from your system at the end of their season: