MySQL Part - 1
Understanding the need
Take example of a school that maintains data about its
students, along with their attendance record and guardian
details.
The class teacher marks daily attendance of the students in the
attendance register.
If class strength is 50 and total working days in a month are 26,
The teacher needs to record 50 × 26 = 1300 records manually
in the register every month.
Limitations of manual record keeping
As the volume of data increases, entry becomes tedious.
Entry in the new register when the student is promoted to the next
class.
Writing student details on each month’s attendance page where
inconsistency(discrepancy) may happen due to incorrectly written
names, skipped student records, etc.
Loss of data in case attendance register is lost or damaged.
Erroneous calculation while consolidating attendance record manually.
Finding information from a huge volume of papers or
deleting/modifying an entry is a difficult task in pen and paper based
approach.
File System
A file can be understood as a container to store data in a
computer.
Files stored on a computer can be accessed directly and
searched for desired data.
Limitations of a File System
It becomes difficult to handle when number of files increases and
volume of data grows.
Difficulty
in Access Data
Data Sharing Redundancy
– Security/
Control Issues (repetition of
data)
Limitations
Data
Data Inconsistency
Dependence Data Isolation (same data at
different places do
(data is in separate not match)
files, difficult to
combine data)
SOLUTION
Limitations faced in file system can be overcome by storing
the data in a database where data are logically related.
We can organise related data in a database so that it can be
managed in an efficient and easy way.
Database
A database is a collection of interrelated data stored together
to serve some application.
It can also be defined as a computer based record keeping
system.
DataBase Management System
A Database management system (DBMS) is software that
contains the database and the tools to manage that database.
DBMS lets users to create a database, store, manage,
update/modify and retrieve data from that database by users
or application programs.
Some examples of DBMS include MySQL, Oracle,
PostgreSQL, SQL Server, Microsoft Access, MongoDB.
Advantages of DBMS
Reduce data redundancy (Repetition of data)
Control data inconsistency (Improper update)
Facilitate sharing of data
Enforce standards
Ensure data security
Maintain data independence
Use of Database in Real-
Real-life
Applications
Key Concepts in DBMS
Database Schema
Database Schema is the design of a database. It is the skeleton
of the database that represents
The structure (table names and their fields/columns),
The type of data each column can hold,
Constraints on the data to be stored (if any),
The relationships among the tables.
Database Instance
When we define database structure or schema, state of
database is empty i.e. no data entry is there.
After loading data, the state or snapshot of the database at any
given time is the database instance.
Meta-
Meta-data and Data Dictionary
The database schema along with various constraints on the
data is stored by DBMS in a database catalog or dictionary,
called meta-data.
A meta-data is data about the data.
Data Constraint
Certain restrictions or limitations on the type of data that can be
inserted in one or more columns of a table.
For example, one can define the constraint that the column mobile
number can only have non-negative integer values of exactly 10 digits.
Since each student shall have one unique roll number, we can put the NOT
NULL and UNIQUE constraints on the RollNumber column.
Constraints are used to ensure accuracy and reliability of data in the
database.
Query
A query is a request to a database for obtaining information
in a desired way.
Query can be made to get data from one table or from a
combination of tables.
For example, “find names of all those students present on
Attendance Date 2000-01-02” is a query to the database.
Data Manipulation
Modification of database consists of three operations
Insertion
Adding data of a new student
Deletion
Removing the data of a student who is leaving
Update
Changing some information stored in the database
Database Engine
Database engine is the underlying component or set of
programs used by a DBMS to create database and handle
various queries for data retrieval and manipulation.
Data Models
Different types of DBMS are available and their classification is
done based on the underlying data model.
Types of DBMS
Relational Data Model (most widely used)
Object-oriented data model
Entity-relationship data model
Document model
Hierarchical data model.
