Scribd Logo
Upload

Welcome to Scribd!

  • 1 views

    6753 Normalization

    Uploaded by

    bwumba23050

    Copyright:

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

    6753 Normalization

    Uploaded by

    bwumba23050
    1 views9 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

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

    6753 Normalization

    Uploaded by

    bwumba23050

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 9

    Database

    • Database is a collection of related Data. Like University, IRCTC etc.


    Database System

    Database DBMS

    SQL Server
    Structured Unstructured

    IRCTC Oracle
    Web Pages
    University My SQL
    DBMS (Data Base Management System)
    • Data – Data is the raw and unprocessed information about anything.
    student name, marks etc are data

    • Information – Information is a processed data which give some information to


    us about any thing.
    mark sheet is information.
    • Database - A database is an organized collection of related data, so that it can
    be easily accessed and managed.
    all students details stored in database.

    • DBMS – The system to manage a huge amount to data in an easily accessible


    form.
    Student database
    Normalization
    Normalization is a process of organizing data in such a way that it
    minimizing data redundancy from a relation or a set of relations. To
    minimize redundancy a large table is divides in multiple tables and
    establish relationship between them.
    There are six levels of normalization, in between them four levels are
    generally studied.

    1NF 2NF 3NF BCNF 5NF 6NF


    Important Keys
    • Super Keys- A super key in database management is a set of one or more attributes (columns) that can uniquely identify a record
    (row) in a table. A super key may contain additional attributes that are not necessary for unique identification. It is a broader concept
    than a candidate key, which is a minimal super key with no extraneous attributes. Super set of keys, which uniquely identify the tuple.
    eg. Id , Id +Name, Name + ph no, Name+ email, and many others
    • Candidate key- A candidate key in database management is a minimal set of attributes (columns) that can uniquely identify a
    record (row) in a table. Unlike a super key, a candidate key has no redundant attributes, meaning that if any attribute is
    removed, it will no longer uniquely identify the record. A table can have more than one candidate key, and one of these is
    typically chosen as the primary key. Unique key and minimal super key. Id, Name + Ph no, Email (no repetition of keys are there)

    • Primary Key- A primary key in database management is a specific candidate key selected to uniquely identify each record
    (row) in a table. The primary key ensures that no two records in the table can have the same value for this key, and it
    cannot contain NULL values. A table can have only one primary key, though it may consist of a single attribute or a
    combination of multiple attributes (composite key).Unique key and not null, it denote from Candidate key. Id is decided by DBA.
    • Alternate key- An alternate key in database management is any candidate key that is not selected as the primary
    key. Since a table can have multiple candidate keys, only one is chosen as the primary key, and the remaining
    candidate keys are referred to as alternate keys. These keys can still uniquely identify records in the table, but they
    are not used as the main identifier. The candidate keys other than Pk.
    • Composite key –A composite key in database management is a primary or candidate key that consists of two or
    more attributes (columns) that, together, uniquely identify a record (row) in a table. None of the individual
    attributes can uniquely identify the record by itself; only the combination of these attributes can ensure
    uniqueness. Which key is compressed of more than one key. Name + Ph no. , Name + email

    • Foreign key- A foreign key in database management is a column or set of columns in one table that
    establishes a link between data in two tables. It refers to the primary key or a unique key in another
    table, ensuring referential integrity. The foreign key creates a relationship between the two tables,
    enforcing that the value in the foreign key column(s) must match a value in the referenced table's
    primary key column(s), or it can be NULL if allowed. The referring key which is Pk of another table
    1NF (First Normal Form)
    • A relation is in 1NF if it contains an atomic value.
    2NF (Second Normal Form)
    • A relation will be in 2NF if it is in 1NF.
    • All non prime attributes are fully functional dependent on the
    candidate key (no partial dependency).
    • LHS is subset of CK and RHS is non-Prime attribute
    Student Id Name Age Sport Id Sport Student Id Name Age Sport Id
    1 A 16 101 Football 1 A 16 101
    2 B 14 102 Cricket 2 B 14 102
    3 C 15 101 Football 3 C 15 101

    Sport Id Sport
    101 Football
    102 Cricket
    3NF (Third Normal Form)
    • A relation will be in 3NF if it is in 2NF.
    • No transition dependency exists (non prime attribute will not
    determine by non prime attribute).
    • LHS will be SK or RHS will be prime attribute
    Student Id Name Marks Exam Name Total Marks Student Id Name marks Exam Id
    1 A 16 E_1
    1 A 16 Internal 20
    2 B 68 E_4
    2 B 68 Final 80
    3 C 19 E_1
    3 C 18 Internal 20
    Exam Id Exam Name Total Marks
    E_1 Internal 20
    E_4 final 80
    BCNF (Boyce Code Normal Form)
    • A relation will be in BCNF if it is in 3NF.
    • In every functional dependency Left-Hand Side (LHS) should be super
    key or candidate key of that particular table.
    Emp Id Emp Location Dept Designation Dept Id
    158 WB CSE Assistant Professor 012
    158 WB IT Professor 011
    235 Delhi AIML Assistant Professor 014
    235 Delhi MCA Associate Professor 015

    Dept Id Dept Designation Emp Id Dept Id


    Emp Id Emp Location 012 CSE Assistant Professor 158 012
    158 WB 011 IT Professor 158 011
    235 Delhi 014 AIML Assistant Professor 235 014
    015 MCA Associate Professor 235 015
    Advantage of Normalization
    • Reduced data redundancy: Normalization helps to eliminate duplicate data in
    tables. Which reduce the amount of storage space needed it improves data
    efficiency.
    • Improved data consistency: Normalization ensures that data is stored in a
    consistent and organized manner which reduce the risk of data inconsistency and
    errors.
    • Simplified database design: Normalization provides guidelines for organizing
    tables and data relationships, making it easier to design and maintain a database.
    • Improved query performance: Normalized tables are typically easier to search
    and retrieve data from, resulting in faster query performance.
    • Easier database maintenance: Normalization reduces the complexity of a
    database by breaking it down into smaller, more manageable tables, making it
    easier to add, modify, and delete data.
    Overall, using normal forms in DBMS helps to improve data quality, increase
    database efficiency, and simplify database design and maintenance.

    You might also like