Overview of DBMS (Database Management System):
• Definition: A DBMS is a software system that manages, organizes, and
retrieves data from a database. It provides an interface for users and
applications to interact with the database efficiently.
• Functions:
• Data storage, retrieval, and manipulation.
• Data security and integrity maintenance.
• Concurrent access control.
• Backup and recovery mechanisms.
Data Models:
• Definition: A data model is a conceptual representation of how data is
organized and stored in a database. It defines the structure, relationships,
and constraints of the data.
• Types of Data Models:
• Relational Model: Organizes data into tables with rows and
columns. Example: MySQL, PostgreSQL.
• Hierarchical Model: Structures data in a tree-like format. Example:
IMS (Information Management System).
• Network Model: Represents data with entities and relationships.
Example: IDMS (Integrated Database Management System).
• Object-Oriented Model: Treats data as objects. Example:
MongoDB.
• Entity-Relationship Model (ER Model): Represents entities,
attributes, and relationships between them.
Database Languages:
• SQL (Structured Query Language): Standard language for interacting
with relational databases. Example:
SELECT * FROM Customers WHERE Country='USA';
• PL/SQL (Procedural Language/SQL): Extension of SQL used in Oracle
databases to create stored procedures and functions.
• NoSQL Query Languages: For non-relational databases like MongoDB
(using MongoDB Query Language) or Cassandra Query Language (CQL) for
Apache Cassandra.
Database Administrator (DBA):
• Role: Responsible for managing and maintaining the database system.
• Tasks:
• Designing database structures.
• Security and user management.
• Performance monitoring and optimization.
• Backup and recovery planning.
Database Users:
• End Users: Access data using applications or interfaces.
• Application Programmers: Develop software applications that interact
with the database.
• Database Administrators: Manage the database system.
Example:
Scenario: A retail company maintains a database of its products, customers, and
orders.
• DBMS Used: PostgreSQL (Relational DBMS)
• Data Model: Relational Model
• Database Languages: SQL for querying and managing data.
• Database Administrator: Manages user access, ensures data integrity,
optimizes database performance.
• Database Users:
• End Users: Customer service representatives accessing customer
information.
• Application Programmers: Developing an inventory management
system.
• Database Administrators: Managing database security and
backups.
This scenario illustrates how a DBMS is applied in a real-world context, utilizing
various data models, languages, and involving different types of users under the
supervision of a database administrator.
Explain the three-level schema architecture in DBMS?
The three-schema architecture divides the database into three-level used to
create a separation between the physical database and the user application. In
simple terms, this architecture hides the details of physical storage from the user.
The database administrator (DBA) responsible is to change the structure of
database storage without affecting the user’s view. It deals with the data, the
relationship between them and the different access methods implemented on the
database. The logical design of database is called a schema
This architecture contains three layers of database management system, which
are as follows −
• External level
• Conceptual level
• Internal level
External/ View level
This is the highest level of database abstraction. It includes a number of external
schemas or user views. This level provides different views of the same database
for a specific user or a group of users. An external view provides a powerful and
flexible security mechanism by hiding the parts of the database from a particular
user.
Conceptual or Logical level
This level describes the structure of the whole database. It acts as a middle layer
between the physical storage and user view. It explains what data to be stored
in the database, what the data types are, and what relationship exists among
those data. There is only one conceptual schema per database.
This level describes the structure of the whole database. It acts as a middle layer
between the physical storage and user view. It explains what data to be stored
in the database, what the data types are, and what relationship exists among
those data. There is only one conceptual schema per database.
Internal or Physical level
This is the lowest level of database abstraction. It describes how the data is stored
in the database and provides the methods to access data from the database. It
allows viewing the physical representation of the database on the computer
system.
The interface between the conceptual and internal schema identifies how an
element in the conceptual schema is stored and how it may be accessed. It is one
which is closest to physical storage. The internal schema not only defines different
stored record types, but also specifies what indices exist, how stored fields are
represented.
The three level schema architecture in DBMS is given below −
1. Question: Explain the role of a Database Administrator (DBA) in the context of a Database
Management System (DBMS). (5 marks)
2. Question: Differentiate between the Relational Model and the Object-Oriented Model in the
context of data modeling. Provide examples of databases that use each model. (5 marks)
3. Question: Describe the significance of the three-level schema architecture in a Database
Management System (DBMS). How does it provide abstraction and separation between
different levels? (5 marks)
4. Question: Compare and contrast SQL (Structured Query Language) with NoSQL query languages.
Provide examples of scenarios where each type of language is more suitable. (5 marks)
5. Question: Illustrate the use of a Relational Database Management System (RDBMS) in a real-
world scenario. Discuss the choice of DBMS, data model, and database languages, considering
a specific example like a retail company's database. (5 marks)
