7/8 Relational Design

Question 1

What is the first step in designing a database?

Answer 1

Common sense and intuition of DB designer

Question 2

What does an ER schema represent?

Answer 2

Entity-Relationship diagram showing entities, their attributes, and relationships

Question 3

What must an ER diagram be transformed into?

Answer 3

Tables with rows and columns

Question 4

Is there a final rule for perfect attribute grouping?

Answer 4

No final rule for perfect attribute grouping

Question 5

What are the two levels of theory to improve relational schema?

Answer 5

• Logical level
• Storage level

Question 6

What is a Functional Dependency (FD)?

Answer 6

Relationship between two sets of attributes in a table

Question 7

What does a functional dependency tell us?

Answer 7

If we know the value of one attribute, we can uniquely determine the value of another attribute

Question 8

Provide an example of a functional dependency.

Answer 8

SSN determines Name

Question 9

What is a super key?

Answer 9

Set of attributes in a relation that uniquely identifies each tuple

Question 10

What does the functional dependency X → Y imply?

Answer 10

If two rows have the same values for attributes in X, they must also have the same values for attributes in Y

Question 11

What is a candidate key?

Answer 11

A super key with no subset Y of K such that Y → U

Question 12

What ensures that data in a relation follows certain rules?

Answer 12

Specifying Functional Dependencies (FD’s)

Question 13

What makes a functional dependency trivial?

Answer 13

The right hand side is already included in the left hand side

Question 14

What symbol is used for a logically implied FD?

Answer 14

|=

Question 15

What does F+ represent?

Answer 15

The set of functional dependencies logically implied by F

Question 16

What is Armstrong’s Reflexivity Rule?

Answer 16

If X ⊆ Y, then Y → X

Question 17

What is the Augmentation Rule in functional dependencies?

Answer 17

If X → Y and Z is a set of attributes then XZ → YZ

Question 18

What does the Union Rule state?

Answer 18

If X → Y and X → Z then X → YZ

Question 19

What is a minimal cover set denoted by?

Answer 19

F’

Question 20

What are the criteria for a set of FDs to be minimal?

Answer 20

• Each FD has one attribute on the right
• No FD can be removed without changing the meaning
• No attribute on the left side is unnecessary

Question 21

How can you determine if AG is a super key?

Answer 21

If AG+ contains all attributes in the relation

Question 22

What is the closure of an attribute set?

Answer 22

All the attributes that can be figured out from attribute (X) given the set of FDs

Question 23

What does it mean if X+ includes all attributes?

Answer 23

X is a super key

Question 24

What is the difference between a super key and a key?

Answer 24

A key is the smallest possible super key

Question 25

What problems can arise from representing, updating, adding, or deleting facts in a database?

Answer 25

Redundancy and anomalies ## Footnote Redundancy refers to waste of space, while anomalies include update inconsistency, insertion issues, and deletion losses.

Question 26

What is redundancy in the context of databases?

Answer 26

Waste of space

Question 27

What types of anomalies can occur in databases?

Answer 27

* Update anomaly: potential inconsistency * Insertion anomaly: inability to add data due to missing other required data * Deletion anomaly: loss of other useful data when deleting something

Question 28

What is normalization in database design?

Answer 28

The process of breaking a large table into smaller ones (decomposition) to reduce anomalies and redundancy

Question 29

What is lossy decomposition?

Answer 29

A decomposition where the original table cannot be reconstructed after joining, leading to extra, incorrect data

Question 30

What is lossless-join decomposition?

Answer 30

A split where the original table can be recovered through a join without adding or losing any data

Question 31

What condition must hold for a decomposition to be lossless-join?

Answer 31

(R1 ∩ R2 → R1) or (R1 ∩ R2 → R2) is in F+

Question 32

What is the significance of preserving functional dependencies in decomposition?

Answer 32

Ensures that the constraints or rules of the original dataset are still represented in the decomposed relations

Question 33

What is a prime attribute?

Answer 33

An attribute that is part of a candidate key for a relation

Question 34

What is the First Normal Form (1NF)?

Answer 34

A relation is in 1NF if all attributes have atomic domains (indivisible values)

Question 35

What does Non-First Normal Form (N1NF) indicate?

Answer 35

The relation has attributes that hold nested relations

Question 36

What is the criteria for a relation schema to be in Second Normal Form (2NF)?

Answer 36

It must be in 1NF and all non-prime attributes depend on the whole candidate key

Question 37

What characterizes a relation schema in Third Normal Form (3NF)?

Answer 37

For every functional dependency X → A, either X is a superkey or A is a prime attribute

Question 38

What is a transitive dependency?

Answer 38

An indirect dependency where Attribute A depends on B and B depends on C

Question 39

What is Boyce-Codd Normal Form (BCNF)?

Answer 39

A stricter normal form where for every functional dependency, the determinant must be a superkey

Question 40

In what order do normal forms relate to each other?

Answer 40

* BCNF is stricter than 3NF * 3NF is stricter than 2NF * 2NF is stricter than 1NF

Question 41

What is the goal in Relational Database Design?

Answer 41

* Achieve Boyce-Codd Normal Form * Ensure Lossless Join * Preserve Dependencies