Relational Model Concepts Relational Model Notation
The relational model represents the database as a collection of relations. In a relational database, there will typically be many relations, and the tuples in
Informally, each relation resembles a table of values or, to some extent, a flat file those relations are usually related in various ways. The state of the whole
of records. It is called a flat file because each record has a simple linear or flat database will correspond to the states of all its relations at a particular point in
structure. For example, the database of files that was shown in Figure 1.2 is time. There are generally many restrictions or constraints. The constraints we
similar to the basic relational model representation. When a relation is thought discuss in this section are of the second category, namely, constraints that can
of as a table of values, each row in the table represents a collection of related be expressed in the schema of the relational model via the DDL. Constraints in
data values. A row represents a fact that typically corresponds to a real-world the third category are more general, relate to the meaning as well as behavior of
entity or relationship In the formal relational model terminology, a row is called attributes, and are difficult to express and enforce within the data model
a tuple, a column header is called an attribute, and the table is called a relation.
The data type describing the types of values that can appear in each column is Domain Constraints
represented by a domain of possible values. We now define these terms—
Domain constraints specify that within each tuple, the value of each attribute A
domain, tuple, attribute, and relation— formally
must be an atomic value from the domain dom(A). We have already discussed
Domains, Attributes, Tuples, and Relations the ways in which domains can be specified in Section 3.1.1. The data types
associated with domains typically include standard numeric data types for
A domain D is a set of atomic values. By atomic we mean that each value in the integers (such as short integer, integer, and long integer) and real numbers (float
domain is indivisible as far as the formal relational model is concerned. A and doubleprecision float).
common method of specifying a domain is to specify a data type from which the
data values forming the domain are drawn. It is also useful to specify a name for Key Constraints and Constraints on NULL Values
the domain, to help in interpreting its values. A data type or format is also
In the formal relational model, a relation is defined as a set of tuples. By
specified for each domain. For example, the data type for the domain
definition, all elements of a set are distinct; hence, all tuples in a relation must
Usa_phone_numbers can be declared as a character string of the form
also be distinct. This means that no two tuples can have the same combination
(ddd)ddddddd, where each d is a numeric (decimal) digit and the first three digits
of values for all their attributes. Usually, there are other subsets of attributes of
form a valid telephone area code.
a relation schema R with the property that no two tuples in any relation state r
A relation (or relation state)4 r of the relation schema R(A1, A2, ..., An), also of R should have the same combination of values for these attributes. Suppose
denoted by r(R), is a set of n-tuples r = {t 1, t 2, ..., t m}. Each n-tuple t is an that we denote one such subset of attributes by SK; then for any two distinct
ordered list of n values t =, where each value vi , 1 ≤ i ≤ n, is an element of dom tuples t 1 and t 2 in a relation state r of R, we have the constraint that:
(Ai ) or is a special NULL value. (NULL values are discussed further below and in
t 1[SK]≠ t 2[SK]
Section 3.1.2.) The i th value in tuple t, which corresponds to the attribute Ai , is
referred to as t[Ai ] or t.Ai (or t[i] if we use the positional notation). The terms
relation intension for the schema R and relation extension for a relation state
r(R) are also commonly used.
This product of cardinalities of all domains represents the total number of
possible instances or tuples that can ever exist in any relation state r(R). Of all
these possible combinations, a relation state at a given time—the current
relation state—reflects only the valid tuples that represent a particular state of
the real world. In general, as the state of the real world changes, so does the
relation state, by being transformed into another relation state. However, the
schema R is relatively static and changes very infrequently.
Data Modeling and Query Languages DBMS Languages
Menu-Based Interfaces for Web Clients or Browsing. These interfaces present the DBMSs where no strict separation of levels is maintained, one language, called
user with lists of options (called menus) that lead the user through the the data definition language (DDL), is used by the DBA and by database
formulation of a request. Menus do away with the need to memorize the specific designers to define both schemas. The DBMS will have a DDL compiler whose
function is to process DDL statements in order to identify descriptions of the
commands and syntax of a query language; rather, the query is composed step-
schema constructs and to store the schema description in the DBMS catalog.
bystep by picking options from a menu that is displayed by the system.
s, the DDL is used to specify the conceptual schema only. Another language,
Forms-Based Interfaces. A forms-based interface displays a form to each user. the storage definition language (SDL), is used to specify the internal schema.
Users can fill out all of the form entries to insert new data, or they can fill out only The mappings between the two schemas may be specified in either one of
certain entries, in which case the DBMS will retrieve matching data for the these languages. In most relational DBMSs today, there is no specific language
remaining entries. Forms are usually designed and programmed for naive users as that performs the role of SDL. Instead, the internal schema is specified by a
interfaces to canned transactions. Many DBMSs have forms specification combination of functions, parameters, and specifications related to storage.
These permit the DBA staff to control indexing choices and mapping of data to
languages
storage
Graphical User Interfaces. A GUI typically displays a schema to the user in the database is populated with data, users must have some means to
diagrammatic form. The user then can specify a query by manipulating the manipulate the database. Typical manipulations include retrieval, insertion,
diagram. In many cases, GUIs utilize both menus and forms. Most GUIs use a deletion, and modification of the data. The DBMS provides a set of operations
pointing device, such as a mouse, to select certain parts of the displayed schema or a language called the data manipulation language (DML) for these purposes.
diagram.
In current DBMSs, the preceding types of languages are usually not considered
Natural Language Interfaces. These interfaces accept requests written in English or distinct languages; rather, a comprehensive integrated language is used that
includes constructs for conceptual schema definition, view definition, and data
some other language and attempt to understand them. A natural language
manipulation. Storage definition is typically kept separate, since it is used for
interface usually has its own schema, which is similar to the database conceptual defining physical storage structures to fine-tune the performance of the
schema, as well as a dictionary of important words. The natural language interface database system, which is usually done by the DBA staff. A typical example of a
refers to the words in its schema, as well as to the set of standard words in its comprehensive database language is the SQL relational database language (see
dictionary, to interpret the request. If the interpretation is successful, the Chapters 4 and 5), which represents a combination of DDL, VDL, and DML, as
interface generates a high-level query corresponding to the natural language well as statements for constraint specification, schema evolution, and other
features. The SDL was a component in early versions of SQL but has been
request and submits it to the DBMS for processing; otherwise, a dialogue is
removed from the language to keep it at the conceptual and external levels
started with the user to clarify the request
only.
Speech Input and Output. Limited use of speech as an input query and speech as There are two main types of DMLs. A high-level or nonprocedural DML can be
an answer to a question or result of a request is becoming commonplace. used on its own to specify complex database operations concisely. Many
Applications with limited vocabularies such as inquiries for telephone directory, DBMSs allow high-level DML statements either to be entered interactively
flight arrival/departure, and credit card account information are allowing speech from a display monitor or terminal or to be embedded in a general-purpose
for input and output to enable customers to access this information programming language..
Data Model The types of access path options for storing files. One well-known family of
DBMSs is based on inverted file structures. Finally, a DBMS can be general
The main data model used in many current commercial DBMSs is the relational data purpose or special purpose. When performance is a primary consideration, a
model. The object data model has been implemented in some commercial systems special-purpose DBMS can be designed and built for a specific application;
but has not had widespread use. Many legacy applications still run on database such a system cannot be used for other applications without major changes.
systems based on the hierarchical and network data models. Examples of Many airline reservations and telephone directory systems developed in the
hierarchical DBMSs include IMS (IBM) and some other systems like System 2K (SAS past are special-purpose DBMSs. These fall into the category of online
Inc.) and TDMS. IMS is still used at governmental and industrial installations, transaction processing (OLTP) systems, which must support a large number of
including hospitals and banks, although many of its users have converted to concurrent transactions without imposing excessive delays
relational systems. The network data model was used by many vendors and the
resulting products like IDMS (Cullinet—now Computer Associates), DMS 1100 The basic relational data model represents a database as a collection of
(Univac—now Unisys), IMAGE (Hewlett-Packard), VAXDBMS (Digital—then Compaq tables, where each table can be stored as a separate file. The database in
and now HP), and SUPRA (Cincom) still have a following and their user groups have Figure 1.2 resembles a relational representation. Most relational databases
their own active organizations. If we add IBM’s popular VSAM file system to these, use the high-level query language called SQL and support a limited form of
we can easily say that a reasonable percentage of worldwide-computerized data is user views.
still in these so-called legacy database systems.
The object data model defines a database in terms of objects, their
The relational DBMSs are evolving continuously, and, in particular, have been properties, and their operations. Objects with the same structure and
incorporating many of the concepts that were developed in object databases. This behavior belong to a class, and classes are organized into hierarchies (or
has led to a new class of DBMSs called object-relational DBMSs. We can categorize acyclic graphs). The operations of each class are specified in terms of
DBMSs based on the data model: relational, object, object-relational, hierarchical, predefined procedures called methods. Relational DBMSs have been
network, and other. More recently, some experimental DBMSs are based on the extending their models to incorporate object database
XML (eXtended Markup Language) model, which is a tree-structured (hierarchical)
data model. These have been called native XML DBMSs. Several commercial The XML model has emerged as a standard for exchanging data over the Web,
relational DBMSs have added XML interfaces and storage to their products and has been used as a basis for implementing several prototype native XML
systems. XML uses hierarchical tree structures. It combines database
e. A distributed DBMS (DDBMS) can have the actual database and DBMS software concepts with concepts from document representation models. Data is
distributed over many sites, connected by a computer network. Homogeneous represented as elements; with the use of tags, data can be nested to create
DDBMSs use the same DBMS software at all the sites, whereas heterogeneous complex hierarchical structures. This model conceptually resembles the
DDBMSs can use different DBMS software at each site. It is also possible to develop object model but uses different terminology
middleware software to access several autonomous preexisting databases stored
under heterogeneousDBMSs. This leads to a federated DBMS (or multidatabase The network model represents data as record types and also represents a
system), in which the participating DBMSs are loosely coupled and have a degree of limited type of 1:N relationship, called a set type. A 1:N, or one-to-many,
local autonomy relationship relates one instance of a record to many record instances using
some pointer linking mechanism in these models
Functional Dependencies
The attributes of a table is said to be dependent on each other when an attribute
of a table uniquely identifies another attribute of the same table.
Timestamp-based Protocols
QUERY TREE
Cost of Query
SELECTION AND PROJECTION
SQL INJECTION
AUTHORIZATION
