DBA1 by panniuniu

VIEWS: 3 PAGES: 38

									         Oracle Programming

            Week 1
 Review on the Relational Data
       Model
Basic Definitions
Relational Database Concepts
Relational Database Management Systems

         Chapter 5 Elmasri & Navathe book
                                            1
                 Basic Definitions
   Data: Known facts that can be recorded and have an implicit
    meaning.
   Database : A collection of related data
   Database management system (DBMS):
    a collection of computer programs, which enables users to create
    and maintain databases


        User1                                      User2
                            Database
        User3                                      User4
                                                                       2
          Basic Definitions (Cont.)

 Schema: description of data at some level
  (e.g., tables, attributes, constraints, domains)
 Model: tools and language for describing:
       Conceptual schema
         Data   definition language (DDL)
       Integrity constraints, domains (DDL)
       Operations on data
         Data   manipulation language (DML)
       Directives that influence the physical schema
        (affects performance, not semantics)
         Storage   definition language (SDL)

                                                        3
             Relational Model

 A particular way of structuring data
  (relations)
 Simple
 Mathematically based
     Expressions (queries) can be 5 by DBMS
     Transformed to equivalent expressions
      automatically (query optimisation)


                                               4
                   Relation
   Relation is a set of tuples/records
     Tuple ordering irrelevant
     Cardinality of relation = number of tuples

   All tuples in a relation have the same
    structure; constructed from the same
    set of attributes
     Attributes  named (=> ordering irrelevant)
     Value of an attribute drawn from the
      attribute’s domain
                                                   5
   General format of a relation when
        represented as a table

                                   Attribute or
Attribute or
Column Name
               A1 A2 A3 … An       Column Name




               a11 a12 a13 … a1n
                                   Row or
                                   tuple values

               an1 an2 an3 … ann

                                                  6
Example




          7
       Relation as A Table (cont.)
   Mathematical entity corresponding to a
    table
     row ~ tuple
     column ~ attribute
   Values in a tuple are related to each other
     John   lives at 123 Main
   Relation R can be thought of as predicate
    R
     R(x,y,z)   is true iff tuple (x,y,z) is in R
                                                     8
    Relational Database Management
            System (RDBMS)
 Finite set of relations
 Each relation consists of a schema and an
  instance
 Database schema = set of relations (and
  other things)
 Database instance = set of (corresponding)
  relation instances

                                           9
    Database Schema (Example)

   Student (Id: INT, Name: STRING, Address:
    STRING, Status: STRING)
   Professor (Id: INT, Name: STRING, DeptId:
    DEPTS)
   Course (DeptId: DEPTS, CrsName: STRING,
    CrsCode: COURSES)
   Transcript (CrsCode: COURSES, StudId: INT,
    Grade: GRADES, Semester: SEMESTERS)
   Department(DeptId: DEPTS, Name: STRING)

                                                 10
              Integrity Constraints
 Part of schema
 Restriction on state (or sequence of states) of
  database
 Enforced by DBMS
 Intra-relational - involve only one relation
       Part of relation schema
       e.g., all Ids are unique
   Inter-relational - involve several relations
       Part of relation schema or database schema


                                                     11
          Database Integrity
 Implies that the data held in the tables of the
  database is consistent in terms of the
  Relational Data Model
 Two Types
    Entityintegrity
    Referential Integrity




                                                    12
         Relation Keys Constraints
   Key Constraint: Values in a column (or
    columns) of a relation are unique: at most one
    row in a relation instance can contain a
    particular value(s)


   What is a Key?
    A  minimal set of attributes satisfying key
      constraint

                                                     13
              Key Constraint (con’t)
   Every relation has a key
   Superkey – superset of a key (has more attributes)
       (Id, Name) is a superkey of Student
   Candidate Key-
       No two tuples of the relation will have identical entries in all
        attributes of the key.
       The number of attributes that comprises the key must be
        minimal.

   Primary Key- Since a table may have more than one candidate
    key, one should be designated as the primary key (PK) of the
    relation.

Examples:
       primary key (Id in Student) –
       candidate key ((Name, Address) in Student)
                                                                           14
                  Candidate Keys &
                    Primary Keys
   A RDBMS allows only one primary key per table.
       Once a PK has been selected, any remaining
        candidate keys are called alternate keys.
   A primary key may be composed of
       a single attribute (single primary key)
       More than one attribute (composite primary key)

 An attribute that is a primary key can not have a
  null value
 An attribute that is part of any key is called a
  prime attribute.

                                                          15
                     Example 1
Employee     EmpId       Name        Salary       NIN        Dno
 Table
              100       Hasan H.     1500        1250011     100

              101       Johns S.     2000        2360031     101

              102        Kelly A.     900        1299997     101

              103       Ibrams S.    1110        2359994     101

              104        Kelly A.    2000        5502300     100


                              Dno      Dname       Loc     Manager


           Department          100    Finance.     1500    1250011
             Table
                               101       IT        2000    2360031
                                                                 16
                    Example 2
                                     Number
                    EmpId   ProjId
                                     Of Hours
                     100     p22        15
Employee -Project
     Table
                     101     p20        9

                     102     p22        11

                     100     p23        24

                     104     p23        15

                     104     p22        11   17
               Candidate Keys &
              Primary Keys (Cont.)

   Primary keys are
       defined using Data Definition Language (DDL)
       Automatically enforced by the RDBMS
       Generally are defined at the time the tables are
        created.
   When selecting primary keys, we need to choose
    attributes that satisfy the uniqueness and
    minimalist conditions for all permissible data.


                                                           18
                      NULL

   What is NULL?
    a  NULL value is used to represent missing
      information, unknown, or inapplicable data.
     A NULL value is not a zero value
     A NULL value doesn’t represent a particular
      value within the computer.



                                                    19
           Foreign Key Constraint
   Referential integrity => Item named for primary key
    attribute/s in one relation must correspond to tuple(s) in
    another that describes the item
       Employee (ProjId) references Project(ProjId)
       Professor(DeptId) references Department(DeptId)
   a1 is a foreign key of R1 referring to a2 in R2 => if v is
    a value of a1, there is a unique tuple of R2 in which a2
    has value v
       This is a special case of referential integrity: a2 must be a
        candidate key of R2 (DeptId is a key of Department)
       If no row exists in R2 => violation of referential integrity
       Not all rows of R2 need to be referenced: relationship is not
        symmetric
       Value of a foreign key might not be specified (DeptId column of
        some professor might be null)                                 20
Foreign Key Constraint (Example)

                         a2
           a1            v3
           v1            v5
           v2            v1
           v3            v6
           v4            v2
           --            v7
           v3            v4
      R1                        R2
           Foreign key
                         Candidate/Primary key

                                                 21
5.7




      22
               Foreign Key (con’t)
   Names of a1 and a2 need not be the same.
      With SQL tables:
    Foreign key <name> professor (DeptId) references
    Department (Dno)

    DeptId attribute of Professor Table references to Dno attribute in
    Department Table

   R1 and R2 need not be distinct.
   The attributes of where the referential integrity
    exists must have the same data type and length.


                                                                   23
              Foreign Key (con’t)
   Foreign key might consist of several columns
       (CrsCode, Semester) of Transcript references (CrsCode,
        Sem) of Teaching
   R1(a1, …an) references R2(b1, …bn)
       There exists a 1 - 1 relationship between a1,…an and
        b1,…bn
       ai and bi have same domains (although not necessarily
        the same names)
       For every tuple T in R1 over ai’s there exists a unique
        tuple S in R2 over bi’s, with T = S
       b1,…bn is a candidate key of R2
                                                           24
              Semantic Constraints
 Domain, primary key, and foreign key are
  examples of structural (syntactic)
  constraints
 Semantic constraints express rules of
  application:
          number of registered students 
     e.g.,
      maximum enrollment
     SQL calls them Check constraint


                                             25
                  SQL
 Language for describing database schema
  and operations on tables
 Data Definition Language (DDL):
  sublanguage of SQL for describing schema
  and constraints




                                         26
                  Tables
 SQL entity that corresponds to a relation
 An element of the database schema
 SQL is current standard
 Database vendors generally deviate from
  standard




                                              27
Table Creation   Steps in table creation:
                 1.   Identify data types for
                      attributes
                 2.   Identify columns that can
                      and cannot be null
                 3.   Identify columns that must
                      be unique (candidate keys)
                 4.   Identify primary key-
                      foreign key mates
                 5.   Determine default values
                 6.   Identify constraints on
                      columns (domain
                      specifications)
                 7.   Create the table and
                      associated indexes
                                                28
        Common SQL Data Types (from Oracle)

   String types
       CHAR(n) – fixed-length character data, n characters long
        Maximum length = 2000 bytes
       VARCHAR2(n) – variable length character data, maximum
        4000 bytes
       LONG – variable-length character data, up to 4GB. Maximum
        1 per table
   Numeric types
       NUMBER(p,q) – general purpose numeric data type
       Numeric (p, q)- general purpose numeric data type
       INTEGER(p) – signed integer, p digits wide
       FLOAT(p) – floating point in scientific notation with p binary
        digits precision
   Date/time type
       DATE – fixed-length date/time in dd-mm-yy form                   29
 Table Declaration in SQL
      CREATE TABLE Student (
        Id, numaric(5),
        Name CHAR(20),
       AddressVARCHAR(50),
      Status: Boolean,
      Constraint Pk_Id primary key(Id));


 Id         Name     Address     Status
101222333 John 10 Cedar St Freshman
234567890 Mary 22 Main St Sophomore


                                           30
                   Student
 Primary, Unique, Check, Foreign, Null Keys
Create table department (
Dno           numeric(2),
Dname         varchar2(20),
Loc           varchar2(30) not null,
Constraint Pk_Dno primary key(Dno)
Constraint uk_name unique key(Dname));
Create table employee(
Eno           numeric(4),
Ename         varchar2(20),
Salary        numeric(7, 2) not null,
Hire_Date     Date not null,
Extention     char(4),
DeptNo        numeric(2),
Constraint Pk_Eno primary key(Eno),
Constraint Fk_Dno foreign key(DeptNo) reference
department (Dno),
                                                       31
Constraint ch_salary check (salary between 900 and 5000));
                 System Catalog
 CREATE TABLE inserts information into the
  catalog
 Catalog is another table that describes Objects
  created such as:
     Table names
     Constraint names
     Role Names
     Triggers, Sequences, Views, etc
     Attribute names of different tables
     Corresponding attribute types, etc.
 Catalog schema is generally fixed by vendor
 In Oracle SQL this catalog is called DICTIONARY
                                                    32
     Circularity in Foreign Key Constraint
       a1    a2     a3                 b1     b2      b3

 A      y            x                  x             y       B

  candidate key : a1                  candidate key : b1
  foreign key : a3 references B(b1)   foreign key : b3 references A(a1)


Problem 1: Creation of A requires existence of B and vice versa
Solution 1:   CREATE TABLE A ( ……), (* no foreign key *)
              CREATE TABLE B ( ……), (* foreign key included *)
              ALTER TABLE A
                 ADD CONSTRAINT cons
                    FOREIGN KEY (a3) REFERENCES B (b1)          33
Circularity in Foreign Key Constraint
  Problem 2 : Insertion of row in A requires prior
   existence of row in B and vice versa
  Solution 2 : DEFERRED constraint check - insert both
   rows within a single transaction

     CREATE TABLE Dept (…..
      DeptId: CHAR (4),
      MngrId : INTEGER,
      PRIMARY KEY DeptId,
      FOREIGN KEY (MngrId) REFERENCES Employee (Id) )
     CREATE TABLE Employee ( ….
      DeptId : CHAR (4),
      Id : INTEGER,
      PRIMARY KEY Id,
                                                          34
      FOREIGN KEY (DeptId) REFERENCES Dept ( DeptId) )
   Handling Foreign Key Violations/
             Anomalies
                  Insert Anomaly:
 Insertion into A: Reject if no row exists in Table
  B containing a primary key of the inserted row
 Insertion into B: No problem




  A              x                           B
                             x

                                                   35
    Handling Foreign Key Violations
     Deletion from B: Reject if the to be removed
      is linked to other rows in A
     Deletion from A: No problem
    Solution
     SET NULL: Set value of foreign key in referencing
      row(s) in A to null
     SET DEFAULT: Set value of foreign key in referencing
      row(s) in A to default value (y)
     CASCADE: Delete referencing row(s) in A as well


A                           B
         null

                                          y      y           36   B
                                A
     Handling Foreign Key Violations
   Update a candidate/primary key in B: Reject if row exists
    in A containing for the old value
   Update a foreign key in A (to z): Reject if no row exists in
    B containing the new value
   Update a foreign key in A (to z): NO problem if a row in
    B exist, which contain the new updated value

   Solution :
      SET NULL: Set value of foreign key to null
      SET DEFAULT: Set value of foreign key to default
      CASCADE: Propagate z to foreign key

                                                              37
              Specifying Actions
CREATE TABLE Teaching (
  ProfId INTEGER,
  CrsCode CHAR (6),
  Semester CHAR (6),
  PRIMARY KEY (CrsCode, Semester),
  FOREIGN KEY (ProfId) REFERENCES Professor (Id)
    ON DELETE CASCADE,
  FOREIGN KEY (CrsCode) REFERENCES Course
(CrsCode)
    ON DELETE SET NULL
    ON UPDATE CASCADE )
                                                   38

								
To top