d1 - relational database and subqueries and joins and sequalize

Question 1

what does RDD stand for?

Answer 1

relational database design. With RDD, data is organized into tables and all types of data access are carried out via controlled transactions.

Question 2

what are the stages of relational database design?

Answer 2

1. Define the purpose/entities of the relational DB
2. Identify primary keys
3. Establish table relationships
4. Apply normalization rules

Question 3

What’s an entity for RDD?

Answer 3

tables

Question 4

What’s a record for RDD?

Answer 4

rows

Question 5

What is a one-to-one table relationship?

Answer 5

one record is associated with one record in another table

least common table relationship

ex. 1 mug associated with 1 description

Question 6

What’s a one-to-many table relationship?

Answer 6

one record is associated with many records in another table

ex. each user can have multiple orders

Question 7

What’s a many-to-many table relationship?

Answer 7

joining many records with many tables. Normally you’d create a third joined table.

ex. each user has multiple orders. each order contains an order_id and a product id (in addition to the id of the order)

Question 8

What’s normalization for RDD?

Answer 8

it’s the process of optimizing the database structure so that it’s not redundant or confusing

Question 9

What are the rules for normalization?

Answer 9

1. first normal form
2. second normal form
3. third normal form
4. Boyce-Codd normal form
5. fifth normal form

• first 3 are widely used in practice

Question 10

What are the first normal form rules?

Answer 10

• eliminate repeating groups in indiv tables
• create a sep table for each set of related data
• identify each set of related data with a primary key

Question 11

What are the second normal form rules?

Answer 11

• create separate tables for sets of values that apply to multiple records
• relate these tables with a foreign key

Question 12

What are the third normal form rules?

Answer 12

• eliminate fields that do not depend on the table’s key

Question 13

What are the different types of join operations?

Answer 13

1. inner join
2. left join
3. right join
4. full outer join
5. self-join

Question 14

What’s inner join?

Answer 14

returns a result set container rows in the left table that match rows in the right table.

ex. table A matches rows with table B (left side matches right side)

Question 15

What’s a left join?

Answer 15

Returns a result set containing all rows from the left table with the matching rows from the right table. If there is no match, the right side will have null values.

ex. table A matches rows with table B, BUT if there’s no match the right side has null values

Question 16

What’s a right join?

Answer 16

Returns a result set containing all rows from the right table with matching rows from the left table. If there is no match, the left side will have null values.

ex. table B matches rows with table A, BUT if there’s no match the left side has null values

Question 17

What’s a full outer join?

Answer 17

Returns a result set containing all rows from both the left and right tables, with the matching rows from both sides where available. If there is no match, the missing side contains null values.

ex. table A matches rows with table B. if no match, missing side contains null values

Question 18

What’s a self-join?

Answer 18

A self-join is a query in which a table is joined to itself. Self-joins are useful for comparing values in a column of rows within the same table.

ex. table A is joined to itself

Question 19

What’s a subquery?

Answer 19

a SELECT statement nested inside another SELECT statement

Question 20

When to use join operation vs. subqueiries?

Answer 20

JOIN is best when you want to combine rows from one or more tables based on a match condition.

Subqueries work well when you’re returning a single value

When returning multiple rows you could use either.

Question 21

What’s sequelize?

Answer 21

a JS library. It’s an example of object relational mapping (ORM).

ORMs allow JS programmers to fetch and store data in a SQL database using JS functions instead of writing SQL code.