week 10

Question 1

what is a candidate key?

Answer 1

A relation schema can have more than one key - these are called candidate keys

A primary key is then assigned from these candidate keys
Rest ( of candidate keys if any) are secondary keys

Question 2

prime

Answer 2

attribute that is a member of some candidate key

Question 3

Non prime

Answer 3

attriibrute that is a member of non- candidate keys

Question 4

what is normalisation?

Answer 4

The process of:
n analysing relation schemas based on FDs and candidate keys to minimise
redundancy and insertion, deletion and update anomalies
n decomposing unsatisfactory relations by breaking up their attributes into
smaller relations

Question 5

what is normal form?

Answer 5

Condition using keys and FDs of a relation to certify whether a relation schema is
in a particular normal form

Question 6

what does normalisation help get rid of

Answer 6

erroneous tuples generated through bad join conditions( usually when fk and pk are not joined properly)
redundancy and by extension insertion , update and deletion anomalies
presence of null values

Question 7

properties of First Normal Form (1NF)

Answer 7

Disallows:
.composite attributes (attributes that can be broken down into further attributes eg FullName)
. multi valued attributes
.nested relations; attributes whose values for an individual tuple are non-
atomic

. no repeating groups of attributes
.Each row must be unique

Question 8

What is full functional dependency

Answer 8

Remove of any attribute from Y-> Z means the functional dependency doesn’t hold any more

eg : Examples: {SSN, PNUMBER} -> HOURS
-Full FD
-since neither SSN -> HOURS nor PNUMBER -> HOURS hold

HOWEVER

SSN, PNUMBER} -> ENAME
- It is not a full FD
-since SSN -> ENAME also holds
-partial dependency

Question 9

2NF

Answer 9

A relation schema R is in second normal form (2NF) if
every nonprime attribute A in R is not partially
dependent on any key of R.

if primary key is single attribute , automatically 2NF

Question 10

3NF

Answer 10

A relation schema R is in third normal form (3NF) if
n it is in 2NF and
n no non-prime attribute A in R is transitively dependent on the
primary key

n X -> Y and Y -> Z, with X as the primary key, we consider this a
problem only if Y is not a candidate key.

Essentially for all non prime attributes , either they have to be functionally dependent on primary key directly or transitively through a candidate key

if functionally dependent transistively througha non - candidate (Y is non candidate) then not in 3NF

Question 11

more useful definition / seeing visuallt if in 3NF

Answer 11

A relation schema R is in third normal form (3NF) if whenever a FD
X -> A holds in R, then either:
(a) X is a superkey of R, OR
(b) A is a prime attribute of R

Question 12

BCNF