Scribd Logo
Upload

Welcome to Scribd!

  • 29 views

    Normalization New 1

    Uploaded by

    Shivanshi Singh
    The document discusses database normalization and anomalies that can occur in unnormalized databases. It describes three types of anomalies - insertion, deletion, and update anomalies. Insertion anomalies occur when a new record cannot be added unless other unavailable data is also added. Deletion anomalies happen when deleting a record can remove other required data. Modification anomalies involve incorrectly changing data across multiple records. The document provides examples of each type of anomaly and discusses different types of functional dependencies like full, partial, transitive, multi-valued, and join dependencies. It also covers lossless and lossy decomposition of tables in the normalization process.

    Copyright:

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

    Normalization New 1

    Uploaded by

    Shivanshi Singh
    29 views60 pages
    The document discusses database normalization and anomalies that can occur in unnormalized databases. It describes three types of anomalies - insertion, deletion, and update anomalies. Insertion anomalies occur when a new record cannot be added unless other unavailable data is also added. Deletion anomalies happen when deleting a record can remove other required data. Modification anomalies involve incorrectly changing data across multiple records. The document provides examples of each type of anomaly and discusses different types of functional dependencies like full, partial, transitive, multi-valued, and join dependencies. It also covers lossless and lossy decomposition of tables in the normalization process.

    Copyright

    © © All Rights Reserved

    Available Formats

    PPTX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document discusses database normalization and anomalies that can occur in unnormalized databases. It describes three types of anomalies - insertion, deletion, and update anomalies. Insertion anomalies occur when a new record cannot be added unless other unavailable data is also added. Deletion anomalies happen when deleting a record can remove other required data. Modification anomalies involve incorrectly changing data across multiple records. The document provides examples of each type of anomaly and discusses different types of functional dependencies like full, partial, transitive, multi-valued, and join dependencies. It also covers lossless and lossy decomposition of tables in the normalization process.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    29 views60 pages

    Normalization New 1

    Uploaded by

    Shivanshi Singh
    The document discusses database normalization and anomalies that can occur in unnormalized databases. It describes three types of anomalies - insertion, deletion, and update anomalies. Insertion anomalies occur when a new record cannot be added unless other unavailable data is also added. Deletion anomalies happen when deleting a record can remove other required data. Modification anomalies involve incorrectly changing data across multiple records. The document provides examples of each type of anomaly and discusses different types of functional dependencies like full, partial, transitive, multi-valued, and join dependencies. It also covers lossless and lossy decomposition of tables in the normalization process.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    You are on page 1of 60

    Normalization

    Functional Dependencies
    Data base anomalies
    • Anomalies are problems that can occur in poorly planned, un-
    normalised databases where all the data is stored in one table (a flat-
    file database).
    • Types:

    • 1. Update
    • 2. Insert
    • 3 Delete anomalies
    Types
    • Insertion Anomaly - The nature of a database may be such that it is not
    possible to add a required piece of data unless another piece of
    unavailable data is also added. E.g. A library database that cannot store
    the details of a new member until that member has taken out a book.

    • Deletion Anomaly - A record of data can legitimately be deleted from a


    database, and the deletion can result in the deletion of the only
    instance of other, required data, E.g. Deleting a book loan from a library
    member can remove all details of the particular book from the database
    such as the author, book title etc.
    • Modification Anomaly - Incorrect data may have to be changed, which
    could involve many records having to be changed, leading to the
    possibility of some changes being made incorrectly.
    Empno Projno Ename Pname No_hours
    E1 P11 A Adv 11
    E2 P10 B Billing 12
    E6 P10 C Billing 15
    E3 P12 D Seals 20

    E5 P10 E Billing 10

    1.Update P10—Billing to accounting


    2. We can not insert new project without assigning employees
    3.Deletion P12 – losing detail of employee D
    Functional dependency
    • Full functional dependency
    • Partial functional dependency
    • Transitive functional dependency
    • Multi-valued functional dependency
    • Join functional dependency
    Functional dependency
    •A B
    • A-determinant
    • B-determined

    Part_name Cost
    Hard disk 1500
    Pen drive 700
    Hard disk 1500
    CD 10
    Pen drive 700
    Full functional dependency
    • An attribute is FFD on a set of attributes if
    • It is functionally dependent on S and
    • Not functionally dependent on any proper subset of S.
    Roll_n NAme Course_id course_title Grade
    um

    1 Raj CSE301 DBMS A

    1 Raj CSE306 NW C

    2 Ankur CSE301 DBMS B

    2 Ankur CSE306 NW A

    3 Arun CSE316 SOFT ENGG C

    roll_num ,course_id Grade


    Name and course_title are not fully functional dependent on composite key
    Partial dependency
    • The value of one attribute is dependent on another attribute of
    relation which is a part of composite key.

    • Name is partially dependent on roll number.


    Transitive functional dependency
    Dept_id Dept_name Hod_name

    1 CSE Mr X

    2 IT Mr Y

    3 ECE Mr Z

    4 ME Mr A

    Dept_id Dept_name Hod_name

    A B C
    Multi-valued functional dependency
    Name Ph_number
    Ram 987217701
    Sham 982271661
    Ram 876622134
    Rajesh 872213477
    Raj 657932721
    Ajay 873539262

    A B

    Name Ph_number
    Decomposition of tables
    • Lossy decomposition
    • Lossless decomposition
    Consider following table

    Model Price Make

    N12 10000 CANON

    P20 12000 NIKON

    A73 15000 CANON

    This can be further divided as following

    Model Make price make


    N12 CANON 10000 CANON
    P20 NIKON 12000 NIKON
    A73 CANON 15000 CANON
    Now try to re-create original table

    model price make

    N12 10000 CANON

    N12 15000 CANON

    P20 12000 NIKON

    A73 10000 CANON

    A73 15000 CANON


    Properties of Decomposition
    • Following are the properties of Decomposition,
    1. Lossless Decomposition
    2. Dependency Preservation
    3. Lack of Data Redundancy
    Functional dependency diagram
    What kind of dependencies can we observe among the attributes in Table R? Since the values of A
    are unique (a1, a2, a3, etc.)??????
    it follows from the FD definition that:

    A → B, A → C, A → D, A → E

    It also follows that A →BC (or any other subset of ABCDE).


    This can be summarized as A →BCDE.
    From our understanding of primary keys, A is a primary key.
    Since the values of E are always the same (all e1), it follows that:

    A → E, B → E, C → E, D → E

    However, we cannot generally summarize the above with ABCD → E because, in general, A → E, B → E, AB → E.
    Dependency Diagram
    A dependency diagram, shown in Figure 11.6, illustrates the various dependencies that might exist in a non-
    normalized table. A non-normalized table is one that has data redundancy in it.
    This table has a composite primary key [Customer ID, Store ID].
    The non-key attribute is [Purchase Location]. In this case,
    [Purchase Location] only depends on [Store ID], which is only
    part of the primary key. Therefore, this table does not satisfy
    second normal form.

    What we have done is to remove the partial functional


    dependency that we initially had. Now, in the table
    [TABLE_STORE], the column [Purchase Location] is fully
    dependent on the primary key of that table, which is [Store ID].
    By transitive functional dependency, we mean we have the
    following relationships in the table: A is functionally dependent on
    B, and B is functionally dependent on C. In this case, C is
    transitively dependent on A via B.
    In the table able, [Book ID] determines [Genre ID], and [Genre
    ID] determines [Genre Type]. Therefore, [Book ID] determines
    [Genre Type] via [Genre ID] and we have transitive functional
    dependency, and this structure does not satisfy third normal
    form.

    Now all non-key attributes are fully functional


    dependent only on the primary key. In [TABLE_BOOK],
    both [Genre ID] and [Price] are only dependent on
    [Book ID]. In [TABLE_GENRE], [Genre Type] is only
    dependent on [Genre ID].
    Draw the dependency diagram for this table.
    An agency called Instant Cover supplies part-time/temporary staff to hotels in Scotland. Figure 12.4 lists the
    time spent by agency staff working at various hotels. The national insurance number (NIN) is unique for every
    member of staff. Use Figure 12.4 to answer questions (a) and (b).

    A. This table is susceptible to update anomalies. Provide examples of insertion, deletion and update
    anomalies.
    B. Normalize this table to third normal form. State any assumptions.
    4th Normal Form
    • It is in BCNF
    • There is no multi value dependency in relation
    Emp-id Language skill
    101 English Teaching
    101 Hindi Conversation
    101 English Conversation
    101 hindi Teaching
    202 English Singing
    202 Hindi Teaching

    Multivalued dependencies exist

    Emp-id  language
    Emp-id  skill

    Anomalies
    Delete—if id 101 discontinues teaching skill … then two rows to be delete
    Update– if id 101 change its skill teaching to singing … then number of changes to
    be done.
    This table could be divided in following two tables:

    Emp-id Language
    101 English
    101 Hindi
    202 English
    202 hindi

    Emp-id skills
    101 Teaching
    101 Conversation
    202 Singing
    202 Teaching
    5th Normal Form
    • A relation R is in Fifth Normal Form (5NF) if and only if the following conditions
    are satisfied simultaneously: 
    • 1.         R is already in 4NF.
    • 2.         It cannot be further non-loss decomposed.
    Consider Following data base:

    Now this data base is decomposed into following two tables:


    Now try to Re-Join these tables to re-create original table;;:::??????

    Now try to find out anomalies it has now?


    Over all picture of
    Normalization
    process:

    transitive
    dependencies
    E.F. Codd’s 12 Rules for RDBMS

    Dr E.F.Codd, also known to the world as the ‘Father of Database Management


    Systems’ had propounded 12 rules which are in-fact 13 in number.

    The rules are numbered from zero to twelve.

    According to him, a DBMS is fully relational if it abides by all his twelve rules.

    Till now, only few databases abide by all the eleven rules.
    To solve this, we normalize tables. We have broken the above table into 2 tables,
    repeating columns we have moved into a separate table

    You might also like