Week 4

Question 1

What is the domain in a relational model?

Answer 1

Range of the values in a column; the data type of the attribute

Question 2

What is a tuple in a relational model?

Answer 2

The data in a row

Question 3

What is a attribute in a relational model?

Answer 3

The name of the columns

Question 4

What is a relation in a relational model?

Answer 4

The ‘table’ (NOT EXACTLY)

Question 5

What are the relation components?

Answer 5

1. Relation schema (the NAME of the relation)

2. Relation body (the attributes of the relation)

Question 6

What are the relation components?

Answer 6

1. Relation schema (the NAME of the relation)

2. Relation body (the attributes of the relation)

Question 7

Whats the difference between a relation and a table?

Answer 7

Main difference is that in a table, data can be input freely

In a relation, tuples (rows) are unique

Question 8

What is the degree and cardinality of a relation relational model?

Answer 8

Degree = number of attributes (columns)
Cardinality = number of tuples (rows)

Question 9

What is a primary key in a relational model?

Answer 9

This uniquely identifies a tuple in a relation

Question 10

What is a foreign key?

Answer 10

This shows the relationship between two relations

Question 11

What is a candidate/minimal key?

Answer 11

A super key that does not contain a subset of any other super key

Question 12

What is normalisation?

Answer 12

A process that ssigns attributes to entities so that data redundancies are reduced or elimatnted.

Question 13

What are the stages of normalisation?

Answer 13

1. First normal form (1NF)
2. Second normal form (2NF)
3. Third normal form (3NF)
4. Fourth normal form (4NF)

Question 14

What is denormalisation? Why necessary sometimes?

Answer 14

The process of lowering the stage of a normal form.

Required to sometimes meet performance requirements as lower forms have greater performance but greater redundancies.

Question 15

What is the prime attribute?

Answer 15

A key attribute that is an attribute that is a part of a key or is the whole key.

Question 16

What are key attributes?

Answer 16

The attributes that form the primary key

Question 17

What is a nonprime attribute?

Answer 17

An attribute that is NOT a part of a key

Question 18

What is a non-key attribute?

Answer 18

An attribute that is NOT a part of a key

Question 19

What is the objective of normalisation?

Answer 19

To ensure that each relation conforms tto the concept of well-formed relations.

Question 20

What is the concept of well-formed relations? List the charactersistics of a well-formed relation.

Answer 20

• Each relation only contains data for a single entity
  (i. e. STUDENT will only contain data for students)
• No data item will be unnecessarily stored in more than one relation. Reduces chance of data anomalies/inconsistencies.
• All non-prime attributes in a relation are dependent on the primary key
• Each relation is void of insertion, update or deletion anomalies. Ensures integrity and consistency of data.

Question 21

What are the characteristics of the first normal form (1NF)

Answer 21

• Table format
• No repeating groups
• PK identified for the main relation

Question 22

What are the characteristics of the second normal form (2NF)

Answer 22

• All of 1NF

- No partial dependencies based on any candidate keys

Question 23

What are the characteristics of the third normal form (3NF)

Answer 23

• All of 2NF

- No transitive dependencies

Question 24

What is the Boyce-Codd normal form? What are its characteristics?

Answer 24

It is a special case of a 3NF BUT every determinant is a candidate key.

Question 25

What are the characteristics of the fourth normal form (4NF)

Answer 25

• All of 3NF

- No independent multivalued dependencies

Question 26

What is functional dependence?

Answer 26

The attribute B is fully functionally dependent on attribute A if each value of A determines ONE and ONLY ONE value of B

i.e. Think bijectivity between set B and set A

Question 27

What is partial dependency?

Answer 27

When an attribute is depenedent on only a portion of the primary key.

Question 28

What is transitive dependency?

Answer 28

When attribute A determines attribute B and attribute B determines attribute C. Attribute A and C have a transitive dependency.

Question 29

What is a repeating groups?

Answer 29

A group of multiple entries of the same type can exist for any SINGLE key attribute occurence.

Question 30

What are the steps to normalisation to 1NF?

Answer 30

1. Eliminate repeating groups
2. Identify PK
3. Identify all dependencies

Question 31

What are the steps to normalisation to 2NF?

Answer 31

1. Make new relations to eliminate partial dependencies

2. Reassign corresponding dependent attributes

Question 32

Should the foreign key be different name compared to the primary key?

Answer 32

NO, unless the FK and PK in a single relation are the same name

Question 33

What is an INSERT anomaly?

Answer 33

When adding data to a relation you are required to add other related data.

Other data may be unavalable, cant process with INSERT

Question 34

What is UPDATE anomaly?

Answer 34

Changing the value in a tuple may require updating of multiple other tuples.

Missing one of the rows to be changes will result in data inconsistencies

Question 35

What is a DELETE anomaly?

Answer 35

When data to be deleted causes other data to be deleted.

When you delete the last row that contains data that you dont want to lose.