Download DBMS Questions & Answers: Third Module Study Guide and more Exercises Database Management Systems (DBMS) in PDF only on Docsity!
MAJLIS ARTS AND SCIENCE COLLEGE
DEPARTMENT OF COMPUTER SCIENCE
D B M S ( 4 th^ Semester Online Study Material)
4 BCA & 4 BSc Computer Science
(Questions and answers based on Third MODULE) (1 Mark questions) 1 .A relation schema R is --- if whenever a nontrivial functional dependency x- >A hold in R the X is a super key of R a) BCNF 2 .If every non-prime attribute A of a relation schema R is fully functionally dependent on the primary key of R, then R is said to be in ------- a)2 NF 3 .The set of functional dependencies that is logically implied by F is called the ----- of F a) Closure 4.The database design prevents some data from being stored due to _______. a) Insertion anomalies 5.If one attribute is determinant of second, which in turn is determinant of third, then the relation cannot be: a) 3NF
- 5NF is designed ------ dependency a) Join dependency 7.The different classes of relations created by the technique for preventing modification anomalies are called ---- a) Normal form 8.A relation in BCNF and no multi-value dependencies is in ------ form a) Fourth Normal form ( 2 Mark questions) 9 .What is Normalization? and why it is done? a) Normalization is a database design technique that organizes tables in a manner that reduces redundancy and dependency of data. Normalization divides larger tables into smaller tables and links them using relationships. The purpose of Normalization is to eliminate redundant (useless) data and ensure data is stored logically. 10 .What is first normal form? a) A relation is in first normal form if and only if the domain of each attribute contains only atomic (indivisible) values 11 .Define 2NF a) A table is said to be in 2NF if both the following conditions hold:
- Table is in 1NF (First normal form)
- No non-prime attribute is dependent on the proper subset of any candidate key of table. An attribute that is not part of any candidate key is known as non-prime attribute.
12 .Define Fourth normal form (4NF) a) It is a level of database normalization where there are no non-trivial multivalued dependencies other than a candidate key. It builds on the first three normal forms (1NF, 2NF and 3NF) and the Boyce-Codd Normal Form (BCNF). ( 5 Mark questions) 13 .What is Functional Dependency and Functional Dependency Set. a) A functional dependency A->B in a relation holds if two tuples having same value of attribute A also have same value for attribute B. Functional Dependency Set: Functional Dependency set or FD set of a relation is the set of all FDs present in the relation. For Example, FD set for relation STUDENT shown in table 1 is: {STUD_NO->STUD_NAME, STUD_NO->STUD_PHONE, STUD_NO->STUD_STATE, STUD_NO-> STUD_COUNTRY, STUD_NO - > STUD_AGE, STUD_STATE->STUD_COUNTRY} 14 .Different types of anomaly in database a) 1. Insert Anomaly 2.Delete Anomaly 3.Update Anomaly
INSERT Anomaly in Database
Boyce Codd normal form (BCNF) It is an advance version of 3NF that’s why it is also referred as 3.5NF. BCNF is stricter than 3NF. A table complies with BCNF if it is in 3NF and for every functional dependency X->Y, X should be the super key of the table. 1 6.Define Multivalued dependency a) Multivalued dependency occurs when there are more than one independent multivalued attributes in a table. For example : Consider a bike manufacture company, which produces two colours (Black and white) in each model every year. bike_model manuf_year color M1001 2007 Black M1001 2007 Red M2012 2008 Black M2012 2008 Red M2222 2009 Black M2222 2009 Red Here columns manuf_year and color are independent of each other and dependent on bike_model. In this case these two columns are said to be multivalued dependent on bike_model. These dependencies can be represented like this: bike_model - >> manuf_year ( 10 Mark questions) 1 7.Explain BCNF with an example a) Rules for BCNF For a table to satisfy the Boyce-Codd Normal Form, it should satisfy the following two conditions:
1. It should be in the Third Normal Form. 2. And, for any dependency A → B, A should be a super key. The second point sounds a bit tricky, right? In simple words, it means, that for a dependency A → B, A cannot be a non-prime attribute , if B is a prime attribute. Example Below we have a college enrolment table with columns student_id , subject and professor. student_id subject professor 101 Java P.Java 101 C++ P.Cpp 102 Java P.Java 103 C# P.Chash 104 Java P.Java
In the above table:
- One student can enrol for multiple subjects. For example, student with student_id 101, has opted for subjects - Java & C++
- For each subject, a professor is assigned to the student.
- And, there can be multiple professors teaching one subject like we have for Java. What do you think should be the Primary Key? Well, in the table above student_id, subject together form the primary key, because using student_id and subject , we can find all the columns of the table. One more important point to note here is, one professor teaches only one subject, but one subject may have two different professors. Hence, there is a dependency between subject and professor here, where subject depends on the professor name. This table satisfies the 1st Normal form because all the values are atomic , column names are unique and all the values stored in a particular column are of same domain. This table also satisfies the 2nd Normal Form as there is no Partial Dependency. And, there is no Transitive Dependency , hence the table also satisfies the 3rd Normal Form. But this table is not in Boyce-Codd Normal Form. Why this table is not in BCNF? In the table above, student_id, subject form primary key, which means subject column is a prime attribute. But there is one more dependency, professor → subject. And while subject is a prime attribute, professor is a non-prime attribute , which is not allowed by BCNF. How to satisfy BCNF? To make this relation(table) satisfy BCNF, we will decompose this table into two tables, student table and professor table. Below we have the structure for both the tables. Student Table student_id p_id 101 1 101 2 and so on...
What is Multi-valued Dependency? 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 value of B exists, then the table may have multi-valued 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. If all these conditions are true for any relation(table), it is said to have multi-valued dependency.
Example
Below we have a college enrolment table with columns s_id , course and hobby. s_id course hobby 1 Science Cricket 1 Maths Hockey 2 C# Cricket 2 Php Hockey As you can see in the table above, student with s_id 1 has opted for two courses, Science and Maths , and has two hobbies, Cricket and Hockey. You must be thinking what problem this can lead to, right? Well the two records for student with s_id 1 , will give rise to two more records, as shown below, because for one student, two hobbies exist, hence along with both the courses, these hobbies should be specified. s_id course hobby 1 Science Cricket 1 Maths Hockey 1 Science Hockey 1 Maths Cricket And, in the table above, there is no relationship between the columns course and hobby. They are independent of each other. So, there is multi-value dependency, which leads to un-necessary repetition of data and other anomalies as well.
How to satisfy 4th Normal Form?
To make the above relation satisfy the 4th normal form, we can decompose the table into 2 tables. Course Opted Table s_id course 1 Science 1 Maths 2 C# 2 Php And, Hobbies Table , s_id hobby 1 Cricket 1 Hockey 2 Cricket 2 Hockey Now this relation satisfies the fourth normal form. 20 .Explain Fifth normal form (5NF) and join dependencies with suitable example a) A relation R is in 5NF if and only if every join dependency in R is implied by the candidate keys of R. A relation decomposed into two relations must have loss-less join Property, which ensures that no spurious or extra tuples are generated, when relations are reunited through a natural join. Properties – A relation R is in 5NF if and only if it satisfies following conditions:
1. R should be already in 4NF. 2. It cannot be further non loss decomposed (join dependency) Example – Consider the above schema, with a case as “if a company makes a product and an agent is an agent for that company, then he always sells that product for the company”. Under these circumstances, the ACP table is shown as: Table – ACP Agent Company Product A1 PQR Nut A1 PQR Bolt A1 XYZ Nut A1 XYZ Bolt A2 PQR Nut
