CHAPTER-2 DATABASE CONCEPTS

DATA—
Data is a collection of raw facts which have not been processed to reveal useful information.

DATABASE—
The collection of related data that has been recorded, organized and made available for searching is called a
Database.

PROPERTIES OF DATABASE
1) A database is a representation of some aspect of the real world also called miniworld. Whenever there are
changes in this miniworld they are also reflected in the database.
2) It is designed, built and populated with data for specific purpose.
3) It can be of any size and complexity.
4) It can be maintained manually or it may be computerized.

DATA REDUNDANCY:
Same information is stored in more than one file. This would result in wastage of space.

DATA INCONSISTENCY:
If a file is updated then all the files containing similar information must be updated else it would result in
inconsistency of data.

DBMS
A database management system is a collection of programs that enables users to create, maintain and use a
database.
Some examples of DBMS are – MySQL, Oracle, DB2, IMS, IDS etc.

OPERATIONS OF A DATABASE
1. Defining the Database: It involves specifying the data type of data that will be stored in the database and
also any constraints on that data.
2. Populating the Database: It involves storing the data on some storage medium that is controlled by
DBMS.
3. Manipulating the Database: It involves modifying the database, retrieving data or querying the database,
generating reports from the database etc.
4. Sharing the Database: Allow multiple users to access the database at the same time.
5. Protecting the Database: It enables protection of the database from software/ hardware failures and
unauthorized access.
6. Maintaining the Database: It is easy to adapt to the changing requirements.

Characteristics of Database Management Systems

1.Self-describing Nature of a Database System
2. Insulation Between Programs and Data
3. Sharing of Data

Types of Users of DBMS

1. End Users: Users who use the database for querying, modifying and generating reports as per their needs.
They are not concerned about the working and designing of the database. They simply use the DBMS to get
their task done.
2. Database Administrator (DBA): As the name implies, the DBA administers the database and the DBMS.
The DBA is responsible for authoring access, monitoring its use, providing technical support, acquiring
software and hardware resources.
3. Application Programmers: Application programmes write application programs to interact with the
database. These programs are written in high level languages and SQL to interact with the database.
4. System Analyst: System analyst determines the requirements of the end users and then develops
specifications to meet these requirements. A system analyst plays a major role in the database design and all
the technical, economic and feasibility aspects.

Advantages of using DBMS

• Reduction in Redundancy
• Improved Consistency
• Improved Availability
• Improved Security
• User Friendly:

Limitations of using DBMS

• The cost of implementing a DBMS system is very high.
• Unauthorized access to a database can lead to 4 Database Management Applications threat to the
individual or organization depending on the data stored

Relational Database
relational database developed by E.F Codd at IBM in 1970. It is used to organize collection of data as a
collection of relations where each relation corresponds to a table of values.

Relational terms:--
• A row is called a Tuple.
• A column is called an Attribute.
• A table is called as a Relation.
• The data type of values in each column is called the Domain.
• The number of attributes in a relation is called the Degree of a relation.
• The number of rows in a relation is called the Cardinality of a relation.
• Relation Schema R is denoted by R (A , A , A …, A ) where R is the relation name 1 2 3, n and A ,
A , A ,….A is the list of attributes. 1 2 3 n
• Relation State is the set of tuples in the relation at a point in time. A relation state r of relation
schema R (A1, A2, ..., An), denoted r(R) is a set of n-tuples r = {t1, t2,...., tm}, where each n-tuple is
an ordered list of values t = , where vi is in domain of A or is NULL. Here n is the degree of the
relation and m is the cardinality of i the relation.

Characteristics of Relations:
• Ordering of tuples is not important in a Relation.
• The ordering of attributes is also unimportant.
• No two tuples of relation should be identical i.e. given any pair of two tuples, value in at least one
column must be different.
• The value in each tuple is an atomic value (indivisible). 5. If the value of an attribute in a tuple is not
known or not applicable or not available, a special value called null is used to represent them.

Types of constraints in Relational model:

• Domain Constraint: It specifies that the value of every attribute in each tuple must be from the
domain of that attribute.
• NOT NULL: This constraint tells that we cannot store a null value in a column. That is, if a
column is specified as NOT NULL then we will not be able to store null in this particular column
any more.
• UNIQUE: This constraint when specified with a column, tells that all the values in the column
must be unique. That is, the values in any row of a column must not be repeated.
• PRIMARY KEY: A primary key is a field which can uniquely identify each row in a table. And
this constraint is used to specify a field in a table as primary key.
• FOREIGN KEY: A Foreign key is a field which can uniquely identify each row in a another table.
And this constraint is used to specify a field as Foreign key.
• CHECK: This constraint helps to validate the values of a column to meet a particular condition.
That is, it helps to ensure that the value stored in a column meets a specific condition.
 • DEFAULT: This constraint specifies a default value for the column when no value is specified by
the user

Structured Query Language (SQL)

SQL is a language that is used to manage data stored in a RDBMS.

Types of SQL languages-

1. DDL—Data Defination Language—this type of language is used to create a database and database
objects. For example—create, alter, drop
2. DML—Data Manipulation Language—this type of language is used to perform different operation
on data of a database. For example—insert, select, update, delete

Create database—
This command is used to create anew database.
Syntax—create database <databasename>;
Example—create database class12;

Use
This command is used to open an existing database.
Syntax—use <databasename>;
Example—use class12;

Show databases—
This command is used to display all the databases of a server
Syntax—show databases;

Drop database—
This command is used to delete a database.
Syntax—drop database <database>;
Example—drop database class12;

Create table
This command is used to create a table
Syntax—
CREATE TABLE <tablename> ( <coulmn1> <datatype, <column2> <datatype>,…………..);

Datatype of SQL
• CHAR (n)-- Fixed length character string. 'n' is the number of characters. For example--
CHAR(5):“Ashok” “Vijay”
• VARCHAR(n)-- Variable length character string. 'n' is the maximum number of characters in the
string.
• DATE--Date in the form of YYYY-MM-DD
• INTEGER--Integer number
• DECIMAL (m, d)-- Fixed point number m represents the number of significant digits that are stored
for values and d represents the number of digits that can be stored following the decimal point. If d is
zero or not specified then the value does not contains any decimal part.

SHOW TABLES
This command displays all the tables created in the current database.
Syntax—show tables;

Self-Referencing Tables:
A foreign key constraint can reference columns within the same table. These tables are called as self-
referencing tables.

Describe –
This command is used to display the structure of a table.
Syntax—describe <tablename>;
Example—describe student;

Drop table –
This command is used to delete a table.
Syntax—drop table <tablename>;
Example—drop table student;

Alter
This command is used to change the structure of a table
• Adding a column:
Syntax—alter table <tablename> add <newcolumnname> <datatype>;
Example—alter table student add contactno integer;

• Dropping a column
Syntax—alter table <tablename> drop<columnname>
Example—alter table student drop contactno ;

• Altering a Column:
• Modify a column—
ALTER TABLE table_name
MODIFY column_name column_type;

• Dropping keys:
• Adding a Constraint:

Insert Command:
This command is used to insert a tuple in a relation
INSERT INTO table_name VALUES (value1, value2, value3);

INSERT INTO table_name (column1, column2, column3)

VALUES ( value1, value2, value3);

Update command
This command is used to update the data of an existing table in the database
UPDATE Teacher SET Salary=55000 WHERE Teacher_ID=101;

Delete command
This command is used to delete the records of a table.
DELETE FROM Teacher WHERE Teacher_ID=101;

Select command—
The SELECT Command is used to retrieve information from a database.
SELECT FROM <tablename> WHERE
<condition>;

• To display all records of a table

Select * from student;
 • To display specific columns of a table
Select rollno, name, marks from student;

• To display records based on logical operators

• Between
• Like
• In
• Distinct
• Order by
• Is null
• Group by

Aggregate Functions.
mathematical functions on group of values in a database
• COUNT- It counts the numbers of tuples in the result of the query.
• SUM – It finds the sum of all the values for a selected attribute which has numeric data type.
• MAX –It finds the maximum value out of all the values for a selected attribute which has numeric
data type.
• MIN - It finds the minimum value out of all the values for a selected attribute which has numeric
data type.
• AVG – It finds the average value of all the values for a selected attribute which has numeric data
type