Basics Flashcards
What are the tables for in databases?
Tables contain rows of data.
What are rows in databases?
Row is a single recordno of data, belonging to a single table or query result.
Relations
‘A set of tuples, where each element d(j) is an element od data domain D(j)’
Associations between tables that are created using join statements to retrieve data.
Types of relations (cardinalities)
One-to-one (one side has unique FK)
One-to-many(one side has FK that can repeat)
Many-to-many(implemented with intermediary table)
What is a primary key in db?
1) A unique column in a table used to identify any row in that table. It’s a special unique index.
CANNOT BE NULL.
What is a foreign key in database?
A column/columns in a table which contain a primary key (usually) of a different table. They providatw a link between data in separate tables.
Transaction
A series or reads/writes performed in a database by a single user.
A sequence or multiple operations performed on a database
What does ACID stand for in db?
Atomicity (All or nothing)
Consistency
Isolation
Durability
Those are principles for transactions to follow to ensure data reliability and integrity
What is Atomicity in ACID in db?
Atomicity “All or nothing” means that each transaction is treated as a single unit made of multiple statements, if one fails then all fail and db gets rolled-back to original
What does ‘Consistency’ stand for in ACID in dbs?
Any transaction can only bring db from one consistent state to another. All of data must always be valid according to defined ‘rules’. It prevents corruption
What does Isolation stand for in ACID in db?
Ensures that concurrent executions of transactions don’t interfere with each other. Each transaction is independent and ‘invisible’ until commited.
What does ‘Durability’ stand for in ACID in db?
Ensures that changes made from transactions executed successfully are saved even in a case of a system failrue.
What is OLTP (online transactional processing)
A way of handling data with a system enebling real-time execution of large number of db transactions by large number or people, typically over internet.
It usually has lots of small/simple requests accessing smaller datasets.
Examples: online stores, ATMs
What is OLAP (online analytical processing)
System for performing multi-dim analysis at high speeds on large volumes of data.
It usually has big/comples requests accessing large datasets.
Examples: Data warehouses
