Two marks Questions with Answers

Two Marks Questions with Answers

Q.1 Explain entity relationship model.

Ans.

• The ER data model specifies enterprise schema that represents the overall logical structure of a database.

• The E-R model is very useful in mapping the meanings and interactions of real- 19 world entities onto a conceptual schema.

Q.2 Give the limitations of E-R model? How do you overcome this?

Ans:

1) Loss of information content: Some information be lost or hidden in ER model 

2) Limited relationship representation: ER model represents limited relationship as compared to another data models like relational model etc.

3) No representation of data manipulation: It is difficult to show data manipulation in ER model.

4) Popular for high level design: ER model is very popular for designing high level design.

Q.3 List the design phases of Entity Relationship model.

Ans:

1) Requirement Analysis                       2) Conceptual Database Design

3) Logical Database Design                  4) Schema Refinement

5) Physical Database Design                 6) Application and Security Design.

Q.4 What is an entity?

Ans: • An entity is an object that exists and is distinguishable from other objects.

• For example - Student named "Poonam" is an entity and can be identified by her name. Entity is represented as a box, in ER model.

Q.5 What do you mean by derived attributes?

Ans:

• Derived attributes are the attributes that contain values that are calculated from other attributes.

• To represent derived attribute there is dotted ellipse inside the solid ellipse. For example -Age can be derived from attribute DateOfBirth. In this situation, DateOfBirth might be called Stored Attribute.

Q.6 What is a weak entity? Give example.

Ans: Refer section 2.3.4

A weak entity is an entity that cannot be uniquely identified by its attributes alone. The entity set which does not have sufficient attributes to form a primary key is called as weak entity set.

Q.7 What are the problems caused by redundancy?   AU: Dec.-16, May-18

Ans:Problems caused by Redundancy: Following problems can be caused by redundancy -

i) Redundant Storage: Some information is stored repeatedly.

ii) Update Anomalies: If one copy of such repeated data is updated then inconsistency is created unless all other copies are similarly updated.

iii) Insertion Anomalies: Due to insertion of new record repeated information get added to the relation schema.

iv) Deletion Anomalies: Due to deletion of particular record some other important information associated with the deleted record get deleted and thus we may lose Isbom some other important information from the schema.

Q.8 Define functional dependency.     AU: Dec 04,05, May 05,14,15

Ans: Let P and Q be sets of columns, then : P functionally determines Q, written P→ Q if and only if any two rows that are equal on (all the attributes in) P must be equal on (all the attributes in) Q.

In other words, the functional dependency holds if

T1.P = T2.P, then T1.Q=T2.Q

Where notation T1.P projects the tuple T1 onto the attribute in P.

Q.9 Why certain functional dependencies are called trivial functional dependencies?   AU: May-06,12

Ans:

• A functional dependency FD: XY is called trivial if Y is a subset of X. This kind of dependency is called trivial because it can be derived from common sense. If one "side" is a subset of the other, it's considered trivial. The left side is considered the determinant and the right the dependent.

• For example - {A,B} -> B is a trivial functional dependency because B is a subset of A,B. Since (A,B) -> B includes B, the value of B can be determined. It's a trivial functional dependency because determining B is satisfied by its relationship to A,B

Q.10 Define normalization. AU: May-14

Ans: Normalization is the process of reorganizing data in a database so that it meets two basic requirements:

1) There is no redundancy of data (all data is stored in only one place), and

2) Data dependencies are logical (all related data items are stored together)

Q.11 State anomalies of 1NF.    AU: Dec.-15

Ans: All the insertion, deletion and update anomalies are in 1NF relation.

Q.12 What is multivalued dependency?   AU: Dec.-06

Ans: A table is said to have multi-valued dependency, if the following conditions are true,

1) For a dependency A → B, if for a single value of A, multiple values of B exists, then the table may have multi-values dependency.

2) Also, a table should have at-least 3 columns for it to have a multi-valued dependency.

3) And, for a relation R(A,B,C), if there is a multi-valued dependency between, A and B, then B and C should be independent of each other.

Q.13 Describe BCNF and describe a relation which is in BCNF.

Ans: Refer section 2.12.

Boyce and Codd Normal Form is a higher version of the Third Normal form. This form deals with certain type of anomaly that is not handled by 3NF.

A 3NF table which does not have multiple overlapping candidate keys is said to be in BCNF.

Or in other words, For a table to be in BCNF, following conditions must be satisfied:

i) R must be in 3rd Normal Form

ii) For each functional dependency (X → Y), X should be a super Key. In simple words if Y is a prime attribute then X can not be non prime attribute.

Q.14 Why 4NF in normal form is more desirable than BCNF?

Ans:

4NF is more desirable than BCNF because it reduces the repetition of information. If we consider a BCNF schema not in 4NF we observe that decomposition into 4NF does not lose information provided that a lossless join decomposition is used, yet  A redundancy is reduced.

Q.15 Give an example of a relation schema R and set of dependencies such that R is in BCNF but not in 4NF.   AU: May-12

Ans: Consider relation R (A,B,C,D) with dependencies

AB→C

ABC→D

AC→B

Here the only key is AB. Thus each functional dependency has superkey on the left. But MVD has non-superky on its left. So it is not 4NF.

Q.16 Show that if a relation is in BCNF, then it is also in 3NF.   Ans:AU: Dec.-12

• Boyce and Codd Normal Form is a higher version of the Third Normal form.

• A 3NF table which does not have multiple overlapping candidate keys is said to ove be in BCNF. When the table is in BCNF then it doesn't have partial functional dependency as well as transitive dependency.

• Hence it is true that if relation is in BCNF then it is also in 3NF.

Q.17 Why it is necessary to decompose a relation?  AU: May-07

Ans:

• Decomposition is the process of breaking down one table into multiple tables.

• The decomposition is used for eliminating redundancy.

Q.18 Explain atleast two desirable properties of decomposition. AU: May-03,17,19, Dec.-05

Ans:There are two properties associated with decomposition and those are -

1) Loss-less Join or non Loss Decomposition: When all information found in the original database is preserved after decomposition, we call it as loss less or nonloss decomposition.

2) Dependency Preservation: This is a property in which the constraints on the wied original table can be maintained by simply enforcing some constraints on each of the smaller relations.

Q.19 Explain with simple example lossless join decomposition.   AU: May-03

Ans: Refer section 2.10.1.                  

i) Union of attributes of R1 and R2 must be equal to attribute of R. Each attribute of R must be either in R1 or in R2.

Att(R1) U Att(R2) = Att(R)

ii) Intersection of attributes of R1 and R2 must not be NULL.

Att(R1) Att(R2) ≠ Φ

iii)Common attribute must be a key for at least one relation (R1 or R2)

Att(R1) Att(R2) -> Att(R1)

Att (R1) Att (R2) -> Att (R2)                         

Q.20 Define the terms entity set and relationship set.  AU: May-19

Ans: Refer section 2.1.2.

Entity set: The entity set is a set of entities of the same types. For example - All students studying in class X of the School. The entity set need not be disjoint. Each entity in entity set have the same set of attributes and the set of attributes will distinguish it from other entity sets. No other entity set will have exactly the same set of attributes.

Relationship Sets: Relationship is an association among two or more entities.

The relationship set is a collection of similar relationships. For example - Following Fig. 2.1.2 shows the relationship works for for the two entities Employee and Departments.

The association between entity sets is called as participation. That is, the entity sets E1, E2,..., En participate in relationship set R.

The function that an entity plays in a relationship is called that entity's role.

Q.21 'Boyce-Codd normal form is found to be stricter than third normal form Justify the statement.   AU: Dec.-19

Ans.:                                                                                   

(i) Every relation which is in BCNF is also in 3NF but every relation which is in 3NF is not necessarily be in BCNF.

(ii) BCNF non-transitionally depends on individual candidate key but there is no such requirement in 3NF.

Hence BCNF is stricter than 3NF.

Q.22 What is the significance of "participation role name" in the description of relationship types?   AU: Dec.-19

Ans: Each entity type that participates in a relationship type plays a particular role in the relationship. The role name signifies the role that a participating entity of an entity plays in each relationship instance. In PREPARED BY relationship type, EMPLOYEE plays the role of document creator and voucher plays the role of document created. Entity TEACHER and Entity STUDENT are related with a relationship TEACHER-teach-STUDENT. The teaches is a participating role in the entity set TEACHER and STUDENT.