0% found this document useful (0 votes)
15K views296 pages(DBMS) Database Management System Text Book
Uploaded by
LolCopyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
0% found this document useful (0 votes)
15K views296 pages(DBMS) Database Management System Text Book
Uploaded by
LolCopyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
/ 296
Database Management
Systems
‘Semester V - Computer Engineering
(Savitribai Phule Pune University)
Pankaj B. Brahmankar
Director, Phoenix InfoTech, Pune,
Maharashtra, India.
"Mech-Maxransin ne
Innovation Throughout : |
Engineering Division _ nt Database Management Systeras
‘(Semester V - Computer Engineering, ‘Savitribai Phule Pune University)
Pankaj B. Brahmankar
Copyright © by Author. All rights reserved. No patt of this publicetion may be reproduced, copied, or stored in a retrieval
system, distributed or transmitted in any form or by any means, including photocopy, ‘evordng, or oher electronic or
mechanical methods, without the prior written perinission of the publisher.
‘This book is sold subject to the condition that it shall not, by the way of trade or otherwise, be lent, esold, hired out, or
‘otherwise circulated without the publisher's prior writen consent in any form of binding or cover othe than which itis
published and without a similar condition including this condition being imposed on the subsequent purchaser and without
limiting the rights under copycight reserved above.
Sune 2017
June 2018
‘This edition is for sale ia india, Bangladesh, Bhutan, Maldives, Nepal, Pakistan, Sri Lanke and designated countces in
‘South-East Asia, Sale and purchase ofthis book ontside of these counties is unauthorized by the publisher,
Printed at : Image Off, Dugune Ind. Area, Survey No’28/25, Dhayai, Near Pari Company,
Pune -41, Maharashtra State, India, E-wail:rhulshahimage @ gmail.com
ISBN. 978-93-5224-577.2
Published by /
‘Tech-Max Publications /
Head Oftice : B/S, First floor, Maniraina Complex, Taware Colony, Aranyeshwar Corner,
Pune - 411009. Maharashtra State, Indian
Pha: 91-20-24225065, 91-20-24217965. Fax 020-24228978,
Email info@techmaxbooks.com,
‘Website : www techmaxbooks.com
£310242] (FID : TP463) (Book Code : PO265B) ‘My dear students,
Tam extremely happy to come out with this book on “Database Management
Systems?” or the students. This Sook has been silly writen as per the syllabus, Lbave
divided the syllabus into small chapters so that the topics can be arranged and
understood properly. The topics within the chapters have been arranged in a proper
sequence to ensure smooth flow of the subject.
T am thankful to Shri. Pradeep Lunawat and Shri. Sachin Shah for the
exicouragement and support that they have exiended. I am also thankful co the staff
membérs of Tech-Max Publications and others for their efforts to make this book as
‘g00d as it is. We have joinily made every possible efforts to eliminate all the errors in
this book. However if you find any, please let me know, because that will help me to
improve further.
Tam also thankful to my family members and friends for ‘patience and
encouragement. 310242 : Database Management Systems
Prerequisites Courses
Diserete Mathematics (210241), Data Structures (210243 & 210252)
Companion Course
Database Management System Lat (310247)
Course Objectives
‘To understand the fundamental concepts of database management, These concepts include aspecis of database design,
database languages, and database-system implementation,
‘To provide a strong formal foundation in database Concepts, echaology and practice,
‘To give systematic database design approaches covering conceptual design logical design and an overview of
physical design.
Be familiar with te basic issues of transaction processing and concurrency contro.
‘To lear and understand various Database Architectures sind Applications.
To lear a powerful, flexible and scalable general purpose database to hanile big data,
Course Outcomes
}On completion ofthe course, student willbe able 10 -
Design E-R Model forgiven roquireaneats aid conver the same into database tabies,
Use database techniques such as SQL gPLsaL.
‘Use modem database techniques such as NoSQL.
‘Explain transaction Management in relational database System.
‘Describe diftereat database architecture and analyses the use of appropriate architecture in realtime environment.
‘Use advanced database Programming concepts . Course Contents
Unit |: introduction (07 Hours)|
fuwroduetion to Database Management Systems, Purpose of Database Systems, Database System Applications,
View of Data, Database Languages, Database System Structure, Data Models, Database Design and ER Model :
Entity, Atributes, Relationships, Constraints, Keys, Design Provess, Entity Relationship Model, ER Diagram,
Design Issucs, Extended E-R Features, converting E-R & EER diagram into tables.
Unit il: SQL AND PLUISQL
SQL : Characteristics and advantages, SQL Data Types and Literals, DDL, DML, DCL, TCL, SQL Operators,
‘Tables : Croating, Modifying, Deleting, Views: Creating, Dropping, Updating using Views, Indexes, SQL DML]
Queries : SELECT Query and clauses, Set Operations, Predicates and Joins, Set membership, Tuple Vaciables,
Set comparison, Ordering of Tuples, Aggregate Functions, Nested Queries, Database Modification using SQL
Tnsert, Update and Delete Queries. PL/SQL + concept of Stored Procedures & Functions, Cursors, Trigger,
Assertions, roles and privileges , Embedded SQL, Dynamic SQL. {Refer Chapter 2)
Unit it : Relational Catabase Design (08 Hours)|
Relational Mode! ; Basic concepts, Attributes and Domains, CODD's Rules, Relational Integrity: Domain,
Refereatial Lategnities, Enterprise Constraiats, Database Design : Features of Good Relational Designs,
Normalization, Atomic Domains and First Normal Form, Decomposition using Functional Dependencies, |
Algorithms for Decomposition, 2NF, 3NF, BCNF, Modeling Temporal Data. (Refer Chapter 3)
[Unit 'V : Database Transactions and Query Processing (08 Hours)
Basic concept ofa Transaction, Transaction Management, Properties of Transactions, Concept of Schedule, Serial
Schedule, Serializability : Conflict and View, Cascaded Aborts, Recoverable and Non-recoverable Schedules,
Concurrency Control : Need, Locking Methods, Deadlocks, Time-stamnping Methods, Recovery methods :
‘Shadow-Poging and Log-Based Recovery, Checkpoints, Query Precessing, Query Optimization, Peformance
‘Taning
[Unit V : Parallel and Distributed Databases:
Introduction to Database Architectures: Multi-user DBMS Architectures, Case study- Oracle Architecture,
Parallel Databases : Speedup nd Seale wp, Arcitectures of Parallel Databases.
Distributed Databases : Architecture of Distributed Databases, Distributed Database Design, Distibuted Data|
Storage, Distributed Transaction : Basics, Failure modes, Commit Protocols, Concurency Contol in istibuted
Darabase, (Refer Chapter 5)
Unit Vi : NoSQL Database
Introduction to NoSQL Database, Types and examples of NoSQL Database - Key value store, document store,
‘graph, Performance, Sirictured verses unstructured data, Distributed Database Model, CAP theorem and BASE
Properties, Comparative study of SQL and NoSQL, NoSQL Data Models, Case Study-unstructured data from
social media. introduction to Big Data, HADOOP : HDFS, MapReduce. (Refer Chapter 6)
i
| 310247 : Database Management System Lab
‘Teaching Scheme Credits Examination Scheme
Practical : 4 Hours/Week 02 Practical
‘Term Work
30 Marks
25 Marks
‘Companion Course
Database Management System (310242)
Course Objectives
«To develop basic, intermediate and advanced Database programming skills.
|e To develop basic Database adrninistration skills:
To percept transaction processing
Course Outcomes
1On completion of the course, student willbe able to ~
Je Develop the ability to handle databases of varying complexities
Js Use advanced database Programming concepts
Guidelines for instructor's Manual
“The instnictor's manual is fo be developed as a hands-on resource and reference, The instructor's manval need to
‘include prologue (about Universty/program institute! departmendforeword preface etc), University syllabus,
conduction & Asseesment guidelines, topics under considration-coucept, objectives, ovtvomes, set of typical
applications/assignmeats/ guidelines, and references.
Guidelines for Student's Journal
‘The laboratory assignments are to be submited by student in the form of journal, Joumal consists of prologue,
Certificate, table of contents, and handwritten write-up of each assigament (Title, Objectives, Problem Statement,
Outcomes, sofware & Hardware requirements, Date of Completion, Assessment glade/marks and assessor's sign,
‘Theory- Concept in brief, Database design, test cases, conchsion/analysis. Program codes with sample output of al
performed assignmenis are tobe submitted as softeop.
yy lab Tn-charge is highly encouraged. For reference one or two journals may be maintained with
program prints at Laboratory.“
Guidelines for Assessment
‘Continuous assessment of laboratory work is done based on overall performance and lab assignments perforiaence
of student. Each lab assignment assessment will assign gradefmarks based on parameters with appropriate
‘weightage. Suggested parameters for overall assessment as well as cach lab assignment assessment inclade- timely
‘completion, perforraace, innovation, efficient codes, punctuality and neatnes.
Guidelines for Practical Examination
‘Both interaah and external examiners should jointly set problem statement. During practical assessment, the expert
‘evaluator sbould give the maximum weightage tothe satisfactory implementation of the problem statement. The
Supplementary and relevant questions may be asked at ube tm of evaluation to test the studeu's for advanced
Teaming, understanding of the fundamentals, effective and efficient implementation. $o encouraging ffors,
ttansperent evaluation and fair approel of the evaluator wil not create any uncertainty or doubt.n the minds of the
‘udents. So adhering to these principles will consummate our team efforts to the promising start of the studeat's
academies
t
I he
Guidelines for Laboratory Conduction
‘The insirucor is expected to fran the assignments by understanding the prerequisites, echnological aspects,
uflty and recent trends related to the topic, The assignment framing policy need to address the average students
and inclusive of an element to attract and promote the intelligent students. The instructor may set multiple sets of
assignments and distribute among batches of students. It is appreciated ifthe assignments are based on real world
problems/applications. Encourage students for appropriate use of Hungarian notation, proper indentation and
‘comments. Use of open source software is to be encouraged. In addition to these, instructor may assign one real life
application in the form of « mini-project based on the concepts learned, Instructor may also set one assignment or
rmini-project that is suitable to respective branch beyond the scope of syllabus.
Set of suggested assignment list is provided in groups- A and B, Bach student must perform atleast 13 assignments
(@-Mandotory plus 4 from remaining & assignments) from group A, from group B and 2 mini projects from
Group C
‘Operating System recommended :- 64-bit Open souzse Linux or its derivative
Programming tools recommended : SQL, PLISQL, Front End: Java/Per/PHP/Pythow/Ruby/.net, Backend
Monod/MYSQLOrecle, Database Connectivity : ODBCIDBC
Suggested List of Laboratory Assignments
(Group A : Database Prograrnming Languages - SOL, PUSQL
1.__ | Study of Open Source Relational Databases : MySQL.
2 | Design and Develop SQL DDL statements which demonstrate the use of SQL objects such as Table,
View, index, Sequence, Synonym.
3, | Design at least 10 SQL queries for suitable database application using SQL DML statements : Insert,
Select, Update, Delets with operators, functions, and set operator.
4. | Design at ieast 10 SQL queries for suitable database application using SOL DML statements: al types of
in, Sub-Query and Viow.
5. | Unnamed PUSQL code block ; Use of Contol structure and Exception handling is mandatory.
‘Wite @ PLISQL block of code forthe folowing requirements :
Sohema
1. Borrower(Rollin, Name, Dateoflssue, NameofBook, Status)
2, Fne(Rol_o,Date Ant)
» Accept rofl_ad & name of book from user.
~~ Check the number of days (from date of issue), if days are between 15 to 30 then fine
amount will be Rs Sper day.
+ If no. of days>30, per day fine will be Rs 50 per day & for days less than 30, Rs. 5 per
ay.
+ Alor subiting the book, stats wil change fom Ito
‘+ tt. condition of fine is true, then details will be stored into fine table.
Frame the problem statement for wilting PL/SQL bluck nine with abave stalerment.
6. | Cursors: (Al ypes : implicit, Expfct, Cursor FOR Loop, Paramoterzed Cursor) White a PLISQL. block of
code using pavamaterized Cursor, that wil morge he data available in the newly created table N_RoliCall
‘ith the data avaiable inthe table O_RolCal If tho data in the fist table already exist inthe second tabla
‘hen that data should be skipped
Frame the separate problem statement for writing PLISQL block to Imploment all types of Cursors
intine with above statement, The problem statement should clearly state the requirements. PUSAQL Stored Procedure and Stored Function.
‘Write a Stored Procedure namely proc_Grade for the categorization of student. if marks scored by
students in examination is <=1500 and marks>=990 then student wil be placed in distinction category if
‘marks scored are between 989 and 900 catagory is fist class, if marks 899 and 825 catagory is Higher
Seeond Class
‘Waite a PLISQL block for using procedure creatad with above requirement,
Stud_Marksiname, total marks) Result(Rot, Namo, Class)
Frame the separate problem statement for writing PL/SQL Stored Procedure and function, inline
‘with above statement, The problem statement shoud clearly state the requirements,
Database Trigger (All Types : Row level and Statement level tiggers, Before and Alter Triggors). Wile a
database trigger on Library table. The Systam should keep track of the records that are being updated or
deleted, The old value of updated or deleted records should be added in Library_Aucit aie.
Frame the problem statement for writing Database Triggers of all types, intine with above
statement. The problem statement should clearly state the requirements.
Group B : Large Seale Databases
‘Stuy of Open Source NOSQL Database : MongoDB (Installation, Basic CRUD operations, Execution)
Design and Develop MongoDB Queries using CRUD operations. (Use CRUD operations, SAVE metnad,
logical operators)
implement aggregation and indexing with suitable example using MongoDB.
Implement Map reduces operation with suitable example using MongoD8.
Design and implement any § query using MongoDB
‘Greate simp objets and array objects using JSON
‘Encode and Decode JSON Objects using JavalPed/PHP/Pythoa/Ruby
‘Group C : Mini Project : Database Project Lite Cycle
‘Write a program to implement WogoDB database connectivity with PHPY python/Java Implement Database
navigation operations (edd, delete, ed ete.) using ODBCLUDBC,
Implement MYSQUOresle database connectivity with PHPY pythoniJava Implement Database navigation
‘poratone (add, delete, ed.) using ODBCHIDEC.
Using the database concepts covered in Part! & Partll & connectivity concepts covered in Part C,
students in group are expected to design/and develop database application with folowing detalls:
‘Requirement Gathering and Scope finalization
Database Analysis and Design
‘* Design Entity Relationship Model, Relational Model, Database Normalization
| implementation
‘© Front End : Jave/PeMiPHP/Python Ruby/.net
Backend: MongeDBMYSQLOracie
¢ ~~ Database Connectivity : ODBC/JDBC
‘Testing : Data Validation
Group of students should submit the Project Report witich will be consist of documentation related 10
Uilferout phases of Software Development Life Cycle ; Till of the Project, Abstract, Intuckicion, supe,
Requirements, Data Modeling features, Data Dictionary, Relational Database Design, Database
Normalization, Graphical User Interface, Source Cade, Testing document, Conclusion. instructor should
rata progress repo of min project throughout the semester tom project group andl assign marks as a
art ofthe term work,
3 i
|
i
| gE Tabie of Contents
DeMs (SPPU-Como)
Chapter 1: Introduotion to DBMS. $1 101-98
Sytabus
Purpose of Database Systems, Database-System Asplicaions,
View of Data, Database Languages, Database System Struct,
Data Madals, Database Dosign and ER Model: Entiy, Atbutes,
Relatonships, Constraints, Keys,
RRelatonstip Moda, ER Diagram, Design issues, Extended E-F
Features, Converting ER & EER diagram ino tables.
124
122
123
124
125
13
ry
14a
wats
142
143
18
154
182
18
Ww
ara
waa
aan
1713.
1A
174s
1748
ta
tat
182
epee
+ Inoducton 10 Database Management “Systems,
Design Process, Entity
‘Syllabus Yople: Intoduction to Database
Management Systems, -
Insoduction to Database Management Systems.
‘Syllabus Tople : Purpose of Database Systems...
Purpose of Database Systems.
Fila Processing Sytem. o
(ravbacks of Traditional Fe Processing Systems... 12
Advantages of Dalabase Management System...
Cisadvartages of Database Management System,
Ditference betiteen File Processing and DBMS
{GPPU- Dec. 13, May 14, Dee. 15).
‘Syllabus Tople : Database-Sjstam Appcations...
Database-Systom Applicaton...
syllabus Tople: View Of Data.
ioe of Bat nso
sat een
Lal of Abstacon SPPU “Dee. 2).
Schema onmnnnnaeee 18
M208 oe Lan 18
‘Syllabus Tophe : Database Languages.
Database Languages.
(ata Datiition Language (ODL) wan ninnnninn 19
‘Data Manipulation Language (DML)... 8
‘Syllabus Topic : Database Syston StUctiT@ uuu 19
Database System Structure (SPPU - Dee. 13). Ww
‘Sylabus Topic : Data Moca. 140
Dat MOMS a 140
1490 of Data hc (SPU ay 13) co
Rolatona! Model.
ierrchcal Med cones MY
Netwock Database Modes...
Entity Retationstip (E-A) Meda
‘Object Oriented Database Model.
Phyical Data Medel. 49
Sys Tope: Oatabase Desk and ER Mode! 1-13
Database Design and ER Mode conse HB
Database Dasign en
ER Modo 14
senna
¥ Syllabus Topic: Entity
1.8.2.1 Entity and Enity Sat...
¥——Syflabus Tople = Atibutes
1822. Atrbete...
2 Sys Tope: Relatonsip...
1828 AaHIONSTIBE oncsessnninnsn
¥ Syllabus Tople : Constants... .
1824 Constraints 7
¥ Syllabus Tople : Keys... nan AR
1.825 Keys (SPPU-Deo. 18)... 4419
¥ Syllabus Tople : Design Pr20288 ener 720
1.8 Database Dasign Proooss sn 20
¥——_Sylabus Tople : Enty Relasonship Modo
110 Ently Relationship Model.
¥ Syllabus Tope : ER Diagram,
nis BN
121
mr
1at ERDiagram.. 121
1.114 Mapping Caray n£ agra
1112 Samples of ER Diagram
¥ —_Sylabue Tope: Design lose.
342 Design isues
Y-——Syabus Tope: Extend ER Fates,
113 Evonded BR Features
(SPPU- Dec. 13, May 14, Dac. 16.
LAB Speciizs80N nnn
1332 Goneralizaton..
1433 Aputagaton.
¥Sylabus Tole Cuverng ER EER Bagram
Into Tales... snes
114 Convering ER & EER Olagram nto Tables
(SPPU- May 19). ee
Syflabus : SOL + Characteristics and advantages, SQL Oata
‘Types and Literals, DL, DML, OGL, TCL, SOL Operas,
Tables: Creating, Moiying, Oseting, Vins: Creating, Dropping,
Updating using Views, Indexee, SQL DML Quorits; SELECT
‘Query and eiuses, Set Operations, Predicts and Joins, Set
‘membership, Tuple Variables, Set comparison, Ordering oF Tops,
Aggregate Functions, Nested Quavies, Calabase Modification
sing SOL ised, Updata and Oelete Qustes,
USGL: Concept of Stored Procedures & Functions, Cursors,
Triggers, Asserions, Roles “sic privileges, Embedded SOL,
Dynamic SOL.
¥ Syllabus Topo: SOL. cece BA
21 SOL Characteristics and Advantages. sat
¥ ——S¥llabus Tople : Characteristics of SOL. 22
RAN Characteristics of $b enon sna
Syllabus Topic : Advaniagas of SOL. 22
21.2 Advantages of SOL.
¥ Syllabus Topic : SOL Data Types... 22 |
|
242
243
24a
248.
2s
254
2saa
282
28a
28
26.
283
a7
274
272
273
ara
28
2e4
282
209
204
292
293
210
Sylabus Topic: SOL Leas
SL Lio..
Sylabus Tople DOL, ML OC, TCL.
DDL, DML, DCL, TCL (SPPU- May 13, May 14)
Data Det Langit (04)
‘Date Maniputation Language (DML) smn
Data Contro! Language (DCL)...
‘Transaction Control Language (TCL}.
‘Syllabus Topic : SOL Operators...
80. Op 88 nen
Athenee Ope nn
COMPANION OPETALOE ernie
Logical Operators ncnmninnnn
Bitwise Operators,
Compound Operators. v
Syllabus Tope :Tattes-Croting, Moding,
Deleting
Tables : Creating, Modiying, Deleting
Cretng Tabe..
Updating View.
‘View : Creating, Dropping Updating View
Slab ope: LOM cans Se ery
‘Syl Tople: Oates Modcabon sing
‘SOL Inser, Update and Dslota Ques...
Data Noda ig SO, hoe, pte
and Delete Quotes. eo BAS
vo BB
218
218
248
2416
2105
2802
2103
2.104
“
an
2a
aaa
ane
naa
2ansa
24s
2a
angr
2siee
2n123
[Ef opm (sPPU-com) “Tabla of Contents
thon
‘Uri At
IR
NES
Sylatue Topi: Peccatos and Joins. vee 27
Pracieats and Joins
Prodieates .
Comparisen Preicate
Between Prodicate
In Prodeale
Uke Preical.
‘sqNoT]NULL..
Joins (SPPU - Dec. 13, Mey 14)
Inner Jin (Equ4 Jon...
ter Join
SELF Jo
Syl Tope: Tl varabios
Tupe Variabas..
Sys Tope: Ondcng of Tus.
Concatof Sore Procadres and Puntos.
‘Stored Prooodures (SPPU - May 13}.
Tigger (SPPU- May 12).
Syfibus Tope: Asserion
Assertion.
Asserion Vs Tiggrs.
Sfisbus Tope : Roles and Prvlogos
Palos and Pavieges..
EE ne «sn
Prog... 7
‘Sytiaous Topke Embedded SOL,
Embedded $l osenne nn
Aavanlages of Embodied SOL,
Syllabus Topi : Dynaic SOL...
‘Oynamic SOL (SPPU - Dee. 19). DEMS (SPPU.Comp)
= Base concept, AtMBuaS and
Sorts ues. Ratonal tagity: Dorin, Rar
*Pfs9 Constants. Database Design Fostures cf
Designs, Nomalzaton, atic Domains and Fist
‘a Form, Decompositon ‘using Functional Dependencies,
Decomsiion, NF, ON, BCNF. Modeing
8 Tople : Codes Rus
Cocks Ruts (SPU tay 14,
S¥tlabus Topi: Raton nog.
Pelationalitegaty (SPPU- egy 13, Dee. 19).
8 Topic: Rational tog Domain.
Domain inogity Constants... cs
NO NULL. -
Datatase Design . esa BF
S¥ilabus Topic: Featros
Peto Orage me
Features of Good Relatonal Designs
Topic: Normatza... a)
Nomalzaon (SPPU -Dec. 4, Deo. 1, May 8). 310
Need of Normaizaon..
Anomalies...
Sy
nema Po
sua
3102
an
aan
ang
s24
Table of Contents
Fist Alonmal Form (INF) - 342
‘Sylabus Topie : Dacompestton using Functional
OepENENCI ns o sonnne AZ
Decomposition using Functional Dependency ...3-12
Functional Dependency cnn B12
“Types of Functional Dependencies 343
(Cosure of Functional Dependency... 918
Infersace Rules for Functonal Dependent. 3618
Syflabus Tople : Algorithm tr Decompositon.... 3-47
Decompastion SPPU- Dec. 3, may 14) a7
Desirable Propo of Decompsiton, 348
Syllabus Tople : 2NF.. 3.18
‘Second Normal Form (2NF)
(SPPU--Dec. 18, May 16, Oot. 1)... son BAD
‘Syllabus Tople: aN. sine AD
‘Thiet Nora Fon (SNF) (SPU May 19, Dec. 13,
May 18, MaY 16. OC 18) smear nrerin ener -AB
‘Syllabus Tople : BON
Soyee-Cosd Normal Form (BCNF)
(SPPU- May 14)... a
Ditorence Between SNF and BCNF
Fourth Noemal Form (SPPU - ay 19).
Diferance between 4NF and BCNF
Syllabus Tople : Modeling Temporal Data.
Moding Temporal Data
Sevial scheduia, Sevatzabilty : Conti and View, Cascaded
‘bors, Racoverabis and Nen-acoverabl schedules, Concurenty
Gontiot : Need, Locking Methods, Deadlocks, Time-Stamping
Methods, Recovery Mothods : Shadow-paging” and Lop asad
Recovery, Chackpoints, Query Processing, Query Optinizaton,
Pesformance Tuning,
¥ ——_Siflabus Topic : Basic Concept of Transaction.
41 Basle Concopt of Trng260 econ
44-1 Transaction (SPPU- May 13, Doo. 13, May 1),
Y .Sflabus Tople : Properties of Transaction...
42 Prepares of Transaction
{SPPU- May 19, D6. 13, May 14)
43° Transaction States (SPPU- Get. 16)
¥ Syllabus Topic: Transaction Management.
44° Traneacton Management... o
¥ Syllabus Topic: Concept of Sched...
Re g DBMS (SPPU-Comp) of Contents.
45
4
‘
452
453
484
4s
481
462
493
4631
4632
47
ana
a2
473
4734
474
478
4a
402
4a24
4022
4823
403
484
4032
4033
49
492
4944
492
410
‘Concapt ot Schade
Sched, se
‘Syllabus Topic: Sarai Sched,
Types of Sched es
Agvantagas of Concurrent Execution of
‘Transactions,
Diferance between Sari) Seda and
‘Sevslizable Schacle (SPPU- Deo. 14, May 18) an. 67
‘Types of Seiaizabiity (SPU - May 14, May 16)... 49
Contict Sodaizabiy 4
‘Vow Serizabity 4st
Syllabus Tople: Racoverabi and Noncecoverable
Schedules, ate
Types ol Schoo Based on Reco. 412
‘Racovarable Schedule (SPU May 14, Dec. 16)... 412
Non Rscoverable Sched... 412
412
aia
Cascada Scud (SPPU Woy 38) ne
Sit SH a9
Silas Tope: Coney CaN Hee nn 413
Conceney COM nn aa
Noo of Conca Cre SPPU- DE. 18). 8
Dien Coneunerey Cone Proeats i
(SPPU - May 18)..
Syllabus Tepe: Losing Moo
Leg Mate on
Lock Based Prot
“eet Bato Prac (SPPU-Hy 13.
Siflabue Topic: Deaton
(Deadtock (SPPU - Dec. 19)...
Dasa REOMEHY
stan Tope: Reon Methods.
Precovery Methods nn
Syabus Topi: op Based Reorey
Log Basar Recovery (SPPU - Deo. 13, Deo. 18)... 1
‘Syllabus Tope = Checkpoints.
Chace ene
Sylabus Tope: Shadow Paging.
‘Shadow Paging (SPPU- Dee. 18) nn.
‘Syllabus Tople: Query Processing...
Quory Processing.
4101
Basle Slope in Quary Processing
{SPPU- Doo. 13, MAY 18) nnn
‘Syflabus Tople : Query Optinzaton
‘Query Optimization
Syflabue Topic: Performance TUBING son
Performance Tuning.
oA 2D
Chapter 5: Parallel and Distributed
Databases
5110 5.21
Syllabus + Ioducton to Database aritecures = Muliuser
DBMS areriectires, Case Stdy- Oracle Arctitecture, Patt
Databeses : Speedup and Scaaup, ArcitOcies of Paral
Ostebases. Distibted Databases : Arhitstire of Ostibted
(Oatbases,Disttbuled Database Design, Distibuted Data Storage
Distbuted Transaction: Bais, Falure moses, Coment Pretec,
‘Goncurency Control in DetrbutedDatabono,
.
84
84
513
‘Sylebus Tople: Inroducton To Database
Architecture - aitiusar DBMS Arcitecutes.
Introduction to Database Arcitectur : Multiuser
DBMS Architectures.
BA
Slats Tl Case Sly Orde roe. 8
Cave Suc - Orci Acectre
‘Syllabus Topo: Peal Databaso.
Syllabus Tope: Speedy ant Sain
Speedy and Seaoup
(SPPU- tty 15, May 16, Dec. 16)...
DifrartFetos Affecting tha Speedup
and Soap Attuts (SPPU- Ose. 16)...
‘Syllabus Tope: Architecture o Paral
Databese.. ~ 510
‘Architects of Pell Databasa
(SPPU-Dec. 14, May 18)
Shared Mamory(SPPU-Dee, 15).
‘Shar Disk Architect. 7
‘Shased Nothing Acticin (SPPU - Dec. 16.
Hlorarchical ACHReCD nn
Syllabus Tople:Distibed Dnabae...
Citttos Database
{SPU -Dee. 18, Dec. 15, May 18)
Distributed Database Inrecucton.
‘dnigos of istuted Detaboss
(SPPU-May 14, Doc. 18)...
56
58 553
354
saat
5542
555
5851
8552
5553
558
$561
5562
5583
58
561
882
87
874
872
i 873
Chapter 8: NoSQL Database 6-1 to 6-18
Disacvantages of Oistibeted Database... 53
“Types of Distbuted Database System
(SPPU- Dec. 14, May 16)
Homogeneous Disidbuted Database Systems
Heterogeneous Distibuted Catabase System.
Syabus Topic: Architecture of Distbutod
Dateba$08 ene
Architoctura of Distrouled Database
(SPPU-May 16, Doc. 18) st
‘Giant Server Architecture for DEMS. a4
Pose-o-PearAzcitecturs for DDBMS. 54
Muti - DBMS Arte nn 518
Sylabus Tope: lsrbied Dabs Desig 15
Distitutad Database Desig.. BAS
‘Design Problem, 516
Design Strategies nn BAB
yan Tople :Ostbuted Oat Storage 516
Oistibuted Data Storage secre 216
‘Syllabus Topic: Disibuled Transaction Basics... 547
Distibuted Transaction . SAT
Distibuted Transaction BASS. .uenineen SAT
‘Syllabus Tope: Faure Moses, oe AB
Faure Modes...
Syllabus Tope : Commit Preto
Comat Protocok.
(On-phase Commit Protect (1 PC)...
‘Two-pase Commit Protood (2 PC}
{SPPU-Dec. 15).
“Threo-phase Commi Protocol (3 PC)
(SPPU- Dec. 15).
Syllabus Tople : Concurrency Control in
Distbuted Database...
‘Concurrency Contre in Distbuted Database.
‘Syliabue £Inracuction to NoSQL, Database, Types and examples
‘of NoSQL Databeso- Key vals store, dooument store, graph,
Porformance, Structured vorses unstuctured data, Distibuted
Database Model, CAP theorem and BASE Properties,
Comparative study of SOL and NoSOL, NoSAL Dela Models,
. (Case Study - unstructured data fom social media, nivoduction to
i ig Data, Hadoop : HOFS, MepRteduoo. -
6A
62
|
A
‘Syllabus Tople: introduction o NeSQL Database... 6
Aotroduction to NOSOL Database ee enee nin OA
‘Syllabus Topic : Types and Bxarples of
NoSOL Databesa, - en
yp and xaos FNS ODA. 4
Syllabus Topic: Key Valo S008 nnn 4
621
64
4a
642
643
| g DEMS (SPPU-Come) Table of Contents
Koy Vauo Stora (SPPU-Oet. 18) 64
Syllabus Tople : Document Store... cS
Document Sor
Colum Store. .
Sylfabus Topia: Graph
Graph Sie
‘Syllabus Topic: Performance, nd
Performance
Syllabus Topic : Stuctued varees
Unstructured Dat. sens
‘Sinuctured verses Unstructured Data.
Unstructured Data... .
(Comparison batwoon Structured and
Unstructured Data.
Sylabus Tople istvtedOalabass te.
Distributed Database Model...
Syllabus Tople : CAP Thoorom and BASE
Propertas.
CAP Theorem and BASE Popes.
CAP Theorem...
ASE Froporne (PU Mey 16)...
Sau Tepe: Compare Sy of OL
‘Syflabue Tople : NoSCL Data Mode! aencrnen PA
NOSQL Data Models... 62
‘Sylabue Topic: Case Sud -Uneuctwed
ata rom Soctal Mec...
(Case Study -Unetructured Data trom
‘Sodal Media 7
‘Sytlbus Tophe:
Introduction to Big Data...
Syllabus Tope: HADOOP - HOFS, MepFledice. 6-16
sno BAB
Hadoop (SPU - Dec. 16) 616
odes (Compo) of Hadoop
{SPAU- Des. 14, Doo. 15, May 16, Dec. 16)... 816
‘MapReduce (SPPU - May 16, May 18)... scan 847
Hadoop Distributed Fle Systm (HDFS) ecu 7
Lab Alana, romance LOA
(Questions and Anewors for In Semester
xamination .
Exam Oriented Key PoInS nnn KA £0 KAS
Fully Sotved University Question Papers
ga0 DBMS (SPPU-Come), 6
EReaaamene
BNO
Group A : Database Programming Languages - SOL, PLISOL.
Table of Contents
‘Study of Open Source Relational Databases : MySQL
LID Ls,
Design and Develop Sl, DDL statements which demonstrate the use of SQL objects
such as Table, View, Index, Sequence, Synonym
THO to L416
‘Design at feast 10 SQL querios for suilable database application using SOL DML
statements: Insert, Select, Update, Delete wih operators, functions, and set operator.
Tele
‘Design al least 10 SQL queries for sulable database application using SCL OML
statements a ypes of Join, Sub-Query and View.
Le wLat
Unnamed PLSQL code block: Use of Control structure and Exception handing is
mandatory. Write & PL/SQL block of code for he following requirements:
‘Schema:
1. Bowrowerfafin, Name, Dateotissue, NameofBook. Status)
2, Fin@(Ro_no,Date, Amt)
++ Accept roil_no & name of book from user.
Check the numberof days trom dae of issue). if days are between 110-0 then
{ine amount wil be Rs Sper day.
+ tno, of days>20, per day fine vill be Fs 50 per day & for days less than 30, Re, §
per day.
‘+ Alter submitting the book, status wil change from I 9
‘+ tf condition of fine is true, then detals wil be stored into fine table,
Frame the problem statement for writing PUSGL block Inline with above
statement.
Laat Las
‘Cursors: (A ypest Imp, Expl, Cursor FOR Loop, Parameterized Cursor) Wile &
PLISOL block of code using parameterized Cursor, tht wil merge the dala avaible in
‘the newly created table N_RollGal with the data availabe in the table O_ Rola, I the
data in the frst table airoady exist inthe second table then that data shou'd be skipped.
Frame the seperate problem statement for writing PLISQL block to implement all
{types of Cursors inline with above statement. The problem statement should
leary stat the requirements.
Latta
PUISQL Stored Procedure and Stored Function.
‘Wie 6 Stored Procedure’namaly poc_Grade forthe categorization of student. marks
scored by students in xamination ie <=1600 and marks>960 then student will be
placed in distinction catagory # marks scored are between $89 and900 catagory is first
dass, if marks 899 4nd 826 category is Higher Second Class
‘Write @ PLISOL. block for using procedure created with above requirement.
‘Stud_Marks(name, total marks) Resul(Rol Namo, Class)
Frame the separate problem statement for writing PL/SQL Stored Procedure and
function, inte with above statement, The problem statement should clearly state
the requirements.
Cs
Database Trigger (All Types: Flow level and Statement level triggers, Before and After
Triggers). Witte a database trigger on Library lable. The System should keep track of
the records that are being updated or deleted. The old vaiue of updated or deleted
‘records should bs added in Library Audit table.
Frame the problem statement for writing Oalabese Triggers of all types, in-line
‘with above statement. The problem statement should clearly state the
L360 La?
requirements
‘Group 8 : Large Scale Databases.
‘operations, Execution)
‘Stuy of Open Source NOSOL Database: MongoDB {Instalation, Basic CRUD LS? to beat
aoa
i
i
ec Introduction to DBMS
Introduction to Database Management Systems
Purpose of Database Systems
Database-System Applications
View of Data
Dalabasé Languages
Database System Structure
Data Models
Database Design and ER Model : Entity, Attributes, Relationships, Constraints, Keys, Design
Process, Enfity Relationship Mode!
ER Diagram...
Design Issues
Extended E-R Features
Converting ER & EER diagram into tables
NE
‘Syllabus Topic : Introduction to Database
Management Systems
1.1 Introduction to Database
Management Systems
~ Data : Data is the information which has been
translated into a form that is more convenient to
process or move, ’
- Database : The collection’ of related data is
termed as Database which-is organized in such a
‘way that it can be easily retrieved and managed.
- Database Management System
© A Database Management System (DBMS) is
system software which manages the data, It
can perform various tasks like creation,
retrieval, insertion, modification and deletion
of data to manage it in a systematic way as
per requirement.
© Database systems are designed to manage
large amount of data by providing security
from accidental -crash of system and
unauthorized access. DBMS provides
convenient and efficient environment which
used to handle the data
Syllabus Topic : Purpose of Database
Systems
1.2 Purpose of Database Systems
— Programming languages like Java, .Net are used to
develop customized software's. Bvery software or
application has its data to be stored permanently.
- Progranmirig languages cannot store data
permanently. For this purpose we have to use the
Database Management System. The DBMS plays
a significant role in storing and managing data,
= Inn application we store data in DBMS and for
operations like insertion; modification or deletion
we write code in programming languages i.e.
software is usually created wita the help of both
Programming language and Database,
When the application is executed on client side,
the client or user interacts with interface of
application which is created in programming
language. DBMS (SPPU-Comp}
= The database always remains backside and do not
come in front of the user. Hence the database is
‘known as backend while programming language is
termed as frontend.
= To understand the purpose or need of database
system, we need (0 study the previous option to
store data which is called as File Processing
System.
1.2.1 File Processing System
= Tn our day to day life, number of times we need to
store data in such way that it should be easily
accessible whenever required.
‘The data may be of bank transaction details, daily
expenses, employee details, product details ete.
Before computers such data was stored with the
help of papers.
"After invention of computers, it becomes easy to
store data with the help of files. ln the early days,
database applications were built on top of file
systems.
Traditional File Processing System is a computer
based system in which all the information is stored
in various computer files.
= Itstores data ina systematic way that the different
departments of an organization can store their data
‘in set of files which helps fo manage and
differentiate the data.
= Initially the Traditional File Processing System
seems to be useful but as the requirement of data
processing and the size of dati increases, the
drawbacks of this system comes‘in picture.
’
1.2.2 Drawbacks of Traditional File
Processing Systems
Consider an example of {T company database
system where the data of all the employes is stored. In
4 professional IT firm the. tasks are always done as
teamwork. Different teams are assigned for different
Introduction to DBMS.
projects. Hence the data is stored indifferent files so as,
{o differentiate
1. Data Redundancy
© Sometimes as per requirement same data may
be stored in multiple files. Consider an
employee having record in both Employee
and Team files. The name and address of
employee is stored in both of these files.
© Means the data get duplicated. If such data
‘increases, it leads to higher storage and access
cost. This duplication of data in various files
is termed as data redundancy.
(© Intraditional file system, it is very difficult to
avoid this data redundancy.
2. Data Inconsistency
© When dala is to be updated the data
redundancy may lead to data inconsistency.
Data inconsistency occurs when data is not
updated in all the files simultaneously.
‘0 For example if the designation of employee
get changed,” then the respective changes
should be made in both Employee and Team
file. If for some reason, itis nct done, then it
leads to data inconsistency.
© Because for the sample employee, we may get
different information” which may create
problems in the processing of data.
3, Limited Data Sharing
© is difficult to share data in traditional file
system. Each application has its own private
files and users have little choice to share the
data Gutside their own applications.
© To share data, we have to write complex
programs.
4. Difficulty in Accessing Data
© The need of data access varies time to time,
Means different types of information is
needed at different situations.
© For example just consider that we want to
retrieve the data of eimployees who do not
have taken any leave throughout the quarter,
In such case we have two options.
© Wecan access the data by manual method or
we have to write an application program to g DBMS (SPPU-Comp) Introduction to OBMS
retrieve such customized data, Both the
options are not convenient as both of them
leads to wastage of time.
© If we do it, then also it may be possible that
after some time we may require data with
some another filter criteria, The data retrieval
for customized information becomes difficult
because the conventional files system does
not provide any efficient and convenient way
to retrieve the data,
5. Data Dependence
© In the files, data is stored in some specific
format tab, semicolon or comma. If the
format of any of the file is changed, then we
hhave to make changes in program which
processes the file.
© But sometimes there may be many programs
elated with the same file. In such case
changes in all such programs should be dooe.
Missing changes in single program may lead
to failure of whole application.
6. Poor Data Control
© The Traditional File System does not have
centralized data control; the “data is de-
centralized or distributed, In this system the
same field may have different names iny files
of different depertinents of an organization.
© This situation may lead to.different meaning
of same data field in different context or samie
meaning for different fields. This causes poor
data control. Y
7. Problem of Security =“
© Itis very difficult to enforce security checks
and access rights in a traditional file system,
To the file we can set password protection,
© But what if we have to give access to only
few records in the file? For example, in our
database system, the project manager should
bbe able to see all the data regarding teams
uunder him
© The team leader should be able to see data
about his specific team. But payment details
of one project manager should not be
aocessible to his team members or any
another project manager. In-the conveational
Ble processing system, the application
programs are added in ad hoe manner (for
specific purpose) which makes it difficult wo
enforce security constraints.
8 Conourrency Problems
© Concurrency means access of same data by
multipic users at the same time, This is very
important aspect as it leads wo increase in
performance of a system and faster response.
Many advanced systems allow the concurrent
access and manipulation of data,
© Forexample, in our system, consider a record
of an employee is accessed and updated by
rmaultiple users simultaneously at atime. This
may lead to inconsistency of data, if the
concurrency is not controlled in a proper
manner.
© In another example if multiple: uansactions
are make updations on a sate bank account,
then it may show incorrect balance, if any
other transactions try to access balance
amount in between.
© Itis very difficult to implement concurrency
control mechanism on file processing system,
which leads to incorrect or wrong dala
retrieval,
9%. Poor Data Modelling of Real World
© It is difficult for File Processing System to
represent the complex data and. interfile
relationships. This results in poor data
‘© That means the real world applications are
difficult to implement using File Processing
System,
10. Data tsolation
© Itis difficult to store the entire data ina single
file. It is distributed in different files as per
the category.
© These files may be in different formats
because of which it becomes difficult to write
application programs to access the desired
data from these files.
11. integrity Problems
© Every enterprise has its own constraints while
maintain data in the files. Suppose in
employee files the employee ID must start
with ‘E’. Such constraints can be added while
‘writing application programs. ‘DBMS (SPPU-Comp)
© But later on if any new constraints are
introduced by the enterprise, and then it
becomes difficult to add these constraints
again. The File processing system does not
provide any functionality to handle this
situation.
12, Atomicity Problem
co Pailure in a computer system may occur any
time. When failure occurs, if any transaction
is in its midway then it may lead to some
incorrect data updation inthe system.
© Consider another example of bank transaction
where some amount is transferred from
account A to account B. Initially the balance
from account A is accessed and debited by
Rs. 1000. Then we are going to credit it in
account B. But before that system crash
occurs which halts the transaction,
© Now this situation leads to incorrect data
updation in the balance of account A.
In file processing system, it very difficult to
handle such situation to maintain the
atomicity of database. The purpose of
Database Management System is to solve all
these problems and give functionality to store
and manage data in efficient and convenient
way. :
1.2.3 Advantages of Database Management
System
1. Controlling Data Redundancy
© In File Processing System the different
applications has separate filés for data
storage. In this case, the du copies of
the same data are created afénany places.
© In DBMS, all the data 6f an organization is
integrated into a single database.
© The data is recorded at only one place in the
database and it is not duplicated. Por
example, the Employee file andthe Team file
contain several items that are identical.
© When they are converted into database, the
dota is integrated into a single database so that
mukiple copies of the same data are reduced
‘to-single copy.
© Controlling the data redundancy helps to save
Storage space. Similarly, it is useful for
retrieving data from database using queries.
14 troduction OBMS
2, Data Consistency
© The data consistency is oblained by
‘controling the data redundancy, If 2 data item
appears only once, any update to its value has
to be performed only once and the updated
value is immediately available to all users.
For example if there is change in designation
of employee, then the changes are made in
single centralized file which is available to all
the users,
3 Sharing of Data
© In DBMS, data can be easily shared by
different applications, ‘The database
administrator manages the data and gives
rights to users to access the data,
© Multiple users can be authorized to access the
same data simultaneously. The remote users
‘can also share same data,
4, Data Independence
© In DBMS we can completely separe the
dala structure of database and programs or
applications which are used to access the data.
© This is called as data independence. If any
changes are made in structure of database
then there is no need to make changes in the
programs. For example you can modify the
size or data type of a data items (fields of a
database table) without making any change in
application program.
5. Data Controt
© The DBMS provides centralized data storage,
Hence keeping control on data is very much
easy as compared to Traditional File
Processing System,
© Asdata is common for all the application, no
possibilty of any confusion or complication,
6 Security
0 In DBMS the different users can have
different levels of access to data based on
their roles. In the college database, students
will have access to their own data only, while
thei teachers will have access to data of all
the students whom they are teaching,
|
j
{
i
| DBMS (SPPU-Comp)
© Class teacher will be able to see the reports of
all the students in that class, but aot other
classes. The principal will have access to
entire data,
co Similarly, in a banking system, individual
‘operator and clerk will have limited access to
the data while the bank manager ean access
the entire data.
© All these levels of security and access are not
allowed in file system.
7. Control over Concurrency
Ina computer file-based system, if multiple
users are accessing data simultaneously, it is
possible that it maylead'to some imelevant
data generation. For example, if both users
attempt to perform update operation on the
same recon, then one may overwrite the
values recorded by the other.
© Most database management systems have
subssystems to control the concurrency so
that transactions are always recorded with
accuracy.
8. Data Modelling of Real World
co The DBMS has many functionalities are
provided to represent the complex data and
interfile relationships.
© This helps to map the database viith real
‘word applications.
1.2.4 Disadvantages of Database
Management System
Increased Costs a
/
© To install Database Systems, we require
standard software afd hardware. Also to
handle the Database System, highly skilled
personnel are required.
© The cost of maintaining the software,
hardware, and personnel required to operate
and manage the database system is more,
© The cost of training, license, and regulation
compliance also increases the overall
expenses.
2. Complexity
‘© Sometimes because of higher functionality
expectations, the design of Database may
become very complex.
Introduction to DBMS
© To utilize such database with complete
efficiency, all the stakeholders like database
designers, devetopers, database administrators
and end-users must understand — the
Functionality.
© Failure in understanding the system can lead
to wrong design decisions, because of which
serious consequences for an organization may
occur,
3. Size
‘The DBMS becomes extremely large piece of
software because of the complexity of
functionality occupying large amount of disk
space and requiring substantial amounts of
memory to run efficiently.
4. Frequent Upgrade/Replacement Cycles
© New functionalities are often “added into
DBMS by their vendors. These new features
‘often come bundled in new upgraded versions
of the same software.
© Sometimes these versions require hardware
upgrades which increases. expenses. Also
work to tain database users and
administrators to properly use and manage the
new features get increased.
5. Higher Impact of a Failure
© The DBMS is placed at centralized location
which increases the vulnerability of the
system.
‘© That means the DBMS may get attacked and
hharmed. Since all users and applications rely
‘on the centralized database, the failure of any
‘component can bring operations to a halt.
6. Performance
o Usually, a File Based system is written for a
specific application. Hence, the performance
is generally very good.
© While the DBMS is written fo be more
‘general, to cater for multiple applications
rather than any specific one.
‘0 Because of which some applications may not
fu as fast as they used to.
© There are number of advantages of DBMS
over File System. DBMS (SPPU-Comp)
1.25 Difference between File Processing
and DBMS
1. | Duplicate data may
exist in multiple files
which lead to data
redundancy.
‘The data is integrated
which avoids - data
redundancy.
Introduction to DBMS
Concurrency problems
means updation of
same data by multiple
‘users may generate
intelevant results.
DBMS have sub-
systems to contzol the
concurrency.
into a single database |
It is difficult for File
Processing System to
‘epresent the complex
data and interfile
relationships. This
results in poor data
‘modelling.
‘The DBMS has many
functionalities are
2 | Data inconsistency | The data consistency
occurs when data is| is obtained — by
not updated in ali the | controlling the data
files simultaneously. | redundaney
3. | It is difficult 10 share | In DBMS, data can
data in traditional file | be easily shared by
system, different applications,
4. [in the files, data is] In DBMS we can
stored in specific | completely separate
format. If the format| the data structure of
of any of the file is | database and.
changed, then we have | programs or
to make changes in| applications — which
program, which | are used 16 access the
processes the file. date, 7
5. | The Traditional File | The‘DBMS provides
System does not have | ceftralized data
centralized data | storage Hence
control; the data is de- | keeping control on
centralized or| data is very much
distributed. easy.
6. | It is very difficult to] In DBMS the
~ | enforce security | different used can
checks and access
tights in a traditional
file system.
hhave different levels
of access to” data
based on their roles
which provides
strong security to
data,
Syllabus Topic : Database-System
Applications
1.3 _Database-System Applications
For any enterprise, its data is very important
which helps to manage the business as well as decide
some strategies to survive and grow the business in
this competitive world A Database “Management
System is a computerized record-keeping system. It
‘works as a container for collection of computerized
data files. :
‘The overall purpose of DBMS is to provide
functionality to the users to create, store, retrieve and
‘update the information contained inthe databese as per
requirement. Information can be of an individual or an
organization,
Databases touch all aspects of our lives. Some of
the major areas of application are as follows :
= Telecom : In Télecom sector database is,
maintained to keep track of the information about
calls made, customer details, network usage ete.
Without using database systems it is dificult to
maintain such huge amount of data which keeps
track of every second.
Banking : In benking sector the data related to
transactions of customers is very huge, Such data
can be comfortably stored in the Database
‘Management System. The DBMS is also used for
storing customer personal information, tracking
day to day credit and debit transactions,
generating bank statements etc. DBMS (SPPU-Comp)
= Industry : In industry database management plays
‘an important role. in departments like production,
sales or account it is very eosential to store the
dala im systematic format For example in
production department the data about.
‘manufactured products is stored which is very
useful for sales department to keep track of orders.
~ E-commerce : There ae number of online
shopping websites such as ebay, Flipkart Amazon,
etc. ‘These sites store the information about
various products, user addresses, their preferences
and credit details. It can be implemented using
Database Management System only.
~ Airlines : In airlines, the data of ticket booking,
flight schedule, employee details, and customer
detais is very huge. Such data can be managed
using the Database Management System.
~ Edueation System : In education sector, the
schools, colleges, private institutes have to store
daia of students, teachers, exam schedules,
accounts etc which can be handles by Database
Management System,
— Railway Reservation System: Database
‘Management System helps in keeping record of
ticket booking, deperture and arrival status of train
etc.
~ Library Management System : In the jibrary
there are thousands of books which make it very
difficult t0 keep record of all the books in a
register. The DBMS can be used to maintain this
information about book issue dates, names of the
‘books, authors and availablity of the book.
friends. Daily millions of uisers signed up for these
social media accounts like Facebook, Whatsapp,
Twitter, and Google plus, The data of these
millions of users and their chats can store using
Database Management System.
Syllabus Topic : View Of Data
1.4 View of Data
AS we have seen a data base system is collection
of relaied data and system software which manages the
data. The data is gonerally stored in a detailed and
complex manner. [tis important to provide an abstract,
view of data to the user.
Untroduction to DEMS.
To understand the view of data, first we have to
Jearm the concept of abstraction.
1,
.4.1 Abstraction
Abstraction is an important feature of Database
Management System. Extracting the important
data by ignoring the remaining relevant details is
known as abstraction,
Database systems are usually made-up of complex
data structures as per their requirements.
To make the user interaction easy with database,
the internal irelevant details can be hidden from
users, This process of hiding irrelevant details
from user is called data abstraction.
‘The complexity of database can be hiding from
user by different level of abstraction as follows.
1.4.1.1 Levels of Abstraction EEZTEDSSER?
Fig. LAL
|. Physical fevel
In data abstraction Physical level is the lowest
level. This level describes how the data is actually
stored in the physical memory.
The physical memory may be hard disks,
‘magnetic tapes, etc. In Physical level the methods
like hashing 272 used for organization purpose.
Developer would know the requirement, size"
accessing frequency of the records clearly in this
level which makes easy to design this level,
Logical tevet
This is the next higher level of abstraction which
is used to describe what data the database stores, ‘and what relationships exist in between the data
items. The logical ievel thus describes an entire
database in terms of a small number of relatively
simple structures.
‘Although implementation of the simple structures
at the logical level may involve complex physical
level structures, the user of the logical level does
‘not need to be aware of this complexity. This is
‘considered as physical data independence.
Database administrators use the logical level of
abstraction to decide what information to keep ina
database,
View level
Ie is the highest level of data abstraction. This
level describes the user interaction with database
system. In the logical level, simple structures are
used but still complexity remains because in the
large database various type of information is
stored.
Many users are not aware of technical details of
the system, and also they need not to access whole
{information from the database. Hence it is
necessary to provide a simple and short interface
for such users as pet their requirements. Muliple
views can be created for same database for
multiple users,
Example : Consider that we are storing
information of all the employees of an
organization in employee table. Af physical level
these records can be described as blocks of storage
(bytes, gigabytes, terabytes’ etc.) in memory.
‘These details are usually hidden from the
developer.
The records can be described as fields and
attributes along with their data types at the logical
fevel. The relationship between these fields can be
implemented logically. Usually the--developer
‘works at this level because they have knowledge
of such things about database systems.
End usec interacts with system with the help of
GU and enters the dels at the screed at view
level. User is not aware of how the data is stored
and what data is stored; such details are hidden
-from them.
g ‘DAMS (SPPU-Comp) {ntroduction to DBMS
Schema
=» The design of a database is called the schema, To
understand schema we can consider an example of
‘3 program of an application. A variable or array
declaced with its structure (data type and/or size)
is schema. The changes in schema or not frequent.
= Types of Schema : According to the level of
abstraction, the database schema is divided into
three types : Physical schema, Logical svhemma and
‘View schema,
(® Physical schema is the design of « database
at physical level, ic. how the data stored in the
blocks of storage is described in this level.
(li) Logical schema is the design of database at
logical level, Developers and database
administrators work at this level. Here the data can
be described ascertain types of data records
gets stored in data structures, however the internal
dolails like the implementation of data structure
are hidden at this level.
(it) View schema refers'to design of database at
view level. This usually describes the end user
interaction with database systems. There may be
multiple schemas af view level
14.3 Instance
Jn database changes are quite frequent i.c.
insertion, deletion ot updation are the frequent
operations on database. The data is stored in the
database at particular moment is called as instance of
the database. In the example of application program,
the value of a variable at particular time or situation is
called as instance of database schema.
——$_—
‘Syllabus Topic : Database Languages
1.5 Database Languages
In general in a database system, the Data
Definition Language is used to specify schemas
(design) of # database while the data manipulation
Tanguage is used to fire queries (commands) on
database in order to manipulate it. Both are the main
pillars of database language like SQL.
With DDL and DML the database system also has
the languages like DCL and TCL for different
functionalities. The database languages are categorized
4 DDL, DML, DCL and TCL. (fons iseru-com)
1.5.1 Data Definition Language (DDL}
‘The DDL specify the schema of database by set of
definitions.
This language allows the users to define dats and
their relationship to other types of data, It is used
to create data tables, dictionaries, and files, within
databases.
‘The DDL is also. used to specify the structure of
each table, set of associated values with each
attribute, integrity constraints, security and
authorization’ information for all the tables and
physical storage structure of alt the tables on the
disk.
‘The data values stored in the database should
satisfy some constraints for consistency purpose.
For example the salary of an employee. should
never be negative or the employee id should start
with ‘E’ etc. The .Data Definition Languages
provides functionality to specify such constrains,
1.5.2 Data Manipulation Language (DML)
‘The Data Manipulation Language (DML) is used
for accessing and manipulating data in a database,
DML provides a set of functionalities to support
the basic data manipulation operations on thé data
stored in the database.
‘The DMS is basically of two types.
1) Procedural DML ~ There is acequirement of
user to specify which data’is required and
how get this data. é
2) Declarative DML - Here is also requirement
of user to specify which data is required and
without specifying how get this data, This is
easier (0 understand and useful than
procedural DML.
A concept of query is used for processing. Query
isa statement or command which requests the
retrieval of information from the database, The
part of DME which involved information rettieval
is known as query language. The tee levels of
abstraction are used in defining structure of data
as well as to manipulate the data.
19
Introduction to DBMS
‘Syllabus Topic : Database System
Structure
DBMS (Database Management System) acts as an
interface between the user and the database.
The user requests the DBMS to perform various
operations (retrieve, insert, delete and update} on
the database,
‘The components of DBMS perform these
requested operations on the database and provide
necessary data to the users. The various
components of DBMS are as shown below
Fig. 1.6, : Database System Structure
‘Structure of DBMS
The Structure of DBMS contains following
components
1, DDL Compiler
© The DDL Compiler converts DDL. commands
into set of tables containing metadata stored
ina data dictionary.
‘©The metadata information is name of the files,
data items, storage details of each file,
‘mapping information and constraints ete.
fan |
|
DBMS (SPPU-Com;
2. DML Compiler and Query optimizer
© The DML commands such as retrieve, insert,
update, delete ete. from the application
program are sent t9 the DML compiler for
compilation. It converts these commands into
object code for understanding of database,
©The object code is then optimized in the best
way to execute a query by ihe query optimizer
and then send to the data manager.
3. Data Manager
© The Data Manager is the central software
component of the DBMS also known as
Database Controt System,
© The main functions of Data Manager are :
© It converts the requests received from
query optimizer to machine
understandable form. Tt makes actual
request inside the database.
© Controls DBMS information access that
is stored on disk.
© It controls handling buffers in main
memory.
© Tt enforces constraints to maintain
consistency and integrity ofthe date,
© It synchronizes . the simultanepus
‘operations pecformed by the concurrent
users,
© [also controls the backup and recovery
operations,
4 Data Dictionary /
Data Dietionary is a repository of description of
data in the database: It contains information about
© Data names ofthe tables, names of attributes
of each table, length of attributes, and number
‘of rows in each table,
‘© Relationships between database transactions
and data items referenced by them which are
useful in determining which transactions. are
affected when certain data definitions are
changed.
o Constraints on data ic. range of values
permitted,
© Detailed information on physical database
design such as storage structure, access paths,
files and record sizes,
Introduction to DBMS
© Access Authorization - is the Description of
database users their responsibilities and their
access rights,
© Usage statistics such as frequency of query
and transactions.
© Data dictionary is used to actually control the
data integrity and accuracy. It may be used as
‘an important past of the DBMS.
Importance of Data Dictionary
© Data Dictionary is necessary in the databases
ddue to following reasons:
0 It improves the control of DBA over the
information system and users understanding
for the use of the system,
© It helps in’ documenting the database design
process by storing documentation of the result
of every design phase and design decisions,
© Ithelps in searching the views on the database.
definitions of those views.
© It provides great assistance in producing a
report of which daia elements (ie. data
values) are used in all the programs.
Data File
It contains the data portion of the database i. it
has the real daia stored in it. It ean be stored as
‘magnetic disks, magnetic tapes or optical disks.
‘Complled DML.
© The DML complier converts the high fevel
‘Queries into Jow level file access commands
known as compiled DML.
© Some of the processed DML statemeats
(insert, update, delete) ae stored in it $0 that
if there is similar requests, the data can be
reused.
End Users
‘They are the real users of the database, They can
be developers, designers, administrator or the
actual users of the database...
Syllabus Topic : Data Models
Data Models
‘Basic Concept of Data Models
‘The process of analysis of data object and their database. It is the first step in database designing. Data
models define how data is connected to each other and
hhow they ace processed and stored inside the system. A
data model provides a way to describe the design of a