DBMS Technical Questions

What is a DBMS?

DBMS stands for Database Management System. It is a software application that is used to store and retrieve data within a system. It does so by creating and managing databases. A general DBMS is tasked with the definition, creation, querying, update, and administration of databases.

A DBMS allows the programmer a quick and efficient way to create, store, retrieve, update and manage data. It ensures that the data stays organizing and is easily assessable to the user via application programs.

Some popular DBMS are MySQL, PostgreSQL, MongoDB, Microsoft SQL Server, Oracle, Sybase, SAP HANA, and IBM DB2.

Why are DBMS used?

As its name suggests a Database Management System (DBMS) is a system that manages databases. It is a program that can be used to collect and organize data within a system called databases. These databases can then by access and modified by users who have been granted access.

DBMS provides an easy and efficient way to consolidate and modify data. It is also quite a cheaper alternative to traditional means to collecting and consolidating data. It is also quite fast. Some of the various types of data that can be consolidated via a DBMS include employee records, student information, payroll, accounting, project management, inventory and library books, etc.

DBMS provides a way to store, organize and manage large amounts of data and information with the use of a single software application. The data can be categorized and structured depending on the needs of the company or the user. The entire data can then be searched through or managed via the use of algorithms.

What are advantages of DBMS over traditional file based systems?

As compared to traditional file based systems, Database Management Systems (DBMS) are quicker and more efficient in collecting data. They also help consolidate and organize the data, which would have to be otherwise done manually. Another benefit of DBMS is that it helps reduce redundancy via data normalization, which again would otherwise have to be done manually.

Using DBMS also allows the users to reorganize the data anytime as depending on their needs. Additionally, the necessary data can be extracted or utilized via algorithms to find patterns and other detail that a user might all. All of which would have been very time consuming in traditional file based systems

What are super, primary, candidate and foreign keys?

Super, primary, candidate and foreign are different types of keys. A key is a single or combination of multiple fields, with the purpose to allow access or retrieval of data rows from a table as required. The keys differ in the manner they access or sequence the stored data. The keys can also be them used to create links between different tables.

A primary key is an attribute or combination of attributes that uniquely identifies a row or record in a relation. A relation can have only one primary key.

A candidate key or alternate key is any attribute within the table that is not being utilized as the primary key.

A foreign key is an attribute or a combination of attributes in a relation whose value match a primary key in another relation. The foreign key is created in a dependent table. The table to which the foreign key refers to is known as the parent table.

A Super key is a combination of attributes that can be used to uniquely identify a database record. A table may have many superkeys.  

What is the difference between primary key and unique constraints?

A primary key is an attribute or column that can identify uniqueness in a row. Hence, it can be said that it is another name for a unique key. However, the default implementation in an SQL Server is different for a Primary Key and a Unique Key.

In a table, there can only be one primary key, however, there can be many unique constraints. A unique constraint can be any constraint. It need not be even be unique. It can also be a candidate key.

Additionally, primary key constraints are not nullable, whereas unique constraints may be nullable. When a unique constraint is created, the database automatically creates a unique index.

What is the difference between primary key and unique key and why one should use unique key if it allows only one null?

The main difference between a primary key and unique key is that there can only be one primary key, whereas a unique key can be any constraint that can identify a uniqueness in a row. There can even be more than one unique key, and it can even be the candidate key.

Another difference between them is that a primary key does not include Null values whereas a unique key can.

ANSI SQL allows columns with unique keys to have multiple nulls, however, Microsoft SQL Server only allows only a single null. This happens because NULL is just another value, and NULL=NULL is true. This is actually a side-effect of the way Microsoft implemented the comparison for uniqueness.

However, one can create the UNIQUE constraint as a filtered index in order to bypass this restriction. This will apply the unique constraint only on rows where the column is not NULL, hence allowing any number of rows with NULL.

What is database normalization?

In the simplest terms, database normalization, is the process of organizing the columns (attributes) and tables (relations). Database normalization seeks to put the columns and tables of a relational database into order so as to reduce data redundancy and improve data integrity.

Database Normalization is often referred to as simply normalization. Its primary objective is to design the database so that it achieves optimum structure. Database Normalization also reduces and eliminates redundant or repeated data. However, it may also have the effect of duplicating data within the database that may often result in the creation of additional tables.

Database normalization is intrinsic to relational database theory. It was proposed by Dr. Edgar F. Codd in 1970 as "a normal form for database relations."

What is SQL?

SQL stands for Structured Query Language. It is an ANSI (American National Standards Institute) language, which allows the user to communicate with a database. It has now become the standard language for relational database management systems.

The primary function of SQL is to help manage relational databases, as well as allow the performance of various operations on the data that is stored within those databases. SQL is primarily used to set up and run analytical queries, both by database administrators, as well as by developers writing data integration scripts and data analysts.

What is embedded and dynamic SQL?

SQL is a type of domain-specific language that is used to help manage databases. There are two main types of SQL, Embedded SQL and Dynamic SQL. Each has its own properties and attributes.

Embedded SQL – Embedded SQL, also known as Static SQL are SQL statements that do not change at runtime. Hence, these statements can be hard-coded into the application.

Dynamic SQL – Dynamic SQL are statements that are constructed at runtime. Therefore, the application may allow users to enter their own queries. Dynamic SQL can also be used to create more general purpose and flexible applications as the full text of a SQL statement may be unknown at compilation.  

What is the difference between embedded and dynamic SQL?

Embedded and dynamic SQL are two of the most commonly used types of SQL. Each one has its own set of properties and attributes, which makes it better suited for a particular usage. The following is the list of the primary differences between Embedded and Dynamic SQL.

Embedded SQL

Dynamic SQL

It is hardcoded in the program itself and the changeable value is the value in the host variable.

Has the capability of changing the columns, tables during the execution of the program.

How the database will be accessed is predetermined in the SQL statement.

How database will be accessed is determined at run time.

Faster and efficient.

Slower and less efficient.

Less flexible

More flexible

Contain host variables

Does not contain any host variables

SQL statements are compiled at compile time

SQL statements are compiled at run time.

Parsing, validation, optimization, and generation of application plan are done at compile time

Parsing, validation, optimization, and generation of application plan are done at run time.

Better suited for situations where data is distributed uniformly.

Better suited for situations where data is distributed non-uniformly.

EXECUTE IMMEDIATE, EXECUTE and PREPARE statements are not used.

EXECUTE IMMEDIATE, EXECUTE and PREPARE statements are used.

The BIND is done prior to the execution and is stored in a PLAN.

The BIND occurs at execution time

Access Path is generated during BIND time.

Access path is generated during Run-time

If VALIDATE(BIND) is used, the table authorizations are checked during BIND ().

Table authorizations can only be checked during run-time.

What is the difference between CHAR and VARCHAR?

CHAR and VARCHAR are two different functions that can be used in SQL. The primary usage of both is to store characters. The CHAR function is better suited to store fixed length characters, whereas VARCHAR is better suited to store variable length characters. The following are the primary differences between the two:

CHAR

VARCHAR

Fixed length

Variable-length

Better suited if content is fixed size

Better suited if content is variable size

Used to store character string value of fixed length.

Used to store variable length alphanumeric data.

The maximum number of characters the data type can hold is 255 characters.

Pre-MySQL 5.0.3 can hold a maximum of 255 characters. After MySQL 5.0.3, can hold 65,535 characters shared for the row.

Is faster than VARCHAR.

Is slower than CHAR

Uses static memory allocation

Uses dynamic memory allocation

What is the difference between HAVING and WHERE clause?

HAVING and WHERE clauses are two different functions that can be used in SQL. Their primary function is for use with SELECT queries. The main difference between them is that while the WHERE is used in any SELECT query, the HAVING clause is only used in SELECT queries, those that contain aggregate functions or groups by clause. However, there are some other differences between the two as stated in the table below.

HAVING clause

WHERE clause

Is only used in SELECT queries, which contains aggregate function or group by clause

Can be used in any SELECT query

Specifies a search condition for a group or an aggregate function used in SELECT statement.

Specifies the criteria which individual records must meet to be selected by a query

Cannot be used without the GROUP BY clause.

Can be used without the GROUP BY clause.

Selects rows after grouping

Selects rows before grouping

Can contain aggregate functions

Cannot contain aggregate functions

Add new comment

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.