DBMS MCQ

Question: What is the Full Form of DBMS?

Options:

• a) Data Management System.
• b) Database Management System.
• c) Database Management Software.
• d) Data Management Software.

Answer: b) Database Management System.

Explanation:
The DBMS full form is Database Management System. It is software that is used to store, manipulate, and query records stored in a certain database. We have several types of database management systems such as NoSQL database management systems (for example MongoDB, CouchDB, Cloudant, Amazon Neptune, Neo4j, Hbase, Big Table, Accumulo, etc.) and SQL database management systems (for example MYSQL, ORACLE, dBase, etc.).

Question: What is the Full Form of DBA in DBMS?

Options:

• a) Database Administrator.
• b) Database Advisor.
• c) Database Advocate.
• d) None.

Answer: a) Database Administrator.

Explanation:
The DBA full form in DBMS is Database Administrator. A DBA is an individual who handles and manages the database and ensures that data is correctly stored and retrieved. It is a very important role as he/she needs to perform the basic important operations like designing the database, migration of the database from another or to another database, the configuration of a database, etc. of the databases. Other important things that are managed by a DBA are security, troubleshooting, recovery, etc. If the DBS designs a good database then the query is also faster and hence improves the overall functionality of the databases.

Question: Which One of the Following is an Example of RDBMS?

• a) Oracle
• b) Publisher
• c) Windows registry
• d) MongoDB

Correct Answer – Oracle is an example of RDBMS

Explanation –
A relational database management system (RDBMS) is a group of tools and features that enables IT teams and other users to build, maintain, and interact with relational databases in various ways. Most commercial relational database management systems (RDBMSes) use Structured Query Language (SQL) to access the databases, which are stored as tables in RDBMSes. However, as SQL was invented after the initial construction of the relational architecture, it is not necessary for RDBMS use.

• Oracle Database is an RDBMS.
• The Publisher is a database instance that makes data available to other locations through SQL Server replication.
• The Windows Registry is a hierarchical database that stores low-level settings for the Microsoft Windows operating system and for applications that opt to use the registry.
• MongoDB is a NoSQL database and hence does not require a relational database management system (RDBMS).

Question: Which of the Following is Not a Key in DBMS?

• a) Primary Key
• b) Unique Key
• c) Foreign Key
• d) Domestic Key

Correct Answer – d) Domestic Key is not a key in DBMS

Explanation –

• Primary Key: A key in a relational database that is unique for each record and is not Null.
• Unique Key: A unique key is a set of one or more than one field/column of a table that uniquely identifies a record in a database table. It can be Null.
• Foreign Key: The Foreign Key links two tables together. It is a field (or collection of fields) in one table that refers to the Primary Key in another table.

Question: Which of the Following is not Involved in DBMS?

• a) End Users
• b) Web services
• c) Application Request
• d) Data

Correct Answer – b) Web services are not involved in DBMS.

Explanation –
Web services aren’t involved in Database Management System. Other things like the data and application requests are a part of the DBMS.

Question: Which of the Following is Not an Example of DBMS?

• a) MySQL
• b) SQL Server
• c) Microsoft Access
• d) Microsoft Outlook

Correct Answer – d) Microsoft Outlook is not an example of DBMS

Explanation –

• MySQL Database Service is a fully managed database service to deploy cloud-native applications.
• Microsoft SQL Server is a relational database management system developed by Microsoft.
• Microsoft Access is the only application software you need to create apps, utilize databases, and store data in SQL Server and Microsoft Azure SQL.
• Microsoft Outlook is the email service provided by Microsoft. Hence this is the answer.

Question: Which of the Following is not an Advantage of DBMS?

• a) Increased data consistency
• b) Reduced data redundancy
• c) Improved data security
• d) Increase data isolation

Correct Answer – d) Increased data isolation is not an advantage of DBMS

Explanation –

• Data Consistency is improved in DBMS with the help of transaction management and following the ACID properties.
• Data redundancy is minimized in DBMS with the help of normalization.
• Database security includes a variety of measures used to secure database management systems from malicious cyber-attacks and illegitimate use.

Question: ER Modelling is a ?

• a) Top down approach
• b) Bottom-Up approach
• c) Left-right approach
• d) Both top-down and Bottom-up

Correct Answer – a) ER Modelling is a top-down approach

Explanation –
The top-down approach goes from the general to the specific, and the bottom-up approach begins at the specific and moves to the general. For example, Generalization is a bottom-up approach, and Specialization is a top-down approach.

An entity-relationship model (ER model) describes inter-related things of interest in a specific knowledge domain. An ER model is composed of entity types (which classify the items of interest) and specifies relationships between instances of those entity types.

In software engineering, an ER model is commonly formed to represent things a business needs to remember to perform business processes. Consequently, the ER model becomes an abstract data model that defines a data or information structure that can be implemented in a database, typically a relational database.

Some ER models show super and subtype entities connected by generalization-specialization relationships, and an ER model can also be used in the specification of domain-specific ontologies.

Question: Controlling Redundancy in a Database Management System Helps in ?

• a) Avoiding duplication of efforts
• b) Avoiding unnecessary wastage of storage space
• c) Avoiding inconsistency among data
• d) All of the above

Correct Answer – d) All of the above

Explanation –
When we control redundancy in a database management system, we try to eliminate the duplicity of data with the help of normalization, reducing the storage space wastage and avoiding inconsistency among data.

Data inconsistency is when multiple tables within a database deal with the same data but may receive it from different inputs. If there are redundant data, then it may be possible that all the redundant data are not getting updated together, which can lead to inconsistency in the database.

Question: Which of the Following Is not an Advantage of the Database Approach?

• a) Elimination of the data redundancy
• b) Ability to associate related data
• c) Increased security
• d) None of the above

Correct Answer – All of the given options are advantages of the database approach

Explanation –

• We create databases with the help of relations, which helps us organize and relate the data with each other.
• With the help of normalization methods, we can reduce data redundancy.
• Keys and access methods help to maintain the database security.
• Hence all of the above options are correct.

Question: Which of the Following Statements Concerning Relational Database is True?

• a) a foreign key field may be null
• b) a primary key field may be null
• c) All relations must be in at least a third normal form
• d) The primary key fields of a relation must be adjacent

Correct Answer – a) A foreign key field may be null

Explanation –

• A foreign key field may be NULL since there may be no entries in the referenced relation for the foreign key attribute.
• A primary key in a database must be unique and not null
• The relations in a database can be in any normal form.
• The primary key fields can be a composite key of one or more related fields.

Question: If a System Can Enforce Referential Integrity, then this Ensures that?

• a) A record can never contain a null value for a foreign key attribute
• b) A non-null foreign key attribute always refers to another record
• c)A foreign key attribute is a record that always refers to another record that contains null
• d) A foreign key attribute in a record always refers to another record that does not contain null values.

Correct Answer – b) If a system can enforce referential integrity, then this ensures that a non-null foreign key attribute always refers to another record

Explanation –
Referential integrity refers to the relationship between tables. Because each table in a database must have a primary key, this primary key can appear in other tables because of its relationship to data within those tables. When a primary key from one table appears in another table, it is called a foreign key.

Referential integrity requires that a foreign key must have a matching primary key or be null. This constraint is specified between two tables (parent and child); it maintains the correspondence between rows in these tables. It means the reference from a row in one table to another table must be valid.

To ensure that there are no orphan records, we need to enforce referential integrity. An orphan record is one whose foreign key value is not found in the corresponding entity – the entity where the Primary key is located.

Question: Relation “C” is the Join of Relation “A” and Relation “B” on Condition “p”. Which of the Following Statements must be True in all Cases?

• a) the cardinality of C is greater than the cardinality of A
• b) the arity of C is greater than that of A
• c) the cardinality of C is less than the cardinality of A
• d) None of the above

Correct Answer – b) If relation C is the join of relation A and relation B on condition p, then the arity of C is greater than that of A.

Explanation –
The arity is the number of columns in the relation. In relational algebra, all relations columns are kept; thus, the arity of C is the arity of A plus B. The arity of both A and B must be greater than one.

Question: Relation “C” is a Projection of Relation “A”. which of the Following Statements Must be True in all Cases where Relation “C” Differs from Relation “A”?

• a) The cardinality of C is greater than the cardinality of A
• b) The cardinality of C is less than the cardinality of A
• c) The arity of C is greater than the arity of A
• d) The arity of C is less than the arity of A

Correct Answer – d)If relation C is a projection of relation A then the arity of C is less than that of A.

Explanation –
The arity is the number of columns in the relation. In projection, either the arity stays the same or is reduced. The fact that C is different from A means this is the only valid option.

Question: Table and View Names are Unique in the Database. So to Make the Naming Convention of the Tables and Views Easier?

• a) Table names are only viewable by the person who created them.
• b) Tables are stored on each user’s own disk space.
• c) The DBA can make creating tables with certain strings in the name password protected.
• d) Table names have their creator’s user name prepended to the table name.
• e) A table created with the same name as another table is automatically renamed to make it unique.

Correct Answer – c) table and view names are unique in the database. So to make the naming convention of the tables and views easier, table names have their creator’s user name prepended to the table name.

Explanation –
Table names are automatically added to the start of table names you specify so that "tablea" would become "satbi.tablea", provided your username was satbi. This helps you use your own table names locally without worrying too much about other users.

Question: A Database Can be Left in an Inconsistent State Due to?

• a) Deadlock
• b) A transaction fails, and its changes are applied to the database.
• c) Roll forward after a failure.
• d) Transactions being aborted.
• e) Inaccurate data is entered into the database.

Correct Answer – b) A database can be left in an inconsistent state if a transaction fails, and its changes are applied to the database.

Explanation –
Suppose you are sending money to your friend through UPI, but when you send the money, there is a network issue at your end, and the transaction fails. The money is not deducted from your account but gets added to your friend’s account. This is an example of inconsistency in the database.

A database can be left in an inconsistent state if there is a transaction failure, but the changes are applied to the database.

Aborting a transaction requires that its effects on the database have been removed from the database. If you do not undo the changes, the database will be inconsistent.

Question: Which of the Following is not Used to Support Recovery and Consistency in a Database System?

• a) Access logging
• b) A dump
• c) Two phase commit
• d) Checkpointing
• e) A journal

Correct Answer – a) Access logging is not used to support recovery and consistency in a database system.

Explanation –
Access logging is when a record is kept of all attribute accesses made by a particular user. It is not directly relevant to supporting data recovery.

Question: During Rollforward, Which of the Following is not True

• a) Transactions in the log but not yet committed are restarted where they left off.
• b) Committed transactions in the log are reapplied.
• c) Committed transactions are reapplied in the same order as they appear in the log.
• d) Transactions that are reapplied are reapplied to the image as it was since the last checkpoint.
• e) Only once the roll forward has been completed can new transactions be performed.

Correct Answer – a) For roll forward, all the options are true except the one which says transactions that are in the log but not yet committed are restarted where they left off.

Explanation –
In general database practice, a ‘roll forward’ is the term given to the part of a database recovery process of “redoing” committed transactions whose changes weren’t reflected in the data file at the point the database service was interrupted to roll the database forward to a transactionally consistent state. In roll forward, uncommitted or unaborted transactions are lost.

Question: Locking Was Introduced into Databases so that ?

• a) Keys can be provided to maintain security.
• b) Reading and writing are possible.
• c) All simultaneous transactions are prevented.
• d) Passwords can be provided to maintain security
• e) Consistency can be enforced.

Correct Answer – e) locking was introduced into databases to enforce consistency.

Explanation –
Locking is a mechanism to support serialization. If two-phase locking is used, serialization can be enforced. If transactions are serializable, then the results are consistent.

Question: Which of the Following Best Describes the Relation between ISO SQL and ORACLE’s SQL*PLUS?

• a) ORACLE SQL*PLUS is faster than ISO SQL
• b) ORACLE SQL*PLUS may be installed on a wider range of platforms
• c) ORACLE SQL*PLUS is an attempt to implement a superset of ISO SQL
• d) ORACLE SQL*PLUS is the industry-standard definition of ISO SQL
• e) ORACLE SQL*PLUS is a commercial product, and ISO SQL is freeware.

Correct Answer – c) ORACLE SQL*PLUS is an attempt to implement a superset of ISO SQL.

Explanation –
SQL*PLUS is ORACLE’s implementation of ISO SQL and includes some extensions to ISO SQL to utilize features of ORACLE better.

Consider the Following Database:

MOVIE(id,title,yr)
ACTOR(id,name)
CASTING(movieid,actorid)

Question: Identify the SQL Command Which Will Return the Titles of all 1959 Marilyn Monroe Films ?

• a) The following SQL…

SELECT title FROM movie, casting, actor
WHERE    movieid = movie.id
AND      name    = 'Marilyn Monroe'
;

• b) The following SQL…

SELECT title FROM movie, actor
WHERE    name    = 'Marilyn Monroe'
AND      yr = 1959
;

• c) The following SQL…

SELECT title FROM movie, casting, actor
WHERE    movieid  = movie.id
AND      actor.id = actorid
AND      name     = 'Marilyn Monroe'
AND      yr = 1959
;

• d) The following SQL…

SELECT title FROM movie, casting, actor
WHERE    movieid  = movie.id
AND      actor.id = actorid
AND      movie.yr = casting.yr
AND      name     = 'Marilyn Monroe'
AND      yr = 1959
;

• e) None of the above

Correct Answer –

SELECT title FROM movie, casting, actor
WHERE    movieid  = movie.id
AND      actor.id = actorid
AND      name     = 'Marilyn Monroe'
AND      yr = 1959
;

Explanation –
The database is really a many-to-many relation between MOVIE and ACTOR. CASTING is an intermediate entity type that is present only to allow the many-to-many relationship to be implemented.

The SELECT clause is correct, and the query uses all the three tables. There are two JOINs, and all the search criteria are fulfilled.

Question: Given a Relation Country(Name, Continent, Population) Which of the Following is a Valid SQL Statement?

• a) SELECT continent, population FROM country GROUP BY continent
• b) SELECT continent, SUM(population) FROM country GROUP BY continent
• c) SELECT name, population FROM country GROUP BY continent
• d) SELECT name, SUM(population) FROM country GROUP BY continent
• e) None of the above OR more than one of the above

Correct Answer –

SELECT continent, SUM(population) FROM country GROUP BY continent

Explanation –
This query will work correctly since the continent field is the correct choice as it is the “grouping” field. The population field has an “aggregating” function: SUM. Hence it will give the correct answer according to the question.

Question: Which One of the Following is Not a Method of Implementing Transactions at the Physical Level?

• a) Differential files
• b) Shadow-paging
• c) Log-files with deferred updates
• d) Log-files with immediate updates
• e) Branch and bound

Correct Answer – e) Branch and bound

Explanation –
Branch and bound is not a method of implementing transactions at the physical level.

Question: Which One of the Following Problems Can Occur due to Introducing Locks in a Concurrent Transaction Scenario?

• a) Information overwrite
• b) Loss of information
• c) Deadlock
• d) Lack of integrity
• e) None of the above

Correct Answer – c) Deadlock can occur due to introducing locks in a concurrent transaction scenario.

Explanation –
If a transaction needs access to an attribute that is locked, then that transaction is suspended until the attribute is available. However, if that transaction has already locked something which other transactions need, then it is possible to enter a state where the transactions of a system are all suspended, and the attributes which they are waiting on will never become available. This is called deadlock and can result when locking is introduced into a system.

Question: Which One of the Following Techniques is Sometimes used to Solve Integrity Problems in a Concurrent Transaction Scenario?

• a) First-come first-served
• b) First-fit
• c) Greedy algorithms
• d) Strassens’s algorithm
• e) Two-phase locking

Correct Answer – Two-phase locking techniques are sometimes used to solve integrity problems in a concurrent transaction scenario

Explanation –

To enforce serialization using locking, you must follow the rules for two-phase locking. Only then is serializability enforced.

Question: Out of the Given Algorithms, Which of the Following would Perform the Minimum Number of Rollbacks if the Transactions are of Longer Durations?

• a) Differential files
• b) Shadow-paging
• c)Log-files with deferred updates
• d)Log-files with immediate updates
• e)None of the above

Correct Answer – Log-files with immediate updates algorithms best suit long-lived transactions with relatively few rollbacks.

Explanation –
Making immediate updates to the database before the COMMIT means that changes are stored efficiently on the disk while the transaction is still running. This has the benefit of making all the changes persistent over reboots and keeping the transaction’s RAM usage to a minimum.

Question: Which one of the Following Requires Some Hardware Assistance to be Implemented Efficiently?

• a) Differential files
• b) Shadow-paging
• c) Log-files with deferred updates
• d) Log-files with immediate updates
• e) None of the above

Correct Answer – Shadow-paging requires some hardware assistance to be implemented in an efficient manner.

Explanation –
Shadow paging can be implemented in software, but one approach to implementing it is to base the algorithm on virtual paging, as present in most modern operating systems. There is usually operating system support for using virtual memory, and thus you get the management of shadow pages for free, simply by calling a shadow page a “virtual page”. Virtual memory managers usually run with less software overheads than a software-based approach to shadow paging.

Question: When a Transaction is Aborted?

• a) all users must be notified
• b) all changes it has made are immediately available to other transactions
• c) the modifications of all transactions currently running are also aborted
• d) it can abort transactions that have already been committed
• e) it releases all of its locks

Correct Answer – When a transaction is aborted, it releases all of its locks.

Explanation –

Transactions at the end of their lifespan must release all of their locks. Locks cannot be maintained outside the scope of a transaction. Thus, whether they abort or commit, all locks must be released.

Question: In Transaction, Cascade Rollback ?

• a) Can occur in systems that use deferred writeback
• b) Can occur in systems that use immediate writeback
• c) Occurs in systems that use the “waterfall” transaction management system
• d) Is a result of simultaneous transaction commits.
• e) None of the above

Correct Answer – In transactions, cascade rollback can occur in systems that use immediate writeback.

Explanation –
With immediate writeback, if a transaction updates an attribute in the database, the modification is immediately made to the database image on the disk. If another transaction then reads that information, it is basically reading information from a transaction that has not yet been committed. If the modifying transaction now aborts, not only do its modifications have to be undone, but the transactions which have read the modifications will have to be aborted as well. This may cause further aborts, or so-called “cascade rollbacks”.

Given the Following Transaction Schedule:

time	TRANSACTION 1	TRANSACTION 2	TRANSACTION 3
t1	read(A)
t2		read(B)	read(B)
t3	read(B)
t4		write( C )
t5			write(B)
t6			read(A)
t7			write(A)
t8	write(A)
t9	write( C )

Question: Which of the Following Precedence Graphs Depicts the Above Scenario?

• a) option A.

• b) option B.

• c) option C.

• d) option D.

• e) None of the above

Correct Answer – None of the above

Explanation –
To draw a precedence graph, you first draw three circles, one for each transaction. Next, you go through the schedule, drawing a line from Transaction A to Transaction B whenever;

B reads an attribute which A has written at a previous time
B write an attribute which A has written to at a previous time
B writes an attribute which A has read from at a previous time
done.

As we can see, the following is the correct graph. For eg, there is a RW conflict from $t1$ of Transaction $T1$ to $t7$ timestamp of transaction $T3$, hence we made an arrow from $T1$ to $T3$. Similarly, we can make the arrow for other $WR$, $RW$, and $WR$ conflicts.

Question: Consider a database that does not have concurrency control. Which consistency problem can occur if the following transaction happens in this database?

Time	Transaction A	Transaction B
t1	read R
t2		read R
t3	write R
t4		write R

• a) Dirty Read
• b) Uncommitted Dependency
• c) Inconsistent Analysis
• d) Lost Update
• e) Deadlock

Correct Answer – Lost Update is the correct answer

Explanation –
In a lost update, a modification is lost due to another transaction overwriting the modification.

A $timetable$ database is required for a University Department. Each taught event is part of a module; each event will have exactly one member of staff associated and several individual students. Each event takes place in a single weekly time slot. Each time slot has a day of the week and a time of day associated.

Question: Which of the following is the best approach to implementing the attends relation using a relational database system?

• a) A repeating field attends is included as part of the event table
• b) A repeating field attends is included as part of the student table
• c) A secondary attends key is added to the event table
• d) A table attends contains an event/student pair for every instance of a student attending an event
• e) None of the above

Correct Answer – A table attends contains an event/student pair for every instance of a student attending an event

Explanation –

As a student can attend multiple events, and each event may involve multiple students, then the relationship must be many to many. This cannot be implemented in a relational database and must be implemented using one-to-many and many-to-one relationships and an intermediate relation that contains the keys of the event/student combination as foreign keys.

Question: A given relation is known to be in third normal form. Select the statement which can be inferred from this ?

• a) All attributes contribute to the primary key
• b) Each non-key attribute determines the primary key
• c) Each non-key attribute is determined by the primary key
• d) Every determinant is a candidate’s key
• e) The relation is not in the fourth normal form.

Correct Answer – In the third normal form, each non-key attribute is determined by the primary key.

Explanation –

In the third normal form, each non-key attribute is determined by the primary key. If we don’t have transitive Dependency and the relationship follows the second normal form, then it is said to be in the third normal form.

Question: There are two relations $X$ and $Y$. Relation $X$ has arity $1$ and cardinality $2$, relation $Y$ has arity $3$ and cardinality $4$. What will be the result of the SQL statement SELECT COUNT(*) FROM X, Y?

• a) $4$
• b) $6$
• c) $8$
• d) Cannot be calculated from the information given
• e) None of the above.

Correct Answer – $8$ is the correct answer

Explanation –

The SQL produces the Cartesian product of the two tables. The number of elements in the Cartesian product is the number of rows of $X$ multiplied by the number of rows of $Y$. The cardinality is the number of rows.

Question: Which of the following is generally a benefit of normalization?

• a) Performance is improved
• b) Insertion anomalies are avoided
• c) Selection anomalies are avoided
• d) the number of tables is reduced
• e) None of the above.

Correct Answer – Insertion anomalies are avoided by normalization.

Explanation –
Insertion anomalies are avoided by normalization.

Question: A lack of normalization can lead to one of the following problems?

• a) Lost Updates
• b) Deletion of data
• c) Insertion problems
• d) Deferred updates
• e) Deadlock
  
  Correct Answer – A lack of normalisation can lead to insertion problems.

Explanation –
Inserting data into an unnormalized system can result in the same data being entered in multiple tables. This gives rise to the possibility of data for the same object being entered “slightly differently” in more than one table. For instance, if a person’s surname appeared in more than one table, and that person then changed his name, this change would have to be done in all tables where the name appeared. Ensuring this can be difficult, so it is better to avoid such duplicated data by normalizing the database design.

Question: To transform a relation from the first normal form to the second normal form, we must remove which one of the following?

• a) All partial-key dependencies
• b) All inverse partial-key dependencies
• c) All repeating groups
• d) All transitive dependencies
• e) None of the above

Correct Answer – All partial-key dependencies must be removed To transform a relation from the first normal form to the second normal form

Explanation –
A relation is said to be in second normal form if it doesn’t have partial Dependency and is in first normal form. Partial Dependency occurs when an attribute in a table depends on only a part of the primary key and not the whole key.

Question: To transform a relation from second normal form to third normal form, we must remove which one of the following?

• a) All partial-key dependencies
• b) All inverse partial-key dependencies
• c) All repeating groups
• d) All transitive dependencies
• e) None of the above

Correct Answer – All transitive dependencies must be removed To transform a relation from the second normal form to the third normal form

Explanation –
A relation is said to be in third normal form if it doesn’t have transitive Dependency and is in second normal form. When we have a dependency in a table such that a non-prime attribute is dependent only on another non-prime attribute(s), the relation is said to have a transitive dependency.

Question: Each of the following is an argument that might be used to support the use of relations that are not fully normalized. Select the weakest argument.

• a) A fully normalized database may perform too slowly
• b) Full normalisation may compromise existing applications/systems
• c) A fully normalized database may have too many tables
• d) Full normalization may make some queries too complicated
• e) A fully normalized database may result in tables that are too large

Correct Answer – A fully normalized database may result in tables that are too large.

Explanation –
Table size is reduced by normalization, not increased. But how large is too large is not defined in the argument.

Consider the following functional dependencies

$a,b \rightarrow c,d$

$e,g,h \rightarrow f,j$

$a,c \rightarrow b,d$

$p,q \rightarrow r,s$

$e,f,g \rightarrow h,i$

$s \rightarrow t$

$f,g \rightarrow j$

$q \rightarrow u$

$g,h \rightarrow i$

Question: Which of the following relational schemas might be the result of normalising R(s,q,t,u)?

• a) The schema R1(s,q) R2(s,t) R3(q,u)
• b) The schema R1(s,q) R2(q,t) R3(t,u)
• c) The schema R1(s,q) R2(s,t) R3(q,u)
• d) The schema R1(s,q,t) R2(s,q,u)
• e) The schema R(s,q,t,u)

Correct Answer – The schema $R1(s,q)$ $R2(s,t)$ $R3(q,u)$ might be the result of normalising $R(s,q,t,u)$

Explanation –
The whole key $s,q$ is not needed to determine $t$ or $u$, so there are partial key dependences. Here, $s\rightarrow t$, and $q \rightarrow u$, so we can take $t$ and $u$ out of $R$.

Question: If the relation $W$ was normalized to BCNF, select the best-resulting relations of that process.

$W(a,b,c,d,e)$
$c \rightarrow a$

• a) $W(c,b,d,e)$ $W1(c,a)$
• b) $W(a,b,c,d,e)$ $W1(c,a)$
• c) $W(a,b,d,e)$ $W1(c,a)$
• d) $W(b,c,d,e)$$W1(c,a)$
• e) None of the above

Correct Answer – $W(c,b,d,e)$ $W1(c,a)$ is the correct answer

Explanation –
You would rewrite $W$ with a better key $(c,b)$, since $c\rightarrow$ a means that $c$ is a better key than $a$. After that, the normalization is straightforward, and the result is that shown here.

Question: The option of dropping a secondary index to a table is considered. Which of the following is most likely to be a consequence of dropping the index?

• a) Certain seek operations may be faster.
• b) Certain foreign key relations may not be maintained
• c) More disk space may be required
• d) Certain insertions may be faster
• e) Certain updates may be slower

Correct Answer – Certain insertions may be faster as a consequence of dropping the index if the option of dropping a secondary index to a table is being considered.

Explanation –
Indexes must be maintained when inserting a record. If an index is dropped, then the insertion should be faster.

When accessing a disk block, the seek time

• a) is insignificant in comparison to transfer times
• b) is about the same as transfer times
• c) greatly exceeds transfer times
• d) is the time taken to search for data in a sorted list of database rows
• e) is measured in nanoseconds

Correct Answer – When accessing a disk block, the seek time greatly exceeds transfer times.

Explanation –
Disk transfers are purely electronic and are the process of transferring data from the disk drive to the computer and vice-versa. Disk transfer times are usually measured in nano- or microseconds. Seek time is the average time taken to move the drive head to the track on the disk which holds the data you need. It is mechanical (involving moving a drive head) and thus slow. It is often measured in milliseconds. Milliseconds are much larger than microseconds, and therefore seek times do significantly exceed transfer times.

Question: Hash-table insertions

• a) avoid hash collisions by manipulating the foreign keys
• b) might use hash-chains to allow hash-collisions
• c) use balanced binary trees to allow hash-collisions
• d) use primary keys to avoid hash-collisions
• e) become unusable if there are any hash-collisions

Correct Answer – Hash-table insertions might use hash-chains to allow hash collisions.

Explanation –
In hash tables, each entry has a hash value calculated from it using the primary keys. A collision occurs when two entries have the same hash value. One solution to a collision is to put hash collision entries one after another in a linked list. These lists are called hash-chains.

An example is shown below:

Here $50$, $85$, and $92$ are forming hash-chains mapping to hash value $1$

Question: Which of the following is not usually part of the responsibilities of a database administrator?

• a) Approving structural changes to the database
• b) Designing data entry screens
• c) Ensuring that an adequate backup regime is in place
• d) Issuing accounts to users
• e) Monitoring the Performance of the system.

Correct Answer – Designing data entry screens is not usually part of the responsibilities of a database administrator.

Explanation –
The DBA is unlikely to be involved in such a detailed design activity.

Question: Which of the following is the most important advantage of an “enterprise-wide” relational database management system compared to an ad-hoc arrangement?

• a) Access to data will be faster.
• b) Confidentiality is assured.
• c) Disk space is used more efficiently.
• d) Inconsistencies are avoided.
• e) Network traffic is reduced.

Correct Answer – d) Inconsistencies are avoided in an “enterprise-wide” relational database management system compared to an ad-hoc arrangement

Explanation –
An enterprise-wide RDBS eliminates redundancy (copies of data). This, in turn, avoids the possible inconsistency in the data.

Question: Which of the following best describes the internal level of the ANSI/SPARC three-level architecture?

• a) The internal level is concerned with the data as seen by individuals internal to the enterprise.
• b) The internal level concerns how stored fields are represented and which indices exist.
• c) The internal level is concerned with the layout of records and their locations within disk blocks.
• d) The internal level concerns the users’ view of the data.
• e) The internal level provides a conceptual view of the data structure

Correct Answer – b) The internal level concerns the representation of the stored fields and which indices exist.

Explanation –
The internal level concerns the representation of the stored fields and which indices exist.

Question: Which of the following best describes the costs of the operations insert, delete and seek on a table where records are stored in primary key order? Deleted records may be “flagged”.

• a) insert and delete are cheap, seek is expensive
• b) insert and delete are expensive, seek is cheap
• c) insert is expensive, delete and seek are cheap.
• d) insert, delete and seek are all cheap
• e) insert, delete and seek are all expensive

Correct Answer – c) insert is expensive, delete and seek are cheap is the correct answer.

Explanation –

If the records are stored in primary key order where deleted records may be “flagged”, then

• insert will be expensive
• delete will be cheap
• seek will be cheap

Since for insertion, we need to search the primary key and then insert the new tuple, and for deletion, we don’t need to do since the deleted records are already flagged so they can be directly accessed.

Question: Which of the following best describes the relationship between C and SQL?

• a) SQL can be executed from within C programs by means of JDBC
• b) C is compatible with the specialist database language SQL
• c) SQL is incompatible with the programming language C
• d) There exist mechanisms by which C statements can be embedded in SQL program
• e) There exist mechanisms by which SQL statements can be embedded in C

Correct Answer – e) There exist mechanisms by which SQL statements can be embedded in C is the correct answer.

Explanation –
Embedded SQL is the method of embedding SQL statements into a C program. This is indeed possible and indeed common.

Question: Which forms have a relation that contains information about a single entity?

• a) 4NF
• b) 2NF
• c) 5NF
• d) 3NF

Correct Answer – a) 4NF relation contains information about a single entity

Explanation –

If and only if, for each of its non-trivial multivalued dependencies $X \twoheadrightarrow Y$, a table is in 4NF. X is a superkey—that is, X is either a candidate key or a superset thereof.

Questiion: After groups have been established, SQL applies predicates in the _ clause, allowing aggregate functions to be used.

• a) Where
• b) Having
• c) Group by
• d) With

Correct Answer – d) After groups have been established, SQL applies predicates in the with clause, allowing aggregate functions to be used.

Explanation –
The “with” clause was introduced to reduce the complexity of the queries, which are lengthy. The “with” clause allows you to define a temporary relation whose definition is only visible to the query in which it appears.

Question: Which of the following is not the utility of DBMS?

i) Backup ii) Loading iii) Process Organization iv) File organization

• a) i, ii, and iv only
• b) i, ii and iii only
• c) i, iii and iv only
• d) All i, ii, iii, and iv

Correct Answer – a) Backup, Loading, and File organization are not the utility of DBMS

Explanation –
A backup utility is used to create a copy of the DB as a backup. Loading utility is used to load an existing file. File organization is used to relocate the files and create a new access path. Processing is not a utility

Question: For designing a normal RDBMS, which of the following normal form is considered adequate?

• a) 4NF
• b) 3NF
• c) 2NF
• d) 5NF

Correct Answer – b) Third normal form is considered an adequate normal form

Explanation –
Because most 3NF tables are free of insertion, update, and deletion anomalies, an RDBMS table is sometimes regarded as “normalized” if it is in the Third Normal Form.

Question: Which of the following command is correct to delete the values in the relation teaches?

• a) Delete from teaches;
• b) Delete from teaches where Id =’Null’;
• c) Remove table teaches;
• d) Drop table teaches;

Correct Answer – a) Delete from teaches; is the correct answer to delete the values in the relation teaches

Explanation –

To delete the entries from the table Delete from the table command should be used.

Question: What does an RDBMS consist of?

• a) Collection of Records
• b) Collection of Keys
• c) Collection of Tables
• d) Collection of Fields

Correct Answer – c) RDMS consists of a collection of tables

Explanation –
RDBMS consists of a collection of tables, i.e., the data is organized in tabular format. The relation columns are known as Fields, and rows of the relation are known as fields. Constraints in a relationship are known as Keys.

Question: Which of the following is a function of the DBMS?

• a) Storing data
• b) Providing multi-users access control
• c) Data Integrity
• d) All of the above

Correct Answer – d) Storing data, Data Integrity, and providing multi-user access control is the function of the DBMS.

Explanation –
The purpose of creating DBMS was to store the data. The data stored in the database management system can be can accessed by multiple users if the access is provided. The data stored will be accurate and complete hence providing data integrity.

Data integrity in the database is the correctness, consistency, and completeness of data. Data integrity is enforced using the following three integrity constraints:

• Entity Integrity – This is related to the concept of primary keys. All tables should have their own primary keys, which should uniquely identify a row and not be NULL.
• Referential Integrity – This is related to the concept of foreign keys. A foreign key is a key of a relationship that is referred to in another relation.
• Domain Integrity – This means that there should be a defined domain for all the columns in a database

Question: Which of the following is not a feature of DBMS?

• a) Minimum Duplication and Redundancy of Data
• b) High Level of Security
• c) Single-user Access only
• d) Support ACID Property

Correct Answer – c) Single-user access is not a feature of DBMS.

Explanation –
The important features of a database management system are:

1. Minimum Duplication and Redundancy of Data
2. High Level of Security
3. Mulitple-user Access
4. Support ACID Property
   a. A – Atomiticity – the entire transaction happens at once or doesn’t happen at all.
   b. C – Consistency – The database is consistent before and after the transaction.
   c. I – Isolation – multiple transactions occur independently without interference
   d. D – Durability – The changes of the successful transaction occurs even if there is a system failure.