- DBMS Tutorial
- What is Database Management System (DBMS)?
- Components of DBMS
- Applications of DBMS
- Three Schema DBMS Architecture
- Difference between DBMS and RDBMS?
- Difference between File Oriented System and DBMS
- Types of Data Models
- DBMS Schema and Instances
- Data Independence and Data Abstraction
- Database Users and Administrator
- DBMS Languages and Interfaces
DBMS ER Model
DBMS Relational Data Model
A relation always has a specific identifier, a field or class of fields (attributes) whose values are specific throughout all of the rows of a relation. Thus, each row is distinct and can be recognized by the values of one or more of its attributes known as a key in DBMS. Keys are always minimal series of attributes that support the uniqueness quality.
Types of Keys
There are following types of keys which are as follows
An attribute which identifies a tuple uniquely is known as the primary key.
Rules for representing Primary Key
Two tuples cannot hold similar primary key value.
It needs to be for each tuple to include a primary key value.
The primary key rule cannot be invalid.
The value in a primary key column cannot be changed or amend if any foreign key defines to that primary key.
Consider the following Employee Table
Note: EMP-ID is a primary key.
A primary key can have more than one attribute such type of key is known as the concatenated key.
The secondary key identifies the tuple but not uniquely. So, the secondary key identifies the collection of records (tuples).
In the given table, EMP-NAME and AGE are the secondary keys in Employee relation.
Super key is an attribute or the set of attributes that uniquely recognizes a tuple within a relation. The super key is a superset of the candidate key.
In the given table, EMP-ID, and EMP-NAME (the name of two employees) can be similar, but their EMP-ID cannot be the same, and this mixture can also be a key. Equally, PHONE-NO for each employee will be unique. So, they are all super key.
There may be more than one attribute in a relation such that they identify a tuple uniquely (i.e., each of the attribute has the property of primary key). These attributes are referred to as a candidate key. It is a subset of the super key.
Rules for representing Candidate key
It must include unique values.
It should not involve null values.
It must involve minimum areas to secure uniqueness.
It uniquely recognizes each information in a table.
In the given table EMP- ID, EMP-NAME, AGE and PHONE-NO are candidate keys that support us to recognize the student information in the table uniquely.
A foreign key is an attribute (or attribute combination) of an association that is the primary key of another association. It is an attribute that develops a relationship among two tables. This technique is also known as Referential Integrity.
Consider the following Employee Table and Department Table
ENO is the primary key for ‘Employee’, and DNO is the primary key for ‘Department’ and is the foreign key in ‘Employee’ table, describing to the primary key of ‘Department’ relation.
The candidate key, which is not chosen as the primary key, is known as the alternate keys. It is also known as Secondary key.
In the given table, EMP-ID, EMP-NAME, and Phone No are candidate keys and are adequate for being the Primary key. As a result of EMP-ID is the primary key, so EMP-NAME and Phone No. develop into the alternate key.
Apply now for Advanced DBMS Course