DataBase management system
notes
Collection of raw facts and figures in an unorganized format without any meaning
Data - Data is individual units of information can be in any form -
Text,numbers,images,Audio and video without any meaning .Data is always stored in
tables,tree,graphs
Exmaple - "Red", "Apple", "90", "john" this is all are in unorganized form
Meaningless until useless we add some context which means when we add meaning to
the data then it is called as information this info can be given by humans or
machines.
Information- Data has been processed data that was organized and holds some
meaning. Information is always stored in the thoughts, language , ideas
Example 1
John is 22 years old
The apple is red and weight and 90 grams
Now the data makes sense because ot tells us something use
Data Information
1. Data is collection of raw facts and figures 1. Information is
processed data
2. Unorganized doesn't carry meaning 2. organized
carries meaning
3. Doesnt help in decision making 3. Helps directly in
decision making
4. Data is input 4. information is output
DataBase- A database is a collection of related data
Example 1 : A student database includes name , roll no, college name, marks , etc
Example 2 : An employee database includes emp id , emp name , department , salary
Its a place to store related data(digital cupboard to store data)
Database Management System-A Database Management System (DBMS) is a software
solution designed to efficiently manage organize and retrieve data in a structured
manner.
Software for creating and manipulating the database
Software for inserting updating and deleting the data from database
Database are controlled by database management system
Database server is physical machine a central hub for storing databases
Examples
MySQL
Microsoft SQL Server
Oracle
Postgre SQL
Key Features of DBMS
Data Modeling: Tools to create and modify data models, defining the structure and
relationships within the database.
Data Storage and Retrieval: Efficient mechanisms for storing data and executing
queries to retrieve it quickly.
Concurrency Control: Ensures multiple users can access the database simultaneously
without conflicts.
Data Integrity and Security: Enforces rules to maintain accurate and secure data,
including access controls and encryption.
Backup and Recovery: Protects data with regular backups and enables recovery in
case of system failures.
Types of Database Management System
1. Non-Relational DBMS(NoSQL)
2. Relational DBMS(SQL)
1. Non-Relational DBMS- NoSQL DBMS
NoSQL systems are designed to handle large-scale data and provide high performance
for scenarios where relational models might be restrictive. They store data in
various non-relational formats, such as key-value pairs, documents, graphs or
columns. These flexible data models enable rapid scaling and are well-suited for
unstructured or semi-structured data.
2. Relational Database Management System (RDBMS)
RDBMS organizes data into tables (relations) composed of rows and columns. It uses
primary keys to uniquely identify rows and foreign keys to establish relationships
between tables. Queries are written in SQL (Structured Query Language), which
allows for efficient data manipulation and retrieval.
This 2 types are mostly used and are imp in market but there are 6 types of DBMS in
the market
1. Relational Database Management System (RDBMS)
2. NoSQL DBMS
3. Object-Oriented DBMS (OODBMS)
4. Hierarchical Database
5. Network Database
6. Cloud-Based Database
Difference between DBMS and RDBMS
DBMS RDBMS
1. Data is stored in the files 1. Data is stored
in the form of tables
2. No relationship b/w data, old model for organising 2.
Relationship b/w data
and accessing data
3. Doesnt support Normalization 3. Supports
Normalization
4. Doesnt support distributed databases 4. Supports distributed
databases
5. Supports single user handle small amount 5. Supports multi-
users handle large amounts of data useful for large
of the data useful for small organisizations. organisations
6. Ex-MongoDb,Foxpro 6.
MySQL,SQl,Server,Oracle
DataBase Table
1. A database consist of multiple tables 1. A table contains of
multiple rows and columns
2. It is a higher level of data storage 2. It is a lower level
of a data storage part of a database
Implementing RDBMS
1. Programming language ---SQL (Structured Query Language )
2. Programming Platform -- MySQL,SQL,Server,Oracle,Postgre SQL
Database Operation
1. create
2. view all databases
3. shifting to a particular databases
4. delete a database
1. How to create a database?
Syntax: create database <databasename> // create a database of the student
2. How to view all databases in workbench ?
Syntax: show databases
3. How to shift to a particular database?
Syntax: use databasename
use student
4. How to delete a database ?
Syntax drop database <databasename>
drop database student
Data types in SQL:
Data types defines what type of data is stored in a column
In SQL we have different types of datatypes
Main Data Types includes:
1. Numeric Datatypes :
1. INT: Used for storing whole integers
2. BIG INT: Used for storing large whole numbers without decimal points
3. FLOAT: Used for storing decimal point number(4 bytes)
4. DOUBLE: Used for storing decimal point(8 bytes)
2. Character Datatypes
1. CHAR(n)- fized length strings can be stored -- char(100,--->. memory wastage
2. VARCHAR(n)- Variab;e length character strings can be stored --varchar(10)--->
no memory wastage
3. Text : Variable length character strings with no specificed limit
3. Date and Time Datatype
1. DATE - Used for storing date values(YYYY-MM-DD)
2. TIME - Used for storing time values(HH-MM-SS)
3. TIMESTAMP-used for storing data and time values(YY-MM-DD HH-MM-S)
comments:
1. Used to explain the code to others
2. Describe the code
3. Temporarily disable the code during execution
Single Line comments:
Starts with --space
any line/text between -- to the end of the line will be ingnored
Multi Line comments : Starts with/* and ends with */
this shows that lines in between them are diasables
Example : Create a 5 databases namely company1 , company2, ...... company5
a) View all the databases
b) use all the databases
c) Drop all the databases
HOW TO CREATE AND INSERT VALUES IN A TABLE
SYNTAX:
CREATE TABLE TableName(
Column1 DataType,
Column2 DataType,
Column3 DataType,
......
);
INSERT INTO name(.....)
Example:
CREATE TABLE Students(
StudentID INT,
Name VARCHAR(100),
Age INT,
Gender VARCHAR(10), // 10 is the number of the digits
EnrollnmentDate DATE // creates only structure of the table,to get the
values we must insert the data .
);
INSERT INTO Students(StudentID,Name,Age,Gender,EnrolllnmentDate)
VALUES
(1,'poojitha',20,'FeMale','2023-09-01'),
(2,'priya',19,'Female','2023-09-02'); // yy mm dd
SQL COMMAND TYPES
1. DDL - Data Defination Language : Defines the structure of the data in the
database/Table used to create the databases/tables all the
commands of DDL are auto-committed
a. Create- used to create the database
CREATE TABLE Employees{
EmpName VARCHAR(100);
}
b. alter:To change the structure of the table
Add a column: ALTER TABLE Employees ADD Department VARCHAR(50);
Modify the column: ALTER TABLE Employees Modify EmpName VARCHAR(150);
Drop a column: ALTER TABLE Employees DROP COLUMN JoinDate;
c. Drop: Used to delete the entire table/DataBase
DROP TABLE Employees;
DROP DATABASE Company;
d. Truncate: Removes all the data from the table but keeps the structure of
table
TRUNCATE TABLE Employees
e. Rename: Used to change the name of the table
RENAME TABLE Employees TO Staff;
2. DML-Data Manipulation :
--->Used to modify the data in the tables . They do not change the structure of the
table Instead they
manipulate the data inside the tables.
---> Responsible for all types of data changes in the tables
---> Not auto-committed
a. INSERT: Adds new data (row) to the table
Example :
CREATE TABLE Students(
StudentID INT,
Name VARCHAR(50),
Age INT
);
INSERT INTO Students(StudentID,Name,Age)
VALUES(1,'JOHN',20)
(2,'DAVID',30);
b. UPDATE: Modifes the existing data in the table
UPDATE Students
SET Age=21
WHERE StudentID=1;
Changes John age from 20 to 21
c. DELETE: Removes the row from the table
DELETE FROM Students
WHERE StudentID=1;
d. SELECT : to view all the data from the table
SELECT * from Students
1. Write an SQL query to create a table named students with the following fields :
StudentID,Name,Age,Gender,EnrollnmentDate and insert 3 rows of data into it
create database college
use college
CREATE TABLE Students(
StudentID INT,
Name VARCHAR(100),
Age INT,
Gender VARCHAR(10),
EnrollnmentDate DATE
);
INSERT INTO Students(StudentID,Name,Age,Gender,EnrollnmentDate)
VALUES
(1,'Poojitha',20,'Female',2023-09-01'),
(2,'Anitha',45,'Female',2023-09-01'),
(3,'Sai',24,'Male','2023-09-01');
Practice Questions
1. Create a table named courses with fields CourseID,CourseName,Credits and
Department and insert 4 rows in data
2. Create a table called Enrollnments with columns
EnrollnmentID,StudentID,EnrollnmentDate and insert 6 rows of data
3. Create a table Employees with the fields
EmpID,EmpName,Salary,JoinDate,DepartmentID and insert 5 rows of data
4. Create a table Departments with the fields DepartmentID,DepartmentName, and
Location and insert 5 rows of data
