Databases and Database Users

A database is a collection of related data
Data is any fact that can be recorded and has an implicit meaning.
eg: Names or telephone numbers

Introduction to DBMS:

Database management system (DBMS) is a collection of programs that enables users to create and maintain a database. The DBMS is a general-purpose software system that facilitates the processes of defining, constructing, manipulating, and sharing databases among various users and applications

Defining a database involves specifying the data types, structures, and constraints of the data to be stored in the database. The database definition or descriptive information is also stored by the DBMS in the form of a database catalog or dictionary; it is called meta-data
- Data Types: For instance, in a database for a library system, you might have a data type for "Book_Title" as a string.
```
CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Book_Title VARCHAR(255),
    Author VARCHAR(100),
    PublicationDate DATE,
    ISBN VARCHAR(13)
);
```
- Structures: Database structures define how the data is organized and stored. This includes tables, indexes, and relationships between tables. In the library example, you might have a structure for a "Members" table.
```
CREATE TABLE Members (
    MemberID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100),
    JOIN_DATE DATE
);
```
- Constraints: Constraints define rules to maintain the integrity and accuracy of the data. Examples include primary keys, foreign keys, unique constraints, etc. In the library database, you might have a constraint to ensure that ISBN numbers in the "Books" table are unique.
```
CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(255),
    Author VARCHAR(100),
    PublicationDate DATE,
    ISBN VARCHAR(13) UNIQUE
);
```
- Metadata: Metadata is data about the data. It includes information about the database structure, data types, constraints, and relationships. This information is stored in a database catalog or dictionary. In SQL, you can query the metadata using system tables.
```
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'Books';
```
  In this example, the metadata query would return information about the columns (column_name) and their data types (data_type) in the "Books" table.
Constructing the database is the process of storing the data on some storage medium that is controlled by the DBMS.
Manipulating a database includes functions such as querying
the database to retrieve specific data, updating the database to reflect changes in the miniworld, and generating reports from the data.
Sharing a database allows multi-
ple users and programs to access the database simultaneously.
An application program accesses the database by sending queries or requests for
data to the DBMS. A query typically causes some data to be retrieved; a
transaction may cause some data to be read and some data to be written into the
database.
Protection includes system protection against hardware or software malfunction (or crashes) and security protection against unauthorized or malicious access.

Diagram of Simplified DB environment

(1.3) Characteristics of the Database Approach:

Self-Describing Nature of a Database System:
The database system contains not only the database itself but also a complete definition or description of the database structure and constraints.
The information stored in the catalog is called meta-data, and it describes the structure of the primary database.
Insulation between Programs and Data, and Data Abstraction:
An operation (also called a function or method) is specified in two parts. The interface (or signature) of an operation includes the operation name and the data types of its arguments (or parameters). The implementation (or method) of the operation is specified separately and can be changed without affecting the interface. User application programs can operate on the data by invoking these operations through their names and arguments, regardless of how the operations are implemented. This may be termed program-operation independence.
Support of Multiple Views of the Data:
A database typically has many users, each of whom may require a different perspec-
tive or view of the database. A view may be a subset of the database or it may con-
tain virtual data that is derived from the database files but is not explicitly stored.
Eg: one user of the database of may be interested only in accessing and printing the transcript of each student. A second user, who is interested only in checking that students have taken all the prerequisites of each course.
Sharing of Data and Multiuser Transaction Processing:
Allows multiple users to access the database at the same time.
The DBMS must include concurrency control software to ensure that several users trying to update the same data do so in a controlled manner so that the result of the updates is correct. For example, when several reservation agents try to assign a seat on an airline flight, the DBMS should ensure that each seat can be accessed by only one agent at a time for assignment to a passenger.
These types of applications are generally called online transaction processing (OLTP) applications.
The concept of a transaction has become central to many database applications. A
transaction is an executing program or process that includes one or more database
accesses, such as reading or updating of database records.

(1.4) Actors on the Scene:

Actors on the scene are people whose jobs involve the day-to-day use of a large database.

Workers behind the scene are those who work to maintain the database system environment but who are not actively interested in the database contents as part of their daily job.

Database Administrators:
The DBA is responsible for authorizing access to the database, coordinating and monitoring its use, and acquiring software and hardware resources as needed. The DBA is accountable for problems such as security breaches and poor system response time.
Database Designers:
Those who are responsible for representation of the data stored in the DB. They communicate with users and develop views of the DB which meet the requirements and finally an integrated version of different views is implemented which is capable of supporting the requirements of all the users.
End Users:
The DB primarily exists for these users. They are those who access the DB for any reason.
- Casual end users occasionally access the database, but they may need differ-
  ent information each time. They use a sophisticated database query language
  to specify their requests and are typically middle- or high-level managers or
  other occasional browsers.
- Naive or parametric end users make up a sizable portion of database end
  users. Their main job function revolves around constantly querying and
  updating the database, using standard types of queries and updates—called
  canned transactions—that have been carefully programmed and tested. The
  tasks that such users perform are varied:
  - Bank tellers check account balances and post withdrawals and deposits.
  - Reservation agents for airlines, hotels, and car rental companies check
    availability for a given request and make reservations.
- Sophisticated end users include engineers, scientists, business analysts, and
  others who thoroughly familiarize themselves with the facilities of the
  DBMS in order to implement their own applications to meet their complex
  requirements.
- Standalone users maintain personal databases by using ready-made pro-
  gram packages that provide easy-to-use menu-based or graphics-based
  interfaces. An example is the user of a tax package that stores a variety of per-
  sonal financial data for tax purposes.
System Analysts and Application Programmers (Software Engineers):
System analysts determine the requirements of end users, especially naive and
parametric end users, and develop specifications for standard canned transactions
that meet these requirements. Application programmers implement these specifi-
cations as programs; then they test, debug, document, and maintain these canned
transactions.

"Canned transactions" typically refer to pre-defined or pre-packaged transactional processes or routines that are readily available for use. These transactions are often designed to perform common and repetitive operations within a software system, and they are usually provided as part of an application or system framework. The term "canned" suggests that these transactions are ready-made or pre-prepared, like goods in a can that are ready to be used without requiring customization.

(1.5) Workers behind the Scene:

Those associated with design, development, and operation of the DBMS software and system

environment.

DBMS system designers and implementers design and implement the DBMS modules and interfaces as a software package. Since DBMS is a very complex software, they implement it such that it must interface with other system software such as the operating system and compilers for various programming languages.
Tool developers design and implement tools—the software packages that facilitate database modeling and design, database system design, and improved performance. Tools are optional packages that are often purchased separately from independent software vendors.

(1.6) Advantages of Using the DBMS Approach:

Controlling Redundancy:
In a university, if each staff member is using their own files to perform their job/task, then there might be errors in data and might take a lot of time if a common section is to be updated in all the files, whereas in DBMS, all the users can access the same data and do their respective jobs, and only one person is enough to update any specific section.
Restricting Unauthorized Access:
A DBMS should provide a security and authorization subsystem, which the DBA uses to create accounts and to specify account restrictions. The users are typically given their account numbers and passwords so that they have access to limited data, avoiding access to confidential data.
Providing Persistent Storage for Program Objects:
The capability of storing and retrieving program objects or data in a way that allows them to persist beyond the lifetime of the program's execution. In other words, it involves the ability to save program data to a storage medium (such as a hard disk or a database) so that the data remains available even after the program terminates or the computer is shut down.
Providing Storage Structures and Search Techniques for Efficient Query Processing:
Auxiliary files called indexes (the word exists, dw) based on tree or hash data-structures are used, and are loaded to the main memory when required, to imporve the query speed.
The query processing and optimization module of the DBMS is responsible for
choosing an efficient query execution plan.
Providing Backup and Recovery:
The backup and recovery subsystem of the DBMS is responsible for recovery in cases of failure. For instance, if a failure occurs during a transaction, then the recovery system must ensure that the DB is restored to a state before the transaction took place, or to a state where the transaction could be resumed.
Providing Multiple User Interfaces:
Provides GUIs for different kinds of users, like developers, parametric users, etc, based on their usage requirements.
Representing Complex Relationships among Data:
A DBMS must have the capability to represent a variety of complex relationships among the data, to define new relationships as they arise, and to retrieve and update related data easily and efficiently.
Enforcing Integrity Constraints:
A DBMS should provide capabilities for defining and enforcing these constraints. The simplest type of integrity constraint involves specifying a data type for each data item. The constraints may be something like key, unique.
Permitting Inferencing and Actions Using Rules:
For example, there may be complex rules in the mini-world application for determining when a student is on probation. These can be specified declaratively as rules, which when compiled and maintained by the DBMS can determine all students on probation.
- A trigger is a form of a rule activated by updates to the table, which results in performing some additional operations to some other tables, sending messages, and so on.
- More involved procedures to enforce rules are popularly called stored procedures.
- powerful functionality is provided by active database systems, which provide active rules that can automatically initiate actions when certain events and conditions occur.
Additional Implications of using DB approach:
- Potential for Enforcing Standards. The database approach permits the DBA to
  define and enforce standards among database users in a large organization.
- Reduced Application Development Time. Once a database is up and running, substantially less time is generally required to create new applications using
  DBMS facilities.
- Flexibility. Modern DBMSs allow certain types of evolutionary changes to the structure of the database without affecting the stored data and the existing application programs.
- Availability of Up-to-Date Information. A DBMS makes the database available
  to all users. As soon as one user’s update is applied to the database, all other users
  can immediately see this update.
- Economies of Scale. The DBMS approach permits consolidation of data and applications, thus reducing the amount of wasteful overlap between activities of data processing personnel in different projects or departments as well as redundancies among applications.