Week 4 Flashcards
What is the domain in a relational model?
Range of the values in a column; the data type of the attribute
What is a tuple in a relational model?
The data in a row
What is a attribute in a relational model?
The name of the columns
What is a relation in a relational model?
The ‘table’ (NOT EXACTLY)
What are the relation components?
- Relation schema (the NAME of the relation)
2. Relation body (the attributes of the relation)
What are the relation components?
- Relation schema (the NAME of the relation)
2. Relation body (the attributes of the relation)
Whats the difference between a relation and a table?
Main difference is that in a table, data can be input freely
In a relation, tuples (rows) are unique
What is the degree and cardinality of a relation relational model?
Degree = number of attributes (columns) Cardinality = number of tuples (rows)
What is a primary key in a relational model?
This uniquely identifies a tuple in a relation
What is a foreign key?
This shows the relationship between two relations
What is a candidate/minimal key?
A super key that does not contain a subset of any other super key
What is normalisation?
A process that ssigns attributes to entities so that data redundancies are reduced or elimatnted.
What are the stages of normalisation?
- First normal form (1NF)
- Second normal form (2NF)
- Third normal form (3NF)
- Fourth normal form (4NF)
What is denormalisation? Why necessary sometimes?
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.
What is the prime attribute?
A key attribute that is an attribute that is a part of a key or is the whole key.
What are key attributes?
The attributes that form the primary key
What is a nonprime attribute?
An attribute that is NOT a part of a key
What is a non-key attribute?
An attribute that is NOT a part of a key
What is the objective of normalisation?
To ensure that each relation conforms tto the concept of well-formed relations.
What is the concept of well-formed relations? List the charactersistics of a well-formed relation.
- 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.
What are the characteristics of the first normal form (1NF)
- Table format
- No repeating groups
- PK identified for the main relation
What are the characteristics of the second normal form (2NF)
- All of 1NF
- No partial dependencies based on any candidate keys
What are the characteristics of the third normal form (3NF)
- All of 2NF
- No transitive dependencies
What is the Boyce-Codd normal form? What are its characteristics?
It is a special case of a 3NF BUT every determinant is a candidate key.
What are the characteristics of the fourth normal form (4NF)
- All of 3NF
- No independent multivalued dependencies
What is functional dependence?
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
What is partial dependency?
When an attribute is depenedent on only a portion of the primary key.
What is transitive dependency?
When attribute A determines attribute B and attribute B determines attribute C. Attribute A and C have a transitive dependency.
What is a repeating groups?
A group of multiple entries of the same type can exist for any SINGLE key attribute occurence.
What are the steps to normalisation to 1NF?
- Eliminate repeating groups
- Identify PK
- Identify all dependencies
What are the steps to normalisation to 2NF?
- Make new relations to eliminate partial dependencies
2. Reassign corresponding dependent attributes
Should the foreign key be different name compared to the primary key?
NO, unless the FK and PK in a single relation are the same name
What is an INSERT anomaly?
When adding data to a relation you are required to add other related data.
Other data may be unavalable, cant process with INSERT
What is UPDATE anomaly?
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
What is a DELETE anomaly?
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.
