Database
What is a Database?
It's a structured representation of data that we can read from and write to.
What are keys?
A field, or combination of fields used to retrieve and sort rows in the table based on certain requirements
One-to-one relationship?
Each record in one of the tables maps to only one record in the other table.
each employee only has one office, and each office only has one employee
What does ERD stand for?
entity relationship diagram
What's a query?
query is a request for data from a database
What is a Relational Database?
Relational databases are structured so that they organize data into tables or relations, where each table represents a logical group of data.
What is a super key?
Super Key - is a set of attributes that uniquely identifies each tuple of a relation
many-to-one relationship?
a row in table A is related to multiple rows in B. But a row in table B is only related to one row in A
Example:
department employs many employees, but each employee is only related to one department
Name 5 ERD components
tables
fields in each table
metadata about each field
id of all primary and foreign key fields
relationship between each table
What is a sub-query?
subquery is a query that is query inside of another query, used to make queries in multiple steps
Database Vs. DBMS
DBMS is a software system that manages databases. It can execute commands, provide security, network access, and admin tools for Database Administrators (DBAs)
What is a primary key?
a column or group of columns in a table that uniquely identifies every row in that table.
many-to-many relationship?
Multiple records in a table are related to multiple records in another table.
A person can be enrolled in multiple courses, and each course has multiple people enrolled in it
What is DML?
Data Manipulation Language is a subset of SQL.
What does SELECT do?
SELECT is a type of query. We can use it to retrieve data from a database
Explain ACID compliance
databases that are designed to withstand unexpected failures without corruption, using concepts of Atomicity, Consistency, Isolation, and Durability.
What is a compound key?
combining multiple columns to create a unique identifier for a specific record
self-referencing relationship?
occurs when a column in a table relates to another column in the same table.
What is ERD of a database?
A diagram showing relationship between multiple components in a database
What does JOIN do?
JOIN clause is an optional part of a SELECT. It can join multiple tables based on related columns.
Explain Replication and Scaling
Replication: process of creating various resources and dividing the work between them, which ultimately improves the performance of the overall cluster of servers.
Scaling: Scaling, usually means scaling horizontally, means dividing work between existing servers.
What is a surrogate key?
a field that is created specifically to identify each record in a database but has no other purpose in the table.
What are aggregates?
Aggregates are single values calculated from many values. for example sum()
Name 4 DML Commands
Commands to read, insert, update and delete data
What is GROUP BY?
GROUP BY clause allows us to further partition a result and calculate aggregates per partition