Class 10 – IT 402 Database Management System – Notes

Unit 3 – Database Management System

Data Base Management System

Data – In a database, data refers to the collection of facts, figures, or information that is stored in a structured format. These pieces of information can include things like names, addresses, dates, numbers, or any other type of information that is relevant to the system or application using the database.

Base – BASE, which stands for Basically Available, Soft state, Eventually consistent, is a data management principle that provides an alternative to the traditional model for database management.

Management – Management in a Database Management System (DBMS) refers to the set of activities involved in creating, organizing, maintaining, securing, and optimizing a database.

System – In a Database Management System (DBMS), the term “system” typically refers to the software system used to manage the database.

Database Management System

A Database Management System is a software package with computer programs that controls the creation, maintenance, and use of a database. It allows organizations to conveniently develop databases for various applications. A database is an integrated collection of data records, files, and other objects. A DBMS allows different user application programs to concurrently access the same database.
Well known DBMSs include Oracle, IBM DB2, Microsoft SQL Server, Microsoft Access, PostgreSQL, MySQL, FoxPro, and SQLite.

A database model is basically used by the following users:

1. Developers: design and develop a database.
2. Administrators: keep track on database and check authorization to provide access to the users. (Admin) / DBA
3. End users: are the group of people who really use the database program. For example, in a school, teachers and students are the end users as they use the database every day.

Key components of Database

• Columns are referred as fields. A field describes a specific property of a record, that is why a field is also known as an attribute.
• Rows are referred as records. A single record is also known as tuple.
• A set of characters that represents a valid value is known as data or data value or data item.

Different types of Database Management System

Following are the different types of DBMS:

1. Relational Database Management System (MySQL)
2. Hierarchical Database Management System
3. Network Database Management System
4. Object-Oriented Database Management System

Microsoft Office vs Apache OpenOffice / Libre Office Features

Types of Data in Database

Data can be organized int two types:

1. Flat File: A flat file is a simple type of data storage format in which data is stored in a single table with a series of rows and columns. It is used to store small amount of data. Example: MS Excel
2. Relational: A relational database uses multiple tables with defined relationships between them to store data. It is used to store huge amount of data and can be accessed and queried using Structured Query Language (SQL). Example: MS Access, MY SQL

Advantages of Database

1. Reduces Data Redundancy
2. Reduces Data Inconsistency
3. Increases Data Integrity
4. Sharing of Data
5. Sharing of Resources
6. Data Security
7. Privacy
8. Backup & Recovery

Features of Database

Database Server

The database server is responsible for storing, managing, and retrieving data from one or more databases, and for handling requests from clients that wish to access or modify the data.

RDBMS (Relational Database Management System)

It is a type of database. It uses a structure that allows us to identify and access data in relation to another piece of data in the database. Often, data in relational database is organized into tables. In a relational database each row is defined in the table is a record with a unique ID called the key.

Types of Keys in Database

1. Primary Key – A primary key is a field or column that uniquely identifies the each record.
2. Composite Primary Key – Composite primary key is a key of two or more attributes that uniquely identifies the row.
3. Foreign Key – A foreign key is a field or a set of fields in a table that refers to the primary key of another table.
4. Alternate Key – An alternate key is a candidate key that is not chosen to be the primary key.
5. Candidate Key – A candidate key is a unique key that can be used as a primary key.

SQL (Structured Query Language)

SQL (Structured Query Language) is the language used in RDBMS for writing queries. Using SQL, a user can create queries to fetch and manipulate the data of the database.

The SQL commands are of two types (according to syllabus):

1. Data Definition Language (DDL)
2. Data Manipulation Language (DML)

Data Definition Language (DDL)

These commands are used to define and modify the structure of a database. The commands that fall under this category are listed as follows:

1. CREATE – It is used to create a new database or table.

CREATE DATABASE

SYNTAX: CREATE DATABASE <DATABASE_NAME>;
EXAMPLE: CREATE DATABASE myschool_db;

USE DATABASE – To create a table USE Database command is required.

SYNTAX: USE <DATABASE_NAME>;
EXAMPLE: USE myschool_db;

CREATE TABLE

SYNTAX: CREATE TABLE table_name(column_definition1, column_definition2, ........ ,........);
EXAMPLE: CREATE TABLE employee(e_id int(8), e_name char(30));

Que: Write a command and query to create the structure of a table named “student1”.

Roll_no

Class

Name

Marks

2. ALTER – It is used to modify the structure of an existing database or table.

TO ADD COLUMN (FIELD)

SYNTAX: ALTER TABLE <Table_Name> ADD COLUMN <Column_Name> <Data_Type>;
EXAMPLE: ALTER TABLE employee ADD COLUMN e_loc char(50);

TO REMOVE/DROP COLUMN (FIELD)

SYNTAX: ALTER TABLE <Table_Name> DROP COLUMN <Column_Name>;
EXAMPLE: ALTER TABLE employee DROP COLUMN e_loc;

3. DROP – Deletes an existing database or table.

TO DROP TABLE

SYNTAX: DROP TABLE <Table_Name>;
EXAMPLE: DROP TABLE employee;

TO DROP DATABASE

SYNTAX: DROP DATABASE <Database_Name>;
EXAMPLE: DROP DATABASE myschool_db;

4. TRUNCATE – Remove all table records including allocated table spaces, but not the table itself.

SYNTAX: TRUNCATE TABLE <Table_Name>;
EXAMPLE: TRUNCATE TABLE employee;

5. RENAME – It is used to change the name of existing database or table.

SYNTAX: ALTER TABLE <Table_Name> RENAME TO <New_Table_Name>;
EXAMPLE: ALTER TABLE employee RENAME TO customers;

Data Manipulation Language (DML)

A DML (Data Manipulation Language) is a computer programming language used for adding (inserting), modifying (updating) and data in database. The commands that fall under this category are listed as follows:

1. INSERT – The insert command is used to add one or more records to a table. There are two methods to use the INSERT command.

Method 1: 
SYNTAX: INSERT INTO <Table_Name> (Field 1, Field 2,….) VALUES (Value 1, Value 2,….);
EXAMPLE: INSERT INTO employee (‘e_id’, ‘e_name’,) VALUES (1, “Mukesh”);

Method 2: 
SYNTAX: INSERT INTO <Table_Name> VALUES (Value 1, Value 2,….);
EXAMPLE: INSERT INTO employee VALUES (1, “Mukesh”);

2. SELECT – It is used to retrieves or fetch the data from the table.

TO FETCH ENTIRE RECORD OF TABLE [* means ALL records]

SYNTAX: SELECT * FROM <Table_Name>;
EXAMPLE: SELECT * FROM employee;

TO FETCH RECORD OF SELECTED FIELDS FROM TABLE

SYNTAX: SELECT <Field1, Field2,…> FROM <Table_Name>;
EXAMPLE: SELECT e_name, e_id FROM <Table_Name>;

Que: Write a command and queries to create the records of a table named ‘Students’ with the following fields.

Roll_No	Name	Class	Marks
101	Ajay	X	765
107	Suraj	IX	865
109	Simran	X	766
103	Aman	IX	821
104	Naresh	IX	1

Ans: (a)
CREATE TABLE Students (Roll_No INT NOT NULL, Name VARCHAR(50), Class CHAR(10), Marks INT);
INSERT INTO Students VALUES (101,“Ajay”,“X”,765);
INSERT INTO Students VALUES (107,“Suraj”,“IX”,865);
INSERT INTO Students VALUES (109,“Simran”,“X”,766);
INSERT INTO Students VALUES (103,“Aman”,“IX”,821);
INSERT INTO Students VALUES (104,“Naresh”,“IX”,1);
(b)
SELECT * FROM Students WHERE Class=“X”;

SELECT Command Clauses

• WHERE clause specifies a criteria about the rows to be retrieved.
• ORDER BY clause specifies an order (ascending/descending) in which the rows (records) are to be retrieved.

To retrieve all records:

SELECT * FROM <Table_Name>;

To retrieved records from specific fields:

SELECT Field1, Field2,… FROM <Table_Name>;

By using WHERE clause:

SELECT * FROM <Table_Name> WHERE condition;
SELECT Field1, Field2,… FROM <Table_Name> WHERE condition;

By using ORDER BY clause:

By default Ascending order
SELECT * FROM <Table_Name> ORDER BY EName;
in Descending order
SELECT * FROM <Table_Name> ORDER BY EName DESC;

3. UPDATE – Sometimes, you need to modify the existing records in the table. The UPDATE command of the SQL can be used for this purpose.

SYNTAX: UPDATE <Table_Name> SET Column1 = Value1, Column2  = Value2,…. WHERE condition;
EXAMPLE: UPDATE employee SET Last_Name=‘Sharma’ WHERE E_ID=101;

4. DELETE – It is used to remove the existing records from a table.

SYNTAX: DELETE FROM <Table_Name> WHERE condition;
EXAMPLE: DELETE FROM employee WHERE E_ID=101;

Important: CREATE TABLE BY ASSIGNING PRIMARY KEY

EXAMPLE: CREATE TABLE Employee1 (Emp_ID INT NOT NULL PRIMARY KEY, ENAME VARCHAR(20), AGE INT(5));

MYSQL Functions

MySQL has many built-in functions. Some of them are as: COUNT( ), SUM( ), AVG( ), MAX( ), MIN( )

COUNT( ) – The COUNT() function returns the number of rows that matches a specified criterion.

SELECT COUNT(*) FROM table_name WHERE condition;
SELECT COUNT(column_name) FROM table_name WHERE condition;

SUM( ) – The SUM() function returns the total sum of a numeric column.

SELECT SUM(column_name) FROM table_name WHERE condition;

AVG( ) – The AVG() function returns the average value of a numeric column.

SELECT AVG(column_name) FROM table_name WHERE condition;

MAX( ) – The MAX() function returns the largest value of the selected column.

SELECT MAX(column_name) FROM table_name WHERE condition;

MIN( ) – The MIN() function returns the smallest value of the selected column.

SELECT MIN(column_name) FROM table_name WHERE condition;

Wildcard Characters in SQL

• % – represent zero or more characters
  • Example: bl% finds bl, black, blue, and blob
• _ – Represents a single character
  • Example: h_t finds hot, hat, and hit
• [] – Represents any single character within the brackets
  • Example: h[oa]t finds hot and hat, not hit or hut
• ^ – Represents any character not in the brackets
  • Example: h[^oa]t finds hit, hut not hot and hat
• Hyphen (-) – Represents a range of characters
  • Example: c[a-d]t finds cat and cbt, cct, cdt

GROUP BY Statement

The GROUP BY statement groups rows that have the same values into summary rows, like “find the number of customers in each country”.

The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

GROUP BY Syntax:
SELECT FUNCTION(ColumnName) FROM table_name WHERE condition GROUP BY column_name;

Example:
SELECT COUNT(CustomerID), country FROM customers GROUP BY country;