UNIT 2

DATABASE MANAGEMENT SYSTEM

Data:

Basic/raw facts about something which is not organized, for example details of some students
which is not organized.
Raw facts about something is data.

Data Item/information:

Formatted data of any entity is information.

Database:

A well-organized collection of interrelated data that ensures safety, security and integrity of data
is called database.

DataBase Management System (DBMS)

Software that provides the essential services to create, manage and maintain the databases is
called as DBMS.
In short a DBMS provides the means to store the data in the database, to edit or delete the data
stored, to search and analyze the data in the database. They also provide various safety and
security mechanisms that ensures that in any case stored data will be safe and accessible.Systems
comprising of Databases and Database Management Systems are simply referred as database
systems.
Advantages of Data Base System:
• Reduce data redundancy (duplication of data)
• Control data inconsistency to a large extent
• Database facilitate sharing of data
• Enforce standards
• databases can ensure data security

Examples of Common Database Management Systems:

MySQL, INGRES, POSTGRES, ORACLE, DB2, Microsoft Access. Levels of Database
Relational DataBase Management System (RDBMS):
In a RDBMS, the data is stored into tables (i.e. Rows and Columns). These tables are called
Relations.
Since table is a collection of relationships it is generally referred to using the mathematical term
Relation. Oracle, DB2, AS400, MYSQL
 COACH
PCode Name ACode
1 Ahmad Hussain 1001
2 Ravinder 1008
3 Janila 1001
4 Naaz 1003

Relational Database terminology

Relation: A table stored in a database is also Relation.
Tuple : A row in a relation/table is called a tuple(row/record)
Attribute : A column in a relation is called an attribute(column/field)
Domain : Domain of an attribute refers to the set of all the possible values for that attribute.
Degree : Number of attributes in a relation is the degree of that relation
Cardinality : Number of tuples in a relation is the cardinality of that relation.

Candidate Key: A set of one or more minimal attributes used to uniquely identify a tuple in the
relation and which can act as Primary Key. A relation can have multiple candidate keys
Primary Key: A candidate key that is primarily chosen for unique identification of tuples in a
Relation. Any subset of Primary key should not be Primary key.
Alternate Key: Candidate keys that not chosen as primary key are the alternate keys.
Example: In A LIBRARY Table
* Candidate keys can be Accession No, Book No
* Primary key: If we select Book No as primary key for our purpose then Alternate Key will be
Accession No.
Views : A view is a virtual table whose contents are taking from other tables depending upon a
condition.
BASICS OF RELATIONAL MODEL RELATION :
• Data is stored in a relational database in one or more tables. These tables termed as relation.
Atomicity : At every row-column intersection (Cell) there must be an atomic value i.e. a value
that can not be further subdivided.
No duplicity: No two rows of relation will be identical i.e. in any two rows value in at least one
column must be different.
• Ordering of rows is immaterial.
• Ordering of columns is immaterial.

KEYS:
Key plays an important role in relational database; it is used for identifying unique rows from
table. It also establishes relationship among tables.

For example: In Student table, ID is used as a key because it is unique for each student. In
PERSON table, passport_number, license_number, SSN are keys since they are unique for each
person.

Types of key:

1. Candidate Key
2. Primary Key
3. Alternate key
 4. Foreign Key

1. Candidate Key

o A candidate key is an attribute or set of an attribute which can uniquely identify a tuple.
o The remaining attributes except for primary key are considered as a candidate key. The
candidate keys are as strong as the primary key.

For example: In the EMPLOYEE table, id is best suited for the primary key. Rest of the
attributes like SSN, Passport_Number, and License_Number, etc. are considered as a candidate
key.

2. Primary key

o It is the first key which is used to identify one and only one instance of an entity
uniquely. An entity can contain multiple keys as we saw in PERSON table. The key
which is most suitable from those lists become a primary key.
o In the EMPLOYEE table, ID can be primary key since it is unique for each employee. In
the EMPLOYEE table, we can even select License_Number and Passport_Number as
primary key since they are also unique.
 o For each entity, selection of the primary key is based on requirement and developers.

2. Alternate key:

o The remaining attributes except for primary key are considered as a alternate key. The
alternate keys are as strong as the primary key.
o In employee table Employee_ID is primary key so passport_number, Licence number and
SSN are alternate keys.
3. Foreign key

o Foreign keys are the column of the table which is used to point to the primary key of
another table.
o In a company, every employee works in a specific department, and employee and
department are two different entities. So we can't store the information of the department
in the employee table. That's why we link these two tables through the primary key of
one table.
o We add the primary key of the DEPARTMENT table, Department_Id as a new attribute
in the EMPLOYEE table.
o Now in the EMPLOYEE table, Department_Id is the foreign key, and both the tables are
related.

DATA TYPES IN MySQL

Data types in mysql:

Class Data Type Description Format Example

CHAR(size)
A fixed-length string between 1 CHAR(size) ‘COMPUTE
Text and 255 characters in length R’
right-padded with spaces to the ‘CBSE’
specified length when stored.
Values must be enclosed in
single quotes or double quotes.
VARCHAR(size) A variable-length string between VARCHAR ‘SCIENCE’
1 and 255 characters (size) ‘Informatics’
in length; for example
VARCHAR(20).
NUMERIC DECIMAL(p,s) It can represent number with or Number(p,s) 58.63
without the fractional part. The
size argument has two parts :
precision and scale. Precision
(p) indicates the number of
significant digits and scale
(s)maximum number of digits to
the right of the decimal point.

INT It is used for storing integer INT 164

Values
Date DATE It represents the date including YYYY-MM- 2014-08-27
day, month and year between DD
1000-01-01 and 9999-12-31

Hh:mm:ss
Difference between CHAR and VARCHAR

The CHAR data-type stores fixed length strings such that strings having length
smaller than the field size are padded on the right with spaces before being stored.
The VARCHAR on the other hand supports variable length strings and therefore
stores strings smaller than the field size without modification.
SQL Constraints/ Integrity Constraints
1-SQL Constraint is a condition or check applicable on a field
or set of fields. 2- They can also be defined or modified after
creating the tables.
3- When constraints are defined any data entering in the table is first checked to
satisfy the condition specified in particular constraint if it is, only then table data
set can be updated. If data updation/ insertion is violating the defined constraints,
database rejects the data (entire record is rejected).
4- When a constraint is applied to a single column, it is called a column level
constraint but if a constraint is applied on a combination of columns it is called a
table constraint.
5- Following constraints can be defined on a table in SQL:
Constraints name Description

PRIMARY KEY Used to create a primary key(NULL, DUPLICATE)

UNIQUE to create a unique key(DUPLICATE)
NOT NULL to define that column will not accept null
values.(NULL)
FOREIGN KEY/ REFERENCES to define referential integrity with another table.

DEFAULT to define the columns default value.

CHECK to define the custom rule.

Not Null and Default constraints can be applied only at column level rest all
constraints can be applied on both column level and table levels.

■ Accessing Database in MySql :

Through USE keyword we can start any database Syntax:
USE DATABASE NAME;
EXAMPLE IF DATA BASE NAME IS SCHOOL:
USE SCHOOL;

■ CREATING TABLE IN MYSQL

Through Create table command we can
CREATE TABLE TABLENAME(
FIELDNAME DATATYPE(SIZE)
CONSTRAINTS,……)

■ INSERTING DATA INTO TABLE

The rows are added to relations using INSERT
command.
INSERT INTO tablename(columnname,
columnname………..)
VALUES (value, value ........... );

INSERT INTO
ADDRESS (SNo, City)
VALUES
(100,’JAIPUR’);

Insert into student

values (values of filed
to be inserted…..);
■ SELECT COMMAND:
The SELECT command is used to make queries on the database. A query is a
command that is given to produce certain specified information from the database
table(s). The SELECT command can be used to retrieve a subset of rows or
columns from one or more tables. The syntax of Select Command is:
SELECTColumn-list>
FROM <table_name>
Where <condition>
ORDER BY <column_list (in case of decending DESC);

Example:
SELECT * FROM ADDRESS WHERE SNo=100;

■ Eliminating Redundant or duplicate Data

■ DISTINCT keyword eliminates
redundant data
■ SELECT DISTINCT City FROM
ADDRESS;

■ Selecting from all the rows

SELECT * FROM ADDRESS;

■ Viewing structure of table:

DESCRIBE/DESC <tablename>;
DESCRIBE ADDRESS;
DECS ADDRESS;

■ Condition based on a range:

Keyword BETWEEN used for making range checks in queries.
SELECT SNo, CITY FROM ADDRESS WHERE SNo BETWEEN 10 AND 20;

■ Condition based on a list:

Keyword IN used for selecting values from a list of
values. SELECT rno, sname FROM student
WHERE rno IN (10, 20, 60);

■ Condition based on a pattern matches:

Keyword LIKE used for making character comparison using
strings percent(%) matches any substring underscore(_) matches
any character
SELECT SNo, City FROM ADDRESS WHERE City LIKE ‘%ri’;

■ Searching for NULL

The NULL value in a column is searched for in a table using IS NULL in the
WHERE clause (Relational Operators like =,<> etc cannot be used with NULL).
For example, to list details of all employees whose departments contain NULL
(i.e., novalue), you use the command:
SELECT
empno,
ename
FROM emp
Where Deptno IS NULL;

■ ORDER BY clause:
It is used to sort the results of a query.
SELECT <column name> [, <column
name>, .] FROM <table name>
[WHERE <condition>] [ORDER BY <column name>];
SELECT * FROM ADDRESS WHERE SNo>50 ORDER BY City;

■ SQL Constraint:
A Constraint is a condition or check applicable on a field or set of fields.
■ NOT NULL/UNIQUE/DEFAULT/CHECK/PRIMARY KEY/FOREIGN KEY
Constraint: CREATE TABLE student (rollno integer NOT NULL );

CREATE TABLE student (rollno integer UNIQUE );

CREATE TABLE student (rollno integer NOT NULL, Sclass integer, Sname
varchar(30), Sclass DEFAULT 12 );

CREATE TABLE student (rollno integer CHECK (rollno>0), Sclass integer,

Sname varchar(30));

CREATE TABLE student (rollno integer NOT NULL PRIMARY KEY,

Sclass integer, Sname varchar(30));

CREATE TABLE teacher (Tid integer NOT NULL, FOREIGN KEY

(Studentid ) REFRENCES student (Sid));

■ Modifying data in tables:

Existing data in tables can be changed with UPDATE command.
The Update command is use to change the value in a table. The syntax of this
command is: UPDATE <table_name>
SET column_name1=new_value1
[,column_name2=new_value2,……] WHERE <condition>;

UPDATE student SET Sclass=12 WHERE Sname=’Rohan’;

■ Deleting data from tables:

The DELETE command removes rows from a table. This removes the entire
rows, not individual field values. The syntax of this command is
DELETE FROM
 <table_name>
[WHERE
<condition>];
e.g., to delete the tuples from EMP that have salary less than 2000, the
following command is used:
DELETE FROM emp WHERE sal<2000;
To delete all tuples from emp table:
DELETE FROM emp;

MySQL functions:
A function is a special type of predefined command set that performs some
operation and returns a single value.
Single-row functions return a single result row for every row of a queried table.
They are categorized into: Numeric functions, String functions, and Date and
Time functions.
1) Numeric Functions
• POWER( ) : Returns the argument raised to the specified power. POW ( )
works the same way.
Example: (i) POW(2,4); Result:16 (ii) POW(2,-2); Result:0.25 (iii) POW(-2,3) Result: -8

• ROUND( ) : ROUND(X) Rounds the argument to the zero decimal

place, Where as ROUND(X,d) Rounds the argument to d decimal places.
Example :(i) ROUND(-1.23); Result: -1 (ii) ROUND(-1.58); Result: -2
(iii) ROUND(1.58); Result: 2 (iv)ROUND(3.798,
1);Result: 3.8 (v) ROUND(1.298, 0); Result: 1
(vi)
ROUND(23.298, -1); Result: 20

• TRUNCATE( ) : Truncates the argument to specified number of

decimal places. Example: (i) TRUNCATE(7.29,1 )Result: 7.2 (ii)
TRUNCATE(27.29,-1) Result: 20

2) Character/String Functions
• LENGTH( ) : Returns the length of a string in bytes/no.of
characters in string. Example: LENGTH(‘INFORMATICS’); Result:11

• CHAR( ) : Returns the corresponding ASCII character for each

integer passed. Example : CHAR(65) ; Result : A
• CONCAT( ): Returns concatenated string i.e. it adds strings.
Example : CONCAT(‘Informatics’,’ ‘,‘Practices’); Result : Informatics Practices

• INSTR( ): Returns the index of the first

occurrence of substring. Example :
INSTR(‘Informatics’,’ mat’);
Result : 6(since ‘m’ of ‘mat’ is at 6th place)
• LOWER( )/ LCASE( ): Returns the argument after converting it
in lowercase. Example: LOWER(‘INFORMATICS’); Result :
informatics

• UPPER( )/ UCASE( ): Returns the argument after converting it

in uppercase. Example:
UCASE(‘informatics’); Result
:INFORMATICS
• LEFT( ) : Returns the given number of characters by extracting
them from the left side of the given string.
Example : LEFT(‘INFORMATICS PRACTICES’, 3); Result : INF
• MID( )/SUBSTR( ) : Returns a substring starting from the specified
position in a given string.
Example: MID(‘INFORMATICS PRACTICES’,3,4); Result : FORM

• LTRIM( ) : Removes leading spaces.

Example : LTRIM(' INFORMATICS'); Result: 'INFORMATICS’

• RTRIM( ): Removes trailing spaces.

Example : RTRIM('INFORMATICS '); Result: 'INFORMATICS’

• TRIM( ) : Removes leading and trailing spaces.

Example: TRIM(' INFORMATICS '); Result: 'INFORMATICS’

3) Date/Time Functions
• CURDATE( ) : Returns the current date
Example: CURDATE( ); Result:'2014-07-21'

• NOW( ): Returns the current date and time

Example: NOW( ); Result: '2014-07-21 13:58:11'

• SYSDATE( ) : Return the time at which the

function executes Example:
SYSDATE( ); Result:'2014-
07-21 13:59:23’

• DATE( ): Extracts the date part of a date or date

time expression Example: DATE('2003-12-31
01:02:03'); Result::'2003-12-31'

• MONTH( ) :Returns the month from the date passed

Example: MONTH('2010-07-21'); Result: 7
• YEAR( ): Returns the year
Example: YEAR('2010-07-21'); Result: 2010
 • DAYNAME( ): Returns the
name of the weekday Example: DAYNAME('2010-07-21');
Result: WEDNESDAY

• DAYOFMONTH( ): Returns the day of the month (0-31)

Example: DAYOFMONTH('2010-07-21'); Result: 21

• DAYOFWEEK( ): Returns the weekday index of the argument

Example: DAYOFWEEK('2010-07-21'); Result: 4 (Sunday is counted as
1)

• DAYOFYEAR( ): Return the day of

the year(1 -366) Example: DAYOFYEAR('2010-07-21'); Result: 202

• Aggregate or Group functions: MySQL provides Aggregate or Group functions which

work on a number of values of a column/expression and return a single value as the
result.
Some of the most frequently used Aggregate functions in MySQL are:
S.No Name of the Function Purpose

1 MAX() Returns the MAXIMUM of the values under the

specified column/expression.
2 MIN() Returns the MINIMUM of the values under the
specified column/expression.
3 AVG() Returns the AVERAGE of the values under the
specified column/expression.
4 SUM() Returns the SUM of the values under the specified
column/expression.

5 COUNT() Returns the COUNT of the number of values under the

specified column/expression.

■ The GROUP BY clause groups the rows in the result by columns that have
the same values. Grouping can be done by column name, or with aggregate
functions in which case the aggregate produces a value for each group.
■ The HAVING clause place conditions on groups in contrast to WHERE clause
that place conditions on individual rows. While WHERE condition cannot
include aggregate functions, HAVING conditions can do so.
■ ALTER TABLE COMMAND:-
The ALTER Table command is used to change the definition (structure) of
existing table. Usually , it can:
(i) Add columns to a table
(ii) Delete columns
(iii) Modi
fy a column
The syntax of
this command
is:
For Add or modify column:
ALTER TABLE <Table_name> ADD/MODIFY
<Column_defnition>; For Delete column
ALTER TABLE <Table_name> DROP COLUMN <Column_name>;
Example :
 To add a new column address in EMP table
command will be : ALTER TABLE EMP ADD
(address char (30));
 To modify the size of sal column in EMP table,
command will be: ALTER TABLE EMP MODIFY
(sal number(9,2) );
 To delete column Address from Table EMP the
command will be: ALTER TABLE EMP DROP
COLUMN address;