Databases

Question 1

Types of Indexes

Answer 1

Clustered: Sorts rows on key values. Can only have 1 clustered index as it’s the data itself stored on disk.

Non-clustered: Data stored in one location and indicies in another that point to the data. Can have many of these.

Question 2

Insertion Anomaly

Answer 2

Cannot insert because other required info is missing

Ex. Cannot add customer before they order something

Question 3

Partial Dependencies

Answer 3

When non-key is dependent on part of a candidate key

studentid | projectno | studentname | projectname

studentname and projectname are both dependent on the ids in the table.

Problem with this is if we don’t have any students assigned to a project, then we can’t get the project name from anywhere

Question 4

ACID

Answer 4

Atomicity: Commit all or nothing
Consistency: Change maintains data integrity
Isolation: Can concurrently process all transactions
Durability: Changes to database persist

Question 5

OLAP

Answer 5

Online Analytical Processing

• Optimized for big reads
• Column-oriented storage
• Star/snowflake schemas
• Low frequency of transactions

Question 6

OLTP

Answer 6

Online Transactional Processing

• Optimized for rights and low-latency
• Row-oriented storage
• 3NF
• Large number of users conducting short transactions