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:
A short list of database applications would include:
Looking from the bottom up, a database is composed of fields, records, and related tables.
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:
A record is the collection of the data contained by all fields which pertains to one entity: eg. a person, product, company, transaction, etc.
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.
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.
There are three possible relationships between two or more tables:
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.
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.
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.
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:
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:
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.
Bruce Miller, 2005, 2014