Descriptions of tables used in practicing for the course Basics of Databases, spring 2000

Create table speciality (               Specialities that can be stydied

   spId varchar(12) NOT null,           Identifier, textual, as capitals
   spName varchar(40),                  Name, initial letter as capital
   spMajorCredits numeric(3),           credit units needed for major
   spMinorCredits numeric(3),           credit units needen for minor
PRIMARY KEY (spId)
);

CREATE TABLE teacher (                  Teachers

   tId     varchar(12) NOT NULL ,       Identifier, Textual, as capitals
   tLname  varchar(40) NOT NULL ,       Last name, Initial as capital
   tFname  varchar(40) not null,        First name, Initial as capital
   tPhone  mnumeric(12) ,               Phone number
   tOffice varchar(12),                 Office room, Char and numbers 
PRIMARY KEY (tId) 
);

CREATE TABLE student (                  Students

   sNumber    numeric(5) NOT NULL ,     Integer >100
   sLName     varchar(40) NOT NULL ,    Last name, Initial as capital
   sFName     varchar(40) not null,     First name, Initial as capital
   sCity      varchar(30),              City of residence, Initial as capital
   sSex       char,                     Sex: M= male, F=  female
   sYearBorn  numeric(4),               Year of birth as YYYY (1999)
   sYearStarted numeric(4),             Year studies began as YYYY (1999)
PRIMARY KEY (sNumber)
);

CREATE TABLE course (                   Courses

   cNumber numeric(8) NOT NULL ,        Course number, >1000
   cName    varchar(40) NOT NULL ,      Course name, Initial as capital
   cCreditUnits   numeric(5,1) ,        Credit units e.g 1.5
   cLectureHours  numeric(3),           Number of lecture hours
   cPracticeHours numeric(3),	          Number of practice hours
PRIMARY KEY (cNumber)
);

CREATE TABLE courseschedule (           Course timetable

   csCourse   numeric(8) not null,      Course identifier
   csBeginDate    date,                 Date course started or will start
   csFee          numeric(6,2),         Course fee 
PRIMARY KEY (csCourse, csBeginDate),
FOREIGN KEY (csCourse) references course(cNumber)
);

create table suitability (              Suitablilities of courses for 
                                        speciality studies

   suCourse  numeric(8) not null,       Course identifier  
   suSpeciality varchar(12) not null,   Speciality identifier
   suPhase        varchar(12),          Phase of studies in which the course is
                                        recommended values: beginner, mid, expert
PRIMARY KEY (suCourse, suSpeciality),
FOREIGN KEY (suCourse) references course(cNumber),
FOREIGN KEY (suSpeciality) references speciality(spId)
);

create table teacherskills (            What topics the teachers are able to teach

   tsTeacher varchar(12) not null,      Teacher identifier
   tsSpeciality  varchar(12) not null,  Speciality identifier
PRIMARY KEY (tsTeacher, tsSpeciality),
FOREIGN KEY (tsTeacher) references teacher(tId),
FOREIGN KEY (tsSpeciality) references speciality(spId)
);

CREATE TABLE studyfor (                 Students' studyplans 

   sfStudent numeric(5) not null,       Student identifier
   sfSpeciality varchar(12) not null,   Speciality identifier
   sfExtent     varchar(12),            Extent: {major, minor}
PRIMARY KEY (sfStudent,sfSpeciality),
FOREIGN KEY (sfSpeciality) references speciality(spId),
FOREIGN KEY (sfStudent) references student(sNumber)
);

CREATE TABLE studying (                 Courses taken be students

   stCourse     numeric(8) not null,    Course identifier
   stBeginDate  date not null,          Date the course began
   stStudent    numeric(5) NOT NULL ,   Student identifier
   stDatePassed date,                   Date student passed the course
                                        many null values
   stGrade      varchar(12),            grade obtained {1-5)
 PRIMARY KEY (stCourse,stBeginDate,stStudent) ,
 FOREIGN KEY (stCourse,stBeginDate) 
    REFERENCES courseschedule(csCourse,csBeginDate),
 FOREIGN KEY (stStudent) REFERENCES student(sNumber)
);

create table teaching (                 Teaching

   tcTeacher varchar(12) not null,      Teacher identifier
   tcCourse  numeric(8) not null,       Course identifier
   tcBeginDate   date not null,         Date the course begun
   tcTask        varchar(20),           Teaching task: (lecture,practice)
   tcHours       number(3),             Hours allocated
PRIMARY KEY (tcTeacher,tcCourse,tcBeginDate,tcTask),
FOREIGN KEY (tcTeacher) references teacher(tId),
FOREIGN KEY (tcCourse,tcBeginDate) 
  references courseschedule(csCourse,csBeginDate)
);