What is a DBMS?

When we talk about storing data, we can think of many ways for example if we have to
store records of 100 students containing their roll numbers, names and marks then we
can simply type them in a notepad file. But the big question is – Is the data organized?
The answer is No. The reasons are many for example think about answering these
questions:
1. Display top 10 students.
2. Display names in alphabetical order.
3. Display list of failed students.
4. How to make sure that a rollnumber once given should not be repeated.

There can be many such questions. The correct solution is to use a system that is
specifically developed for storing data in an organized manner. This type of system is
called as database management system or simply DBMS.

Database Management System is a software system that allows storage, modification,

and extraction of information from a database. The examples of DBMS are MS-ACCESS,
ORACLE, SQL SERVER, MySQL etc.

Definitions:

1. Relation: A relation is a table having atomic values, unique and ordered rows and
columns. It is actually a table where we store data.

2. Tuple (Record): A row in a relation is known as a Tuple.

3. Attribute (Field): A column in a relation is known as an attribute.

4. Degree: Number of attributes in a relation is called its degree.

5. Cardinality: Number of tuples in a relation is called its cardinality.

6. Redundancy: Duplication of data in key field is known as redundancy. Redundant

data makes the database inconsistent.

7. Primary Key: It is a set of one or more attributes that can uniquely identify tuples
(rows) within the relation.

1|Page
8. Candidate Key: All attributes combinations inside a relation that can serve as
primary key. (The key’s that are opting for the post of primary key are called candidate
key)

9. Foreign Key: Attributes whose values are derived from the primary key of some other
table.

10. Alternate Key: A candidate key which is not primary key.

11. Database: It refers to collection of interrelated data.

Example:

Consider following Table / Relation named as STUDENT:

RNO SCHOLARNO NAME CITY PHONE CLASS
R1 S1 Raj Indore 9955 XI
R2 S2 Ajay Delhi 8855 XII
R3 S3 Vijay Mumbai 3366 XII
R4 S4 Gourav Delhi 5488 XI
R5 S5 Tinku Goa 3355 XI

This table has following information:

1. The Cardinality of this table is 5 as there are total 5 records / rows / tuples.
2. The Degree of this table is 6 as there are total 6 columns / attributes / fields.
3. The candidate keys are ________________________________________.
4. If RNO is the primary key then Alternate keys are ___________________________.
5. If SCHOLARNO is the primary key then Alternate keys are ___________________.

Another Example: Consider the following two tables – STUDENT and RESULT. The
STUDENT table contains the information about Students’ personal details and the
RESULT table contains information about results of SEM I and SEM II for all the
students.

STUDENT

RNO SCHOLARNO Name CITY PHONE CLASS

R1 S1 Raj Indore 9955 XI

R2 S2 Ajay Delhi 8855 XII

R3 S3 Vijay Mumbai 3366 XII

R4 S4 Gourav Delhi 5488 XI

R5 S5 Tinku Goa 3355 XI

2|Page
RESULT

RNO SEM MARKS

R1 I 56

R2 II 78

R3 I 98

R1 II 87

R2 I 56

R3 II 85

R4 I 86

R4 II 63

R5 I 97

R5 II 65

Now think about these two tables by taking a close look. You can clearly see that these
tables are connected to each other. How? To understand this try to answer the following
question:

How many marks Vijay got in SEMII?

Yes the answer is 85 but how were you able to tell this? You have actually matched the
RNO of Vijay in both the tables where SEM is SEMII. So RNO is the common column here
in both the tables. This RNO is the primary key in STUDENT table and we know the
reason. But is RNO a primary key in RESULT table as well? The answer is no because
clearly there are duplicate values in RNO column.

The RNO column in RESULT table is actually a foreign key because in this column only
those values can be entered that are first present in RNO column in STUDENT table. We
simply cannot enter a Roll Number which does not exist.

This is called primary key - foreign key relationship. Also conceptually when a foreign
key column ensures that it contains only those values that are present in the primary
key column, it’s called as Referential Integrity.

There is one more concept here. Look at the table RESULT closely. Can you find a primary
key here? No because all columns here may contain duplicate values and it is logically

3|Page
correct for RESULT table. According to DBMS concepts each record should be uniquely
identified by a primary key.

In table RESULT the combination of RNO and SEM cannot appear twice. Hence this
combination of RNO and SEM is working as the primary key in result table. Remember
there can only be one primary key in a table. So in case of RESULT table this one primary
key is (RNO, SEM) combination. Such a primary key in which more than one columns
take part is called as composite primary key.

SQL (Structured query language):

SQL is a query language through which we interact with the DBMS or to operate any
DBMS we use SQL commands. There are 3 types of SQL statements/Commands:

Data Definition Language (DDL): SQL commands which are related with the structure
of the table like creating a table structure, making changes in the structure of the table
and deleting the table. DDL contains 3 SQL commands:

1. CREATE
2. ALTER
3. DROP

Data Manipulation Language (DML): SQL commands which are related with the data
stored in the table like inserting records in a table, updatting records in a table and
deleting records from a table. DML contains following 4 SQL commands:

1. SELECT
2. INSERT
3. UPDATE
4. DELETE

Data types in MySQL

• int (n): Used to store integer values in a field. Here n is the number of digits it can
take.
• varchar (n): Variable-length character string in the range 1 to 255 characters.
• char (n): used to store fixed length in the range 1 to 255.

4|Page
 • decimal (size, d): Used to store numbers with a decimal point. Size is total number
of digits excluding decimal point and d is maximum number of digits after the
decimal point.
• date: Used to store dates in the format YYYY-MM-DD.
• time: used to store time in the format HH:MM:SS.

SQL Commands:

1. creating a database: (All tables are stored inside databases)

mysql>create database databasename;

Example:

mysql>create database studentdatabase;

2. To display the list of databases:

mysql>show databases;

3. To select/use the database that we have created.

mysql>use databasename;

4. create table command: This is used to create a table.

mysql>create table tablename(fieldname1 datatype(size), fieldname2
datatype(size)….);

Example:

mysql>create table students (rollno int(2), name varchar(30), address

varchar(50));

Note: Every SQL statement must be terminated by a semicolon symbol

To see the structure(schema) of this table use the following command:

mysql>desc students;

It will display the table description as follows:

5|Page
5. To display list of all tables in a database:
mysql>show tables;

6. Insert command: This command is used to insert records in a table. For example to
insert records in the above table we will write insert command as follows:

Syntax: insert into tablename values (value1,value2,…….);

Example:

mysql>insert into students values(10,'Raj sharma','Indore');

mysql>insert into students values(20,'Ajay jain','Delhi');

The above two statements will insert 2 records in the table [Link] insert
values in selected columns, we use the insert command in the following manner:

mysql>insert into students(rollno, name) values (30,'Vijay Shah');

In this command we are inserting values only for the fields rollno and name. The
third column address will contain a special value called NULL value in this case.
NULL value means empty value.

Note: When you insert records remember this: Always put single quotes
around a character type of value and date type of value and do not put
single quote around a numerical value.

7. Select command: The select command has many variations. It is used to fetch data
from the table. Here we will only see how to display all fields and all records from the
above table.
To see all fields and all rows use the following query:
mysql> select * from students;

This will give the following output:

6|Page
Constraints (Or Integrity Constraints):

Constraint is a condition or rule applied on a field or on a set of fields. When you enter
data in a table this condition must be satisfied.

1. Primary key constraint: Primary key uniquely identifies each record in the table. We
cannot have duplicate/repeated values in the field onto which the primary key
constraint is set. The following is the example of how to create a primary key constraint
on rollno field in the table mentioned above (because rollno field will contain unique
values for all records)
Example:

mysql>Create table students

(rollno int(3) primary key, name varchar(30), address varchar(50));

In the above table we cannot insert duplicate values in rollno field.

2. NOT NULL Constraint: This constraint if applied on any column then that column
can never take null values i.e. it can never be left blank while inserting records in the
table.
Example:

mysql>Create table students

(rollno int(2) primary key, name char(30) NOT NULL,

address char(50) NOT NULL);

Now when we insert values in the above created table, we must give values for name
and address fields.

3. Unique Constraint: It is just like primary key but the difference is we can have null
values in unique fields and in primary key we cannot.
Example:

mysql>Create table students

(rollno int(2) primary key, name char(30) unique, address char(50));

Now when we insert records in the above created table it will not accept duplicate
names.

7|Page
 Note: A primary key can never be null so NOT NULL is automatically set when
we set a primary key constraint on the table. Also the values in the primary
key field are unique so UNIQUE constraint is automatically set on the primary
key field.

Primary key and foreign key relationship

We can link two or more tables with the help of a primary key. There is field that acts as
a primary key in one table. In another table a field with same data type can be set as a
foreign key. The data in this foreign key field must first present in the primary key field.
This way the foreign key field cannot contain any value which is not present in the
primary key field. This is shown in the following example:

Student table Attendance table

Rno Name
(The Rno field Rno Month Attendance
1 Raj
is a primary key here) 1 Aug 68
2 Ajay
2 Sep 96
3 Vijay
1 Sep 88
4 Ankita
4 Jul 30
5 Anshul
2 Nov 90
6 Gourav

7 Smith

8 Rex

(The Rno field is a foreign key here)

Think about this! Can we have a roll number 11 entered in second table. The answer is
No. This is because there is no Roll number 11 in the primary key field or in other words
there is no existence of Roll No 11. That way we can say the First and Second table should
be linked in order to make this happen.

To set primary key:

mysql>create table student (rno int(3) primary key, name varchar(30));

To set foreign key:

8|Page
 mysql>create table marks (rno int(3), month varchar(15), per char(5),

foreign key(rno) references student(rno));

The SELECT Command:

The Select command of SQL lets you fetch data from table. The Select command has the
following syntax:

SELECT column1, column2,….. / * / SingleRowfunction / aggregatefunction /

distinct columnname / Calculated column

FROM tablename(s)

WHERE condition(s)

GROUP BY groupcolumnname(s)

HAVING condition on groupcolumnname / on any aggregate function

ORDER BY columnname asc / desc;

Note: This is a complete syntax of select command. Not everything is going to be

used now. Remember the order of SELECT, FROM, WHERE, GROUP BY, HAVING and
ORDER BY.

Example:

mysql>select eno, ename from employee;

1. Selecting all records from the table:

mysql>select * from student;

Output:

Note: * means all fields

9|Page
2. Selecting specified columns from the table:
mysql>select no, name from student;
Output:

3. Changing column order in a query:

mysql>select name, no from student;

Output:

4. Eliminating redundant data (Distinct keyword):

mysql>select distinct stream from student;

Output:

No duplicate values will be displayed in stream field.

5. Selecting specific rows – Where clause:

We can specify a condition using where clause in a query and Result will be displayed as
after satisfying that condition.

Example 1:

mysql>select * from student where stream = 'Commerce';

Output:

10 | P a g e
It will display only those students’ records who are having Commerce stream.

Example 2:

mysql>select * from student where stipend >= 400;

Output:

We have following relational operators that can be used with where clause:

=, <, >, <=, >=, <>

Logical operators (And, Or & Not):

1. Display those students’ record for which marks is greater than or equal to 60 and
section is ‘A’.
mysql>select * from student where grade = 'A' && avgmarks >= 60;

Here both conditions must be true.

2. Display those students’ record for which grade is either ‘A’ or ‘B‘;
mysql>select * from student where grade = ‘A’ or grade = ‘B’;

11 | P a g e
 The above query can also be written as:
mysql>select * from student where not (grade = 'C');

Condition based on range (Between):

Display name and average marks of students where average marks in the range 50 – 90

mysql>select name, avgmark from student where avgmark between 50 and 90;

The range in above query is inclusive of 50 and 90.

Condition based on list of randomly selected values (In):

Example 1: Display records for roll numbers 1,5,8,10 only.

mysql>select * from student where no in(1,5,8,10);

Example 2: Display records for which stream is commerce, medical or nonmedical.

mysql>select * from student where stream in ('commerce', 'medical', 'nonmedical');

Condition based on pattern matches (like):

Example 1: Display records for which name is starting from letter ‘A’

mysql>select * from student where name like ‘A%’;

12 | P a g e
Example 2: Display records for which name is ending with letter ‘n’

mysql>select * from student where name like ‘%n’;

Example 3: Display names with exactly four characters in it.

mysql>select name from student where name like '____';

Arun
John

Here we have used the underscore sign four times. Each underscore represents a single
character.

This query can also be written as:

mysql>select name from student where length(name)=4;

length() is a single row function which will be discussed later.

Display sorted/arranged result (Order by):

Example 1: Display all records from student table in ascending order of marks.

mysql>select * from student order by avgmark;

Example 2: Display all records from student table in descending order of marks.

mysql>select * from student order by avgmark desc;

13 | P a g e
Note: By default the order is ascending order, for descending order we use desc
keyword at last.

We can also perform sorting on multiple fields

Example: Display records in ascending order of marks and descending order of rno.

mysql>select * from student order by marks, rno desc;

14 | P a g e