RBMS

1. What is a Relational Database Management System?

A relational database management system (RDBMS or just RDB) is a common type of database that stores data in tables, so it can be used in relation to other stored datasets. This means any record can be related to any other record, and new relations can be easily added. All RDBMS use SQL queries to access the data stored within. All modern database management systems like SQL, MS SQL Server, IBM DB2, ORACLE, My-SQL and Microsoft Access are based on RDBMS.

2. How does a relational DBMS internally store its data?

They use B+ trees. B+ tree is a special data structure allowing to efficiently store (i.e. access and update) a large sorted dictionary on a block storage device (i.e. HDD or SSD).

Introduction of ER Model

3. Definitions of Entity and Attributes

ER Model is used to model the logical view of the system from data perspective

Entity, Entity Type, Entity Set :-

An Entity may be an object with a physical existence. For example, in a school database, students, teachers, classes, and courses offered can be considered as entities. An Entity is an object of Entity Type and set of all entities is called as entity set. e.g.; E1 is an entity having Entity Type Student and set of all students is called Entity Set.

Attribute(s): Attributes are the properties which define the entity type. For example, Roll_No, Name, DOB, Age, Address, Mobile_No are the attributes which defines entity type Student.

er2

1. Key Attribute:- The attribute which uniquely identifies each entity in the entity set is called key attribute.For example, Roll_No will be unique for each student.
2. Composite Attribute:- An attribute composed of many other attribute is called as composite attribute. For example, Address attribute of student Entity type consists of Street, City, State, and Country.

   
3. Multivalued Attribute:- An attribute consisting more than one value for a given entity. For example, Phone_No (can be more than one for a given student).

   
4. Derived Attribute:- An attribute which can be derived from other attributes of the entity type is known as derived attribute. e.g.; Age (can be derived from DOB).

   The complete entity type Student with its attributes can be represented as:

   

Weak Entity Type and Identifying Relationship: An entity type has a key attribute which uniquely identifies each entity in the entity set. But there exists some entity type for which key attribute can’t be defined. These are called Weak Entity type.

For example, A company may store the information of dependants (Parents, Children, Spouse) of an Employee. But the dependents don’t have existence without the employee. So Dependent will be weak entity type and Employee will be Identifying Entity type for Dependant.

n3

4. Three Types of Relationships in an ER Diagram (Cardinality)

There are three types of relationships that can exist between two entities.

1.One to one :- In relational database design, a one-to-one (1:1) relationship exists when zero or one instance of entity A can be associated with zero or one instance of entity B, and zero or one instance of entity B can be associated with zero or one instance of entity A.

In this case, a man can be married to only one woman; a woman can be married to only one man

2. Many to one :- n relational database design, a one-to-many (1:N) relationship exists when, for one instance of entity A, there exists zero, one, or many instances of entity B; but for one instance of entity B, there exists zero or one instance of entity A.

Using Sets, it can be represented as:

In this case, each student is taking only 1 course but 1 course has been taken by many student.

3.Many to many :- In relational database design, a many-to-many (M:N) relationship exists when, for one instance of entity A, there exists zero, one, or many instances of entity B; and for one instance of entity B, there exists zero, one, or many instances of entity A.

Using sets, it can be represented as:

In this example, student S1 is enrolled in C1 and C3 and Course C3 is enrolled by S1, S3 and S4. So it is many to many relationships.

5. Participation Constraint:

Participation Constraint is applied on the entity participating in the relationship set.

1. Total Participation :- Each entity in the entity set must participate in the relationship. If each student must enroll in a course, the participation of student will be total.

2. Partial Participation :- The entity in the entity set may or may NOT participate in the relationship. If some courses are not enrolled by any of the student, the participation of course will be partial.

   Using set, it can be represented as,

   

   Every student in Student Entity set is participating in relationship but there exists a course C4 which is not taking part in the relationship.

6. Relational Model in DBMS

Relational Model represents how data is stored in Relational Databases. A relational database stores data in the form of relations (tables).

STUDENT

ROLL_NO	NAME	ADDRESS	PHONE	AGE
1	RAM	DELHI	9455123451	18
2	RAMESH	GURGAON	9652431543	18
3	SUJIT	ROHTAK	9156253131	20
4	SURESH	DELHI		18

IMPORTANT TERMINOLOGIES

• Attribute: Attributes are the properties that define a relation. e.g.: ROLL_NO, NAME

• Relation Schema: A relation schema represents name of the relation with its attributes. e.g.; STUDENT (ROLL_NO, NAME, ADDRESS, PHONE and AGE) is relation schema for STUDENT. If a schema has more than 1 relation, it is called Relational Schema.

• Tuple: Each row in the relation is known as tuple. The above relation contains 4 tuples, one of which is shown as:

• Relation Instance: The set of tuples of a relation at a particular instance of time is called as relation instance. Table 1 shows the relation instance of STUDENT at a particular time. It can change whenever there is insertion, deletion or updation in the database.

• Degree: The number of attributes in the relation is known as degree of the relation. The STUDENT relation defined above has degree 5.

• Cardinality: The number of tuples in a relation is known as cardinality. The STUDENT relation defined above has cardinality 4.

Constraints in Relational Model

While designing Relational Model, we define some conditions which must hold for data present in database are called Constraints. These constraints are checked before performing any operation (insertion, deletion and updation) in database. If there is a violation in any of constrains, operation will fail.

Domain Constraints: These are attribute level constraints. An attribute can only take values which lie inside the domain range. e.g,: If a constrains AGE>0 is applied on STUDENT relation, inserting negative value of AGE will result in failure.

Key Integrity: Every relation in the database should have at least one set of attributes which defines a tuple uniquely. Those set of attributes is called key. e.g.; ROLL_NO in STUDENT is a key. No two students can have same roll number. So a key has two properties:

• It should be unique for all tuples.

• It can’t have NULL values.

Referential Integrity: When one attribute of a relation can only take values from other attribute of same relation or any other relation, it is called referential integrity.

7. Types of Keys in Relational Model (Candidate, Super, Primary, Alternate and Foreign)

Different Types of Keys in Relational Model

image

Candidate Key: The minimal set of attribute which can uniquely identify a tuple is known as candidate key. For Example, STUD_NO in STUDENT relation.

• The value of Candidate Key is unique and non-null for every tuple.

• There can be more than one candidate key in a relation.

Super Key: The set of attributes which can uniquely identify a tuple is known as Super Key. For Example, STUD_NO, (STUD_NO, STUD_NAME) etc.

• Adding zero or more attributes to candidate key generates super key.

• A candidate key is a super key but vice versa is not true.

Primary Key: There can be more than one candidate key in relation out of which one can be chosen as the primary key. For Example, STUD_NO, as well as STUD_PHONE both, are candidate keys for relation STUDENT but STUD_NO can be chosen as the primary key (only one out of many candidate keys).

Alternate Key: The candidate key other than the primary key is called an alternate key. For Example, STUD_NO, as well as STUD_PHONE both, are candidate keys for relation STUDENT but STUD_PHONE will be alternate key (only one out of many candidate keys).

Foreign Key: A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It acts as a cross-reference between tables because it references the primary key of another table.

8. Database Schema

A database schema is the skeleton structure that represents the logical view of the entire database. It defines how the data is organized and how the relations among them are associated. It formulates all the constraints that are to be applied on the data.

A database schema defines its entities and the relationship among them. It’s the database designers who design the schema to help programmers understand the database and make it useful.

A database schema can be divided broadly into two categories −

• Physical Database Schema :- This schema pertains to the actual storage of data and its form of storage like files, indices, etc. It defines how the data will be stored in a secondary storage.

• Logical Database Schema :- This schema defines all the logical constraints that need to be applied on the data stored. It defines tables, views, and integrity constraints.

9. Database Instance

It is important that we distinguish these two terms individually. Database schema is the skeleton of database. It is designed when the database doesn't exist at all. Once the database is operational, it is very difficult to make any changes to it. A database schema does not contain any data or information.

A database instance is a state of operational database with data at any given time. It contains a snapshot of the database.