gr 2

Is a special type of programming language used to define and manipulate a database. A
database system provides a data-definition language to specify the database schema and a datamanipulation language to express database queries and updates

A data definition language (DDL) is a computer language used to create and modify the
structure of database objects in a database.

Data-Definition Language (DDL) Commands

Used to create new database objects like tables, indexes, or constraint

Used to modify the structure of existing database objects.

Used to delete database objects.

Removes all records from a table, but keeps the table structure intact.

Adds comments or notes to the data dictionary (metadata) of the database

Renames an existing database object

Provides specific privileges to users or roles

Removes specific privileges from users or roles.

Features Of Database Management and Design

define the allowable values for a specific attribute (or column) in a
database table.

ensures that relationships between tables are maintained correctly

are conditions or rules that must always be true in a database.

controls access to database resources and determines what actions users
or roles are allowed to perform

is a centralized repository that contains metadata about the database,
such as the structure of tables, data types, constraints, and other details.

is a language that enables users to access or
manipulate data as organized by the appropriate data model. SELECT, UPDATE, INSERT INTO
and DELETE FROM.

The types of access are

require a user to specify what data are needed and how to get those data.

(also referred to as nonprocedural DMLs) require a user to specify what data
are needed without specifying how to get those data.

is a statement requesting the retrieval of information

The portion of a DML that involves
information retrieval is called a

The primary goal of a database system is to retrieve information from and store new
information in the database. People who work with a database can be categorized as database users
or database administrators

- are unsophisticated users who interact with the system by invoking one of the application
programs that have been written previously.
−Uses web/mobile/Users interface

− are computer professionals who write application programs. Application programmers can
choose from many tools to develop user interfaces.
− use Rapid application development (RAD) tools.

Sophisticated users

Specialized users

It refers to the structural design and methodology of a database system, which forms the
core of a Database Management System (DBMS). It is software that interacts with a database to
perform various functions such as storing, retrieving, updating, and managing data. These
applications are essential for businesses and organizations to organize and utilize their data
effectively.

is greatly influenced by the underlying computer
system on which the database system runs. 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.

It is software that interacts with a database to perform various functions such as storing,
retrieving, updating, and managing data. These applications are essential for businesses and
organizations to organize and utilize their data effectively

Common Database Application Architectural Pattern:

the presentation layer and the application layer are
combined into a single tier, while the data layer remains separate. This architecture
is simpler and often used for smaller applications or applications with less complex
business logic

each layer (presentation, application, and data) is
separated into its own tier. This architecture offers better scalability, modularity,
and maintainability compared to the two-tier architecture. It is commonly used for
larger, more complex applications where separation of concerns is important

is a collection of operations that performs a single logical function in a database
application. We require that transactions do not violate any database-consistency constraints. That
is, if the database was consistent when a transaction started, the database must be consistent when
the transaction successfully terminates

Either the entire transaction takes place at once or doesn’t happen at all. There
is no midway i.e. transactions do not occur partially. Each transaction is considered as one
unit and either runs to completion or is not executed at all.

This means that integrity constraints must be maintained so that the database
is consistent before and after the transaction. It refers to the correctness of a database.

This property ensures that multiple transactions can occur concurrently without
leading to the inconsistency of database state. Transactions occur independently without
interference.

This property ensures that once the transaction has completed execution, the
updates and modifications to the database are stored in and written to disk and they persist
even if a system failure occurs. These updates now become permanent and are stored in
non-volatile memory. The effects of the transaction, thus, are never lost

provide a mechanism to ensure correctness and consistency
of a database in a way such that each transaction is a group of operations that acts a single unit,
produces consistent results, acts in isolation from other operations and updates that it makes are
durably stored.

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 for the interaction with the file manager. The raw data are stored on the disk using
the file system, which is usually provided by a conventional operating system. The storage
manager translates the various DML statements into low-level file-system commands. Thus, the
storage manager is responsible for storing, retrieving, and updating data in the database.

storage manager components

which tests for the satisfaction of integrity
constraints and checks the authority of users to access data

which ensures that the database remains in a consistent
(correct) state despite system failures, and that concurrent transaction executions
proceed without conflicting.

which manages the allocation of space on disk storage and the data
structures used to represent information stored on disk.

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

is the process of translating a high-level query, such as SQL, into a low evel query that can be executed by the database system.

of a database system has the function of determining how to answer
the requests for information from a user in the most optimal manner. It is a crucial component of
a database management system (DBMS). It plays a central role in interpreting and executing
queries submitted to the database, ensuring efficient retrieval of data

Components of Query Processor

which interprets DDL statements and records the definitions in
the data dictionary.

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.

which executes low-level instructions generated by
the DML compiler