General Database

Question 1

What Is Relationship? How Many Types of Relationships Are There?

Answer 1

The relationship can be defined as the connection between more than one tables in the database. There are 4 types of relationships:
•	One to One Relationship
•	Many to One Relationship
•	Many to Many Relationship
•	One to Many Relationship

Question 2

What is the Database transaction?

Answer 2

Sequence of operation performed which changes the consistent state of the database to another is known as the database transaction. After the completion of the transaction, either the successful completion is reflected in the system or the transaction fails and no change is reflected.

Question 3

What are some of the Drawbacks of Indexes?

Answer 3

• Indexes increase performance of select query, they can also decrease performance of data manipulation.
• Many indexes on a table can slow down INSERTS and DELETES drastically
• The more the indexes on the table, the more time inserts and delete will take.
• Similarly, every change to an indexed column will need a change to index.
• So we need to choose the index very carefully and drop which are not in use.
• Though the extra space occupied by indexes is also a consideration, it may not matter much since the cost of data storage has declined substantially.

Question 4

Difference between Locking, Blocking and Deadlocking?

Answer 4

• Locking: Locking occurs when a connection needs access to a piece of data in a database and it locks it for certain use so that no other transaction is able to access it.
• Blocking: Blocking occurs when a transaction tries to acquire an incompatible lock on a resource that another transaction has already locked. The blocked transaction remains blocked until the blocking transaction releases the lock.
• Deadlocking: Deadlocking occurs when two or more transactions have a resource locked, and each transaction requests a lock on the resource that another transaction has already locked. Neither of the transactions here can move forward, as each one is waiting for the other to release the lock.

Question 5

What is the difference between DELETE and TRUNCATE commands?

Answer 5

DELETE command is used to remove rows from the table, and WHERE clause can be used for conditional set of parameters. Commit and Rollback can be performed after delete statement.
TRUNCATE removes all rows from the table. Truncate operation cannot be rolled back.

Question 6

What is the difference between primary key and unique constraints?

Answer 6

Primary key cannot have NULL value, the unique constraints can have NULL values. There is only one primary key in a table, but there can be multiple unique constrains.

Question 7

What are the uses of view?

Answer 7

1) Views can represent a subset of the data contained in a table; consequently, a view can limit the degree of exposure of the underlying tables to the outer world: a given user may have permission to query the view, while denied access to the rest of the base table.
2) Views can join and simplify multiple tables into a single virtual table
3) Views can act as aggregated tables, where the database engine aggregates data (sum, average etc.) and presents the calculated results as part of the data
4) Views can hide the complexity of data; for example, a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table
5) Views take very little space to store; the database contains only the definition of a view, not a copy of all the data which it presents.
6) Depending on the SQL engine used, views can provide extra security