Question

1. Discuss database constraints: Primary key, check, and referential integrity constraints? Give an example for each.
2. What are the three types of database design situations?
3. Briefly describe the various tasks of the primary key. Explain the concept of a foreign key. Explain the concept of a surrogate key.
4. Explain the essence of normalization that is implemented through the use of normal forms.
5. What is SQL? Explain why it is important to learn SQL.
6. What is the purpose of normalization? What conditions are required for a relation to be in 3NF?
7. What are the advantages and disadvantages of normalization?
8. Explain denormalization, and why it may be appropriate to denormalize a set of tables.

Answer 1

Part-1

Primary-Key:- As the name suggests this key identifies each record in a table uniquely. Record basically means a row of the table. The Primary key has to be unique and can't be NULL. A table can have only one Primary key but that Primary key can be either a column or a combination of multiple columns.

Eg;- A table with student details can have student no. as a Primary key.

Check:- It is used to limit the range of a column,if you put check in a column it will allow only certain range of value in that column but you can also put this constraint on table in that case it will limit the values in certain columns.

Eg:- Let's say we have a database which has age as one of the column, and we are making this database for senior citizens so we can put a age constraint that age >=65 are allowed.

Referential integrity constraints:- It is specified between two tables, this makes sure that the foreign key of table1 can have values either of the primary key it is refering to NULL.This checks there are no orphan records in the table.

Table -1 Roll no:-{12,20,34,89,78}(Primary key of table -1)
Table-2 Roll no;-{12,34,89}(foreign key of Table-2)-------------------------allowed
Table-2 Roll no;-{12,34,14}(foreign key of Table-2)-------------------------Not allowed as the value 14 is not in table-1

Part-2:-

1.Unified Modeling Language

2.Entity-Relationship

3.Relational Model

PART-3:-

Primary Key:-As the name suggests this key identifies each record in a table uniquely. Record basically means a row of the table. The Primary key has to be unique and can't be NULL. A table can have only one Primary key but that Primary key can be either a column or a multiple columns.
For eg:- there is a database of student information so in this roll no column can be primary key as it would be unique and will not be null,
but let's say we have a MIS of a school which has roll no,course ,grade so in this case none of the columns can individually be primary key as they will not be unique so we can make roll no+course as a primary key.

Foreign key:- This is key is basically used to link the two tables, it is either a column or combination of columns refrencing a primary key of another table. It is also called refrencing key. The table containing the foreign key is called a child table where as the table it is refering to is called Parent table.
This doesn't mean that a table having a foreign key doesn't have primary key.
eg:- let's say there is a students information database so in this we have department name as one of the columns and other table has all details of a department so department no.is the primary key of table -2 but also department name of table is refering to department name of table-1 and department name of table-2 is the foreign key.

Surrogate Key:- It is basically an artificially generated key, which is basically of most use when there is no natural key in the table. Such as we can use serial no. as the auto increment column which could be used as primary key.

PART-4:-

Normalization:- It is the process of minimizing redundancy(occurs when the same piece of data is stored in two or more separate places) from a relation or set of relations.Redundancy can cause insertion deletion anomolies etc.Normal forms are used to eliminate or reduce redundancy in database tables. Normal forms like(first ,second third and BCNF etc). Normalization is must and a very important concept in databases.

1.First normal form:- A column can't hold multiple values

2.Second normal form;-

1. The relation should be in first normal form

2. There should not be any non-prime attribute that is dependent on the proper subset of any candidate key of table.( Non prime attribute:-An attribute that is not part of any candidate key )

3.Third normal form:-

1. There is no transitive dependency for non-prime attributes.
2.The relation should be in second normal form.

PART-5:-

SQL:- This stands for structured query language, it is a language which is used to handle or deal with relational database. SQL programming can be effectively used to insert, search, update, delete database records etc.

Why to learn SQL?
1.Most commonly and popular database language.
2.It is easy to learn this language and very useful in any sector.
3. As the amount of data is increasing obviously we need databases to store it and to handle database we need to know SQL.

PART-6:-
Purpose of normalization is to reduce the redundancy and data inconsistency. To increase the efficiency and performance of the databases.

Conditions to be in third Normal form:-

A relation is in third normal form, if there is no transitive dependency for non-prime attributes.
The relation should be in second normal form.

Short trick :-

If any of these conditons hold then table is in third normal form.
For every non-trivial function dependency X –> Y

• X is a super key
• Y is a prime attribute (each element of Y is part of some candidate key).

PART-7:-

ADVANTAGES OF NORMALIZATION

.1) Size of the databases can be reduced with this concept as well as the efficiency could be more.

2) Better performance can be guranteed. As databases become lesser in size, the pass through the data becomes faster and shorter.

3) More data can be saved per page.

4) Faster maintenance.

DISADVANTAGES OF NORMALIZATION

1) The need to join table’s increases and the task becomes more tedious. The database becomes harder to realize as well.

2) There is always a need to go to the lookup table.

3) As we move to higher normal form, the performance becomes slower and slower.

5) Proper knowledge is required on the various normal forms to effectively use this concept, other wise there can major errors.

PART-8:-

Denormalization:- It is just the opposite of normalization, it is a optimization technique to add redundancy in table,to decrease the cost of more joins between tables , to increase performance.

It may be appropriate to use denormalization for tasks mentioned below.:-

1.For maintaing history

2. Improving query performance

3.Speeding up the reporting

4.Computing commonly needed values.