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, e.g. tables
• 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 top down, databases are composed of related tables, which in turn are composed of fields and records.

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, including such things as telephone numbers and zip codes
  • Numeric: numbers which can be manipulated using math operators
  • Date: calendar dates which can be manipulated mathematically

  • Logical: True or False, Yes or No
• Field size
  • Amount of space reserved for storing data

Record

A record is the collection of values for all the fields pertaining to one entity: i.e. a person, product, company, transaction, etc.

Table

A table is a collection of related records. For example, employee table, product table, customer, and orders tables.

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

Database

A database is a collection of related tables. It can also include other objects, such as queries, forms, and reports. The structure of a database is the relationships between its tables.

Relationships

There are three types of relationships which can exist between tables:

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

The most common relationships in relational databases are One-to-Many and 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 a Products table: an order can contain many products, and a product can be on many orders.

A Many-to-Many relationship consists of three tables: two "one" tables, both in a One-to-Many relationship with a third table. The third table is sometimes referred to as the lien.

Key Fields

In order for two tables to be related, they must share a common field. The common field (key field) in the "one" table of a One-to- Many relationship needs to be a primary key. The same field in the "many" table of a One-to-Many relationship is called the foreign key.

Primary key

A Primary key is a field or a combination of two or more fields. The value in the primary key field for each record uniquely identifies that record.

In the example above, customer number is the Primary key for the Customer table. A customer number identifies one and only one customer in the Customer table. The primary key for the Orders table would be a field for the order number.

Foreign key

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

In the example above, the primary key (customer number) from the Customer table ("one" table) is a foreign key in the Orders table ("many" table).

For the "many" records of the Order table, the foreign key identifies with which unique record in the Customer table they are associated.

Rationalization and Redundancy

Grouping logically-related fields into distinct tables, determining key fields, and then relating distinct tables using 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 complication of 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. To do so would take up storage space needlessly and make the job of updating multiple customer addresses difficult and time-consuming.

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, 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.