DBMS

DBMS Concepts TRAINING MODULE

Learning Objectives Understand the Recall the Explain the Summarize fundamentals of functions of Master SQL the concept database design, (Structured Query DBMS principles of Language) of DBMS DBMS

INTRODUCTION TO DATABASE SYSTEM Data : Known Facts that can be recorded and have an implicit meaning Database: A Database is an organized collection of related data stored on disk and can be accessible by many concurrent users DBMS : A set of programsthat manage any number of databases Database System:The DBMS Software works together with the data itself. Sometimes, the Applications are alsoincluded

INTRODUCTION TO DATABASE SYSTEM accessing Inserting , data updating and deleting data security, A DBMS is backup, responsible recovery for Integrity , availability, performance

Managing Data 2 There are two methods to manage data: 1 File Based Approach Database Approach • An approach that utilizes a • An approach that data is collected collection of application programs and manipulated using specific software called Database which performs services to end- Management System, and many users(eg.Reports) . programs share this data • Each program defines and manages its own data

File Based Approach

File Based Approach Centralization Data shared by Provision of Representatios Intregrity Advanced of information different multiple of complex constraint facilities for interfaces relationships handling backup and managment groups of users and application 3 between data 5 recovery 1 programs 4 6 2

Difference between DBMS vs Flat file system

Difference between DBMS vs Flat file system Query Ability Backup and Migrate Redundancy Control Multiple User Interfaces Access control Integrity Constraints Application Development Option to store Persistent Objects Time is reduced

Typical DBMS Functionality 01 02 03 04 Define a particular Construct or Load Manipulates the Share a database database in terms the initial database database by allows multiple users and program of its data contents on a retrieval,modificati to access the types,structures,an storage medium on,accessingthe database through database d constraints web applications simultaneously

Database Schema

Data Independence

Types of Database Schema A database schema can be divided broadly into two categories − 12 Physical Database Logical Database Schema Schema This schema pertains to the actual This schema defines all the logical storage of data and its form of storage constraints that need to be applied on the data stored. It defines tables, views, like files, indices, etc. It defines how the data will be stored in a secondary and integrity constraints. storage.

DBMS Architecture • The design of a DBMS depends on its architecture • Single Tier and Multi-tier • Single Tier : The DBMS is the only entity where the user directly sits on the same system and uses it . It is called Single tier Architecture.Database Designers and Programmers preferthis • Ex : MS-ACCESS

General DBMS Architecture

General DBMS Architecture Time of Huge \"Mainframe\" All processing in single computer All resource attached to the same computer Access via Dumb terminal

Client/Server Architecture

Client/Server Architecture In this architecture, users access the Database Server through a client applicationsuch as sqlplus, sql developer etc., Direct Communication between the Data Source and the Client Application User Interface Program and Application Programs runs on Client Side through ODBC (Open Database Connectivity)

Advantages & Disadvantages of Client/Server Advantages Disadvantages Easy to maintain Faster Communication Cost- Ineffective Performance is less for higher number of users

3- Tier Architecture

Three Schema Architecture







3-Tier Architecture 1 This tier is used for Business Applications like web-based . It Consists of ClientLayer, Applications Layer andData Layer 2 The Client Layer (Presentation Layer) consists of User Interface (UI) and used for design purpose . The End users operate on this tier The Application Layer Hides the Physical storage structures from the End user and 3 describesentities, data types, relationships and Constraints The Data Layer consists of Internal View of Data and its representation method like Storage 4 Allocation and Access Paths etc., File Organization, Access Paths like Indexes, data compression techniques, encryption methods and 5 record placement The information is received and stored in a file system or database . This information is processed in 6 the Logical Tier and returned back to the Presentation Layer for Users

Types of Databases Operational End User Centralized Distributed Personal Commercial Related to the These 1. These Databases store The processing of The databases are These databases are Operations of the Databasescontain entire information and data is done on two maintained on rent out for particular information for End levels : on the Client category of users . Eg: Enterprise Eg: Users like Managers Application Programs at a Side and the Server Personal Computers . Shock Markets, Foreign Marketing, at different levels CentralComputing Facility . For Eg: giving Summary Users from different locations Side . Eg: Exchange etc., Production, Sales, HR access this database . Eg: Client/Server Departmentslike HR, Information Architecture Sales etc., Indian Railways

INTRODUCTION TO DATA MODELING

DATA MODELS REPRESENT INTRODUCTION THELOGICAL OR TO CONCEPTUALREPRESENTATION OF DATA DATA MODELING DATA MODELS ARE HELPFUL IN DATA ABSTRACTION DATA MODELS ARE USEFUL IN PROCESSING AND STORING DATA THE PREDOMINANTLY USED DATA MODELS ARE : 1. HIERARCHICAL MODEL 2. NETWORK MODEL 3. RELATIONAL MODEL

Hierarchical Model(Inverted Tree)

Hierarchical Model Each entity can have a single parent but several children At thetop of hierarchythere is an entity called Root. Data is organized like an organization chart Each node represents an entity and its subordinate entity represents the next level of hierarchical tree Each entity is equivalent of a table. The record is represented by row and attribute by column The relationship depicted is 1:N mapping

Network Model Entities are Some entities can It is slow, complex organized in a be accessed and more difficult graph . viavarious paths to maintain i.e., M:N relationships

Network Model Data are organized in the two dimensional tables called relations Eg: Oracle, MySQL, SQLServer

Entity – E-R Model The attributes Relationship represent the Model defines the E-R modeldefines propertiesof the conceptual view of the realworld a database objects as entities Entitiesand their association as Relationship

E-R Model The student is an entity and its attributes are Name, Roll_No, BirthDate , PhoneNo etc.,

Three Types of Anomalies Emp_id Emp_name Emp_Addr Emp_Dept 101 101 Senthil Delhi D001 123 166 Senthil Delhi D002 Siva Madurai D890 Murugan D004 Chennai

Three Anomalies Update Anomaly Insertion Anomaly Deletion Anomaly If we wantto update If an employee is If the company the address of Senthil given training and yet closes a department , weshould update all and wants to delete to assign a all the records of that the rows but if we department , then We department then the leave unattended any row then it becomes cannot insert his employee siva’s record with dept records have to be inconsistent deleted since he is in name blank a single dept

Normalization Normalization is the process of removing redundancies , anomalies etc., • First Normal Form : Every attribute of a table must be atomic i.e . Non- divisible

After First Normal Form First Normal Form

After First Normal Form It states that every Non-Prime Attribute Should be fully functionally dependent on all prime attributes Before Second Normal Form: Stu_Name depends on Stu_ID only and not Proj_ID and hence it is partially dependent

After Second Normal Form The below relation Student_Project is divided into two tables : Student and Project

Third Normal Form • It should hold Second Normal Form • It Should not be transitively dependent

After Third Normal Form

SQL BASIC OPERATIONS DDL Command- DML Command- DRL Command- DDL Command- TCL Command- Data Definition Data Data Retrieval Data Definition Transaction Control Language Manipulation Language Language Language Language

DDL Commands • These are Auto Commit Commands • The datatypes can be char, varchar, varchar2, number,long, date • create , alter , drop, truncate • create - To create • alter - To alter a Table . • drop table stud; a table alter tablestud add(saddr Drops the entire table char(10), smobno number); • truncate - truncates the • create table . The contents are tablestud(sid • alter table studrename deleted maintaining its number, sname column saddr to sadd; varchar2(20)); • alter table stud drop structure as it is • desc stud; column sadd; • truncate table stud; • alter table stud modify (sadd varchar(20));

DML Commands DML Commands are : • insert – inserts • update - updates the • delete – deletes the insert, update, delete. records into database database records records selected These commands are to selectively or as a rows or as a whole • insert into stud whole be committed values(&sid,’&sname’); • update studset • delete from table sname=‘thiagu’ where sid=543; • insert into stud where sid=543; sid(543) ; • commit; • delete from stud; • commit; • commit;

DRL Commands select statement is DRL select sid,sname from stud; select * from stud; select * from stud where no>453;

TCL Commands commit; whenever a transaction is committed it is written into the database rollback; This is equivalent to undo

DCL Commands grant and revoke • grant – grants • revoke – takes back the permissions, permissions,privileges, roles and profiles from privileges,roles and the users profiles to users • revoke connect,resource • grant connect,resource to from user1; user1;

DCL Commands • To give conditions according to our needs • There are three types of constraints: Domain not null, check, default Types of Entity primary key , unique key constraints Referential foreign key

Constraints DOMAIN • not null – This constraint prohibits the database to have null values • default – This constraint assigns default values to the columns • check – This constraint checks the value of some specific column ENTITY • primary key – combination of unique and not null constraints • unique constraint – This constraint does not allow repeated values in the same column or group of columns REFERENTIA L • foreign key – A parent table references a child table