Database Design Process

There are several main steps in the database design process:

Conceptual Design - The Data Model

Before diving into developing the SQL CREATE TABLE statements required to build a database, it proves necessary to first develop a plan for the kind of information the database will need to store. To support this planning, we need a way to model the components of the database (tables, columns, rows, and constraints) and capture the design decisions that affect how such components are configured in SQL. This model should allow rapid prototyping of our table design and relationship but also be standard and specific enough to easily transition from the model to implementation via SQL CREATE TABLE statements. Database designers use the Entity-Relationship (ER) Model for exactly this purpose. An ER Model consists of

Entities and Attributes

We use the word entity to describe both the thing we wish to track, an entity class, and and actual occurrence of that entity in our model, an entity instance. As an example, an entity class would be midshipmen and another entity class would be courses or grades. The entities will eventually be stored as tables. The values we desired to track about an entity, which we will store as a table columns, are known as entity attributes. Once we provide these values (for example alpha, first name, last name, etc.), we are creating a specific entity instance (a midshipman)! In the example below, we wish to track information about customers, the entity class, and there are a few attributes concerning the customers that we wish to store.


In summary, an entity class is a collection of entities instances of a certain type and an entity instance is the actual occurrence (stored attributes) of a particular identity. All instances of a entity class will have the same attributes, but with different values.

Throughout this course, texts, and notes, entities will be presented in different ways.


Identifiers

Identifiers are those attributes that can identify specific instances of an entity. The identifiers in the data model become the keys in the database design, and just as before we have composite identifiers consisting of two or more attributes that when combined can uniquely identify an entity.

Relationships

Relationships describe the associations between entities, they have no attributes, but can have a degree.

Binary Relationship
Ternary Relationship

Our focus will be primarily on binary relationships.

Cardinality

Cardinality refers to a count, and in terms of ER Models, we use cardinality to represent the minimum and maximum number of entity instances that can participate in a relationship. There are two types of cardinalities that characterize a relationship between two entities:

Notation

We will commonly use crow's foot notation for representing the cardinality in an Entity Relationship Diagram. This notation allows us to quickly see from a drawing what the allowable links are within the model.

ID-Dependent (Weak) Entities and Identifying Relationships

An ID-Dependent Entity can apper in a one-to-many relationship where the child entity (the entity on the "many" side) has an identifier that includes the identifier of the parent entity. As an example, an apartment entity would be the child to a building entity. The minimum cardinality from the ID-dependent entity to the parent is always one, because the child must have the parent to exist! In the following example the solid line between the entities indicates an identifying relationship.

Weak entities are those whose existence depends on another entity, therefore an ID-dependent entity is a weak entity. Weak (child) entities form identifying relationships with strong (parent) entities.

In summary, here are some important traits about identifying relationships:

ID-Independent (Strong) Entities and Non-identifying Relationships

An ID-Independent Entity, or strong entity, can exist on its own without depending on a parent existing first. As an example, an employee can exist without a skill, say the employee is still in training and has not qualified for any desired skills yet. Vice-versa, a skill can exist without currently having any employees who have that skill...maybe it's time to hire a new employee that does! The dashed lines in the ER diagrams for EMPLOYEE and SKILL in the Notation section above are what declares this as a non-identifying relationship in our ER Model.

In summary, here are some important traits about non-identifying relationships:

Subtype Entities

Subtype entities are special cases of a supertype entity, where all common attributes are held in the supertype while specific subtype attributes are held in the subtype entity. Consider the EMPLOYEE entity we have seen thus far, it is possible to have different types of employees, such as managers or database administrators. Both examples are employees but we may want to store specific information based on their subtype, such as what database a DB admin is responsible for, or what level in the organization a manager is currently employed at.

Subtypes can be either exclusive or inclusive. If the subtypes are exclusive then one supertype relates to at most one subtype.

HAS-A versus IS-A relationships

The examples provided above when discussing cardinality were HAS-A relationships, where object A has an object B. Subtype/supertype relationships are IS-A relationship, where the subtype is a more specific entity then their supertype, for example a database administrator is an employee.

Process for Drawing an ER Diagram

Practice Problems

  1. Spring Break

    Draw the ER diagram to meet the following requirements.

    • You will plan a Spring Break trip with a selection of your USNA friends. You need at a minimum the following entity classes: FRIEND, CAR, HOUSE
    • You store the necessary information in the database to pick a Friend's or rental car, one of your friends' house to visit, and which friends you want to include.
    • Your ER diagram should capture any entity relationships, their type (identifying, non-identifying, sub-type) and all appropriate entity attributes.
    • Your House class must include a House_Code (Why would you want to go there? i.e. Beach House, Lake House, Ski Cabin) attribute and should describe at least three sub-types of houses.

  2. Vegetable Garden

    Assuming we want to store data about a vegetable garden, create an ER diagram for a Vegetable entity, a Plot entity, and an Observation entity. I plan on having a cabinet full of different Vegetable seeds, and I'll be planting seeds in the plots. I might plant the same vegetable in different plots, and I might not plant every type of vegetable I have inventory for. Some plots might also be vacant. There are different kinds of plots: I'll have cold-frames, planters, and square foot gardens. Throughout the season I'll be making observations about the status of my plants growing.

  3. Choose your own scenario

    Come up with your own scenario/requirements for a database. Describe the scenario in plain English, then draw the ER diagram. Make sure you have strong and weak entities, different kinds of cardinality, and both is-a and has-a replationships.