TNOU BCA, first year (Assignment, Model Q & A, and TIPS): BCA 09 Relational Database Management Systems - SPOT ASSIGNMENT Answers (CY-2013 AY 2013-2014)

TAMIL NADU OPEN UNIVERSITY

School of Computer Science

SPOT ASSIGNMENT (CY-2013/AY 2012-2013)

NOTE : Part A Contains 3 Questions and will carry 5 Marks each, Part B Contain 1 Question and will carry 10 Marks. Students are requested to write 150 words and should not exceed 2 pages each in Part A, 300 words and should not exceed 4 pages in Part B. Referring Materials.

Bachelor of Computer Applications

Second Year

Course Code : BCA-09

Course Title : Relational Data Base Management System

(Total Marks=25)

Part- A- Short Answer Questions

Answer all questions (3 X 5 = 15 Marks)

Define the following

Normalization b. Domain c. Data Dictionary

Answer:

A) Normalization :

Normalization is primarily a technique to validate and improve a logical design, so that it satisfies certain constraints that avoid unnecessary duplication of data. It is the process of decomposing relations with anomalies to produce smaller, well structured relations. To restate the same Normalization is the

process of efficiently organizing data in a database.

B) Domain :

A group of computers and devices on a network that are administered as a unit with common rules and procedures. Within the Internet, domains are defined by the IP address. All devices sharing a common part of the IP address are said to be in the same domain. In database technology, domain refers to the description of an attribute's allowed values. The physical description is a set of values the attribute can have, and the semantic, or logical, description is the meaning of the attribute

C) Data Dictionary:

The data dictionary stores information about the data in the database that is essential to its management as a business resource. A data dictionary, or data catalog is a database (in its own right) that provides a list of the definitions of all objects. in the main database. For instance, it should include information

on all entities in the database, along with their attributes and indexes. This "data about data" is sometimes referred to as metadata. The data dictionary should be accessible to the user of the database, so that she can obtain this metadata.

Explain Functional Dependencies in detail?

Answer:

Functional Dependency:

A dependency from X —>Y is said to be a functional dependency if the value of X attributes determine the value of Y attributes (or) the value of Y attributes is determined by the ralue of X attributes.

Example:

Consider the following relation EMPLOYEE_PROJECT.

Business Rules:

An employee can work on any number of projects on a day. However he / she will not be permitted to work more than once on a project he / she worked on the same day.

ENO

ENAME

DESIGNATION

PNO

PNAME

DATE

INTIME

OUTTIME

HOURS

In the above relation the following functionally dependencies hold:

FD 1: ENO, PNO, DATE —> INTIME, OUTTIME. HOURS

FD 2: ENO —> ENAME, DESIGNATION

FD 3: PNO —> PNAME

Full Functional Dependency:

A dependency from X -->Y is said to be a dependency if removal of some value from X r.-..e-a-s the dependency will no more hold.

Partial Functional Dependency:

A dependency from X —>Y is said to be partial functional dependency if removal of some attribute from X the dependency still holds. (ie. The value of Y attribute can be determines by a

part of X attributes)

Prime Attribute:

An attribute is said to be prime if it is a candidate key, primary key or part of candidate key or primary key.

Non Prime Attribute:

An attribute is said to be non prime if it is neither a candidate key nor a primary key or not part of a candidate key or primary key.

Transitive Functional Dependency:

A functional dependency X —> Y in a relation schema R is a transitive dependency if there is a set of attributes Z, and

both X and Z —> Y hold.

Explain the Multivalued dependencies in detail?

Answer:

Multi-valued Dependence:

Let R be a relation, and let A, B and C are attributes of R. Then we state that B is multi- dependent on A if for a value of A there are multiple values of B and C is multi-dependent on A if for a value of A there are multiple values of C. It can be illustrated symbolically as follows:

A-->-->B

A-->-->C

It states that no relation must contain two or more one to many or many to many relationships that are not directly related to the key. These kinds of relationships are called Multi-valued Dependencies.

Consider the following Relation EMP_PROJ_HOBBY:

ENO

PNO

HOBBY

An Employee may be involved in two or more projects. An EmPloyee may have any number of hobbies. The following multi-valued dependencies hold:

ENO-->-->PNO

ENO-->-->HOBBY

Part- B- Long Answer Question

Answer the following question (1 X 10 = 10 Marks)

1. Draw an E-R Diagram for the database of a hospital with a set of patients and set of medical doctors. With each patient a log of various tests conducted is also associated.