This week we are looking at the concept of a Relational Database Model. The relational database model is based on the data structure of a 2-dimentional table (rows and columns). In the Relational Database model we call the actual database table a "Relation" (not relational or relationship). We call each rowss in the table a "tuple" and each column in the table an "attribute". So our new terminology is Relation (table), Tuple (row) and Attribute (column).
Each one of your rows in a particular table (tuple in the relation) must have a unique identifier (column) - such as Social Security Num for EMPLOYEE, or PATIENT; for DEPARTMENT we might use the column Dept_ID or possibly Dept_Name as the unique identifier. This unique identifier is called your Primary Key. The Primary Key is an attribute (or combination of attributes) that uniquely identifies each row in a relation. In other words - we can only have ONE row for each individual employee in an organization. The column Soc_Sec_Num will contain a value that is different for each row as each individual employee has a unique social security number. It is possible that your relation (table) doesn't have a unique column that you can use - for instance, if we didn't use Soc_Sec_Num in a table CUSTOMER (entity) - then we could put two or more attributes together to get a unique identifier: for instance use Cust_Last_Name (by itself this is not unique as we could have several customers with the last name "Smith") plus Cust_Firstname plus Cust_Street (putting together the customer's lastname, firstname and street would give us a unique value for most instances). We could possibly have a problem if we have a father and son with the same first and last name and street, so we might need to use an additional column for the Primary Key - such as Cust_Last_Name + Cust_Firstname + Cust_Street + Cust_Age to make sure we have a unique Primary Key value for each record in the table. The combination of more than one attribute used together to create our Primary Key is called a Composite Key.
Another important concept for Relational Database models includes the Foreign Key. A Foreign Key is a non-primary key attribute in one relation that also serves as the primary key in another relation. In other words, you have the same attributes in two different tables - one where the attribute serves as the primary key and the other table where the attribute does NOT serve as the primary key - this is your Foreign Key. This "ties together" or relates these two tables together.
Tables are related to other tables (your entities in your E/R diaram were related to other entities) in your database through "keys".
Your entities in your E/R diagram will become your relations (tables) in your database. Your attributes that you had on your E/R diagram for your entities (such as PATIENT entity has attributes Pat_Soc_Sec, Pat_Lastname, Pat_Firstname, Pat_Street, etc.) become your columns (attributes) in your table.
Properties of a Relation (table):
1. Each relation (table) in a database has a unique name
2. An entry at the intersection of each row and column is atomic (or single valued). There can be no multi-valued attributes in a relation
3. Each row is unique; no two rows in a relation are identical (Primary key takes care of this)
4. Each attribute (column) in a particular relation (table) has a unique name
5. The sequence of columns (left to right) is insignificant. The columns of a relation can be interchanged without changing the meaning or use of the relation
6. The sequence of the rows (top to bottom) is insignificant. As with columns, the rows or a relation may be interchanged or stored in any sequence.
Other relational database model terminology:
1. Integrity constraints (these are normally business rules that must be followed in the table)
2. Domain constraints - all of the values that appear in a column within a table must be taken from the same domain. A domain is the set of values that may be assigned to an attribute. For example - Cust_Last_name would be a alphanumeric domain and it would have a certain length and may list any allowable values or allowable range (if applicable). Cust_Soc_Sec would be a numeric field (no alphabetic characters allowed) and would have a length of 9.
3. Null value - a value that may be assigned to an attribute when no other value applies or when the value is unknown
4. No Primary Key attribute (or composite attributes) may be null
5. Referential Integrity - this concerns the Primary Key-Foreign Key relationships. Each foreign key value MUST match a primary key value in another relation (table) or the foreign key value must be null
**The goal of creating our Relational Database is to have a set of well-structured relations - a set of well-structured tables. A well-structured relation is a relation that contains minimum redundancy and allows users to properly insert new rows of data, modify existing rows (modify your values in your columns), and delete complete rows in a table without errors or inconsistencies.
When an error occurs when you try to add a new row (for instance you can't add the same social security value to a table that already has that value in another row), or you try to modify existing data and you have more than one row to modify, or you try to delete a row that doesn't exist - these are called an Anomoly. An Anomoly is an error or inconsistency that may result when a user attempts to update (add, modify or delete) a table that contains redundant data. The three types of these anomalies are what was just explained - an Insertion anomaly, a Deletion anomaly, and a Modification anomaly.