10 - Database Concept

A database may be defined as a collection of interrelated data stored

together to serve multiple application.

File based system : A file based system or flat file database

Store all data in one big file.
There is no structure associated with the data.
Common data may be repeated a number of time

Limitation of File based Approach :

Difficulty in Access :
Data Redundancy : it refers to the storage of the same data multiple
time ( duplicated data )
Data Inconsistency : Multiple mismatched copies of same data is
called Data Inconsistence.
Data Isolation : it refers to a situation where data of one file
cannot be mapped to other related file in the
absence of link or mapping.
Data Dependence : multiple files dependence each other , so the
software or application required to update and
maintain all files.
Data Sharing / Security / Control Issue :
All these are absences in Flat file data system.
Database : A Database is an organized collection of structured information,
or data typically stored electronically in a computer system at a
central location.

DBMS ( Data Base Management System) : A DBMS is software system

designed to maintain a database and provide data management
services.
Advantage of DBMS :
It Reduce the data redundancy to a large extent.
It Control data inconsistency to a large extent.
It facilitate sharing of data
It enforce standards.
It can ensure data security
 It ensure data Independence.
DBMS Key Concepts :
Database Schema : it is a sketch / skeleton /blueprint of a planned
data . It represents the design of tables, columns,
relations, constrains and relationships that make
up a logically distinct section of database.
Database Instance : It is a snapshot of a database that exists at a
particular time , i.e the data which is stored in the
database at a particular moment of time.
Metadata / (Data dictionary) : The data dictionary is file storing
metadata of the object of database. The Metadata
refer to data about data
Data Constraints : A database constraint is set of rules that define
valid data. It is also enforces to accept the valid
data from user. Like
PRIMARY KEY, NOT NULL, UNIQUE, CHECK,
DEFAULT, FOREIGN KEY
Query : It is type of command that retrieves data from a
database stored on a server.
Data Manipulation : Inserting , updating , deleting data in data base
refer to data manipulation.
Database Engine : It is the underlying software component that a
DBMS uses to create , read, update and delete
data from database.
Database Views : A Database View is a virtual table that provides a
user-friendly representation of data from one or
more underlying tables
Database Model : A database model shows the logical structure of a
database, including the relationships and constraints
that determine how data can be stored and accessed
Hierarchical database model , Relational model , Network model,
Object-oriented database model , Entity-relationship model,
Document model , Entity-attribute-value model , Star schema ,
The object-relational model, which combines the two that make up its name
Relational Database Model : In relational data model the data is
organized into relations ( tables ), tuple (rows) , attributes ( columns )
etc.
Relational Model Terminology: I
Relation : In Relational Database Model relation is table.
That arranged the data in rows and columns
Domain : A domain is a set of values that can be stored in a column
of a database table. A domain is usually defined by a
column's data type, which determines the kind of values
that can be stored in the column. For example, a column
with a data type of INTEGER can only store numeric values.
Tuple : The rows of tables (relation) are generally refereed to
as Tuples
Attributes : The columns of table (relation) are generally to as
attributes
Degree : The number of attributes in a relation determines the
degree of a relation.
Cardinality : The number of tuples (rows) in a relation is called the
cardinality of the relation
Properties of Relation :
➢ Every row is unique.
➢ All of the values present in a column hold the same data type.
➢ Values are atomic.
➢ The columns sequence is not significant.
➢ The rows sequence is not significant.
➢ The name of every column is unique.

Properties of Attributes :
The sequence / Ordering of column is Insignificant.
Each column has a Unique Name
Properties of Row(Tuple) :
Each row is Unique (distinct)
The sequence of Rows is Insignificant.
Keys in a Database :
 Primary Key : A Primary key is set of one or more attributes that can
uniquely identify tuple within the relation. The Primary key does not
allowed the Null value & each relation have only one primary key.

Candidate Key : All attributes combination inside a relation that can

serve as primary key are candidate keys as they are candidates for the
primary key position. A relation can have more than one candidate
key.
Alternate Key : A candidate key that is not the primary key is called an
alternate key. Incase two or more candidate keys , only one of them
serves as the primary key. The rest of them are alternate keys .
Foreign Key : A non-key attribute , whose values are derived from
the primary key of some other table. Is known as foreign key in the
current table.
A non primary key of one table , that can serve as the primary key of
another table . The value of non-primary key derived and reference
with the value of another table primary key . That key treat as the
foreign key of current table.

MySQL :
Classification of SQL Statements :
DDL : Data Definition Language
DML : Data Manipulation Language
TCL : Transaction Control Language
DCL : Data Control Language

How to Start MySql

Grno Sname Dob Tot
101 Aaa 2021-04-12 240
102 Bbb 2020-04-06 250
103 Ccc 2014-04-01 450
104 Ddd 2021-05-22 350