chapter3

							3.1 A Logical View of Data                                   Characteristics of a Relational Table
     Unlike the hierarchical and network data
        models, the relational data model allows      1.     A table is perceived as a two-
        the designer to focus on the logical                 dimensional structure composed of
        representation of the data and its                   rows and columns.
        relationships, rather than the physical
                                                      2.     Each table (tuple) represents a single
        storage details.
                                                             entity occurrence within the entity
     Relational database model enables us to
                                                             set.
        view data logically rather than physically
        by using tables.                              3.     Each table column represents an
     Relational data models has the                         attribute, and each column has a
        disadvantages of structural and data                 distinct name.
        independence; and much easier to
                                                      4.     Each row/column intersection
        understand than the hierarchical and
                                                             represents a single data value.
        network models.
     Relational data models has greater logical      5.     All values in a column must conform
        simplicity which tends to yield a more               to the same data format. For example,
        efficient database.                                  if the attribute is assigned an integer
3.1.1 Tables & their Characteristics                         data format, all values in the column
     Table – a logical construct that has been              representing that attribute must be
        the basis for the creation of data                   integers.
        relationships which facilitates the logical
                                                      6.     Each column has a specific range of
        view of relational database
                                                             values known as the attribute domain.
     - perceived as a two-dimensional
        structure composed of rows & columns          7.     The order of the rows and columns is
TABLE                                                        immaterial to the DBMS.
     - contains a group of related entities/
        entity set                                    8.     Each table must have an attribute or a
     - also called relation because the                     combination of attributes that
        relation’s model creator E. F. Codd used             uniquely identifies each row.
        the term relation as a synonym for table      Sample Data Types
     - a persistent relation ( a relation whose      Data           Characteristic
        contents can be permanently saved for         Type
        future use)
     In MSAccess, relation is called dataset         Numeric        Are on which you can perform
        which is based on the mathematical                           meaningful arithmetic procedures
        theory derived from E. F. Codd’s model.
                                                      Character      Also known as string data or text
     Some database users incorrectly assume
                                                                     data, can contain any character
        that relation refers to relationships
                                                                     symbol not intended for
     Many incorrectly conclude that only
                                                                     mathematical manipulation.
        relational model permits the use of
        relationships.                                Date           Date attributes contain calendar
                                                                     dates stored in a special format

Viny :D                                                                                                Page 1
            known as the Julian date format                       An attribute whose values match
            which supports Julian date                             primary key values in the related
            arithmetic.                                            table
                                                           Referential integrity
Logical     Can have only a true or false (yes or                FK contains a value that refers to
            no) condition.                                         an existing valid tuple (row) in
    Domain – column’s range of permissible                        another relation
     values                                                Secondary key
    Primary key (PK) – an attribute (or a                       Key used strictly for data retrieval
     combination of attributes) that uniquely                      purposes
     identifies any given row.
SUMMARY                                                     Null Values
    Tables are the basic building blocks of a             No data entry
     relational database.                                  Not permitted in primary key
    A grouping of related entities, known as              Should be avoided in other attributes
     an entity set is stored in a table.                   Can represent
    The relational table is composed of                          An unknown attribute value
     intersecting rows (tuples) and columns.                      A known, but missing, attribute
    Each row represents a single-entity.                           value
Keys                                                              A “not applicable” condition
    Consists of one or more attributes that               Can create problems in logic and using
     determine other attributes                             formulas
    Primary key (PK) is an attribute (or a                Controlled RedundancyMakes the
     combination of attributes) that uniquely               relational database work
     identifies any given entity (row)                     Tables within the database share common
    Key’s role is based on determination                   attributes that enable us to link tables
          If you know the value of attribute               together
              A, you can look up (determine)               Multiple occurrences of values in a table
              the value of attribute B                      are not redundant when they are required
          This extends to the notion of a                  to make the relationship work
              mathematical “function” f(x).                Redundancy is unnecessary duplication of
    Composite key                                          data
          Composed of more than one
              attribute                             Types of Participation
    Key attribute                                      Mandatory
          Any attribute that is part of a key                  Table A’s participation is
    Superkey                                                       mandatory if you must enter at
          Any key that uniquely identifies                         least one record in Table A before
              each entity                                           you can enter values in Table B
    Candidate key                                      Optional
          A superkey without redundancies                      Table A’s participation is optional
    Foreign key (FK)                                               if you are not required enter at



Viny :D                                                                                        Page 2
                 least one record in Table A before      Intersect:
                 you can enter values in Table B                Yields only the rows that appear
Degree of Participation                                           in both tables
     Is the minimum and maximum number of               Difference
        records (entity instances) a Table must                 Yields all rows in one table not
        have associated with a single record in the               found in the other table—that is,
        related Table.                                            it subtracts one table from the
     Usually expressed as a pair of numbers                      other
        Min,Max example 1,10.                            Divide
Integrity                                                       Divide one table by the attributes
     Table Level Integrity = Entity Integrity                    of another
     Relationship Level Integrity = Referential                Seldom used
        Integrity                                        Select
     Field Level Integrity = Domain Integrity                  Yields values for all rows found in
             Ensures that every field is sound.                  a table
                 The values are valid, consistent,              Can be used to list either all row
                 and accurate                                     values or it can yield only those
Relational Algebra                                                row values that match a specified
     Codd’s contribution included the idea that                  criterion
        you could describe an “Algebra”, a                      Yields a horizontal subset of a
        consistent mathematical description of a                  table
        DBMS.                                            Project
     This is huge because if it is                             Yields all values for selected
        ‘mathematically consistent’ then when                     attributes
        you perform an operation you know that                  Yields a vertical subset of a table
        it must return the results you expect.           Join
Relational Database Operators                                   Allows us to combine information
     Relational algebra                                          from two or more tables
             Defines theoretical way of                        Real power behind the relational
                 manipulating table contents using                database, allowing the use of
                 relational operators:                            independent tables linked by
                       SELECT                                    common attributes
                       PROJECT                          Natural JoinLinks tables by selecting only
                       JOIN                              rows with common values in their
                       INTERSECT                         common attribute(s)
             Use of relational algebra                  Result of a three-stage process:
                 operators on existing tables                  1. PRODUCT of the tables is created
                 (relations) produces new relations            2. SELECT is performed on Step 1
     Union:                                                      output to yield only the rows for
             Combines all rows from two                          which the AGENT_CODE values
                 tables, excluding duplicate rows                 are equal
             Tables must have the same                                 Common column(s) are
                 attribute characteristics                                 called join column(s)

Viny :D                                                                                      Page 3
              3. PROJECT is performed on Step 2              In outer join for tables CUSTOMER and
                  results to yield a single copy of           AGENT, two scenarios are possible:
                  each attribute, thereby                          Left outer join
                  eliminating duplicate columns                             Yields all rows in
     Final outcome yields table that                                           CUSTOMER table,
              1. Does not include unmatched pairs                               including those that do
              2. Provides only copies of matches                                not have a matching value
     If no match is made between the table                                     in the AGENT table
         rows,                                                     Right outer join
              1. the new table does not include                             Yields all rows in AGENT
                  the unmatched row                                             table, including those that
     The column on which we made the JOIN—                                     do not have matching
         that is, AGENT_CODE—occurs only once in                                values in the CUSTOMER
         the new table                                                          table
     If the same AGENT_CODE were to occur            The Data Dictionary
         several times in the AGENT table,            and System Catalog
              1. a customer would be listed for            Data dictionary
                  each match                                       Used to provide detailed
This is IT                                                            accounting of all tables found
     This is what makes the relational database                      within the user/designer-created
         work in practical terms.                                     database
     You can use values from different but                        Contains (at least) all the attribute
         related tables work together to get the                      names and characteristics for
         results you need.                                            each table in the system
Other Forms of Join                                                Contains metadata—data about
     Equijoin                                                        data
               Links tables on the basis of an                    Sometimes described as “the
                  equality condition that compares                    database designer’s database”
                  specified columns of each table                     because it records the design
               Outcome does not eliminate                            decisions about tables and their
                  duplicate columns                                   structures
               Condition or criterion to join             System catalog
                  tables must be explicitly defined                Contains metadata
               Takes its name from the equality                   Detailed system data dictionary
                  comparison operator (=) used in                     that describes all objects within
                  the condition                                       the database
     Theta join                                                   Terms “system catalog” and “data
               If any other comparison operator                      dictionary” are often used
                  is used                                             interchangeably
Outer Join                                                         Can be queried just like any
     Matched pairs are retained and any                              user/designer-created table
         unmatched values in other table are left     Relationships within the
         null                                         Relational Database

Viny :D                                                                                             Page 4
       1:M relationship                                         Crucial to exercising data
             Relational modeling ideal                           redundancy control
             Should be the norm in any                 Sometimes, data redundancy is necessary
                 relational database design         Summary
     M:N relationships                                 Primary key uniquely identifies attributes
             Must be avoided because they                     Can link tables by using controlled
                 lead to data redundancies                        redundancy
     1:1 relationship                                  Relational databases classified according
             Should be rare in any relational            to degree to which they support relational
                 database design                          algebra functions
The 1:1 Relationship                                    Relationships between entities are
     Found in any database environment                   represented by entity relationship models
     One entity can be related to only one             Data retrieval speed can be increased
        other entity, and vice versa                      dramatically by using indexes
     Often means that entity components were
        not defined properly
     Could indicate that two entities actually
        belong in the same table
     Sometimes 1:1 relationships are
        appropriate
The M:N Relationship
     Can be implemented by breaking it up to
        produce a set of 1:M relationships
     Can avoid problems inherent to M:N
        relationship by creating a composite
        entity called a bridge or linking entity
Linking Table
     Implementation of a composite entity
     Yields required M:N to 1:M conversion
     Composite entity table must contain at
        least the primary keys of original tables
     Linking table contains multiple
        occurrences of the foreign key values
     Additional attributes may be assigned as
        needed
Data Redundancy Revisited
     Data redundancy leads to data anomalies
             Such anomalies can destroy
                 database effectiveness
     Foreign keys
             Control data redundancies by
                 using common attributes shared
                 by tables

Viny :D                                                                                      Page 5