1What is user defined type How may it be used 2 Define Norma

1.What is user defined type? How may it be used?

2. Define Normalization and briefly describe all 3 forms of normalization with examples.

1.User Defind Types-

A user-defined type facilitates handling multiple fields of related information in a table. Applications that required multiple tables can be simplified to use fewer tables by using a user-defined type to represent the related fields of information instead of storing the information in a separate table.

It provides ability to self-document your data structures.

For example, in several different tables you might have columns defined as varchar(30), and those columns might have nothing to do with each other. But if you define a data type called lastname to be equivalent to varchar(30), when you have multiple columns with the data type lastname, you know that those columns contain similar data.

You can also define a rule or a default as a separate object and bind it to a user-defined data type so that all columns using that data type automatically inherit that rule or default. Also user defined types syntax varies from language to language if you mention the language I will provide you syntax.

for example in cassandra you can defined it as-

2.Normalization

Normalisation is a technique of organizing the data . In normalization we basically decomposing tables to eliminate data redundancy ,errors and also in anamolies like-

Why we need Normalization-

To handle and update the database, without facing data loss. Insertion, Updation and Deletion Anamolies are very frequent if Database is not Normalized.

For Example-

S_id

S_Name

S_Address

Subject_opted

Adam

Noida

Bio

Alex

Panipat

Maths

Stuart

Jammu

Maths

Adam

Noida

Physics

First Normal Form (1NF)

In First Normal Form-

The Primary key is usually a single column, but sometimes more than one column can be combined to create a single primary key. For example consider a table which is not in First normal form

Student Table :

Student

Age

Subject

Adam

Biology, Maths

Alex

Maths

Stuart

Maths

In First Normal Form, any row must not have a column in which more than one value is saved, like separated with commas. Rather than that, we must separate such data into multiple rows.

Student Table following 1NF will be :

Student

Age

Subject

Adam

Biology

Adam

Maths

Alex

Maths

Stuart

Maths

Using the First Normal Form, data redundancy increases, as there will be many columns with same data in multiple rows but each row as a whole will be unique.

Second Normal Form (2NF)

Two basic conditions for 2NF are-

As per the Second Normal Form there must not be any partial dependency of any column on primary key. It means that for a table that has concatenated primary key, each column in the table that is not part of the primary key must depend upon the entire concatenated key for its existence. If any column depends only on one part of the concatenated key, then the table fails Second normal form.

In example of First Normal Form there are two rows for Adam, to include multiple subjects that he has opted for. While this is searchable, and follows First normal form, it is an inefficient use of space. Also in the above Table in First Normal Form, while the candidate key is {Student, Subject}, Age of Student only depends on Student column, which is incorrect as per Second Normal Form. To achieve second normal form, it would be helpful to split out the subjects into an independent table, and match them up using the student names as foreign keys.

New Student Table following 2NF will be :

Student

Age

Adam

Alex

Stuart

In Student Table the candidate key will be Student column, because all other column i.e Age is dependent on it.

New Subject Table introduced for 2NF will be :

Student

Subject

Adam

Biology

Adam

Maths

Alex

Maths

Stuart

Maths

In Subject Table the candidate key will be {Student, Subject} column. Now, both the above tables qualifies for Second Normal Form and will never suffer from Update Anomalies. Although there are a few complex cases in which table in Second Normal Form suffers Update Anomalies, and to handle those scenarios Third Normal Form is there.

Third Normal Form (3NF)

two basic conditions for 3NF are-

Third Normal form applies that every non-prime attribute of table must be dependent on primary key, or we can say that, there should not be the case that a non-prime attribute is determined by another non-prime attribute. So this transitive functional dependency should be removed from the table and also the table must be in Second Normal form.

For example, consider a table with following fields.

Student_Detail Table :

Student_id

Student_name

DOB

Street

city

State

Zip

In this table Student_id is Primary key, but street, city and state depends upon Zip. The dependency between zip and other fields is called transitive dependency. Hence to apply 3NF, we need to move the street, city and state to new table, with Zip as primary key.

New Student_Detail Table :

Student_id

Student_name

DOB

Zip

Address Table :

Zip

Street

city

state

The advantage of removing transtive dependency is-