Database fundamentals

Question 1

Name five features of a relational database.

Answer 1

1. Consists of tables with columns and rows.
2. Uses keys: primary, foreign.
3. Uses relationships: one-to-many.
4. Uses transactions.
5. Uses SQL.

Question 2

What do columns and rows in a database table represent?

Answer 2

Columns: Represent the data that should be in the DB. These are the structure and rules for the data.
Rows: The actual data.

Question 3

What is a primary key?

Answer 3

A unique piece of data. Can be naturally unique or system generated piece of data. It is identified by the column and makes each row unique. Allows you to connect tables together (form relationships between tables).

Question 4

What are the three kinds of table relationships?

Answer 4

1. One-to-many: most common
2. Many-to-many: not too common but used. Uses a junction or linking table between the two tables.
3. One-to-one: not common

Question 5

How is a transaction defined?

Answer 5

A transaction is a combined unit of work. Think of a simple money transfer.

Question 6

What is the ACID test?

Answer 6

A: Atomic (indivisible) - All or nothing.
C: Consistent - Take the DB from one valid state to another valid state based on the rules of the DB.
I: Isolated - The data and the transaction are locked while the transaction is occurring.
D: Durable - Robust. Once the transaction is done, it is guaranteed.
All transactions must pass the ACID test.

Question 7

What is SQL?

Answer 7

Structured Query Language. It is a declarative query language so you describe what you want and the DMS figures out the procedural stuff: SELECT * FROM Books WHERE ListPrice > 40.
Uses C-R-U-D.
PLSQL: Oracle SQL
T-SQL: Microsoft SQL server