Databases Flashcards
What is a database?
An organized collection of structured information or data, stored electronically
What was the first kind of database?
Navigational Database
What is the query language for navigational databases?
CODASYL (Conference/Committee on Data Systems Languages)
What is the primary benefit of a navigational database over reading data sequentially?
Navigational databases allow to navigate directly from record to record following relations
Who invented relational databases?
Dr. Edgar Codd
What is the entity-relationship model used for?
To model data in databases using relationships and to help navigating a database
What are the three subcomponents of a relationship in ER models?
Entities, Atributes and Relationships
How many cardinality types an entity-relationship can have?
4: Zero or one Exactly One Zero or More One or More
What is the sign of a mandatory one (exactly one) cardinality type in crow’s foot notation?
||
What is the sign of an optional one (zero or one) cardinality type in crow’s foot notation?
o|
What is the sign of a mandatory many (one or more) cardinality type in crow’s foot notation?
|{ or }|
What is the sign of an optional many (zero or more) cardinality type in crow’s foot notation?
o{ or }o
What is the data type for missing data in a relational database?
null
What is the query language used for relational databases?
SQL
What does DDL stand for?
Data Definition Language
What does DML stand for?
Data Manipulation Language
What does DCL stand for?
Data Control Language
How do you create a table in SQL?
CREATE TABLE a (a INTEGER);
How do you read data from a table in SQL?
SELECT * FROM a
How do you add data to an SQL table?
INSERT INTO a (a)
VALUES
(1),
(2);
How do you delete data from an SQL table?
DELETE FROM a WHERE a=1
How do you modify data in an SQL table?
UPDATE a SET a=”new” WHERE a=1
How do you investigate what a query actually does in SQL?
EXPLAIN QUERY PLAN SELECT * FROM a
What is an attribute in SQL?
An attribute is whats commonly refered to as a column in a table and stores one single datapoint per entry in the Database
What is a tuple in SQL?
A tuple is whats commonly refered to a row in a table, it is an individual entry in the Database
What is a primary key in SQL?
A Primary key is a unique identifier with which an entry can be identified
What is a composite key in SQL?
A composite key is a combination of two or more Primary and Foreign keys in a joining table used to secure the uniqueness of entries into the joining table
What is a candidate key in SQL?
A candidate key is a natural, domain or business key. The Candidate key is unique, might be primary and can be null, which is why it cant be a primary key
What are the two criteria of the first normal form?
- Contains only atomic values
- There are no repeating groups
How many join types the ANSI SQL standard have?
5:
INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, CROSS
What does a CROSS JOIN of two tables return in set theory terms?
A Carthesian Product
What does an INNER JOIN of two tables return?
Returns the set of records that match in both tables
What does a FULL OUTER JOIN of two tables return?
Returns every record from all tables, if no matching record can be found to a record it will be matched with a null value
What concepts a simple key-value store has to store data?
what? (maybe key, value)
What are the three concepts of a graph database?
Nodes, Edges and Properties
What does OLTP stand for?
Online Transaction Processing
What does OLAP stand for?
Online Analytical Processing
What does ACID stand for?
Atomicity, Consistency, Isolation, Durability
What are the two most popular data storage options?
Row oriented and Collumnar oriented Data Storage
What are the advantages of row-oriented data storage?
- Easy to insert a new row
- Transactions are concerned about a single row
- Row level security can be used with this design
What are the disadvantages of row-oriented data storage?
- Reading an entire row while only needing a single collumn
What are the advantages of columnar-oriented data storage?
- Reading a single column results in reading the minimum amount of data
- Advanced techniques can be applied using the columnar aproach
What are the disadvantages of columnar-oriented data storage?
- Hard to update
- Hard to insert into
Which data storage is better suited for OLTP use cases?
Row Data Storage
Which data storage is better suited for OLAP use cases?
Collumnar Data Storage
What are the three letters stand for in CAP theorem?
Consistency, Availability, Partition Tolerance
What does ETL stand for?
Extract, Transform, Load
