I will be sharing a series of interview questions and answers. The topics will be focused on:
- Data Modeling
- Transact SQL
- Data Warehouse Design
- SQL Server Integration Services (SSIS)
- SQL Server Reporting Services (SSRS)
- SQL Server Analysis Services (SSAS)
Please feel free to share your ideas with me on how to improve this further. I will also update this as I come up with more questions and answers.
Data Modeling
Describe a primary key.
- A primary key is a column that uniquely identifies a row in a table.
- A table can only have one primary key
- A primary key cannot contain any NULL value
- You can combine multiple keys together to generate a single primary key
- A unique clustered index is created when a primary key is created
Describe a unique key
- A unique key is a column that uniquely identifies a row in a table
- Unlike a primary key, a unique key can accept up to one NULL value
- Unlike a primary key, a table can have more than one unique key
- Unique non-clustered index is created when a unique key is created
Describe a foreign key
- A foreign key helps to enforce referential integrity between related tables
- It is a column in a table that references a primary key in another table
- A foreign key can reference itself, that is the foreign key is acting as its own primary key (in the case of a unary degree relationship)
- NULLs are allowed in a foreign key (not recommended)
What are the types of relationships in database design?
- One-to-one
- One-to-many
- Many-to-many
- One-to-fixed
What is Normalization?
Normalization is simply a step by step process of efficiently organizing data in a database based on some normalization rules.
Advantages of normalizing include:
· Getting rid of insert, update and delete anomalies
· Helps to improve the performance of DML operations (insert, update, delete)
· Eliminating data redundancy
Normalization comes with some drawbacks like reducing the performance of read operations (SELECT)
What are the most common degrees of normalization?
· First Normal Form (1NF): First normal form sets the very basic rules for an organized database. It is the most basic form of normalization
o There should be no repeating rows. To ensure this, it means the table must have a primary key.
o There should be no repeating columns. This means there cannot be two different columns with the same information.
o All the attributes should be single-valued
o All composite attributes should be broken down into its minimal component
· Second Normal Form (2NF): A table is in its second normal form when
o It satisfies the conditions for a first normal form
o There are no partial dependencies. If any exist, they must be removed.
· Third Normal Form (3NF): A table is in its third normal form when
o It satisfies the conditions for a second normal form
o There are no transitive dependencies. It any exist, they must be removed.
Abayomi Obawomiye
I am a SQL Server/Business Intelligence Developer in Chandler Arizona. I share resources on beginner & advanced concepts in SQL Server.
Read Also
0
0
votes
Article Rating
Subscribe
Login
0 Comments
Inline Feedbacks
View all comments