Database Schema

When we talk about a database, we must differentiate between the database
schema, which is the logical design of the database, and the database instance,
which is a snapshot of the data in the database at a given instant in time.

The concept of a relation corresponds to the programming-language notion
of a variable, while the concept of a relation schema corresponds to the
programming-language notion of type definition.

In general, a relation schema consists of a list of attributes and their corresponding
domains. We shall not be concerned about the precise definition of the
domain of each attribute until we discuss the SQL language.

The concept of a relation instance corresponds to the programming-language
notion of a value of a variable. The value of a given variable may changewith time;
similarly the contents of a relation instance may change with time as the relation
is updated. In contrast, the schema of a relation does not generally change.

Although it is important to know the difference between a relation schema
and a relation instance, we often use the same name, such as instructor, to refer
to both the schema and the instance. Where required, we explicitly refer to the
schema or to the instance, for example “the instructor schema,” or “an instance of
the instructor relation.” However, where it is clear whether we mean the schema
or the instance, we simply use the relation name.

Consider the department relation of Figure 2.5. The schema for that relation is

department (dept_name, building, budget)

Note that the attribute dept_name appears in both the instructor schema and the
department schema. This duplication is not a coincidence. Rather, using common
attributes in relation schemas is one way of relating tuples of distinct relations.
For example, suppose we wish to find the information about all the instructors
who work in the Watson building. We look first at the department relation to
find the dept_name of all the departments housed in Watson. Then, for each such
department, we look in the instructor relation to find the information about the
instructor associated with the corresponding dept_name.

Let us continue with our university database example.

Each course in a university may be offered multiple times, across different
semesters, or even within a semester.We need a relation to describe each individual
offering, or section, of the class. The schema is

section (course_id, sec_id, semester, year, building, room_number, time_slot_id)

Figure 2.6 shows a sample instance of the section relation.

We need a relation to describe the association between instructors and the
class sections that they teach. The relation schema to describe this association is

teaches (ID, course_id, sec_id, semester, year)

Figure 2.7 shows a sample instance of the teaches relation.

As you can imagine, there are many more relations maintained in a real university
database. In addition to those relations we have listed already, instructor,
department, course, section, prereq, and teaches,we use the following relations in this
text:

• student (ID, name, dept_name, tot_cred)
• advisor (s_id, i_id)
• takes (ID, course_id, sec_id, semester, year, grade)
• classroom (building, room_number, capacity)
• time slot (time_slot_id, day, start_time, end_time)
 

• student (ID, name, dept name, tot cred)
• advisor (s id, i id)
• takes (ID, course id, sec id, semester, year, grade)
• classroom (building, room number, capacity)
• time slot (time slot id, day, start time, end time)

Frequently Asked Questions