A Relational Database Model
A relational database is based on the relational model developed by E.F. Codd. A relational database allows the definition of data structures, storage and retrieval operations and integrity constraints. In such a database the data and relations between them are organized into tables. A table is a collection of records and each record in a table contains the same fields. The contents of a table can be permanently saved for future use.
Properties of the relational database model
Properties of Relational Tables:
1. Data is presented as a collection of relations.
2. Each relation is depicted as a table.
3. Columns are attributes that belong to the entity modeled by the table (ex. In a student table, you could have name, address, student ID, major, etc.).
4. Each row ("tuple") represents a single entity (ex. In a student table, John Smith, 14 Oak St, 9002342, Accounting, would represent one student entity).
5. Every table has a set of attributes that taken together as a "key" (technically, a "superkey") uniquely identifies each entity (Ex. In the student table, “student ID” would uniquely identify each student – no two students would have the same student ID).
Certain fields may be designated as keys, which means that searches for specific values of that field will use indexing to speed them up and more importantly, uniquely identify each entity. There are many types of keys, however, quite possibly the two most important are the primary key and the foreign key. The primary key is what uniquely identifies each entity. The foreign key is a primary key of one table that also sits in another table. Ultimately, the use of foreign keys is the heart of the relational database model. This linkage that the foreign key provides is what allows tables to pull data from each other and link data together. Where fields in two different tables take values from the same set, a join operation can be performed to select related records in the two tables by matching values in those fields. Most often, but not always, the fields will have the same name in both tables. For example, an "orders" table might contain (customer-ID – primary key, product-code – foreign key) pairs and a "products" table might contain (product-code – primary key, price) pairs so to calculate a given customer's bill you would sum the prices of all products ordered by that customer by joining on the product-code fields of the two tables. This can be extended to joining multiple tables on multiple fields. Because these relationships are only specified at retrieval time, relational databases are classed as dynamic database management system. Also, another important role of the primary key is called “determination”. What this means is that if you know the value of attribute X, you can determine the value of attribute Y. The relational database model is based on the Relational Algebra. Which means that operations in the relational database model are based on Select, Project, Join, Intersect, Union, Difference, and Product. Here is a brief description of each operation:
- Select: Shows values for all rows found a table that meet a given criteria.
- Project: Shows values for all selected attributes.
- Join: Will combine information from one or more tables.
- Intersect: Shows all rows that are found in both tables.
- Union: Combines all rows from multiple tables and removes the duplicate rows.
- Difference: Shows all rows from one table that are not contained in another table.
- Product: Combines all rows from two or more tables (does contain duplicates).
As you can see, this is a very powerful set of operations that can be used to manipulate data.
In the relational database model, there are five, very important rules. When followed, these rules help to ensure data integrity.
1. The order of tuples and attributes is not important. (Ex. Attribute order not important…if you have name before address, is the same as address before name).
2. Every tuple is unique. This means that for every record in a table there is something that uniquely identifies it from any other tuple.
3. Cells contain single values. This means that each cell in a table can contain only one value.
4. All values within an attribute are from the same domain. This means that however the attribute is defined, the values for each tuple fall into that definition. For example, if the attribute is labeled as Date, you would not enter a dollar amount, shirt size, or model number in that column, only dates.
5. Table names in the database must be unique and attribute names in tables must be unique. No two tables can have the same name in a database. Attributes (columns) cannot have the same name in a table. You can have two different tables that have similar attribute names.
Databases are very commonly used in everyday life. The relational model of databases provides a very simple way of looking at data structured into tables, and there are straightforward techniques, such as ER modeling to represent a world view from which to build a relational database.