Database Management System

We can manage the data using a conventional file management system, but that approach has several drawbacks:

• The same data may need to be stored in multiple files, leading to duplication of data.
• There is no mechanism to protect data from inconsistent changes made by different users accessing it simultaneously.
• If data is not properly organised, retrieval is slow and prone to inaccuracy.
• There is no way to restore data to a consistent state if the system crashes mid-update.
• Operating systems provide only a password mechanism, which is not flexible enough for real security policies.
• There is no standardisation on how data is stored.

Database: is an organized collection of inter-related data stored together with minimum redundancy, in a manner that makes it accessible to multiple applications. The software Database Management System (DBMS) is a set of programs that facilitates the storage, retrieval and management of a database, aiming to give both a convenient and an efficient environment for working with it.

Its advantages over a traditional file system include:

• Controlling data redundancy — data is kept centrally instead of duplicated across many files, so redundancy stays under control.
• Data consistency — because a data item appears only once, any update needs to be made only once and is immediately available to every user.
• Efficient data access — a DBMS uses a variety of techniques to store and retrieve data efficiently.
• Data integrity — the overall completeness, accuracy and consistency of data, maintained through standard rules, error-checking and validation routines.
• Data security — access to specific information can be limited to selected users by setting access rights, typically enforced through passwords.
• Sharing of data — the same data can be shared, even simultaneously, among several users or programs for different purposes.
• Enforcement of standards — a Database Administrator (DBA) defines and enforces standards for data formats, naming conventions, display formats and update procedures.
• Crash recovery — DBMS provides mechanisms to recover data and protect it from the effects of system failures.

A DBMS environment has several components, each performing a significant task:

Hardware is the actual computer system used to store and retrieve the database — computers, storage devices such as hard disks and magnetic tapes, and network devices like hubs, switches and routers.

Software consists of the actual DBMS, application programs and utilities. The DBMS acts as a bridge between the user and the database, handling every request for access — along with data definition, security, integrity, recovery and performance optimization — while shielding the database from complex hardware-level detail.

Data is the most important component from the end user's point of view, containing both operational data and meta-data (data about data). For effective storage and retrieval, data is organized as fields (the smallest unit of stored data), records (a collection of related fields), and files (a collection of all occurrences of the same type of record) — for example, Admission Number, Name, Batch, Result and Marks are the fields that together form one student's record, and the complete collection of such records forms a file.

Users access data on demand, using application programs, and are classified according to how they interact with the DBMS: Database Administrator (DBA), Application Programmers, Sophisticated users and Naive users.

Procedures are the documented instructions and rules that govern the design and use of the database — how to log onto the DBMS, use a particular facility, start and stop the DBMS, take backups, or reorganise the database across multiple disks.

Three levels of data abstraction: the physical level, the logical (conceptual) level, and the view level.

• Physical level — the lowest level, describing how data is actually stored on secondary storage devices such as disks, including auxiliary structures called indexes that speed up retrieval.
• Logical level — describes what data is stored and what relationships exist among it, in terms of a small number of relatively simple structures, without the user needing to know the underlying physical complexity. Database administrators use this level to decide what information the database should hold.
• View level — the highest level, closest to individual users. It describes only the part of the database relevant to a particular user, simplifying their interaction with the system; the same database may support many different views for different users.

Because a database can be viewed through three levels of abstraction, a change made at one level could otherwise ripple through and force redesign at the next level too. The ability to modify a schema definition at one level without affecting the schema at the next higher level is called data independence, and it comes in two forms:

• Physical data independence — the ability to modify the physical-level schema without affecting the conceptual-level schema, so application programs remain unaffected even when physical storage details change.
• Logical data independence — the ability to modify the conceptual schema without affecting the view (external) level schema. This is harder to achieve than physical independence, because application programs are heavily dependent on the logical structure of the database.

Database Administrator (DBA) — the person responsible for the centralized and shared database. The DBA designs the conceptual and physical schemas, ensures authorized access through security policies, and handles data availability and recovery from system crashes by taking regular backups and maintaining logs.

Application programmers — computer professionals who interact with the DBMS through application programs written in a host language (Visual Basic, C, C++, Java, etc.) that communicates with the DBMS using a Data Manipulation Language (DML).

Sophisticated users — engineers, scientists, business analysts and others thoroughly familiar with the DBMS's facilities, who interact through their own queries to meet complex requirements.

Naive users — interact with the system only by invoking previously written application programs, without being concerned with, or even aware of, the details of the DBMS. Clerical staff, billing clerks and people accessing data over the web are examples of naive users.

The relational data model represents a database as a collection of tables called relations, each assigned a unique name. Both data and the relationships among data are represented in this tabular form, which makes even complex database concepts easy to understand.

This model was invented by Edgar Frank Codd (1923–2003), an English computer scientist who joined IBM in 1948 and received the Turing Award in 1981 for his work. Today, most database products — called Relational Database Management Systems (RDBMS) — are based on the relational model, thanks to its simple data representation and the ease with which even complex queries can be expressed. Oracle, Microsoft SQL Server, MySQL, DB2, Informix and Ingress are popular RDBMS, and most of them offer a query language such as Structured Query Language (SQL), Query-by-Example (QBE) or Datalog.

Before discussing operations on relational databases, it helps to be familiar with a few core terms, illustrated using the sample STUDENT relation below.

• Entity — a person or thing in the real world that is distinguishable from others, such as each student or each school.
• Relation — a collection of data elements organized in terms of rows and columns; also called a Table (STUDENT above is one example).
• Tuple — a row of a relation, representing a complete set of values for one particular entity.
• Attribute — a column of a relation, whose values are drawn from a range of possible values called its domain.
• Degree — the number of attributes in a relation (STUDENT has degree 6).
• Cardinality — the number of tuples in a relation (STUDENT has cardinality 8).
• Domain — the pool of values from which the values of a column are drawn, e.g. the domain of Batch is {Science, Humanities, Commerce}.
• Schema — the description or structure of a database, specifying a relation's name, its column names, and each column's type.
• Instance — a set of tuples in which every tuple has the same number of fields as the relational schema — the STUDENT table above is one instance of the STUDENT relation.

Because all tuples in a relation must be distinct, a key — an attribute or collection of attributes that uniquely distinguishes each tuple — is needed. A key made up of more than one attribute is called a composite key.

• Candidate key — the minimal set of attributes that uniquely identifies a row in a relation. A relation can have more than one candidate key, and a candidate key need not be a single attribute.
• Primary key — one of the candidate keys chosen by the database designer as the unique identifier for a table; since it uniquely identifies each entity, it cannot contain a null or duplicate value.
• Alternate key — a candidate key that is not chosen as the primary key.
• Foreign key — a key in one table that is the primary key of another table; because it links two or more tables together, it is also called a reference key.

In the STUDENT relation, AdmNo is a candidate key (and is chosen as the primary key), while the combination Roll + Batch + Year could serve as an alternate key.

Once a database is designed and data is stored, the required information must be retrieved. The collection of operations used to manipulate the entire relations of a database is known as relational algebra. Each operation takes one or two relations as input and produces a new relation as the result. SELECT and PROJECT are unary operations, operating on a single relation; UNION, INTERSECTION, SET DIFFERENCE and CARTESIAN PRODUCT are binary operations, operating on a pair of relations.

SELECT is used to choose rows from a relation that satisfy a given predicate, denoted by the lower-case Greek letter sigma (σ). Comparison operators (<, <=, >, >=, =, <>) build simple conditions, and logical operators (∨ OR, ∧ AND, ! NOT) combine them into composite ones.

PROJECT selects certain attributes from a relation and forms a new relation, useful when the user wants only the values of a few columns rather than all of them. It is denoted by the lower-case Greek letter pi (π).

SELECT and PROJECT can be nested together in a single statement, e.g. πAdmNo, Name(σResult="EHS"(STUDENT)) gives the admission number and name of only those students eligible for higher studies.

These three binary operations require the two participating relations to be union-compatible — that is, they must have the same number of attributes, with corresponding attributes drawn from the same domain.

• UNION (R1 ∪ R2) — returns all tuples appearing in either or both relations, with duplicates eliminated.
• INTERSECTION (R1 ∩ R2) — returns only the tuples common to both relations.
• SET DIFFERENCE (R1 − R2) — returns the tuples appearing in R1 but not in R2.

UNION and INTERSECTION are commutative — the order of the relations doesn't matter — but SET DIFFERENCE is not: R1−R2 is generally not the same as R2−R1.

CARTESIAN PRODUCT (also called CROSS PRODUCT, denoted by ×) returns a relation consisting of every possible combination of tuples from two relations. Its degree is the sum of the degrees of the two relations, and its cardinality is the product of their cardinalities — every tuple of the first relation is concatenated with every tuple of the second.