Introduction to DBMS

 

Introduction to DBMS

Database is collection of interrelated data

A Database Management System (DBMS) is a software for creating and managing databases.

Some Applications of DBMS

1)      Railway Reservation System : Database is required to keep record of ticket booking, train’s departure and arrival status, status of seats available etc.

2)       Library Management System :  There are thousands of books in the library so it is very difficult to keep a record of all the books in a copy or register.

3)      Banking : People make thousands of  transactions through banks daily and they can do this without going to the bank.

 

Advantages of DBMS

1)      Reducing Data Redundancy : The file based data management systems contained multiple files that were stored in many different locations in a system or even across multiple systems.

2)      Sharing of Data : In a database, the users of the database can share the data among themselves.

3)      Data Integrity : Data integrity means that the data is accurate and consistent in the database

4)      Data Security : Data Security is vital concept in a database. Only authorised users should be allowed to access the database and their identity should be authenticated using a username and password.

5)      Privacy : The privacy rule in a database means only the authorized users can access a database according to its privacy constraints.

6)      Backup and Recovery : Database Management System automatically takes care of backup and recovery.

 

Data types in the DBMS

 When you create a table or add a field to a table in the database, fields are created with specific data type. Data types are classifications that identify possible values for and operations that can be done on the data, as well as the way the data in that field is stored in the database.

1)      CHAR-Holds a fixed length string (can contain letters, numbers,

2)      VARCHAR-Holds a variable length string (can contain letters, numbers, and special characters).

3)      DECIMAL-It can represent numbers with or without the fractional part

4)      INT- It is used for storing integer values

5)      DATE-t holds the date  including day, month and year

6)      TIME()-It holds time. Format: HH:MM:SS

 

Properties of Transactions : Transactions have the following four standard properties, usually referred to by the acronym ACID.

1)      Atomicity : It ensures that all operations within the work unit are completed successfully. Otherwise, the transaction is aborted at the point of failure and all the previous operations are rolled back to their former state.

2)      Consistency : It ensures that the database properly changes states upon a successfully committed transaction.

3)       Isolation : It enables transactions to  operate independently of and transparent to each other.

4)       Durability : It ensures that the result or effect of a committed transaction persists in case of a system failure.

 

Introduction of RDBMS

 RDBMS stands for Relational Database Management System. In RDBMS a database is considered as a collection of interrelated data.

Table : The table is the basic data storage unit in a Relational database. Table consists of columns and rows.

Field : A table consists of information which is stored under different headings, called as fields or columns.

Record : All the columns in a table make a row. Each row contains information on individual topics.

Primary Key : The group of one or more columns used to uniquely identify each row of a relation is called its Primary Key.

  Foreign Key : It is a field (or collection of fields) in one table that refers to the Primary Key in another table.

 

Relationships in database : Relationships link data from individual tables to increase the usefulness of the database.  A relationship in the context of databases, is a situation that exists  between two relational database tables when one table has a foreign key that  is used as a reference to the primary key of the other table.

 

There are 3 types of relationships in relational database design. They are :

1)  One-to-One            2)One-to-Many (or Many-to-One)      3)  Many-to-Many

1) One-to-One : In a one-to-one relationship, one record in a table is associated with one and only one record in another table

2) One to many- one record in a table is associated with many record in another table.

3) Many-to-Many A many-to-many relationship occurs when multiple records in a table are associated with multiple records in another table

 

Introduction to SQL

SQL is Structured Query Language,  which is a computer language for storing, manipulating and retrieving data stored in a relational database. SQL is the standard language for Relational Database Management System.

Using SQL we  Can :

1)      Create new databases.

2)        Create new tables in a database

3)        Insert records in a database  

4)        Retrieve data from a database

5)       Update records in a database

Categories of SQL Commands

1)      Data Definition Language (DDL) Commands DDL statements or commands are used to define and modify the database structure of your tables or schema.

DDL COMMAND USED FOR

CREATE DATABASE -Creates database

CREATE TABLE- Creates a new table

ALTER TABLE- Modifies a table

DROP TABLE/DROP DATABASE-Deletes a table or Database

 

2)      Data Manipulation Language (DML) Commands Data Manipulation Language (DML) statements or commands are used for managing data within tables.

SELECT -Extracts data from a table

UPDATE -Updates data in a table

DELETE- Deletes data from a table

INSERT INTO-Insert data into a table

 

3)      Data Control Language (DCL) Commands-DCL is used to control user access in a database. it is related to security issue. it is also deals with the rights and permissions of the databse access.

GRANT -To provide access or privileges on the database objects.

REVOKE -To remove access rights or privileges on the database object.