Creating the Database

To start with, the most basic command for creating the empty sample database is as follows:

CREATE DATABASE InstantUniversity;

On each of the different database platforms (except for Access), this command creates a new database with the default set of properties assigned to it. For more information, see Chapter 14, "Working with Database Objects."

Note that Access can't create a new database in this manner, so to create the new database in Access, you need to select File Ø New from the main menu. Then you need to create a new blank database, as shown in Figure B-2.

Figure B-2: Creating a new database

Once the database is created, you need to create the tables and relationships. In just a moment you'll look at the design for each table and the code needed to create each table.

It's worth noting that because you'll be using transactions in some of the chapters in this book, the code for creating tables in MySQL is slightly different from that used on other RDBMSs. When working with MySQL, you need to specify a table type that supports transactions and referential integrity: InnoDB. Also, when creating fields that have a data type suitable for storing dates and times, in SQL Server you refer to the data type as DATETIME, but other database platforms use the DATE data type. We'll highlight differences as you encounter them.

Creating the Professor Table

The first table you need to create is the Professor table. This table stores information about the professors at the fictitious university. Table B-1 shows the Professor table definition.

Table B-1: The *Professor* Table
Column	Data Type	Required?	Primary Key
ProfessorID	INTEGER	Yes	Yes
Name	VARCHAR(50)	Yes	No

The SQL code used to create this table is as follows:

CREATE TABLE Professor (
   ProfessorID INT NOT NULL PRIMARY KEY,
   Name         VARCHAR(50) NOT NULL);

As mentioned previously, you need to specify the InnoDB table type when working with MySQL, so you should add one last clause to the statement before executing it:

CREATE TABLE Professor (
   ProfessorID INT NOT NULL PRIMARY KEY,
   Name         VARCHAR(50) NOT NULL)
TYPE = InnoDB;

Creating the Course Table

The Course table stores information on each of the available courses at the university, as shown in Table B-2.

Table B-2: The *Course* Table
Column	Data Type	Required?	Primary Key
CourseID	INTEGER	Yes	Yes
Name	VARCHAR(50)	No	No
Credits	INTEGER	No	No

The code for creating this table is as follows:

CREATE TABLE Course (
   CourseID INT NOT NULL PRIMARY KEY,
   Name     VARCHAR(50),
   Credits INT);

The only platform difference to this example is for MySQL, where you specify the InnoDB table type:

CREATE TABLE Course (
   CourseID INT NOT NULL PRIMARY KEY,
   Name     VARCHAR(50),
   Credits INT)
TYPE = InnoDB;

Creating the Room Table

The Room table stores information on the available rooms in the university that can be used for teaching courses or for sitting exams, as shown in Table B-3.

Table B-3: The *Room* Table
Column	Data Type	Required?	Primary Key
RoomID	INTEGER	Yes	Yes
Comments	VARCHAR(50)	No	No
Capacity	INTEGER	No	No

The SQL used for creating this table is as follows:

CREATE TABLE Room (
   RoomID   INT NOT NULL PRIMARY KEY,
   Comments VARCHAR(50),
   Capacity INT);

Again, you specify the InnoDB table type when using MySQL:

CREATE TABLE Room (
   RoomID   INT NOT NULL PRIMARY KEY,
   Comments VARCHAR(50),
   Capacity INT)
TYPE = InnoDB;

Creating the Class Table

The Class table stores information on where and when a course is being held and who is taking the class. This table relies on the Professor, Course, and Room tables, as shown in Table B-4.

Table B-4: The *Class* Table
Column	Data Type	Required?	Primary Key
ClassID	INTEGER	Yes	Yes
CourseID (Foreign key, table: Course)	INTEGER	Yes	No
ProfessorID (Foreign key, table: Professor)	INTEGER	Yes	No
RoomID (Foreign key, table: Room)	INTEGER	Yes	No
Time	VARCHAR(50)	No	No

Note

We've used a data type of VARCHAR (50) for the Time field. This allows you to enter text to describe when a class is taking place and for how long, rather than entering just a time.

The code for creating this table on platforms other than MySQL is as follows:

CREATE TABLE Class (
   ClassID      INT NOT NULL PRIMARY KEY,
   CourseID     INT NOT NULL,
   ProfessorID  INT NOT NULL,
   RoomID       INT NOT NULL,
   Time         VARCHAR(50),

   CONSTRAINT   FK_Course FOREIGN KEY (CourseID)
                 REFERENCES Course(CourseID),

   CONSTRAINT   FK_Prof FOREIGN KEY (ProfessorID)
                 REFERENCES Professor(ProfessorID),

   CONSTRAINT   FK_Room FOREIGN KEY (RoomID)
                 REFERENCES Room(RoomID));

Note that for Access, if you're creating this table using this SQL statement, you need to enclose Time in square brackets (because Time is a reserved word):

...
   [Time]        VARCHAR(50),
...

The code for creating this table on MySQL is a bit different:

CREATE TABLE Class (
   CourseID     INT NOT NULL,
   ProfessorID  INT NOT NULL,
   RoomID       INT NOT NULL,
   ClassID      INT NOT NULL PRIMARY KEY,
   Time         VARCHAR(50),

   INDEX        course_index(CourseID),
   CONSTRAINT   FK_Course FOREIGN KEY (CourseID)
                 REFERENCES Course(CourseID),

   INDEX        prof_index(ProfessorID),
   CONSTRAINT   FK_Prof FOREIGN KEY (ProfessorID)
                 REFERENCES Professor(ProfessorID),
   INDEX        room_index(RoomID),
   CONSTRAINT   FK_Room FOREIGN KEY (RoomID)
                  REFERENCES Room(RoomID)
) TYPE = InnoDB;

Note that all foreign keys in the table must be indexed when creating an InnoDB table. This topic is covered in more detail in Chapter 14, "Working with Database Objects."

Creating the Student Table

The Student table stores basic information about the students who attend the university, as shown in Table B-5.

Table B-5: The *Student* Table
Column	Data Type	Required?	Primary Key
StudentID	INTEGER	Yes	Yes
Name	VARCHAR(50)	No	No

To create the Student table, use the following SQL code:

CREATE TABLE Student (
   StudentID INT NOT NULL PRIMARY KEY,
   Name       VARCHAR(50) NOT NULL);

And to specify the table type in MySQL, alter the end of the statement as follows:

CREATE TABLE Student (
   StudentID INT NOT NULL PRIMARY KEY,
   Name      VARCHAR(50) NOT NULL
) TYPE = InnoDB;

Creating the Exam Table

The Exam table stores information on the course on which the exam is based, the professor who is marking the exam, the date when the exam is to be held, and any extra comments about the contents of the exam, as shown in Table B-6.

Table B-6: The *Exam* Table
Column	Data Type	Required?	Primary Key
ExamID	INTEGER	Yes	Yes
CourseID (Foreign key, table: Course)	INTEGER	Yes	No
ProfessorID (Foreign key, table: Professor)	INTEGER	Yes	No
SustainedOn	DATE	No	No
Comments	VARCHAR(255)	No	No

The code for creating this table is as follows:

CREATE TABLE Exam (
   ExamID       INT NOT NULL PRIMARY KEY,
   CourseID     INT NOT NULL,
   ProfessorID  INT NOT NULL,
   SustainedOn  DATE,
   Comments     VARCHAR(255),

   CONSTRAINT   FK_ExamCourse FOREIGN KEY (CourseID)
                 REFERENCES Course(CourseID),

   CONSTRAINT   FK_ExamProf FOREIGN KEY (ProfessorID)
                 REFERENCES Professor(ProfessorID));

Note that SQL Server uses DATETIME instead of DATE:

...
   SustainedOn DATETIME,
...

And, again, MySQL has extra indexes and a table type definition:

...
   INDEX        examcourse_index(CourseID),
   CONSTRAINT  FK_ExamCourse FOREIGN KEY (CourseID)
                REFERENCES Course(CourseID),
   INDEX        examprof_index(ProfessorID),
   CONSTRAINT  FK_ExamProf FOREIGN KEY (ProfessorID)
                REFERENCES Professor(ProfessorID)
) TYPE = InnoDB;

Creating the Enrollment Table

The Enrollment table describes which students attend which classes. Of course, a student will attend more than one class, and each class will have more than one student, as shown in Table B-7.

Table B-7: The *Enrollment* Table
Column	Data Type	Required?	Primary Key
EnrollmentID	INTEGER	Yes	Yes
StudentID (Foreign key, table: Student)	INTEGER	Yes	No
ClassID (Foreign key, table: Class)	INTEGER	Yes	No
EnrolledOn	DATE	No	No
Grade	VARCHAR(255)	No	No

You can create this table with the following code:

CREATE TABLE Enrollment (
   EnrollmentID INT NOT NULL PRIMARY KEY,
   StudentID    INT NOT NULL,
   ClassID      INT NOT NULL,
   EnrolledOn   DATE,
   Grade        INT,

   CONSTRAINT   FK_EnrollStudent FOREIGN KEY (StudentID)
                 REFERENCES Student(StudentID),

   CONSTRAINT   FK_EnrollClass FOREIGN KEY (ClassID)
                 REFERENCES Class(ClassID));

Make sure to change DATE to DATETIME for SQL Server:

...
   EnrolledOn DATETIME,
...

Again, MySQL has a couple of extra indexes to add, along with the table type:

...
   INDEX        enrollstudent_index(StudentID),
   CONSTRAINT  FK_EnrollStudent FOREIGN KEY (StudentID)
                REFERENCES Student(StudentID),

   INDEX        enrollclass_index(ClassID),
   CONSTRAINT   FK_EnrollClass FOREIGN KEY (ClassID)
                 REFERENCES Class(ClassID)
) TYPE = InnoDB;

Creating the StudentExam Table

This joining table makes a note of which exams were taken by which students, the grades achieved by each student for each exam taken, and whether the exam was passed (subject to moderation), as shown in Table B-8.

Table B-8: The *StudentExam* Table
Column	Data Type	Required?	Primary Key
StudentID (Foreign key, table: Student)	INTEGER	Yes	Yes
ExamID (Foreign key, table: Exam)	INTEGER	Yes	Yes
Mark	INTEGER	Yes	No
IfPassed	DATE	No	No
Comments	VARCHAR(255)	No	No

To create the table, use the following SQL:

CREATE TABLE StudentExam (
   StudentID  INT NOT NULL,
   ExamID     INT NOT NULL,
   Mark       INT,
   IfPassed   SMALLINT,
   Comments   VARCHAR(255),

   CONSTRAINT PK_StudentExam PRIMARY KEY (StudentID, ExamID),

   CONSTRAINT FK_Student FOREIGN KEY (StudentID)
               REFERENCES Student(StudentID),

   CONSTRAINT FK_Exam FOREIGN KEY (ExamID)
               REFERENCES Exam(ExamID));

And, if you're using MySQL, add the following:

...
   INDEX       student_index (StudentID),
   CONSTRAINT FK_Student FOREIGN KEY (StudentID)
               REFERENCES Student(StudentID),

   INDEX      exam_index (ExamID),
   CONSTRAINT FK_Exam FOREIGN KEY (ExamID)
               REFERENCES Exam(ExamID)
) TYPE = InnoDB;