UNIT-3

DATABASE MANAGEMENT
SYSTEMS
*Data and Information
✔ Data refers to the collection of
facts and figures
✔ When processing is performed on
the data, it gets transformed into
information.
✔ Data----🡪processing----🡪Information
✔ Eg.15--🡪mark/Age/Rollno
*Database
✔ A database is an organized
collection of inter-related data.
✔ We can visualize it as a container
of information
✔ A database allows us to retrieve
the data and modification of the
data for controlling the operation.
*Database Management Systems
✔ A DBMS is a software package with
computer programs that control the
creation, maintenance, and use of a
database.
✔ A database is an integrated collection of
data records, files and other objects.
✔ It supports concurrency
✔ Examples: Oracle, MySql, Microsoft
Access, PostgreSQL, Foxpro etc
*Data can be organized into
two types:
1.Flat File:Data is stored in a single
table. Usually suitable for less amount
of data
2.Relational: Data is stored in multiple
tables and the tables are linked using
a common field. It is suitable for
medium to large amount of data.
DATABASE SERVER
IMPORTANT TERMS
 SESSION-2
CREATE
AND
EDIT TABLE

Data in a table is organized

in the form of tables.
DATABASE OBJECTS
1.A Table: A table is a set of data elements (values) that is
organized using a model of vertical columns and horizontal
rows.

2.Columns(or)Attributes: A Column is a set of data values

of a particular simple type , one for each row of the
table.

3.Rows (or) Tuples: A row represents a single data item in

a table. Every row in a table has the same structure.
CREATING DATABASE USING
OPEN OFFICE
CREATE TABLE

Two ways to create a table

a)Use wizard to create table
b)Creating table using Design View
Use wizard to create table
Creating table using Design
View
Data Types
Data types are used to identify which type of data are
going to store in the database.

It is classified into five categories

1. Numeric Types
2. Alphanumeric Types
3.Binary Types
4.Date Time
5.Other variable types
Numeric types
Alphanumeric types
Binary Types
✔ They are stored for using data in binary
formats.
✔ It can be used for storing photos,music files
etc
Date Time
✔ They are used for storing information such as
date of birth,date of admission etc.
 SESSION-3
PERFORM
OPERATIONS
ON TABLE
Inserting Data in a table
✔ Select the table > Double click on it.
✔ The table will open in Datasheet view
✔ Insert the required number of records in
Datasheet view.

Editing Records in a table

✔ Select the table > Double click on it.
✔ The table will open in Datasheet view
✔ Edit the required record in Datasheet view.
Deleting Records from a table
✔ Select the table > Double click on it.
✔ The table will open in Datasheet view
✔ Select the data->right click->Select delete
option
Field Properties
✔ Select the table >Right Click > select option
Edit>Table will open in Design view
Field Properties
Sorting Data
Sorting means to arrange the data in either
ascending or descending order

Referential Integrity
Referential Integrity is used to maintain accuracy
and consistency of data in a relationship.
Creating and Editing
Relationships between Tables
• A relationship refers to an association or connection
between two or more tables.
• Relationships between the tables can be created by
click tools menu->relationship options

Relationship between tables helps to

• Save time as there is no need to enter the same data in separate
tables
• Reduce data entry errors
• Summarize data from related tables.
Types of Relationships
Query
Query displays specific records
from a table that meet certain
criteria
Types of commands
DDL-Data Definition language

DML-Data Manipulation
language
DATA DEFINITION / DESCRIPTION
LANGUAGE:-
✔ It is a standard for commands that
define the different structures in a
database.
✔ DDL statements create, modify and
remove database objects such as
tables, indexes and users.
Common DDL Statements are:-
✔ Create :- Used to create database
objects.
✔ Alter :- Used to modify database
objects.
✔ Drop :- Used to delete database
objects.
Create :- Used to create database
objects.
Syntax :- Create Table <table name>
(<column name> <data type> <size>,
<column name> <data type> <size>, - - - );
✔ Example :-
✔ Create table Lab
( Item_Code Char(3),
Item_Name Varchar(10),
Price Float(4,2),
Quantity Int,
Date_of_Purchase Date);
Alter :- This statement is used to add,
delete, modify columns in an existing
table
1) Adding column in an existing table
Syntax :- Alter table <table name>
Add <column name> <data type> [constraint definition];
✔ Example :-
Alter Table Lab
Add Item_Company Varchar(10);
Alter table CLASSX add gender varchar(10);
Alter :- This statement is used to add,
delete, modify columns in an existing
table
2) Changing a column’s definition
Syntax :- Alter Table <table name>
Modify <column name> <data type>;
✔ Example :-
SQL> Alter Table Lab
Modify Item_Name varchar(15);
Alter :- This statement is used to add,
delete, modify columns in an existing
table
3) Dropping a column from the table
Syntax :- Alter Table <table name>
Drop Column <column name>;
✔ Example :-
SQL> Alter Table Lab
Drop Column Item_Company;
Drop:
Dropping a table not only deletes the data
contained in the table but it also removes
the definition of its structure. It is a DDL
statement.

Syntax: Drop Table <table name>;

Example: SQL> Drop table Employee;

Constraints:
Constraints are used to prevent invalid
data entry into the table. They provide
methods so to follow the rules of an
organization.
(i) Not Null-Not Null constraint on a column ensures that the
column doesn’t remain empty.
Example: SQL> Create Table Student
( Name Varchar (25) Not Null,
Class char(3) );
(ii) Unique constraint makes it sure that the data entered in a
column does not match with the records.
Example: SQL> Create Table Student
( Roll-No char(3) Unique,
Name Varchar(15) Not Null,
Class char(3));
(iii) Primary Key constraint makes it sure that data is unique and
cell doesn’t remain empty.
Example: SQL> Create Table Student
( Roll-No char(3) Primary Key,
Name Varchar(15),
Class char(3));
(iv) Check constraint makes sure that data entered in a table is
within the certain range of our requirement.

Example: SQL> Create Table Employee

( EmpNo char(3),
Ename Varchar(15),
Salary Float(8,2) Check (salary > 0) );

(v) Foreign Key constraint setup a relationship with Primary Key

of another table.

Example: SQL> Create Table Student

( Roll-No char(3),
Name Varchar(15),
Class char(2),
Stream Char(5) References
School(Stream));
Arithmetic operators in SQL?

Operators Description Example

+ Perform Addition 7+2=9

- Perform Subtraction 7–2=5
* Perform Multiplication 7 * 2 = 14
/ Perform Division 7/2=3
% Returns remainder 7%2=1
Comparison operators in SQL
Comparison operators are used to compare two
values and return true (1) or false (0).
Operators Description Example
= Less than 7 = 2 False
<= Less than equal to 7 <= 2 False
>= Greater than equl to 7 >= 2 True
<> Not equal to 7 <> 2 True
Logical operators are used to combine the
conditions in SQL. These are the five logical
operators in SQL:
Operators Description

AND All conditions should be true.

OR Any condition true.
NOT Reverse the meaning of operator.
BETWEEN Search the value within the set of values.
ALL Compare a value to all values.
DATA MANIPULATION LANGUAGE:- It is
a standard for commands that enables
users to access and manipulate data in
a database.
Common DML Statements are:-
✔ SELECT :- Used for retrieval of information
from the database.
✔ INSERT :- Used for insertion of new
information into the database.
✔ DELETE :- Used for deletion of information in
the database.
✔ UPDATE :- Used for modification of
information in the database.
INSERT :- Used for insertion of new information
into the database.

Syntax: Insert into <table name> (<column list>)

Values (<list of values>);
Example:
SQL> Insert into Sport
Values (15, 10, ‘Arpit’, ‘Cricket’, ‘A’, 90);
(or)
Example:
SQL> Insert into Sport (Roll No, Class, Name, Game)
Values (14, 9, ‘Ravi’, Basketball’);
SELECT :- Used for retrieval of information from
the database.
i) Select All Data from Table
Syntax:
Select * from <table name>;
Example:
SQL> Select * From Sport;
ii) Select Specific column from TableSyntax:
Select <column list> from <table name>
Example:
SQL> Select Roll No, Name, Marks from Sport;
 ORDER BY CLAUSE
Order by clause is used to display data in
ascending or descending order using Select
command in SQL

1) Order By ASC:

Syntax: Select * from <table name> order by

<column> ASC;

Example: SQL> Select * From Sport order by

Name asc;
Order by clause is used to display data in
ascending or descending order using Select
command in SQL

2) Order By DESC:
Syntax: Select * from <table name> order by
<column> DESC;

Example: SQL> Select * From Sport order by

Name desc;
 DISTINCT CLAUSE
Distinct clause is used to avoid getting duplicate
data in a table.

Syntax: Select Distinct <column list>

From <table name>;

Example: Select Distinct Game from Sport;

 WHERE CLAUSE
It is used to specify a condition when using
select command.

Syntax:
Select [Distinct] <column list> from <table
name>
Where <condition> ;
Example:
SQL>Select * from sports where mark>=75;
 WILD CARDS
Wild Cards are used to specify SELECT command
in SQL.
✔ '_' wild card is used for single character
✔ % wild card for multiple characters.

Example: SQL> Select * From Sport

Where Name like 'A%';
UPDATE :- Used for modification of information
in the database.

Syntax:
Update <table name>
set <column name> = <value> Where
<condition> ;

Example: SQL> Update Sport set Price = 440

Where Product Name = 'Pen';
DELETE :- Used for deletion of information in
the database.
Syntax:
Delete from <table name>
Where <condition> ;

Example: SQL> Delete from Sport

Where Product Name = 'Pen';
 What is Function in SQL?
SQL provides inbuilt functions in SQL

Example: SQL> Select Count(Price) from Sport;

Function Description
Count() Used to count records
Avg() Used to find Average
Max() Used to find Maximum value
Min() Used to find Minimum value
Sum() Used to find sum in record
What is degree and cardinality with example?
• Degree in SQL refers to the number of attributes
or columns in a relation or table.
• It represents the horizontal size of the table.
• For example, if a table has three attributes, it
has a degree of three.
• Cardinality in SQL refers to the number of tuples
or rows in a relation or table.