CS 245 Database Management Systems

Lecture #1 Introduction

Rashmi Dutta Baruah

Department of Computer Science & Engineering
 Topics
• Introduction to Databases
• Data Modelling using ER Model
DBMS as a
• Relational Data Model and SQL Black-box
• ER-to-Relational Model
• Database Design Theory and Normalization

• File Structures, Hashing, Indexing, and Physical Database

Design Internals of relational DBMS
• Query Processing and Optimization

• Transaction Processing, Concurrency Control, and Recovery

• Non-relational DBMS: NOSQL Systems and Big Data
2
 Books
• R. Elmasri and S.B. Navathe, Fundamentals of
Database Systems, Pearson, 7th Edition, 2017.
• R. Ramakrishnan, J. Geherke, Database
Management Systems, McGraw Hill, 2014.
• A. Silberschatz, H.F. Korth, S. Sudarshan, Database
Systems Concepts, 7th Edition, McGraw-Hill, 2021
• H. Garcia-Molina, J. Ullman, J. Widom, Database
System: The Complete Book, 2nd Edition, Pearson,
2013.

3
 Grading Components
• End Sem Exam 40%
• Mid Sem Exam 30 %
• QUIZ I & II 20 %
• In-class assignments (in groups of 2) 10%
• Dates
– Quiz I - 17th February, Monday
– Quiz II – 21st April, Monday
– Mid Sem Exam
– End Sem Exam

4
 Databases
• You may not notice, but you encounter several activities
everyday that involve some interaction with a database.

– Banking and Financial Services, Airline/Railway reservation,

Government and Public Services (tax filing) : Traditional
database applications – stored or accessed information is
textual or numeric.
– Online search engines (Google), Social media platforms
(Instagram, LinkedIn), E-commerce platforms (Amazon) : NOSQL
Systems - stored or accessed information can be documents,
programs, images, videos.

5
 What is a Database?
• Database
– Collection of related data where data is known facts that
can be recorded and have implicit meaning.
• It has following implicit properties:
– represents some aspects of real-world
– logically coherent collection of data with some inherent
meaning
– designed, built, and populated with data for a specific
purpose

6
 What is a DBMS?
• Database Management
System (DBMS)
– general purpose software
that enables creation and
maintenance of database
• Database System
– The DBMS software
together with the data
itself. Sometimes, the
applications are also
included. A simplified database system environment

7
 Typical DBMS Functionality
• Define a database in terms of data types, structures
and constraints
• Construct or Load the Database on a secondary
storage medium
• Manipulating the database - querying, generating
reports, insertions, deletions and modifications to its
content
• Concurrent Processing and Sharing by a set of users
and programs – yet, keeping all data valid and
consistent
• Other features include protecting and maintaining
the database.

8
 Example of a Database
• Mini-world for the example: Part of a UNIVERSITY
environment.
• Some mini-world entities:
– STUDENTs
– COURSEs
– SECTIONs (of COURSEs)
– (academic) DEPARTMENTs
– INSTRUCTORs
• Some mini-world relationships:
– SECTIONs are of specific COURSEs
– STUDENTs take SECTIONs
– COURSEs have prerequisite COURSEs
– INSTRUCTORs teach SECTIONs
– COURSEs are offered by DEPARTMENTs
– STUDENTs major in DEPARTMENTs

9
 Characteristics of Database Approach
• The main characteristics of the database approach versus the
file-processing approach:
• Self-describing nature of a database system
– A DBMS catalog stores the description of the database.
The description is called meta-data
Relation Columns

Relation_name No_of_columns Column_ Data_type Belongs_to_relation

name
STUDENT 4
Name Char(30) STUDENT
COURSE 4
Student_ Char(4) STUDENT
SECTION 5 number
… …

10
 Characteristics of Database Approach
• Insulation between programs and data
– Called program-data independence. Allows changing data
storage structures and operations without having to change the
DBMS access programs.
• Support of multiple views of the data
– Each user may see a different view of the database, which
describes only the data of interest to that user.

• Sharing of data and multiuser transaction processing

– allowing a set of concurrent users to retrieve and to update the
database. Concurrency control within the DBMS guarantees that
each transaction is correctly executed or completely aborted

11
 Advantages of using Database approach
• Controlling redundancy in data storage and in
development and maintenance efforts
• Sharing of data among multiple users
• Restricting unauthorized access to data
• Providing persistent storage for program Objects
• Providing Storage Structures for efficient Query
Processing
• Providing backup and recovery services.
• Providing multiple interfaces to different classes of users.
• Representing complex relationships among data.

12
 Advantages of using Database approach
• Enforcing integrity constraints on the database.
• Drawing Inferences and Actions using rules
• Potential for enforcing standards
• Reduced application development time
• Flexibility to change data structures
• Availability of up-to-date information
• Economies of scale

13
 When not to use a DBMS
• Main inhibitors (costs) of using a DBMS:
– High initial investment and possible need for additional hardware.
– Overhead for providing generality, security, concurrency control,
recovery, and integrity functions.
• When a DBMS may be unnecessary:
– If the database and applications are simple, well defined, and not
expected to change.
– If there are stringent real-time requirements that may not be met
because of DBMS overhead.
– If access to data by multiple users is not required.
• When no DBMS may suffice:
– If the database system is not able to handle the complexity of data
because of modeling limitations
– If the database users need special operations not supported by the
DBMS.

14
 Summary
• Database - a collection of related data, where data means
recorded facts –represents some aspect of real world – used
for specific purpose
• DBMS –generalized software package for implementing and
maintaining database
• Discussed characteristics of database approach versus file
processing applications
• Discussed advantages of DBSM and situations where it is not
advantageous to use it

15