A Relational Database Management System(RDBMS) is a database management system based on the relational model introduced by E.F Codd. In the relational model, data is represented in terms of tuples(rows).
The relational database is a collection of an organised set of tables from which data can be accessed easily.
A collection of database elements organized in terms of rows and columns.
What is a Record?
A single entry in a table is called a Record or Row. A Record in a table represents set of related data. For example, the above Employee table has 4 records. Following is an example of a single record.
What is Field?
A table consists of several records(row), each record can be broken into several smaller entities known asFields. The above Employee table consists of four fields, ID, Name, Age and Salary.
What is a Column ?
In Relational table, a column is a set of value of a particular type. The term Attribute is also used to represent a column. For example, in Employee table, Name is a column that represent names of employee.
It is used to establish and identity relation between tables.
Super Key is defined as a set of attributes within a table that uniquely identifies each record within a table. Super Key is a superset of Candidate key.
- Specifies that no two distinct records have same value. (Combination of no two records will have same data)
- Super key may have redundant attributes.
Note: Follow this Video – https://www.youtube.com/watch?v=CsGalH0QKbs
A candidate key is a field orcombination of fields that can act as a primary key field for that table to uniquely identify each record in that table.A candidate key is a sub set of a Super Keys.
In order to be eligible for a candidate key it must pass certain criteria.
- It must contain unique values
- It must not contain null values
- It contains the minimum number of fields to ensure uniqueness
- It must uniquely identify each record in the table
- Once your candidate keys have been identified you can now select one to be your primary key
A simple key consists of a single field to uniquely identify a record.primary key is also known as simple key.
SECONDARY KEY OR ALTERNATIVE KEY :
The attributes that are not even the Super Key but can be still usedfor identification of records (not unique) are known as Secondary Key.Alternate Key can be any of the Candidate Keys except for the Primary Key. It is a subset of candidate key.
COMPOUND KEY :
A Combination of more than one column identifying records of a table uniquely, all the columns that take part in the combination process are Simple Key’s.
COMPOSITE KEY :
If we use multiple attributes to createa Primary Key then that Primary Key is called Composite Key. In thisall the other columns that take part in the combination process arenot simple keys.
PRIMARY KEY :
- It uniquely identifies each record in a database table.
- Primary keys must contain unique values.
- A primary key column cannot contain NULLvalues.
Non-key attributes are attributes other than candidate key attributes in a table.
Non-prime Attributes are attributes other than Primary attribute.
SQl Constraints are rules used to limit the type of data that can go into a table, to maintain the accuracy and integrity of the data inside table.
Constraints can be divided into following two types,
- Column level constraints : limits only column data
- Table level constraints : limits whole table data
Constraints are used to make sure that the integrity of data is maintained in the database. Following are the most used constraints that can be applied to a table.
- NOT NULL
- PRIMARY KEY
- FOREIGN KEY
NOT NULL Constraint
NOT NULL constraint restricts a column from having a NULL value. Once NOT NULL constraint is applied to a column, you cannot pass a null value to that column. It enforces a column to contain a proper value. One important point to note about NOT NULL constraint is that it cannot be defined at the table level.
Example using NOT NULL constraint
CREATE table Student(s_id int NOT NULL, Name varchar(60), Age int);
The above query will declare that the s_id field of Student table will not take NULL value.
UNIQUE constraint ensures that a field or column will only have unique values. A UNIQUE constraint field will not have duplicate data. UNIQUE constraint can be applied at column level or table level.
Example using UNIQUE constraint when creating a Table (Table Level)
CREATE table Student(s_id int NOT NULL UNIQUE, Name varchar(60), Age int);
The above query will declare that the s_id field of Student table will only have unique values and wont take NULL value.
Example using UNIQUE constraint after Table is created (Column Level)
ALTER table Student add UNIQUE(s_id);
The above query specifies that s_id field of Student table will only have unique value.
Primary Key Constraint
Primary key constraint uniquely identifies each record in a database. A Primary Key must contain unique value and it must not contain null value. Usually Primary Key is used to index the data inside the table.
Example using PRIMARY KEY constraint at Table Level
CREATE table Student (s_id int PRIMARY KEY, Name varchar(60) NOT NULL, Age int)
The above command will creates a PRIMARY KEY on the s_id.
Example using PRIMARY KEY constraint at Column Level
ALTER table Student add PRIMARY KEY (s_id);
The above command will creates a PRIMARY KEY on the s_id.
Foreign Key Constraint
FOREIGN KEY is used to relate two tables. FOREIGN KEY constraint is also used to restrict actions that would destroy links between tables. To understand FOREIGN KEY, let’s see it using two table.
Customer_Detail Table :
20 Cool funny Websites Kills Your Boredom-2016
18 Cool and Interesting Websites 2016 – Internet
The Art of Writing a Perfect Resume – Allinfi
In Customer_Detail table, c_id is the primary key which is set as foreign key in Order_Detail table. The value that is entered in c_id which is set as foreign key in Order_Detail table must be present in Customer_Detailtable where it is set as primary key. This prevents invalid data to be inserted into c_id column of Order_Detailtable.
Example using FOREIGN KEY constraint at Table Level
CREATE table Order_Detail(order_id int PRIMARY KEY,order_name varchar(60) NOT NULL, c_id int FOREIGN KEY REFERENCES Customer_Detail(c_id));
In this query, c_id in table Order_Detail is made as foriegn key, which is a reference of c_id column of Customer_Detail.
Example using FOREIGN KEY constraint at Column Level.
ALTER table Order_Detail add FOREIGN KEY (c_id) REFERENCES Customer_Detail(c_id);
Behaviour of Foriegn Key Column on Delete
There are two ways to maintin the integrity of data in Child table, when a particular record is deleted in main table. When two tables are connected with Foriegn key, and certain data in the main table is deleted, for which record exit in child table too, then we must have some mechanism to save the integrity of data in child table.
- On Delete Cascade : This will remove the record from child table, if that value of foriegn key is deleted from the main table.
- On Delete Null : This will set all the values in that record of child table as NULL, for which the value of foriegn key is elected from the main table.
If we don’t use any of the above, then we cannot delete data from the main table for which data in child table exists. We will get an error if we try to do so.ERROR: Record in child table exist
CHECK constraint is used to restrict the value of a column between a range. It performs check on the values, before storing them into the database. Its like condition checking before saving data into a column.
Example using CHECK constraint at Table Level
create table Student(s_id int NOT NULL CHECK(s_id > 0), Name varchar(60) NOT NULL, Age int);
The above query will restrict the s_id value to be greater than zero.
Example using CHECK constraint at Column Level
ALTER table Student add CHECK(s_id > 0);
If you found it is good, please like or comment below. Do visit for more articles.