#DBMS  #Database 

Relational Model

  • Data relation represented in the form of table with columns and rows are called Relational Model
  • Tuple
    • Each row is known as a tuple
  • Columns
    • It represents the attributes of the relation
    • Each attribute, there is a permitted value, called domain of the attribute.
  • Cardinality
    • Total number of tuples in a given relation.
  • Degree of table
    • Number of attributes/columns in a given table/relation.
  • A relational DB consists of collection of tables, each of which is assigned a unique name.
    • Software that implemented Relation model is RDBMS
    • Eg: Oracle, IBM, MySQL, MS Access
  • Relation Schema
    • It defines the design and structure of the relation, contains the name of the relation and all the columns/attributes.
  • Relational Key
    • Set of attributes which can uniquely identify an each tuple.
  • Comparison with ER-Model
    • Entity –> Table
    • Attribute –> Column
    • Relation –> Foreign Key (FK)

Important properties of a Table in Relational Model

  1. The name of relation is distinct among all other relation.
  2. The values have to be atomic. Can’t be broken down further.
  3. The name of each attribute/column must be unique.
  4. Each tuple must be unique in a table.
  5. The sequence of row and column has no significance.
  6. Tables must follow integrity constraints - it helps to maintain data consistency across the tables.

Relational Model Keys

  • Super Key (SK)
    • Any Permutation & Combination of attributes present in a table which can uniquely identify each tuple.
  • Candidate Key (CK)
    • Minimum subset of super keys, which can uniquely identify each tuple.
    • It contains no redundant attribute.
    • CK value shouldn’t be NULL.
  • Primary Key (PK)
    • An single attributes that can uniquely identify each tuple
  • Alternate Key (AK)
    • All CK except PK.
  • Foreign Key (FK)
    • It creates relation between two tables
    • PK of Table T1 is used as attribute of another Table T2
    • This attribute in T2 is referred as FK
    • FK helps to cross-reference between two different relations.
  • Composite Key
    • PK formed using at least 2 attributes
  • Compound Key
    • PK which is formed using 2 FK
  • Surrogate Key
    • Synthetic PK.
    • Generated automatically by DB, usually an integer value.
    • May be used as PK.

Integrity Constraints

  • CRUD Operations must be done with some integrity policy so that DB is always consistent.
  • Introduced so that we do not accidentally corrupt the DB.
  • Domain Constraints
    • Restricts the value in the attribute of relation, specifies the Domain.
    • Restrict the Data types of every attribute.
    • E.g. birth year < 2002
  • Entity Constraints
    • Every relation should have PK
    • PK != NULL
  • Referential Constraints
    • Specified between two relations & helps maintain consistency among tuples of two relations.
    1. Insert Constrain
    2. Delete Constrain
      • Whenever the referenced object is deleted we have the following option to maintain Delete constrain
      1. CASCADE
        • The objects referencing it are deleted as well
      2. SET_NULL
        • Set the ForeignKey to null
      3. SET_DEFAULT
        • Set the ForeignKey to its default value
      4. DO_NOTHING
        • Do nothing - Consistency must be handled elsewhere
  • Key Constraints
    • The six types of key constraints present in the Database management system are:-
    1. NOT NULL
      • This constraint will restrict the user from not having a NULL value
      • It ensures that every element in the database has a value.
    2. UNIQUE
      • It helps us to ensure that all the values consisting in a column are different from each other
    3. DEFAULT
      • It is used to set the default value to the column.
      • The default value is added to the columns if no value is specified for them.
    4. CHECK
      • It is one of the integrity constraints in DBMS.
      • It keeps the check that integrity of data is maintained before and after the completion of the CRUD.
    5. PRIMARY KEY
      • This is an attribute or set of attributes that can uniquely identify each entity in the entity set.
      • The primary key must contain unique as well as not null values.
    6. FOREIGN KEY
      • Whenever there is some relationship between two entities, there must be some common attribute between them.
      • This common attribute must be the primary key of an entity set and will become the foreign key of another entity set.
      • This key will prevent every action which can result in loss of connection between tables.

Transform - ER Model to Relational Model

  • Both ER-Model and Relational Model are abstract logical representation of real world enterprises.
  • Because the two models implies the similar design principles, we can convert ER design into Relational design.
  • Converting a DB representation from an ER diagram to a table format is the way we arrive at Relational DB-design from an ER diagram.

ER diagram notations to relations:

  • Strong Entity
    1. Becomes an individual table with entity name, attributes becomes columns of the relation.
    2. Entity’s Primary Key (PK) is used as Relation’s PK.
    3. FK are added to establish relationships with other relations.
  • Weak Entity
    1. A table is formed with all the attributes of the entity.
    2. PK of its corresponding Strong Entity will be added as FK
    3. PK of the relation will be a composite PK, {FK + Partial discriminator Key}.
  • Single Values Attributes
    1. Represented as columns directly in the tables/relations.
  • Composite Attributes
    1. Handled by creating a separate attribute itself in the original relation for each composite attribute.
    2. E.g., Address: {street-name, house-no}
      • address-street-name & address-house-name as new columns in the existing table and ignore “address” as an attribute.
  • Multivalued Attributes
    1. New tables are created for each multivalued attribute.
    2. PK of the entity is used as column FK in the new table.
    3. Multivalued attribute’s similar name is added as a column to define multiple values.
    4. PK of the new table would be {FK + multivalued name}.
    5. e.g., For Strong entity Employee, dependent-name is a multivalued attribute.
      1. New table named dependent-name will be formed with columns emp-id, and dname.
      2. PK: {emp-id, name}
      3. FK: {emp-id}
  • Derived Attributes
    • Not considered in the tables
  • Generalisation
    • For e.g., Banking System generalisation of Account - saving & current.
    1. Method-1:
      • Create 3 tables
      1. Table 1: account (account-number, balance)
      2. Table 2: savings-account (account-number[FK], interest-rate, daily-withdrawal-limit)
      3. Table 3: current-account (account-number[FK], overdraft-amount, per-transaction-charges)
    2. Method-2
      • Create 2 tables
      1. Table 1: savings-account (account-number, balance, interest-rate, daily-withdrawal-limit)
      2. Table 2: current-account (account-number, balance, overdraft-amount, per-transaction-charges)
      • Drawbacks of Method-2
        • Common/overlapping attribute
  • Aggregation
    1. Table of the relationship set is made
    2. Attributes includes primary keys of entity set and aggregation set’s entities.
    3. Also, add descriptive attribute if any on the relationship. Aggregation
  • Unary Relationship
    • 1:1 Unary relationship Aggregation
    • M:N Unary relationship Aggregation