ADBMS

 Textbook pdf : 

Database System Concepts

links:

Module 4 - chapter 1

Module 4 - chapter 2

Introduction

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. The primary goal of a DBMS is to provide a way to store and retrieve database information that is both convenient and efficient. Database systems are designed to manage large bodies of information. Management of data involves both defining structures for storage of information and providing mechanisms for the manipulation of information. In addition, the database system must ensure the safety of the information stored, despite system crashes or attempts at unauthorized access .If data are to be shared among several users, the system must avoid possible anomalous results.

Database-System Applications

Databases are widely used. Here are some representative applications:

• Enterprise Information

◦ Sales: For customer, product, and purchase information.

◦ Accounting: For payments, receipts, account balances, assets and other accounting information. 

◦ Human resources: For information about employees, salaries, payroll taxes, and benefits, and for generation of paychecks.

◦ Manufacturing: For management of the supply chain and for tracking productionofitemsinfactories,inventoriesofitemsinwarehousesandstores, and orders for items.

◦ Online retailers: For sales data noted above plus online order tracking, generation of recommendation lists, and maintenance of online product evaluations.

• Banking and Finance

◦ Banking: For customer information, accounts, loans, and banking transactions.

◦ Credit card transactions: For purchases on credit cards and generation of monthly statements.

 ◦ Finance: For storing information about holdings, sales, and purchases of f inancial instruments such as stocks and bonds; also for storing real-time market data to enable online trading by customers and automated trading by the firm.

• Universities: 

For student information, course registrations, and grades (in addition to standard enterprise information such as human resources and accounting).

• Airlines: 

For reservations and schedule information. Airlines were among the first to use databases in a geographically distributed manner.

• Telecommunication: 

For keeping records of calls made, generating monthly bills, maintaining balances on prepaid calling cards, and storing information about the communication networks.

purpose of database systems:

Database systems are design to deal with large volumes of data. Data management comprises both the construction of data storage systems and the provision of data manipulation methods.

The goal of a database management system (DBMS) is to transform the following:

1. Data into information.

2. Information into knowledge.

3. Knowledge of the action.

Furthermore, it has  to manage the following insecurities:

Avoid data redundancy and inconsistency:

If there are multiple copies of the same data, it just avoids it. It just maintains data in a single repository. Also, the purpose of database systems is to make the database consistent.

Difficulty in accessing data:

A database system can easily manage to access data. Through different queries, it can access data from the database.

Data isolation:

Data are isolated in several fields in the same database.

Atomicity of updates:

In case of power failure, the database might lose data. So, this feature will automatically prevent data loss.

Concurrent access:

Users can have multiple access to the database at the same time.

Security problems:

Database systems will make the restricted access. So, the data will not be vulnerable.

Supports multiple views of data:

It can support multiple views of data to give the required view as their needs. Only database admins can have a complete view of the database. We cannot allow the end-users to have a view of developers.

Database Languages: 

A database system provides a data-definition language to specify the database schema and a data-manipulation language to express database queries and updates. In practice, the data-definition and data-manipulation languages are not two separate languages; instead they simply form parts of a single database language, such as the widely used SQL language. 

Data Definition Language(DDL):

We specify a database schema by a set of definitions expressed by a special language called a data-definition language (DDL). We specify the storage structure and access methods used by the database system by a set of statements in a special type of DDL called a data storage and definition language. These statements define the implementation details of the database schemas, which are usually hidden from the user.

The data values stored in the database must satisfy certain consistency constraints. they are :

• Domain Constraints. A domain of possible values must be associated with every attribute (for example, integer types, character types, date/time types). Declaring an attribute to be of a particular domain acts as a constraint on the values that it can take. Domain constraints are the most elementary form of integrity constraint. They are tested easily by the system whenever a new data item is entered into the database. 

• Referential Integrity. There are cases where we wish to ensure that a value that appears in one relation for a given set of attributes also appears in a certain set of attributes in another relation (referential integrity).  Database modifications can cause violations of referential integrity. When a referential-integrity constraint is violated, the normal procedure is to reject the action that caused the violation. 

• Assertions. An assertion is any condition that the database must always satisfy. Domain constraints and referential-integrity constraints are special forms of assertions. However, there are many constraints that we cannot express by using only these special forms. For example, “Every department must have at least five courses offered every semester” must be expressed as an assertion. When an assertion is created, the system tests it for validity. If the assertion is valid, then any future modification to the database is allowed only if it does not cause that assertion to be violated. 

• Authorization. We may want to differentiate among the users as far as the type of access they are permitted on various data values in the database. These differentiations are expressed in terms of authorization, the most common being: read authorization, which allows reading, but not modification, of data; insert authorization, which allows insertion of new data, but not modification of existing data; update authorization, which allows modification, but not deletion, of data; and delete authorization, which allows deletion of data. We may assign the user all, none, or a combination of these types of authorization.

Data Manipulation Language:

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: 

• Retrieval of information stored in the database 

• Insertion of new information into the database 

• Deletion of information from the database 

• Modification of information stored in the database 

There are basically two types:

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

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

Declarative DMLs are usually easier to learn and use than are procedural DMLs. However, since a user does not have to specify how to get the data, the database system has to figure out an efficient means of accessing data.

view of data

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. designers hide irrelevant information like complex data structures that are used to store data from users using data abstraction. developers hide the complexity from users through several levels of abstraction, to simplify users’ interactions with the system:

• Physical level. The lowest level of abstraction describes how the data are actually stored. The physical level describes complex low-level data structures in detail. 

• Logical level. The next-higher level of abstraction describes what data are stored in the database, and what relationships exist among those data. 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. Database administrators, who must decide what information to keep in the database, use the logical level of abstraction. 

• View level. The highest level of abstraction describes only part of the entire database. Even though the logical level uses simpler structures, complexity remains because of the variety of information stored in a large database. Many users of the database system do not need all this information; instead, they need to access only a part of the database. The view level of abstraction exists to simplify their interaction with the system. The system may provide many views for the same database.

• schemas : Databases change over time as information is inserted and deleted. The collection of information stored in the database at a particular moment is called an instance of the 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.

• data models: Underlying the structure of a database is the data model: a collection of conceptual tools for describing data, data relationships, data semantics, and consistency constraints. A data model provides a way to describe the design of a database at the physical, logical, and view levels.

            there are different data models like
• Relational Model. The relational model uses a collection of tables to represent both data and the relationships among those data. Each table has multiple columns, and each column has a unique name. Tables are also known as relations.
• Entity-Relationship Model. The entity-relationship (E-R) data model uses a collection of basic objects, called entities, and relationships among these objects. An entity is a “thing” or “object” in the real world that is distinguishable from other objects.
• Object-Based Data Model. Object-  this model can be seen as extending the E-R model with notions of encapsulation, methods (functions), and object identity. The object-relational data model combines
features of the object-oriented data model and relational data model.
• Semi structured Data Model. The semi structured data model permits the specification of data where individual data items of the same type may have different sets of attributes. This is in contrast to the data models mentioned earlier, where every data item of a particular type must have the same set
of attributes. The Extensible Markup Language (XML) is widely used to represent semi structured data.

Relational databases

A relational database is a type of database that stores and provides access to data points that are related to one another. Relational databases are based on the relational model, an intuitive, straightforward way of representing data in tables. In a relational database, each row in the table is a record with a unique ID called the key. The columns of the table hold attributes of the data, and each record usually has a value for each attribute, making it easy to establish the relationships among data points. 

The relational model means that the logical data structures—the data tables, views, and indexes—are separate from the physical storage structures. This separation means that database administrators can manage physical data storage without affecting access to that data as a logical structure. For example, renaming a database file does not rename the tables stored within it.

To ensure that data is always accurate and accessible, relational databases follow certain integrity rules. For example, an integrity rule can specify that duplicate rows are not allowed in a table in order to eliminate the potential for erroneous information entering the database.

exmple: 

Database Design 

Database systems are designed to manage large bodies of information. These large bodies of information do not exist in isolation. They are part of the operation of some enterprise whose end product may be information from the database or may be some device or service for which the database plays only a supporting role. Database design mainly involves the design of the database schema. The

design of a complete database application environment that meets the needs of the enterprise being modeled requires attention to a broader set of issues.

Design Process

A high-level data model provides the database designer with a conceptual framework in which to specify the data requirements of the database users, and how the database will be structured to fulfill these requirements. The initial phase of database design, then, is to characterize fully the data needs of the prospective database users. The database designer needs to interact extensively with domain

experts and users to carry out this task. The outcome of this phase is a specification of user requirements.

Next, the designer chooses a data model, and by applying the concepts of the chosen data model, translates these requirements into a conceptual schema of the database. The schema developed at this conceptual-design phase provides a detailed overview of the enterprise. A fully developed conceptual schema indicates the functional requirements of the enterprise. In a specification of functional requirements, users describe the kinds of operations (or transactions) that will be performed on the data.

example: (optional)

University Database:

Here are the major characteristics of the university.

• The university is organized into departments. Each department is identified by a unique name (dept name), is located in a particular building, and has a budget.

• Each department has a list of courses it offers. Each course has associated with it a course id, title, dept name, and credits, and may also have have associated prerequisites.

• Instructors are identified by their unique ID. Each instructor has name, associated department (dept name), and salary.

• Students are identified by their unique ID. Each student has a name, an associated major department (dept name), and tot cred (total credit hours the student earned thus far).

• The university maintains a list of classrooms, specifying the name of the building, room number, and room capacity.

• The university maintains a list of all classes (sections) taught. Each section is identified by a course id, sec id, year, and semester, and has associated with it a semester, year, building, room number, and time slot id (the time slot when the class meets).

• The department has a list of teaching assignments specifying, for each instructor, the sections the instructor is teaching.

• The university has a list of all student course registrations, specifying, for each student, the courses and the associated sections that the student has taken (registered for).

Normalization: 

Another method for designing a relational database is to use a process commonly known as normalization. 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. The approach is to design schemas that are in an appropriate normal form.

Among the undesirable properties that a bad design may have are:

• Repetition of information

• Inability to represent certain information

DBMS Architecture

A database system is partitioned into modules that deal with each of the responsibilities of the overall system. The functional components of a database system can be broadly divided into the storage manager and the query processor components. The storage manager is important because databases typically require a large amount of storage space. The query processor is important because it helps the database system simplify and facilitate access to data. It is the job of the database system to translate updates and queries written in a nonprocedural language, at the logical level, into an efficient sequence of operations at the physical level.

The query processor components include :

DDL interpreter, which interprets DDL statements and records the definitions in the data dictionary.
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.
Query evaluation engine, which executes low-level instructions generated by the DML compiler.

A storage manager is 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 storage manager components include:

·        Authorization and integrity manager, which tests for the satisfaction of integrity constraints and checks the authority of users to access data.
·        Transaction manager, which ensures that the database remains in a consistent (correct) state despite system failures, and that concurrent transaction executions proceed without conflicting.
·        File manager, which manages the allocation of space on disk storage and the data structures used to represent information stored on disk.
·        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.

A transaction is a collection of operations that performs a single logical function in a database application. Each transaction is a unit of both atomicity and consistency. Thus, we require that transactions do not violate any database-consistency constraints.

the picture bellow shows various components and the connections between them.

Database applications are usually partitioned into two or three parts, say

1-tier :

in which the client, server, and Database all reside on the same machine. such architecture is rarely used in production.

2-tier :

where the presentation layer runs on a client (PC, Mobile, Tablet, etc.), and data is stored on a server called the second tier. Two tier architecture provides added security to the DBMS .

3-tier :

the most popular client server architecture in DBMS in which the development and maintenance of functional processes, logic, data access, data storage, and user interface is done independently as separate modules. Three Tier architecture contains a presentation layer, an application layer, and a database server.