RELATIONAL SRUCTIURES

WACHIRA DAVIS DIS514

RELATIONAL STRUCTURES
 It is used by most microcomputers DBMS packages.
 In this model, all data elements within the database are viewed as being stored in
the form of tables.
 Where columns are the fields and the rows are the records.
 With this structure, end-users could easily receive information in response to ad hoc
requests.
 However, it cannot process large amounts of business transactions quickly and
efficiently as those based on the hierarchical and network models.

The Relational Model

 The Relational Database Management System (RDBMS) has become the dominant data
processing software in use today.

 It is based on the relational data model.

 In the relational model, all data is logically structured within relations (tables).

 Each relation has a name and is made up of named attributes (columns) of data.

 Each tuple (row) contains one value per attribute.

 A great strength of the relational model is this simple logical structure.

 The formal and de facto standard language for RDBMS is SQL (Structured Query Language).

Relational Data Structure

Relation

 A relation is a table with columns and rows.

 An RDBMS requires only that the database be perceived by the user as tables.

 This perception applies only to the logical structure of the database - that is, the external
and conceptual levels of the ANSI-SPARC architecture. It does not apply to the physical
structure of the database, which can be implemented using a variety of storage structures.

Attribute

 An attribute is a named column of a relation.

1
RELATIONAL SRUCTIURES

WACHIRA DAVIS DIS514

 In the relational model, relations are used to hold information about the objects to be
represented in the database.

 A relation is represented as a two-dimensional table in which the rows of the table

correspond to individual records and the table columns correspond to attributes.

 Attributes can appear in any order and the relation will still be the same relation, and
therefore convey the same meaning.

Domain

 A domain is the set of allowable values for one or more attributes.

 Every attribute in a relation is defined on a domain.

 Domains may be distinct for each attribute, or two or more attributes may be defined on the
same domain.

 The domain concept is important because it allows the user to define in a central place the
meaning and source of values that attributes can hold.

 As a result, more information is available to the system when it undertakes the execution of
a relational operation, and operations that are semantically incorrect can be avoided.

 For example, it is not sensible to compare a street name with a telephone number, even
though the domain definitions for both these attributes are character strings.

Tuple
 A tuple is a row of a relation.
 The elements of a relation are the rows or tuples in the table.
 Tuples can appear in any order and the relation will still be the same relation, and therefore
convey the same meaning.

 The structure of a relation, together with a specification of the domains and any other
restrictions on possible values, is sometimes called its intension

 This is usually fixed unless the meaning of a relation is changed to include additional
attributes.
 The tuples are called the extension (or state) of a relation, which changes over time.

Degree
 The degree of a relation is the number of attributes it contains.
 A relation with four attributes has degree four.
 The degree of a relation is a property of the intension of the relation.

2
RELATIONAL SRUCTIURES

WACHIRA DAVIS DIS514

Cardinality
 The cardinality of a relation is the number of tuples it contains.
 This changes as tuples are added or deleted.
 The cardinality is a property of the extension of the relation and is determined from the
particular instance of the relation at any given moment.

Relational database
 A relational database is a collection of normalized relations with distinct relation names.
 A relational database consists of relations that are appropriately structured.
 This appropriateness is normalization.

Properties of a relation
A relation has the following properties:
o the relation has a name that is distinct from all other relation names in the relational
schema;
o each cell of the relation contains exactly one atomic (single) value;
o each attribute has a distinct name;
o the values of an attribute are all from the same domain;
o each tuple is distinct; there are no duplicate tuples;
o the order of attributes has no significance;
o the order of tuples has no significance, theoretically.

 There are no duplicate tuples within a relation.

 To uniquely identify each tuple in a relation, one or more attributes called relational keys are
needed.
 A superkey is an attribute or set of attributes that uniquely identifies a tuple within a
relation.
 As superkey may contain more attributes, a candidate key is needed which can be several in
a relation.
 A key that consist of more than one attribute is composite key.
 A candidate key that is selected to identify tuples uniquely within the relation is the primary
key.
 The keys that are not selected are the candidate keys.
 An attribute within one relation that matches the candidate key of some relation is a foreign
key.

Integrity Constraints
 Domain constraints – they form restrictions on the set of values allowed for the attributes of
relations

3
RELATIONAL SRUCTIURES

WACHIRA DAVIS DIS514

 Entity integrity - In a base relation, no attribute of a primary key can be null. (A base relation
is a named relation corresponding to an entity in the conceptual schema, whose tuples are
physically stored in the database. )

 Referential integrity - If a foreign key exists in a relation, either the foreign key value must
match a candidate key value of some tuple in its home relation or the foreign key value must
be wholly null.
 General constraints – additional rules specified by the users or database administrators of a
database that define or constrain some aspect of the enterprise.
o For example a constrain that sets upper limit of a value.

SQL
 SQL emerged from the development of the relational model. SQL has become the standard
relational database language.
Objectives of SQL
Ideally, a database language should allow a user to:
 create the database and relation structures;
 perform basic data management tasks, such as the insertion, modification, and deletion
of data from the relations;
 perform both simple and complex queries.

 A database language must perform these tasks with minimal user effort, and its command
structure and syntax must be relatively easy to learn.
 The language must also be portable.
 SQL is intended to satisfy these requirements.

The ISO SQL standard has two major components:

 A Data Definition Language (DDL) for defining the database structure and controlling access to
the data;
 A Data Manipulation Language (DML) for retrieving and updating data.
SQL can be used in two ways.
 The first way is to use SQL interactively by entering the statements at a terminal.
 The second way is to embed SQL statements in a procedural language.

SQL is a relatively easy language to learn:

 It is a non-procedural language: you specify what information you require, rather than
how to get it. In other words, SQL does not require you to specify the access methods
to the data.
 Like most modern languages, SQL is essentially free-format, which means that parts of
statements do not have to be typed at particular locations on the screen.
 The command structure consists of Standard English words such as CREATE TABLE,
INSERT, SELECT.

4
RELATIONAL SRUCTIURES

WACHIRA DAVIS DIS514

SQL works with the DDL statements to create the database structure (that is, the tables) and the
access mechanisms (that is, what each user can legally access), and the DML statements to populate
and query the tables.

 SQL is a non-procedural language, consisting of Standard English words such as SELECT, INSERT,
DELETE, that can be used by professionals and non-professionals alike. It is both the formal and
de facto standard language for defining and manipulating relational databases.

 The SELECT statement is the most important statement in the language and is used to express a
query. It combines the three fundamental relational algebra operations of Selection, Projection,
and Join.

 Every SELECT statement produces a query result table consisting of one or more columns and
zero or more rows.

 The SELECT clause identifies the columns and/or calculated data to appear in the result table. All
column names that appear in the SELECT clause must have their corresponding tables or views
listed in the FROM clause.

 The WHERE clause selects rows to be included in the result table by applying a search condition
to the rows of the named table(s).

 The ORDER BY clause allows the result table to be sorted on the values in one or more columns.
Each column can be sorted in ascending or descending order. If specified, the ORDER BY clause
must be the last clause in the SELECT statement.

 SQL supports five aggregate functions (COUNT, SUM, A VG, MIN, and MAX) that take an entire
column as an argument and compute a single value as the result. It is illegal to mix aggregate
functions with column names in a SELECT clause, unless the GROUP BY clause is used.

 The GROUP BY clause allows summary information to be included in the result table. Rows that
have the same value for one or more columns can be grouped together and treated as a unit for
using the aggregate functions. In this case the aggregate functions take each group as an
argument and compute a single value for each group as the result.

 The HAVING clause acts as a WHERE clause for groups, restricting the groups
that appear in the final result table. However, unlike the WHERE clause, the HAVING clause can
include aggregate functions.

As indicated SQL statements could be DDL or DML. Examples/illustrations are given as follows:
SQL DML statements, include:
 SELECT - to query data in the database;
 INSERT - to insert data into a table;

5
RELATIONAL SRUCTIURES

WACHIRA DAVIS DIS514

 UPDATE - to update data in a table;

 DELETE - to delete data from a table.

SQL DDL statements for creating table may take the following form:

CREATE TABLE TableName

{(columName dataType [NOT NULL] [UNIQUE]
[DEFAULT defaultOption] [CHECK (searchCondition)] [, ... ]}
[PRIMARY KEY (listOfColumns),]
{[UNIQUE (listOfColumns)] [, ... ]}
{[FOREIGN KEY (listOfForeignKeyColumns)