A relational database management system compromises various component. Tables, records, attributes, instances, schemas and keys together form a relational database.
In this page, we will discuss each component of RDBMS in detail.
It is the primary component of RDMS. A relational database may constitute one or more tables related together. It is a combination of rows and columns where rows represent different entries in the database and column represents the features or attributes of those entries. Each table has a unique name in RDBMS and a primary key to uniquely identify the entry. These entries are also called entities of the table.
Each row of the table is a record of the single entity. This contains all the information about the entity and all its corresponding values to all the attributes of the particular entity. Each record in the table is called a tuple. For example: If we have an employee table with records of five employees, then each horizontal row with all detail of the particular employee is a Record.
Field or Column Name
Each column of a table represents a unique attribute of the entity. No two column names can be the same. They describe various attributes and features of the entity. For example: In the employee table there can be various attributes like employee name, employee_id and various other details.
It is a constraint on the entry of the value in the field of the table. It describes the set of values that can be accepted as valid inputs in the particular column. For example: In the Employee_phone_number this field will only accept a ten-digit integer number. The domain for various attributes can be specified by the user while creating the table. Any value that lies outside this set of values will not be accepted and an error will be thrown when such value is entered.
Schema and Instance
Schema: It describes the structure of the database at various levels. There are three layers of schema that separates the physical, logical and application part of the database. Schema determines the design of the database. Physical Schema, Logical Schema and the External Schema are mapped together. They interact with each other to perform the operations on the database. A schema represents all the entities of the database and the relationship between those entities.
1. Internal Level
The internal level of schema determines the definition of the database. It defines the physical structure of the database. It defines the format in which the data will be stored or say the structure in which the data will be stored. It is also known as the Physical Schema. It describes the complex data structures of the database. The user does not have the access to this level, only the data administrator can alter any change into it.
2. Conceptual Level
At this level, we describe the design of the database. This is called the logical schema; the end-user does not have access to this layer. The programmers and database administrators are the only ones with access to this level. At this level, we store the data in the data structures described at the physical level. Though the details of the implementation of the physical level are kept hidden at this level. It describes what data will be stored in the database.
3. External Level
It describes the various views of the database. This level is also called the view level. There can be different views for different users depending on the needs of the end-users. At this level, the end-user interacts with the database and the details of both physical and logical schemas are kept hidden from the user.
The hiding of details of one level from other and only showing the necessary information is called abstraction.
Instance: An instance is the view of the data stored in the database at a particular time. It is a snapshot of the database at that particular time. It is the moment at which we declare all the attributes of the table in the database and the data type variable that will be stored in the particular column. The instance of the database changes continuously whenever any updating is performed on the database.
There are various types of keys in a database. Let us discuss each type one by one.
Primary Key – Each table should at least have a primary key, this key can uniquely identify each record of that table.
Super Key – A super key is a combination of multiple columns to uniquely identify a record of the table.
Candidate Key – Any key in the table is also a candidate key of that table.
Alternate Key– Every key except the primary key of the table is an alternate key.
Composite Key– When we cannot uniquely identify each record with a single column entry, we combine multiple columns and assign the combination as the primary key.
Foreign Key – It refers to the primary key of the other table. They act as a way to refer to the entry of another table.