Relational Model in DBMS

History of Relational Model

Proposed by E.F. Codd in 1970

• "A relational model of data for large shared data banks"

Objectives of relational model

• High degree of data independence
• Dealing with issues e.g. data semantics, consistencies, and redundancy
• To enable the expansion of set-oriented data manipulation languages.

Examples of RDBMSs

• Oracle
• Microsoft SQL Server
• Microsoft Access
• Visual FoxPro
• InterBase
• JDataStore
• R:Base

Relational Model

• Based on mathematical concept of a relation.
• Codd, a trained mathematician, used terminology taken from mathematics, principally set theory and predicate logic.

Relational Model Terminology

• A relation is a table with columns and rows
• Attribute is a named column of a relation
• Domain is the set of allowable values for one or more attributes.
• Tuple is a row of a relation
• Degree is the number of attributes in a relation
• Cardinality is the number of tuples in a relation
• Relation Database is a collection of normalized relations with distinct relation names.

Instances of Sample Relation

Examples of Attribute Domains

Alternative Terminology

Mathematical Relations

• Consider two sets, D1 & D2

             - Where D1 = {2,4}  and D2 = {1,3,5}

• Cartesian product, D1 х D2 is,

            - Set of all ordered pairs where first element is member of D1 and second element is member of D2 
                   * D1 х D2 = { (2,1) . (2,3) . (2,5) . (4,1) . (4,3) . (4,5)
                   * Alternative way is to find all combinations of elements with first from D1 and second from D2.

Any subset of Cartesian product is a relation; e.g.

• R = { (2,1), (4,1)}
• May specify which pairs are in relation using some condition for selection; e.g.
• Second element is 1:

             - R = {(x,y) | x є D1, y є D2, and y = 1}

• First element is always twice the second:

            - S = {(x,y) |  x є D1, y є D2, and x =2y}

• Relating with database relations

        - Consider Branch relation in the example where branchNo = {B002, B003}  city = {London, Glasgow}

• Cartesian product, branchNo х city is,

         - {(B002, London), (B002, Glasgow), (B003, London), (B003, Glasgow)}

• Any subset of Cartesian product is a relation

Database Relations

Relation schema

• Named relation defined by a set of attribute and domain name pairs
• Let A1, A2,....,An be attributes with domain D1, D2,....,Dn
• Then the set {A1:D1, A2:D2, ..... , An:Dn} is a relation schema
• A relation T defined by a relation schema S is a set of mapping from the attributes names to their corresponding domains. Thus, relation R is a set of n-tuples:
• (A1:d1, A2:d2,....., An:dn) such that
• d1∈D1, d2∈D2,.....,dn∈Dn
• Branch relation schema

         * Branch = { branchNo:BranchNumbers, street:StreetNames, city:CityNames, postcode:PostCodes}

• Tuple of a Branch relation

         {branchNo:B005, street:22DeerRd, city:London, postcode:SW1 4EH}

Relational Database Schema

• If R1, R2,..., Rn are a set of relation schemas, then we can write the relational database schema, or simply relational schema, R, as:

           R = {R1, R2,....., Rn}

Properties of Relations

• Relation name is distinct from all other relation names in relational schema.
• Each cell of relation contains exactly one atomic (single) value
• Each attributes has a distinct name
• Values of an attribute are all from the same domain
• Each tuple is distinct; there are no duplicate tuples
• Order of attributes has no significance
• Order of tuples has no significance, theoretically