Unit-1
Introduction to Database
Management System and ER Model
Database Management System (DBMS)
• DBMS contains information about a particular
enterprise
– Collection of interrelated data
– Set of programs to access the data
– An environment that is both convenient and
efficient to use
Database Applications
• 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
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
• 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
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.
type instructor = record
ID : string;
name : string;
dept_name : string;
salary : integer;
end;
• 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
Instances and Schemas
• Similar to types and variables in programming
languages
• 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 the
relationship between them
Analogous to type information of a variable in a program
• Physical schema– the overall physical structure of
the database
Instances and Schemas
• Instance – the actual content of the database at a
particular point in time
– Analogous to the value of a variable
• 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.
Data Models
• A collection of 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
Data Definition Language (DDL)
• Specification notation for defining the database schema
Example: create table instructor (
ID char(5),
name varchar(20),
dept_name varchar(20),
salary numeric(8,2))
• 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 Manipulation Language (DML)
• Language for accessing and manipulating the data
organized by the appropriate data model
– DML also known as query language
• Two classes of languages
– Pure – used for proving properties about computational
power and for optimization
• Relational Algebra
• Tuple relational calculus
• Domain relational calculus
– Commercial – used in commercial systems
• SQL is the most widely used commercial language
Object-Relational Data Models
• Relational model: flat, “atomic” values
• Object Relational Data Models
– Extend the relational data model by including object
orientation and constructs to deal with added data types.
– Allow attributes of tuples to have complex types, including
non-atomic values such as nested relations.
– Preserve relational foundations, in particular the
declarative access to data, while extending modeling
power.
– Provide upward compatibility with existing relational
languages.
Database Engine
• Storage manager
• Query processing
• Transaction manager
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
Query Processing
Parsing and translation
[Link]
[Link]
Query Processing
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 Users and Administrators
Database System Internals
Entity
• An entity can be a real-world object, either
animate or inanimate, that can be easily
identifiable. For example, in a school
database, students, teachers, classes, and
courses offered can be considered as entities.
All these entities have some attributes or
properties that give them their identity.
Attributes
• Entities are represented by means of their
properties, called attributes. All attributes
have values. For example, a student entity
may have name, class, and age as attributes.
Types of Attributes
• Simple attribute
• Composite attribute
• Derived attribute
• Single-value attribute
• Multi-value attribute
Relationship
• The association among entities is called a relationship.
For example, an employee works_at a department, a
student enrolls in a course. Here, Works_at and Enrolls
are called relationships.
• Degree of Relationship:
The number of participating entities in a relationship
defines the degree of the relationship.
• Binary = degree 2
• Ternary = degree 3
• n-ary = degree
Mapping Cardinalities
• Cardinality defines the number of entities in
one entity set, which can be associated with
the number of entities of other set via
relationship set.
- One-to-One
- One-to-Many
- Many-to-One
- Many-to-Many
Keys
• Keys play an important role in the relational
database.
• It is used to uniquely identify any record or
row of data from the table. It is also used to
establish and identify relationships between
tables.
Types of Keys
• Primary key
• Candidate key
• Super Key
• Foreign key
• Alternate key
• Composite key
Extended Entity-Relationship (EE-R) Model
• EER is a high-level data model that incorporates
the extensions to the original ER model.
Enhanced ERD are high level models that
represent the requirements and complexities of
complex database.
• In addition to ER model concepts EE-R includes −
-Subclasses and Super classes.
-Specialization and Generalization.
- Aggregation.
