SQL Server

Relational Database Management System (RDBMS) and Normalization Forms – Part – 1

Relational Database Management System (RDBMS) and Normalization Forms – Part – 1

Introduction

At the time of planning a database, an individual needs to take few decisions concerning about how to take some real world systems and fit in the model of the database. This can be done by determining which tables or relations to form, which attributes or columns it will have, in addition to which type of associations are there among the tables or relations. However it would be good if this method was completely instinctive as well as understandable, otherwise even better if it was automatic, but then again this is just not the situation. An elegant database design takes time as well as effort to look on, shape and to refining the design.

There are numerous advantages of a database which has been designed accordingly with the relational model, a number of them are stated below:

Inserting, modifying or removing of information will be effective.

Recovery, summarization as well as reporting of information will also be effective.

As the database follows a well framed model, it performance is predictably.

As many of the data is kept in the database compare to the application level data, the database is to some extent itself is self-documenting.

Modifications to the database schema are stress-free to create.

The objective of this article is to clarify the rudimentary ethics behind the relational database design as well as to exhibit by what manner to put on these ethics at the time of designing a database.

Familiar Designing Rules for Relational Schemas

Here, four (4) familiar methods of designing for relation schemas are discussed in this following section:

Semantics of the columns or attributes.

Dropping the repeated data in the rows or tuples.

Dropping the null data in rows or tuples.

Prohibiting the likelihood of creating false tuples or rows.

These above mentioned actions are not at all times independent of one another.

Semantics of the Columns or Attributes

The semantics, stipulates in what manner to understand the column or attribute data which are kept in a row or tuple of the relation or table, in another words, in what way the attribute or column data is related in the tuples or rows with one another. If the theoretical plan is completed wisely, followed by a systematic planning into relations or tables, then maximum of the semantics will be responsible for the consequential design as well as it will have a perfect meaning.

Designing a relation schema so that it will be stress-free to understand its meaning. An individual should not join columns or attributes from numerous entity types as well as association types into a sole relation or table. Instinctively, if a relation schema agrees to one entity type or one relation / table.

Dropping the Repeated Data in Rows or Tuples

Storing the identical data superfluously, that is, keeping a same data in more than one (1) place in a database, which as a consequence can lead to numerous difficulties:

· Repeated Storage: A number of data is kept repeatedly.

· Update Irregularities: When one (1) occurrence of such repeated information is modified, an inconsistency is made if not all the occurrences are likewise updated.

· Insertion Irregularities: It might not be probable to stock certain data if not some other particular, unconnected, data is kept well before.

· Deletion Irregularities: It might not be probable to erase certain data without misplacing a number of other, unconnected, data well before.

Plan the base relation schemas so that no insertion, removal, or alteration irregularities are present in the relations or tables. If any irregularities are existing then an individual need to take a note of them unmistakably as well as need to make confirm that the application programs which modify the database will work appropriately.

Dropping the Null Data in Rows or Tuples

It is valuable to consider if the usage of NULL values can take care of a number of these difficulties. As it can be seen in the below mention instances, that the NULL values cannot deliver a comprehensive way out, on the other hand it can make available of more than few assistances. In this full series of article, the use of NULL value will not be discussed further than this single instance. Think through the instance of table or relation Doc_Pay. Obviously, NULL values cannot assist in removing repetitive storing or else update irregularities. It seems that NULL values can address insertion as well as deletion irregularities. For an example, to take care of the insertion irregularity instance, an individual can add a doctor data in the row or tuple with NULL values in the hourly rate attribute or column. But, NULL values cannot take care of every insertion irregularities. For an instance, an individual cannot record the weekly total of a doctor without the hourly rate, daily hours and number of days in the week the doctor visits, for the reason that an individual cannot store a NULL value in the Weekly_Total column or attribute, which is an important, attribute or column for the table or relation. In the same way, to deal with the deletion irregularity example, an individual may think through an instance where keeping a tuple or row with NULL values in every field excluding the Weekly_Total column or attribute. On the other hand, this way out does not works as it necessitates the hourly rate, daily hours and number of days in the week the doctor visits details for the calculation because the Weekly_Total is a computed column or attribute. Thus, NULL values do not offer an overall clarification to the difficulties of redundancy, despite the fact they can assist in a number of circumstances.

Doc_ID

Name

Hourly_Rate

Daily_Hours

Days_in_Week

Weekly_Total

D001

ABC

$ 15

5

7

$ 525

D002

XYZ

$ 09

2

5

$ 90

———

———

———

———

———

———

D0123

PQR

$ 11

3

5

$ 165

Table – Doc_Pay

In the upcoming part we will be discussing about Decompositions, how to Cancel the Prospect of Producing False Tuples / Rows, what are Practical Dependencies and what are the Classification of Practical Dependencies.

Facebooktwittergoogle_plusredditpinterestlinkedinmail