Database System
Relational Data Model
Evolution of Database models
Database management systems were first introduced during the 1960s and have continued to evolve during subsequent decades. Some combination of the following three objectives always drove the development and evolution of database technology.
- To provide greater independence between programs and data.
- To manage increasingly complex data types and structures.
- To provide easier and faster access to data for non-computer professionals.
First Generation DBMS's
The hierarchical and network database management systems (generally regarded as the first-generation DBMS) were developed to cope with increasingly complex data structures. Both approaches to database structure used physical pointers or disk address to relate records in deferent files.
| Hierarchical database model | Network database model |
| Very simple and organized as a family tree structure | Complex and organized as a network structure |
| One to many relationships | Many to many relationships |
| The whole database depends on root record | Database does not depend on a root record |
| The topmost record is the root record | Many records at the top level |
| Single access path for a data record | Multiple access paths for a data record |
| The data record can be duplicated | Data records are not duplicated |
| Access using navigational record at a time procedure | Access using navigational record at a time procedure |
| Need to write complex queries to get information from multiple branches. No cross-links | Can write simple queries to get information from multiple branches because cross-links are available |
Hierarchical database model was developed to overcome the limitations of file processing system and Network model has derived as a solution to problems caused by Hierarchical model. And both have following problems.
- Difficult access to data, based on navigational record at a time procedures.
- Very limited data independence.
- No widely accepted theoretical foundation for either model.
Relational Data Model (Second Generation DBMS's)
To overcome these limitations, E.F Codd and others developed the relational data model during the 1970s. This model is considered as the second-generation DBMS, received widespread commercial acceptance during the 1980s.
With the relational model, all data are represented in the form of tables. It is base on logical relationships in data, overcome the problems of physical address handling. It is based on mathematical theory and therefore has a solid mathematical foundation. A relatively simple fourth generational language called SQL is used for data retrieval.
Data Structure: Data are stored in the form of tabular structure.
Data Manipulation: Powerful operations are used to manipulate data.
Data Integrity: Facilities are included to ensure data integrity.
Fundamental Concept
Relation
A named two-dimensional table consisting of rows and columns of data. Each relation consists of a set of named columns and an arbitrary number of rows, that is a set of tuples. Each row of relation corresponds to a record that contains values for the entity instance.
Relational model terminologies
| Tuple | A row in a relation |
| Attribute | A column in a relation |
| Attribute Domain | A set of valid values for an attribute |
| The degree of a relation | The number of attributes in a relation |
| The cardinality of a relation | The number of tuples in a relation |
| Null | Value is not applicable or the value is not known |
Properties of a Relation
- Each relation in a database must have a unique name.
- An entry at the intersection of each row and column is automatic or single-valued. (no multi-valued attributes are allowed)
- Each tuple must be unique. No two rows in a relation are identical.
- Each attribute (or column) within a table has a unique name.
- The sequence or columns (left to right) is insignificant.
Concept of Keys
Key
A minimal set of attributes that uniquely identify each tuple in a relation.
Candidate key
In any given relation, there may be more than one set of attributes that could be chosen as a key. These are called candidate keys.
Primary key
When one of the candidate keys is selected as the relation key.
Alternate key
Other candidate keys which are not selected as the relation key.
Composite key
A key consisting of more than one attribute.
Foreign key
The foreign key is an attribute or a set of attributes in one relation which is a primary key of another relation. Foreign key attributes need not have the same names as the key attributes to which they corresponded.
Foreign keys are the essential links between relations. They are used to tie data in one relation to data in another relation. Logical links between two relations are made through foreign key concept. This helps to relate from one table to another without having physical links.
Integrity Constraints
The relational data model includes several types of constraints or business rules, whose purpose is to maintain the accuracy and integrity of data in the database. The major types of integrity constraints are
Domain integrity
All of the value that appears in a column of a relation must be taken from the same domain. A domain is a valid set of values that may be assigned to an attribute.
Domain integrity can be maintained by the use of
- User-defined domain types
- Not Null
- Unique
- Default value
- Check clause
Entity integrity
The entity integrity rules are designed to assure that every relation has a primary key, and it guarantees that every primary key attribute is not null. That is the data values for that primary key are all valid.
Referential integrity
A referential integrity constraint is a rule that maintains consistency among the rows of two relations. In the relational data model, associations between tables are defined through the use of foreign keys. The rule states that if there is a foreign key in one relation, either each foreign key value must match a primary key value in referred relation or the foreign key value must be null. That is values of foreign key must be defined from primary key domain or null referencing relation is having the primary key of the referred as the foreign key.
You can learn Database Design in next lesson.