diff --git a/STINK_student_records.xml b/STINK_student_records.xml index 946dcf0..5d99cbc 100644 --- a/STINK_student_records.xml +++ b/STINK_student_records.xml @@ -6,18 +6,18 @@ System specification and details -

The Southern Technical Institute for Natural Knowledge is a medium-sized tertiary education provider (founded in 1982) that teaches papers across many subjects, which lead to several different qualifications. They are currently in the process of designing and implementing a new student records database. The requirements analysis and conceptual design phases of the project are complete, and you have been brought in as lead database developer. It will be your task to implement an initial prototype of the conceptual specification. A design-level entity-relationship diagram of the proposed database is shown in , and more detailed specifications of the database requirements may be found in the following sections. Reminder: In the tables that follow, indicates the unique identifier, * indicates that a value is required, and o indicates that a value is not required.

+

The Southern Technical Institute for Natural Knowledge is a medium-sized tertiary education provider (founded in 1982) that teaches papers across many subjects, which lead to several different qualifications. They are currently in the process of designing and implementing a new student records database. The requirements analysis phase of the project is complete, and you have been brought in as a lead database developer. It will be your task to implement an initial prototype of the specification resulting from the requirements analysis phase. An ERD of the proposed database is shown in , and more detailed specifications of the database requirements may be found in the following sections.

- Design-level ERD of the proposed database - - Design-level ERD of the proposed database + Design-level ERD of the proposed database (Barker notation) + + Design-level ERD of the proposed database (Barker notation)
-
+
The <tt>Qualification</tt> entity @@ -56,36 +56,34 @@ - The abbreviation is a short string that identifies the qualification, e.g., BCom, PGDipCApSc (these are not the only possible values). + The abbreviation is a short string that identifies the qualification, e.g., “BCom”, “PGDipCApSc” (these are not the only possible values). - The qualification type must be one of the following: Degree, Diploma or Certificate. + The qualification type must be one of the following: “Degree”, “Diploma” or “Certificate”. -

QualificationAbbreviation, FullName, Type

- CREATE TABLE Qualification ( Abbreviation VARCHAR2(10), Full_Name VARCHAR2(100) NOT NULL, Type VARCHAR2(11) NOT NULL CONSTRAINT Qualification_Type_Valid - CHECK (Type IN ('Degree', 'Diploma', 'Certificate')), + CHECK ( Type IN ( 'Degree', 'Diploma', 'Certificate' ) ), -- - CONSTRAINT Qualification_PK PRIMARY KEY (Abbreviation) + CONSTRAINT Qualification_PK PRIMARY KEY ( Abbreviation ) ); -

If we wanted to allow for additional qualification types in future, we could create a separate Qualification_Type lookup table, with a single column Type. We could then replace the CHECK constraint Qualification_Type_Valid with a foreign key to the lookup table.

+

If we wanted to allow for additional qualification types in future, we could create a separate Qualification_Type lookup table, with a single column Type. We could then replace the CHECK constraint Qualification_Type_Valid with a foreign key to the lookup table. If we use the existing values, this can even be done without breaking the specification.
-
+
The <tt>Paper</tt> entity @@ -121,7 +119,7 @@ * Points - Integer 036, default 18 + Whole number 0–36, default 18 * @@ -132,55 +130,66 @@ - + -

The period of a paper must be one of the following: SS (Summer School), S1 (Semester One), S2 (Semester Two) or FY (Full Year).

+ + + The paper code comprises a four-letter subject code (e.g., “INFO”) followed by a three digit course number (e.g., “214”). + + The period of a paper must be one of the following: “SS” (Summer School), “S1” (Semester One), “S2” (Semester Two) or “FY” (Full Year). + + -

PaperPaperCode, Title, Description, Points, Period

- CREATE TABLE Paper ( Paper_Code CHAR(7), Title VARCHAR2(50) NOT NULL, Description VARCHAR2(500) NOT NULL, Points NUMBER(2) DEFAULT 18 NOT NULL - CONSTRAINT Paper_Points_Range CHECK (Points BETWEEN 0 AND 36), + CONSTRAINT Paper_Points_Range CHECK ( Points BETWEEN 0 AND 36 ), Period CHAR(2) NOT NULL CONSTRAINT Paper_Period_Valid - CHECK (Period IN ('S1', 'S2', 'SS', 'FY')), + CHECK ( Period IN ( 'S1', 'S2', 'SS', 'FY' ) ), -- - CONSTRAINT Paper_PK PRIMARY KEY (Paper_Code) + CONSTRAINT Paper_PK PRIMARY KEY ( Paper_Code ) ); -

We also need to add an associative entity between Paper and Qualification in order to resolve the many-to-many relationship:

- - - -

ScheduleAbbreviation, PaperCode; Abbreviation FK to Qualification, PaperCode FK to Paper

- - -CREATE TABLE Schedule -( Abbreviation VARCHAR2(10), - Paper_Code CHAR(7), - -- - CONSTRAINT Schedule_PK PRIMARY KEY (Abbreviation, Paper_Code), - CONSTRAINT Schedule_FK_to_Qualification - FOREIGN KEY (Abbreviation) REFERENCES Qualification, - CONSTRAINT Schedule_FK_to_Paper FOREIGN KEY (Paper_Code) REFERENCES Paper -); - -
-
+
- The <tt>Person</tt> entity and its subtypes + The <tt>Schedule</tt> entity + +

The Schedule entity exists only to associate Qualification with Paper and thus has no additional attributes beyond its primary key attributes.

+ + + + +CREATE TABLE Schedule +( Abbreviation VARCHAR2(10), + Paper_Code CHAR(7), + -- + CONSTRAINT Schedule_PK PRIMARY KEY ( Abbreviation, Paper_Code ), + CONSTRAINT Schedule_FK_to_Qualification + FOREIGN KEY ( Abbreviation ) REFERENCES Qualification, + CONSTRAINT Schedule_FK_to_Paper FOREIGN KEY ( Paper_Code ) REFERENCES Paper +); + + + + +
+ + +
+ + The <tt>Person</tt>, <tt>Staff</tt> and <tt>Student</tt> entities @@ -202,7 +211,7 @@ Person_ID - Internally generated 7 digit identifier (see below) + Internally generated 7 digit identifier * @@ -226,6 +235,11 @@ * + Email + Up to 50 characters + + + * Username Up to 10 characters @@ -245,9 +259,9 @@ - * - Email - Up to 50 characters + + Staff_ID + 7 digit identifier * @@ -257,7 +271,7 @@ * Salary - Monetary, 35450.00 + Monetary, 40450.00 @@ -275,9 +289,9 @@ - * - Email - Up to 50 characters + + Student_ID + 7 digit identifier o @@ -298,63 +312,53 @@ + + - You are not required to automatically generate the value of the Person_ID attribute, but bonus marks will be awarded if you are able to do so. (This also applies to the Enrolment_ID and Assessment_ID attributes below.) - - Phone numbers must cater for full international numbers, as many students are from overseas. + Staff and Student are subtypes of Person, and thus share the same primary key values. - A staff members rank must be one of the following: AL (Assistant Lecturer), L (Lecturer), SL (Senior Lecturer), AP (Associate Professor) or P (Professor). + Contact phone numbers must cater for full national landline and mobile numbers. Students’ home phone numbers must cater for full international numbers, as many students are from overseas. + + A staff member’s rank must be one of the following: “AL” (Assistant Lecturer), “L” (Lecturer), “SL” (Senior Lecturer), “AP” (Associate Professor) or “P” (Professor). Salaries for the highest level positions are over 100000. -

PersonPersonID, Surname, OtherNames, ContactPhone, ContactAddress, Username

- - - -

StaffStaffID, Email, Rank, Salary; StaffID FK to Person

- - - -

StaffStudentID, Email, HomePhone, HomeAddress, International, SupervisorID; StudentID FK to Person, SupervisorID FK to Staff

- CREATE TABLE Person ( Person_ID NUMBER(7), Surname VARCHAR2(50) NOT NULL, Other_Names VARCHAR2(50) NOT NULL, - Contact_Phone VARCHAR2(12), -- at least 12, maybe more + Contact_Phone VARCHAR2(11), -- at least 11, maybe more Contact_Address VARCHAR2(200) NOT NULL, Username VARCHAR2(50) NOT NULL CONSTRAINT Person_Username_Unique UNIQUE, -- bonus marks! -- - CONSTRAINT Person_PK PRIMARY KEY (Person_ID) + CONSTRAINT Person_PK PRIMARY KEY ( Person_ID ) ); CREATE TABLE Staff ( Staff_ID NUMBER(7), - Email VARCHAR2(50) NOT NULL, Rank VARCHAR2(2) NOT NULL CONSTRAINT Staff Rank_Valid - CHECK (Rank IN ('AL', 'L', 'SL', 'AP', 'P')), - Salary NUMBER(7,2) NOT NULL - CONSTRAINT Staff_Salary_Range CHECK (Salary >= 35450), + CHECK ( Rank IN ( 'AL', 'L', 'SL', 'AP', 'P' ) ), + Salary NUMBER(8,2) NOT NULL + CONSTRAINT Staff_Salary_Range CHECK ( Salary >= 40450 ), -- - CONSTRAINT Staff_PK PRIMARY KEY (Staff_ID), + CONSTRAINT Staff_PK PRIMARY KEY ( Staff_ID ), CONSTRAINT Staff_FK_to_Person - FOREIGN KEY (Staff_ID) REFERENCES Person (Person_ID) + FOREIGN KEY ( Staff_ID ) REFERENCES Person ( Person_ID ) ); CREATE TABLE Student ( Student_ID NUMBER(7), - Email VARCHAR2(50) NOT NULL, - Home_Phone VARCHAR2(12), + Home_Phone VARCHAR2(15), -- ITU Recommendation E.164 Home_Address VARCHAR2(200) NOT NULL, International CHAR(1) DEFAULT 'F' NOT NULL CONSTRAINT Student_International_Valid - CHECK (International IN ('T', 'F')), + CHECK ( International IN ( 'T', 'F' ) ), Supervisor_ID NUMBER(7), -- optional -- CONSTRAINT Student_PK PRIMARY KEY (Student_ID), @@ -365,42 +369,91 @@ ); -

This solution uses the discrete approach for transforming subtypes. The additive approach is also feasible (i.e., place the Person attributes into both Staff and Student, and eliminate Person entirely). We accepted either approach, but note that the discrete approach has one distinct advantage over the additive approach in this scenario. Consider what happens when someone is both a staff member and a student: we end up duplicating all the person data across both tables. This doesnt occur with the discrete approach. People who didnt discuss this aspect lost marks.

+

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.

-

For those who implemented the additive approach, we marked the person attributes in Staff and Student as if they were a separate Person entity.

- -

The integrated approach was definitely not suitable due to the supervision relationship between Staff and Student (which, incidentally, many people missed completely), and the mandatory attributes that exist in both Staff and Student.

- -

We also need to add an associative entity between Paper and Staff in order to resolve the many-to-many relationship:

- - - -

TeachStaffID, PaperCode; StaffID FK to Staff, PaperCode FK to Paper

- - -CREATE TABLE Teach -( Staff_ID NUMBER(7), - Paper_Code CHAR(7), - -- - CONSTRAINT Teach_PK PRIMARY KEY (Staff_ID, Paper_Code), - CONSTRAINT Teach_FK_to_Staff FOREIGN KEY (Staff_ID) REFERENCES Staff, - CONSTRAINT Teach_FK_to_Paper FOREIGN KEY (Paper_Code) REFERENCES Paper -); - - -

Its important when implementing boolean style columns such as International in Student and Visible 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 discoverpossibly inadvertently, given that it isnt mentioned anywhere in the documentationthat now supports the use of the BOOLEAN data type in CREATE TABLE! This certainly wasnt 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 <tt>Teach</tt> entity + + + + + + + + + + + + Column + Description + + + + + Staff_ID + 7 digit identifier + + + + Paper_Code + 7 characters + + + + Year_Taught + 4 digits + + + * + Role + (see below) + + + + + + + + + + The teaching year cannot be earlier than the year in which the Institute was founded. (Technically it should also not be in the future, but this is surprisingly difficult to check in ! You are welcome to try if you have already completed the rest of the assignment.) + + The role must be one of the following: “Coordinator”, “Lecturer” or “Tutor”. + + + + + + +CREATE TABLE Teach +( Staff_ID NUMBER(7), + Paper_Code CHAR(7), + Year_Taught NUMBER(4), + CONSTRAINT Teach_Year_Taught_Range CHECK ( Year_Taught >= 1982 ), + Role VARCHAR2(11) NOT NULL + CONSTRAINT Teach_Role_Valid + CHECK ( Role IN ( 'Coordinator', 'Lecturer', 'Tutor' ) ), + -- + CONSTRAINT Teach_PK PRIMARY KEY ( Staff_ID, Paper_Code, Year_Taught ), + CONSTRAINT Teach_FK_to_Staff FOREIGN KEY ( Staff_ID ) REFERENCES Staff, + CONSTRAINT Teach_FK_to_Paper FOREIGN KEY ( Paper_Code ) REFERENCES Paper +); + + + + +
-
+
The <tt>Enrolment</tt> entity @@ -442,42 +495,47 @@ - + -

The year of enrolment cannot be earlier than the year in which the Institute was founded. (Technically it should also not be in the future, but we cannot check this in .)

+ + + The year of enrolment cannot be earlier than the year in which the Institute was founded (nor in the future—see the note above under the Teachentity). + + The Comments attribute is used to record details of any issues relating to the enrolment, e.g., a detailed explanation of the reasons for waiving a prerequisite, or a description of how a timetable clash is to be resolved. + + -

Enrolment(EnrolmentID, Description, YearEnrolled, Comments, PaperCode, StudentID); PaperCode FK to Paper, StudentID FK to Student

- CREATE TABLE Enrolment ( Enrolment_ID NUMBER(10), Description VARCHAR2(100) NOT NULL, Year_Enrolled NUMBER(4) NOT NULL - CONSTRAINT Enrolment_Year_Enrolled_Range CHECK (Year_Enrolled >= 1982), + CONSTRAINT Enrolment_Year_Enrolled_Range + CHECK (Year_Enrolled >= 1982), Comments VARCHAR2(4000), -- or CLOB Student_ID NUMBER(7) NOT NULL, Paper_Code CHAR(7) NOT NULL, -- - CONSTRAINT Enrolment_PK PRIMARY KEY (Enrolment_ID), + CONSTRAINT Enrolment_PK PRIMARY KEY ( Enrolment_ID ), CONSTRAINT Enrolment_FK_to_Student - FOREIGN KEY (Student_ID) REFERENCES Student, + FOREIGN KEY ( Student_ID ) REFERENCES Student, CONSTRAINT Enrolment_FK_to_Paper - FOREIGN KEY (Paper_Code) REFERENCES Paper + FOREIGN KEY ( Paper_Code ) REFERENCES Paper );

A surprising number of people appear to have missed the statement of when the Institute was founded in the first paragraph of the specification!

-

Another very common error was to set the size of the Comments column to a relatively small number, like 200. Think about how much you can say in that many characters (one and a bit text messages), then consider what kinds of things you might want to enter into a general comments column. We deducted marks for anything smaller than 500 characters. When you have no idea of how much youre going to get, its much better to go large than to go small!

+

Another very common error was to set the size of the Comments column to a relatively small number, like 200. Think about how much you can say in that many characters (one and a bit text messages), then consider what kinds of things you might want to enter into a general comments column. We deducted marks for anything smaller than 500 characters. When you have no idea of how much you’re going to get, it’s much better to go large than to go small!
-
+
The <tt>Assessment</tt> entity @@ -522,13 +580,13 @@ * - Visible + Release True/false, default false * Weight - 0100, no decimal places + 0–100, no decimal places o @@ -539,42 +597,43 @@ + + - A new set of assessments is created for each year that a paper is offered. The year cannot be earlier than the year in which the Institute was founded. (Technically it should also not be in the future, but we cannot check this in .) + A new set of assessments is created for each year that a paper is offered. The year cannot be earlier than the year in which the Institute was founded (nor in the future—see the note above under the Teachentity). - The assessment type must be one of the following: A (assignment), T (test) or X (exam). + The assessment type must be one of the following: “A” (assignment), “P” (presentation), “T” (test) or “X” (exam). The Maximum_Mark attribute stores the maximum possible raw mark for the assessment (e.g., 30), while the Weight attribute stores the percentage weight of this assessment for the paper as a whole (e.g., 10). If Maximum_Mark is not specified, then applications should use Weight for both. - The Visible attribute controls whether or not the marks for this assessment are publicly accessible. + The Release attribute controls whether or not the marks for this assessment are publicly accessible. -

Assessment(AssessmentID, AssessmentYear, Name, Description, Type, Visible, Weight, MaximumMark, PaperCode); PaperCode FK to Paper

- CREATE TABLE Assessment ( Assessment_ID NUMBER(10), Assessment_Year NUMBER(4) NOT NULL CONSTRAINT Assessment_Year_Range - CHECK (Assessment_Year >= 1982), + CHECK ( Assessment_Year >= 1982 ), Name VARCHAR2(50) NOT NULL, Description VARCHAR2(500), Type CHAR(1) NOT NULL - CONSTRAINT Assessment_Type_Valid CHECK (Type IN ('A', 'T', 'X')), - Visible CHAR(1) DEFAULT 'F' NOT NULL - CONSTRAINT Assessment_Visible_Valid CHECK (Visible IN ('T', 'F')), + CONSTRAINT Assessment_Type_Valid + CHECK ( Type IN ( 'A', 'P', 'T', 'X' ) ), + Release CHAR(1) DEFAULT 'F' NOT NULL + CONSTRAINT Assessment_Release_Valid CHECK ( Release IN ( 'T', 'F' ) ), Weight NUMBER(3) NOT NULL - CONSTRAINT Assessment_Weight_Range CHECK (Weight BETWEEN 0 AND 100), + CONSTRAINT Assessment_Weight_Range CHECK ( Weight BETWEEN 0 AND 100 ), Maximum_Mark NUMBER(3), Paper_Code CHAR(7) NOT NULL, -- - CONSTRAINT Assessment_PK PRIMARY KEY (Assessment_ID), + CONSTRAINT Assessment_PK PRIMARY KEY ( Assessment_ID ), CONSTRAINT Assessment_FK_to_Paper - FOREIGN KEY (Paper_Code) REFERENCES Paper + FOREIGN KEY ( Paper_Code ) REFERENCES Paper ); @@ -583,10 +642,7 @@
- - - -
+
The <tt>Result</tt> entity @@ -627,15 +683,17 @@ * Percentage_Mark - 0100, 2 decimal places + 0–100, 2 decimal places + + - The Raw_Mark attribute stores the raw mark awarded for an assessment. Its value should be between zero and the value of Assessment.Maximum_Mark (note that only the former is required for this assignmentbonus marks if you can also achieve the latter). + The Raw_Mark attribute stores the raw mark awarded for an assessment. Its value should be between zero and the value of Assessment.Maximum_Mark (note that only the former is required for this assignment—bonus marks if you can also achieve the latter). The value of the Weighted_Mark attribute is calculated by the formula: Raw_Mark / Assessment.Maximum_Mark Assessment.Weight. @@ -649,8 +707,6 @@ -

Result(AssessmentID, EnrolmentID, RawMark, WeightedMark, PercentageMark); AssessmentID FK to Assessment, EnrolmentID FK to Enrolment

- CREATE TABLE Result ( Assessment_ID NUMBER(10), @@ -661,11 +717,11 @@ CONSTRAINT Result_Percentage_Mark_Range CHECK (Percentage_Mark BETWEEN 0 AND 100), -- - CONSTRAINT Result_PK PRIMARY KEY (Assessment_ID, Enrolment_ID), + CONSTRAINT Result_PK PRIMARY KEY ( Assessment_ID, Enrolment_ID ), CONSTRAINT Result_FK_to_Assessment - FOREIGN KEY (Assessment_ID) REFERENCES Assessment, + FOREIGN KEY ( Assessment_ID ) REFERENCES Assessment, CONSTRAINT Result_FK_to_Enrolment - FOREIGN KEY (Enrolment_ID) REFERENCES Enrolment + FOREIGN KEY ( Enrolment_ID ) REFERENCES Enrolment );