Re: DB Design Principles for RDBMS

Richard Wheeldon wrote:

> Help! I'm confused. How can you have a 5NF schema with derived
> data?

How about this:

create table student (
  student_id int not null primary key,
  last_name varchar(30) not null,
  first_name varchar(30) not null,
  units_completed int not null
)

Where units_completed is derived from the transcript table:

create table transcript (
  student_id int not null,
  course_id int not null,
  term_taken char(4) not null,
  units_attempted int not null,
  units_completed int not null,

  primary key (student_id, course_id, term_taken),
  foreign key (tudent_id) references student (student_id),
  foreign key (course_id) references course (course_id),
  foreign key (term_taken) references term (term_name)

student.units_completed is the sum of all transcript.units_completed for that student_id. Where is this not normalized?

Larry Coon
University of California
larry_at_assist.org
and lmcoon_at_home.com Received on Fri Mar 14 2003 - 19:59:31 CET