Database Management Systems|
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 top down, databases are composed of related tables, which in turn are composed of fields and records.
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 values for all the fields pertaining to one entity: i.e. a person, product, company, transaction, etc.
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.
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.
There are three types of relationships which can exist between tables:
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.
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.
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.
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:
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:
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