Implementation of the Relational Model

1
Implementation of the Relational Model
There is no substitute for the comfort supplied
by the utterlytaken-for-granted relationship.
Iris Murdoch
2
Class Outline

• What are the features of a relational database
  management system (RDBMS)?
• What is entity integrity and referential
  integrity?
• To what extent are entity and referential
  integrity supported by MS Access 97?
• What are the eight algebraic functions supported
  by a fully relational DBMS? Give examples of
  each.

3
Relational Database Management Systems

• Relational database architecture
• Codd, E.F. (1970). A relational model for large
  shared data banks. CACM, 13(6), 377-87.
• based on relational algebra and calculus
• First relational prototype - early 1970s - IBMs
  System R
• Relational databases required considerable
  computing resources (memory, processing speed)
• not feasible until mid- 1980s when
  price-performance ratio dropped
• low end (Access, Paradox, dBase, FoxPro, Clipper,
  RBase)
• high end (DB2, Oracle, Sybase, MS SQL Server,
  Informix, INGRES commercial)

4
The Relational Model

• ...consists of relations, which are made up of
  attributes.
• A relation is a set of columns (attributes) with
  values for each attribute such that
• Each column (attribute) value must be a single
  value only.
• All values for a given column (attribute) must be
  of the same type.
• Each column (attribute) name must be unique.
• The order of columns is insignificant
• No two rows (tuples) in a relation can be
  identical.
• The order of the rows (tuples) is insignificant.

5
Steps to Relational Implementation

• 1. Define the database structure to the DBMS
• for server and mainframe databases, use Data
  Definition Language (DDL) in a text file that
  describes columns of tables, defines indexes,
  constraints and security restrictions
• many PC databases (e.g., MS Access) provide a
  graphical interface to define the database tables
• in both cases, the Database Definition Subsystem
  of the DBMS creates the indexes and metadata
• 2. Allocation of Media Space
• unnecessary for PC databases, but performance
  issues must be considered for server/mainframe
  dbs
• 3. Creating the Database Data
• import pre-existing data or enter data either
  through DML (Data Manipulation Langauge) or forms
  of the application

6
Relational Data Manipulation

• Four strategies for relational data manipulation
• relational algebra - difficult to use because it
  is procedural - users must specify not only what
  they want but how to get it
• relational calculus - difficult to learn due to
  theoretical nature, not used in commercial
  database processing
• transform-oriented languages - non-procedural
  languages (e.g., SQUARE, SQL, SEQUEL)
• graphical interface to Data Manipulation Language
  (DML)
• query-by-example and query-by-form (behind each
  is a corresponding SQL query) - supported by many
  PC RDBMS (Lotus Approach, MS Access,Wall Datas
  Cyberprise DBApp)
• application program interface - written in
  programming languages such as COBOL, Pascal,
  Perl, C

7
Relational DBMS Defined

• Logical database model (rather than physical)
  that represents all data as if they are stored in
  separate two-dimensional but related tables
• Each table consists of single-value data elements
  describing a common theme among which is one (or
  more) elements that uniquely describe each record
  in the table (i.e. no two rows are identical)
• Tables are related as long as two tables share a
  common data element
• Information in these tables can be combined on an
  as-needed basis (flexibility) to get answers to
  queries and generate complex reports

8
Requirements of a RDBMS

• 1. Enforces Integrity rules
• (a) Entity Integrity - every row must have a
  unique identifier (primary key) which cannot
  include null entries
• (b) Referential Integrity - foreign key must have
  either a null entry or an entry that matches the
  primary key value in a table to which it is
  related

primary key
9
Entity Integrity

• Elements of a primary key
• It must uniquely identify each record n the table
• It must contain unique values
• It cannot be null
• It cannot be a multi-part field
• It should contain the minimum number of fields
  necessary to define uniqueness

• It is not optional whole or in part
• It must directly identify the value of each field
  in the table
• Its value can only be modified in rare or extreme
  cases

10
Referential Integrity

• Referential integrity is a mechanism that
  enforces the ties between data in separate tables
  and prevents them from being broken
• Referential integrity minimizes the undesirable
  likelihood of the existence of a record in the
  child table for which there is no corresponding
  record in the parent table - referred to as an
  orphan (or dangling) record
• Prior to setting referential integrity, ensure
  that
• the field used to tie two tables together (the
  link field) must be a primary key field in the
  parent table and a foreign key in the child table
• the link fields have an identical data type
• the two tables are in the same database container

11
Referential Integrity in Access 97

• A value cannot be entered in the foreign key
  field of the related table if that value doesn't
  exist in the primary key of the parent table. A
  Null value in the foreign key is allowed,
  specifying that the records are unrelated.

• A record cannot be deleted from a parent table if
  matching records exist in a related table.
• A primary key value in the parent table cannot be
  changed, if that record has related records.

Determined by MS Access on the basis of primary
key settings.
12
Referential Integrity Options in Access 97

• Cascade Update
• Special override of the referential integrity
  mechanism in order to be able to edit the primary
  key in the one table MS Access will
  automatically make the same change to the foreign
  key in the child table so the relationship is
  maintained.
• Cascade Delete
• Special override of the referential integrity
  mechanism to facilitate deleting records in the
  parent table even when there are related records
  in the child table. All related records in the
  child table will automatically be deleted so that
  there will be no orphan records.

Do not use these options unless you realize the
full implications of making the selection.
13
Relationship Integrityis a way of minimizing
data errors
MS Access 97 On-line Help
14
Requirements of a RDBMS

• 2. Supports many of the relational algebraic
  functions - a collection of operations on
  relations, resulting in relations
• Set theory operators
• union
• intersect
• difference
• product

Specific relational operators

• select
• project
• divide
• join

15
Algebraic function 1. Union
Combination of data without repeating common
rows must have equivalent columns as to number
and domains (union compatible).
Provide information on all employees regardless
of their position
note that Anne appears only once even though
shes in both tables
16
Algebraic function 2. Intersection
Identification of rows that are common to two
relations must have equivalent columns as to
number and domains.
Provide information on employees who have both a
salesperson and manager role
17
Algebraic Function 3. Difference
Identification of rows that are in one relation
and not in another must have equivalent columns
as to number and domains.
Provide information on employees who have a
salesperson role but do not have a managerial
role
18
Algebraic Function 4. Product
Adjoining (concatenating) each row in the first
relation to each row in the second relation must
have different column names
No obvious query conceptually important because
it is used as a building block (Cartesian
product) for the join operator.
19
Algebraic Function 5. Select
Creation of a relation by identifying only rows
that satisfy specific conditions
Provide information on employees who are based in
Tokyo
Provide information on employees whose salary is
at least 2000
20
Algebraic Function 6. Project
Creates a relation by deleting columns from an
existing relation
Provide a list of employee names (not all
information)
Can nest (combine) operators (e.g., select,
project)
Provide names of employees whose office is in
Tokyo
21
Algebraic Function 7. Divide
Creating a new relation by selecting the rows in
one relation that match every row in another
relation
Who has sold every product?
22
Algebraic Function 8. Join
Connection of data across relations natural join
(rows are joined when common columns have equal
values) outer join (all rows from both tables
even if there is no matching column value) and
theta join (not covered)
Provide the Supplier Name for each product
Provide all products and all suppliers, joining
where possible