Page 39
Telephone
Fax
Year in School
Primary key: Student ID
By creating the Department table, you are ensuring that other tables refer to a department in a consistent manner. The Department table list displays the simple structure of the Department table. The primary key for the Department table is the Department ID.
Department
Department ID
Description
Primary key: Department ID
Information about each instructor is kept in the Instructor table, as shown in the following list. The primary key for the table is Instructor ID. There also is one foreign key—Department ID—which points to the Department table.
Instructor
Instructor ID
Department ID (foreign key to Department table)
Last Name
First Name
Middle Initial
Position
Telephone
Fax
Primary key: Instructor ID
In the Flugle database, each instructor is assigned to a single department, which is identified by Department ID. Of course, in a real college or university, you wouldn't want to impose such an artificial limitation.
Each department offers a series of courses. Because it's possible for two departments to offer a course with the same number, the primary key for the Course table includes both the Course ID and the Department ID. In addition to the primary key attributes, the following list
Page 40
contains the attributes for a course—a title, description, a number of units, and additional fees that may be required.
Course
Department ID (foreign key to Department table)
Course ID
Title
Description
Units
Additional Fees
Primary key: Course ID
As an alternative, the primary key for the Course table could consist solely of the Course ID attribute. But that would require that Course ID be unique across all departments. This requirement could be satisfied if a Course ID concatenated the Department ID with the Course ID—for example, BIO101 for Biology 101. However, the drawback to this approach is that it is a violation of First Normal Form—namely, Course ID now contains two pieces of information, department and course. Although this is a real drawback, in practice you will find many examples of primary key values that are composed in this manner.
With this table, a college administrator will be able to determine the appropriate location for a class, based on the seating capacity. The primary key for the Class Location table consists of both the class building and the class room, as shown in the following list. In a real application, you might want to know more about the class location—such as the availability of other amenities such as projection screens or microphones.
Class Location
Class Building
Class Room
Seating Capacity
Primary key: Class Building, Class Room
The purpose of this table is to describe different types of class schedules. An example of a schedule type is three meetings per week. The primary key for this table is Schedule ID. The following contains a description of the Schedule Type table.
Schedule Type
Schedule ID
Description
Primary key: Schedule ID
Page 41
The Schedule Type Details table contains the details about a particular schedule type, such as the day, starting time, and duration for each meeting of a class with this schedule type. For example, if schedule ID T10 consists of three meetings per week at 10:00 AM, there will be three rows in the Schedule Type Details table. One row will have an entry for Monday at 10:00, another row will have an entry for Wednesday at 10:00, and the final row will have an entry for Friday at 10:00. Because duration is specified for each row, the table can accommodate a schedule type in which the duration could be different, depending upon which day the meeting was held. The following describes the structure of the Schedule Type Details table.
Schedule Type Details
Schedule ID (foreign key to Schedule Type table)
Day
Starting Time
Duration
Primary key: Schedule ID, Day
The Class table contains the specifics of each course offered by the college during a particular semester. You can think of a class as an instantiation of a course—therefore, the Class table must include the Course ID and the Department ID. The following list outlines the structure of the Class table. Each class must have a location and a schedule. And, of course, each class must be taught by an instructor.
Class
Class ID
Department ID (foreign key to Course table)
Course ID (foreign key to Course table)
Instructor ID (foreign key to Instructor table)
Schedule ID (foreign key to Schedule Type table)
Class Building (foreign key to Class Location table)
Class Room (foreign key to Class Location table)
Semester
School Year
Primary key: Class ID
The Student Schedule table is designed to serve two purposes. First, it records the grade that a student has received for each class. Second, for classes that have not yet been completed,
Page 42
it identifies which course is part of a student's current schedule. In a more realistic database, you would probably want to separate the classes that a student has completed from a student's current schedule. The primary key of the table is composed of both Student ID and Class ID. The following describes the structure of the Student Schedule table.
Student Schedule
Student ID (foreign key to Student table)
Class ID (foreign key to Class table)
Grade
Date Grade Assigned
Primary key: Student ID, Class ID
Before you learn how to create a table during the lesson on Day 4, you need to feel comfortable with the terminology used in this book, in the various Oracle tools, and in the Oracle doc-umentation. Two terms—database connection and database user—merit special attention.
A database connection is an Oracle user who has supplied a username and password to an Oracle database and is able to submit SQL statements to the Oracle database.A database user is an individual or automatic process that has a unique name and password that is recognized by a specific Oracle database.
To connect to Oracle means to supply a valid username and password that is accepted by the Oracle database. A user can connect to an Oracle database using any of the Oracle tools. A user also can establish an Oracle connection with a third-party tool such as PowerBuilder. The terms Oracle session, Oracle connection, and database connection are often substituted for one another. They all refer to the same thing—the tasks performed by a database user from the time the user successfully connects to an Oracle database until the time the same user disconnects.
An important characteristic of a database connection is whether it is local or remote. The term local connection indicates that the program used by the user to connect to an Oracle database resides on the same machine where the Oracle database is located. The term remote connection indicates that the program used by the user to connect to an Oracle database resides on one machine and the Oracle database resides on a different machine.