DBMS

                                           -:Normal forms in DBMS :-

Edgar F. Codd  originally defined the first three normal forms (1NF, 2NF, and 3NF). These normal forms have been summarized as requiring that all non-key attributes be dependent on "the key, the whole key and nothing but the key". The fourth and fifth normal forms (4NF and 5NF) deal specifically with the representation of many-to-many and one-to-many relationships among attributes. Sixth normal form (6NF) incorporates considerations relevant to temporal databases.

1NF:-"values in the domains on which each relation is defined are required to be atomic with respect to the DBMS....

table is in 1NF if and only if it is "isomorphic to some relation", which means, specifically, that it satisfies the following five conditions:

        1. There's no top-to-bottom ordering to the rows.

        2. There's no left-to-right ordering to the columns.

        3. There are no duplicate rows.

        4. Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).

        5. All columns are regular [i.e. rows have no hidden components such as row IDs, object IDs, or hidden timestamps].

Examples of tables (or views) that would not meet this definition of 1NF are:

    * A table that lacks a unique key. Such a table would be able to accommodate duplicate rows, in violation of condition 3.

    * A view whose definition mandates that results be returned in a particular order, so that the row-ordering is an intrinsic and meaningful aspect of the view.[5] This violates condition 1. The tuples in true relations are not ordered.

    * A table with at least one nullable attribute. A nullable attribute would be in violation of condition 4, which requires every field to contain exactly one value from its column's domain. It should be noted, however, that this aspect of condition 4 is controversial. It marks an important departure from Codd's original vision of the relational model, which made explicit provision for nulls.

2NF:-

A table that is in first normal form (1NF) must meet additional criteria if it is to qualify for second normal form. Specifically: a 1NF table is in 2NF if and only if, given any candidate key and any attribute that is not a constituent of a candidate key, the non-key attribute depends upon the whole of the candidate key rather than just a part of it.

In slightly more formal terms: a 1NF table is in 2NF if and only if none of its non-prime attributes are functionally dependent on a part (proper subset) of a candidate key. (A non-prime attribute is one that does not belong to any candidate key.)

Note that when a 1NF table has no composite candidate keys (candidate keys consisting of more than one attribute), the table is automatically in 2NF.

A table for which there are no partial functional dependencies on the primary key is typically, but not always, in 2NF. In addition to the primary key, the table may contain other candidate keys; it is necessary to establish that no non-prime attributes have part-key dependencies on any of these candidate keys.

Multiple candidate keys occur in the following table:

3NF:-

a table is in 3NF if and only if both of the following conditions hold:

    * The table is in second normal form (2NF)

    * No non-prime attribute of the table is transitively dependent on a candidate key

A non-prime attribute is an attribute that does not belong to any candidate key. A transitive dependency is a functional dependency X → Z in which Z is not immediately dependent on X, but rather on a third set of attributes Y which depends on X. That is, X → Z by virtue of X → Y and Y → Z.

a table is in 3NF if and only if, for each of its functional dependencies X → A, at least one of the following conditions holds:

    * X contains A, or

    * X is a superkey, or

    * A is a prime attribute (i.e., A is contained within a candidate key)

giving a clear sense of the difference between 3NF and the more stringent Boyce-Codd normal form (BCNF). BCNF simply eliminates the third alternative ("A is a prime attribute").

BCNF:-

A table is in Boyce-Codd normal form if and only if, for every one of its non-trivial functional dependencies X → Y, X is a superkey—that is, X is either a candidate key or a superset thereof.

2NF prohibits partial functional dependencies of non-prime attributes on candidate keys, and that 3NF prohibits transitive functional dependencies of non-prime attributes on candidate keys. Since the table above lacks any non-prime attributes, it adheres to both 2NF and 3NF.

BCNF is more stringent than 3NF in that it does not permit any functional dependency in which the determining set of attributes is not a candidate key (or superset thereof).

4NF:-

4NF ensures that independent multivalued facts are correctly and efficiently represented in a database design.

The definition of 4NF relies on the notion of a multivalued dependency. A table with a multivalued dependency is one where the existence of more than one independent many-to-many relationships in a table causes redundancy; and it is this redundancy which is removed by fourth normal form.

5NF:-

Fifth normal form (5NF), also known as Project-join normal form (PJ/NF) is a level of database normalisation, designed to reduce redundancy in relational databases recording multi-valued facts by isolating semantically related multiple relationships. A table is said to be in the 5NF if and only if it is in 4NF and every join dependency in it is implied by the candidate keys.

A multivalued dependency is a full constraint between two sets of attributes in a relation.

In contrast to the functional dependency, the multivalued dependency requires that certain tuples be present in a relation. Therefore, a multivalued dependency is a special case of tuple-generating dependency. The multivalued dependency plays a role in the 4NF database normalization.

Let R be a relation schema and let \alpha \subseteq R and \beta \subseteq R (subsets). The multivalued dependency

α Image: twoheadrightarrow.gif β

holds on R if, in any legal relation r(R), for all pairs of tuples t1 and t2 in r such that t1[α] = t2[α], there exist tuples t3 and t4 in r such that

t1[α] = t2[α] = t3[α] = t4[α]

t3[β] = t1[β]

t3[R − β] = t2[R − β]

t4[β] = t2[β]

t4[R − β] = t1[R − β][1]

Databases with multivalued dependencies thus exhibit redundancy. In database normalization, fourth normal form requires that databases have no multivalued dependencies.

    * If α Image: twoheadrightarrow.gif β, Then α Image: twoheadrightarrow.gif R − β

    * If α Image: twoheadrightarrow.gif β and \gamma \subseteq \delta, Then αδ Image: twoheadrightarrow.gif βγ

    * If α Image: twoheadrightarrow.gif β and If β Image: twoheadrightarrow.gif γ, then α Image: twoheadrightarrow.gif γ − β

The following also involve functional dependencies:

    * If \alpha \rightarrow \beta, then α Image: twoheadrightarrow.gif β

    * If α Image: twoheadrightarrow.gif β and \beta \rightarrow \gamma, then \alpha \rightarrow \gamma - \beta

The above rules are sound and complete.

    * A decomposition of R into (X, Y) and (X, R-Y) is a lossless-join decomposition if and only if X Image: twoheadrightarrow.gif Y holds in R.

full constraint

    A constraint which expresses something about all attributes in a database. (In contrary to an embedded constraint.) That a multivalued dependency is a full constraint follows from its definition, where it says something about the attributes R − β.

tuple-generating dependency

    A dependency which explicitly requires certain tuples to be present in the relation.

trivial multivalued dependency

    A multivalued dependency which involves all the attributes of a relation i.e.R = \alpha \cup \beta. A trivial multivalued dependency implies, for tuples t1 and t2, tuples t3 and t4 which are equal to t1 and t2.

Domain/key normal form (DKNF) is a normal form used in database normalization which requires that the database contains no constraints other than domain constraints and key constraints.

A domain constraint specifies the permissible values for a given attribute, while a key constraint specifies the attributes that uniquely identify a row in a given table.

he domain/key normal form is the Holy Grail of relational database design,[citation needed] achieved when every constraint on the relation is a logical consequence of the definition of keys and domains, and enforcing key and domain restraints and conditions causes all constraints to be met. Thus, it avoids all non-temporal anomalies.

Database constraints are constraints on the database that require relations to satisfy certain properties. Relations that satisfy all such constraints are legal relations.

Databases intended for Online Transaction Processing (OLTP) are typically more normalized than databases intended for Online Analytical Processing (OLAP). OLTP Applications are characterized by a high volume of small transactions such as updating a sales record at a super market checkout counter. The expectation is that each transaction will leave the database in a consistent state. By contrast, databases intended for OLAP operations are primarily "read mostly" databases. OLAP applications tend to extract historical data that has accumulated over a long period of time. For such databases, redundant or "denormalized" data may facilitate Business Intelligence applications. Specifically, dimensional tables in a star schema often contain denormalized data. The denormalized or redundant data must be carefully controlled during ETL processing, and users should not be permitted to see the data until it is in a consistent state. The normalized alternative to the star schema is the snowflake schema. It has never been proven that this denormalization itself provides any increase in performance, or if the concurrent removal of data constraints is what increases the performance. The need for denormalization has waned as computers and RDBMS software have become more powerful.

Denormalization is also used to improve performance on smaller computers as in computerized cash-registers and mobile devices, since these may use the data for look-up only (e.g. price lookups). Denormalization may also be used when no RDBMS exists for a platform (such as Palm), or no changes are to be made to the data and a swift response is crucial.

Non-first normal form (NF²)

In recognition that denormalization can be deliberate and (dubiously) useful, the non-first normal form is a definition of database designs which do not conform to the first normal form, by allowing "sets and sets of sets to be attribute domains" (Schek 1982). This extension is a (non-optimal) way of implementing hierarchies in relations. Some theoreticians have dubbed this practitioner developed method, "First Ab-normal Form", Codd defined a relational database as using relations, so any table not in 1NF could not be considered to be relational.

Data integrity in a relational database is concerned with accuracy, correctness, and validity of the data in a database.

A common variety of data integrity is referential integrity, which involves prevention of errors in Foreign Key to Primary Key relationships,

***********************************************************************************

DBMS

DBMS contains information about a particular enterprise

ü Collection of interrelated data

ü Set of programs to access the data

ü An environment that is both convenient and efficient to use

Drawbacks of using file systems to store data:

ü Data redundancy and inconsistency

Multiple file formats, duplication of information in different files

ü Difficulty in accessing data

            Need to write a new program to carry out each new task

ü Data isolation —multiple files and formats

ü Integrity problems

            Integrity constraints (e.g. account balance > 0) become “buried”in            program code rather than being stated explicitly

                Hard to add new constraints or change existing ones

ü Atomicity of updates

                        Failures may leave database in an inconsistent state with partial updates carried out

                        Example: Transfer of funds from one account to another should either complete or not happen at all

ü Concurrent access by multiple users

              Concurrent accessed needed for performance

              Uncontrolled concurrent accesses can lead to inconsistencies

              Example: Two people reading a balance and updating it at the same time

ü Security problems

******************************************************************************************************************************************

DBMS

DBMS contains information about a particular enterprise

ü Collection of interrelated data

ü Set of programs to access the data

ü An environment that is both convenient and efficient to use

Drawbacks of using file systems to store data:

ü Data redundancy and inconsistency

Multiple file formats, duplication of information in different files

ü Difficulty in accessing data

            Need to write a new program to carry out each new task

ü Data isolation —multiple files and formats

ü Integrity problems

            Integrity constraints (e.g. account balance > 0) become “buried”in            program code rather than being stated explicitly

                Hard to add new constraints or change existing ones

ü Atomicity of updates

                        Failures may leave database in an inconsistent state with partial updates carried out

                        Example: Transfer of funds from one account to another should either complete or not                            happen at all

ü Concurrent access by multiple users

              Concurrent accessed needed for performance

              Uncontrolled concurrent accesses can lead to inconsistencies

              Example: Two people reading a balance and updating it at the same time

ü Security problems