By Gail Seymour
As we discussed in our last article “An Introduction to Database Management Systems,” (DMS), one of the key components of any DMS is the modeling language used to define the database schema, or structure. Common database structures are hierarchical, network, relational and object. Models differ in how they connect related information. The most widely used, particularly in Web applications, is the relational database model, which we’re going to look at in more detail here.
What is a Relational Database?
At its simplest, a database is an electronic store of data, for example a company’s sales records. In a file based system, each sale would have its own file, where the recording clerk would have to enter every piece of required information. That might include sales order number, items ordered and quantities, customer name with billing and shipping address, contact name and phone number, plus various other details. Obviously, entering all of these details for every order in full would be both time consuming, and open to human error, such as spelling mistakes. It would also mean if a detail, like a customer phone number changed, it would have to be changed in multiple places.
Relational databases are designed to make data entry and management simpler, quicker and less prone to data corruption by storing information so that each piece of data is stored only once, and referenced by other pieces of data when needed. This is achieved by creating relations between data sets, hence the term relational database.
To see how some of the key principles of relational databases work, let’s take our example order records through the process of database development.
Tables, Rows and Columns
The basic unit of a relational database is a table, also referred to as a relation or base relvar. Within a table, each column represents an attribute, and each row an entry. So for example, our order records might consists of columns labeled “Order Number, Customer Name, Customer Contact, Contact Phone Number, Order Date, Delivery Date, Shipping Address, Billing Address, Item Ordered, Qty, Price,” and so on. Each row in this table would then represent an order. Details might be entered in a table, much like a spread sheet, and the resulting flat file database would contain the output for all the files, and all of their details. Although this might prevent a lot of user errors by constraining the data input, each piece of information might appear multiple times. For example, if an order contains multiple items, the whole line might be duplicated except for the item ordered columns. Clearly, this is not an efficient use of storage space, and updating data, though quicker than searching multiple files, would still be time consuming.
Normalizing
The process of normalizing a relational database consists of separating data into smaller, more closely related information. In this example, initially, we might separate the customer information from the order information so that all the customer details are stored in one table, and a single reference, Customer Number, included in the order table. Through a series of logical iterations, each designed to prevent extensive data duplication, we might end up with several tables. For example, we might have a list of customers, a list of contacts, a list of addresses, a list of orders and a list of products.
Keys and Primary Keys
Having separated all these types of information, we need a way to identify records within each table in order to reference them in other tables. Since the main reason for maintaining smaller tables is to ensure integrity of the data and avoid duplication, we also need to specify which column within a table must be unique. We do this by assigning a column within a table as a “key.” With some tables, such as orders, there are “natural keys,” like order numbers, which have to be unique for the system to work anyway. For other tables, such as contacts, these natural keys do not exist. We may have multiple contacts with the same name, so we can’t use them as a key. Instead, we assign a computer generated “primary key,” that creates an artificial, unique column in the table and assigns each row an incrementally increasing number.
Relationships
Now that we have a way to identify the entries in a table, we can pull the information from the table by simply referencing that column in another table. This is done by “joining” tables, and creating resulting relationships, which may be one to one, meaning each unique entry in one table must also be unique in the other, one to many, where an entry must be unique in one table but may be duplicated in the other, or many to many, where entries need not be unique in either table. So for example, we might join the customer table primary key to the order table customer number column by instructing the database to
INNER JOIN Customer_Id
ON Customers.Customer_Id=Orders.Customer_Id
Queries and Views
Once the data is stored in the database, the data can be retrieved for viewing by using queries and presented in views. A typical query would instruct the database to return rows and columns from chosen tables that meet certain criteria, using a “SELECT FROM WHERE” clause. The resulting information would then be presented to the user in tabular, paginated or graphical format for analysis or editing. So in order to view all orders from a particular customer we might query the database like this:
SELECT Order_Id
FROM Orders
WHERE Customer_Id ="123"
Thus data can be dynamically pulled from the database and presented differently to multiple users according to their needs. Since most users are unfamiliar with SQL query language, and it’s often desirable to restrict access to the database, most queries will be written into Web applications by Web developers or database administrators, and all the end user has to do is select the view they want to work with and enter the data.
Read the full series:
Read the entire series:
Part 1: Introduction to Database Management Systems
Part 2: Relational Database Management Systems
Part 3: Introduction to Net Database Administrators
About the Author
Gail Seymour has been a website designer for more than 10 years. During that time she has won three design awards and has provided the content and copy for dozens of websites and more than 50,000 Web pages.