d1 - relational database and subqueries and joins and sequalize Flashcards
what does RDD stand for?
relational database design. With RDD, data is organized into tables and all types of data access are carried out via controlled transactions.
what are the stages of relational database design?
- Define the purpose/entities of the relational DB
- Identify primary keys
- Establish table relationships
- Apply normalization rules
What’s an entity for RDD?
tables
What’s a record for RDD?
rows
What is a one-to-one table relationship?
one record is associated with one record in another table
least common table relationship
ex. 1 mug associated with 1 description
What’s a one-to-many table relationship?
one record is associated with many records in another table
ex. each user can have multiple orders
What’s a many-to-many table relationship?
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)
What’s normalization for RDD?
it’s the process of optimizing the database structure so that it’s not redundant or confusing
What are the rules for normalization?
- first normal form
- second normal form
- third normal form
- Boyce-Codd normal form
- fifth normal form
- first 3 are widely used in practice
What are the first normal form rules?
- 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
What are the second normal form rules?
- create separate tables for sets of values that apply to multiple records
- relate these tables with a foreign key
What are the third normal form rules?
- eliminate fields that do not depend on the table’s key
What are the different types of join operations?
- inner join
- left join
- right join
- full outer join
- self-join
What’s inner join?
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)
What’s a left join?
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
What’s a right join?
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
What’s a full outer join?
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
What’s a self-join?
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
What’s a subquery?
a SELECT statement nested inside another SELECT statement
When to use join operation vs. subqueiries?
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.
What’s sequelize?
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.
