Relational databases enable you to organize information in natural ways based on “relationships” between the various data. For example a corporate database would include information about:
 |
Employees (contains names, addresses, phone numbers, etc. of employees) |
|
Projects |
|
Costs |
|
Facilities |
These groups of information have relationships based on the corporate structure: employees are assigned to projects, which have costs. The relationships are used to define the database and to make sense of the data by end users of the database.
Technically
speaking, relational databases consist of:
|
One or more tables
describing an aspect of the database (e.g., employees) containing... |
|
Records holding data
organized by one or more (usually more) fields (e.g., employee name, address,
phone). |
|
Fields designed to hold
various types of data (text, numbers, currency, etc.) |
Tables
Each table in a database is used for a specific grouping of information. The data in a table typically describes characteristics or specific things about as a person, thing, or event.
For example, a corporate database might contain:
|
Employee table—name, address, phone, job of each employee |
|
Project table—project name, description, name of project leader for each project |
|
Jobs table—job, description, salary range for each type of job |
|
Assignment table—project name, employee name (which references you back to the employee table) |
Note that each table has unique information that is not found in other tables. Good table design means: never repeating data (e.g., an employee address only appears once).
Fields and Data Types
Fields in a table describe the kind and nature of the information that is being stored. For example, using our employee table:
|
EmpoyeeID |
LastName |
FirstName |
Address |
JobCode |
number |
text- 20 char |
text - 20 char |
text - 100 char |
number |
|
911 |
Smith |
John |
111
Elm Str... |
22 |
|
1004 |
Hillman |
David |
25912
Lasalle... |
15 |
|
1298 |
Doe |
Betty |
101
Main St.. |
22 |
|
|
|
|
|
|
Standard data types found in databases include:
|
Number – integer and whole numbers |
|
Strings – fixed and variable length (memo) |
|
Boolean (True/False) |
|
Date and time |
|
Currency |
|
Objects |
|
Images |
|
Digital audio and video |
|
BLOBs (Binary Large Objects) – various forms of binary data |
Note: not all databases, contain all data types.
Why are data types important?
Data types are important for two reasons:
-
They determine the "best way" to preserve data so that it can be used in applications. Storing "12/15/2003" as a date means that you can compare a range of dates.
-
They determine the amount of memory (disk and RAM) that will be used to store the data.
On the second point, many database applications allow you to determine how much space is used, for example:
|
Text as n characters. |
|
Numbers as long, single, integer, double, float etc. |
Part of the design process should focus on the best fit for data sizing (e.g., "lastname" requires 30 characters, "qty" as integers). This will result in more efficient data retrieval and save storage space.
Why Relational Databases?
Relational databases offer a number of advantages:
|
Elimination of Redundancy – a relational database does not repeat domain specific data in various tables. For example, imagine having to repeat a name (spelling, etc.) across a number of files; whereas a relational database would store this information once and use an identification (e.g., studentid) to link information to courses and grades tables. |
|
This "identification link" or key establishes relationships of data across various tables in a database so that data does not have to be repeated. |
For example:
One table lists people where personid is the key for each each record...
| personid | lastname | firstname | address |
| 1001 | Smith | John | 111 Main St |
| 1002 | Jones | Mary | 222 Oak Dr |
Another table uses the "personid" field (a foreign key) to link the names to other information:
| transactionid | personid | trans_date | amt_spent |
| 2001 | 1001 | 2/15/2003 | $25.12 |
| 2002 | 1002 | 2/16/2004 | $14.99 |
The use of keys establishes an efficient way to correlate data from one table to the next:
|
Elimination of Multiple Value Data – a relational database allows you to create relationships for subordinate data. For example, a book could have a single author or multiple authors (the authors are maintained in a separate table). |
|
Avoiding Update Anomalies – since data is stored in only one place, it is easy to update (no other copies to remember to update). |
|
Avoiding Insertion Anomalies – like updates, since data is only stored in one place, it needs to be inserted in one place. |
|
Avoiding Deletion Anomalies – once again, since data is in one place only, it is deleted only once. |
There are also some potential problems to be aware of in maintaining relational databases:
|
Avoid Data Loss – be careful in removing tables because of relationships that are necessary for the data context. For example, if the authors’ table were removed, you wouldn’t know you wrote the books. |
|
Maintain Relational Integrity – if data is removed from one table, make sure that references to that table are not affected. For example, if a publisher is removed from the publishers’ table, make sure that books that refer to that publisher are not affected. |
|
Problems with Views – as the data is more widely distributed across a number of tables, it is harder to reconstruct a complete picture of the data set. |