SQL & RDB Basic Flashcards
DBMS
program that controls creation, maintenance and use of DB
RDBMS
store data in collection of tables which is related by common fields between columns
SQL
structured query language, use to communicate with a database
Table
set of data organized in columns a rows
Field
column, single piece of info from a record
PK
combination of fields which uniquely specify a row, implicit NOT NULL
Unique Key
uniquely identify each record in database
FK
a column or group of column that provide link between data in two tables
Inner Join
matching values in both tables
Outer Join
union of two tables
Natural join
columns with the same name of associated table will appear only once (same data type)
Right join
everything from the right table and everything in both table
Left join
everything from the left table and everything in both table
Full join
all row of joined table whether the same or different
Self join
table joined itself
Normalization
process of minimizing redundancy and dependency by organizing fields and tables of a database
View
virtual table consists of subset of data contained in a table
Index
performance tuning method of allowing faster retrieval of records
Unique index
indexing does not allow field to have duplicate values if the column is unique indexed (e.g. pk column)
Clustered index
reorder the physical order of the table and search based on key values
Non clustered index
does not alter physical order of the table and maintains logical order of data
Stored Procedure
function consists of many SQL statement to access DB system
Trigger
code/program that automatically runs with response to event on a table or view
SQL vs NoSQL
SQL
- Tables with fixed rows and columns
- Schemas are Rigid
- Requires Join between table
- Requires Object relation mapping
- used when data validity is super important
- vertically scalable
NoSQL
- Document: JSON documents, Key-value: key-value pairs, Wide-column: tables with rows and dynamic columns, Graph: nodes and edges
- Schemas are flexible
- Usually dont require join
- Usually faster than SQL
- Use when it’s more important to have fast data than correct data
- Horizontally Scalable
Benefit of SQL
- Data integrity
- Data accuracy
- An ideal choice for the complex query intensive environment
- Cross-platform support, Secure and free
Benefit of NoSQL
Flexible data models
Horizontal scaling
Fast queries
Easy to use, High performance, and Flexible tool
Disadvantage of SQL
not suitable for hierarchical data storage
Disadvantage of NoSQL
don’t support ACID (atomicity, consistency, isolation, durability) transactions across multiple documents
To remove a table
DROP
To remove a record
DELETE
TRUNCATE
Delete all rows from a table, cannot rollback, faster than DELETE
What is Normalization and what are the advantages of it?
the process of organizing data to avoid duplication and redundancy
First Normal Form (1NF)
No repeating groups within rows
Second Normal Form (2NF)
Every non-key (supporting) column value is dependent on the whole primary key.
Third Normal Form (3NF)
Dependent solely on the primary key and no other non-key (supporting) column value. in 2NF and not contain any transitive partial dependency.
ACID Property
Atomicity, Consistency, Isolation, Durability
Atomicity
It means if one part of any transaction fails, the entire transaction fails and the database state is left unchanged
Consistency
Consistency ensures that the data must meet all the validation rules.
Isolation
The main goal of isolation is concurrency control.
Durability
Durability means that if a transaction has been committed, it will occur whatever may come in between such as power loss, crash or any sort of error.
What is a Database?
Database is nothing but an organized form of data for easy access, storing, retrieval and managing of data. This is also known as structured form of data which can be accessed in many ways.
