Docstoc

MYCH3

Document Sample
MYCH3 Powered By Docstoc
					                           The Relational Model


                                           Chapter 3




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   1
       Why Study the Relational Model?

        v Most widely used model.
        v Multi-billion dollar industry, $15+ bill in
          2006.
        v Vendors: IBM, Microsoft, Oracle, SAP,
          Peoplesoft, Informix, Sybase.
        v Recent competitor: object-oriented model
             §   ObjectStore, Versant, Ontos
             §   A synthesis emerging: object-relational model
                  • Informix Universal Server, UniSQL, O2, Oracle, DB2

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke           2
        Overview

        v The Relational Model
        v Creating Relations in SQL




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   3
        Relational Database: Definitions

        v Relational database: a set of relations
        v Relation: made up of 2 parts:
             §   Instance : a table, with rows and columns.
                 #Rows = cardinality, #fields = degree / arity.
             §   Schema : specifies name of relation, plus name and
                 type of each column.
                  • e.g., Students(sid: string, name: string, login: string,
                    age: integer, gpa: real).
        v   Can think of a relation as a set of rows or
            tuples (all rows are distinct).

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                 4
        Example Instance of Students Relation




      v   Cardinality = 3, degree = 5, all rows distinct
      v   Do all columns in a relation instance have to
          be distinct?

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   5
        Quick Question
         How many distinct tuples are in a relation instance with
         cardinality 22?




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke      6
        Relational Query Languages

        v A major strength of the relational model:
          supports simple, powerful querying of data.
        v Queries can be written intuitively, and the
          DBMS is responsible for efficient evaluation.
             §   The key: precise semantics for relational queries.
             §   Allows the optimizer to extensively re-order
                 operations, and still ensure that the answer does
                 not change.



Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke        7
        The SQL Query Language

        v Developed by IBM (system R) in the 1970s
        v Need for a standard since it is used by many
          vendors
        v Standards:
             §   SQL-86
             §   SQL-89 (minor revision)
             §   SQL-92 (major revision)
             §   SQL-99 (major extensions)
             §   ...SQL-2011

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   8
        The SQL Query Language

        v   To find all 18 year old students, we can write:

              SELECT *
              FROM Students S
              WHERE S.age=18


            •To find just names and logins, replace the first line:
               SELECT      S.name, S.login



Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke        9
        Exercise

        1.   Modify this query so that                     SELECT *
             only the login column is                      FROM Students S
             included in the answer.
                                                           WHERE S.age=18
        2.   If the clause WHERE S.gpa
             >= 3.3 is added to the
             original query, what is the
             set of tuples in the answer?
        3.   What if the clause WHERE
             S.gpa > Jones is added?




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke               10
         Querying Multiple Relations
        v   What does the following query compute?
                 SELECT S.name, E.cid
                 FROM Students S, Enrolled E
                 WHERE S.sid=E.sid AND E.grade=“A”

    Given the following instance
    of Enrolled:




        we get:
        Subset of Cross-Product of S,E
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   11
     Data Description
     Creating Tables in SQL




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   12
        Creating Relations in SQL
     v Creates the Students         CREATE TABLE Students
       relation. Observe that the        (sid CHAR(20),
       type (domain) of each field        name CHAR(20),
       is specified, and enforced by      login CHAR(10),
                                          age INTEGER,
       the DBMS whenever tuples           gpa REAL)
       are added or modified.
     v As another example, the
                                    CREATE TABLE Enrolled
       Enrolled table holds              (sid CHAR(20),
       information about courses          cid CHAR(20),
       that students take.                grade CHAR(2))

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   13
        Destroying and Altering Relations

     DROP TABLE          Students

    v   Destroys the relation Students. The schema
        information and the tuples are deleted.

    ALTER TABLE          Students ADD COLUMN firstYear: integer


    v   The schema of Students is altered by adding a
        new field; every tuple in the current instance
        is extended with a null value in the new field.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke    14
        Adding and Deleting Tuples
        v   Can insert a single tuple using:
                    INSERT INTO Students (sid, name, login, age,   gpa)
                    VALUES (53688, ‘Smith’, ‘smith@ee’, 18, 3.2)

        v   Can delete all tuples satisfying some
            condition (e.g., name = Smith):
                           DELETE
                           FROM Students S
                           WHERE S.name =             ‘Smith’

      * Powerful variants of these commands are available; more later!
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke            15
     Data Description
     Specifying Constraints in SQL




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   16
         Integrity Constraints (ICs)
     v   IC: condition that must be true for any instance
         of the database; e.g., domain constraints.
          §   ICs are specified when schema is defined.
          §   ICs are checked when relations are modified.
     v   A legal instance of a relation is one that satisfies
         all specified ICs.
          §   DBMS should not allow illegal instances.
     v   If the DBMS checks ICs, stored data is more
         faithful to real-world meaning.
          §   Avoids data entry errors, too!
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   17
        Primary Key Constraints
       v   A set of fields is a (candidate) key for a
           relation if :
            1. No two distinct tuples can have same values in all
               key fields, and
            2. This is not true for any subset of the key.
            § Part 2 false? A superkey.
            § If there’s >1 key for a relation, one of the keys is
               chosen (by DBA) to be the primary key.
       v   Examples.
            § sid is a key for Students. (What about name?)
            § The set {sid, gpa} is a superkey.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke       18
        Primary and Candidate Keys in SQL
v   Possibly many candidate keys (specified using
    UNIQUE), one of which is chosen as the primary key.
v   “For a given student and course, CREATE TABLE Enrolled
    there is a single grade.” vs.       (sid CHAR(20),
    “Students can take only one           cid CHAR(20),
    course, and receive a single grade    grade CHAR(2),
    for that course; further, no two      PRIMARY KEY (sid,cid) )
    students in a course receive the   CREATE TABLE Enrolled
    same grade.”                         (sid CHAR(20)
v   What does the Unique constraint        cid CHAR(20),
    do? Is this a good idea?               grade CHAR(2),
                                           PRIMARY KEY (sid),
                                           UNIQUE (cid, grade) )
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   19
        Exercise




           •    Give an example of an attribute (or set of attributes)
                that you can deduce is not a candidate key, if this
                instance is legal.
           •    Is there any example of an attribute (or set of
                attributes) that you can deduce is a candidate key?
           •    Does every relational schema have some candidate
                key?
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke           20
        Foreign Keys, Referential Integrity

  v Foreign key : Set of fields in one relation that is
    used to `refer’ to a tuple in another relation. (Must
    correspond to primary key of the second relation.)
    Like a `logical pointer’.
  v E.g. sid is a foreign key referring to Students:
       §   Enrolled(sid: string, cid: string, grade: string)
       §   If all foreign key constraints are enforced, referential
           integrity is achieved, i.e., no dangling references.
       §   Can you name a data model w/o referential integrity?
            •   Links in HTML!


Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke        21
        Foreign Keys in SQL
  v   Only students listed in the Students relation should
      be allowed to enroll for courses.
            CREATE TABLE Enrolled
             (sid CHAR(20), cid CHAR(20), grade CHAR(2),
              PRIMARY KEY (sid,cid),
              FOREIGN KEY (sid) REFERENCES Students )
  Enrolled
                                                   Students




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   22
         Enforcing Referential Integrity
v    Consider Students and Enrolled; sid in Enrolled is
     a foreign key that references Students.
1.   What should be done if an Enrolled tuple with a
     non-existent student id is inserted?
2.   What should be done if a Students tuple is deleted?
     §   Also delete all Enrolled tuples that refer to it.
     §   Disallow deletion of a Students tuple that is referred to.
     §   Set sid in Enrolled tuples that refer to it to a default sid.
     §   (In SQL, also: Set sid in Enrolled tuples that refer to it to a
         special value null, denoting `unknown’ or `inapplicable’.)
v    Similar if primary key of Students tuple is updated.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke             23
        Referential Integrity in SQL
v   SQL/92 and SQL:1999            CREATE TABLE Enrolled
    support all 4 options on        (sid CHAR(20),
    deletes and updates.             cid CHAR(20),
     § Default is NO ACTION          grade CHAR(2),
       (delete/update is rejected)   PRIMARY KEY (sid,cid),
     § CASCADE (also delete          FOREIGN KEY (sid)
       all tuples that refer to       REFERENCES Students
       deleted tuple)                     ON DELETE CASCADE
                                          ON UPDATE SET DEFAULT )
     § SET NULL / SET DEFAULT
       (sets foreign key value
       of referencing tuple)

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke      24
        Primary and Foreign Keys
        v   A foreign key must point to a primary key.
        v   In logic, we can introduce names to denote entities.
        v   In object-oriented models, we can have object IDs.
        v   In the relational model, primary keys play the role of
            names of entities.
        v   Base tables define names for entities.
             § E.g. student ids in Students.
        v   Foreign keys point to names defined by other tables.




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke       25
        Where do ICs Come From?
       v ICs are based upon the semantics of the real-
         world enterprise that is being described in the
         database relations.
       v We can check a database instance to see if an
         IC is violated, but we can NEVER infer that
         an IC is true by looking at an instance.
            §   An IC is a statement about all possible instances!
            §   From example, we know name is not a key, but the
                assertion that sid is a key is given to us.
       v   Key and foreign key ICs are the most
           common; more general ICs supported too.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke       26
      Translate ER Diagrams to SQL




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   27
        Problem Solving Steps

        v Understand the business rules/requirements
        v Draw the ER diagram
        v Draw the Relational Model
        v Write the SQL and create the database




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   28
         Logical DB Design: ER to Relational

     v   Entity sets to tables:

                                                           CREATE TABLE Employees
                       name                                    (ssn CHAR(11),
      ssn                                   lot                name CHAR(20),
                                                               lot INTEGER,
                    Employees                                  PRIMARY KEY (ssn))




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                  29
       Review: The Works_In Relation
                                                 sinc
                         nam                     e                      dname
              ssn        e           lo                          did                budget
                                     t
                       Employees              Manages                  Department
                                                                       s

                                                Works_I
                                                n

                                                 sinc
                                                 e
    Exercise:
    • Write a create statement for the Departments entity set.
    • Write a create statement for the Works_In relation.
         Don’t worry too much about the exact syntax. It’s enough for now just to
         have an approach. E.g, how many columns do you need? What kind of
         columns?
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                               30
     Relationship Sets to Tables
 v   In translating a relationship                    CREATE TABLE Works_In(
     set to a relation, attributes of                  ssn CHAR(11),
     the relation must include:                        did INTEGER,
      § Keys for each                                  since DATE,
        participating entity set                       PRIMARY KEY (ssn, did),
        (as foreign keys).                             FOREIGN KEY (ssn)
          • This set of attributes                        REFERENCES Employees,
            forms a key for the                        FOREIGN KEY (did)
            relation. (Superkey?)                         REFERENCES Departments)
      § All descriptive attributes.




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                 31
     Key Constraints
                                                                  since
v   Each dept has at                        name                                    dname

    most one manager,                 ssn            lot                      did           budget

    according to the
    key constraint on                     Employees              Manages        Departments
    Manages.



                                                                          Translation to
                                                                          relational model?

     1-to-1          1-to Many        Many-to-1       Many-to-Many

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                                 32
        Translating ER Diagrams with Key Constraints
                                       CREATE TABLE Manages(
v   Map relationship to a               ssn CHAR(11),
    table:                              did INTEGER,
     § Note that did is                 since DATE,
                                        PRIMARY KEY (did),
       the key now!                     FOREIGN KEY (ssn) REFERENCES Employees,
     § Separate tables for              FOREIGN KEY (did) REFERENCES Departments)
       Employees and
       Departments.                     CREATE TABLE Dept_Mgr(
v   Since each                            did INTEGER,
    department has a                      dname CHAR(20),
    unique manager, we                    budget REAL,
                                          ssn CHAR(11),
    could instead                         since DATE,
    combine Manages                       PRIMARY KEY (did),
    and Departments.                      FOREIGN KEY (ssn) REFERENCES Employees)


Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                      33
     Participation Constraints
                                                    CREATE TABLE Works_In(
 v   Every department must                           ssn CHAR(11),
     have some employee.                             did INTEGER,
 v   Each employee must work                         since DATE,
     in some department.                             PRIMARY KEY (ssn, did),
 v   Can we capture these                            FOREIGN KEY (ssn)
     constraints?                                       REFERENCES Employees,
                                                     FOREIGN KEY (did)
                                                        REFERENCES Departments)

                 nam                                              dname
       ssn       e           lo            sinc        did                    budget
                             t             e
               Employees                                         Department
                                         Works_I                 s
                                         n
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                         34
       Participation Constraint + Key
       Constraint
v Every department must have a manager.
v Can we capture this constraint?




                                                 since
                         name                                           dname
              ssn                    lot                         did             budget

                       Employees              Manages                  Departments


                                                Works_In



                                                 since
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                            35
        Participation Constraints in SQL
v   We can capture participation constraints involving
    one entity set in a binary relationship, but little else
    (without resorting to CHECK constraints).
              CREATE TABLE Dept_Mgr(
               did INTEGER,
               dname CHAR(20),
               budget REAL,
               ssn CHAR(11) NOT NULL,
               since DATE,
               PRIMARY KEY (did),
               FOREIGN KEY (ssn) REFERENCES                      Employees,
                 ON DELETE NO ACTION)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                36
        Review: Weak Entities
v   A weak entity can be identified uniquely only by
    considering the primary key of another (owner) entity.
    §   Owner entity set and weak entity set must participate in a
        one-to-many relationship set (1 owner, many weak entities).
    §   Weak entity set must have total participation in this
        identifying relationship set.

                     name
                                                    cost         pname        age
        ssn                        lot



                 Employees                         Policy            Dependents


Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                      37
        Translating Weak Entity Sets
      v   Weak entity set and identifying relationship set are
          translated into a single table.
           § When the owner entity is deleted, all owned weak
             entities must also be deleted.
           § What guarantees existence of owner?

     CREATE TABLE Dep_Policy (
      pname CHAR(20),
      age INTEGER,
      cost REAL,
      owner CHAR(11),
      PRIMARY KEY (pname, owner),
      FOREIGN KEY (owner) REFERENCES                             Employees(ssn),
        ON DELETE CASCADE)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                     38
                                                                               name
                                                                    ssn                    lot

        Review: ISA Hierarchies                                            Employees


                                           hourly_wages     hours_worked
                                                                                ISA
v As in C++, or other PLs,                                                               contractid

attributes are inherited.
                                                                 Hourly_Emps          Contract_Emps
v If we declare A ISA B, every A
entity is also considered to be a B
entity.
 v   Overlap constraints: Can Joe be an Hourly_Emps as well as
     a Contract_Emps entity? (Allowed/disallowed)
 v   Covering constraints: Does every Employees entity also have
     to be an Hourly_Emps or a Contract_Emps entity? (Yes/no)


Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                                        39
        Translating ISA Hierarchies to Relations
v   General approach:
    §   3 relations: Employees, Hourly_Emps and Contract_Emps.
         • Hourly_Emps: Every employee is recorded in
           Employees. For hourly emps, extra info recorded in
           Hourly_Emps (hourly_wages, hours_worked, ssn); must
           delete Hourly_Emps tuple if referenced Employees
           tuple is deleted).
         • Queries involving all employees easy, those involving
           just Hourly_Emps require a join with Employees to get
           some attributes.
v   Alternative: Just Hourly_Emps and Contract_Emps.
    §   Hourly_Emps: ssn, name, lot, hourly_wages, hours_worked.
    §   Each employee must be in one of these two subclasses.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke     40
    Review: Binary vs. Ternary
    Relationships                 ssn
                                         name
                                                        lot                            pname       age

                                         Employees                   Covers                Dependents

v   What are the                    Bad design                       Policies
    additional
    constraints in                                        policyid              cost

    the 2nd                     ssn
                                        name
                                                  lot
                                                                                       pname        age

    diagram?                          Employees
                                                                                               Dependents


                                                   Purchaser
                                                                         Beneficiary


                                      Better design                  Policies

                                                          policyid         cost
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                                            41
        Binary vs. Ternary Relationships (Contd.)
                               CREATE TABLE Policies (
v   The key                     policyid INTEGER,
    constraints allow           cost REAL,
    us to combine
                                ssn CHAR(11) NOT NULL,
    Purchaser with
                                PRIMARY KEY (policyid).
    Policies and
    Beneficiary with                                Employees,
                                FOREIGN KEY (ssn) REFERENCES
    Dependents.                   ON DELETE CASCADE)
v   Participation     CREATE TABLE Dependents (
    constraints lead   pname CHAR(20),
    to NOT NULL        age INTEGER,
    constraints.       policyid INTEGER,
                       PRIMARY KEY (pname, policyid).
                       FOREIGN KEY (policyid) REFERENCES Policies,
                         ON DELETE CASCADE)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   42
        Summary: From ER to SQL

        v Basic construction: each entity set becomes a
          table.
        v Each relationship becomes a table with
          primary keys that are also foreign keys
          referencing the entities involved.
        v Key constraints in ER give option of merging
          entity table with relationship table (e.g.
          Dept_Mgr).
             § Use non-null to enforce participation.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   43
       Views




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   44
        Views
v   A view is just a relation, but we store a
    definition, rather than a set of tuples.
           CREATE VIEW YoungActiveStudents (name,                grade)
                AS SELECT S.name, E.grade
                FROM Students S, Enrolled E
                WHERE S.sid = E.sid and S.age<21

v   Views can be dropped using the DROP VIEW command.
    §   How to handle DROP TABLE if there’s a view on the table?
         • DROP TABLE command has options to let the user specify
           this.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke            45
        Views and Security

        v   Views can be used to present necessary
            information (or a summary), while hiding
            details in underlying relation(s).
             §   Given YoungStudents, but not Students or
                 Enrolled, we can find students s who have are
                 enrolled, but not the cid’s of the courses they are
                 enrolled in.




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke         46
        Exercise 3.19
          Consider the following schema.
          Emp(eid: integer, ename: string, age: integer, salary: real)
          Works(eid: integer, did: integer, pct_time: integer)
          Dept(did: integer, budget: real, managerid: integer)
          And the view
          CREATE VIEW SeniorEmp(sname, sage, salary)
          AS SELECT E.ename, E.age, E.salary
          FROM Emp E
          WHERE E.age >50
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke           47
        Exercise ctd.
          How will the system process the query:
          SELECT S.sname
          FROM SeniorEmp S
          WHERE S.salary > 100,000




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   48
        Relational Model: Summary

        v   A tabular representation of data.
        v   Simple and intuitive, currently the most widely used.
        v   Integrity constraints can be specified by the DBA,
            based on application semantics. DBMS checks for
            violations.
             §   Two important ICs: primary and foreign keys
             §   In addition, we always have domain constraints.
        v   Powerful and natural query languages exist.
        v   Rules to translate ER to relational model


Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke      49

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:0
posted:8/4/2014
language:Unknown
pages:49