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:
- 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.
- Put all the fields related to orders (date, salesperson,
total, etc.) into the Orders table.
- 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.
Source
