Table of contents
1.
Introduction
2.
MCQs
3.
Frequently asked questions
4.
Conclusion
Last Updated: Mar 27, 2024
Easy

Database design

Author Ankit Kumar
0 upvote
Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

In this article, you will get to learn about some of the MCQ questions based on Database design topics. Along with their answers, we will also give a brief explanation of the multiple-choice questions enlisted below.

Let us begin.

MCQs

  1. Which of the following statement is correct?
    A. Every relation in 3NF is also in BCNF
    B. A relation R is in 3NF if every non-prime attribute of R is fully functionall 
    dependent on every key of R
    C. Every relation in BCNF is also in 3NF
    D. No relation can be in both BCNF and 3N
    Ans Option C
    Explanation - BCNF is a stronger version of 3NF, so every relation in BCNF will be there in 3NF.
     
  2. What are the maximum numbers of superkeys for the relation schema R(A, B, C, D) with A as the key?
    A. Five
    B. Six
    C. Seven
    D. Eight
    Ans Option D
    Explanation - The maximum number of superkeys is given by 2^(n-1), where n is the number of attributes.
    Here n = 4 this implies 2^(n-1) = 2^(4-1) = 8.
     
  3. Consider the following relational schemes for a library database: Book (Title, Author, Serial_no, Publisher, Year, Rate) Collection (Title, Author, Serial_no) within the following functional dependencies:
    I. Title Author --> Serial_no
    II. Serial_no --> Title, Author, Publisher, Year
    III. Publisher Title Year --> Rate
    Given {Author, Title} is the key for both schemes. Which of the following statement is correct?
    A. Both Book and Collection are in BCNF
    B. Both Book and Collection are in 3NF only
    C. Book is in 2NF, and Collection is in 3NF
    D. Both Book and Collection are in 2NF only
    Ans Option C
    Explanation
    As there is just one functional dependency, "Title Author –> Serial no," and Author and Title is the key for Collection, the table "Collection" is in BCNF.
    As the Serial no is not a key, and there is a functional relationship "Serial no –> Title Author Publisher Year," the Book is not in BCNF.
    As non-prime qualities (Publisher Year) are transitively reliant on key [Title, Author], the Book isn't in 3NF.
    As every non-prime property of the table is either reliant on the entire candidate key [Title, Author] or on another non-prime attribute, Book is in 2NF.
    Candidate keys in a table book are Title, Author, and Serial no. Publisher, Year, and Place are non-prime attributes in table Book (attributes that do not appear in any candidate key).
    Thus the Book is in 2NF, and the Collection is in 3NF.
     
  4. In RDBMS, different classes of relations are created by using which technique to prevent modification anomalies.
    A. Functional Dependencies
    B. Data integrity
    C. Referential integrity
    D. Normal Forms
    Ans Option D
    Explanation - Normalization is the process of structuring data in a database so that data redundancy, insertion anomaly, update anomaly, and deletion anomaly are avoided. Normal forms include 1NF, 2NF, 3NF, and BCNF.
     
  5. Out of the following, which normal form is based on 'full functional dependency'?
    A. First normal form
    B. Second normal form
    C. Third normal form
    D. Fourth normal form
    Ans Option B
    Explanation - A full functional dependency, like Second Normal Form, is a state of database normalisation (2NF). The schema must comply with the First Normal Form (1NF) criteria, with all non-key characteristics entirely functionally dependent on the main key and no partial dependency on the candidate key.
    Hence Option B is correct.
     
  6. Consider the relation P(ABCDE): FD = { A → B, BC, C → D, D → E} Find out the highest normal form from the following options.
    A. 1NF
    B. 2NF
    C. 3NF
    D. BCNF
    Ans Option B
    Explanation - Here, candidate keys are A and B -> C, C -> D, D -> E all are. (Non prime attribute -> Non prime attribute.).
    This type of Functional Dependency must not be present in 3NF. Therefore, the highest normal form of this FD is 2NF.
     
  7. Suppose the adjacency relation of vertices in a graph is represented in a table Adj(X, Y). Which of the given queries cannot be expressed by a relational algebra expression of constant length?
    A. List all vertices adjacent to a given vertex
    B. List all vertices which have self-loops
    C. List all vertices which belong to cycles of less than three vertices
    D. List all vertices reachable from a given vertex.
    Ans Option D
    Explanation - In the last option, we need to find out the closure of vertices. This will require a kind of loop. If the graph is like the skewed tree, our query must loop for O(N) Times.
    Thus this query cannot be found out by constant length.
     
  8. An attribute A of datatype varchar (20) has the value 'Ankit', and attribute B of datatype char (20) has the value 'Puja' in oracle. How many memory spaces do attributes A and B have?
    A. 20,20
    B. 5,20
    C. 5,4
    D. 20,4
    Ans Option B
    Explanation - The datatype varchar takes the same memory as the size of the attribute. Hence it will take five memory spaces, and the datatype char takes twenty memory spaces as the attribute b is initiated with twenty.

With this, we come to the end of the questionnaire blog. We hope it added immensely to your understanding of the database designs.

Let us now discuss some of the Frequently asked questions related to this.

Frequently asked questions

  1. What is the most important element in database design?
    The table is the most important element in database design, and it contains columns and rows which store the well-organized data.
  2. What is DBMS?
    Once the data is stored in a database, we need to manipulate it with the help of a system. This system is known as a database management system.
  3. What is Database design?
    The process of designing, developing, implementing, and maintaining the enterprise database management system is known as database design. The design of a particular database decides upon the correlation between the data and also which data is to be stored.
  4. What is the significance of Database design?
    Designing improves the data consistency and is easy to maintain. It becomes so economical in terms of storing the data in a storage disk. Thus the database design in DBMS is very crucial for the high performance of the database system.
  5. Describe the structure of the database design?
    The database tables store the data in a well-organized manner with the help of rows and columns, and thus it is the most common data structure which is used in database design.

Let us now look at the key takeaways from the article.

Conclusion

In this article, we extensively discussed some of the important questions based on database design. If you want to get the interview ready, then you should definitely read out these multiple-choice questions, and you should also read these articles to answer all of the questions easily.

Also, check out - Anomalies In DBMS.

We hope that this blog has helped you enhance your knowledge regarding interview questions on database design, and if you would like to learn more, check out our articles on Coding Ninjas Studio. Do upvote our blog to help other ninjas grow. Happy Coding!

Live masterclass