What are the conditions for 1st normal form
- having no mixed data types within the same column
- a table must have a primary key
- each row must contain one piece of data not multiple (no repeating groups - every cell must have one value (atomic))
What are the conditions for 2nd normal form
- There must be no partial dependancies
i.e each non-key attribute must depend entirely on the primary key
Define a transitive dependancy
If A -> B and B -> C, then C is transitive dependant on A via B: A -> C
Define Partial functional dependancy
Gives A -> B, if removing some attribute(s) from A, the dependency still holds.
Define a full functional dependancy
IF A -> B and B is not functionally dependant on any subset of A.
How do you convert to 2NF
must be 1NF and have no partial dependancies
What is a partial dependancy
when a non-key attribute depends on only part of the a composite primary key
How do you fix partial dependancies (2NF)
split the table so attributes that depend only on one part of the key go into their own table
What is 3NF
Must be 2NF and have no transitive dependencies
What is a transitive dependency
A non-key attribute depending on another non-key attribute
Give an explain of a transitive dependancy
A transitive dependency is when a non-key attribute depends on another non-key attribute, not directly on the key.
Example:
Table: Product(ProductID, ProductName, CategoryName, CategoryDescription)
Key = ProductID
CategoryDescription depends on CategoryName, not directly on ProductID → transitive dependency → violates 3NF.
What is a partial dependency? Give an example.
A partial dependency is when a non-key attribute depends on only part of a composite primary key, not the whole key.
Example:
Table: OrderLine(OrderID, ProductID, CustomerName, Qty)
Composite PK = (OrderID, ProductID)
CustomerName depends only on OrderID, not ProductID → partial dependency → violates 2NF.
When is 2NF relevant
only when the table has a composite primary key (otherwise it already is in 2NF)
What is relational algebra
provides a way to query the data in a database and to modify the data
What operator is used to select rows where a condition is true
Selection (σ)
give an example of selector age from employee table that is greater than 30
σ_(AGE > 30)(Employee)
What is the operator for selecting certain columns in a table to present
Projection (π)
Give an example of how you would use projection and its meaning
Meaning: Choose these attributes only.”
Example:
π_(CUS_NAME, CUS_PHONE)(Customer)
What is the operator issued to rename a relation or an attribute
Renaming (ρ)
How does does union select data from two tables (R and S)
from either R or S. Eliminate duplicate tuples
How does does intersection select data from two tables (R and S)
selects rows that is from both R and S
How does does Set Difference select data from two tables (R and S) (R-S)
From R, but not S
Answer this in relational algebra: Find the lecturers who work in 01 or 03 department and age is below 50
r_(deptID=’01’ (or - symbol) deptID=’03’) (and-symbol) (age<50) (lecture) - this is upper case but is used to say which table you selected it from
What is the cartesian product (x)
R x S: produces a relation that is the concatenation of every tuple relation of R with every tuple of relation S
- if both R and S have the same attribute A, then use: R.A and S.A
