relational data base management system

RDBMS Concepts:

08-03-2011 II MBA RDBMS Concepts MIS

Agenda:

6-Jan-04 Satyam Computer Services Ltd. Agenda Introduction Database Management Systems Normalization Codd’s Rules SQL

Introduction:

6-Jan-04 Satyam Computer Services Ltd. Introduction

Slide 4:

6-Jan-04 Satyam Computer Services Ltd. Data? Information?

Initial Data Storage Methods:

6-Jan-04 Satyam Computer Services Ltd. Initial Data Storage Methods Data is represented in one or more flat files Flat file is nothing but electronic representation of cardboard file. Every business group has its own set of files

Disadvantages of Flat File Systems:

6-Jan-04 Satyam Computer Services Ltd. Disadvantages of Flat File Systems No centralized control. Data Redundancy Data Inconsistency Data can not be shared Standards can not be enforced Security issues Integrity can not be maintained Data dependence

Database Management Systems:

6-Jan-04 Satyam Computer Services Ltd. Database Management Systems

Database Management Systems:

6-Jan-04 Satyam Computer Services Ltd. Database Management Systems A system whose overall purpose is to record and maintain information A database is a repository for stored data and programs to manipulate it.

Advantages of DBMS:

6-Jan-04 Satyam Computer Services Ltd. Advantages of DBMS Centralized control. No Data Redundancy Data Consistency Data can be shared Standards can be enforced Security can be enforced Integrity can be maintained Data independence

Data Models:

6-Jan-04 Satyam Computer Services Ltd. Data Models A data model is a collection of concepts for describing data A Schema is a description of a particular collection of data using the given data model The relational model is the most widely used model today

Levels of Abstraction:

6-Jan-04 Satyam Computer Services Ltd. Levels of Abstraction Many Views and single Conceptual and Physical Schema Views Describe how users see the data Conceptual Schema defines the logical structure Physical Schema defines the physical files and Indexes

Example University Database:

6-Jan-04 Satyam Computer Services Ltd. Example University Database Conceptual Schema Students(sid: string, name: string, login: string, age: integer, gpa:real) Courses(cid: string, cname:string, credits:integer) Physical Schema Relations stored as unordered files. Index on first column of Students. External Schema (View): Course_info(cid:string,enrollment:integer)

Data Independence:

6-Jan-04 Satyam Computer Services Ltd. Data Independence Applications insulated from how data is structured and stored Logical Data Independence : Protection from changes in logical structure of data Physical Data Independence : Protection from changes in physical structure of data

Structure of a DBMS:

6-Jan-04 Satyam Computer Services Ltd. Structure of a DBMS

ACID Test:

6-Jan-04 Satyam Computer Services Ltd. ACID Test Atomicity Consistency Isolation Durability

Types of DBMS:

6-Jan-04 Satyam Computer Services Ltd. Types of DBMS Hierarchical Network Relational

Example Data:

6-Jan-04 Satyam Computer Services Ltd. Example Data

Hierarchical DBMS:

6-Jan-04 Satyam Computer Services Ltd. Hierarchical DBMS Data is represented by a tree structure P1 Nut Red 12 London S2 Jones 10 Paris 300 S1 Smith 20 London 300 P2 Bolt Green 17 Paris S3 Blake 30 Paris 200 S2 Jones 10 Paris 400 S1 Smith 20 London 200 P3 Screw Blue 17 Rome S1 Smith 20 London 400 P4 Screw Red 14 London

Hierarchical DBMS (Contd.):

6-Jan-04 Satyam Computer Services Ltd. Hierarchical DBMS (Contd.) Can not handle Many-Many relations Can not reflect all real life situations Anomalies in insert, delete and update operations.

Network DBMS:

6-Jan-04 Satyam Computer Services Ltd. Network DBMS Data is represented by records and pointers Addresses Many-Many relations Insert,delete,update operations possible Complex in design

Relational DBMS:

6-Jan-04 Satyam Computer Services Ltd. Relational DBMS Based on Relational Mathematics principles Data is represented in terms of rows and columns of a table Addresses all types of relations Easy to design No anomalies for insert/delete/update

Relational Terminology:

6-Jan-04 Satyam Computer Services Ltd. Relational Terminology Tuple (Row) Attribute (Column) Relation (Table) Integrity Constraints Primary Key Alternate Key Foreign Key

Normalization:

6-Jan-04 Satyam Computer Services Ltd. Normalization

Normalization:

6-Jan-04 Satyam Computer Services Ltd. Normalization Normalization - process of removing data redundancy by decomposing relations in a Database. De normalization - carefully introduced redundancy to improve query performance.

Normalization through decomposition:

6-Jan-04 Satyam Computer Services Ltd. Normalization through decomposition The decomposition approach starts with one relation and the relation is decomposed into more number of relations to remove insert, delete and update anomalies. 1NF, 2NF, 3NF and BCNF can be achieved by this approach.

Un normalized Form:

6-Jan-04 Satyam Computer Services Ltd. Un normalized Form A relation is said to be in Un normalized Form (0NF) if the values of any of its attributes are non-atomic. In other words more than one value is associated with each instance of the attribute.

Un normalized Relation:

6-Jan-04 Satyam Computer Services Ltd. Un normalized Relation

First Normal Form:

6-Jan-04 Satyam Computer Services Ltd. First Normal Form A Relation is said to be in First Normal Form (1 NF) if the values of each attribute of the relation are atomic. In other words, only one value is associated with each attribute and the value is not a set or a list of values.

First Normal Form:

6-Jan-04 Satyam Computer Services Ltd. First Normal Form

Functional Dependency:

6-Jan-04 Satyam Computer Services Ltd. Functional Dependency Given a relation R, attribute Y of R is functionally dependent on attribute X if and only if each X-value in R has associated with it precisely one Y-value in R (at any one time)

Full Functional Dependency:

6-Jan-04 Satyam Computer Services Ltd. Full Functional Dependency Attribute Y is fully functionally dependent on attribute X if it is functionally dependent on X And Functionally dependent on any proper subset of X

Second Normal Form:

6-Jan-04 Satyam Computer Services Ltd. Second Normal Form A relation R is in Second Normal Form (2 NF) if it is in the 1NF and every non key attribute is full functionally dependent on the primary key.

Third Normal Form:

6-Jan-04 Satyam Computer Services Ltd. Third Normal Form A relation R is in Third Normal Form (3 NF) if and only if it is in the 2NF and every non-key attribute is non-transitively dependent on the primary key.

Boyce Codd Normal Form:

6-Jan-04 Satyam Computer Services Ltd. Boyce Codd Normal Form A relation R is in Boyce/Codd Normal Form (BCNF) if and only if every determinant is a candidate key. An attribute, possibly composite, on which some other attribute is fully functionally dependent is a determinant .

Slide 35:

6-Jan-04 Satyam Computer Services Ltd. A subject can be taught to a student by only one teacher. Each teacher teaches only one subject. Each subject is taught by several teachers.

Slide 36:

6-Jan-04 Satyam Computer Services Ltd. No two students can get same position in same subject.

Codd’s Rules:

6-Jan-04 Satyam Computer Services Ltd. Codd’s Rules

Codd’s Rules:

6-Jan-04 Satyam Computer Services Ltd. Codd’s Rules

Structured Query Language:

6-Jan-04 Satyam Computer Services Ltd. Structured Query Language

Structured Query Language:

6-Jan-04 Satyam Computer Services Ltd. Structured Query Language DDL – Data Definition Language DML – Data Manipulation language DCL – Data Control Language

DDL:

6-Jan-04 Satyam Computer Services Ltd. DDL Create Alter Drop Truncate

DML:

6-Jan-04 Satyam Computer Services Ltd. DML Insert Update Delete Select

DCL:

6-Jan-04 Satyam Computer Services Ltd. DCL Commit Rollback Save point Set transaction

Integrity Constraints:

6-Jan-04 Satyam Computer Services Ltd. Integrity Constraints Primary key (PK) Foreign Key (FK) Unique key (UK) Not Null Check

Data Types:

6-Jan-04 Satyam Computer Services Ltd. Data Types Character Varchar2 Number Date BLOB BFILE

Arithmetic Operator:

6-Jan-04 Satyam Computer Services Ltd. Arithmetic Operator + - * / Mod ABS

Logical Operators:

6-Jan-04 Satyam Computer Services Ltd. Logical Operators AND OR IN NOT IN < > <= >= <> BETWEEN

Set Operators:

6-Jan-04 Satyam Computer Services Ltd. Set Operators UNION UNION ALL INTERSECTION MINUS

Thank You:

6-Jan-04 Satyam Computer Services Ltd. Thank You