Joe Celko s SQL for Smarties - Advanced SQL Programming P11. In the SQL database community, Joe Celko is a well-known columnist and purveyor of valuable insights. In Joe Celko's SQL for Smarties: Advanced SQL Programming, he picks up where basic SQL training and experience leaves many database professionals and offers tips, techniques, and explanations that help readers extend their capabilities to top-tier SQL programming. Although Celko denies that the book is about database theory, he nevertheless alludes to theory often to buttress his practical points. This title is not for novices, as the author points out. Instead, its intended audience. | 72 CHAPTER 2 NORMALIZATION section_id CHAR 1 NOT NULL grade CHAR 1 NOT NULL CREATE TABLE Students student_name CHAR 25 NOT NULL PRIMARY KEY major CHAR 10 NOT NULL A common misunderstanding about relational theory is that 3NF tables have no transitive dependencies. As indicated above if X Y X does not have to be a key if Y is part of a candidate key. We still have a transitive dependency in the example room_nbr time_period course_name section_id but since the right side of the dependency is a key it is technically in 3NF. This table structure still has unreasonable behavior though several courses can be assigned to the same room at the same time. Another form of transitive dependency is a computed column. For example CREATE TABLE Stuff width INTEGER NOT NULL length INTEGER NOT NULL height INTEGER NOT NULL volume INTEGER NOT NULL CHECK width length height volume PRIMARY KEY width length height The volume column is determined by the other three columns so any change to one of the three columns will require a change to the volume column. You can use a view to create computed columns. Elementary Key Normal Form EKNF Elementary Key Normal Form EKNF is a subtle enhancement on 3NF. By definition EKNF tables are also in 3NF. This happens when there is more than one unique composite key and they overlap. Such cases can cause redundant information in the overlapping column s . For example in the following table let s assume that a subject title is also a unique identifier for a given subject in the following table CREATE TABLE Enrollment student_id INTEGER NOT NULL Boyce-Codd Normal Form BCNF 73 course_code CHAR 6 NOT NULL course_name VARCHAR 15 NOT NULL PRIMARY KEY student_id course_name UNIQUE student_id course_name alternative key Enrollment student_id course_name course_name 1 CS-100 ER Diagrams 1 CS-114 Database Design 2 CS-114 Database Design This table although it is in 3NF violates EKNF. The primary key of the above table is the combination of student_id .
