Scribd Logo
Upload

Welcome to Scribd!

  • 1K views

    nORMALIZATION EXTRANOTES

    Uploaded by

    Mohamed Luffycero Ahmed
    The document discusses normalization of database tables through decomposition. It provides examples of different types of dependencies and how to transform tables to higher normal forms by removing dependencies. The key steps are identifying dependencies, creating separate tables for attributes involved in partial and transitive dependencies, and establishing foreign key relationships between the new tables to preserve data integrity. Normalization helps reduce data redundancy and anomalies.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd

    nORMALIZATION EXTRANOTES

    Uploaded by

    Mohamed Luffycero Ahmed
    1K views5 pages
    The document discusses normalization of database tables through decomposition. It provides examples of different types of dependencies and how to transform tables to higher normal forms by removing dependencies. The key steps are identifying dependencies, creating separate tables for attributes involved in partial and transitive dependencies, and establishing foreign key relationships between the new tables to preserve data integrity. Normalization helps reduce data redundancy and anomalies.

    Original Title

    nORMALIZATION-EXTRANOTES

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    DOC, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document discusses normalization of database tables through decomposition. It provides examples of different types of dependencies and how to transform tables to higher normal forms by removing dependencies. The key steps are identifying dependencies, creating separate tables for attributes involved in partial and transitive dependencies, and establishing foreign key relationships between the new tables to preserve data integrity. Normalization helps reduce data redundancy and anomalies.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    1K views5 pages

    nORMALIZATION EXTRANOTES

    Uploaded by

    Mohamed Luffycero Ahmed
    The document discusses normalization of database tables through decomposition. It provides examples of different types of dependencies and how to transform tables to higher normal forms by removing dependencies. The key steps are identifying dependencies, creating separate tables for attributes involved in partial and transitive dependencies, and establishing foreign key relationships between the new tables to preserve data integrity. Normalization helps reduce data redundancy and anomalies.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    of 5
    At a glance
    Powered by AI
    The text discusses normalization of databases and different types of dependencies including partial, transitive, and functional dependencies.

    Partial dependencies occur when an attribute depends on only part of the primary key. Transitive dependencies occur when an attribute depends on another attribute that is not part of the primary key. Functional dependencies occur when attributes depend on the entire primary key.

    To remove a transitive dependency, the attributes that create the dependency must be placed in a separate table where the primary key attribute of the new table is a foreign key in the original table.

    NORMALIZATION

    TUTORIAL (EXTRA)
    1. Given the dependency diagram, answer items a-c:

    C
    1 C
    2 C
    3 C
    4 C
    5

    a. Identify and discuss each of the indicated dependencies.

    C1 → C2 represents a partial dependency, because C2 depends only on C1, rather


    than on the entire primary key composed of C1 and C3.

    C4 → C5 represents a transitive dependency, because C5 depends on an attribute


    (C4) that is not part of a primary key.

    C1, C3 → C2, C4, C5 represents a set of proper functional dependencies, because


    C2, C4, and C5 depend on the primary key composed of C1 and C3.

    b. Create a database whose tables are at least in 2NF, showing the


    dependency diagrams for each table.

    Table 1

    Primary key: C1
    C1 C2 Foreign key: None
    Normal form: 3NF

    Table 2

    C1 C3 C4 C5 Primary key: C1 + C3
    Foreign key: C1 (to Table 1)
    Normal form: 2NF, because the
    table exhibits the transitive
    dependencies C4 C5
    c. Create a database whose tables are at least in 3NF, showing the
    dependency diagrams for each table.

    Table 1
    Primary key: C1
    C1 C2 Foreign key: None
    Normal form: 3NF

    Table 2
    Primary key: C1 + C3
    C1 C3 C4 Foreign key: C1 (to Table 1)
    C4 (to Table 3)
    Normal form: 3NF

    Table 3
    Primary key: C4
    C4 C5 Foreign key: None
    Normal form: 3NF

    Transitive dependency is a condition in which an attribute is


    dependent on another attribute that is not part of the primary key.
    This kind of dependency usually requires the decomposition of the
    table containing the transitive dependency.

    To remove a transitive dependency, the designer must perform the


    following actions:
    • Place the attributes that create the transitive dependency in a separate table.
    • Make sure that the new table's primary key attribute is the foreign key in the
    original table.

    Figure Q5.9 shows an example of a transitive dependency removal.

    Figure Q5.9 Transitive Dependency Removal


    Original table

    INV_NUM INV_DATE INV_AMOUNT CUS_NUM CUS_ADDRESS CUS_PHONE

    Transitive Dependencies

    INV_NUM INV_DATE INV_AMOUNT CUS_NUM

    New Tables

    CUS_NUM CUS_ADDRESS CUS_PHONE

    1. Using the INVOICE table structure shown in Table P5.1,


    write the relational schema, draw its dependency diagram and identify all
    dependencies (including all partial and transitive dependencies). You can
    assume that the table does not contain repeating groups and that any invoice
    number may reference more than one product. (Hint: This table uses a
    composite primary key.)

    2. Using the initial dependency diagram drawn in Problem 1,


    remove all partial dependencies, draw the new dependency diagrams, and
    identify the normal forms for each table structure you created.

    Table P5.1 Sample INVOICE Records


    Attribute Name Sample Value Sample Value Sample Value Sample Value Sample Value
    INV_NUM 211347 211347 211347 211348 211349
    PROD_NUM AA-E3422QW QD-300932X RU-995748G AA-E3422QW GH-778345P
    SALE_DATE 15-Jan-2006 15-Jan-2006 15-Jan-2006 15-Jan-2006 16-Jan-2006
    PROD_LABEL Rotary sander 0.25-in. drill Band saw Rotary sander Power drill
    bit
    VEND_CODE 211 211 309 211 157
    VEND_NAME NeverFail, Inc. NeverFail, Inc. BeGood, Inc. NeverFail, Inc. ToughGo, Inc.
    QUANT_SOLD 1 8 1 2 1
    PROD_PRICE $49.95 $3.45 $39.99 $49.95 $87.75
    Answer:

    NOTE
    You can assume that any given product is supplied by a single vendor but a vendor can
    supply many products. Therefore, it is proper to conclude that the following dependency
    exists:
    PROD_NUM → PROD_DESCRIPTION, PROD_PRICE, VEND_CODE, VEND_NAME
    (Hint: Your actions should produce three dependency diagrams.)

    Figure P5.1&2 The Dependency Diagrams for


    Problems 1 and 2

    INV_NUM PROD_NUM SALE_DATE

    Partial dependency

    Relational schema: 1NF(INV_NUM


    VEND_NA

    You might also like