Database Management Systems (DBMS)

Defines DBMS and gives examples of common applications. Defines and describes DBMS terminology, including fields, records, and tables. Discusses the principles of relational databases and gives illustrated examples of one-to-many and many-to-many relations and the role played by primary and foreign keys.

Introduction to Database Concepts

Database Management Systems

Database management systems (DBMS) are collections of tools used to manage databases. Four basic functions performed by all DBMS are:

  • Create, modify, and delete data structures (tables, queries, etc)
  • Add, modify, and delete data
  • Retrieve data selectively
  • Generate reports based on data

A short list of database applications would include:
  • Inventory
  • Payroll
  • Membership
  • Orders
  • Shipping
  • Reservation
  • Invoicing
  • Accounting
  • Security
  • Catalogues
  • Mailing
  • Medical records
  • Database Components

    Looking from the bottom up, a database is composed of fields, records, and related tables.

    Field

    A field is an area (within a record) reserved for a specific piece of data. Examples: customer number, customer name, street address, city, state, phone, current balance.

    Fields are defined by:

    • Field name

    • Data type
      • Character: text (inc. telephone numbers and zip codes)
      • Numeric: numbers capable of being used in math operations
      • Date: calendar dates (can also be used in math operations)

      • Logical: True or False (Yes or No)

    • Field size
      • Amount of storage space reserved for storing data

    Record

    A record is the collection of the data contained by all fields which pertains to one entity: eg. a person, product, company, transaction, etc.

    Table

    A table is a collection of related records. Examples: employee table, product table, customer table, orders table.

    In a table, records are represented in rows and fields are represented in columns.

    Table structure is determined by the characteristics of its fields.

    Database

    A database is a collection of related tables, such as a company's sales force, customers, and sales orders.

    Database structure is determined by the relationships between its tables.

    A database can also include other objects, such as queries, forms, and reports.

    Relationships

    There are three possible relationships between two or more tables:

    • One-to-One
    • One-to-Many
    • Many-to-Many

    An example of a One-to-Many relationship would be a Customer table and an Orders table: each order has only one customer, but a customer can make many orders.

    One-to-Many relationships consist of two tables, the "one" table, and the "many" table.

    An example of a Many-to-Many relationship would be an Orders table and an Inventory table: an order can contain many inventory items, and each inventory item can appear on many orders.

    A Many-to-Many relationship consists of three tables: two "one" tables, both in a One-to-Many relationship with the third table. The third table is sometimes referred to as a lien because it joins the two "one" tables by combining the foreign keys from both. The lien table between an Inventory and an Orders table would represent the line items on an invoice.

    Key Fields

    For two tables to be related, they must share a common field, called a key field. The key field in the "one" table of a One-to- Many relationship is referred to as a primary key. The same key field is called the foreign key when it appears in the "many" table.

    Primary key

    A Primary key is a field or a combination of two or more fields whose value uniquely identifies one record in the "one" table.

    Customer ID numbers would be the primary key for a Customer table because a customer id number identifies one and only one customer.

    By the same logic, the primary key for an Orders table would be the Order ID numbers. One order id number = one specific order.

    Foreign key

    When a "one" table's primary key field is added to a "many" table in order to establish a common field to relate the two tables, it is called a foreign key in the "many" table.

    The primary key in a Customers table, Customer ID number, becomes a foreign key in Orders, the "many" table.

    The foreign keys in Orders relate each record uniquely to one record in the Customer table.

    Rationalization and Redundancy

    Grouping logically-related fields into distinct tables, determining key fields, and then relating tables through their common key fields is called rationalizing a database.

    There are two major reasons for designing a database this way:

    • To avoid wasting storage space for redundant data
    • To eliminate the complications involved in updating duplicate data copies

    For example, in the Customers/Orders database, we want to be able to identify the customer name, address, and phone number for each order, but we want to avoid repeating that information for each order. Repeating customer information over and over would take up storage space needlessly and make the job of updating customer addresses difficult, time-consuming, and likely introduce errors in the data.

    To avoid redundancy:

    1. Place all the fields related to customers (name, address, etc.) into a Customer table and create a Primary key field which uniquely identifies each customer: Customer ID.
    2. Put all the fields related to orders (date, salesperson (key), total, etc.) into the Orders table.
    3. Include the Primary key field (Customer ID) from the Customer table in the table for Orders.

    The One-to-Many relationship between Customer and Orders is defined by the common field Customer ID. In the table for Customers (the "one" table) Customer ID is a primary key, while in the Orders table (the "many" table) it is a foreign key.

    Also see:
    Mail Order Database Design Case
    and
    Rationalizing Database Structures


    Bruce Miller, 2005, 2014