Terms and Definition

Question 1

What does it mean atomic data in the database? (атомарные данные)

Answer 1

Atomic data is information that can’t be broken down into smaller parts.

Иными словами, это такие данные, разделение которых на составляющие приводит к потере их смысла с точки зрения решаемой задачи. Например, если атрибут «Цена» содержит значение 15, то попытка разделить его на 1 и 5 приведет к полной бессмыслице.

Question 2

What does it mean normalized db?

Answer 2

It’s a db without data duplication.

Question 3

What does it mean the first normal form (1NF)?

Answer 3

1) Fields should be atomic

2) The table should include primary_key

Question 4

What is CROSS JOIN?

Answer 4

Will create a new table each row with each row.

In SQL, the CROSS JOIN is used to combine each row of the first table with each row of the second table.

Question 5

What is INNER JOIN?

Answer 5

The same as CROSS JOIN but with some conditions. It doesn’t take all values from the table but only values for which condition is true.

Inner Join clause in SQL Server creates a new table (not physical) by combining rows that have matching values in two or more tables. This join is based on a logical relationship (or a common field) between the tables and is used to retrieve data that appears in both tables.

Question 6

What is NATURAL JOIN?

Answer 6

The same as INNER JOIN but we have to use the same column for 2 tables.

A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns of the two tables that are being joined. Common columns are columns that have the same name in both the tables

Question 7

What is SQL Correlated Subqueries?

Answer 7

When queries need data from outer query.

SQL Correlated Subqueries are used to select data from a table referenced in the outer query. The subquery is known as a correlated because the subquery is related to the outer query

Question 8

What is Uncorrelated Sub-query?

Answer 8

An uncorrelated sub-query is a type of sub-query where inner query doesn’t depend upon the outer query for its execution. It can complete its execution as a standalone query.

Question 9

What is LEFT OUTER JOIN?

Answer 9

The LEFT OUTER JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). If there is no match from the right side then return NULL instead of the right value.

Question 10

What is the difference between LEFT JOIN and INNER JOIN?

Answer 10

INNER JOIN does not return a row if condition doesn’t pass.

LEFT JOIN returns left row and NULL if the condition doesn’t pass

Question 11

What is RIGHT OUTER JOIN?

Answer 11

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.

Question 12

What “Relational Database” means?

Answer 12

This is a database that stores data in tables ( fields and records).
You retrieve data by using SQL.

Question 13

What is normalization?

Answer 13

Normalization is the process of decomposing large tables into smaller ones in order to eliminate redundant data and duplicate data.

Question 14

What is the primary key?

Answer 14

Primary key - uniquely identifies records.

Question 15

What is view?

Answer 15

A view is a “virtual” table composed of fields from one or more tables in the database; ( JOIN )

Question 16

What is composite primary key?

Answer 16

Primary key composed of two or more fields is known as a composite primary key.

Question 17

What is the foreign key?

Answer 17

You have 2 tables. The name “foreign key” is derived from the fact that the second table already has a
primary key of its own, and the primary key you are introducing from the first table is “foreign” to the second table.

The values of a foreign key must match existing values of the primary key to which it refers. ( If you remove record with primary key from they first table the record from the second table with foreign key will be broken)

Question 18

What table types do you know ( to store something )?

Answer 18

• data ( to store data or events)
• linking ( to store association between tables )
• validation ( to store all countries for example )

Question 19

What multipart field means?

Answer 19

A field that contains more than one entity. For example Address ( Belarus, Minsk, Street home )

Question 20

What multivalue field means?

Answer 20

Multivalued field can potentially store two or more occurrences of the same value. ( for example category with values - AG, BF, CD - many via coma )

Question 21

What ‘code smells’ can break your database design?

Answer 21

• One table represent characteristic for several subjects ( For example table that contains Company and Employee together )
• Multipart fields ( For example Address ( Belarus, Minsk, Street home ) )
• Multivalue fields ( for example category with values - AG, BF, CD - many via coma )
• Contains calculated or concatenated values
• It is not unique within the entire database structure (denormalized)

Question 22

What is a candidate key?

Answer 22

This is a more common name for primary key. We can create more than one candidate key on the table.

Question 23

What conditions should be present for primary/candidate key?

Answer 23

• It must contain unique values.
• It cannot contain null values.
• Its value cannot cause a breach of the organization’s security or privacy rules.
• It includes a minimum number of fields necessary to define uniqueness.
• Its value can be modified only in rare or extreme cases.

Question 24

What is artificial ( surrogate) candidate key?

Answer 24

When your table doesn’t have any candidate key you can add a new field just for this purpose.

Question 25

What is alternate key?

Answer 25

This is a candidate keys that you don’t use as primery key.

Question 26

What characteristic you can set on the field?

Answer 26

• Date type ( String, Integer )
• Uniqueness ( true/false)
• Null Support
• Default
• Length
• Indexed

Question 27

What is self-referencing relatinship?

Answer 27

When a given record in the table related to other records within the table.

Question 28

What kind of deletion rules you can set on association?

Answer 28

• Deny. Will not delete the record in the parent table, but will instead keep the record and designate it as “inactive.”
• Cascade. It will delete the record in the parent table, and it will also automatically delete all related records in the child table.”
• Nullify: The RDBMS will delete the record in the parent table and will then update the foreign key values of related records in the child table to null.
• Set Default: The RDBMS will delete the record in the parent table and will then update the foreign key values of related records in the child table to the current Default Value

Question 29

What is a validation table?

Answer 29

This is a table which just store values for validation ( Contry for example)

Question 30

What kind of data constraint do you know?

Answer 30

• Field specific - ( length, type, valid values)

- Relationship specific ( Number of available association records, destroy rules )

Question 31

What are the differences between postgresql and mysql?

Answer 31

Mysql

• operation alter table can lock the entire table (because it copies the entire table)
• use statement-based replication by default
• use :read_committed isolation level

Postgresql

• operation alter table doesn’t lock the table
• use WAL replication by default
• use :repeatable_read as default isolation level

Question 32

What is Data Warehouse?

Answer 32

A data warehouse is a large collection of business data used to help an organization make decisions. ( Copy of your database to run requests to get business data values)

Question 33

What is materialized view?

Answer 33

Materialized view is a database object that contains the results of a query. For example, it may be a local copy of data located remotely, or may be a subset of the rows and/or columns of a table or join result, or may be a summary using an aggregate function.