Database management systems

l
- 3 , p – 2, c - 4
15IT302J
Outline
 DBMS
 Examples
 Purpose of DB
 File Processing System
 View of Data
 Level of Abstraction
 Instances and Schema
 Database Management System

(DBMS)
Database
 Collection of data, Primary Goal : store and retrieve database information that is both convenient and efficient.
 DBMS contains information about a particular enterprise
 Collection of interrelated data
 Set of programs to access the data
 Database Applications:
 Banking: transactions
 Airlines: reservations, schedules
 Universities: registration, grades
 Sales: customers, products, purchases
 Online retailers: order tracking, customized recommendations
 Manufacturing: production, inventory, orders, supply chain
 Human resources: employee records, salaries, tax deductions
 Databases can be very large.
 Databases touch all aspects of our lives
University Database Example
 Application program examples
 Add new students, instructors, and courses
 Register students for courses, and generate class
rosters
 Assign grades to students, compute grade point
averages (GPA) and generate transcripts
 In the early days, database applications were
built directly on top of file systems
Purpose of database systems
 In need of handling computerized management of
commercial data.
 Allow users to manipulate the information.
 Have to be written to handle new rules
 Acquires more files and more application programs
 Drawbacks of using file systems to store data

 Data redundancy and inconsistency

 Multiple file formats, duplication of information in different
files
 Difficulty in accessing data
 Need to write a new program to carry out each new task
 Data isolation
 Multiple files and formats
 Integrity problems
 Integrity constraints (e.g., account balance > 0) become
“buried” in program code rather than being stated explicitly
 Hard to add new constraints or change existing ones
 Drawbacks of using file systems to store data (Cont.)

 Atomicity of updates
 Failures may leave database in an inconsistent state with partial

updates carried out

 Example: Transfer of funds from one account to another should either

complete or not happen at all

 Concurrent access by multiple users
 Concurrent access needed for performance

 Uncontrolled concurrent accesses can lead to inconsistencies

 Example: Two people reading a balance (say 100) and updating it

by withdrawing money (say 50 each) at the same time
 Security problems
 Hard to provide user access to some, but not all, data

Database systems offer solutions to all the above problems

VIEW OF DATA
 A major purpose of a database system is to provide
users with an abstract view of the data.
 The system hides certain details of how the data are
stored and maintained.
 Data Abstraction
 Physical Level
 Logical Level
 View Level
 Levels of Abstraction
 Physical level: describes how a record (e.g.,
instructor) is stored.
 Logical level: describes data stored in database, and
the relationships among the data.
 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 - physical data independence.
 View level: application programs hide details of data
types. Views can also hide information (such as an
employee’s salary) for security purposes.
View of Data
An architecture for a database system
 Example of university organization
 Record –
 INSTRUCTOR with fields ID, NAME, DEPT_NAME, SALARY.
 DEPARTMENT with fields DEPT_NAME, BUILDING, BUDGET
 COURSE with fields COURSE_ID, TITLE, DEPT_NAME,
CREDITS
 STUDENT with fields ID, NAME, DEPT_NAME, AND
TOT_CRED
 Instances and Schemas
 Instances - collection of information stored in the database at a
particular moment
 Schema - The overall design of the database
 Logical Schema – the overall logical structure of the database
 Example: The database consists of information about a set of customers
and accounts in a bank and their relationship
 Physical schema–
schema the overall physical structure of the database
 Instance – the actual content of the database at a particular point in time
 Physical Data Independence – the ability to modify the physical schema
without changing the logical schema
 Applications depend on the logical schema
 In general, the interfaces between the various levels and components
should be well defined so that changes in some parts do not seriously
influence others.
Example of university oraganization (cont)

 In the physical level,

 an instructor, department, or student record - block of consecutive
storage locations – KNOWN by Database Administrator.
 The compiler hides this level of detail from programmers.
 In the logical level,
 each such record is described by a type definition and their
interrelationship among record types.
 Programmers work at this level of abstraction using a programming
language.
 In the view level,
 Application programs that hide details of the data types from users.
 Several views of the database are defined, and a database user sees
some or all of these views.
Data Models
Session 2
Outline
 Data model
 Relational Model
 ER model
 Object based Data Model
 Semistructure Model
 Network Model
 Hierarchical Model
 Database Language
Data Models
 A collection of conceptual tools for describing
 Data
 Data relationships
 Data semantics
 Data constraints
 Relational model
 Entity-Relationship data model (mainly for database
design)
 Object-based data models (Object-oriented and Object-
relational)
 Semistructured data model (XML)
 Other older models:
 Network model
 Hierarchical model
 Relational model
 Model uses a collection of tables to represent both data and the
relationships among those data.
 Each table has multiple columns and each column has a unique name.
 The relational model is an example of a record-based model.
 Record-based models are so named because the database is structured
in fixed-format records
 of several types.
 Each table contains records of a particular type. Each record type
defines a fixed number of fields, or attributes.
 The columns of the table correspond to the attributes of the record type.
 It is not hard to see how tables may be stored in files.
 The relational model hides such low-level implementation details from
database developers and users.
 Data Definition Language
 Data Manipulation Language
 Application Program
ENTITY-RELATIONSHIP
MODEL
 The entity-relationship (E-R) data model uses
 Entities - a collection of basic objects - “thing” or
“object” in the real world
 Relationships among these objects.
OBJECT BASE DATA MODEL
 Object-oriented programming (especially in Java,
C++, or C#) has become the dominant software-
development methodology.
 Extending the E-R model
 Encapsulation,
 Methods (functions),
 Object identity.
 Combines features of the object-oriented data
model and relational data model.
 SEMISTRUCTURED DATA
 MODEL
Specification of data where individual data items of the same
type may have different sets of attributes.
 Every data item of a particular type must have the same set of
attributes.
 The Extensible Markup Language (XML) is widely used to
represent semistructured data.
 Defined by the WWW Consortium (W3C)
 Originally intended as a document markup language not a database
language
 The ability to specify new tags, and to create nested tag structures
made XML a great way to exchange data, not just documents
 XML has become the basis for all new generation data
interchange formats.
 A wide variety of tools is available for parsing, browsing and
querying XML documents/data
 Hierarchical model
 Hierarchical model is a data model which uses
the tree as its basic structure.
 A data structure diagram for a tree representing
the STUDENT, FACULTY and CLASS.
 The root node chosen is faculty, CLASS as a child
of faculty and STUDENT as a child of class.
 The cardinality between CLASS and FACULTY
is one to many cardinality as a FACULTY teaches
one or more CLASS.
 The cardinality between a CLASS and a
STUDENT is also one to many cardinality
because a CLASS has many STUDENTS.
Hierarchical model
NETWORK MODEL
 The Network model replaces the hierarchical tree with a graph thus
allowing more general connections among the nodes.
 The main difference of the network model from the hierarchical model, is
its ability to handle many to many (N:N) relations. In other words, it
allows a record to have more than one parent.
 Suppose an professor works in department, courses and students.
 The strict hierarchical arrangement is not possible here and the tree
becomes a more generalized graph - a network.
 The network model was evolved to specifically handle non-hierarchical
relationships. As shown below data can belong to more than one parent.
Note that there are lateral connections as well as top-down connections.
 A network structure thus allows 1:1 (one: one), l: M (one: many), M: M
(many: many) relationships among entities.
NETWORK MODEL
Database language
Session 3
 DATABASE LANGUAGE
 Data-Definition Lanaguage (DDL) – express database schema by a set of
definitions.
 For Instance,
Create table account (acc_no number(5),
balance integer)
 Data Dictionary or Data Directory – contains metadata (data about data).
 DDL compiler generates a set of table templates stored in a data dictionary
 Data dictionary contains metadata (i.e., data about data)
 Database schema
 Integrity constraints

 Primary key (ID uniquely identifies instructors)

 Authorization

 Who can access what

 Data storage and definition Language – storage and access methods used by
DB system by special type of DDL.
DataBASE Language Contd
 DML - specify a database schema by a set of definitions
 Language for accessing and manipulating the data
organized by the appropriate data model
 Retrieval of information stored in the database
 Insertion of new information into the database
 Deletion of information from the database
 Modification of information stored in the database
 Two classes of languages
 Procedural DMLs - a user to specify what data are needed and
how to get those data.
 Declarative DMLs (nonprocedural DMLs) - a user to specify
what data are needed without specifying how to get those data.
 Database language contd
 Database Access from Application Programs
 Programs that are used to interact with the database
 Host language – Cobol, C, C++, Java
 Two ways to do this
 Application program interface – used to send DML and DDL
statements to the DB, retrieve the results
 Open Database Connectivity ODBC
 Java Database Connectivity JDBC
 Embed DML within the host language program – DML
precompiler.
Database users and administrators
 Database users and administrators contd

 Goal – Retrieve information and store new information

in DB.
 Database users and User Interfaces
 Naive users - unsophisticated users - interact with the
system through previously application programs.
 Application programmers - computer professionals write
application programs to develop user interfaces
 Sophisticated users - interact with the system without
writing programs - access using a database query language
or data analysis software tools
 Specialized users - sophisticated users who write
specialized database applications
 Database users and administrators contd
 Database Administrators
 Have central control of both the data and the programs - person who control
over the system is called a database administrator (DBA).
 Functions of a DBA
 Schema definition - DBA creates database schema by a set of data
definition statements in the DDL.
 Storage structure and access-method definition.
 Schema and physical-organization modification -alter the physical
organization to improve performance.
 Granting of authorization for data access - By granting different types of
authorization, the database administrator can regulate which parts of the
database various users can access.
 Routine maintenance. Examples of the database administrator’s routine
maintenance activities are:
 Periodically backing up the database
 Ensuring that enough free disk space
 Monitoring jobs running on the database
 Ensuring that performance
Database architecture
database system structure
Session 4
Outling
 Database Architecture
 Database System Structure
 History of Database
Database Architecture
Storage Management
 Storage manager is a program module that provides
the interface between the low-level data stored in the
database and the application programs and queries
submitted to the system.
 The storage manager is responsible to the following
tasks:
 Interaction with the OS file manager
 Efficient storing, retrieving and updating of data
 Issues:
 Storage access
 File organization
 Indexing and hashing
 Storage manage
 The storage manager components include:
 Authorization and integrity manager – tests integrity constraints

and checks the authority of users to access data.

 Transaction manager, ensures DB in a consistent state even in

system failures and in concurrent transaction executions

 File manager - manages disk storage and the data structures

 Buffer manager – fetch data from disk storage into main memory

 Data Structures Implemented

 Data files - store the database itself.

 Data dictionary -stores metadata about the structure of the database,

 Indices – provide fast access to data items

Query Processing
1. Parsing and translation
2. Optimization
3. Evaluation
Query Processing (Cont.)
 Alternative ways of evaluating a given query
 Equivalent expressions
 Different algorithms for each operation
 Cost difference between a good and a bad way of
evaluating a query can be enormous
 Need to estimate the cost of operations
 Depends critically on statistical information about
relations which the database must maintain
 Need to estimate statistics for intermediate results to
compute cost of complex expressions
Transaction Management
 What if the system fails?
 What if more than one user is concurrently updating the
same data?
 A transaction is a collection of operations that performs
a single logical function in a database application
 Transaction-management component ensures that the
database remains in a consistent (correct) state despite
system failures (e.g., power failures and operating
system crashes) and transaction failures.
 Concurrency-control manager controls the interaction
among the concurrent transactions, to ensure the
consistency of the database.
Database Architecture
The architecture of a database systems is greatly
influenced by
the underlying computer system on which the
database is running:
 Centralized

 Client-server

 Parallel (multi-processor)

 Distributed
Database system structure
 Database systems can be centralized, or client-server, where one server
machine executes work on behalf of multiple client machines.
 Database systems can also be designed to exploit parallel computer
architectures. Distributed databases span multiple geographically
separated machines.
 Structure of Modern Computer System.
 Query processing using parallel processing
 Distributed Database
 Ensure atomicity & concurrency control of transactions that execute at multiple
sites
 Distributed query processing and directory systems
 Database applications are usually partitioned into two or three parts,
 Two-tier architecture, the application resides at the client machine,
 Three-tier architecture, the client machine acts as front end and no direct
database calls.
History of Database Systems
 1950s and early 1960s:
 Data processing using magnetic tapes for storage
 Tapes provided only sequential access
 Punched cards for input
 Late 1960s and 1970s:
 Hard disks allowed direct access to data
 Network and hierarchical data models in widespread use
 Ted Codd defines the relational data model
 Would win the ACM Turing Award for this work
 IBM Research begins System R prototype
 UC Berkeley begins Ingres prototype
 High-performance (for the era) transaction processing
History (cont.)
 1980s:
 Research relational prototypes evolve into commercial systems
 SQL becomes industrial standard
 Parallel and distributed database systems
 Object-oriented database systems
 1990s:
 Large decision support and data-mining applications
 Large multi-terabyte data warehouses
 Emergence of Web commerce
 Early 2000s:
 XML and XQuery standards
 Automated database administration
 Later 2000s:
 Giant data storage systems
 Google BigTable, Yahoo PNuts, Amazon, ..
Entity relationship model
Session 5
outline
 ER model
 Entity Sets
 Attribute Type
 Relationship Sets
 Constraints
 Keys
Entity relationship model
 Represents the logical structure of DB, serve as
semantic data model
 Useful in mapping the meaning and interactions of
real world onto conceptual schema.
 Basic concepts
 Entity Sets
 Relationship sets
 Constraints
 Keys
 Entity sets
 Entity: A thing or object in real world.
 Ex: Each student in a department is an entity.
 Stud Id= 101 identify a unique person in the department.
 Entity Set: set of entities of same type.
 Ex: set of all student details - student entity set.
 Attributes: descriptive properties possessed by each member of entity
set
 Ex: Stud_id, Stud_name, City
 101 is value of the attribute Stud_id
 Domain / Value set: set of permitted values for each attributes.
 Ex: Stud_name is set of all strings of certain length
 Attribute types
 Simple attribute – attributes cant be divided
into subparts.
 Ex: Firstname, MiddleName, LastName of
students
 Composite attribute – attributes can be
divided into subparts
 Ex: StudName can be divided into Firstname,
LastName
 Single-valued attribute – have single value
for particular entity
 Ex: StudId
 Multivalued attribute – can have more than
single value for a particular entity
 Ex: StudPhoneNo.
 Derived attribute – value can be derived from
value of other related attribute
 Ex: Value of Age attribute can be derived from
DOB attribute
 Relationship sets
 Relationship : association among entities.
 Ex: Relation between Devi and DBMS.
 Relationship set: set of relationships of same type.
 Ex Relationship set Coursestaken
 Relationship Instance: association between entities that is modeled
 Ex: 101 from Goa taken DBMS.
 Descriptive Attribute – attribute of relationship set
 Ex: DateofEnrollment in relationship set Coursestaken
 Participate: An entity E1,E2 ..En Participate in Relationship set R.
 Entity Role: Function that entity plays in relationship
 Recursive Relationship set
 Binary Relationship set
Constraints
 Mapping Cardinalities
 One to One
 One to Many
 Many to One
 Many to Many
 Participation Constraints
 Total
 Partial
 Constraints
1:1 1:M M:1 M:M

B Total with A
A Partial with B
keys
 Keys – used to identify a set of attributes that can identify entities
from each others.
 Entity sets
 Super Key: set of one or more attributes (collectively) identify
uniquely an entity in entity set. Ex: <StudId>, <StudName,DOB>,
<StudId,City>, <StudId, City, State>
 Candidate keys: a superkey which has no propersubset or minimal
superkey. Ex: StudId
 Primary Key: DB designer uses a candidate key for identification of
entity uniquiely. Ex: StudId
 Relationship sets
 Relationship sets contains Primary key of all entity set involves in
relationship
End of Unit 1
Session 5