UNIT 3

DATABASE MANAGEMENT
Database Concepts
 A database (DB) is an organized collection of interrelated data
stored together to serve multiple application

 It is computer based record keeping system.

 It not only allows to store but also allows us modification of data

as per requirements.

 In other words, a database is used by an organization as a method of

storing, managing and retrieving information.

 Modern databases are managed using a database management system

(DBMS).

DBMS

 A DBMS refers to Database Management System

 It is software that is responsible for storing, manipulating,

maintaining and utilizing database.

 A database along with the DBMS is referred to as a database

system.

 There are various DBMS software available in the market like :-

1
  Oracle, MS SQL Server, MySQL, Sybase, PostgreSQL, SQLite

Purpose of Database Concepts

 Database systems reduce data redundancy to large extent.

 Database systems reduce data inconsistency to large extent.

 Databases facilitate sharing of data.

 Centralized databases can ensure data security.

 Integrity can be maintained through databases

Relational Data Model

 A Data models define how the logical structure of a database is

modeled.

 Data models define how data is connected to each other and how
they are processed and stored inside the system.

 Relational Model is the most widely used model.

 In this model, the data is maintained in the form of a two-

dimensional table.

 All the information is stored in the form of row and columns.

 The basic structure of a relational model is tables. So, the tables are
also called relations in the relational model. Example: In this
example, we have a Department Table.

2
 DEPTNO DNAME LOCATION

10 HR NEW YORK

20 ACCOUNTS BRAZIL

30 SALES CANADA

40 IT INDIA

General Terminology of Relational Data Model

 Concept of Domain

It defines the kind of data represented by the attribute. It is the

set of all possible permissible values that an attribute may contain.
For example in above department table, domain for the field DNAME
is HR, ACCOUNTS, SALES, IT.

 Relation/Table

In the Relational model the, relations are saved in the table format.
It is stored along with its entities. A table has two properties rows
and columns. Rows represent records and columns represent
attributes.

 Tuple

It is nothing but a single row of a table, which contains a single

record.

 Attribute

Each column in a Table. Attributes are the properties which define a

relation. e.g., Student_Rollno, NAME, etc.

 Degree

3
 The total number of attributes in the relation is called the degree
of the relation/table.

 Cardinality

Total number of rows present in the Relation/Table is called

Cardinality.

 Keys

 It helps you to identify any row of data in a table.

 In a real-world application, a table could contain thousands of
records. Moreover, the records could be duplicated.
 Keys ensure that you can uniquely identify a table record
despite these challenges.

 Primary Key

 It is an attribute or set of attributes which are used to identify

a tuple uniquely.
 The Primary Key can't be a duplicate meaning the same value
can't appear more than once in the table.
4
  A table cannot have more than one primary key
 It also forces mandatory entry (NOT NULL) i.e. we cannot leave
it blank.

 Candidate Key

 It is an attribute or set of attributes that can be used as a

primary key.
 Candidate Key is a super key with no repeated attributes. The
Primary key should be selected from the candidate keys.
 Every table must have at least a single candidate key.
 A table can have multiple candidate keys but only a single
primary key.

 Alternate Key

 Candidate key/keys which is/are not a primary key are known as

Alternate Key.
 A table can have multiple choices for a primary key but only one
can be set as the primary key. All the keys which are not
primary key are called Alternate Key.

5
 Foreign Key

 It is a non-key attribute whose value is derived from the Primary key of

another table.
 It is used to create relationship between two tables.
 Foreign key column will fill values from the values of Primary Key of
another table, if present then entry will be allowed otherwise data will be
rejected.
 Primary Key column table from where values will be derived is known as
Primary Table or Master Table or Parent Table.
 Foreign key column table will be Foreign Table or Child table.

EMPLOYEE TABLE
EMPNO ENAME GENDER DEPTNO SALARY COMM

1 ANKITA F 10 20000 1200

2 SUJEET M 20 24000 2000

3 VIJAYA F 10 28000 2000

4 NITIN M 30 18000 3000

5 VIKRAM M 30 22000 1700

DEPARTMENT TABLE

DEPTNO DNAME LOCATION

10 HR NEW YORK

20 ACCOUNTS BRAZIL

30 SALES CANADA

40 IT INDIA

From the Above table definition we can observe that the DEPTNO column
of EMPLOYEE table is deriving its values from DEPTNO of table
DEPARTMENT. So we can say that the DEPTNO of EMPLOYEE table is a
foreign key whose value is dependent upon the Primary key column
DEPTNO of table DEPARTMENT

6
 Quick Revision
Table- Customer

Acc_No Cust_Name Cust_City Cust_Phone Open_Bal

2101001 Sunita Ambala 9710557614 10000

2201002 Sandhya Patna 8223545233 15000

2301003 Vivek New Delhi 9972136576 13000

2401004 Meena New Delhi 9321305453 10000

Table- Transaction

Trans_Id Acc_No Transaction_Type Amount

Tr001 2301003 Credit 15000
Tr002 2201002 Credit 20000
Tr003 2101001 Debit 3500
Tr004 2301003 Credit 26000
Tr005 2301003 Credit 24000

1. Identify the candidate keys of Customer table.

2. Identify the primary key from the selected candidate keys.
3. Write degree and cardinality of Customer table.
4. If 3 rows and 2 new columns are added in this table than what
will be the impact on degree and cardinality of the table.
5. Which column can be considered as foreign key column in
Transaction table?
6. Identify Primary Key column of Transaction table.

7
Structured Query Language (SQL)

 It is a language that enables you to create and operate on relational

databases

 It is the standard language used by almost all the database

software vendors.
 Pronounced as SEQUEL
 Original version was developed by IBM’s Almanden Research Center

 Latest ISO standard of SQL was released in 2008 and named as

SQL:2008

SQL Features
 Allows creating/modifying a database’s structure

 Changing security settings for system

 Permitting users for working on databases or tables

 Querying database

 Inserting/modifying/deleting the database contents

8
Advantages of using SQL
 Ease of use
 No coding required
 Portable
 Not case sensitive
 Reliable
 Freedom of data abstraction
 Standard for database connectivity

My SQL
 MySQL is freely available open source RDBMS
 Can be downloaded from www.mysql.org
 In MySQL information is stored in Tables.
 Provides features that support secure environment for storing,
maintaining and accessing data.
 It is fast, reliable, scalable alternative to many of the commercial
RDBMS today.
 Create and supported by MySQL AB, a company based in Sweden.
This company is now subsidiary of Sun Microsystems. On April 2009
Oracle Corp acquires Sun Microsystems.
 The chief inventor of MySQL was Michael Widenius (a.k.a Monty).
MySQL has been named after Monty’s daughter My. The logo of
MySQL is dolphin and name of that dolphin is ‘Sakila’

Classification of SQL Statements

 DDL
 DML

9
Data Definition Language(DDL )
 DDL is abbreviation of Data Definition Language.
 It deals with the structure of the database.
 It is used to create, remove and modify the structure of database
objects in database. It provides following commands
 CREATE – Creates objects in the database
 ALTER – Alters objects of the database
 DROP – Deletes objects of the database

Data Manipulation Language(DML)

 DML is abbreviation of Data Manipulation Language.
 It deals with the data of the database.
 It is used to retrieve, store, modify, delete, insert and update data
in database. It provides following commands
 SELECT – Retrieves data from a table
 INSERT – Inserts data into a table
 UPDATE – Updates existing data into a table
 DELETE – Deletes all records from a table
Data Types
 Data types are means the type of value and type of operation we can
perform on data. For example on numeric value we can store numbers
and perform all arithmetic operations and so on.

 MySQL support main three categories of data types:

 Numeric
 Date and time
 String types

10
Numeric Data Types
 Data type  Description
 INT Numbers without decimal. Store up to 11 digits. -2147483648 to
/NUMBER 2147483647

 DECIMAL It is used to store exact numeric value that preserves exact precision
for e.g. money data in accounting system.
DECIMAL(P,D) means P no. of significant digits (1-65), D represent no.
of digit after decimal(0-30), for e.g DECIMAL(6,2) means 4 digit
before decimal and 2 digit after decimal. Max will be 9999.99

Date and YearTypes

 Data type  Description

 DATE A date in YYYY-MM-DD format between 1000-01-01 to 9999-12-31

In oracle data format is DD-MON-YYYY for e.g 10-SEP-2019

 YEAR(M) To store only year part of data where M may be 2 or 4 i.e. year in 2
digit like 18 or 4 digit like 2018

String Types
 Data type  Description

 CHAR(M)  Fixed length string between 1 and 255. it always occupy M size for
each data for example if size is CHAR(20) and we store value
‘MOBILE’ , although the size of MOBILE is 6 but in a table it will
occupy 20 size with space padded at right side for remaining place.
 Mostly use in the case where the data to be insert is of fixed size
like Grade (A,B,C,..) or Employee code as E001, E002, etc. In this
case CHAR will give better performance than varchar

 VARCHAR(M)  Variable length string between 1 and 65535 (from MySQL 5.0.3) ,
earlier it was 255. It takes size as per the data entered for
example with VARCHAR (20) if the data entered is MOBILE then it
will take only 6 byte. It is useful for the data like name, address

11
 where the number of character to be entered is not fixed.

 VARCHAR2  It is supported in ORACLE; both are almost same with minor

difference. The difference is in the way they are handling Empty
String and NULL, for VARCHAR these two are different where as
VARCHAR2 treats both same.

Quick Revision
Table- Library
Bid Name Author Price Mem_name Issue_Date Status
B01 Wings of Fire A.P.J 550 Sarita 2018-05-20 Returned
Abdul
Kalam

 Look at the table library and identify the correct datatype for
all the columns
 One Computer Shopkeeper wants to create table to store records
related to the material in his shop. Help him in identifying the
columns with their datatypes.

12
 SQL Commands

DDL Commands

TO SEE LIST OF DATABASES

show databases;

TO CREATE A DATABASE

create database mydb;

TO OPEN A DATABASE

use mydb;

TO DELETE A DATABASE

drop database mydb;

 TO CREATE A TABLE
CREATE TABLE is used to create a table in a database.
Syntax:
Create Table TableName
(ColumnName1 datatype(size) constraint,
13
 ColumnName2 datatype(size) constraint,…..);
Example:
Create Table Employee
(empno int primary key,
name varchar(20) not null,
dept varchar(20) unique,
salary int check(salary>=5000));

Different Constraints:
Primary Key
It ensures unique value in any column, also forces data
entry mandatory. Only one primary key can be applied
in one table
Unique
It also allows unique value in any column but it allows
NULL values and can be applied to n times
Not Null
It will make data entry mandatory for applied column
i.e. NULL will not be allowed
Default
It allows to specify any value which will be auto-
matically inserted in applied column if we not specify
applied column at the time of data entry using INSERT

CHECK
14
 It allows to specify range of values that can be
entered in applied column like salary must be greater
than 5000; marks must be greater than 0 or dept must
be in given list of values etc.

 TO MODIFY A TABLE
ALTER TABLE command is used to modify the
structure of the table. It performs the following
operations:
a) Adding new column in existing table
b) Dropping existing column from table
c) Modifying column definition in table
a) Adding new column in existing table
Syntax:
Alter Table TableName add
( ColumnName1 datatype(size) constraint);
Example:
Alter Table Employee add
( designation varchar(15) not null);
b) Dropping existing column from table
Syntax:
Alter Table TableName drop ColumnName;
Example:
Alter Table Employee drop salary;

15
 c) Modifying column definition in table
Syntax:
Alter Table TableName
modify (ColumnName datatype(size) ) ;
Example:
Alter Table Employee modify (designation varchar(20));

TO REMOVE A TABLE

DROP TABLE command is used to remove the
structure of the table.
Syntax:
Drop Table TableName ;
Example:
Drop Table Employee;

16
 Quick Revision
Table- Library
Bid Name Author Price Mem_name Issue_Date Status
B01 Wings of Fire A.P.J 550 Sarita 2018-05-20 Returned
Abdul
Kalam
Write the commands to perform following operations:
 Create the above table, set Bid as Primary key.
 Add a new column to store no of pages
 Modify the range of Author, increase it by 5 characters.
 Remove a column named
 Remove the column named price.

17
 SQL Operators

Relational Operators
SQL is providing various relational operators to apply
conditions in different commands. Such as
<, >, <= ,>=, <> (not equal to), =
Example :
 salary>2000
 dept =’Sales’
 rollno =121 etc.

Logical Operators
SQL is also providing various logical operators to merge
different relational expressions to apply conditions in
different commands. Such as
AND, OR, NOT
Example :
 salary>2000 and salary< 50000
 dept =’Sales’ or designation=’manager’ etc.
DML Commands

TO ADD RECORDS IN TABLE

Insert command is used to insert data into the table.
18
a) In selected columns.
Syntax:
Insert Into table_name (column1,column2,column3, ..)
VALUES(value1, value2, value3, ..);
Example :
Insert into Employee (empno, name, dept ) values
(2,’dipanker’,’IT’);

b) In all columns.
Syntax:
Insert Into table_name VALUES(value1, value2, value3, ..);
Example :
Insert into Employee values (2,’dipanker’,’IT’,20000);

TO CHANGE THE VALUES OF RECORDS

Update command is used to change the record of the
table. You can change all the rows or particular row
from the table. The Update command specifies the row
with where clause and new data is written into
respective record using set keyword.

a) Change all rows.

Syntax:
Update table_name set column1= newvalue ;
Example :
19
 Update Employee set dept= ‘Sales’;
or
Update Employee set salary = salary +2000;
b) Change specific rows as per condition
Syntax:
Update table_name set column1= newvalue where (condition)
;
Example :
Update Employee set dept= ‘Sales’ where empno =101;
or
Update Employee set salary = salary +2000 where salary <
15000;

TO DELETE RECORDS

Delete Command is used to delete all rows or particular
row from the table using where clause.
a) Delete all rows.
Syntax:
Delete from table_name ;
Example:
Delete from Employee;
b) Delete specific rows as per condition
Syntax:
Delete from table_name where (condition) ;
Example :
20
 Delete from Employee where empno =101;
or
Delete from Employee where salary >= 500 and salary <=1000;

Quick Revision
Table: PharmaDB
RxID DrugID DrugName Price PharmacyName Location
R1000 5476 Amlodipine 100.00 RxPharmacy Pitampura
R1001 2345 Paracetamol 15.00 RajMedicos Bahadurgarh
R1002 1236 Nebistar 60.00 MyChemist RajGarden
R1003 6512 VitaPlus 150.00 MyChemist Gurgaon
R1004 5631 Levocitrezine 110.00 RxPharmacy SouthExtension

Write command to perform the following operations:

1. Create the table PharmaDB
2. Add 5 rows in it as shown above.
3. Update the price of all drugs 50/-Rs.
4. Change the location Pitampura to Rohini
5. Delete the information of DrugID 5631
6. Remove the complete table.

By: Mrs Suman Gupta

21
22