Databases

Question 1

What are the components of a database?

Answer 1

Hardware (server)
software (mySQl or RDS)
data
procedure
database access language(sql)

Question 2

What are the different types of databases and their distinctions?

Answer 2

SQL is relational and NoSQL is not
SQL scales vertically but NoSQL scales horizontally
SQL has structed predefined schema, NoSQL is dynamic and flexible and is for unstructed data
SQL has tables based on relationships between those tables, NoSQL has other types of data structures such as Document, Key-value, Graph, or wide column
An example of Relational is oracle, MySQL, and example of NoSQL is Redis, MongoDB

Question 3

When would you use SQL vs NoSQL

Answer 3

SQL- transaction orientated systems. Storing students infomation of a school
NoSQL - unstructured data like a social media post

Question 4

What is ETL?

Answer 4

• Extract the data from various sources or legacy systems
• Transform the data by cleansing, de-duplicating, and standardising to make it into a form that fits your database
• Load the data into the target database.

Question 5

Compare a data warehouse and an Online Transaction Processing database

Answer 5

They are both databases.
OLTP is centerd around inserting, updating, searching and viewing the data

Data Warehousing is generally used to collect and manage data from multiple sources to provide a meaningful business insight.

OLTP are typically in third normal form dw is not

OLTP is produced for real-time business operations whereas DW are for analysis of business measures by categories and attributes.

Question 6

What is DB tuning?

Answer 6

Optimizing the performance of a database
minimise bottle necks
keeps query times as short as possible

Question 7

How would you provide high availability for a database deployed on an instance in the cloud?

Answer 7

Deploy a primary database and one or more standby database in a different location/availability zone that synchronously replicates the data. In case of an infrastructure failure, ensure there is an automatic failover to the standby database.
You can do this with Amazon RDS

Question 8

What can’t you horizontally scale a sql db

Answer 8

Because when you have tables split across the different cluster nodes, joins usually become very inefficient.

Question 9

Why do we use indexes?

Answer 9

Indexes contain all the necessary information needed to access items quickly and efficiently.

Question 10

What is the port number for DNS?

Answer 10

53

Question 11

What is the port number for http?

Answer 11

80

Question 12

What is the port number for https?

Answer 12

443

Question 13

What is the port number for telnet?

Answer 13

23

Question 14

What is a dbms

Answer 14

DBMS stands for Database Management System. It is a collection of application programs which allow the user to organize, restore and retrieve information about data efficiently and as effectively as possible.

Question 15

What do you understand by Data Redundancy?

Answer 15

Duplication of data in the database is known as data redundancy. As a result of data redundancy, duplicated data is present at multiple locations, hence it leads to wastage of the storage space and the integrity of the database is destroyed.

Question 16

What are the advantages of DBMS?

Answer 16

Data is stored in a structured way and hence redundancy is controlled.

Validates the data entered and provide restrictions on unauthorized access to the database.

Provides backup and recovery of the data when required.

It provides multiple user interfaces.

Question 17

What is normalisation?

Answer 17

the process of removing redundant data from the database by splitting the table in a well-defined manner in order to maintain data integrity. This process saves much of the storage space.
There are seven normal forms.

Question 18

What is the first normal form?

Answer 18

First Normal Form (1NF): A relation is said to be in 1NF only when all the entities of the table contain unique or atomic values.

Question 19

Second normal form?

Answer 19

Second Normal Form (2NF): A relation is said to be in 2NF only if it is in 1NF and all the non-key attribute of the table is fully dependent on the primary key.

Question 20

What is 3NF

Answer 20

Third Normal Form (3NF): A relation is said to be in 3NF only if it is in 2NF and every non-key attribute of the table is not transitively dependent on the primary key.

Question 21

What is SQL?

Answer 21

Structured Query language, SQL is a standard programming language that is designed specifically for storing and managing data in the relational database management system (RDBMS) using all kinds of data operations.

Question 22

Enlist the advantages of SQL.

Answer 22

Simple SQL queries can be used to retrieve a large amount of data from the database very quickly and efficiently.

SQL is easy to learn and almost every DBMS supports SQL.

It is easier to manage the database using SQL as no large amount of coding is required.

Question 23

What is a record?

Answer 23

A record is a collection of values or fields of a specific entity. For Example, An employee, Salary account, etc.

Question 24

What is a field?

Answer 24

A field refers to an area within a record that is reserved for specific data. For Example, Employee ID.

Question 25

What is a table?

Answer 25

A Table is the collection of records of specific types. For Example, the Employee table is a collection of records related to all the employees.