Student Guide


 Introduction to the Guide
 The Module and Its Delivery
 The Module and Its Aim
 The Module Objectives
 The Module Content
 Achieving the Aims and Objectives
 Assessment
 The Module Prerequisites
 Resources
 Preparing for and Using the Session
 Your Learning Schedule
 Session Plans
 Assessment of the Module
 Sample Assigment
 Sample Projects


Introduction to the Guide

The aim of this guide is to give you all the information and guidance you need concerning what the Relational Database Management System module will involve and how it will be delivered. In addition it will make clear:

The guide therefore begins with a general section covering the module aims and objectives, its syllabus content, the essential resource materials and the mode of its delivery.

A full explanation of the learning process is given, with information and advice about how sessions are structured, about your role and the role of the tutor in the learning process, and on the kind of pre-session and follow-up work used to prepare for and reinforce the session learning.

A detailed schedule of work follows which gives you session by session plans of what you will be doing during the whole module.

Each session plan sets out:

 The final section of the guide gives you details of the module assessment methods, the assessment schemes and criteria by which these will be marked and a sample assessment for guidance only.
 

The Module and Its Delivery

Organisations now see information as a key resource and depend upon computerised information systems for everyday administrative transactions. Managing these systems depends upon capitalising on the opportunities of information technology whilst avoiding the risks which would be detrimental to the control of sensitive information.

Databases and database technology are having a major impact on the growing use of computers. Databases are playing a critical role in almost all areas where computers are used, including business, engineering, medicine, law, education, and many other areas. The word database is in common usage, but in this module a formal definition is given. The fundamental concepts necessary for the design, use and implementation of database systems are provided.
 

The Module and Its Aims

The study of database theory, design and management is an important component in modern education. This module provides the students theoretical background and knowledge about current Relational Database Management Systems (RDBMS). The module is designed to provide effective integration of theoretical material and its application. The material stresses the fundamentals of database modelling and design, the languages and facilities provided by database management systems, and techniques for implementing database systems.
 

The Module Objectives

On completing this module, you should be able to:

 
The Module Content

The syllabus content to be explored in the module covers eight areas:

Achieving the Aims and Objectives

A wide range of teaching methods and materials will be used to achieve the aims and learning objectives of this module. The principal vehicle for learning will be sessions combining lecture and seminar discussion, supported by preparatory and follow-up reading. No single core text will be used for study, but a wide range of material from a variety of source texts will be covered by brief presentations from both the tutor and students. Each session will include opportunities for class discussion of the theoretical material and of personal experiences relating to it.

It will be vital for the learning process that all students undertake the extensive reading conscientiously and critically, and commit themselves fully to the sessional discussions and activities.

The course also contains a workshop element aimed at integrating material from across the sessions.

This student and activity-centred approach aims to achieve a high degree of learning and concept assimilation in students; developing their awareness and skills practically and making their learning accessible and ‘usable’ in their everyday work.

Throughout the module students will be encouraged to make use of their own real life experiences, to adopt a reflective and questioning approach to database design and implementation activity.
 

Assessment

Formal assessment in this module will be achieved through one assignment.

The sessional work will involve both seminar discussions and student activities. This provides the opportunity for the tutor to assess the students’ contributions continuously on a formal basis. It also provides a natural forum for informal feedback to students as they compare their own understanding of reading and presentations, with that of other group members and of the tutor.

In line with the teaching and learning philosophy of this module, the written assignment will be aimed at encouraging students to develop their understanding by integrating theory with experience.

The "Assessment of the Module" gives further details of the assessment process.
 

The Module Prerequisites

No specific prerequisite studies are required for students to be able to complete the module.
 

Resources

A full list of the core materials by tutors and students follows, along with additional texts and resources recommended for library or student resource centre use.

Preparing for and Using the Sessions

For the learning process to be effective, committed pre-session preparation and follow-up work for lectures, workshops and case study sessions is essential. The detail of the preparation required is shown in the learning schedule of this guide, but the general pattern of session work and preparation is set out below.
 
        Lecture Sessions

Your Learning Schedule

 

Session Plans

Session 1

Topic: Database Concepts and Architecture

Practical Outcomes


Session 2

Topic: Database system development. Data Modelling using the Entity Relationship (ER) Model

Theoretical Outcomes

Practical outcomes


Session 3

Topics: Database Models. Mapping ER Model to Relational Schema

Theoretical Outcomes

Practical Outcomes


Session 4

Topic: Relational Database Design

Theoretical Outcomes

Practical Outcomes


Session 5

Topic: Relational Algebra

Theoretical Outcomes

  Practical Outcomes

 

Session 6

Topic: SQL

Theoretical Outcomes

Practical Outcomes


Session 7

Topic: Query Optimisation

Theoretical Outcomes

Practical Outcomes

Session 8

Topics: Current Trends in Database Systems

Theoretical Outcomes


Assessment of the Module

Here we set out the specific assessment aims and procedures for Relational Database Management Systems module.


Assessment and Assessment Aims

Relational Database Management System students will be assessed both formally and informally.

 The informal assessment processes provide you with immediate and continuous feedback on your ability and understanding as you progress through individual topics. The formal assessment processes provide feedback as to your level of achievement and whether you are reaching the required standards.

 There is no formal written examination at the end of this module. Only the one set assignment set will count towards your overall assessment.

 The formal assessment is intended to verify how well you are able to grasp the concepts and issues delivered in the programme. They test how far you have achieved the module aims - that is, how well you can:


Assignments

The minimum pass marks for individual assignments and for the complete module are as set out in the overall programme regulations.

There is one assignment in this module that is assessed formally.


Guidelines on Writing the Assignments

 The work that you submit must be original. This does not prevent you using material from wider reading in your assignment work, to support it with examples or relevant opinions and ideas, or to place it within the context of existing knowledge. It does mean, however, that you must make clear the sources of any such material. Above all, avoid plagiarism!


Assessment

 You should raise with the tutor any questions about what is required in an assignment or how it will be assessed when the assignment topic is introduced, during the assignment support session, or as soon as they arise when you are working on it. Do not wait until an assessment has been made to clarify any uncertainties you may have.

If you are unhappy with a given assessment you should approach your tutor initially and discuss it in the light of the assignment criteria.

The conditions governing students' passing of modules and progression within the programme are set out in the course assessment regulations. If you are dissatisfied with the outcome of an examination board you should consult these conditions to ascertain the appropriate appeal conditions and procedures.

 Should you consider your ability to complete an assignment on time or adequately has been adversely affected by illness or other mitigating circumstances, you should inform your tutor and course leader immediately and produce any supporting evidence you can for consideration. The rules and procedures governing such consideration are set out in the programme regulations.


  The Assignments and Assessment Schemes

The following indicative assessment scheme and assignment are for your information and guidance. Depending on the individual assignments, the assessment scheme may vary to ensure its suitability. For example, where the tutor provides the assignment data, marks for selection of data may be re-allocated between other criteria. However, you will always be told the precise assessment criteria when the assignment is set formally.


Sample Assigment

Using the case study for your study:

  1. Draw the ER diagram
  2. Give the Entity Definitions
  3. Give the Relationship Definition
  4. Give the Constraints and assumptions
  5. Using the algorithm for mapping the ER schema produce a set of relations
  6. Produce a set of normalised relations
  7. Produce the relational schema
  8. Use SQL (standard SQL from Assess or Fox Pro) to produce examples of querying, table creation, table access and data manipulation.

Marking Scheme

Project Description 10 marks 
E-R diagram  10 marks
Entity definitionn 10 marks
Relationship definition 10 marks 
Constraint Definition 10 marks 
Produce a set of relations 20 marks
Produce a set of normalised relations 20 marks
Applying SQL 10 marks
Total 100 marks

Below you will find six sample assignments each of which covers the aims of the course, but which vary slightly. The tutor is required to choose the most appropriate assignment which best maps onto the delivery style employed.


Sample Projects

Project One: The Global Insurance Agency

Project Two: The Art Gallery

Project Three: The Prescription Database

Project Four: Metropolitan Hotel Reservation System

Project Five: Passport Credit Card Processing

Project Six: The Rental Car Agency, Auto-Rent
 



Project One: The Global Insurance Agency

General Description

Global is an independent insurance agency that sells policies from several different companies. It has only one location and five agents, one of whom is the owner. They each have about 3000 customers. The agency sells only individual homeowner’s, automobile and life insurance policies.

A basic homeowner’s policy covers a client’s home and its contents against fire, theft, lightning and similar dangers. The policy lists the specific dangers included and those excluded, and the client pays more for policies that are more inclusive. There is usually a "deductible", a fixed amount that the client must pay before the insurance company pays.

Automobile insurance is designed to provide coverage for damage caused by use of a car. Policies provide varying limits for liability, which is damage of driver clauses, medical payments, personal injury protection, protection from uninsured motorists, collision and comprehensive coverage. Collision covers damage to the insured’s own car from colliding with an object or another car, while comprehensive covers damage to the insured’s car by fire, theft or other clauses not including collision. Both collision and comprehensive have associated deductibles.

There are several types of life insurance, including term, whole life, limited payment life, endowment, and combination plans.

Basic Operations

Names, addresses and telephone numbers of prospective customers are obtained from various sources. Agents contact potential clients by mail or phone and try to set up appointments. At these meetings, the agent reviews the client’s present insurance coverage and tries to identify the policy or policies that will best meet the client’s insurance needs. To buy insurance, the client fills out an application for coverage and the agent draws up a tentative contract furnished by the appropriate insurance company. The application is signed by both the client and the agent, and forwarded to the insurance company. The insurance company may conduct an investigation, the nature and scope of which are terminated by type of insurance, the characteristics of the policy, and the identity of the client. If the outcome of the investigation is positive, the company sends a policy to the agent, who signs it and meets with the client for his or her signature. Both the agent and the client keep copies of the contract. If the outcome is negative, the agent is informed of the problem and he or she meets with the client to discuss alternatives. The policy is in effect once the client signs it and makes the first insurance premium payment.

Every policy carries with it a schedule of premiums, which varies with the type and coverage. Each policy also provides a commission for the agency. The commission varies with the insurance company, policy type and coverage. However, a given insurance company will always pay the same commission for the same policy, regardless of the agency or the seniority of the agent. The Global Agency works on a strict commission system and does not pay a salary to any of its agents. It splits the commission received for each policy with the agent who sold it.

Once a policy has been sold, the agency submits premium bills to the client, collects payment, and sends the payment, minus its commission to the insurance company. If a client fails to pay premiums, the agent who sold the policy is informed so that he or she can contact the client. There is a short “grace period” once a payment is missed, during which coverage continues and the agent attempts to resolve the problem. If the grace period expires without payment, the policy is dropped by the insurance company. Some life insurance policies pay dividends at the end of the year. The client may choose to receive dividends in cash or to use them as credit towards premium payment.

Claims can be made on insurance policies as specified in the policy itself. Clients or their beneficiaries contact the agent to file such claims. For an automobile insurance policy, claims are made when the car is involved in an accident, damaged or stolen. Homeowner’s policy claims are made when property is damaged or lost. Life insurance claims can be made by the beneficiaries of the dead, or by the insured, if the policy has a cash value. Once the insurance company receives a claim, it assigns an adjuster who investigates the legitimacy of the claim and handles payment, if any. The agency is not involved in the following up on the claim.

Information Needs

The agency now handles all records manually, using an office staff of two clerical workers. The owners wishes to computerise all data relating to insurance policies and has hired you to design a database for this purpose. You are to design a system that can be maintained by the clerical staff and accessed by the agents. The following reports, documents and transactions will be used:

1. Application for Life Insurance: This form is filled out by the client and the agent during the initial visit. It contains: Agent name, Client name, Address, Years Living There, Phone, Date of Birth, Sex, Previous Address, Years at Previous Address, Employer Name, Employer Address, Years there, Previous Employer, Previous Employer Address, Years at previous Employment, Marital Status, Number of Children, Height, Weight, Smoker, High Risk Employment, High Risk Hobby, History of Mental Illness, History of Heart Disease, History of Cancer, Date of most Recent Physical, Physician Name, Physician Address, Physician Telephone, General Condition.

The client provides information on previous address only if he or she has been at the present address less than five years. The same rule applies to previous employment. The applications contains a few questions designed to help the insurance company assess the health of the applicant. Some of these, such as Smoker, are flags, that contain only true or false values. The form contains a printed statement verifying that all answers are complete and truthful, and agreeing that the company has the right to contact the client’s physician for additional health information. The company also reserves the right to request a physical examination. The client’s signature signifies agreement to these conditions.

2. Application for Automobile Insurance: This form contains: Agent Name, Client Name, Address, Phone, Liability, Medical Payments, Personal Injury, Uninsured Motorist. For each driver it lists: Lic State, Lic No, Date of Birth, Sex, Number of Points. For each car it lists: Make, Model, Style, Year, Mileage, Primary Driver, Used For Work, Collision, Comprehensive, Collision Deductible, Comprehensive Deductible.

The application lists up to four drivers to be covered by the policy and up to three cars. The coverage for liability, Medical Payments, Personal Injury and Uninsured Motorist is the same for all drivers and all cars covered by the policy, but the collision and the Comprehensive deductibles may be different for each of the three cars covered. The amount of collision and comprehensive coverage is always the actual cash value less any deductible.

3. Premium Invoice: This form contains: Insured Name, Address, Insurance company Name, Policy Number, Policy Type, Policy Coverage, Amount Due, Date Due, Agent Name. The invoice is generated by the agency, which receives the payment and sends the appropriate portion to the insurance company.

4. Monthly Activity by agent: This report is generated each month to track the sales activity of each of the agents. It contains: Agent Name, Number of First Appointments, Number of Auto Policies sold, Annual Total New Auto Premiums, Number of Homeowner’s Policies sold, Annual Total New Homeowner’s Premiums, Number of Life Policies Sold, Annual Total New Life Premiums.

5. Automobile Insurance Policy: Most of this document is preprinted, with the insurance company’s name and address, and the terms and condition of the insurance. It contains: Policy Number, Insured Name, Insured Address, Liability Coverage, Medical Payments Coverage, Personal Injury Coverage, Uninsured Motorist Coverage, Premiums, Valid Until. For each driver it lists: Make, Model, Style, Year, Collision, Collision Deductible, Comprehensive, Comprehensive Deductible.

6. Policies by Agent <name>: This report lists all the policies, new or old, that the named agent has sold and is responsible for. It appears in order of customer’s name. The items are: Customer Name, Address, Telephone. For each policy belonging to the customer, the following data appears: Insurance company, Policy Number, Insurance Type, Type of policy, Coverage, Annual Premiums, Annual Commission. At the bottom of the report, the following summary appears: Number of Customers, Number of Policies, Total Annual Premiums, Total Annual Commission.

There are similar applications and policies for other types of insurance, transactions to calculate agent’s commissions, to record payments, and other reports that would be useful.

Project Two: The Art Gallery

General Description

The Art Gallery accepts works by living contemporary artists to be sold on a commission basis. It currently offers work from about 100 artists and sells approximately 1000 pieces each year. The average selling price is several thousand dollars. There are about 5000 customers who have purchased pieces from the gallery. The sales staff consists of the gallery owner, Alan Hughes, and four sales associates. Their activities are supported by an office staff of two people.

Basic Operations

When an artist wishes to sell one or more works, he or she contacts the gallery. Alan Hughes, the owner, visits the artist’s studio and selects the works to be sold through the gallery, working with the artist to set an asking price for each piece. The sales staff attempt to sell the work at that price, but customers may negotiate with salespeople, so that the actual selling price may be below the asking price. In that case, the final selling price must be approved by the artist. The commission charged by the gallery is 10 percent of the selling price. The gallery splits the commission with the salesperson who makes the sale. Any salesperson can sell any work in the gallery. However, customers work with a single salesperson when they buy each piece, so that the salesperson’s portion of the commission for a single piece goes to only one employee.

The gallery promotes the works by holding showings featuring various pieces. The showings are advertised in newspapers and other media, and past customers are sent personal invitations. A showing provides an opportunity for the public to see the pieces and to meet the artist or artists whose works are featured. Works of art that have been featured at a showing remain on display until they are sold or returned to the artist. A piece may be purchased at the showing or any time afterward. Occasionally, a work may be purchased from the gallery prior to the show and included in the exhibit to provide the public with a better view of the artist’s work.

Information Needs

At present, all data relating to artists, unsold works, sales, and customers are kept in separate files. The files have the following data:

The Unsold Work file keeps track of the works currently on exhibit, and is used by salespeople. Each work must have a title, which is unique to the artist but may not be totally unique to the gallery. For example, many artists may have works such as “Composition Number 5,” but no artist has two works with that title. Each work is by a single artist. The type refers to the type of work, which may be painting, sculpture, collage, and so on. The medium refers to the materials used in the work, such as oil, water-colour, marble, mixed, and so on. A piece using more than one medium is categorised as "mixed". The style means the style of the work, which may be contemporary, impressionist, folk, and so on. The size is expressed in units appropriate for the work; for example, for a painting, the size would be the number of inches in height and width. The Artist file is used to keep track of artists and to report to the Internal Revenue Service the amount of sales for each artist. Sales Last Year is a dollar figure giving the total amount of sales for that artist last year, and Sales Year To Date gives the total amount of sales so far this year. Information is kept about the usual type, medium, and style of each artist, where this words have the same meanings as in the Unsold Work file. The Customer file keeps track of customers who have made purchases, and includes information about the dollar amount of their purchases last year and so far this year. When a purchase is made, a receipt is made out for the customer, a payment check and stub are made out for the artist, the commission is allocated between the gallery and the salesperson, and all files are updated individually.

Alan realises that a database would provide more information from the stored data than is available now. He also wants to capture data not presently stored. In addition, he foresees that the gallery may begin to accept works owned by collectors as well as works directly from artists. Your database design should include the possibility that the owner is not the artist, storing both artist data and owner data. He would like to hire you as a consultant to design and implement a database that the office staff can maintain. The database should be capable of producing the following reports:

  1. Active Artists: Lists data about each artist, including Artist’s Name, Address, Telephone, Usual Type, Usual Medium, Usual Style, Last Year’s Sales, This Year’s Sales
  2. Collector Owners: Lists data about each owner, including Owner’s Name, Address, Telephone, Last Year’s Sales, This Year’s Sales
  3. Works for Sale: Lists data about each work, including Title, Artist, Type, Medium, Style, Owner’s Name, Asking Price, Date of Showing
  4. Sales This Week: Lists data about all sales of works during the current week. Divided by salesperson it shows Salesperson Name. For each work sold by that person, it shows Selling Price, Title, Artist, Owner, Customer Name, Customer Address, Date of Sale
  5. Customers: In alphabetical order by name, shows Customer Name, Address, Telephone, Dollar Amount of Last Year’s Purchases, Dollar Amount of This Year’s Purchases

It should print receipts such as the following

  1. Customer Receipt: Date of Sale, Name, Address, Title, Artist, Type, Medium, Style, Size, Selling Price, Salesperson
  2. A payment stub for the artist or owner should be printed:
  3. Payment: Owner Name, Owner Address, Artist Name, Owner Social Security Number, Title, Type, Medium, Style, Size, Salesperson, Selling Price, Amount Remitted
  4. Alan would like to target potential customers as well as present ones, by making lists of all those who attend showing or whose names are gathered in other ways. For each present and potential customer, he would like to keep the identifying data now in the customer file, and add information about the customer’s preferences, such as the name of preferred artist, type, medium, and style. He hopes to increase sales and hold down costs by using information to make up targeted invitation lists for showing of works that match customer preferences. For example, he would like to be able to get a report such as the following:
  5. Preferred Customer Report: Artist, Title, Type, Medium, Style. For each person who might be interested in the work, it lists Customer Name, Customer Address, Preferred Artist, Preferred Type, Preferred Media, Preferred Style.
  6. Here, the values for artist, type, medium, and style of the piece may match some of the values of the customers preferences, and the gallery can send private invitations to those customers.Alan would also like to be able to combine artist and works data, making lists of all the works, whether sold or unsold, of a particular artists, in a report such as the following:
  7. Artist Report: Lists Artist Name, Artist Address. For each work by this artist, it shows Title, Type, Medium, Style, Asking Price, Selling Price, Date Sold. If a work has been sold, there will be values for Selling Price and Date Sold. Unsold works will have blanks in these areas.
  8. He would like a similar report for owners and their works, as follows:
  9. Owners Report: Shows Owner Name, Owner Address. For each work owned, it lists Artist Name, Title, Type, Medium, Style, Asking Price, Selling Price, Date Sold.
  10. Alan might also like reports on the sales of individual salespeople for the current quarter or the past year:
  11. Salesperson Performance Report: Lists Report Starting Date, Report Ending Date, Salesperson, Total Sales This Period. For each work sold by that person, it shows Title, Artist, Asking Price, Selling Price.
  12. This report would be generated for a period starting with whatever date is selected (e.g. January first of the current year) and ending with another selected date (e.g. today’s date ). It provides an individual listing of each of the works sold by that person during the period, as his or her total sales for the period chosen.


Project Three: The Prescription Database

General Description

A local independent pharmacy now keeps manual records of prescriptions. The pharmacy serves about 1000 patients, filling approximately 4000 prescriptions per year. There are four pharmacists: the owner and three associates. They alternate shifts, with two working weekdays, one during the evening hours and one during the weekend.

Basic Operations

Patients normally come to the pharmacy in person with prescriptions from their physicians, dentists, or other health practitioners. Most prescriptions can be refilled a specified number of times, as determined by the physician, who writes the number of the original prescription. Any prescription marked “NO REFILLS” cannot be refilled, while one marked “UNLIMITED REFILLS” can be refilled any number of times. Refill requests are often telephoned in by the patient, to be delivered or picked up later. At present, the patient must provide the prescription number, but your design should require only the patient’s name and a general description such as date or drug name. The pharmacy has to take special care when filling prescriptions for controlled substances such as tranquillisers. To prevent abuse, periodic reports about the number of such prescriptions are required by various government agencies. Prescriptions for controlled substances can never be refilled.

When a prescription is filled, a label and a receipt are printed. The label is attached to the bottle or other container, and the receipt is given to the patient for insurance or tax reporting purposes. To permit third-party billing, the pharmacist must keep track of all prescriptions filled for patients who have health plans or coverage for prescriptions drugs so that the insurance company can be billed directly. Some plans require that the patient pay the pharmacy a flat fee each prescription(typically, $1). Others require no payment. The pharmacy must obtain the customer’s signature at the time the drug is issued, so that it can provide proof, if needed, that the drug was actually issued. However, the signature is kept in a manual ledger, outside the proposed database.

Information Needs

The burden of maintaining the manual system is increasing, because the number of prescriptions is growing, and because customers expect more services than in the past. For example, customers expect the pharmacist to be able to find a prescription record and refill a prescription without providing the prescription number, to do third-party billing and to check for possible drug interactions with medications they are now taking before filling a prescription. Families and individuals expect to be able to obtain a detailed annual or on-demand report of prescription charges for tax or insurance reporting. To compete with large chains that offer these services, the owner has decided to hire you to design and create a database system that replaces the manual one and provides support for these new services.

The database must print several routine reports:

1. Label: Rx Number, Doctor Name, Patient Name, Patient Address, Directions, Drug Name, Form, Strength, Pharmacist’s `Initials, Date Filled, Original Date, Previous Date, Number of Refills Remaining.

The Rx Number is issued sequentially by the system. If the prescription is a refill, the Rx number for the original prescription is used. There are several standard directions, such as “Four times a day” or “Every six hours”. If the directions are not one of the standard phrases, the label reads “As directed by physician”. Drug name is the generic name of the medication. It may be a brand name or a generic name, depending on which is used in filling the prescription. Medications come in several forms, including tablets, capsules, creams, elixirs, and others. The label tells the quantity of medication, which varies from the form (e.g. for capsules or tablets it is the number, but for elixirs it may be the number of fluid ounces). The strength of the medication is also given, usually in milligrams. The initials of the pharmacist who actually fills the prescription are included. "Date filled" refers to the actual date issued. Original Date is the date of the original prescription, and the Previous Date, if any, is the date of refill prior to the present one. Number of refills Remaining is decreased by one every time a refill is made, except when unlimited refills are permitted.

2. Receipt: All of the information on the label, plus Price, Amount Due - Here, Price is the entire cost of the prescription, while Amount Due is what the patient would pay the drugstore. For patients who don’t use third-party billing, these are identical. For those using third-party billing, Amount Due is Price minus the anticipated insurance payment.

3. Insurance Claim: A universal claim form is used for the third-party billing. It contains the following information: Patient Name, Patient Address, Insurance Company, Policy Number, Insured Name, Relationship to Insured, Patient Date of Birth, Rx Number, Drug, Form, Strength, Quantity, Date Filled, Price, Amount Paid.

4. Family Report: This report, generated as needed, contains the following information: Report Date, Starting Date. For each member of the family, it shows Last Name, First Name, Patient Address, Date of Birth. For each prescription for that family member, it lists Rx Number, Date, Drug, Form, Strength, Quantity, Date Filled, Price, Amount Paid. The report lists each family member who had prescriptions filled during the period from a Starting Date to the Report Date. For each member, it lists the information abut every prescription filled during the period.

5. Controlled Substance Report: This report is filled monthly or quarterly with agencies that supervise distribution of controlled substances. It contains: Report Date, Starting Date. For each physician who has written prescriptions for controlled substances, it shows Physician Name, Physician Address. Fop each prescription from that physician, it lists Rx Number, Patient Name, Patient Address, Drug, Form, Strength, Quantity, Date Filled. The report is in order Physician Name etc and lists information about every prescription for controlled substances written by that physician and filled by the pharmacy.

6. Drug Inventory Report: Drug Name, Generic Name, Form, Strength, Controlled Substance, Quantity on Hand, Quantity on Order, Reorder Point, Supplier Name, Supplier Address, Unit Cost. We assume, that there is only one supplier for each drug. Controlled Substance is a flag that displays a value of “true” for drugs that are controlled substances.


Project Four: Metropolitan Hotel Reservation System

 General Description

The Metropolitan Hotel is an independent hotel located in a midsize city. It has 300 guest rooms, 10 meeting rooms, and 5 banquet rooms. The rooms are normally 80 percent occupied, with an average length of stay three days. Meeting rooms are reserved at least a week in advance. The banquet rooms are used for various social functions such as dances and wedding receptions. They are usually booked several months in advance. The hotel also has two restaurants and a coffee shop. In the lobby there are several small shops, including a jeweller, a beauty shop, and a clothing boutique. The lobby shops are operated by concessionaires who pay a flat rental fee.

Basic Operations

Guest room reservations are usually made by individuals or their travel agents several days in advance, using a credit card number to guarantee the reservation. At that time they specify the type of accommodation desired, the number of people who will occupy the room, the date and estimated time of arrival, and the length of stay. The hotel makes the reservation and returns a confirmation number. Customers who have confirmed reservations are guaranteed a room. If they do not show up and do not cancel, they are charged for one day. Guest may extend their stay beyond the stated length provided that there are rooms available. Hotel guests may charge meals at the restaurants or coffee shop to their rooms, and the charge will appear on their room bill. Room service charges for items delivered to a guest’s room or a meeting room are also added to the bill. Telephone calls from a room are automatically charged to the room. Telephone charges include the actual charge determined by the telephone company and a service fee of 50 cents per call. There is no charge for incoming calls. Meeting rooms are usually booked about a month in advance. A deposit is required at the booking time. They are ordinarily reserved by business people for meetings. Each meeting room is furnished with a large table and chairs for approximately 25 people. Room dividers can be rolled back to combine two or more rooms and provide a larger meeting space. There is a flat rate for use of each room for a four-hour period. At the customer’s request, audio-visual equipment owned by the hotel can be set up in the room at no additional charge. If special equipment that must be rented is needed, the hotel charges the rental and a setup fee. There are additional charges for food or beverage service, if any. Bills for the use of meeting rooms are prepared immediately after the meeting and customers must pay the bill within 30 days. The five banquet rooms are designed to hold 100, 150, 200, 300, and 500 people, respectively. Customers, who may be individuals or corporations, book the rooms for various events, such as political fund-raisers, dances, or wedding receptions. The customer must guarantee a minimum of 75 percent of the occupancy in order to reserve a banquet room. Reservations are accepted up to two years in advance. Each room can be rented for two banquets per day, one in the afternoon and one in the evening . Each banquet is scheduled to last five hours, unless special arrangements are made. There is a one-hour room preparation time between banquets. Customers who reserve banquet rooms are required to provide a deposit which they forfeit if they cancel. When a customer reserves a banquet room, he or she specifies the date, the time, the number of guests expected, the room desired, and the menu. The hotel estimates the cost per person, assigns the room ad draws up a contract which is then signed by both parties. The customer pays a deposit of 10 percent. As the event nears, the final menu is elected and the final guest count is provided, usually resulting in modification of the contract. The customer will be charged according to the final guest count, even if fewer guests attend. If additional guests attend, there is a charge for each extra person settings, floral arrangements, waiters and waitresses, and food and beverage service, all of which are specified in the contract. Musicians, entertainers, photographers, or other nonhotel employees are provided by the customer, but the hotel management must be told in advance who will be providing any of these services, and can refuse to admit anyone it deems unsuitable. The final bill is prepared immediately after the event, and the customer must pay it within 30 days.

Information Needs

The hotel needs a database to handle guest room reservations, check-in with guest room assignment, guest room billing, meeting room reservations and billing,, concession rental, and banquet room reservations, contracts, and billing. Your assignment is to design this database. Some of the reports or transactions requires are the following:

1. Room Reservation Transaction: A reservations clerk provides the following information: Guests, Arrival Date, Arrival Time, Expected Departure Date, Room Type Desired. The system returns: Confirmation Number. Normally, the reservation is entered and a confirmation is generated by the system. However, if the hotel is already fully booked for the date specified, the reservation will not be entered and the message “No available rooms” will be returned. The clerk can re-attempt the transaction with a different room types, provided the guest agrees.

2. Check-in Transaction: The check-in transaction requires the Confirmation Number, if any, or data similar to the room reservation transaction. The output is the Room Number of the room assigned.

3. Guest Room Bill: The bill heading has the following data: Invoice Number, Guest Name, Address, Telephone, Room Number, Number of Persons. For each day, the following data appears: Date, Room Charge, Room Tax, Room Service Charges, Restaurant Charges (for each, Restaurant Name, Amount), Telephone Charges. The following data appears at the end of the bill: Total Charges, Discount, Net Charges, Amount Paid, Amount Due, Method of Payment, Credit Card Type, Credit card Number. The hotel may offer discounts as promotional devices, or give corporate discounts. The amount of the discount, if any, appears on the bill, and the Net Charges entry represents the Total Charges less any discount. The guest may pay by cash, check, traveller’s check, or credit card. If a credit card is used, the type and account number appear on the bill. Normally, the guest pays the bill in full and the Amount Due is zero. Otherwise, the Amount Due is Net Charges less Amount Paid.

4. Banquet Room Contract: The banquet room contract contains the following data: Contract Number, Contract Date, Cluster Name, Address, Telephone, Date of Event, Time of Event, Room, Number of Persons, Type of Event, Menu, Table Decor, Floral Arrangements, Per-Person Cost, Total Cost, Deposit, Balance. The customer chooses the menu from several possible ones, and the code for the menu appears on the contract. Table Decor includes colour of linen and other details, and Floral Arrangements indicates the number and size of centrepieces. The Total Cost is found by taking the product of the Per-Person Cost and the Number of Persons, and adding any charges for special decor or floral arrangements.

5. Concession Rent Bill: This bill is produced monthly and includes the following data: Bill Date, Location, Renter Name, Address, Telephone, Type of Business, Monthly Rent, Amount Past Due, Date of Last Payment, Amount of Last Payment, Total Amount Due.

6. Banquets Scheduled for Date mont/day/year: This report shows all the events planned for the banquet rooms on the given date. It may be generated routinely each day for that day’s events, or may be produced on request for any date desired. It lists the date in the heading, and the following items: Room, Time, Type of event, Customer Name, Menu, Table Decor, Floral Arrangements, Number of Persons.

7. Guest Room Utilisation for Month of <Month, Year>: This monthly report summarises the utilisation of guest rooms for the month indicated. It is normally generated at the end of the month. It includes a line for each day of the month, showing Date, Number Available, Number Unavailable, Number Occupied, Number Unoccupied, Number of Reservations, Number of NoShows, Total WalkIns(see below). The Number Available indicates how many guest rooms have been rented on a particular day. The Number Unavailable shows how many could not have been rented, because they were undergoing repairs, were held for hotel employees or other nonpaying guests, or other reasons. The total of these two columns should always be 300, the number of guest rooms in the hotel. The Number Occupied means the number of rooms rented to paying guests who actually checked in, while the Number Unoccupied is the number of available rooms for which no guest checked in. The total of these two columns should be the number of rooms available. NoShows are people who made reservations but did not check in, while WalkIns are guests who checked in without reservations. The report contains the figures for each day and the totals for the month in each category indicated.
 


Project Five: Passport Credit Card Processing

General Description

Passport is a credit card company with customers throughout the United States. At present, the card is distributed by major banks in all 50 states . There are approximately 500,000 customers holding Passport Cards, and the card is accepted by merchants throughout the country in payment for goods and services of all types . Customers average five purchases per month and receive a monthly bill reflecting new purchases posted during the last month, as well as any amount carried over from the previous month . They must pay at least 10 percent of the balance each month . Any unpaid amount is carried over to the following month, with an interest charge of 1.5 percent of the unpaid balance. Payment is due within 20 days of receipt of the bill.

Basic Operations

Customers apply for the card through local banks . The application lists the terms and conditions of the charge agreement and requires the customer’s signature . The bank runs a credit check and if the result is satisfactory, it forwards information about the new customer to the Passport central office, keeping a microfilm copy of the application . The Passport Agency sets an initial credit limit for the account, issues the card and mails it to the customer, along with a letter specifying the terms and conditions, advising the customer to sign the card immediately, and stating that using the credit card constitutes legal acceptance of the terms and conditions . A week later, the agency sends a follow -up letter reminding the customer that a card has been mailed and asking him or her to call the agency immediately if the card was not received . Fewer than 1 percent of cards are lost during the initial mailing, and almost all of these cases are reported to the agency by customers receiving the follow-up letter . When such a report is received, the card is cancelled immediately and a new one is issued . Any charges from the lost card are covered by Passport, and law enforcement agencies are alerted to try to recover any losses .

The customer uses the card at any of the shops, hotels, restaurants, department stores, travel agencies, or other locations that accept it . When a purchase is made, the salesclerk fills out a sales slip, filling in the total amount of the sale and taking an impression of the card showing the account number, expiration date, and cardholder name and an impression of a plate showing the merchant number, name, and location . Merchants set their own policies regarding authorisations . For example, they may require that all purchases over $50 be authorised by calling the Passport authorisation service . Authorisation involves checking the customer’s credit limit, current unpaid balance, and previously approved authorisations not reflected in the current balance . If the customer still has credit available, an authorisation code is returned to the clerk, who writes it on the sales slip . Otherwise, the sale is rejected . Merchants are reminded to check the signature and those who accept unauthorised sales for credit cards that turn out to be stolen are responsible for their losses . The on-line authorisation process is supplemented by a monthly listing of card numbers of lost or stolen cards, which the merchant can check manually . The customer signs the sales slip, which is in triplicate . The original is the Passport copy, the first carbon is the merchant’s copy, and the third copy is the customer’s .

The merchant returns the original, called the Receipt of Charge, or ROC, to the Passport processing office . There the ROC is microfilmed and the image is stored in a filing cabinet, from which it can be retrieved if there is a problem with the transaction . However, retrieval of the ROC image can take several days . After microfilming, the actual receipt is scanned by an optical character reader for the account numbers of the customer and the merchant . A data entry clerk checks the receipt and enters the total . A transaction number is generated by the system . These items, together with the date of sale and the date posted, are also stored on disk, and the ROC is discarded.

Credit for returned merchandise is handled in much the same way as purchases, except that authorisation is not required . The clerk fills out a credit voucher, taking an impression of the credit card and one showing the merchant data, and writes in the amount of the credit . The customer signs the voucher and keeps the third copy . The original is sent to Passport, and the first carbon is kept by the merchant .

Each month, bills are generated for each customer . Billing involves retrieving the balance from the previous month’s bill, data about any payments made or credits received since that bill was created, any corrections of earlier errors, and transaction records for any sales posted during the current month . During the first month the card is used in any year, there is a $50 annual fee added to the bill . The bill generating programme consolidates this data and adds any interest charges to produce the new bill . The bill shows the customer data, the transaction number, posting date, merchant name, location, and amount of each transaction, and the balance forward, interest charges, current amount due, minimum payment, and due date . In the past, the paper receipts were sorted by customer account number and sent as a packet along with the bill to the customer . This method, called “ country club billing “, was dropped by Passport because it was too expensive, and the paper receipts are now simply discarded after the microfilming and data entry.

Merchants’ accounts are also processed monthly . Merchants agree to pay Passport a percentage of the amount of each sale as a transaction fee . Merchant accounts are processed by adding the totals of all transactions for each merchant during the previous month, deducting transaction fees, and printing a check for the merchant .

All accounts are reviewed annually to determine whether the customer’s credit limit should be adjusted . In addition, customers may initiate such a review if they want their credit limit raised .

The agency provides a hotline for reporting lost or stolen credit cards . Customers are responsible for the first $50 charged on a stolen card, but not for any losses after they have reported the card lost or stolen . When a report is received, Passport immediately cancels the card, issues a new card with a different account number to the customer, and flags the card as stolen so that no further authorisation will be granted on the old card.

Information Needs

The Passport agency wishes to devise a new system that will take advantage of technological advances . It would like to reinstate “country club billing.” A major competitor that retained this more expensive type of billing has recently switched to image processing of receipts and is able to send customers laser-printed sheets with reduced images of the receipts . Customers appreciate the service because it allows them to keep records of expenses for tax and reimbursement purposes and is more convenient than the traditional packet of ROC receipts .

In the competitor’s new system, the ROCs are scanned by an image processing camera that produces a digital image of the contents and they are then discarded . To create a transaction record, an optical character reader takes the date for sale, account number, and merchant number from the digital image and a data entry clerk reads the handwritten amount from the image, since the optical character reader is not able to recognise handwritten characters at its present stage of development . The transaction record is then stored on disk, and the digital image is stored on an optical disk, from which it can be retrieved within seconds . The billing processing consists of consolidating data from the previous month’s bill, credit and payment records, and disk records of new transactions and adding interest charges, if any . In addition, ROC images for the transactions are retrieved and printed by laser in reduced form . A sheet of images is produced along with each bill, and both are mailed to the cardholder .

The method is slightly more expensive than Passport’s present billing system, but it provides the customer with more information . When customers receive the laser-printed images of receipts, there are fewer questions about items on bills, resulting in lower costs for customer inquiries and greater customer satisfaction . At present, Passport receive customer billing questions at the central processing facility, where clerks can retrieve the billing and transaction records saved on disk . If the customer still challenges a transaction, the microfilmed ROC must be retrieved and a copy mailed, a process that is expensive and time consuming . This step would be eliminated with the new system

Another change the agency would like to make with a new system is to provide for electronic funds transfer both for receiving payments from customers and making payments to merchants’ banks instead of mailing checks .

The following documents and transactions are used :

1 . Receipt of Charge : This document is the original of the sales slip completed by the sales clerk and signed by the customer. It contains the following items : Customer Account Number, Expiration Date, Cardholder Name, Merchant Number, Merchant Name, Merchant Address, Amount of Sale, Date of Sale, Authorisation Number, Customer Signature .

2.Customer Monthly Bill : This monthly bill sent to the customer has the following preprinted information : Passport name and address, finance charge rates, hotline number for lost or stolen cards, and customer service number for questions about the bill . The system prints the following items : Customer Name, Customer Address, Passport Card Number, Bill Date, Due Date, Previous Balance, Payments, Credits, Total New Charges, Finance Charge, New Balance, Minimum Payment Due. For each transaction, the following items appear : Transaction Date, Posting Date, Transaction Number, Transaction Details, Amount .

The Transaction Number is actually a reference number used to retrieve the disk record of the transaction and the microfilmed ROC, if needed . It could be used to identify the digital image of the ROC in the future . The Posting Date is the date on which the transaction date reached Passport’s office . The Transaction Details for a sale lists the merchant’s name and location . For a credit, they include the same information .

3.Merchant Report : This monthly report is sent to each merchant along with a check . It contains : Merchant Name, Merchant Address, Merchant Number, Date of Report, Total Sales, Total Credits, Total Transaction Fees, Amount of Check. For each sale it contains : Customer Account Number, Date of Sale, Authorisation Number, Amount of Sale, Posting Date . For each customer credit it contains : Customer Account Number, Date of Credit, Amount of Credit, Posting Date .

4.Customer Payment Transaction : The input screen contains : Passport Card Number, Date, Amount Paid . The output screen shows : Customer Name, Customer Address, Old balance, New balance .


Project Six: The Rental Car Agency, Auto-Rent

General description

Auto-Rent is a car rental agency that operates within one state. It has 50 locations where cars may be picked up or dropped off. There is a central office where the main computer is located, and each location has terminals linked to the central site. The project is to design the database for the central computing facility. The agency has a fleet of 750 cars, all registered in the state. It handles almost 40,000 reservations per year. There are 500 corporate accounts, which account for about half of the reservations. The remaining reservations are made by individual customers.

Basic Operations

Customers or their travel agents usually make reservations about a week in advance by dialling a toll-free number. If there are sufficient available cars, some last-minute rentals are arranged. Reservations can also be made in person at any location . All reservations are sent to the central computer. Each location can generate lists of reservations for that site as needed. A car can be picked up at one location and returned to another. It is the responsibility of the central office to insure that cars are dispatched to the sites where they will be needed to fill reservations. The sites are responsible for notifying the central location as soon as cars are returned.

All rentals require a credit card number or corporate account number. Customers can choose from a variety of car models, mileage plans, and insurance coverage options. There are daily, weekly, or monthly rates. Every car is rented out with a full tank of gasoline. When a car is returned, the mileage and the amount of gasoline are checked. If the tank is not full, the customer is charged for the missing gasoline. If the customer has chosen a plan other than unlimited mileage, the mileage is used to calculate mileage charges. The car is also checked for damage, and the customer may be liable for repairs unless the damage occurred and was reported before the rental began.

Auto-Rent contracts with garages to perform routine maintenance and do repairs on its cars. Maintenance jobs such as oil changes and tune-ups are shredded at regular intervals for all cars in the fleet. Repairs are performed as needed, using authorised garages. If a car needs repairs during a rental, the customer normally calls Auto-Rent to report the problem, and representatives bring a replacement car to the customer, arrange to bring the defective car to an authorised garage, and obtain an estimate of repairs. If a problem is spotted while the car is not rented, the car is brought to an authorised garage and an estimate is obtained. A garage must have written authorisation from Auto-Rent before it performs a repair job. Cars are normally kept active for about two years. After that time, they are sold as used cars and new cars are purchased to replace them.

Information Needs

All data relating to cars, rentals, maintenance, and repair are stored at the central site. Local sites are responsible for sending updates of the information they generate. They make up tentative bills for clients who return cars to their sites, based on the customer’s report of mileage and gasoline level. The final bill is generated from the central site, once mileage, gasoline level, and damage checks have been made locally. Local sites can download information they need. For example, each site requests data about reservations made for that location, and each can request records of cars rented elsewhere and returned to their site, so that they can generate tentative bills. The following reports are needed:

1. Reservations for Location A for Date mont/day/year: Date, Customer Name, Customer Address, Customer Phone, Credit Card Type, Credit Card Number, Corporation Name, Corporation Account Number, Make, Model, Mileage Plan, Insurance Plan, Rate Period, Rate, Discount, Estimated Length of Rental.

If the customer is renting under a corporate account, the Corporation Name, Account Number, and Discount are given. Mileage Plan and Insurance Plan are codes indicating the choices the customer has made. Rate Period is day, week, or month, and Rate is the actual amount per day, week, or month, before any discount is taken. The Estimated Length is included to help Auto-Rent in its planning. However, any rental can be extended beyond the estimated length if the customer notifies the agency and provides a new estimate.

2. Individual Customer Bill: Customer Name, Customer Address, Customer Phone, Corporation Name, Corporation Address, Corporate Account Number, Contract #, Bill Date, Credit Card Type, Credit Card Number, License #, Make, Model, Mileage Plan, Insurance Plan, Rate Period, Rate, Date Rented, Time Rented, Date Returned, Time Returned, Starting Miles, Ending Miles, Gasoline Level, Basic Charge, Mileage Charge, Insurance Charge, Gasoline Charge, Tax, Final Charge.

The individual customer bill is prepared for customers. If they are corporate clients, the Corporation Name, Corporation Address, and Corporate Account Number appear. If they are individual customers, the Credit Card Type and Credit Card Number appear. Both the tentative bill and the final bill have the same format.

3. Corporate Customer Bill: Corporation Name, Corporation Address, Account Number, Bill Date, Contract Number, Customer Name, Customer Address, Date Rented, Time Rented, Date Returned, Time Returned, Rate Period, Discount, Licence #, Make, Model, Mileage Plan, Insurance Plan, Starting Miles, Ending Miles, Gasoline Level, Basic Charge, Mileage Charge, Insurance Charge, Discount, Tax, Final Charge.

The first four items appear only once on the bill, while the rest of the data is repeated for each rental made by anyone from the corporation during the billing period. In addition, each of the corporate customers receives an individual statement that contains all the data on the individual bill described above.

4. Cars Report: Today’s Date, Make, Model, Colour, Licence Number, Vehicle Identification Number, Mileage, Condition, Location.

This report lists every car not currently rented and identifies the location of the car. It can be used to plan reservations and to provide cars for last-minute requests. The condition is described by a code; for example, 1= new, 2 = excellent, 3 = good, and so on. The cars are all licensed in a single state, so the Licence Number is sufficient to identify each car uniquely. The Vehicle Identification Number is a unique number stamped on the engine of a car at the time it is manufactured.

5. Maintenance Report: Licence Number, Vehicle Identification Number, Make, Model, Colour, Date of Procedure, Procedure Name, Cost, Garage Name, Garage Address, Mileage, Condition.

This report lists a single car and gives details of maintenance procedures performed on it. The first four items appear only once and the remaining items are repeated for each date on which maintenance was performed on the car.

6. Repairs Report: Licence Number, Vehicle Identification Number, Make, Model, Colour, Date of Repair, Repair Name, Garage Name, Garage Address, Authorisation Number, Estimate, Final Cost, Mileage, Condition.

This report details all the repairs done on a single car. The condition is the state of the car after the repair is performed. The estimate and final cost are stored to allow the agency to spot garages that routinely underestimate costs.

7. Activity by Location: Location, Manager Name, Starting Date, Ending Date, Number of Daily Rentals, Number of Weekly Rentals, Number of Month Rentals, Total Income.

This report provides information about the activity of any location during any period desired.

8.Activity by Corporation: Corporation Name, Corporation Address, Representative Name, Representative Phone, Account Number, Corporate Discount, Starting Date, Ending Date, Number of Daily Rentals, Number of Monthly Rentals, Total Income.

[Back][Top]