Relational Model in DBMS

     

    Relational model can represent as a table with columns and rows. Each row is known as a tuple. Each table of the column has a name or attribute.

    Domain: It contains a set of atomic values that an attribute can take.

    Attribute: It contains the name of a column in a particular table. Each attribute Ai must have a domain, dom(Ai)

    Relational instance: In the relational database system, the relational instance is represented by a finite set of tuples. Relation instances do not have duplicate tuples.

    Relational schema: A relational schema contains the name of the relation and name of all columns or attributes.

    Relational key: In the relational key, each row has one or more attributes. It can identify the row in the relation uniquely.

     

    Example: STUDENT Relation

    • In the given table, NAME, ROLL_NO, PHONE_NO, ADDRESS, and AGE are the attributes.
    • The instance of schema STUDENT has 5 tuples.
    • t3 = <Laxman, 33289, 8583287182, Gurugram, 20>

     

     

     

    Properties of Relations

    • Name of the relation is distinct from all other relations.
    • Each relation cell contains exactly one atomic (single) value
    • Each attribute contains a distinct name
    • Attribute domain has no significance
    • tuple has no duplicate value
    • Order of tuple can have a different sequence

    Structure of Relational Databases

     

    The structure of relational databases is based on the relational model, which organizes data into tables (also called relations). Each table is composed of rows (tuples) and columns (attributes). Here is a detailed overview of the structure and components of relational databases:

    1. Tables (Relations) :

     

     A table is a collection of related data entries and consists of columns and rows.

    Example: A table named STUDENT with columns for NAME, ROLL_NO,PHONE_NO, ADDRESS, and AGE.

    2. Columns (Attributes) :

     

     Columns represent the attributes of the entity modeled by the table. Each column has a unique name and a specific data type.

    Attributes Example:

      • NAME (String)
      • ROLL_NO (Integer)
      • PHONE_NO (String or Integer, depending on format)
      • ADDRESS (String)
      • AGE (Integer)

    3. Rows (Tuples) :

     

     Rows represent individual records in the table. Each row contains a set of values for the attributes.

    Example: A row in the STUDENT table: (Ram, 14795, 7305758992, Noida, 24)

    4. Domains :

     

    A domain is a set of allowable values for one or more attributes. Each attribute in a table is associated with a domain.

    Example: The domain of AGE could be all integers from 0 to 120.

    5. Schemas :

     

     A schema defines the structure of a table, including the table name, column names, and data types.

    6. Keys :

    • Primary Key: A column or a set of columns that uniquely identifies each row in the table.
      • Example: ROLL_NO in the STUDENT table.
    • Foreign Key: A column or a set of columns in one table that references the primary key of another table, establishing a relationship between the tables.
      • Example: If there is another table COURSE with ROLL_NO as a foreign key linking to the STUDENT table.

    7. Indexes :

     

    Indexes are used to improve the speed of data retrieval operations on a table. They are created on one or more columns.

    Example: An index on ROLL_NO to quickly search students by their roll number.

    8. Constraints :

     

    Constraints are rules enforced on data columns to ensure data integrity and accuracy.

    • NOT NULL: Ensures that a column cannot have a NULL value.
    • UNIQUE: Ensures that all values in a column are unique.
    • PRIMARY KEY: Combines NOT NULL and UNIQUE. Ensures that a column (or set of columns) uniquely identifies each row in the table.
    • FOREIGN KEY: Ensures the referential integrity of the data in one table to match values in another table.
    • CHECK: Ensures that the values in a column meet a specific condition.
    • DEFAULT: Sets a default value for a column when no value is specified.

    9. Relationships :

     

     Relationships define how tables are related to each other. They can be one-to-one, one-to-many, or many-to-many.

    • One-to-One: Each row in one table is linked to one and only one row in another table.
    • One-to-Many: Each row in one table is linked to multiple rows in another table.
    • Many-to-Many: Each row in one table can be linked to multiple rows in another table and vice versa.