Theoretical Questions Flashcards
What is a database?
A database is a system that stores data and makes it possible for users to work with that data OR an organised collection of structured information or data, stored electronically
What was the first kind of database?
Navigational Database / Integrated Database Systems IDS
What is the query language for navigational databases?
CODASYL (Conference/Committee on Data System Languages)
What is the primary benefit of a navigational data base over reading data sequentially?
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 database using relationships and to help navigating a database OR abstract logical model devoid of any implementation details OR physical models of relational databases
What are the three subcomponents of a relationship in data bases?
Entities, Attributes, 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 an optional one (zero or one) cardinality type in crow’s foot notation?
ol
What is the sign of a mandatory (exactly one) cardinality type in crow’s foot notation?
||
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 relational databases?
Null
What is the query language used for relational databases?
Structured Query Language (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?
What’s referred to as a column and stores one single datapoint per entry in the database
What is a tuple in SQL?
What’s referred to as a row in a table, individual entry in the database
What is a primary key in SQL?
Primary key is a unique identifier with which an entry can be identified
What is a composite key in SQL?
Combination of two or more primary keys 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?
Natural, Domain or business key. Candidate key is unique, might be primary and can be null, which is why it can’t 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 does the ANSI SQL standard have?
5
- Inner Join
- Left Outer
- Right Outer
- Full Outer
- Cross Join
What does a cross join of two tables return in set theory terms?
A Cartesian 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 does a simple key-value store have to store data?
Keys and values
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 Columnar 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 column
What are the advantages if columnar-oriented data storage?
- Reading a single column results in reading the minimum amount of data
- Advanced techniques can be applied using the columnar approach
What are the disadvantages if 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 datas storage is better suited for OLAP use cases
Columnar Data Storage
What do the letters in CAP theorem stand for?
Consistency, Availability, Partition Tolerance
What does ETL stand for?
Extract, Transform, Load
