Week 5

Question 1

What is Normalization?

Answer 1

Technique for producing a set of relations with desired properties

Remove redundancy

Remove potential for insertion, modification and deletion anomalies

Question 2

What is an Insertion Anomaly?

Answer 2

An issue in relational databases when inserting data causes unnecessary redundancy or requires irrelevant information to be added

Cause: Occurs in poorly normalized tables, where adding new data (e.g., a student) forces the inclusion of unnecessary data (e.g., course or instructor details)

Impact: Leads to data redundancy, inconsistency, and maintenance difficulties

Fix: Normalize the database by separating data into related tables (e.g., Student, Course, Instructor) and using foreign keys to link them

Question 3

What is a Modification Anomaly?

Answer 3

An issue in relational databases where updates (insert, delete, or modify) cause inconsistency or redundancy

Cause: Occurs in poorly normalized tables, where adding new data (e.g., a student) forces the inclusion of unnecessary data (e.g., course or instructor details)

Impact: Leads to data redundancy, inconsistency, and maintenance difficulties

Fix: Normalize the database by separating data into related tables (e.g., Student, Course, Instructor) and using foreign keys to link them

Question 4

What is a Deletion Anomaly?

Answer 4

An issue in relational databases where deleting data causes unintended loss of other important data.

Cause: Occurs in poorly normalized tables, where multiple types of information are stored together, leading to unintended deletions

Impact: Important data might be lost when deleting a record (e.g., removing a student also deletes course information)

Fix: Normalize the database to separate data into related tables, ensuring deletion of one record doesn’t affect others

Question 5

What is Functional Dependancy?

Answer 5

If A and B are attributes of relation R, if each value A is associated with exactly one value of B, B is said to be functionally dependant (FD) on A

Denoted A->B

A is the determinant

eg.

Student(studentID, studentName, DoB, postcode, city)

city is functionally dependant on postcode

postcode is the determinant

Question 6

What is Transitive Dependancy?

Answer 6

If A->B and B->C, then C is transitive dependant on A via A->C

Question 7

What is Partial Functional Dependancy?

Answer 7

Given A->B, if removing some attribute(s) from A, the dependancy still holds

Question 8

What is Full Functional Dependancy?

Answer 8

If A->B and B is not functionally dependant on any subset of A

Question 9

What is 1st Normal Form?

Answer 9

Remove repeating groups

Question 10

What is 2nd Normal Form?

Answer 10

Remove partial dependancies

Question 11

What is 3rd Normal Form?

Answer 11

Remove transitive dependancies

Question 12

What is Boyce-Codd Normal Form (also called 3.5 NF)?

Answer 12

A stricter version of the 3rd Normal Form (3NF) that eliminates certain types of redundancy in relational databases.

Definition: A table is in BCNF if, for every non-trivial functional dependency, the left side (determinant) is a candidate key

Key Concept: In BCNF, no non-key attribute can determine another non-key attribute

Purpose: Prevents anomalies by ensuring that every determinant is a candidate key, reducing redundancy and improving data integrity

Example: If A → B (A determines B), and A is not a candidate key, the table is not in BCNF

Question 13

What is Algebra?

Answer 13

In its most general form, algebra is the study of mathematical symbols and the rules for manipulating these symbols

Question 14

What is Relational Algebra?

Answer 14

An Algebra whose operands are relations

Relation -> Relational Operator -> A new Relation

Question 15

How does Relational Algebra help with us databases?

Answer 15

Relational algebra provides a means to query a means to query the data in a database and to modify the data

SQL incorporates relational algebra

Question 16

What are the 3 Unary Operators?

Answer 16

Projection (π)

Selection (σ)

Rename (ρ)

Question 17

What is the Projection (π) Operator?

Answer 17

π(R)

Produce a new relation that only has only some of R’s columns (vertical rows)

Elimate duplicate tuples, if any

Question 18

What is the Selection (σ) Operator?

Answer 18

Produce a new relation that contains only those tuples (rows) of R that satisfy the condition

Question 19

What is the Rename (ρ) Operator?

Answer 19

The Rename (ρ) operator in relational algebra is used to rename the attributes or the entire relation (table) in a query

Purpose: Simplifies queries by changing the names of attributes or relations for clarity or to avoid conflicts (e.g., in joins)

Question 20

What are the 4 Set Operations?

Answer 20

Union (∪)

Intersection (∩)

Set difference (-)

Cartesian Product (x)

Question 21

What is the Union Set Operator?

Answer 21

R ∪ S - creates new tuple of all values in R and S (remove duplicates if any)

Think of union in probability

Question 22

What is the Intersection Set Operator?

Answer 22

R ∩ S - Creates new tuple with values only in both in R and S

Think of intersection in probability

Question 23

What is the Difference Set Operator?

Answer 23

R - S

Will create new tuple with values in R but not in S

Think of difference in probability

Question 24

What does Union Compatible mean?

Answer 24

Two relations (tables) in a database are union compatible if they have the same number of attributes (columns) and corresponding attributes have the same data types. This is necessary for performing a union operation, which combines the rows from both tables, removing duplicates

The Union, Intersection and Difference set operators are all Union Compatible

Question 25

What is the Cartesian Product (x)?

Answer 25

R x S

Produces a relation that is the concatenation of every tuple of Relation R with every tuple of relation S

Question 26

What are the 3 Binary Operations?

Answer 26

Join (⋈)

Outer Join (⟖, ⟕)

Division (÷)

Question 27

What is the Join (⋈) Operator?

Answer 27

A join operation performs a selection over the Cartesian product of 2 relations

R ⋈ S - Defines a relatio nthat contains all combinations of tuples from R and S that satisfy the join condition

Question 28

What is Equijoin?

Answer 28

Combines two tables based on matching values in specified columns, using the equality (=) condition. It retrieves rows where values in the joined columns are the same in both tables

Equijoin is special case of theta-join

Question 29

What is Natural Join?

Answer 29

Combines two tables by automatically matching columns with the same name and selecting rows where the values in these columns are equal. It avoids duplicate columns in the result

Question 30

What is Outer Join (⟖, ⟕) ?

Answer 30

Combines two tables and returns all rows from one table and matching rows from the other. If there’s no match, it fills with NULL values for the missing side. There are three types: Left, Right, and Full

Question 31

What is the Division Operator (÷) ?

Answer 31

the division operator returns rows from one table that match all values in another table. It’s used when you want to find records that are related to every item in a set