Docstoc

Relational_Database-Chapter_3_The_Relational_Database_Model

Document Sample
Relational_Database-Chapter_3_The_Relational_Database_Model Powered By Docstoc
					Database Systems: Design, Implementation, and Management, 8th Edition   3-1



Chapter 3
The Relational Database Model

At a Glance

Instructor’s Manual Table of Contents
•   Overview

•   Objectives

•   Teaching Tips

•   Quick Quizzes

•   Class Discussion Topics

•   Additional Projects

•   Additional Resources

•   Key Terms
Database Systems: Design, Implementation, and Management, 8th Edition                        3-2




Lecture Notes

Overview
        Chapter 3 presents characteristics of tables and explains what keys are. It
        discusses relational operators in some depth with examples. Relationships in a
        relational database are revisited, and the implementation of M:N relationships
        using a composite entity is presented. Data redundancy is reviewed and discussed
        in more depth. A discussion of indexes follows. The chapter ends with Dr. Codd’s
        relational database rules.


Chapter Objectives
    In this chapter, students will learn:
    • That the relational database model offers a logical view of data
    • About the relational model’s basic component: relations
    • That relations are logical constructs composed of rows (tuples) and columns
        (attributes)
    • That relations are implemented as tables in a relational DBMS
    • About relational database operators, the data dictionary, and the system catalog
    • How data redundancy is handled in the relational database model
    • Why indexing is important


Teaching Tips
    1. Review Chapter 2, specifically the relational data model, structural and data
       independence, and entity relationship diagrams (ERDs).

    2. Introduce topics covered by this chapter, such as the table as a logical construct,
       and note that individual tables within the database can be related to one another.

    3. Note that the chapter will introduce basic concepts in the design of tables and
       basic concepts that become important in the coming chapters.


    3.1 A Logical View of Data

    1. Remind students that placing the DBMS between the application and the database
       eliminates most of the file system’s limitations.

    2. Emphasize that the increased flexibility comes at the expense of complexity.
Database Systems: Design, Implementation, and Management, 8th Edition                            3-3


    3. Explain that the relational data model allows the designer to focus on the logical
       representation of the data and its relationships

    3.1.1 Tables and Their Characteristics

    1. Introduce the relation, and explain that it is a mathematical construct.

    2. Explain that end users often find it easier to think of a relation as a table, and that
       a table is thought of as a two-dimensional structure of rows and columns.



                It may not be obvious to students why a relation should be thought of as a table.
Teaching
                Spend some time in class discussing this. Note that Dr. Codd used the term
Tip
                relation as a synonym for table. Explain why.


    3. Explain that a table can be thought of as a persistent relation, and explain why,
       emphasizing that from the perspective of the end user, a table contains a group of
       related entity occurrences.

    4. Note that the terms entity set and table are often used interchangeably.

    5. Walk through Table 3.1 to discuss characteristics of a relational table.

    6. Walk through the example presented in Figure 3.1 and the itemized list on pages
       64 – 66, and introduce the concept of a primary key.


    3.2 Keys

    1. Explain what a key is and why it is important.

    2. Explain that in addition to uniquely identifying each row in the table, they
       establish relationships among tables and ensure the integrity of the data.

    3. Discuss the concept of determination.

    4. Remind students what a primary key is, and explain that a key’s role is based on
       the concept of determination.

    5. Explain what functional dependence is.
Database Systems: Design, Implementation, and Management, 8th Edition                         3-4




Teaching        Emphasize that students should be careful when defining a dependency’s
Tip             direction.


    6. Explain that it may take more than a single attribute to define functional
       dependence, and explain that a multi-attribute key is a composite key.

    7. Explain that any attribute that is part of a key is a key attribute, and provide an
       example.

    8. Explain what full functional dependence is, and walk through the example on
       page 68.



Teaching        Take the time to discuss the difference between functional dependence and full
Tip             functional dependence.


    9. Explain that within the broad key classification, several specialized keys can be
       defined, and a superkey is any key that uniquely identifies each row.

    10. Explain what entity integrity is, and that a null value is not permitted in the
        primary key.

    11. Emphasize that nulls can never be part of a primary key, and explain why they
        should be avoided in general.

    12. Walk through the list on page 69 of possible meanings of nulls, and discuss why
        the multiple definitions create problems.

    13. Explain that controlled redundancy makes a relational database work, and discuss
        how tables are linked by common attributes.

    14. Walk through the example presented in Figure 3.2.

    15. Explain that a relational database can be represented by a relational schema, and
        explain what a relational schema is.



Teaching        It may be helpful to students to briefly review what an entity relationship
Tip             diagram (ERD) is.
Database Systems: Design, Implementation, and Management, 8th Edition                       3-5


    16. Explain what a foreign key (FK) is, and discuss how foreign keys exhibit
        referential integrity. Explain the concept of referential integrity.

    17. Explain that a secondary key is strictly used for data retrieval purposes.



Teaching        Take the time to walk through an example of how a secondary key might be used
Tip             for data retrieval purposes, and discuss when this might be useful.


    18. Walk through Table 3.3 to summarize different types of relational database keys.


    Quick Quiz 1
    1. Data relationships in a relational database are based on a logical construct known
       as a(n) ____.
       Answer: relation

    2. A(n) ____ consists of one or more attributes that determine other attributes.
       Answer: key

    3. True or False: The statement “A determines B” indicates that if you know the
       value of attribute B, you can look up the value of attribute A.
       Answer: False

    4. True or False: The attribute B is functionally dependent on A if A determines B.
       Answer: True


    3.3 Integrity Rules
    1. Emphasize that relational database integrity rules are important to good database
       design, and walk through Table 3.4, which summarizes the rules.

    2. Walk through Figure 3.4, which provides an illustration of integrity rules.

    3. Explain the use of flags to avoid nulls, and use Table 3.5 to illustrate.


Teaching
                Take the time to discuss why using flags is preferable to using nulls.
Tip
Database Systems: Design, Implementation, and Management, 8th Edition                           3-6


    3.4 Relational Set Operators

    1. Explain what relational algebra is, and discuss each of the eight relational
       operators.

    2. Explain the closure property.

    3. Walk through the itemized list, which runs from page 73 to page 78, to discuss
       each operator in more detail. Use Figures 3.5 through 3.17 to illustrate.

    4. Explain that union-compatible means that two or more tables have the same
       number of columns, and the columns have the same names and domains.


Teaching
                Emphasize that the JOIN operator is the real power behind a relational database.
Tip

    5. Explain that a natural join links tables by selecting only rows with common
       values in their common attributes.


Teaching
                Take time to walk through the three-step process resulting in a natural join.
Tip

    6. Explain the different types of joins, including equijoins, theta joins, and left and
       right outer joins


    3.5 The Data Dictionary and the System Catalog

    1. Explain that a data dictionary provides a detailed description of all tables found
       within the database.

    2. Explain that a data dictionary includes attribute names and characteristics for each
       table in the system, and use Figure 3.4 and Table 3.6 to illustrate.

    3. Explain that a system catalog contains metadata, and describes all objects within
       the database.



Teaching        It may be helpful to students to compare and contrast a data dictionary with a
Tip             system catalog.
Database Systems: Design, Implementation, and Management, 8th Edition                       3-7


    4. Explain what homonyms and synonyms are in the context of a database.


    Quick Quiz 2
    1. A requirement of _____ integrity is that all primary key entries are unique and no
       part of a primary key may be null.
       Answer: entity

    2. A requirement of ____ integrity is that every non-null foreign key value must
       reference an exiting primary key value.
       Answer: referential

    3. When two or more tables share the same number of columns, when the columns
       have the same names, and when they share the same domains, they are said to be
       ____.
       Answer: union-compatible

    4. In a(n) ____ join, the matched pairs are retained and any unmatched values in the
       other table would be left null.
       Answer: outer


    3.6 Relationships within the Relational Database

    1. Remind students what one-to-one, one-to-many, and many-to-many relationships
       are.

    2. Emphasize that 1:M relationships are the norm in relational database design.

    3. Emphasize that 1:1 relationships are rare in a relational database.

    4. Remind students that many-to-many relationships cannot be implemented in a
       relational model.

    3.6.1 The 1:M Relationship

    1. Use Figures 3.18 through 3.21 to illustrate 1:M relationships and their
       implementation in a relational database.

    3.6.2 The 1:1 Relationship

    1. Use Figures 3.22 and 3.23 to illustrate 1:1 relationships, reminding students they
       are rare in a relational database.

    2. Discuss conditions that require a 1:1 relationship.
Database Systems: Design, Implementation, and Management, 8th Edition                       3-8


    3.6.3 The M:N Relationship

    1. Explain that the M:N relationship is not directly supported in a relational
       environment.

    2. Explain that M:N relationships are implemented by creating a new entity, which
       has 1:M relationships with the original entities.

    3. Use Figures 3.24 and 3.25 with Table 3.7 to illustrate M:N relationships.

    4. Point out the problems in implementation discussed in the bulleted list on page
       85.

    5. Explain what a composite entity is, and how its use avoids many of the problems
       in M:N relationships.

    6. Walk through Figures 3.26 through 3.29 to illustrate how M:N relationships are
       implemented.


    3.7 Data Redundancy Revisited

    1. Remind students what data anomalies are, and how they can be avoided using
       foreign keys.



Teaching        Emphasize that using foreign keys will not eliminate data anomalies, but will
Tip             minimize them.


    2. Introduce situations when data redundancy must increase to make the database
       serve information purposes, and walk through the example presented in Figures
       3.30 and 3.31.


3.7 Indexes
    1. Explain that an index is an orderly arrangement to access rows in a table.

    2. Explain that an index key is the index reference point, and each key points to the
       location of the data identified by the key.

    3. Use Figures 3.19 and 3.32 to illustrate the use of an index.
Database Systems: Design, Implementation, and Management, 8th Edition                       3-9


    4. Explain that a unique index is an index in which the key can have only one
       pointer value associated with it.

    5. Emphasize that although a table can have many indexes, each index is associated
       with only one table.


3.8 Codd’s Relational Database Rules

    1. Explain that Dr. E. F. Codd published a list of 12 rules to define a relational
       database system, and discuss his purpose in doing so.

    2. Walk through Table 3.8, which lists Dr. Codd's rules.



Teaching        Remind students that most database vendors do not support all 12 of Dr. Codd’s
Tip             rules, and take time to discuss why.



Quick Quiz 3
    1. True or False: There are no situations that require the use of 1:1 relationships.
       Answer: False

    2. A(n) ____ table is the implementation of a composite entry.
       Answer: linking

    3. A(n) ____ is an orderly arrangement used to logically access rows in a table.
       Answer: index

    4. True or False: According to Dr. Codd’s relational database rules, any view that is
       theoretically updatable must be updatable through the system.
       Answer: True


Class Discussion Topics
    1. What types of joins are most appropriate for which types of problems? For
       example, what types of problems would be best addressed with an equijoin or a
       theta join compared to other types of joins?

    2. Why is it not always appropriate to use the terminology “record” and “field” in
       place of “row” and “column”?
Database Systems: Design, Implementation, and Management, 8th Edition                           3-10



Additional Projects
    1. Compare and contrast a data dictionary and a system catalog.

    2. Explain why M:N relationships cannot be directly implemented in a relational
       database.


Additional Resources
    1. An online article about relational databases:
       www.firstsql.com/ireldb.htm

    2. A blog discussion of the limitations of the relational model:
       www.dbms2.com/2005/10/10/limitations-of-the-relational-model/

    3. A Wikipedia article listing Dr. Codd’s rules:
       http://en.wikipedia.org/wiki/Codd's_12_rules

    4. An Oracle page on data integrity:
       http://download-
       west.oracle.com/docs/cd/B10501_01/server.920/a96524/c22integ.htm


Key Terms
        associative entity—See composite entity.
        attribute domain—See domain.
        bridge entity—See composite entity.
        candidate key—See key.
        closure—A property of relational operators that permits the use of relational
        algebra operators on existing tables (relations) to produce new relations.
        composite entity—An entity designed to transform an M:N relationship into two
        1:M relationships. The composite entity’s primary key comprises at least the
        primary keys of the entities that it connects. Also known as a bridge entity. See
        also linking table.
        composite key—A multiple-attribute key.
        data dictionary—A DBMS component that stores metadata—data about data.
        Thus, the data dictionary contains the data definition as well as its characteristics
        and relationships. A data dictionary may also include data that are external to the
        DBMS. Also known as an information resource dictionary. See also active data
        dictionary, metadata, and passive data dictionary.
        determination—The role of a key. In the context of a database table, the
        statement “A determines B” indicates that knowing the value of attribute A means
        that the value of attribute B can be looked up (determined).
Database Systems: Design, Implementation, and Management, 8th Edition                           3-11


        domain—In data modeling, refers to the construct used to organize and describe
        an attribute’s set of possible values.
        entity integrity—The property of a relational table that guarantees that each
        entity has a unique value in a primary key and that there are no null values in the
        primary key.
        equijoin—A join operator that links tables based on an equality condition that
        compares specified columns of the tables.
        flags—Special codes implemented by designers to trigger a required response, to
        alert end users to specified conditions, or to encode values. Flags may be used to
        prevent nulls by bringing attention to the absence of a value in a table.
        foreign key (FK)—See key.
        full functional dependence—A condition in which an attribute is functionally
        dependent on a composite key but not on any subset of that composite key.
        functional dependence—Within a relation R, an attribute B is functionally
        dependent on an attribute A if and only if a given value of the attribute A
        determines exactly one value of the attribute B. The relationship “B is dependent
        on A” is equivalent to “A determines B” and is written as A B.
        homonyms—Indicates the use of the same name to label different attributes;
        generally should be avoided. Some relational software automatically checks for
        homonyms and either alerts the user to their existence or automatically makes the
        appropriate adjustments. See also synonym.
        index—An ordered array composed of index key values and row ID values
        (pointers). Indexes are generally used to speed up and facilitate data retrieval.
        Also known as an index key.
        index key—See index.
        join column(s)—A term used to refer to the columns that join two tables. The
        join columns generally share similar values.
        key—An entity identifier based on the concept of functional dependence; may be
        classified as follows: Superkey: An attribute (or combination of attributes) that
        uniquely identifies each entity in a table. Candidate key: A minimal attribute that
        is itself a superkey. Primary key (PK): A candidate key selected as a unique entity
        identifier. Secondary key: A key that is used strictly for data retrieval purposes.
        For example, a customer is not likely to know his or her customer number
        (primary key), but the combination of last name, first name, middle initial, and
        telephone number is likely to make a match to the appropriate table row. Foreign
        key: An attribute (or combination of attributes) in one table whose values must
        match the primary key in another table or whose values must be null.
        key attribute—The attribute(s) that form(s) a primary key. See also prime
        attribute.
        left outer join—In a pair of tables to be joined, a left outer join yields all of the
        rows in the left table, including those that have no matching values in the other
        table. For example, a left outer join of Customer with Agent will yield all of the
        Customer rows, including the ones that do not have a matching Agent row. See
        also outer join and right outer join.
        linking table—In the relational model, a table that implements a M:M
        relationship. See also composite entity.
Database Systems: Design, Implementation, and Management, 8th Edition                          3-12


        natural join—A relational operation that links tables by selecting only the rows
        with common values in their common attribute(s).
        null—In SQL, refers to the absence of an attribute value. Note: A null is not a
        blank.
        outer join—A relational-algebra JOIN operation that produces a table in which
        all unmatched pairs are retained; unmatched values in the related table are left
        null. Contrast with inner join. See also left outer join and right outer join.
        predicate logic—Used extensively in mathematics, provides a framework in
        which an assertion (statement of fact) can be verified as either true or false. For
        example, suppose that a student with a student ID of 12345678 is named Melissa
        Sanduski. That assertion can easily be demonstrated to be true or false.
        primary key (PK)—In the relational model, an identifier composed of one or
        more attributes that uniquely identifies a row. See also key.
        referential integrity—A condition by which a dependent table’s foreign key
        must have either a null entry or a matching entry in the related table. Even though
        an attribute may not have a corresponding attribute, it is impossible to have an
        invalid entry.
        relational algebra—A set of mathematical principles that form the basis of the
        manipulation of relational table contents; composed of eight main functions:
        SELECT, PROJECT, JOIN, INTERSECT, UNION, DIFFERENCE, PRODUCT,
        and DIVIDE.
        relational schema—The description of the organization of a relational database
        as seen by the database administrator.
        right outer join—In a pair of tables to be joined, a right outer join yields all of
        the rows in the right table, including the ones with no matching values in the other
        table. For example, a right outer join of CUSTOMER with AGENT will yield all
        of the agent rows, including the ones that do not have a matching CUSTOMER
        row. See also left outer join and outer join.
        secondary key—A key that is used strictly for data retrieval purposes. For
        example, a customer is not likely to know his or her customer number (primary
        key), but the combination of last name, first name, middle initial, and telephone
        number is likely to make a match to the appropriate table row. See also key.
        set theory—A mathematical science component that deals with sets, or groups of
        things, and is used as the basis for data manipulation in the relational model.
        superkey—See key.
        synonym—The use of different names to identify the same object, such as an
        entity, an attribute, or a relationship; should generally be avoided. See also
        homonym.
        system catalog—A detailed system data dictionary that describes all objects in a
        database.
        theta join—A join operator that links tables, using an inequality comparison
        operator (<, >, <=, >=) in the join condition.
        tuple—In the relational model, a table row.
        union-compatible—Two or more tables are union-compatible when they share
        the same column names and the columns have compatible data types or domains.
Database Systems: Design, Implementation, and Management, 8th Edition                  3-13


        unique index—An index in which the index key can have only one pointer value
        (row) associated with it.

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:75
posted:6/26/2012
language:English
pages:13