5
\$\begingroup\$

I am building a database for a hypothetical university. This is my SQL definition, with generated data added for my future use, such as demonstrating queries:

Create Table Semesters (
    Semester varchar(6) Primary Key Not Null,
);

Insert Into UniversityDatabase.dbo.Semesters
Values ('Spring'),
       ('Summer'),
       ('Fall');

Create Table Grades (
    Grade_Letter char(1) Primary Key,
);

Insert Into UniversityDatabase.dbo.Grades
Values ('A'),
       ('B'),
       ('C'),
       ('D'),
       ('F'),
       ('S'),
       ('N');

Create Table LectureSeries (
    Lecture_Series char(3) Primary Key Not Null,
);

Insert Into UniversityDatabase.dbo.LectureSeries
Values ('001'),
       ('002'),
       ('003'),
       ('E90'),
       ('E91'),
       ('E92');

Create Table Departments (
    Department_Code varchar(4) Primary Key,
    Department_Name varchar(30) Unique Not Null,
);

Insert Into UniversityDatabase.dbo.Departments
Values ('ECON', 'Economics'),
       ('PHYS', 'Physics'),
       ('FIN', 'Finance'),
       ('LEGL', 'Legal'),
       ('CHEM', 'Chemistry'),
       ('CS', 'Computer Science'),
       ('PSY', 'Psychology'),
       ('PHIL', 'Philosophy');

Create Table [Degrees] (
    Degree_ID int Identity(10000,1) Primary Key,
    Department varchar(4) Foreign Key References Departments(Department_Code) Not Null,
    [Description] varchar(200) Not Null,
);

Insert Into UniversityDatabase.dbo.[Degrees]
Values ('ECON', 'Economics BS'),
       ('PHYS', 'Physics BS'),
       ('FIN', 'Business Finance BA'),
       ('LEGL', 'Constitutional Law BA'),
       ('CHEM', 'Chemistry BS'),
       ('CS', 'Computer Engineering BS'),
       ('PSY', 'Psychology BS'),
       ('PHIL', 'Philosophy BA');

Create Table Instructors (
    Instructor_ID int Identity(10000,1) Primary Key,
    FName nvarchar(200) Not Null,
    LName nvarchar(200) Not Null,
    Year_Hired int Not Null,
);

Insert Into UniversityDatabase.dbo.Instructors
Values ('Bill', 'Smith', 1995),
       ('James', 'Peterson', 1997),
       ('Adam', 'Thompson', 1998),
       ('Janetta', 'Oakley', 2001),
       ('Robin', 'Dexter', 2003),
       ('Annie', 'Jackson', 2004),
       ('Philip', 'Petrovsky', 2006),
       ('Anastasia', 'Scott', 2013);

Create Table Students (
    Student_ID int Identity(10000,1) Primary Key,
    FName nvarchar(200) Not Null,
    LName nvarchar(200) Not Null,
    Degree int Foreign Key References [Degrees](Degree_ID) Not Null,
    Current_GPA real,
);

Insert Into UniversityDatabase.dbo.Students
Values ('Jack', 'Johnson', 10005, null),
       ('Dolly', 'Denver', 10000, 2.89),
       ('Helmut', 'Ziegler', 10003, 3.04),
       ('Robert', 'Thompson', 10004, 3.83),
       ('Jeffrey', 'Petersen', 10006, null),
       ('Jack', 'Pirate', 10002, null),
       ('Barb', 'Wire', 10001, null),
       ('Roberta', 'Strong', 10004, null),
       ('Heather', 'Black', 10002, 3.94),
       ('Erik', 'Bjornsen', 10002, 3.57);

Create Table ClassLibrary (
    Department varchar(4) Foreign Key References Departments(Department_Code),
    Class_Number int Not Null,
    Class_Name varchar(200) Not Null,
    Available_Online bit Not Null,
    Credits int Not Null,
    Primary Key (Department, Class_Number),
);

Insert Into UniversityDatabase.dbo.ClassLibrary
Values ('ECON', '1015', 'Microeconomics', 1, 3),
       ('ECON', '1016', 'Macroeconomics', 1, 3),
       ('PHYS', '4364', 'Quantum Mechanics', 0, 4),
       ('CHEM', '1001', 'Introduction to Chemistry', 1, 4),
       ('CS', '3060', 'Algorithms I', 1, 3),
       ('CS', '3061', 'Algorithms II', 1, 3),
       ('PSY', '1009', 'Introduction to Psychology', 1, 3),
       ('PHIL', '1001', 'Introduction to Philosophy', 1, 3);

-- This table says which classes are offered in which semesters
Create Table ClassesSemester (
    Class_ID int Identity(10000,1) Primary Key,
    Department varchar(4),
    Class_Number int,
    Lecture_Series char(3) Foreign Key References LectureSeries(Lecture_Series) Not Null,
    [Year] int Not Null,
    Semester varchar(6) Foreign Key References Semesters(Semester) Not Null,
    Instructor int Foreign Key References Instructors(Instructor_ID) Not Null,
    Seats_Available int Not Null,
    Foreign Key (Department, Class_Number) References ClassLibrary,
    Unique ([Year], Semester, Department, Class_Number, Lecture_Series),
);

Insert Into UniversityDatabase.dbo.ClassesSemester
Values ('ECON', 1015, 'E90', 2015, 'Fall', 10001, 30),
       ('ECON', 1015, '001', 2015, 'Fall', 10001, 30),
       ('ECON', 1016, 'E90', 2015, 'Fall', 10001, 30),
       ('ECON', 1016, 'E91', 2015, 'Fall', 10001, 30),
       ('PHIL', 1001, 'E90', 2015, 'Fall', 10006, 40),
       ('PHIL', 1001, '001', 2015, 'Fall', 10006, 40),
       ('CS', 3060, 'E90', 2015, 'Fall', 10000, 30),
       ('CS', 3061, 'E90', 2015, 'Fall', 10000, 30),
       ('CHEM', 1001, '001', 2015, 'Fall', 10002, 25),
       ('ECON', 1015, '001', 2016, 'Spring', 10001, 30),
       ('ECON', 1016, 'E90', 2016, 'Spring', 10001, 30),
       ('ECON', 1016, 'E91', 2016, 'Spring', 10001, 30),
       ('PHIL', 1001, 'E90', 2016, 'Spring', 10006, 40),
       ('PHIL', 1001, '001', 2016, 'Spring', 10006, 40),
       ('CHEM', 1001, '001', 2016, 'Spring', 10002, 25);

-- This table tells us which classes students have taken/are taking
Create Table StudentClasses (
    Student_ID int Foreign Key References Students(Student_ID),
    Class_ID int Foreign Key References ClassesSemester(Class_ID),
    Grade char(1) Foreign Key References Grades(Grade_Letter),
    Primary Key (Student_ID, Class_ID),
);

Insert Into UniversityDatabase.dbo.StudentClasses
Values (10000, 10000, 'B'),
       (10000, 10001, 'B'),
       (10001, 10000, 'C'),
       (10002, 10000, null),
       (10004, 10000, null),
       (10008, 10006, 'A'),
       (10008, 10007, null);

-- This table tells us which instructors can teach which class
Create Table InstructorClasses (
    Instructor_ID int Foreign Key References Instructors(Instructor_ID),
    Department varchar(4),
    Class_Number int,
    Foreign Key (Department, Class_Number) References ClassLibrary,
);

Insert Into UniversityDatabase.dbo.InstructorClasses
Values (10000, 'CS', 3060),
       (10000, 'CS', 3061),
       (10001, 'ECON', 1015),
       (10001, 'ECON', 1016),
       (10002, 'CHEM', 1001),
       (10006, 'PHIL', 1001),
       (10005, 'CS', 3060),
       (10005, 'CS', 3061),
       (10006, 'PHIL', 1001);

Feel free to comment on any and all of it. I am most interested in reviews on my design - are my relationships built correctly? Do any of my tables have too much responsibility?

\$\endgroup\$
2
\$\begingroup\$

Smaller issues


Full referencing of tables

There is a consistent inconsistency in your script.

Create Table Semesters (
    --...
);

Insert Into UniversityDatabase.dbo.Semesters
Values --...

On the one hand, you are creating the table without any reference, and the other you are fully referencing it while inserting. Perhaps a better way of doing this is declaring the database catalog you want to use at the top, and then just reference the schema and table. SQL Server will always use that database catalog until it encounters another USE statement with a different database.

Use UniversityDatabase;
GO
Create Table dbo.Semesters (
    --...
);

Insert Into dbo.Semesters
Values --...

This is pretty minor, but it helps improve DRY as well as possible ambiguity if you have multiple schemas, which is not uncommon. The only time fully-qualified references are needed are when querying across database catalogs (fairly common), or across linked server instances (much more rare, and there are usually better solutions).


Implicit Inserts

You are using implicit Inserts without referencing columns, which is not only more error prone, as it relies on the column IDs and the order they are in, which can break if columns are added, removed, etc. (which can and does happen in real environments).

It also makes the code harder to read, as you need to go back to the table definition to know for sure what columns the data is getting inserted in. So, instead of doing this:

Insert Into dbo.Students
Values ('Jack', 'Johnson', 10005, null),
       ('Dolly', 'Denver', 10000, 2.89),
       --...

It is better to do this:

Insert Into dbo.Students
    (FName, LName, Degree, Current_GPA)
Values 
    ('Jack', 'Johnson', 10005, null),
    ('Dolly', 'Denver', 10000, 2.89),
    --...

This will become especially important as you start working with bigger scripts like stored procedures and such, that can insert data in dozens of rows, and not necessarily all in one operation.


FName, LName

I think your naming overall is quite good. But I would change these to be First_Name and Last_Name just to be consistent, and make it read easier. This will save you some annoying aliasing in everyday queries when you're sending data sets (reports etc.) to non-IT people who may think FName and LName are not very user-friendly, or you always having to do:

Select 
  FName As First_Name, 
  LName As Last_Name
From dbo.Students
Order By LName Asc, FName Asc;

Bigger issues


Auto-commit mode

You are currently running the script in auto-commit mode, which means that you're leaving it up to the SQL engine to decide on your behalf when to begin and commit transactions. While it may do a fine job at it usually, imagine if you had an error (say a key violation or data-type error) halfway down your script, chances are it has already committed the operations that came before that part. In a small script like this, in a "virgin" database, this may not be a big deal, just delete the database and start over. But on a production environment, many tables have triggers that fire once for example an insert transaction is committed, which then can be extremely frustrating and time-consuming to undo.

So, ideally, at least wrap the whole script into one transaction. More details coming in a minute.

Use UniversityDatabase;
GO
Begin Transaction;
  --do all the work you need
Commit Transaction;

No error handling

Ideally, we would want to handle the transaction differently depending on whether or not there are errors/problems during it. Good news is that SQL supports TRY/CATCH operations...

Begin Transaction;
Begin Try
    Insert Into Foo (Bar) Values ('Hello'), ('World');
    --whatever other work you need done
    Commit Transaction;
End Try
Begin Catch
    Rollback Transaction;
    --Get information about the error:
    Select
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;
End Catch

So this way, not only will it rollback the transaction if something goes wrong, but also will give you all the details about why it failed. In bigger scripts you can also use RAISERROR() (not a typo, only one "E") to define your own error handling, like you would in traditional programming. For example:

Begin Try
  If (1=0)
  Begin
    Declare 
        @ErrMessage Varchar(200) = 'Something went wrong',
        @ErrSeverity Int = 16, --Severity from 11 to 20 redirects to Catch block
        @ErrState Int = 1;     --Multiple states from 0-255 can be used in different areas of the script, to help identify errors in specific sections
    Raiserror(
      @ErrMessage, 
      @ErrSeverity, 
      @ErrState
    );
  End
End Try
Begin Catch
  --Goes here after Raiserror is encountered in If statement, with severity 11-20
End Catch

Another thing you will see frequently in production environments is there will be a stored procedure (or multiple for different use cases) which have some of the error code built-in, for example from TechNet:

-- Create a procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;

Then you just execute it in your catch block each time you need it:

BEGIN TRY
    -- Generate divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
    -- Execute the error retrieval routine.
    EXECUTE usp_GetErrorInfo;
END CATCH;

Nitpick on casing

One last little nitpick, while your casing of SQL keywords is consistent, it's quite rare that you see Select Foo From Bar Order By Baz Asc, and for a few reasons. Besides the fact that it looks like VB, it mostly makes it a bit more tiring to look at keywords vs. symbols if the casing is the same. The most common casing for SQL is SHOUT CASE, followed by lower case keywords.

\$\endgroup\$
2
  • \$\begingroup\$ Excellent comments. I was wondering if there was a way to avoid manually setting which database ran the script every time. The rest of it is good information, but because this is so small and will be completely dropped within a couple weeks, I don't know if I will bother setting up the error handling, etc. As for the casing, I can't stand SHOUT CASE, so I think I'll just leave it the way it is. \$\endgroup\$ – user34073 Nov 21 '15 at 23:12
  • \$\begingroup\$ Well, error handling you would just need to start your try at the very top, add your catch at the bottom, and that's pretty much it! (also, I cannot stand SHOUT CASE either, so I use lower case ;) \$\endgroup\$ – Phrancis Nov 21 '15 at 23:14
2
\$\begingroup\$

As someone who works at a large university, I'd say that this schema is refreshingly simple. In my experience, any assumption that you care to make about business rules is probably false. Some examples:

  • One summer semester? Nah, let's have multiple summer sessions, some overlapping with others!
  • One course number per course? Nah, let's have some courses cross-listed (like how some flights are code-shared between airlines)!
  • One degree per student? That precludes double majors and graduate degrees.
  • Each course is identified by a department code? The Department of Electrical and Computer Engineering demands to list some of its courses as EE 123 and other courses as CE 123! And they want to renumber the courses when they revise the curriculum!
  • Course numbers are numbers? We need suffixes like ECON 99A!
  • Instructors only get hired once? No, some of them come and go.
  • Courses can have co-instructors.

Anyway, I know you're just doing this for fun, so enough ranting. I'll just point out a few ideas without bloating your scope into a multi-million-dollar project.

  • You have the Current_GPA as an attribute of Students. That makes the database more of a reporting database than a system of record. I would expect the GPA to be derivable from grade records. In any case, you should let the Student table be purely about identity; your GPA does not help define who you are.
  • Semesters would benefit from having a sequencing attribute to aid with ORDER BY. Grades could probably use a second column for GPA equivalents.
  • Are the Student_ID and Instructor_ID numbers intended to be used as public identifiers? From a human-factors point of view, having them in the same namespace could be confusing. You could design student numbers to have more digits than instructor numbers. Alternatively, unify the namespaces, and issue a University ID to every person, regardless of affiliation.
\$\endgroup\$
1
  • \$\begingroup\$ ++ for "any assumption that you care to make about business rules is probably false" \$\endgroup\$ – Phrancis Nov 22 '15 at 19:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy