DBMS BASICS

Database :

· A database is an organized collection of data that is useful to us.

· The data inside in a database can be modified, deleted or new data can be added.

· Database is nothing but a collection of inter-related tables.

· It is also called as STORAGE MANAGER

TABLE: A table consists of rows and columns.

ROWS: Rows are the records stored

COLUMNS: Attributes or fields.

Data Type: A data type is a set of data with values having predefined characteristics. Each language has its own data types. Usually, a limited number of such data types come built into a language

SQL domain Types

SQL supports the following domain types.

1) Char(n) 2) varchar(n) 3) int 4) numeric(p,d)

5) float(n) 6) date

Aggregate functions are functions that take a collection of values as input and return a single value.

Aggregate functions supported by SQL are

· Average: avg

· Minimum: min

· Maximum: max

· Total: sum

· Count: count

GROUP BY. was added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called, and without the GROUP BY function it was impossible to find the sum for each individual group of column values

CURSORS:

A cursor is a private SQL area used to execute SQL statements and store processing information. PL/SQL implicitly uses cursors for all DML commands and SELECT command that returns only one row. And it also allows you to define explicit cursor to deal with multiple row queries

TYPES OF CURSORS:

1. IMPLICIT CURSORS.

2. EXPLICIT CURSORS.

TRIGGERS:

Triggers are statements that are executed automatically by the system as the side effect of a modification to the database

categories of SQL commanD:

SQL commands are divided in to the following categories:

1. Data - definitition language.

2. Data manipulation language

3. Data Query language

4. Data control language

5. Data administration statements

6. Transaction control statements

Three classes of SQL expression

SQL expression consists of three clauses:

· Select

· From

· where

parts of SQL language:

The SQL language has several parts:

· Data - definitition language

· Data manipulation language

· View definition

· Transaction control

· Embedded SQL

· Integrity

· Authorization

query language

A query is a statement requesting the retrieval of information. The portion of

DML that involves information retrieval is called a query language

applications of DBMS:

a) Banking

b) Airlines

c) Universities

d) Credit card transactions

e) Tele communication

f) Finance

g) Sales

h) Manufacturing

i) Human resources

Disadvantages of file processing system

The disadvantages of file processing systems are

a) Data redundancy and inconsistency

b) Difficulty in accessing data

c) Data isolation

d) Integrity problems

e) Atomicity problems

f) Concurrent access anomalies

advantages of using a DBMS

The advantages of using a DBMS are

a) Controlling redundancy

b) Restricting unauthorized access

c) Providing multiple user interfaces

d) Enforcing integrity constraints.

e) Providing back up and recovery

levels of data abstraction:

a) Physical level

b) logical level

c) view level

data structures implemented by the storage manager.

The storage manager implements the following data structure

a) Data files

b) Data dictionary

c) indices

data dictionary

A data dictionary is a data structure which stores meta data about the structure of

the database ie. the schema of the database

entity relationship model

The entity relationship model is a collection of basic objects called entities and relationship among those objects. An entity is a thing or object in the real world that is distinguishable from other objects.

Attributes

An entity is represented by a set of attributes. Attributes are descriptive properties possessed by each member of an entity set.

Example: possible attributes of customer entity are customer name, customer id,customer street, customer city.

TYPES OF ATTRIBUTES:

· SINGLE VALUED

· MULTI VALUED

· STORED

· DERIVED

· COMPOSITE

· NULL

privileges in SQL

· Delete

· Select

· Insert

· Update

OBJect-oriented data model

The object-oriented paradigm is based on encapsulation of data and code related to an object in to a single unit, whose contents are not visible to the outside world.

DDL(DATA DEFINITION LANGUAGE):

· Create Table ---- creates a new database table.

· Alter Table ----alters a database table.

· Drop Table : ----deletes a database table.

· Create Index ----: creates an index.

· Drop Index : ---- deletes an index.

Data Manipulation Language (DML)

1. SQL can be termed as DML i.e. Data Manipulation Language.

2. Database is manipulated so as to insert, update and delete records.

3. CREATE, ALTER or DROP a database, table, index etc.

This section also describes SELECT INTO which is used create a back up of a database table.

· Select :Retrieves desired records from a database table.

· Update : Updates/modifies records in a database table

· Delete : Deletes records from a database table.

· Insert into: Inserts new records into a database table.

Data Control Language (DCL)

1. In this section we will be using commands that control the behavior of database objects. Although DCL form a part of PL/SQL part, they have been described in brief here.

2. The commands have been described one at a time, covering all the clauses.

The commands have been described with appropriate example one at a time covering all the clauses before we go on to discuss Data Control Language (DCL) commands