The
principles of the relational model were first outlined by Dr. E. F. Codd in a
June 1970 paper titled “A Relational Model of Data for Large Shared Data
Banks.” In this paper, Dr. Codd proposed the relational model for database
systems.
The common
models used at that time were hierarchical and network, or even simple
flat-file data structures. Relational database management systems (RDBMS) soon
became very popular, especially for their ease of use and flexibility in
structure. In addition, a number of innovative vendors, such as Oracle,
supplemented the RDBMS with a suite of powerful, application development and
user-interface products, thereby providing a total solution.
Components
of the Relational Model
•
Collections
of objects or relations that store the data
•
A
set of operators that can act on the relations to produce other relations
•
Data
integrity for accuracy and consistency
Definition
of a Relational Database
•
A
relational database uses relations or two-dimensional tables to store
information.
•
For
example, you might want to store information about all the employees in your
company. In a relational database, you create several tables to store different
pieces of information about your employees, such as an employee table, a
department table, and a salary table.
Data
Models
Models are
the cornerstone of design. Engineers build a model of a car to work out any
details before putting it into production. In the same manner, system designers
develop models to explore ideas and improve the understanding of database
design.
Purpose
of Models
Models help
communicate the concepts that are in people’s minds. They can be used to do the
following:
•
Communicate
•
Categorize
•
Describe
•
Specify
•
Investigate
•
Evolve
•
Analyze
•
Imitate
The
objective is to produce a model that fits a multitude of these uses, can be
understood by an end user, and contains sufficient detail for a developer to
build a database system.
Entity
Relationship Model
In an
effective system, data is divided into discrete categories or entities. An
entity relationship (ER) model is an illustration of the various entities in a
business and the relationships among them. An ER model is derived from business
specifications or narratives and built during the analysis phase of the system
development life cycle. ER models separate the information required by a
business from the activities performed within the business. Although businesses
can change their activities, the type of information tends to remain constant.
Therefore, the data structures also tend to be constant.
Benefits
of ER Modeling:
•
Documents
information for the organization in a clear, precise format
•
Provides
a clear picture of the scope of the information requirement
•
Provides
an easily understood pictorial map for database design
•
Offers
an effective framework for integrating multiple applications
Key
Components
•
Entity: An aspect of significance about
which information must be known. Examples are departments, employees, and
orders.
•
Attribute: Something that describes or
qualifies an entity. For example, for the employee entity, the attributes would
be the employee number, name, job title, hire date, department number, and so
on. Each of the attributes is either required or optional. This state is called
optionality.
•
Relationship: A named association between
entities showing optionality and degree. Examples are employees and
departments, and orders and items
ER Modeling
Conventions
Entities
To
represent an entity in a model, use the following conventions:
•
Singular,
unique entity name
•
Entity
name in uppercase
•
Soft
box
•
Optional
synonym names in uppercase within parentheses: ( )
Attributes
To
represent an attribute in a model, use the following conventions:
•
Singular
name in lowercase
•
Asterisk
(*) tag for mandatory attributes (that is, values that must be known)
•
Letter
“o” tag for optional attributes (that is, values that may be known)
Relationships
Each
direction of the relationship contains:
•
A
label: for example,
taught by or assigned to
•
An
optionality: either
must be or maybe
•
A
degree: either one
and only one or one or more
Note: The term cardinality is a
synonym for the term degree.
Each source
entity {may be | must be} in relation {one and only one | one or more} with the
destination entity.
Note: The convention is to read
clockwise.
Unique
Identifiers
A unique
identifier (UID) is any combination of attributes or relationships, or both,
that serves to distinguish occurrences of an entity. Each entity occurrence
must be uniquely identifiable.
•
Tag
each attribute that is part of the UID with a hash sign (#).
•
Tag
secondary UIDs with a hash sign in parentheses (#).
Relating
Multiple Tables
Each table
contains data that describes exactly one entity. For example, the EMPLOYEES
table contains information about employees. Categories of data are listed
across the top of each table, and individual cases are listed below. By using a
table format, you can readily visualize, understand, and use information. Because
data about different entities is stored in different tables, you may need to
combine two or more tables to answer a particular question. For example, you
may want to know the location of the department where an employee works. In
this scenario, you need information from the EMPLOYEES table (which contains
data about employees) and the DEPARTMENTS table (which contains information
about departments). With an RDBMS, you can relate the data in one table to the
data in another by using the foreign keys. A foreign key is a column (or a set
of columns) that refers to a primary key in the same table or another table.
•
You
can use the ability to relate data in one table to data in another to organize
information in separate, manageable units. Employee data can be kept logically
distinct from the department data by storing it in a separate table.
Guidelines
for Primary Keys and Foreign Keys
•
You
cannot use duplicate values in a primary key.
•
Primary
keys generally cannot be changed.
•
Foreign
keys are based on data values and are purely logical (not physical) pointers.
•
A
foreign key value must match an existing primary key value or unique key value,
otherwise it must be null.
•
A
foreign key must reference either a primary key or a unique key column.
Relational
Database Terminology
A
relational database can contain one or many tables. A table is the basic
storage structure of an RDBMS. A table holds all the data necessary about
something in the real world, such as employees, invoices, or customers.
The shows
the contents of the EMPLOYEES table or relation. The numbers
indicate the following:
1. A single row (or tuple) representing
all the data required for a particular employee. Each row in a table should be
identified by a primary key, which permits no duplicate rows. The order of rows
is insignificant; specify the row order when the data is retrieved.
2. A column or attribute containing
the employee number. The employee number identifies a unique employee in
the EMPLOYEES table. In this example, the employee number column is designated
as the primary key. A primary key must contain a value and the value
must be unique.
3. A column that is not a key value. A
column represents one kind of data in a table; in this example, the data is the
salaries of all the employees. Column order is insignificant when storing data;
specify the column order when the data is retrieved.
4. A column containing the department
number, which is also a foreign key. A foreign key is a column that
defines how tables relate to each other. A foreign key refers to a primary key
or a unique key in the same table or in another table. In the example, DEPARTMENT_ID
uniquely identifies a department in the DEPARTMENTS table.
5. A field can be found at the
intersection of a row and a column. There can be only one value in it.
6. A field may have no value in it. This is
called a null value. In the EMPLOYEES table, only those employees who have the
role of sales representative have a value in the COMMISSION_PCT (commission)
field.
Using
SQL to Query Your Database
In a
relational database, you do not specify the access route to the tables, and you
do not need to know how the data is arranged physically.
To access
the database, you execute a structured query language (SQL) statement, which is
the American National Standards Institute (ANSI) standard language for
operating relational databases. SQL is a set of statements with which all programs
and users access data in an Oracle database. Application programs and Oracle
tools often allow users access to the database without using SQL directly, but
these applications, in turn, must use SQL when executing the user’s request.
SQL
provides statements for a variety of tasks, including:
•
Querying
data
•
Inserting,
updating, and deleting rows in a table
•
Creating,
replacing, altering, and dropping objects
•
Controlling
access to the database and its objects
•
Guaranteeing
database consistency and integrity
SQL unifies
all of the preceding tasks in one consistent language and enables you to work
with data at a logical level.
SQL
Statements
SQL
statements supported by Oracle comply with industry standards. Oracle
Corporation ensures future compliance with evolving standards by actively
involving key personnel in SQL standards committees. The industry-accepted
committees are ANSI and International Standards Organization (ISO). Both ANSI and
ISO have accepted SQL as the standard language for relational databases.
