- What is SQL?
SQL is an acronym for Structured Query Language.
- What is Normalization, and explain the different levels.
Normalization is a method for reducing redundancy in a database. The idea is that data is grouped together in such a manner to enhance a given performance goal.
1NF- Removes any duplicated attributes.
2NF - Should be 1NF and each non-key should be dependent on the primary key.
3NF - Should be 2NF and all non-key attributes that are not dependent on the primary key should be removed.
- What is Denormalization and when should it be applied?
Denormalization is the reverse of normalization. It increases query performance by reducing the number of joins by consolidating data.
- What are the database relationship types?
One-To-One - For each instance in the first entity there is one and only one matching instance in the second entity, and vice versa.
One-To-Many - For each instance in the first entity, there can be more than one matching instance in the second entity. The second entity will have one and only one matching instance in in the first entity.
Many-To_Many - For each instance in the first entity, there can be one or more matching instances in the second entity, and vice versa.
- What is the difference between a Primary and a Unique Key?
A Primary Key denotes a default clustered index, and does not allow nulls.
A Unique Key denotes a non-clustered index, and allows one null.
- What are Candidate, Alternate and Composite Keys?
Candidate Key - a key that can uniquely identify a row in a table
Alternate Key - If the table has more than one candidate keys and one becomes a primary key, the rest become alternate keys.
Composite Key - More than one key to uniquely define a table row.
- What is a Transaction?
A Transaction is a work wrapper that creates a reversible work instance as long as the transaction has not been committed. Transactions conform to the ACID principle.
- What is ACID?
ACID is the acronym for Atomicy, Consistency, Isolation and Durability.
- What are the primary steps in data modeling?
Logical - Planning, Analysis and Design
Physical - Design,Implementation and Maintenance
- What is a cursor?
A cursor is a mechanism that allows the developer to go throw a data result set row by row.
- What are the different types of cursors?
- Why should you avoid using cursors?
(To be continued)