1

DATABASE MANAGEMENT SYSTEMS

UNIT II: Relational Model & Basic SQL

Relational Model: Introduction to relational model, concepts

of domain, attribute, tuple, relation, importance of null values,
constraints (Domain, Key constraints, integrity constraints)
and their importance, Relational algebra, relational calculus.
BASIC SQL: Simple Database schema, data types, table
definitions (create, alter), different DML operations (insert,
delete, update)

2.1 Introduction
Relational Model was proposed by E.F Codd to model data in the form of
relations or tables. After designing the conceptual model of database using
ER diagram, we need to convert the conceptual model in the relational
model which can be implemented using any RDBMS (Relational Data Base
Management System) like SQL, MY SQL etc.

The relational model is very simple and elegant; a database is a collection

of one or more relations, where each relation is a table with rows and
columns.

This simple tabular representation enables even new users to understand

the contents of a database, and it permits the use of simple, high-level
languages to query the data.

2.2 Relational Model

Relational Model represents how date is stored in relational databases.
A Relational database stores data in the form of relations (tables).
Consider a relation STUDENT with attributes ROLL_NO, NAME, ADDRESS,
PHONE and AGE as shown in table.

ROLL_NO NAME ADDRESS PHONE AGE

1 Nishma Hyderabad 9455123451 28

2 Sai Guntur 9652431843 27

3 Swetha Nellore 9156253131 26

4 Raji Ongole 9215635311 25

Attribute: Attributes are the properties that define a relation.
Ex: ROLL_NO, NAME

Tuple: Each row in a relation is known as tuple.

Ex:
1 Nishma Hyderabad 9455123451 28
 2

Degree: The number of attributes in the relation is known as degree.

Ex: The degree of the given STUDENT table is 5.
Column: Column represent the set of values for a particular attribute.
The column ROLL_NO is extracted from the relation STUDENT.
Ex:
ROLL_NO

Null values: The value which is not known or unavailable is called NULL
VALUE. It is represented by blank space.
Cardinality: The number of tuples are present in the relation is called as
its cardinality.
Ex: The Cardinality of the STUDENT table is 4.

2.3 Concept Of Domain

The domain of a database is the set of all allowable values (or)
attributes of the database. Ex: Gender (Male, Female, Others).

Relation
 A relation is defined as a set of tuples and attributes.
 A relation consists of Relation schema and relation instance.
 Relation schema: A relation schema represents the name of the
relation with its attributes.
Ex: STUDENT (ROLL_NO, NAME, ADDRESS, PHONE and AGE) is Relation
schema for STUDENT.
 Relation instance: The set of tuples of a relation at a particular
instance of a time is called Relation Instance.
An instance of „Employee „relation
Emp_code Emp_Name Dept_Name
01234 John HR

12567 Smith Sales

21678 Sai Production

12456 Jay Design

2.4 Importance of Null values:
 SQL supports a special value known as NULL which is used to
represent the values of attributes that may be unknown or not apply
to a tuple.
 For example, the apartment_number attribute of an address applies
only to the address that is in apartment buildings and not to other
types of residences.
 It is important to understand that a NULL value is different from Zero
value.
 A Null value is used to represent a missing value, but that is usually
has one of the following interpretations:
 3

• Value unknown (Value exists but it is unknown)

• Value not available (exists but it is purposely withheld)
• Attribute not applicable (undefined for this tuple)
 It is often not possible to determine which of the meanings is
intended.

2.5 Constraints
 On modeling the design of the relational data base, we can put some
rules(conditions) like what values are allowed to be inserted in the
relation
 Constraints are the rules enforced on the data columns of a table.
These are used to limit the type of data that can go in to a table
 This Ensure the accuracy and reliability of the data in the database.
Constraints could be either on a column level on a table level.

2.6 Domain Constraints In DBMS

 In DBMS table is viewed as a combination of rows and columns
 For example, if you are having a column called month and you want
only (jan, feb, march……) as values allowed to be entered for that
particular column which is referred to as domain for that particular
column
Definition: Domain constraint ensures two things it makes sure that the
data value entered for that particular column matches with the data
type defined by that column
It shows that the constraints (NOT NULL/UNIQUE/PRIMARY
KEY/FOREIGN KEY/CHECK/DEFAULT)

Domain constraint= data type check for the column +constraints.

Example:, we want to create a table “STUDENT” with “stu_id” field
having a value greater than 100, can create a domain and table like
this.

 Create domain id_value int constraint id_test check

(value>=100);
 CREATE table STUDENT (stu_id id value primary key, stu_name
varchar
(30), stu_age int);
 4

2.7 Key constraints in DBMS:

 Constraints are nothing but the rules that are to be followed while entering data
into columns of the database table.
 Constraints ensure that the data entered by the user into columns must be
within the criteria specified by the condition.
 We have 6 types of key constraints in DBMS
1. Not Null
2. Unique
3. Default
4. Check
5. Primary key
6. Foreign key

1. Not Null:
• Null represents a record where data may be missing data or data for that
record may be optional.
• Once not null is applied to a particular column, you cannot enter null values
to that column.  A not null constraint cannot be applied at table level.

Example:

Create table EMPLOYEE (id int Not null, name varchar Not null,
Age int not null, address char (25), salary decimal (18,2),
primary key(id));

 In the above example we have applied not null on three columns id,
name and age which means whenever a record is entered using insert
statement all three columns should contain a value other than null.
 We have two other columns address and salary, where not null is not
applied which means that you can leave the row as empty.

2. Unique:

Some times we need to maintain only. Unique data in the column of a

database table, this is possible by using a Unique constraint.
 5

 Unique constraint ensures that all values in a column are Unique.

Example:

Create table PERSONS (id int unique, last_name varchar (25) not null,
First name varchar (25), age int);

 In the above example, as we have used unique constraint on ID column

we are not supposed to enter the data that is already present, simply
no two ID values are same.

3. Default:

Default in SQL is used to add default data to the columns.

 When a column is specified as default with same value then all the
rows will use the same value i.e., each and every time while entering
the data we need not enter that value.
 But default column value can be customised i.e., it can be over ridden
when inserting a data for that row based on the requirement.

(Row with default values “abc”)

Example:

Create table EMPLOYEE (id int Not null, last_name varchar (25) Not
null, first_name varchar (25), Age int, city varchar (25) Default
Hyderabad);

 As a result, whenever you insert a new row each time you need not
enter a value for this default column that is entering a column value
for a default column is optional.
 6

4. Check:
 Check constraint ensures that the data entered by the user for that
column is within the range of values or possible values specified.

Example: Create table STUDENT (id int, name varchar (25), age int,
check(age>=18));

 As we have used a check constraint as (age>=18) which means value

entered by user for this age column while inserting the data must be
less than or equal to 18.

5. Primary Key:
 A primary key is a constraint in a table which uniquely identifies each
row record in a database table by enabling one or more column in the
table as primary key.

Creating a primary key:

 A particular column is made as a primary key column by using the

primary key keyword followed by the column name. Example:
Create table EMP (ID int, name varchar (20), age int, course
varchar
(10), Primary key (ID));

 Here we have used the primary key on ID column then ID column

must contain unique values i.e., one ID cannot be used for another
student.

6. Foreign Key:
 The foreign key constraint is a column or list of columns which points
to the primary key column of another table.
 7

 The main purpose of the foreign key is only those values are allowed
in the present table that will match to the primary key column of
another table.

From the above two tables, COURSE_ID is a primary key of the table
STUDENT_MARKS and also behaves as a foreign key as it is same in
STUDENT_DETAILS and STUDENT_MARKS.
Example:

(Reference Table)

Create table CUSTOMER1 (id int, name varchar (25), course varchar
(10), primary key (ID));

(Child table)

Create table CUSTOMER2 (id int, marks int, references

customer1(ID));

2.8 Integrity Constraints in DBMS:

 There are two types of integrity constraints
1. Entity Integrity Constraints
2. Referential Integrity Constraints

Entity Integrity constraints:

 These constraints are used to ensure the uniqueness of each record or
row in the data table.

 Entity Integrity constraints says that no primary key can take NULL
VALUE, since using primary key we identify each tuple uniquely in a

relation.

Example:
 8

Explanation:

 In the above relation, EID is made primary key, and the primary key
can‟t take NULL values but in the 3rd tuple, the primary key is NULL, so
it is violating Entity integrity constraints.

Referential Integrity constraints:

 The referential integrity constraint is specified between two relations
or tables and used to maintain the consistency among the tuples in
two relations.

 This constraint is enforced through foreign key, when an attribute in

the foreign key of relation R1 have the same domain as primary key of
relation R2, then the foreign key of R1 is said to reference or refer to
the primary key of relation R2.
 The values of the foreign key in a tuple of relation R1 can either take
the values of the primary key for some tuple in Relation R2, or can
take NULL values, but can‟t be empty.

Explanation:

 In the above, DNO of the first relation is the foreign key and DNO in
the second relation is the primary key
 9

 DNO=22 in the foreign key of the first relation is not available in the
second relation so, since DNO=22 is not defined in the primary key of
the second relation therefore Referential integrity constraints is
violated here.

2.9 Basic SQL (introduction)

 SQL stands for Structure Query Language it is used for storing and
managing data in relational database management system.
 It is standard language for relational database system. It enables a
user to create, read, update and delete relational databases and
tables.
 All the RDBMS like MYSQL, Oracle, MA access and SQL Server use SQL
as their standard database language.
 SQL allows users to Query the database in a number of ways using
statements like common English.
Rules: SQL follows following rules

• SQL is not a case sensitive. Generally, keywords are

represented in UPPERCASE.
• Using the SQL statements, you can perform most of the
actions in a database.
• Statements of SQL are dependent on text lines. We can
use a single SQL statement on one or multiple text line.

2.10 SQL Process:

 When an SQL command is executing for any RDBMS, then the system
figure out the best way to carry out the request and the sql engine
determines that how to interrupt the task.
 In the process, various components are included. These components
can be optimization engine, query engine, query dispatcher etc.,

 All the non-sql queries are handled by the classic query engine, but sql
query engine won‟t handle logical files.

2.11 Characteristics of SQL:

• SQL is easy to learn.
• SQL is used to access data from relational database
management system.
• SQL is used to describe the data.
• SQL is used to create and drop the database and table.
• SQL allows users to set permissions on tables,

procedures and views. 2.12 Simple database Schema:

 A database schema is a structure that represents the logical storage
of the data in the database.
 It represents the organization of data and provides information about
the relationships between the tables in a given database.
 A database schema is the logical representation of a database, which
shows how the data is stored logically in the entire database.
 10

 It contains list of attributes and instruction that informs the database

engine that how the data is organized and how the elements are
related to each other.
 A database schema contains schema objects that may include tables,
fields, packages, views, relationship, primary key, foreign key.
 In actual, the data is physically stored in files that may be in
unstructured form, but to retrieve it and use it, we need to keep them
in a structured manner. To do this a database schema is used. It
provides knowledge about how the data is organized in a database
and how it is associated with other data.
 A database schema object includes the following:
• Consistent formatting for all data entries.
• Database objects and unique keys for all data entries.
• Tables with multiple columns, and each column contains its
names and datatypes.
• The given diagram is an example of a database schema it
contains three tables, their data types. This also represents the
relationships between the tables and primary keys as well as
foreign keys.

2.13 SQL Commands:

SQL commands are categorized into three types.

1. Data Definition Language (DDL): used to create (define) a table.

2. Data Manipulation Language (DML): used to update, store and

retrieve data from tables.

3. Data Control Language (DCL): used to control the access of

database created using DDL and DML.
 11

2.14 SQL DATATYPES :

SQL data type is used to define the values that a

column can contain Every column is required to have a

name and data type in the database table.

DATA TYPES OF SQL :

SQL DATA
TYPES

Binary data Numeric data Extract String data Date data type
type type numeric data type
type

1. BINARY DATATYPES:
There are three types of binary data types which are given below

DATA TYPE DESCRIPTION

Binary It has a maximum length of 800 bytes. It contains a

fixed- length binary data
Var binary It has a maximum length of 800 bytes. It contains a
variable - length binary data
Image It has a maximum length of 2,147,483,647 bytes. It
contains a variable - length binary data

2. NUMERIC DATATYPE:
 12

DATA TYPE FROM TO DESCRIPTION

Float -1.79 E 1.79 E It is used to specify a floating-point

+308 +308 value.
Ex: 6.2, 2.9 etc
Real -3.40 E 3.40 E It specifies a single precision floating
+38 +38 point number.

3. EXACT NUMERIC DATA TYPE:

DATA TYPE DESCCRIPTION

Int It is used to specify an integer value

Small int It is used to specify small integer value
Bit It has the number of bits to store
Decimal It specifies a numeric value that can have a decimal
number
Numeric It is used to specify a numeric value

4. DATE AND TIME DATATYPES:

DATA TYPE DESCRIPTION
Date It is used to store the year, month, and days value
Time It is used to store the hour, minute, and seconds value
Time stamp It stores the year, month, hour, minute, and the second
value

5. STRING DATATYPE:
DATA TYPE DESCRIPTION
Char It has a maximum length of 8000 characters. It contains fixed-
length nonUnicode characters.
Varchar It has a maximum length of 8000 characters. It contains variable-
length non-Unicode characters.
Text It has a maximum length of 2,147,483,647 characters. It contains
variablelength non-Unicode characters.

2.15 TABLE DEFINITIONS: (CREATE, ALTER)

SQL TABLE: SQL table is a collection of data which is organized in terms of
rows and columns.

• In DBMS, the table is known as relation and row as a tuple

• Let‟s see an example of the “EMPLOYEE “table

EMP_ID EMP_NAME CITY PHONE_ID
1 Kristen Washington 7289201223
2 Anna Franklin 9378282882
3 Jackson California 9264783838
4 Daniel Hawaii 9638482678
 13

• In the above table, “EMPLOYEE” is the table name, “EMP_ID,

“EMP_NAME”, “CITY”,” PHONE-NO” are the column names.
• The combination of data of multiple columns forms a row
EG: 1, “Kristen”, “Washington” and “7289201223 “are the data of one row

2.16 OPERATIONS ON TABLE:

1. Create table
2. Alter table
3. Drop table

[Link] table: SQL create table is used to create a table in the

database. To define the table, you should define the name of the table and
also define its column and column‟s data type.

SYNTAX:

Create table table_name (“column1” “datatype”,

“column2” “datatype”,

“column3” “datatype”,

….

“column N” “datatype”);

EXAMPLE:

SQL > create table employee (emp_id int, emp_name varchar (25), phone_no
int, address char (30));

 If you create the table successfully, you can verify the table by looking
at the message by the sql server. else you can use DESC command as
follows

SQL > DESC employee;

FIELD TYPE NULL DEFAULT EXTRA
Emp_id Int (11) No NULL
Emp_name Varchar (25) No NULL
Phone_no No Int (11) NULL
address yes NULL Char(30)

2. ALTER TABLE:

• The alter table command adds, delete or modifies columns in a

table
• The alter table command also adds and deletes various
constraints in a table  The following SQL adds an “EMAIL”
column to the “EMPLOYEE “table
SYNTAX:

ALTER table table_name add column1

datatype; EXAMPLE:
 14

ALTER table employee add email varchar (255);

SQL > DESC employee;

FIELD TYPE NULL DEFAULT EXTRA
Emp_id Int (11) No NULL
Emp_name Varchar (25) No NULL
Phone_no No Int (11) NULL
Address Yes NULL Char (30)
Email Varchar (255) NULL

3. DROP TABLE:

• The drop table command deletes a table in the data

base  The following example SQL deletes the
table “EMPLOYEE”
SYNTAX :

DROP table table_name;

EXAMPLE:

DROP table employee;

• Dropping a table results in loss of all information

stored in the table.

2.17 Different DML Operations (insert, delete, update):

 DML-Data Manipulation Language.
 Data Manipulation Commands are used to manipulate data to the
database.
 Some of the data manipulation commands are
1. Insert
2. Update
3. Delete

1. Insert:
SQL insert statement is a sql query. It is used to insert a single multiple
records in a table.

Syntax:
Insert into table name values (value 1, value 2, value 3);

Let‟s take an example of table which has

3 records within it. ▪ insert into student

values(„alekhya‟,501,‟hyderabad‟);
 15

▪
insert into student

values(„deepti‟,502,‟guntur‟); ▪
insert into student
values(„ramya‟,503,‟nellore‟); The
following table will be as follows:
NAME ID CITY
Alekhya 501 Hyderabad
Deepti 502 Guntur
Ramya 503 Nellore
2. Update:
 The SQL Commands update are used to modify the data that is
already in the database.
 SQL Update statement is used to change the data of records held by
tables which rows is to be update, it is decided by condition to
specify condition, we use “WHERE” clause. Ø The update statement
can be written in following form:

Syntax:
Update table_name set column_name=expression where condition;

Example:

Let‟s take an example: here we are going to update an entry in the table.
Update students set name=‟rasi‟ where id=503;

After update the table is as follows:

NAME ID CITY
Alekhya 501 Hyderabad
Deepti 502 Guntur
Rasi 503 Nellore
3. Delete:
 The SQL delete statement is used to delete rows from a table.
 Generally, delete statement removes one or more records from a
table.

Syntax:
delete from table_name [where condition];

Example:

Let us take a table named “student” table

Delete from students where id=501; Resulting table after the query:

NAME ID CITY
Deepti 502 Guntur
Rasi 503 Nellore