Exam 98-364: Database Fundamentals Interview Questions

Preparing for an interview is as important as preparing for an exam. Therefore, preparing for an interview takes a lot more practice and confidence to ace any exam. You have to make the best first impression. So to help our candidates to prepare well for the interview, we have tried our best to present you with the best and expert-revised interview questions. Moreover, we have covered all questions from basic 98-364 interview questions to intermediate and advance levels. Therefore, we highly recommend the aspirants prepare with the best and achieve the best. But first, let’s take an overview of Exam 98-364: Database Fundamentals.

Overview

Exam 98-364: Database Fundamentals is intended for candidates who are seeking to enhance their introductory knowledge of and skills with databases, including relational databases, such as Microsoft SQL Server. Moreover, the candidates are required to take a few courses and tutorials available on MSDN and in Microsoft Visual Studio. Although minimal hands-on experience with the technologies is recommended, job experience is not assumed for these exams.

Now, let’s begin with the Exam 98-364: Database Fundamentals Interview Questions.

1. Who is a Database Administrator?

A Database Administrator is responsible for significant databases that span over a number of platforms and environments. They are responsible for building complex, highly scalable databases that are able to fulfill the different business needs and security requirements. Moreover, they should know optimizing, maintaining, and troubleshooting databases, designing archival, data distribution, and high availability solutions. A Database Administrator’s primary responsibility is to make sure the data is available, protected from loss or corruption, and easily accessible when needed.

2. What do you understand by heterogeneous data?

Heterogeneous data are the data types with high variability of data formats. They are possibly ambiguous and low quality due to missing values, high data redundancy, and untruthfulness. It is difficult to integrate heterogeneous data to meet the business information demands. This heterogeneity means that a single data store is usually not the best approach.

3. What do you understand by RDBMS?

The RDBMS model is very useful and powerful database management system when strong consistency guarantees are important where all changes are atomic, and transactions always leave the data in a consistent state. However, an RDBMS generally cannot scale out horizontally without sharing the data in some way. Also, the data in an RDBMS must be normalized, which isn’t appropriate for every data set.

4. What are the assets of using RDBMS?

• Firstly, Records are frequently created and updated.
• Secondly, Multiple operations have to be completed in a single transaction.
• Thirdly, Relationships are enforced using database constraints.
• Lastly, Indexes are used to optimize query performance.

5. What is the data type in RDBMS?

• Data is highly normalized.
• Database schemas are required and enforced.
• Many-to-many relationships between data entities in the database.
• Constraints are defined in the schema and imposed on any data in the database.
• Data requires high integrity.
• Data requires strong consistency.
• The size of individual data entries is small to medium-sized.

6. Give some examples of RDBMS?

• Inventory management
• Order management
• Reporting database
• Accounting

7. What is the use of Key/value stores?

A key/value store associates each data value with a unique key. Most key/value stores only support simple query, insert, and delete operations. Key/value stores are highly optimized for applications performing simple lookups, but are not suitable if you need to query data across different key/value stores. Key/value stores are also not optimized for querying by value.

8. What is the data type used for Key/value stores?

• Each key is associated with a single value.
• There is no schema enforcement.
• No relationships between entities.

9. Give few examples of Key/value stores?

• Firstly, Data caching
• Secondly, Session management
• Thirdly, User preference and profile management
• Lastly, Product recommendation and ad serving

10. Define Document database?

A document database stores a collection of documents, where each document consists of named fields and data. The data can be simple values or complex elements such as lists and child collections. Documents are retrieved by unique keys.

11. What is the data type used for Document database?

• Firstly, Data can be managed in a de-normalized way.
• Secondly, The size of individual document data is relatively small.
• Thirdly, Each document type can use its own schema.
• Fourthly, Documents can include optional fields.
• Lastly, Document data is semi-structured, meaning that the data types of each field are not strictly defined.

12. Give few examples of Document database?

• Product catalog
• Content management
• Inventory management

13. What do you understand by Graph databases?

A graph database consists of two types of information, nodes, and edges. Edges specify relationships between nodes. Nodes and edges can have properties that provide information about that node or edge, similar to columns in a table. Edges can also have a direction indicating the nature of the relationship. Graph databases can efficiently perform queries across the network of nodes and edges and analyze the relationships between entities.

14. What is the data type used for Graph databases?

• Nodes and relationships.
• Nodes are similar to table rows or JSON documents.
• Relationships are just as important as nodes and are exposed directly in the query language.
• Composite objects, such as a person with multiple phone numbers, tend to be broken into separate, smaller nodes, combined with traversable relationships

15. Give few examples of Graph database?

• Organization charts
• Social graphs
• Fraud detection
• Recommendation engines

16. Define Normalizations?

Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency.

17. What are the features of First normal form?

• Firstly, Eliminate repeating groups in individual tables.
• Secondly, Create a separate table for each set of related data.
• Lastly, Identify each set of related data with a primary key.

18. What is a Second normal form?

• Firstly, it Creates separate tables for sets of values that apply to multiple records.
• Lastly, Relate these tables with a foreign key.

19. Define Third normal form?

The Third normal form eliminate fields that do not depend on the key. It is more feasible to apply third normal form only to data that changes frequently. If some dependent fields remain, design your application to require the user to verify all related fields when any one is changed.

20. What is a Primary key?

A table typically has a column or combination of columns that contain values that uniquely identify each row in the table. This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table.

21. Give some features of Primary keys?

• Firstly, a table can contain only one primary key constraint.
• Secondly, a primary key cannot exceed 16 columns and a total key length of 900 bytes.
• Thirdly, the index generated by a primary key constraint cannot cause the number of indexes on the table to exceed 999 nonclustered indexes and 1 clustered index.
• Fourthly, if clustered or nonclustered is not specified for a primary key constraint, clustered is used if there no clustered index on the table.
• Fifthly, all columns defined within a primary key constraint must be defined as not null. If nullability is not specified, all columns participating in a primary key constraint have their nullability set to not null.
• Lastly, if a primary key is defined on a CLR user-defined type column, the implementation of the type must support binary ordering.

22. What is a Foreign key?

A foreign key (FK) is a column or combination of columns which is used to establish and enforce a link between the data in two tables to control the data that can be stored in the foreign key table.

23. What are the two authentication modes supported by SQL Server?

SQL Server supports two authentication modes, Windows authentication mode and mixed mode.

24. What do you understand by Windows authentication mode?

Windows authentication mode is often referred as an integrated security because this SQL Server security model is tightly integrated with Windows. Specific Windows user and group accounts are trusted to log in to SQL Server. Windows users who have already been authenticated do not have to present additional credentials.

25. What is mixed mode?

Mixed mode supports authentication served by both Windows and SQL Server. Moreover, User name and password pairs are maintained within SQL Server.

26. Why Windows authentication is better than mixed mode?

Windows authentication is usually the best choice because:

• Firstly, there is a domain controller.
• Secondly, the application and the database are on the same computer.
• Lastly, you can use an instance of SQL Server Express or LocalDB.

27. Explain the three Login Types supported by SQL Server?

SQL Server supports three types of logins:

• A local Windows user account or trusted domain account. SQL Server relies on Windows to authenticate the Windows user accounts.
• Windows group. Granting access to a Windows group grants access to all Windows user logins that are members of the group.
• SQL Server login. SQL Server stores both the username and a hash of the password in the master database, by using internal authentication methods to verify login attempts.

28. Explain the CLR Integration Security in SQL Server?

 CLR integration allows its users to write stored procedures, triggers, user-defined types, user-defined functions, user-defined aggregates, and streaming table-valued functions, using any .NET Framework language, such as Microsoft Visual Basic .NET or Microsoft Visual C#. The CLR supports a security model called code access security (CAS) for managed code.

29. What is the use of Column-family databases?

A column-family database organizes data into rows and columns. In other words, a column-family database can appear very similar to a relational database, at least conceptually. The real power of a column-family database lies in its denormalized approach to structuring sparse data.

30. What is the data type used by Column-family database?

• Firstly, data is stored in tables consisting of a key column and one or more column families.
• Secondly, specific columns can vary by individual rows.
• Thirdly, individual cells are accessed via getting and put commands
• Lastly, multiple rows are returned using a scan command.

31. Give few examples of Column-family database?

• Telemetry
• Messaging
• Social media analytics
• Web analytics
• Activity monitoring
• Weather and other time-series data

32. What are Search Engine Databases?

A search engine database allows applications to search for information held in external data stores. A search engine database can index massive volumes of data and provide near real-time access to these indexes.

33. What is the data type used in Search Engine Databases?

• Firstly, Semi-structured or unstructured text
• Secondly, Text with reference to structured data

34. Give few examples of Search Engine Databases?

• Product catalogs
• Site search
• Logging

35. Explain Time series databases?

Time series data is a set of values organized by time. Time series databases basically collect large amount of data in real time from a large number of sources. Updates are rare, and deletes are often done as bulk operations. Although the records written to a time-series database are generally small, there are often a large number of records, and total data size can grow rapidly.

36. What is the data type used in Time series databases?

• Firstly, a timestamp is used as the primary key and sorting mechanism.
• Lastly, tags may define additional information about the type, origin, and other information about the entry.

37. Give few examples of Time series databases?

• Monitoring and event telemetry.
• Sensor or other IoT data.

38. What do you understand by Object storage?

Object storage is optimized for storing and retrieving large binary objects like images, files, video and audio streams etc. Moreover, Object stores can manage extremely large amounts of unstructured data.

39. What is the data type used in Object storage?

• Firstly, the data size is large.
• Lastly, the value is opaque.

40. List some examples of Object storage?

• Firstly, images, videos, office documents, PDFs
• Secondly, static HTML, JSON, CSS
• Thirdly, log and audit files
• Lastly, database backups

41. What do you understand by SQL Server Management Studio (SSMS)?

SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure, from SQL Server to Azure SQL Database. SSMS provides with some tools to configure, monitor, and administer instances of SQL Server and databases. SSMS can be used to deploy, monitor, and upgrade the data-tier components used by your applications, and build queries and scripts.