Unit-1 Database Concepts

Basic Concepts and Definitions :-

Data:- Raw facts and figure of an entity is known as data.

​

Information:- The process data is know as information.

​

Data

process

Information

Database:- It is a space where we collect similar type of data in an organized manner.

​

Need of Database

1.Data Redundancy:-

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

​

2.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

​

3. Lack of Data Integration:-

As data files are independent, accessing information out of multiple files becomes very difficult

Database Management System (DBMS)

A database management system(DBMS) is a collection of program/software that enables users to create, maintain and use a database.

​

Characteristics of Database Management Systems

​

1. Self-describing Nature of a Database System:-DBMS contains not only the database but also the description of the data that it stores. This description of data is called metadata.
2. Insulation Between Programs and Data:-The data structure and storage details are separated from the application programs, allowing changes to data without needing to modify the programs.
3. Sharing of Data:- A multiuser environment allows multiple users to access the database simultaneously

Example of various DBMS software

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):-

DBA is a person/group of person who is responsible for maintaining, securing, and optimizing databases, ensuring data integrity and availability, and collaborating with developers to design and implement new features.

​

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. Asystem analyst plays a major role in the database design and all the technical, economic and feasibility aspects

RDBMS(Relational Database Management System)

• RDBMS is a type of DBMS where the data are store in the form of rows and columns(Table).
• It is developed by E.F Codd at IBM in 1970.

​

• 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 domain of a database attribute is the set of all the possible values that attribute may contain.
• 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 (A1 , A2 , A3 …, An ) where R is the relation name and A1 , A2 , A3 ,….An is the list of attributes.
• 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}

Some terminology of RDBMS:-

Some More Characteristics of Relations

• Ordering of tuples and attribute is not important in a Relation.
• No two tuples of relation should be identical
• The value in each tuple is an atomic value
• 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

Relational Model Constraints

Constraints, are restrictions on the values, stored in a database based on the requirements.

Example:- Employee_phone_no must be within 10 digit

Type of Constraints

1. Domain Constraint:-

It is apply to every attributes of a relation of each tuples. For example, the Employee_ID must be a 4-digit number. Hence a value such as “12321” or “A234” violates the domain constraint.

​

2. Null Value Constraint:-

Sometimes it is required that certain attributes cannot have null values. For Example, every student has a roll_no, it can’t be NULL. So that we use NOT NULL in Roll_no attribute.

​

3.Key Constraint:-

(i)Super key:-

a set of one or more attributes (columns) that can uniquely identify each row (tuple) in a table, but it might contain more attributes than necessary.

(ii)Candidate key:-

It is a minimal super key which is a set of attributes (or columns) that can uniquely identify each row in a table.

For example,

(iii) Primary Key:-

DBA(Database Administrator choose a candidate key as primarity key for uniquely identify each row in a table.

(iv)Alternate key:-

All the candidate key except primary key is know as alternate key.

(v)Foreign Key:-

A foreign key is a attribute in one table that simply refers to the primary key of another table.

4. Entity Integrity Constraint:-

This constraint specifies that primary key of a relation cannot have null value.

5.Referencial integrity constraints:-

​

It is also know as Foreign key constraints. The table from which the values are derived is known as Master or Referenced Table and the Table in which values are inserted accordingly is known as Child or Referencing Table

There are two referential integrity constraint:

​

Insert Constraint: Value cannot be inserted in CHILD/Referencing Table if the value is not lying in MASTER Table

Delete Constraint: Value cannot be deleted from MASTER/Referenced Table if the value is lying in CHILD Table

​

SQL

DataType:-

• Different type of data is known as Datatype.

or

• Datatype is used to determine which type of value a variable can hold.

Types of DataType:-

To store String value

Numeric Data Types

Date and Time Data Types

BLOB, which stands for a Binary Large Object, is a MySQL data type that can store images, PDF files, multimedia, and other types of binary data.

Other DataType

Different Type of SQL command

SQL

DDL

DML

DQL

TCL

DDL(Data Definition Language)

1.Create Database command:- Use to create database in MySQL.

Syntax:-

CREATE DATABASE <database name>;

example:-

create database oav_narla;

​

2.Use command:- this command is used to create table in which database.

Syntax:-

USE <database name>;

example:-

use oav_narla;

​

3.Create table command:- This command is used to create table inside the database.

3.Create table command:- This command is used to create table inside the database.

Syntax:-

CREATE TABLE table_name (�    column1 datatype,�    column2 datatype,�    column3 datatype,�   ....� );

Example:-

Add primary key in Teacher table

1.Primary Key of a table can be specified in two ways. If the primary key of the table consist of a single attribute, then the corresponding attribute can be declared primary key along with its description.

2.if primary key contains more than one attribute then it must be specified separately as a list of attributes it comprises of, within parenthesis, separated by commas. For example, the primary key of the TEACHER relation comprises of Teacher_ID and Date_of_Birth.

1

2

DEFAULT

If a user has not entered a value for an attribute, then default value specified is used.

NOT NULL

An attribute value may not be permitted to be NULL. For example, the First name of the Teacher cannot be NULL.

CHECK:

In order to restrict the values of an attribute within a range, CHECK constraint may be used

Add REFERENTIAL INTEGRITY CONSTRAINT/FOREIGN KEY

Department (Dept_ID, Dept_Name)

​

Teacher (Teacher_ID, First_Name, Last_Name, Gender, Salary, Date_of_Birth, Dept_No)

CREATE TABLE Teacher (

Teacher_ID INTEGER PRIMARY KEY,

First_Name VARCHAR(20) NOT NULL,

Last_Name VARCHAR(20),

Gender CHAR(1),

Salary DECIMAL(10,2) DEFAULT 40000,

Date_of_Birth DATE,

Dept_No INTEGER,

FOREIGN KEY (Dept_No) REFERENCES Department(Dept_ID)

);

Some other commands use for Referential Integrity constraints

1. SET NULL
2. CASCADE
3. RESTRICT

Use for “on delete” and “on update” command

1. Set Null command:-

If you want to set Dept_no of Teacher table set to NULL when Dept_ID of Department table is either deleted or updated then

2.CASCADE command:-

If you want when Dept_ID attribute in Department table is deleted, then the corresponding rows in the Teacher table would also be deleted and when Dept_ID attribute of department table is update then the change in corresponding value is also reflected in Dept_No of Teacher Table.

3.RESTRICT command:-

RESTRICT option will reject the delete or update operation for the referenced table(parent table) if there are one or more related foreign key values in a referencing table(child table).

CREATE TABLE Teacher (

Teacher_ID INTEGER PRIMARY KEY,

First_Name VARCHAR(20) NOT NULL,

Last_Name VARCHAR(20),

Gender CHAR(1),

Salary DECIMAL(10,2) DEFAULT 40000,

Date_of_Birth DATE,

Dept_No INTEGER,

FOREIGN KEY (Dept_No) REFERENCES Department (Dept_ID) ON DELETE RESTRICT ON UPDATE RESTRICT

​

);

Note:-

Self-Referencing Tables:- When a table referencing itself then, it is known as self referencing table.

DDL(Data Definition Language)

4.Drop table command:-

This command is used to delete tables.

​

Syntax:- Drop table <table name>;

Example:- Drop table Teacher;

​

(i)DROP TABLE Teacher CASCADE;

Thus Teacher table would be dropped and with the CASCADE option, i.e. all the constraints that refer this table would also be automatically dropped.

​

(ii)DROP TABLE Teacher RESTRICT;

if the requirement is that the table should not be dropped if it is being referenced in some other table then RESTRICT option can be used.

DDL(Data Definition Language)

5.Alter command:-

This command is used to modify the table definition in four different way I,e

• Adding a column
• Dropping a column
• Modify a column
• Rename a column

1. Adding a column:-

This command is use To add a column in a existing table.

Syntax:-

ALTER TABLE <table name> ADD <column name > datatype;

Example:-

ALTER TABLE Teacher ADD Pran_no int;

Add Primary Key using Add command

Syntax:-

ALTER TABEL <table name> ADD PRIMARY KEY(column name);

(b) Droping a column:-

This command is use To remove a column in a existing table.

Syntax:-

ALTER TABLE <table name> DROP COLUMN<column name > ;

Example:-

ALTER TABLE Teacher DROP COLUMN Last_Name;

Remove Primary Key using drop command

Syntax:-

ALTER TABLE <table name> DROP PRIMARY KEY;

Example:-

(C) modify/alter a column:-

This command is use To modify the datatType of a column in a existing table.

Syntax:-

ALTER TABLE <table name> MODIFY COLUMN<column name > datatype ;

alter a column:-

A column definition can also be altered. For example – dropping the default value or defining a new default value

(d)

Rename a column

This column is used to rename a column in a table

Syntax:-

ALTER TABLE <table_name> RENAME COLUMN <old_name> to <new_name>;

SQL

DDL

DML

DQL

TCL

DDL(Data Definition Language)

5.Drop table command:-

This command is used to remove entire table with data.

Syntax:-

Drop table <table name>;

​

5.Truncate command:-

This command is used to remove only data present inside the table.

Syntax:-

Truncate <table name>;

DML(Data Manipulation Language)

There are three type of DML commands I,e

• Insert Into
• Delete
• update