Introduction to Database Management System - AD(ICT)/AME/AP/SO(IT)
1. What is Database Management System?
Answer:
A database-management system (DBMS) is a collection of interrelated data and a
set of programs to access those data. The collection of data, usually referred
to as the database, contains information relevant to an enterprise
2. What
is the purpose of DBMS?
Answer:
The purpose of a Database Management System (DBMS) is to address the
limitations and challenges associated with conventional file-processing systems
in managing organizational data. DBMS serves the following purposes:
a.
Data Organization and Reduction of
Redundancy:
(1) DBMS
eliminates data redundancy by organizing and storing data in a centralized and
structured manner.
(2) Data
is stored in a systematic way, reducing the chances of inconsistencies caused
by duplicated information.
b.
Efficient Data Retrieval:
(1) DBMS
provides efficient methods for retrieving data, allowing users to query and
retrieve specific information without the need for manual extraction or the
development of new application programs.
c.
Data Integration and Minimization of Data
Isolation:
(1) The
DBMS integrates data from various files and formats, providing a unified view
of the data.
(2) Data
isolation is minimized, as the DBMS facilitates writing new applications to
retrieve the necessary data in a more straightforward manner.
d.
Enforcement of Data Integrity:
(1) DBMS
enforces consistency constraints, ensuring that data values stored in the
database satisfy specified rules.
(2) Changes
to constraints and the addition of new constraints are more easily managed
within the DBMS.
e.
Atomic Transactions and Data Consistency:
(1) DBMS
ensures atomicity in transactions, guaranteeing that either the entire
transaction is completed or none of it is, preventing inconsistent database
states in case of failures.
f.
Concurrency Control:
(1) DBMS
includes mechanisms for managing concurrent access to data, preventing
anomalies that may arise from simultaneous updates by multiple users.
g.
Enhanced Security:
(1) DBMS
provides robust security features, allowing for controlled access to specific
data based on user roles and permissions.
(2) Security
constraints are more effectively enforced within the DBMS environment.
In summary, the DBMS aims to overcome the
shortcomings of file-processing systems by providing a more efficient,
organized, and secure way to manage, retrieve, and maintain organizational
data. It introduces centralized control and standardized methods for data
management, offering a comprehensive solution to the challenges posed by
traditional data processing systems.
3. What
is view of data? Explain data abstraction and different levels of it.
Answer:
A database system is a collection of interrelated data and a set of programs
that allow users to access and modify these data. A major purpose of a database
system is to provide users with an abstract view of the data. That is, the
system hides certain details of how the data are stored and maintained.
For the system to be usable, it must
retrieve data efficiently. The need for efficiency has led designers to use
complex data structures to represent data in the database. Since many
database-system users are not computer trained, developers hide the complexity
from users through several levels of abstraction, to simplify users’ interactions
with the system:
a.
Physical level: The lowest level of
abstraction describes how data are actually stored. The physical level
describes complex low level data structures in details.
b.
Logical level: This level of abstraction
describes what data are stored and what relations are existing in those data.
c.
View level: This is the highest level of
abstraction. The view level provides a customized and simplified representation
of the data for specific user groups or applications.
4. Explain
Instance and Schemas.
Answer:
The collection of information stored in a particular moment is called an
instance of a database. The overall design of the database is called the
database schema.
Database systems have several schemas,
partitioned according to the levels of abstraction. The physical schema describes
the database design at the physical level, while the logical schema describes
the database design at the logical level. A database may also have several
schemas at the view level, sometimes called subschemas, that describe different
views of the database.
5. What
is data model? Explain different categories of data model.
Answer:
A data model provides a way to describe the design of a database at the
physical, logical, and view levels.
The data models can be classified into
four different categories:
a.
Relational data model
b.
Entity-relationship data model
c.
Object-based data model
d.
Semi structured data model
6. Explain
DDL, DQL, DML and DCL.
Answer:
DDL, DQL, DML and DCL is written below,
DDL is for defining and managing the
structure of the database.
DQL is for querying or retrieving data
from the database.
A data-manipulation language (DML) is a
language that enables users to access or manipulate data as organized by the
appropriate data model. The types of access are,
a.
Retrieval of information stored in the database.
b.
Insertion of new information into the
database.
c.
Deletion of information from the database
d.
Modification of information stored in the
database
DCL is for controlling access and
permissions to the database.
7. What
is normalization? Explain different forms of normalization and ways of
achieving normalization.
Answer:
Normalization is a process used in the design and organization of relational
database tables to reduce data redundancy and improve data integrity. The goal
of normalization is to eliminate or minimize data anomalies that can arise when
data is stored in a non-optimal manner. Data anomalies can include insertion,
update, and deletion anomalies.
There are different normal forms, each
representing a level of normalization. The most commonly discussed normal forms
are the first normal form (1NF), second normal form (2NF), and third normal
form (3NF). Here's a brief explanation of each:
a.
First Normal Form (1NF):
(1) Each
table cell should contain a single, atomic value.
(2) Eliminate
repeating groups or arrays in data.
(3) Ensure
that each column contains only one type of data.
b.
Second Normal Form (2NF):
(1) Must
be in 1NF.
(2) Eliminate
partial dependencies, meaning that non-key attributes are dependent on the
entire primary key, not just a part of it.
c.
Third Normal Form (3NF):
(1) Must
be in 2NF.
(2) Eliminate
transitive dependencies, where non-key attributes depend on other non-key
attributes.
8. What
is storage manager and components of storage manager?
Answer:
The storage manager is the component of a database system that provides the
interface between the low-level data stored in the database and the application
programs and queries submitted to the system. It is responsible for accessing
file manager. The raw data is stored on the disk using the file system provided
by the operating system. The storage manager translates the various DML file
systems into low-level file system commands. Thus, the storage
manager is responsible for storing, retrieving, and updating data in the
database.
The storage manager
components include:
a.
Authorization and integrity manager, which
tests for the satisfaction of integrity constraints and checks the authority of
users to access data
b.
Transaction manager, which ensures that the
database remains in a consistent (correct) state despite system failures, and
that concurrent transaction executions proceed without conflicting.
c.
File manager, which manages the allocation
of space on disk storage and the data structures used to represent information
stored on disk.
d.
Buffer manager, which is responsible for
fetching data from disk storage into main memory, and deciding what data to
cache in main memory. The buffer manager is a critical part of the database
system, since it enables the database to handle data sizes that are much larger
than the size of main memory.
9. What
are the components of query processor?
Answer:
The query processor components include:
a.
DDL interpreter, which interprets DDL
statements and records the definitions in the data dictionary.
b.
DML compiler, which translates DML
statements in a query language into an evaluation plan consisting of low-level
instructions that the query evaluation engine understands. A query can usually
be translated into any of a number of alternative evaluation plans that all
give the same result. The DML compiler also performs query optimization; that
is, it picks the lowest cost evaluation plan from among the alternatives.
c.
Query evaluation engine, which executes
low-level instructions generated by the DML compiler.
10. What
is transaction? Explain properties of transaction/ACID properties.
Answer:
A
transaction, in the context of databases, is a logical unit of work that
consists of one or more operations performed against a database. Transactions
are crucial in ensuring data integrity and consistency in a database management
system (DBMS). The properties that guarantee the reliability of transactions
are often referred to as ACID properties, which stands for Atomicity, Consistency,
Isolation, and Durability.
a.
Atomicity (A): Atomicity ensures that a
transaction is treated as a single, indivisible unit of work. Either all the
operations within the transaction are successfully completed, or none of them
are. If any part of the transaction fails, the entire transaction is rolled
back, and the database is left unchanged.
b.
Consistency (C): Consistency ensures that
a transaction brings the database from one valid state to another. The database
should satisfy certain integrity constraints before and after the transaction.
If a transaction violates any integrity constraints, it is rolled back to
maintain the overall consistency of the database.
c.
Isolation (I): Isolation ensures that the
execution of one transaction is isolated from the execution of other
transactions. Even though multiple transactions may be executing concurrently,
the final result should be as if the transactions were executed sequentially.
Isolation prevents interference between transactions and helps maintain data
integrity.
d.
Durability (D): Durability guarantees that
once a transaction is committed, its effects will persist even in the face of
system failures. The changes made by a committed transaction are permanent and
survive any subsequent system crashes or power outages. This is typically
achieved through mechanisms like transaction logs and database backups.
Together, the ACID
properties ensure that database transactions are reliable and maintain the
integrity of the data. These properties are fundamental in scenarios where data
consistency is critical, such as in financial transactions, reservation
systems, and other applications where accurate and reliable data handling is
essential.
11. Explain
Database architecture.
Answer:
In a tiered architecture, also known as a multi-tier architecture, software
components are distributed across multiple layers, each responsible for
specific functionalities. The most common tiers are:
a.
Tier 1 (Presentation Tier):
(1) Also
known as the client tier or user interface tier.
(2) Deals
with the presentation and user interaction.
(3) Includes
user interfaces, web browsers, or other client applications.
b.
Tier 2 (Application Tier):
(1) Often
referred to as the business logic tier.
(2) Manages
the application's business logic and processes.
(3) Handles
data validation, processing, and other application-specific functionalities.
c.
Tier 3 (Data Tier):
(1) Also
called the data storage or database tier.
(2) Manages
data storage, retrieval, and persistence.
(3) Includes
databases and data storage systems.
This architecture is
beneficial for scalability, maintainability, and separation of concerns. It
allows for easier modification and updates to specific layers without affecting
the entire system. The communication between tiers can be achieved through
various protocols and technologies such as HTTP, REST, or messaging systems.
12. Explain
different users of database and roles of them.
Answer:
There are four different types of database-system users, differentiated by the
way they expect to interact with the system. Different types of user interfaces
have been designed for the different types of users.
a.
Na¨ıve users are unsophisticated users who
interact with the system by invoking one of the application programs that have
been written previously.
b.
Application programmers are computer
professionals who write application programs. Application programmers can
choose from many tools to develop user interfaces. Rapid application development
(RAD) tools are tools that enable an application programmer to construct forms
and reports with minimal programming effort.
c.
Sophisticated users interact with the system
without writing programs. Instead, they form their requests either using a
database query language or by using tools such as data analysis software.
Analysts who submit queries to explore data in the database fall in this
category
d.
Specialized users are sophisticated users
who write specialized database applications that do not fit into the
traditional data-processing framework. Among these applications are computer-aided
design systems, knowledgebase and expert systems, systems that store data with
complex data types (for example, graphics data and audio data), and
environment-modeling systems.
Points to Remember
1. The
primary goal of a DBMS is to provide a way to store and retrieve database
information that is both convenient and efficient.
2. The
logical level thus describes the entire database in terms of a small number of
relatively simple structures. Although 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. This is
referred to as physical data independence.
3. Physical
data independence is seen in logical level.
4. Database
administrators, who must decide what information to keep in the database, use
the logical level of abstraction.
5. Type
definition is done in logical level.
6.
Programmers
and Database Administrators work at Logical level.
7. End users use View Level Abstraction.
8. Views
also provide a security mechanism to prevent users from accessing certain parts
of the database
9. Database
schemas changed very rarely.
10. Tables
are also known as relations.
11. A
query is a statement requesting the retrieval of information. The portion of a
DML that involves information retrieval is called a query language.
12. The
SQL query language is nonprocedural
13. SQL
doesn’t support actions such as user input, output over display and
communication over network.
14. A
relationship is an association among several entities.
15. Another
method for designing a relational database is to use a process commonly known
as normalization.
16. The
goal is to generate a set of relation schemas that allows us to store
information without unnecessary redundancy, yet also allows us to retrieve
information easily.
17. The
functional components of a database system can be broadly divided into the storage manager and the
query processor components.
18. The
query processor is important because it helps the database system to simplify
and facilitate access to data
মন্তব্যসমূহ
একটি মন্তব্য পোস্ট করুন