Introduction to Database Languages
A database system provides a data-definition language to specify the database schema
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-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.
A query is a statement requesting the retrieval of information. The portion of a DML that involves information retrieval is called a query language. Although technically incorrect, it is common practice to use the terms query language and data-manipulation language synonymously.
Data-Definition Language
We specify a database schema by a set of definitions expressed by a special language called a data-definition language ( DDL). It is also used to specify additional properties of the data.
The storage structure and access methods used by the database system is specified 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 users.
The data values stored in the database must satisfy certain consistency constraints.
For example, suppose the university requires that the account balance of a department must never be negative. The DDL provides facilities to specify such constraints.
The database system checks these constraints every time the database is updated.
In general, a constraint can be an arbitrary predicate pertaining to the database. However, arbitrary predicates may be costly to test.
Thus, database systems implement integrity constraints that can be tested with minimal overhead
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.
Attributes may have the same domain, e.g. cname and employee-name. At the implementation level, they are both character strings. At the conceptual level, we do not expect customers to have the same names as branches, in general. Strong typing of domains allows us to test for values inserted, and whether queries make sense.
Referential Integrity :
Often we wish to ensure that a value appearing in a relation for a given set of attributes also appears for another set of attributes in another relation.
For example, the department listed for each course must be one that actually exists.
More precisely, the dept name value in a course record must appear in the dept name attribute of some record of the department relation
Assertions :
An assertion is any condition that the database must always satisfy
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.
The DDL, just like any other programming language, gets as input some instructions (statements) and generates some output. The output of the DDL is placed in the data dictionary, which contains metadata - that is, data about data.
The data dictionary is considered to be a special type of table that can only be accessed and updated by the database system itself (not a regular user). The database system consults the data dictionary before reading or modifying actual data.
Relational Databases
A relational database is based on the relational model and uses a collection of tables to represent both data and the relationships among those data.
It also includes a DML and DDL.
Tables
Each table has multiple columns and each column has a unique name.
The relational model is an example of a record-based model. Record-based models are so named because the database is structured in fixed-format records of several types.
Each table contains records of a particular type. Each record type defines a fixed number of fields, or attributes.
The columns of the table correspond to the attributes of the record type.
Data-Manipulation Language
The SQL query language is non-procedural.
A query takes as input several tables (possibly only one) and always returns a single table.
Queries may involve information from more than one table.
Data-Definition Language
SQL provides a rich DDL that allows one to define tables, integrity constraints, assertions, etc.
The DDL statements update the data dictionary, which contains metadata
The schema of a table is an example of metadata.
Shortcomings of SQL
SQL is not as powerful as a universal Turing machine; that is, there are some computations that are possible using a general-purpose programming language but are not possible using SQL.
SQL does not support actions such as input from users, output to displays, or communication over the network.
To access the database, DML statements need to be executed from the host language. By providing an application program interface (set of procedures) that can be used to send DML and DDL statements to the database and retrieve the results. The Open Database Connectivity ( ODBC) standard for use with the C language is a commonly used application program interface standard. The Java Database Connectivity ( JDBC) standard provides corresponding features to the Java language.
This can also be done by extending the host language syntax to embed DML calls within the host language program. Usually, a special character prefaces DML calls, and a preprocessor, called the DML precompiler, converts the DML statements to normal procedure calls in the host language.
Database Administrator
A person who has such central control over the central control of both the data and the programs that access those data is called a database administrator ( DBA)
The functions of a DBA include:
Schema definition :The DBA creates the original database schema by executing a set of data definition statements in the DDL
Storage structure and access - method definition :
Schema and physical - organization modification :The DBA carries out changes to the schema and physical organization to reflect the changing needs of the organization, or to alter the physical organization to improve performance.
Granting of authorization for data access :
Routine maintenance :Periodically backing up the database, Ensuring that enough free disk space is available for normal operations, Monitoring jobs running on the database.