SQL Server Database fundamental Interview questions covers the basics of a relational database engine independent of a vendor. This is just to get you started with the interview questions.
Question 1: What is a Database?
A database is a collection of information organized in rows and columns in most cases. The data is stored in an optimized way for a computer application to quickly and efficiently select data in a desired presentable format. The primary storage mechanism of a database is in an electronic format stored on a computer file-system. With multiple abbreviations like DB, Data, Records etc. The primary job of a Database is to store data.
Databases can be organized by fields, records, and files. A good example is a Telephone Directory which contains a collection of records, each of which consists of different fields like name, address, and telephone number.
QUESTION 2: WHAT IS A DBMS?
Database management system is an Application which enables you to extract, select, insert, update and delete data. These systems vary in size and functionality to address different needs. For example an app can store user data locally on a phone, whereas a banking system can store data on an enterprise DBMS running on mainframe computers. Internally, a DBMS can store data in a variety of ways like Flat Files, Relational, Hierarchical or non-structured. There can be different systems to manage data for Transactional processing or reporting. In DBMS, the Application developer is responsible for maintaining ACID properties and data consistency.
Examples: Flat Files, Excel Spreadsheets, Microsoft Access.
QUESTION 3: WHAT IS A RDBMS?
RDBMS or Relational Data Base Management Systems are a type of DBMS (Database management systems) that records and maintains data with indices in the tables. You can create Relationships based upon assumptions about how data is related and will be extracted from the database. One of the notable features of relational systems is that a single database can be spread across several tables.
In a relational database, relationships are created between tables. These inter dependencies are created by data values rather than by pointers. These are created to maintain data consistency and referential integrity with ACID properties.
Examples: Microsoft SQL Server, Oracle Database, IBM DB2, Sybase etc.
Question 4: What are the Properties of the Relational Tables?
RDBMS have six properties and are as follows:
- Values are atomic.
- Column values are of the same kind.
- Each row is unique.
- The sequence of columns is insignificant.
- The sequence of rows is insignificant.
- Each column must have a unique name.
Question 5: What is Normalization?
Database normalization is a technique of organizing the data in the database using a design methodology to reduce or minimize Data Redundancy and the process is called normalization. Generally while performing a Normalization process you would ideally need to divide a database into two or more tables and define relationships between the tables. By doing this, it allows Users and Applications to isolate data such that any additions, deletions, and data modifications of a row or a column can be made in one table and then propagated through the rest of the database based on the defined relationships. Normalization allows you to avoid data loss possibilities during Insert, Update and Deletion and avoid any Anomalies.
question 6: What are the normalization rules?
There are four types of normalization forms:
- First Normal Form (1NF): This rules tells that you must Eliminate Repeating Groups by making a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
2. Second Normal Form (2NF): In order to remove redundant data, If an attribute depends on only part of a multi-valued key, then remove it to a separate table.
3. Third Normal Form (3NF): The Third Normal form applies to tables where you should Eliminate Columns Not Dependent On Key, else remove then to a separate table.
4. Boyce and Codd Normal Form (BCNF): This is an enhanced version of the Third Normal form where if there are non-trivial dependencies between candidate key attributes, then separate them out into distinct tables.
Other Normalization Forms:
- 5NF: Isolate Semantically Related Multiple Relationships
- ONF: Optimal Normal Form
- DKNF: Domain-Key Normal Form