Database Topic Title

1
An Overview of Relational Databases
2
The Relational Data Model

• Representing Things thru Tables
• Structure, Terminology, Rules
• Data Content
• Connections
• Manipulating Data in Tables
• Relational Algebra (Query Building Blocks)
• Query By Example
• SQL
• Entities Relationships Defined thru Tables

3
Representing Things

• Table (a.k.a. Relation, Record-Type, File)
• Row Entity Occurrence (a.k.a. Tuple, Record)
• Column Entity Property (a.k.a. Attribute, Field)

4
Table Definition (MS Access)
5
Table Definition (Oracle SQL)

• CREATE TABLE Student (
• Student_Id CHAR(9),
• Stu_name VARCHAR2(15),
• Hometown VARCHAR2(12),
• Birth_date DATE,
• Major CHAR(3),
• GPA NUMBER(3,2),
• Adviser VARCHAR2(15)
• )

6
Table Rules Definitions

• Tables are Normalized
• each row-column entry is single-valued (or null)
• Null Value
• a special value indicating absence of an actual
  value
• Candidate Key (CK) - a column or compound group
  of columns whose value is
• Non-Null, and
• Unique (identifies a single row in the table)
• Primary Key (PK)
• a CK selected as the identifier for a table
• every table must have a PK
• basis for Entity Integrity rule

7
Table Rules Definitions

• Alternate Key (AK)
• any CK not selected as the PK
• Domain
• A set of values from which entries in a column
  defined on that domain are drawn (and hence
  constrained)
• Non-PK column values may be specified as
• Nulls are allowed, or
• NOT NULL (Nulls are not allowed)
• Datatype (Character, Numeric, Date, )
• Row order is immaterial

8
Table ConnectionsA One-Many (1-M) Relationship

• Adviser (FK) Fac-name (PK) values

FK
PK
9
1-M Relationship Characteristics

• Foreign Key (FK) - a set of column(s) in a
  Child (Dependent) Table whose value is either
• Null, or
• Matches a PK value in a Parent Table, and thus
  uniquely identifies a corresponding row there
• One parent may have many children
• Each child has at most one parent
• Basis for Referential Integrity (RI) rule

10
1-M Relationship Diagrams
MS Access Relationship
Entity-Relationship Class
11
1-M Recursive Relationship
MS Access Relationship
Entity-Relationship Diagram
12
Referential Integrity (RI)

• Performance Implications of Enforcing RI
• Add a Student requires a Faculty row lookup
• Change a Student requires a Faculty row lookup
• Delete a Faculty implies 1 of 3 delete rules
• Delete Restrict disallow the Faculty delete if
  any Student dependents exist
• Delete Cascade delete Students with that
  Adviser
• Delete Set Null/Value set the Adviser column
  values to Null (or to a predefined default value)
  for related Students
• Update rules similar (but PK updates not
  advised)

13
Base Table Design Rules

• Normalization Theory
• Store one fact in one place
• Every nonkey column provides a fact about the
  key, the whole key, and nothing but the key
• An Example with Duplicate Data (Title, Cr)

14
Base Table Design Rules

• An Example with Duplicate Data Removed

15
Base Table Design Rules

• Data Semantics of Capacity

16
Base Table Design Rules

• The Temporal Dimension of Base Tables

PK
17
A Many-Many (M-M) Relationship
18
Registration Intersection Table
PK1
PK2
FK2
FK1
PK3 FK1FK2
18
19
M-M Implemented via Two 1-M Relationships
Intersection Table
20
Attributes of an M-M Relationship
PK
Non-Key Attributes
21
Entity Relationship Diagram
22
Microsoft Access Relationships
23
Stureg Relational Model
24
Stureg Relational Model
25
Stureg Relational Model(with Prerequisites)
26
Relational Data Model Components

• A. Database Structural Part
• Tables for Entities Relationships
• Columns Entity Relationship Attributes
• Rows Entity Relationship Occurrences
• Domains for Constraints on Column Values
• Keys
• Candidate Key
• Super Key
• Primary Key
• Alternate Key
• Foreign Key

27
Relational Data Model Components

• B. Integrity Rules
• Entity Integrity - PK Values must be
• Non-null, and
• Unique
• Referential Integrity (RI) - A FK value in a
  child (dependent) table must be
• Null, or
• Equal to a corresponding PK value in the parent
  table associated with the referential constraint

28
Relational Data Model Components

• B. Integrity Rules (continued)
• Additional Column Constraints
• Data type - value must conform to the column data
  type
• Not Null - column must contain a value
• Unique - duplicate values not allowed
• Check - value must satisfy a user-defined
  condition

29
Relational Data Model Components

• C. Data Manipulation Language (DML)
• Operation Types
• Creation
• Retrieval
• Update
• Deletion
• Operation Characteristics
• Operators work on whole tables
• Result of each operation is a new table
• Operations are based only on matching data values

30
Relational DML - SQL

• Retrieval
• SELECT ltcolumnsgt FROM lttablesgt WHERE
  ltconditionsgt
• SELECT Stu-name, Major, Adviser, Office, Phone
  FROM Student, Faculty WHERE Major
  MIS and Adviser Fac-name
• Update
• INSERT INTO
• UPDATE
• DELETE

31
Relational DML - SQL

• Cursors for Individual Record Manipulation
• DECLARE
• OPEN CLOSE
• FETCH
• Data Definition (DDL)
• CREATE TABLE VIEW INDEX
• DROP TABLE VIEW INDEX
• ALTER TABLE
• Data Control (DCL)
• GRANT REVOKE (privileges)

31
32
Relational DML - Relational Algebra
Basic Operations
33
Relational AlgebraSelect (Restrict) Rows

• STUDENT where Major MIS AND GPA gt 2.80

34
Relational AlgebraProject (Columns)

• STUDENT Stu-name, Major, GPA, Adviser

35
Relational AlgebraProduct (Extended Cross
Product)

• COURSE (CRS) TIMES REGISTRATION (REG)

36
Relational AlgebraEqui-Join (Redundant Columns)

• COURSE (CRS) EQUI-JOIN REGISTRATION
  (REG)CRS.DeptREG.Dept AND CRS.C_noREG.C_no

Duplicate Columns
Duplicate Columns
37
Relational AlgebraNatural-Join

• COURSE (CRS) JOIN REGISTRATION (REG)CRS.DeptREG
  .Dept AND CRS.C_noREG.C_no

Join Columns
38
Relational Algebra SET Operators (Union,
Intersection, Difference)

• Union Compatible Tables (same columns)
• Fall 2003 MGT301 Students
• Fall 2003 MGT329 Students

39
Relational Algebra SET Operators (Union,
Intersection, Difference)

• MGT301 UNION MGT329
• MGT301 INTERSECTION MGT329

• MGT301 MINUS MGT329
• MGT329 MINUS MGT301

40
Relational AlgebraDivide

• Database Data
• COMPLETED-CLASSES DIVIDEBY MGT460-PREREQS

• Projected Tables

41
Other DML OperationsSummarize (SQL Group By)

• STUDENT COUNT(Hometown), AVERAGE(GPA) GROUP BY
  Major

42
Relational DBMS Advantages

• Ease of Understanding Tables
• High Data Independence
• Power Ease of Use of Table Operations
• Common Data Definition, Data Manipulation, and
  Data Control Languages (e.g., SQL)
• Theoretical Foundations Relate to Query
  Optimization

43
Relational DBMS Disadvantages

• Performance Tradeoffs
• Access Based on Data Values
• Data Access vs. Index Maintenance
• Intelligent SQL Query FormulationSQL is like
  playing chess. You can learn all the basic moves
  in half an hour. But it takes practice for the
  rest of your life to become any good. -- George
  Schussel
• Table JOINs
• Data Access vs. Redundant Data Data Integrity
• Complexity in Performance Tuning
• Referential Integrity
• Update Performance vs. Data Integrity
• Recovery Locking via Entire Referential Structure

43
44
E.F. Codds 12 Relational DBMS Rules

• 0. Foundation Rule The DBMS must manage the
  database entirely through its relational
  capabilities.
• 1. Information Representation All information
  must be represented explicitly and only by table
  values.
• 2. Logical Accessibility Every data value in
  the database must be accessible via a combination
  of table name, column name, and primary key
  value.
• 3. Missing Information The DBMS must have a
  consistent method for representing null values
  to indicate missing information.

45
E.F. Codds 12 Relational DBMS Rules

• 4. System Catalog Facilities The database
  description must be represented in the same
  manner as ordinary data, so that facilities of
  the relational DBMS can be used to maintain the
  database descriptions.
• 5. Comprehensive Data Languages The DBMS must
  support as least one language that uses ordinary
  character strings to support data definition,
  integrity constraints, data manipulation, views,
  authorization privileges, and boundaries of
  recovery units.

46
E.F. Codds 12 Relational DBMS Rules

• 6. View Updatability The DBMS must be able to
  determine at view definition time whether a view
  can be used to insert, delete, or update rows of
  its underlying base tables.
• 7. Set Level Updates The ability to operate on
  whole tables applies not only to retrieval, but
  also to data insertion, modification, and
  deletion.
• 8. Physical Data Independence Application
  programs should not have to be modified when
  changes are made to physical storage or access
  methods.

47
E.F. Codds 12 Relational DBMS Rules

• 9. Logical Data Independence Application
  programs should not have to be modified when
  changes are made to base tables that do not
  modify data already stored in the tables.
• 10. Integrity Constraints Constraints that
  apply to entity integrity and referential
  integrity must be specifiable by the DBMSs data
  language. Application programs should not have
  to be modified when changes are made to those
  integrity constraints.

48
E.F. Codds 12 Relational DBMS Rules

• 11. Database Distribution Application programs
  should not have to be modified whenever data is
  first distributed or redistributed across
  different computers.
• 12. Nonsubversion If a DBMS has a low-level
  (procedural) language, that language should not
  be allowed to bypass entity integrity or
  referential integrity constraints expressed in
  the high-level relational language.