Unit1: Introduction
Database System Concepts, 6th Ed.
©Silberschatz, Korth and Sudarshan
See [Link] for conditions on re-use
Outline
Introduction 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, Attributes, Relationships, Constraints, Keys,
Design Process, Entity Relationship Model,
ER Diagram, Design Issues,
Extended E-R Features,
Converting E-R & EER diagram into tables.
Database System Concepts - 6th Edition 1.2 ©Silberschatz, Korth and Sudarshan
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:
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
Database System Concepts - 6th Edition 1.3 ©Silberschatz, Korth and Sudarshan
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
Database System Concepts - 6th Edition 1.4 ©Silberschatz, Korth and Sudarshan
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
Database System Concepts - 6th Edition 1.5 ©Silberschatz, Korth and Sudarshan
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
Database System Concepts - 6th Edition 1.6 ©Silberschatz, Korth and Sudarshan
DBMS Architecture-1 Tier
• 1-Tier Architecture is the simplest form of DBMS architecture.
• In this setup, the database, the DBMS software, and the user
interface all reside on the same machine.
• There is no client-server separation.
• Everything the user needs to access and manage the data is on one
single layer.
• Example: Let’s say you're learning SQL on your laptop using MySQL
Workbench or SQL Server Management Studio (SSMS), where you've
installed the DBMS software, created your own database, and run SQL
queries directly on it. This is a 1-Tier Architecture, where everything
happens on your own system.
DBMS Architecture-1 Tier
DBMS Architecture-1 Tier
Advantages of 1-Tier Architecture
• 1-Tier Architecture is simple to set up and use, making it ideal for beginners
and personal projects.
• It offers fast performance because all operations are executed locally
without network delays.
• This architecture is great for development and testing, allowing developers
to work directly on their own system.
• Architecture
Disadvantages of 1-Tier Architecture
• 1-Tier Architecture is unsuitable for multi-user environments as it only
supports one user at a time.
• It doesn’t allow remote access or real-time collaboration since everything
runs on a single machine.
• It lacks scalability, making it inefficient for handling large datasets or
growing user demands.
DBMS Architecture-2 Tier-Client-Server
• 2-Tier Architecture in DBMS is a client-server model where the
application is split into two layers: the client (user interface) and
the database ( data storage).
• The client directly communicates with the database server to send queries
and retrieve data.
• It is commonly used in small to medium-sized applications like desktop or
intranet-based systems.
• Example: In a retail store, a desktop inventory system installed on your
computer acts as the client, directly connected to a central SQL Server that
stores all data. When you search for an item, the app sends a query to the
database, retrieves the result, and displays it instantly. This setup is a typical
example of 2-Tier Architecture, where the client talks directly to the
database.
DBMS Architecture-2 Tier-Client-Server
DBMS Architecture-2 Tier-Client-Server
• Use a 2-Tier Architecture when building small to medium-sized applications
where:ter Servers
• Security and scalability are not major concerns.
• The number of users is limited.
• You need faster performance with direct database access.
• Ideal for LAN-based desktop apps like inventory or billing systems.
Advantages of 2-Tier Architecture
• Easy to build and maintain for small-scale applications.
• Faster than multi-tier systems for simple transactions.
• Direct communication between the client and the database means less complexity.
Disadvantages of 2-Tier Architecture
• Not ideal for large applications with complex business logic.
• Scalability is limited because all clients connect directly to the database.
• Security risks are higher since the database is exposed to the client layer.
DBMS Architecture-3 Tier
• The 3-tier architecture in DBMS is a robust and scalable model that separates the
application into three distinct layers: the presentation layer, application layer, and
data layer.
• This structure allows developers to isolate user interface, business logic, and data
storage concerns.
• The Presentation Layer is the user interface, like a browser or mobile app, where
users interact with the application.
• The Application Layer contains business logic, often hosted on a server (e.g.,
.NET Core, [Link]), that processes data and handles rules.
• The Data Layer is the database server (e.g., SQL Server, MySQL) that stores and
manages data.
• Example: In an online shopping website, the user interface (presentation layer) runs in the browser, the
server-side code that handles orders and payment (application layer) runs on a backend server, and the product
data is stored in a database (data layer). When a user places an order, the request flows from the presentation
layer to the application server, which applies business rules and then communicates with the database to fetch
or update information.
DBMS Architecture-3 Tier
DBMS Architecture-3 Tier
Advantages of 3-Tier Architecture
• The 3-tier architecture provides a clear separation of concerns, which improves code maintainability and
simplifies application updates.
• This architecture enhances security by isolating the database from the client, reducing direct access risks.
• It improves scalability because additional servers can be added to handle application logic or user traffic
without modifying the database or UI.
• Performance can be optimized because each layer can be tuned or scaled independently based on demand.
• Teams can simultaneously work on different layers (UI, business logic, database), speeding up development
[Link] & Encyclopedias
Disadvantages of 3-Tier Architecture
• 3-tier systems are more complex to develop and require careful coordination between layers.
• Deploying and managing separate layers may increase infrastructure and operational costs.
• Debugging and troubleshooting can be slower since issues may span across multiple layers.
• Network latency may increase slightly due to the communication between layers.
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.
Database System Concepts - 6th Edition 1.7 ©Silberschatz, Korth and Sudarshan
View of Data
An architecture for a database system
Database System Concepts - 6th Edition 1.8 ©Silberschatz, Korth and Sudarshan
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
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.
Database System Concepts - 6th Edition 1.9 ©Silberschatz, Korth and Sudarshan
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
Database System Concepts - 6th Edition 1.10 ©Silberschatz, Korth and Sudarshan
Relational Model
All the data is stored in various tables.
Example of tabular data in the relational model Columns
Rows
Database System Concepts - 6th Edition 1.11 ©Silberschatz, Korth and Sudarshan
A Sample Relational Database
Database System Concepts - 6th Edition 1.12 ©Silberschatz, Korth and Sudarshan
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 System Concepts - 6th Edition 1.19 ©Silberschatz, Korth and Sudarshan
Hierarchical Data Model
• A hierarchical data model is the oldest type of the data model. It was developed by IBM in 1968. It
organizes data in a tree-like structure. Hierarchical model consists of the following :
• It contains nodes which are connected by branches.
• The topmost node is called the root node.
• If there are multiple nodes appear at the top level, then these can be called root segments.
• Each node has exactly one parent.
• One parent may have many children.
Characteristics of the Hierarchical Data Model
• Nodes and Branches: The data is stored in nodes, and each node is connected to its parent node by a
branch.
• Top-level node without a parent, known as the root node.
• Parent-kid Relationship: A parent may have more than one kid, but each node (child) only has one
parent.
Hierarchical Data Model
Example of Hierarchical Data Model
Think of a database used to hold information about technological devices:
● Electronics is the root node.
● TVs and portable electronics are examples of child nodes.
● Grandchild Nodes: Tube, LCD, and Plasma under televisions. These all stand for different categories under the Television
node.
Advantages of Hierarchical Data Model
● Simple Structure: Because of its tree-like structure, it is simple to comprehend and use.
● Data Security: A distinct parent-child hierarchy is used to guarantee data security.
● Effective Data Retrieval: Quick and effective way to query data in hierarchies.
Disadvantages of Hierarchical Data Model
● The hierarchical data model has limited flexibility since each child node can only have one parent.
● Insertion and Deletion Anomalies: Problems might occur with the addition or removal of nodes.
● Absent Data Independence: Modifications to the structure may result in problems with data administration.
Hierarchical Data Model
• In the above figure, Electronics is the root node which has two children i.e. Televisions and Portable
Electronics. These two has further children for which they act as parent. For example: Television has
children as Tube, LCD and Plasma, for these three Television act as parent. It follows one to many
relationship.
Network Data Model
• It is the advance version of the hierarchical data model. To organize data it uses directed graphs instead of
the tree-structure. In this child can have more than one parent. It uses the concept of the two data structures
i.e. Records and Sets.
Characteristics of the Network Data Model
● Graph Organization: Graphs are used to organize data and support numerous parent-child connections.
● Flexibility: Facilitates intricate connections, including many-to-many (m), one-to-many (1:m), and
one-to-one (1:1).
● Data linkage connects records by using linked lists and pointers.
Example of Network Data Model
Think of a database used to hold project and departmental data:
● Project as the Root Node
● Offspring Nodes: Projects 1 and 2
● Multiple Parents: Projects 1 and 2 are connected to Departments B and C, demonstrating the
many-to-many link.
Network Data Model
Advantages of Network Data Model
● Offers many ways to retrieve data and supports intricate interactions.
● No Insertion or Deletion abnormalities: Reduces structural abnormalities by allowing child nodes to have
numerous parents.
● Multiple access pathways are provided for efficient data access, which facilitates record searches.
Disadvantages of Network Data Model
● The graph structure is more difficult to maintain and build, which is a drawback of the complexity of the
network data model.
● Compared to contemporary relational models, partial data independence offers a restricted degree of data
independence.
● Less Popular: Compared to relational models, it is less often employed because of its complexity.
Network Data Model
• In the above figure, Project is the root node which has two children i.e. Project 1 and Project 2. Project 1
has 3 children and Project 2 has 2 children. Total there are 5 children i.e Department A, Department B and
Department C, they are network related children as we said that this model can have more than one parent.
So, for the Department B and Department C have two parents i.e. Project 1 and Project 2.
Database Users and Administrators
Database
Database System Concepts - 6th Edition 1.20 ©Silberschatz, Korth and Sudarshan
Database System Internals
Database System Concepts - 6th Edition 1.21 ©Silberschatz, Korth and Sudarshan