Database Design

Feasability Study

Costs Benefits


Designing Systems

Identifying User Requirements

Design Methodologies:


Focus on the data, not the forms and reports.
The data remains generally stable over time, while forms and reports change.

Definition of Terms
 
 
 
TERM DEFINITION PET STORE EXAMPLES
Entity Something in the real world that we wish to describe or track. Customer, Merchandise, Sale
Class Description of an entity that includes its attributes (properties) and behavior (methods). Customer, Merchandise, Sale
Object One instance of a class with specific data. Joan Smith, Eukanuba Large Breed Puppy Chow, Sale#32
Property A characteristic or desciptor of a class or entity. LastName, Description, SaleDate
Method A function performed by the class. AddCustomer, UpdateInventory, ComputeTotal
Association A relationship between two or more classes. Each sale can only have one customer. 
One customer can have many sales.

Associations

Drawing Class Diagrams
Let's do some examples. How do the assumptions you make affect the class diagram?
    a. Employee, Spouse
    b. Employee, Child
    c. Employee, Automobile
    d. Employee, Manager
    e. Employee, Specialty
    f. Radio Station, Disk Jockey
    g. Radio Station, Call Letters
    h. Radio Station, Song
    i.  Radio Station, Advertiser
    j. Radio Station, Vehicle
    k. US citizen, Taxpayer Identification Number (SSN)
    l. US citizen, State of Residence
    m. Class, Student
    n. Class, Instructor
    o. Class, Classroom
    p. Automobile, Owner
    q. Automobile, Insurance Company
    r. Automobile, Vehicle Identification Number

Now let's do another exercise. Identify whether each item on the right should be an attribute of the entity to the left or a separate entity. What assumptions do you make?
    a. Employee        Manager, Date of Birth, Spouse, Specialty, Office, Phone Number
    b. Automobile     Color, Owner, Engine Size, Insurance Policy, Repair Shop, Damage
    c. Movie             Actor, Director, Length, Date Made, Cost, Theater
    d. Ship                Captain, Crew, Passenger, Name, Country of Registry
    e. Order              Customer, Date, Items, Order Number, Employee

Many-to-many associations are OK in an initial diagram, but they cause problems in the database design. Normalization converts many-to-many relationships into several one-to-many relationships.

N-ary associations are relationships among more than two classes. They are drawn as a diamond. They often include a Date/Time column to record when an event occured. Compare the following two class diagrams:

In the diagram above, it is not clear which mechanics did which services on which flight.

In the diagram above, there is one Job object for each instance of a service performed by each mechanic for each flight. The * means that if the values for the other classes are fixed, there are that many possible values for this class.
 
 

An aggregation association occurs when a class is a collection of another class. You draw a diamond on the association line next to the class that is the aggregate.

A composition is an aggregate association where the individual items become the new object. The components then no longer exist separately.

Unlike aggregates, compositions can exist only for one-many relationships. But there can be more than one class that gets included in the composition - for example, the components of a car.
 

A generalization is an association that describes a class hierarchy. For example, an Animal is a generalization of the Cat, Dog, and Fish classes.

The Animal class would include generic properties and methods like DataBorn, Name, Gender, Color, and ListPrice.

The Cat class might contain properties like Altered, Claws, and Breed.

The Dog class might contain properties like Altered, Breed, and Hunting.

The Fish class might contain FreshWater and ScaleCondition properties.

Of course, there could also be a Mammal class in the hierarchy as well.

You can draw the class hierarchy like this, using an arrow to indicate the hierarchy...

What other class hierarchies exist?

It is also possible that a class inherits methods and properties from more than one parent class.

Encapsulation refers to the definition of properties and methods within a class. The advantage of encapsulation is that relevant definitions are in just one place.

Polymorphism means that a derived class defines the same function as a parent class.
 
 

A reflexive association is a relationship from one class back to itself. For example, if an Employee has a manager, the manager is also an Employee. The following shows how you would draw this in a class diagram...









Often, it's confusing to look at an entire class diagram. Sometimes, it makes sense to group elements (groups of related classes) of the class diagram and form packages.

Here's the class diagram for the Sally's Pet Store database (as expressed in Access).


 
 


A reasonable package for this might include Supplier, AnimalPurchase, Animal, Employee, Merchandise Purchase, Merchandise, Sale and Customer classes. Let's sketch this class diagram. Which is easier to understand? Which is more useful in implementation?
 
 

Data Types

The usual available data types include:
 


Events

Database systems respond to events. Events can include:


Case Studies

Let's consider the following cases, and construct class diagrams...

1. A small health clinic hires you to design a system for them. The clinic consists of several examining rooms a a few rooms for short-term critical care patients. A core staff of seven physicians is supplemented by internists from a local teaching hospital. The clinic wants to computerize the patient records. Currently, all patient data is stored in a folder kept in a large central file cabinet. Arriving patients sign in at the front desk. A clerk checks the billing records, prints out a summary status sheet, and selects an examination room. After waiting for the physician, the clerk moves the data packet and the patient to the examination room. A nurse records basic medical data (weight, blood pressure, etc.). The physician makes additional notes to both the medical and billing data and generally writes a prescription order which is given to the patient and recorded on the charts. When th patient leaves, the clerk enters the new billing data into the system, collects any payments, and prints a list of charges and a receipt. The new billing data is forwarded to the appropriate insurance company. The medical data is returned to the filing cabinet. When the patient gets a prescription filled, the pharmicist calls the clinic for verification. A clerk retrieves the medical data, identifies the prescription, and verifies or corrects the order.

2. A friend of yours has just opened a photofinishing business. She wants you to create a database system to help her run the business. The basic processing is straight-forward: A customer drops or mails in one or more rolls of film. A clerk records the basic data on the customer and the film. The rolls are assigned a number, sorted, and run through the processor. Processing varies slightly, depending upon the type of film, film speed and processing options. Your friend wants to keep track of which clerk performed the processing and match the names with any complaints that might arise. She also wants to offer a frequent buyer program to reward the most active customers. It is also important to track the chemical usage for the processing - both to keep track of orders and expenses and to make sure the processors always have fresh chemicals. The clerks are also responsible for cleaning the processing equipment.