diff --git a/STINK_student_records/STINK_student_records.xml b/STINK_student_records/STINK_student_records.xml index 6268591..58daabb 100644 --- a/STINK_student_records/STINK_student_records.xml +++ b/STINK_student_records/STINK_student_records.xml @@ -156,6 +156,8 @@ CONSTRAINT Paper_PK PRIMARY KEY ( Paper_Code ) ); + +

As with Qualification.Type, we could create a separate lookup table for Period. This table could even store additional information about the period, such as its full name, start and end dates, etc.

@@ -333,6 +335,7 @@ Other_Names VARCHAR2(50) NOT NULL, Contact_Phone VARCHAR2(11), -- at least 11, maybe more Contact_Address VARCHAR2(200) NOT NULL, + Email VARCHAR2(50) NOT NULL, Username VARCHAR2(50) NOT NULL CONSTRAINT Person_Username_Unique UNIQUE, -- bonus marks! -- @@ -343,7 +346,7 @@ ( Staff_ID NUMBER(7), Rank VARCHAR2(2) NOT NULL CONSTRAINT Staff Rank_Valid - CHECK ( Rank IN ( 'AL', 'L', 'SL', 'AP', 'P' ) ), + CHECK ( Rank IN ( 'T', 'AL', 'L', 'SL', 'AP', 'P' ) ), Salary NUMBER(8,2) NOT NULL CONSTRAINT Staff_Salary_Range CHECK ( Salary >= 40450 ), -- @@ -369,9 +372,11 @@ ); -

It’s important when implementing “boolean” style columns such as International in Student and Release in Assessment that you are consistent in the values that you use across all such columns (more precisely, all such columns should have the same domain). Quite a few people did something like implement one column as 'T', 'F' and the other as 'True', 'False', or even more subtle, 't', 'f' (remembering that SQL is case sensitive). Using inconsistent domains for columns that should have the same domain could lead to subtle bugs later on.

+

It’s important when implementing “Boolean” style columns such as International in Student and Release in Assessment that you are consistent in the values that you use across all such columns (more precisely, all such columns should have the same domain). Quite a few people did something like implement one column as 'T', 'F' and the other as 'True', 'False', or even more subtle, 't', 'f' (remembering that SQL is case sensitive). Using inconsistent domains for columns that should have the same domain could lead to subtle bugs later on.

-

This of course is really a consequence of not having a proper BOOLEAN data type in SQL. However, one person did discover—possibly inadvertently, given that it isn’t mentioned anywhere in the documentation—that now supports the use of the BOOLEAN data type in CREATE TABLE! This certainly wasn’t possible in older versions, where BOOLEAN was available in PL/SQL only.

+

This of course is really a consequence of not having a proper BOOLEAN data type in SQL. However, one person did discover—possibly inadvertently, given that it isn’t mentioned anywhere in the documentation—that now supports the use of the BOOLEAN data type in CREATE TABLE! This certainly wasn’t possible in older versions, where BOOLEAN was available in PL/SQL only. The inconsistency of implementation of a Boolean data type across various DBMSs means that it’s generally safer to use the approach above if portability is important.

+ +

Once again, we could use a lookup table for Staff.Rank.

@@ -447,6 +452,8 @@ CONSTRAINT Teach_FK_to_Paper FOREIGN KEY ( Paper_Code ) REFERENCES Paper ); + +

Role could be constrained using a lookup table.

@@ -637,6 +644,8 @@ ); +

Type could be constrained by a lookup table.

+