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:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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
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.
Source
