• Semantics of the attributes.
• Reducing the redundant values in tuples.
• Reducing the null values in tuples.
• Disallowing spurious tuples.

Definition : If a and b are attributes or sets of attributes of relation R, we say that b is Functionally Dependent on a if each value of a in R has associated with it exactly one value of b in R( a ® b)

When a functional dependency exists, the attribute or set of attributes on the left side of the arrow is called a determinant.

Student Table (Each student has at most one major)

student( stuid, stuname, major, credits, status, socsecno )

stuid ® stuname

stuid ® stuname, major, credits, status, socsecno

sosecno ® stuid, stuname, major, credits, status

credits ® statu

Superkeys, Candidate Keys, and Primary Keys

Superkey is an attribute or a set of attributes that identifies an entity uniquely.

{stuid}, { stuid, any other attribute} {socsecno, and other attribute} 

Candidate key is a superkey such that no proper subset of its attributes is itself a superkey

{stuid}, {socsecno}, {stuname, major} 

Primary key is a candidate key that is used to identify tuples in a relation. None of its attributes may have null values.

{stuid}, {socsecno} 

Definition : A relation is in First Normal Form (1NF) if and only if every attribute is single-valued for each tuple.

Student table (students may have more than one major)

Student table rewritten in 1NF

{stuid, major}

Definition: In a relation R, attribute b of R is Fully Functionally Dependent on an attribute or set of attributes a of R if b is functionally dependent on a but not functionally dependent on any proper subset of a.

class( course#, stuid, stuname, facid, sched, room, grade )

Class Table

fd:

course#, stuid ® stuname, facid, sched, room, grade

course#, stuid - composite key

fd:

course# ® facid, sched, room

stuid ® stuname

Stuname is fd on course#, stuid, it is also fd of stuid

Definition : A relation is in Second Normal Form (2NF) if and only if it is in first normal form and all the nonkey attributes are fully functionally dependent on the key.

class (course, stdid, stuname, facid, sched, room, grade )

Class Table

course # ® facid, sched, room

stuid ® stuname

course#, stuid ® grade ( and, of course, facid, sced, room, stuname ); 

Using projection we break up the class into:

class2(course#, stuid, grade)

couse(course#, facid, shed, room)

stu(stuid, stuname)

Class2 Table

Stu Table

Course Table

Transitive dependency

student (stuid, stuname, major, credits, status )

stuid ® credits

credits ® status

(stuid ® credits ) and (credits ® status) => (stuid ® status)

Definition: A relation is in Third Normal Form (3NF) if it is in second normal form and no nonkey attribute is transitively dependent on the key.

Student Table

Stu2 Table

Stats Table

Definition: A relation is in Boyce-Codd Normal Form (BCNF) in and only if every determinant is a candidate key.

student(stuid, stuname, major, credits, status)

determinents: stuid, credits

Credits is not a candidate key - the relation is not BCNF.

faculty (facname, dept, office, rank, datehired ) 

office ® dept

facname, dept ® office, rank, datehired

facname, office ® dept, rank, datehired

fac1 (dept, office)

fac2 (facname, office, rank, datehired) 

stu (stuname, stuadd, major, gpa)

Analysis

• Universal relation -all the attributes in the db
• Decomposition

Synthesis

• Make a list of all fds
• Group together those with the same determinant
• Construct a relation for each such group

1. How are data to be actually stored on physical devices?

2. How are data to be accessed and/or modified?

Considerations:

• Usage of storage space
• Correctness/efficiency of transactions

The physical storage designer may or may not be able to choose the data item storage technique.

Specific options available will depend on dbms being used.

Data item storage techniques

Transactions

A single operation as viewed by user involves more than one task 

Eg. Update grade of student in relation

Tasks: 

• Bring data from secondary storage to memory
• Locate block containing tuple (via index ?)
• Obtain locks on data
• Read in block 
• Make update 
• Check for errors
• Rollback, or
• Commit 
• Write back to secondary storage 
• Release locks  

...Such a program unit is called a transaction

Atomicity

- Runs to completion as indivisible unit

- Result either no change or consistent change

- Updates accessible to other transactions 

Consistency

- If database in consistent state before transaction, will be afterwards 

Isolation

- Actions hidden until termination 

Durability

- Ensures 'commit' action reflected in database

Efficiency in transactions achieved by: 

• Good requirements analysis in first stage
• Trial of selected transactions

Integrity

• Ensuring data accurate and up-to-date
• Era
• Normalisation
• Data locks

Security

• Protection from unauthorised access/modification/destruction
• Sub-schemas
• Locks
• Rollbacks
• Logs 

Deadlock 

• Two or more processes locking each other out of activity
• Maps
• Logs

Recovery 

• Minimise effects of crashes, failures etc
• Separate log
• Backups

'Rollback'='Windback' process until its activities with database are sound

Practical Knowledge 

Example 

The Teachers Relation 

 This is un-normalised because it allows a multiple set of values in column 'course#'

 Decompose to 1NF(ie atomic values only)

However, There May be Problems: 

1. Insertion Anomaly

Room size cannot be entered in the database unless a course is scheduled in that room 

2. Deletion Anomaly 

Rsize of a room in which only one course is scheduled will be deleted if that course is deleted 

3. Update Anomaly 

There is nothing to prevent the same room appearing more than once in the database. Inconsistencies could arise between entries 'room' and 'rsize' if updating is careless 

Rsize is not fully functionally dependent on course#

(Dependent on room)

Decompose to 2NF

 course_details

Room_details 

 There is a transitive dependence between room and en_limit>

Decompose 'course_details' to 3NF:

Course

Redundancy 

• Each course fee repeated for each employee

Insertion anomaly 

• A new course cannot be added unless some employee has enrolled for it

Deletion anomaly 

• If only one employee for a course, deletion of employee will delete course details

Update anomaly 

• Modifying course fee may lead to inconsistency if careless 

Employee_course 

• Maintain consistency of references between two related tables 

• Don't add a course fee for an employee unless fee has been established 
• Don't drop a course if one or more employees are enrolled for it

For the example from the previous time:

1. Using these assumptions and stating any others you need to make, list all the nontrivial functional dependencies for this relation.

2. What are the candidate keys for this relation? Identify the primary key.

3. Is the relation in Third Normal Form? If not, find a 3NF lossless join decomposition of college that preserves dependencies.

4. Is the relation or resulting set of relations in Boyce-Codd Normal Form? If not, find a lossless join decomposition that is in BCNF, if possible.