What is RDBMS
And Normalization
Relational Data
Base Management Systems (RDBMS) are
database management systems that maintain data records
and indices in tables. Relationships may be created and
maintained across and among the data and tables. In a
relational database, relationships between data items are
expressed by means of tables. Interdependencies among
these tables are expressed by data values rather than by
pointers. This allows a high degree of data independence. An
RDBMS has the capability to recombine the data items from
different files, providing powerful tools for data usage.
What are the properties of the relational tables?
Relational tables have the following five properties:
· Values are atomic.
· Column values are of the same kind.
· The sequence of columns is insignificant.
· The sequence of rows is insignificant.
· Each column must have a unique name.
database management systems that maintain data records
and indices in tables. Relationships may be created and
maintained across and among the data and tables. In a
relational database, relationships between data items are
expressed by means of tables. Interdependencies among
these tables are expressed by data values rather than by
pointers. This allows a high degree of data independence. An
RDBMS has the capability to recombine the data items from
different files, providing powerful tools for data usage.
What are the properties of the relational tables?
Relational tables have the following five properties:
· Values are atomic.
· Column values are of the same kind.
· The sequence of columns is insignificant.
· The sequence of rows is insignificant.
· Each column must have a unique name.
Database
Concepts with SQL SERVER
What is normalization?
Database normalization is a data design and organizational
process applied to data structures based on rules that help
build relational databases. In relational database design, the
process of organizing data to minimize redundancy is called
normalization. Normalization usually involves dividing
database data into different tables and defining relationships
between the tables. The objective is to isolate data so that
additions, deletions, and modifications of a field can be made
in just one table and then retrieved through the rest of the
database via the defined relationships.
The key traits for Normalization are eliminating redundant data and
ensuring data dependencies.
What is de-normalization?
De-normalization is the process of attempting to optimize the
performance of a database by adding redundant data. It is
sometimes necessary because current DBMSs implement the
relational model poorly. A true relational DBMS would allow
for a fully normalized database at the logical level, while
providing physical storage of data that is tuned for high
performance. De-normalization is a technique to move from
higher to lower normal forms of database modeling in order
to speed up database access.
De-normalizing the
database design allows for fewer joins with
tables and foreign key requirements. This method is commonly used
for Reporting and OLAP workloads.
tables and foreign key requirements. This method is commonly used
for Reporting and OLAP workloads.
How
is the ACID property related to databases?
ACID (an acronym for Atomicity Consistency Isolation
Durability) is a concept that database professionals generally
look for while evaluating relational databases and application
architectures. For a reliable database, all four of these
attributes should be achieved:
ACID (an acronym for Atomicity Consistency Isolation
Durability) is a concept that database professionals generally
look for while evaluating relational databases and application
architectures. For a reliable database, all four of these
attributes should be achieved:
Atomicity is an all-or-none rule for database
modifications.
Consistency guarantees that a transaction
never leaves your
database in a half-finished state.
database in a half-finished state.
Isolation keeps transactions separated
from each other until
they are finished.
they are finished.
Durability guarantees that the database
will keep track of
pending changes in such a way that the server can recover
from an abnormal termination and committed transactions
will not be lost.
pending changes in such a way that the server can recover
from an abnormal termination and committed transactions
will not be lost.
What
are the different normalization forms?
1NF: Eliminate repeating groups
Make a separate table for each set of related attributes and
give each table a primary key. Each field contains at most one
value from its attribute domain
2NF:
Eliminate redundant data
If an attribute depends on only part of a multi-valued key,
then remove it to a separate table.
3NF: Eliminate columns not dependent on the key
If attributes do not contribute to a description of the key, then
remove them to a separate table. All attributes must be
directly dependent on the primary key.
If an attribute depends on only part of a multi-valued key,
then remove it to a separate table.
3NF: Eliminate columns not dependent on the key
If attributes do not contribute to a description of the key, then
remove them to a separate table. All attributes must be
directly dependent on the primary key.
BCNF:
Boyce-Codd Normal Form
If there are non-trivial dependencies between candidate key
attributes, then separate them out into distinct tables.
4NF: Isolate independent multiple relationships
No table may contain two or more 1:n or n:m relationships
that are not directly related.
5NF: Isolate semantically related multiple relationships
There may be practical constraints on information that
justifies separating logically related many-to-many
relationships.
ONF: Optimal Normal Form
A model limited to only simple (elemental) facts, as expressed
in Object Role Model notation.
If there are non-trivial dependencies between candidate key
attributes, then separate them out into distinct tables.
4NF: Isolate independent multiple relationships
No table may contain two or more 1:n or n:m relationships
that are not directly related.
5NF: Isolate semantically related multiple relationships
There may be practical constraints on information that
justifies separating logically related many-to-many
relationships.
ONF: Optimal Normal Form
A model limited to only simple (elemental) facts, as expressed
in Object Role Model notation.
DKNF:
Domain-Key Normal Form
A model free from all modification anomalies are said to be in
DKNF.
Remember, these normalization guidelines are cumulative.
For a database to be in the 3NF, it must first fulfill all the
criteria of a 2NF and 1NF database.
A model free from all modification anomalies are said to be in
DKNF.
Remember, these normalization guidelines are cumulative.
For a database to be in the 3NF, it must first fulfill all the
criteria of a 2NF and 1NF database.
EmoticonEmoticon