IGNOU BCA CS-06 Solved Assignment 2011

There are five questions in this assignment.  Answer all the questions. You may use illustrations and diagrams to enhance the explanations.  Please go through the guidelines regarding assignments given in the Programme Guide for the format of presentation.  Answer to each part of the question should be confined to about 300 words.

Question 1:  What is Normalization? Explain the condition under which a relation needs to be normalized to 3 NF from 2 NF with the help of   an example?                    (5 Marks)

Ans:

Normalization : Database normalization is a technique for designing relational database tables to minimize duplication of

Information and, in so doing, to safeguard the database against certain types of logical or structural problems, namely data anomalies. For example, when multiple instances of a given piece of information occur in a table, the possibility exists that these instances will not be kept consistent when the data within the table is updated, leading to a loss of data integrity. A table that is sufficiently normalized is less vulnerable to problems of this kind, because its structure reflects the basic assumptions for when multiple instances of the same information should be represented by a single

Instance only.

A relation R is in Second normal form (2NF) if

(a) R is 1NF, and

(b) All non-prime attributes are fully dependent on the candidate keys

Third normal form (3NF) requires that there are no functional dependencies of non-key attributes on something other than a candidate key.

Here an example of a relation needs to be normalized to 3 NF from 2 NF

2 NF	3NF
Module Dept Lecture M1 D1 L1 M2 D1 L1 M3 D1 L2 M4 D2 L3 M5 D2 L4	Lecture Dept L1 D1 L2 D1 L3 D2 L4 D2 Module lecture M1 L1 M2 L1 M3 L2 M4 L3 M5 L4

Another example
Tabel1

 

Emp ID

Emp name

Address

Age

Date  of Birth

Dept Code

Department name

Here every Non key attribute are depend on Key attribute, But there is a Transitive dependency

 So, table 1 is not in 3 NF

Table 1.1

Emp ID

Emp name

Address

Age

Date  of Birth

Dept Code

Table 1.2

Dept Code

Department name

Question 2: Compare primary, secondary and clustering indexes. Which of these indexes are dense and which are not?                                                                             (5 Marks)

ANS:

Two Types of Indices

•         Ordered index (Primary index or clustering index) – which is used to access data sorted by order of values.

Ordered Index

•         Hash index (secondary index or non-clustering index) - used to access data that is distributed uniformly across a range of buckets.

Hash Index

Which of these indexes are dense and which are not.

The ordered indices are dense or not

•         Dense index - an index record appears for every search-key value in the file.

•         Sparse index - an index record that appears for only some of the values in the file.

      

Question 3: What is difference between B tree and B⁺   tree? Why a B⁺   tree is a better tree structure than a B tree for implementation of an indexed sequential file.   (5 Marks)

Ans:

In a B- tree you can store both keys and data in the internal/leaf nodes. But in a B+ tree you have to store the data in the leaf nodes only. The principal advantage of B+ trees over B trees is they allow you to in pack more pointers to other nodes by removing pointers to data, thus increasing the fan-out and potentially decreasing the depth of the tree

B⁺   tree is a better tree structure than a B tree for implementation of an indexed sequential file .

In a B+ tree, in contrast to a B-tree, all records are stored at the leaf level of the tree; only keys are
Question 4: Consider the following employee database schema:

        

Employee ( ESSN ,ENAME , DOB , DEPT-NO , SALARY)

   Dependent (ESSN, DEPENDENT_NAME, RELATION, DOB)

   Department (DEPT_NO, DEPT_NAME, MANAGER)                            

         Perform the following queries using SQL                                                               (5 Marks)

      

(a)    Find the details of dependent for employee having name ABC.

SELECT     DEPENDENT.*

FROM         DEPENDENT INNER JOIN

                     EMPLOYEE ON DEPENDENT.ESSN = EMPLOYEE.ESSN

WHERE     (EMPLOYEE.ENAME = 'ABC') stored in interior nodes

(b)   Find the name of manager of the department in which employee  whose ESSN code is     1234 

SELECT     DEPARTMENT.MANAGER

FROM         DEPARTMENT INNER JOIN

                      EMPLOYEE ON DEPARTMENT.DEPT_NO = EMPLOYEE.DEPT_NO

WHERE     (EMPLOYEE.ESSN = '1234')

(c)    Find the name of all employees whose age is less than 18 years.   

SELECT    ENAME

FROM         EMPLOYEE

Where FLOOR (DATEDIFF (DAY, DOB, SYSDATE) / 365.25) <18

(d)   Find the DOB of the son  of  employee  having employee code ESSN 5078 

SELECT DEPENDENT.DEPENDENT_NAME,DEPENDENT.DOB, EMPLOYEE.ESSN

FROM  DEPENDENT INNER JOIN

       EMPLOYEE ON DEPENDENT.ESSN = EMPLOYEE.ESSN

WHERE     (EMPLOYEE.ESSN = '5078')