Scribd Logo
Upload

Welcome to Scribd!

  • 2 views

    DBMS Chapter3

    Uploaded by

    nainularab999
    chapter 3 notes

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    DBMS Chapter3

    Uploaded by

    nainularab999
    2 views9 pages
    chapter 3 notes

    Original Title

    DBMS-Chapter3

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    chapter 3 notes

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    2 views9 pages

    DBMS Chapter3

    Uploaded by

    nainularab999
    chapter 3 notes

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    You are on page 1of 9

    The Relational Database Models

    Relational database model enables logical representation of the data


    and its relationships.
     Logical simplicity yields simple and effective database design
    methodologies.
     The logical view is facilitated by the creation of data
    relationships based on a logical construct called a relation

    Attribute Domain: Are the values that can be used to specify each
    tuples values for that specific column. Also known as an “domain.” It
    is the set of allowable values for an attribute.
    In data modeling, the construct used to organize and describe an
    attribute’s set of possible values.
    Eg: Student GPA can range from 1-4

    Determination
    ⁃ State in which knowing the value of one attribute makes it
    possible to determine the value of another
    ⁃ Establishes the role of a key
    ⁃ Based on the relationships among the attributes
    Functional dependence: value of one or more attributes determines
    the value of one or more other attributes. A -> B
     Determinant: attribute whose value determines another
     Dependent: attribute whose value is determined by the other
    attribute

    Full functional dependence: entire collection of attributes in the


    determinant is necessary for the relationship.
    A condition in which an attribute is functionally dependent on a
    composite key but not on any subset of the key.

    Keys consist of one or more attributes that determine other attributes


    ⁃ Ensure that each row in a table is uniquely identifiable
    ⁃ Establish relationships among tables and to ensure the integrity
    of the data

    Primary key (PK): attribute or combination of attributes that


    uniquely identifies a row. Selected candidate key.
    In the relational model, an identifier composed of one or more
    attributes that uniquely identifies a row. Also, a candidate key selected
    as a unique entity identifier. See also key.

    Composite key: key that is composed of more than one attribute.

    Key attribute: attribute that is a part of a primary key

    Superkey: key that can uniquely identify any row in the table
    Note: Not all keys are superkeys (e.g., STU _HRS → STU_CLASS)

    Candidate key: minimal superkey. that is, a key that does not contain
    a subset of attributes that is itself a superkey. See key. Dropping one
    or more attributes from the candidate key should retain it as a
    superkey.

    Several different types of keys are used in the relational model


    1. Entity integrity: condition in which each row in the table has
    its own unique identity
    ⁃ All of the values in the primary key must be unique
    ⁃ No key attribute in the primary key can contain a null

    2. Null: absence of any data value. Note that a null is not a blank.
    ⁃ Unknown attribute value, known but missing attribute value, or
    inapplicable condition

    3. Referential integrity: every reference to an entity instance by


    another entity instance is valid.
    4. Foreign key: primary key of one table that has been placed into
    another table to create a common attribute. The values in the
    foreign key must be constrained to ensure referential integrity.
    See key.

    5. Secondary key: key used strictly for data retrieval purposes that
    does not require a functional dependency.

    Relational database integrity rules are very important to good


    database design

     Relational database management systems (RDBMSs) enforce


    integrity rules automatically
    ⁃ Much safer to make sure the application design conforms to
    entity and referential integrity rules

    Integrity Rules:

    Ways to handle nulls

     Flags - Special codes used to indicate the absence of some value


     Constraints
    ⁃ NOT NULL constraint: placed on a column to ensure that
    every row in the table has a value for that column
    ⁃ UNIQUE constraint: restriction placed on a column to ensure
    that no duplicate values exist for that column
    ⁃ Every primary key has a NOT NULL and Unique constraint by
    default.

    Operations In Relational Table

     Relvar: variable that holds a relation.


    ⁃ Heading contains the names of the attributes
    ⁃ Body contains the relation
     Relational operators have the property of closure
    ⁃ Closure: use of relational algebra operators on existing
    relations produces new relations

    Relational Set Operators

    1. SELECT (RESTRICT) - Unary operator that yields a


    horizontal subset of a table.

    2. PROJECT - Unary operator that yields a vertical subset of a


    table

    3. Union - Combines all rows from two tables, excluding duplicate


    rows. Union-compatible: Tables share the same number of
    columns, and their corresponding columns share compatible
    domains.
    ⁃ It is rather unusual to find two relations that are union-
    compatible.
    • Typically, PROJECT operators are applied to relations to
    produce results that are union-compatible.

    4. INTERSECT - Yields only the rows that appear in both tables.


    Tables must be union-compatible to yield valid results.

    5. Difference - Yields all rows in one table that are not found in
    the other table. Tables must be union-compatible to yield valid
    results.
    AuB=BuA
    A intersection B = B intersection A
    But A – B Not equal to B -A

    6. Product - Yields all possible pairs of rows from two tables.

    7. Joins - allow information to be intelligently combined from two


    or more tables. Union Compatibility is not needed for joins.
     Inner join: Only returns matched records from the tables that
    are being joined.
    ⁃ Natural join (or simply called "JOIN"): links tables by
    selecting only the rows with common values in their
    common attribute.
    o Join columns: Common columns.
    o It has three steps Product, SELECT,
    Project(duplicate column is removed).
    o It Eliminates duplicate columns.

    ⁃ Equijoin: links tables on the basis of an equality condition


    that compares specified columns of each table.
    o Condition or criterion used to join the tables
    must be specified explicitly
    o Does not eliminate duplicate columns (i.e.,
    outcome is same as Step 2 of natural join)

    ⁃ Theta join: links tables using an inequality comparison


    operator.
    o Similar to equijoin but uses an operator other than
    the equality operator.

     Outer join: matched pairs are retained and unmatched values in


    the other table are left null
    ⁃ Think of an outer join as an "inner join plus".
    ⁃ Left outer join: yields all of the rows in the first table,
    including those that do not have a matching value in the
    second table.
    ⁃ Right outer join: yields all of the rows in the second
    table, including those that do not have matching values in
    the first table.

     Divide : Uses one double-column table as the dividend and one


    single-column tableas the divisor.
    ⁃ Output is a single column that contains all values from the
    second column of the dividend that are associated with
    every row in the divisor.

    Data Dictionary
    ⁃ Description of all tables in the database created by the user
    and designer
    ⁃ Contains at least all of the attribute names and
    characteristics for each table.
    System catalog - System data dictionary that describes all objects
    within the database, including data about table names, table's creator
    and creation date, number of columns in each table, data type
    corresponding to each column, index filenames, index creators,
    authorized users, and access privileges.

    Homonyms and synonyms must be avoided to lessen confusion


    ⁃ Homonym: same name is used to label different attributes (e.g.,
    name)
    ⁃ Synonym: different names are used to describe the same
    attribute (e.g., car and auto)

     One-to-manv (1:M) - Norm for relational databases.


     One-to-one (1:1) - Une entity can be related to only one other
    entity and vice versa
     Many-to-many (M:N) - implemented by creating a new entity in
    1:M relationships with the original entities
    ⁃ Composite entity (i.e., bridge or associative entity): helps
    avoid problems inherent to M:N relationships. Includes the
    primary keys of tables to be linked

    The relational database facilitates control of data redundancies


    through use of foreign keys.

    To be controlled except the following circumstances:


    ⁃ Sometimes data redundancy must be increased to make the
    database serve crucial information purposes
    ⁃ Sometimes data redundancy exists to preserve the historical
    accuracy of data.

    Indexes : Orderly arrangement to logically access rows in a table.


    ⁃ Index key: index's reference point that leads to data location
    identified by the key
    ⁃ Unique index: index key can have only one pointer value
    associated with it
    Each index is associated with only one table. The index key can have
    multiple attributes.

    Predicate logic - Used extensively in mathematics to provide a


    framework in which an assertion (statement of fact) can be verified as
    either true or false.

    Set theory - A part of mathematical science that deals with sets, or


    groups of things, and is used as the basis for data manipulation in the
    relational model.

    bridge 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 or associative entity. See also linking
    table.
    associative 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 or associative entity. See
    also linking table.

    linking table - In the relational model, a table that implements an M:N


    relationship.

    You might also like