Quick reference in DBMS, Database management systems quick reference section, quick reference to multiple basic questions in DBMS, database questions and answers
Quick Reference
Is it mandatory to give value to primary key when record is inserted?
Yes. Primary key is an attribute with the following property;
PRIMARY KEY = UNIQUE + NOT NULL
Due to NOT NULL property, we need to include data for primary key attribute while inserting a record.
Two different ways to insert primary key in table
- You can include a primary key in the CREATE TABLE statement while creating a table.
- You can include a primary in the ALTER TABLE statement using ADD PRIMARY KEY(column(s)).
Example (Oracle SQL):
- CREATE TABLE x (abc CHAR(10) PRIMARY KEY, xyz VARCHAR(20));
- ALTER TABLE z ADD
PRIMARY KEY(Sno);
3 different techniques to achieve 1nf with suitable examples
- Reducing ER diagram into database schema
- Flatten the records of an un-normalized table
- Decompose an un-normalized table into one or more tables.
What is the name of the schema that contains definitions for schema objects in a catalog?
Data dictionary is the name of schema.
Name for data dictionary schema by various RDBMSs
- Oracle – Data dictionary
- MySQL – Information Schema
- IBM DB2 – SYSCAT schema
What will happen if locks are released just before commit? Will other transactions interfere and acid property is affected?
If the locks on data items are released before commit, there is a possibility that the released data items values (which are uncommitted) may be consumed by other ongoing transactions (if any). This may result in inconsistent database.
The person involved with database design is called.
Usually DBA designs a database. In case of very large organizations, it may be divided between various DBAs with names like “database designer” or “database architect”.
Advantages and disadvantages of using foreign key in same table as primary key or in different table
Give pros and cons of lock based protocols for concurrency control
How to avoid lost update problem?
Let us assume two transactions A and B are working on same data item Q. If both of them are trying to update the value of Q, this will result in lost update problem. To avoid this situation, the transactions that are working on same data item must update the data item after the other transactions commit.
How to avoid dirty read problem?
Reading the value of a data item that was produced by an uncommitted transaction is referred as dirty read problem. This will be avoided if we permit transactions to read the values that are produced by committed transactions.
What is the solution for lost update and dirty read problems?
Transactions should be permitted to read/use the data items that are produced by committed transactions.
Where do we use BCNF? Why?
We use BCNF when there are more than one candidate keys in a table and they are overlapping.
BCNF is a stronger version of 3NF because it restricts both prime and non-prime attributes, while 3NF only restricts non-prime attributes.
Is deadlock prevention preferred over deadlock detection in database concurrency?
No. Deadlock prevention is costly when compared with the cost of deadlock detection. Hence, deadlock detection is preferred most of the time.
What's the difference between minimal cover and closure of a functional dependency?
A minimal cover of a set of functional dependencies (FDs) F is a minimal set of FDs Fmin that is equivalent to F.
Closure of set F of FDs is a set of all possible FDs (denoted as F+) that are logically implied by F. Closure is derived using Armstrong’s axioms and additional rules.
Simply put, for a set F, minimal cover includes only required number of FDs that imply F, whereas closure has redundant FDs that imply F.
Compare and contrast various indexing techniques in different database systems
To insert date and time in the database data type is used
How can we delete particularly a second row from a table in a single line SQL command?
*********************