Database Management System, Concepts and Relational Model

single

A database is an organized collection of data, so that it can be easily accessed and managed. Where as a database management system (DBMS) or database system in short, is a software that can be used to create and manage databases. DBMS lets users to create a database, store, manage, update/modify and retrieve data from that database by users or application programs. Some examples of open source and commercial DBMS include MySQL, Oracle, PostgreSQL, SQL Server, Microsoft Access, MongoDB. Today we are going to cover all the basic concepts of Database and Relation Concept.


1. Data and Information

Data is a collection of any raw numbers, characters and other letters. Information is processed, organized and structured data, which has some meaning.


2. File System of Storing Data

A file can be understood as a container to store data in a computer. Files can be stored on the storage device of a computer system. Contents of a file can be texts, computer program code, comma separated values (CSV), etc. Likewise, pictures, audios/videos, web pages are also files.:

Limitation of File System:

  • Difficulty in Access
  • Data Redundancy
  • Data Isolation
  • Uncontrollable Data Sharing

  • 3. Database Management System

    A database management system (DBMS) or database system in short, is a software that can be used to create and manage databases. DBMS lets users to create a database, store, manage, update/modify and retrieve data from that database by users or application programs. Some examples of open source and commercial DBMS include MySQL, Oracle, PostgreSQL, MongoDB. A database system hides certain details about how data are actually stored and maintained.

    Application of DBMS:

  • Banking
  • Inventory Management
  • Organisation Resource Management
  • Online Shopping

  • 4. Database Schema(Schema)

    Database Schema is the design of a database. It is the skeleton of the database that represents the structure (table names and their fields/columns), the type of data each column can hold, constraints on the data to be stored (if any), and the relationships among the tables.


    5. Data Constraint

    IConstraints are the limitation or restriction given to the type of data that can be stored in a field/column of a table. There can be multiple constrains for a column. For example: one can define the constraint that the column mobile number can only have non-negative integer values of exactly 10 digits.


    6. Relation

    In relational model, tables are called relations that store data for different columns. For example here is a relation STUDENT.

    ID NAME PHONE ADDRESS
    1 Dean 5552225556 ABC
    2 Ambrose 5552225557 XYZ
    3 Jon 5552225558 PQR

    7. Attribute

    Characteristic or parameters for which data are to be stored in a relation. Simply stated, the columns of a relation are the attributes which are also referred as fields. For example, ID, NAME, PHONE and ADDRESS can be attributes of relation STUDENT.


    8. Tuple

    Each row of data in a relation (table) is called a tuple. In a table with n columns, a tuple is a relationship between the n related values.


    9. Domain

    It is a set of values from which an attribute can take a value in each row. Usually, a data type is used to specify domain for an attribute. For example, in STUDENT relation, the attribute ROLLNUMBER takes integer values and hence its domain is a set of integer values. Similarly, the set of character strings constitutes the domain of the attribute NAME.


    10. Degree

    The number of attributes in a relation is called the Degree of the relation. For example, relation STUDENT with four attributes is a relation of degree 4.


    11. Cardinality

    The number of tuples in a relation is called the Cardinality of the relation. For example, the cardinality of relation STUDENT is 3 as there are 3 tuples in the table.


    12. Keys In DBMS

    12.A. Candidate Key

    A relation can have one or more attributes that takes distinct values. Any of these attributes can be used to uniquely identify the tuples in the relation. Such attributes are called candidate keys as each of them are candidates for the primary key. For example: ID and NAME are the candidate key for the above relation STUDENT as both of these attribute can be used to uniquely identify the rows.

    12.B. Primary Key

    Out of one or more candidate keys, the attribute chosen by the database designer to uniquely identify the tuples in a relation is called the primary key of that relation. The remaining attributes in the list of candidate keys are called the alternate keys.

    12.C. Foreign Key

    A foreign key is used to represent the relationship between two relations. A foreign key is an attribute whose value is derived from the primary key of another relation. This means that any attribute of a relation (referencing), which is used to refer contents from another (referenced) relation, becomes foreign key if it refers to the primary key of referenced relation. The referencing relation is called Foreign Relation.

    STUDENT Relation

    ID NAME PHONE ADDRESS
    1 Dean 5552225556 ABC
    2 Ambrose 5552225557 XYZ
    3 Jon 5552225558 PQR

    ATTENDANCE Relation

    DATE STUDNET_ID ATTENDANCE MONTH
    01-12-2002 1 P February
    01-12-2002 2 P February
    01-12-2002 3 A February

    In the above two tables the relation STUDENT is linked with relation ATTENDANCE using a attribute STUDENT_ID of the both the relation.