Week 4 - Logical Design

Question 1

List the steps(9) of mappings an ER to a relational schema.

Answer 1

1. Mapping of regular entity types
2. Mapping of weak entity types
3. Binary 1:1
4. Binary 1:N
5. Binary N:M
6. Multivalued attributes
7. N-ary relationships
8. Specializations or generalizations
9. Union types

Question 2

What is data normalization?
What are the stages of normalization?

Answer 2

The process of analyzing given relational schemas based on their primary keys and functional dependencies to minimize data redundancy and update anomalies.
1. 1NF - if attributes only take single and atomic values.
2. 2NF - every non-prime attribute fully funactionally depends on every candidate key.
3. 3NF - No non-prime attribute in transitively dependent on the primary key.
4. BCNF - only if every determinant is a key.

Question 3

What is meant by a single and atomic in a value?

Answer 3

Single: single value from a domain of attributes. Not multivalued.
Atomic: value cannot be divided further. Not composite.

Question 4

What is a prime attribute?

Answer 4

An attribute is prime if it appears as a candidate key. (a key value used to uniquely identify a record)

Question 5

What is a transitive dependency?

Answer 5

An attribute that is not a prime key or a subset of any key. ???

Question 6

What is a transaction?
What is a transaction processing system?

Answer 6

A transaction provides means of describing logical units of database processing.
A system with large databases and a large amount of concurrent users executing db transactions.

Question 7

What is multiprogramming?
Why is concurrency beneficial in db management?

Answer 7

In Multiprogramming execution of programs are interleaved. Processes suspend and resume as needed with commands. Allows multiple users to access the db at the same time.
Serial execution provides poor performance.
Since disk assesses are frequent and slow, it’s better to work on multiple programs at once.

Question 8

What is granularity?

Answer 8

The size of a data item.

Question 9

List the problems that occur with concurrency(3)

Answer 9

1. Lost update problem (eg)
2. Temporary update (dirty read) problem (eg)
3. Incorrect summary problem.

Question 10

List 6 methods a transaction can fail. Give examples.

Answer 10

1. Computer failure - software/ hardware/ network
2. Transaction error - division by 0
3. Local errors - data not found
4. Concurrency control enforcements - serializable and deadlock
5. Disk failure - read/write head crash
6. Physical problem or catastrophe

Question 11

What are the states of a transaction?

Answer 11

Active –> partially committed –> committed
Active –> partially committed –> failed –> aborted

Question 12

What are the desirable properties of a transaction?

Answer 12

1. Atomicity - executing all actions in one step or not at all.
2. Consistency preservation - each transaction leaves the db in a consistent state.
3. Isolation - each transaction is executed by itself.
4. Durability or permanency - when a transaction commits all updates made in the db should persist even if the system crashes later.

Question 13

What is the state of a database?
What is meant by consistency?

Answer 13

• The collection of data in a db at a given point.
• A state that satisfies all constraints.

Question 14

What is a schedule?

Answer 14

A sequence of operations from a set of transactions, where the operations are in their original order.

Question 15

How can conflicting operations be identified?(3)

Answer 15

-They belong to two transactaions.
-Are accessing the same data item X
- at lease one operation is a write operation.

Question 16

Conflict equivalent?
Conflict serializable?

Answer 16

?
?