Database Schema

When we talk about a database, we must differentiate between the database
, 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.

Database Schema

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)

Database Schema

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

Database Schema

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

Ans: A relational database consists of a collection of tables, each of which is assigned a unique name. view more..
Ans: Information processing drives the growth of computers, as it has from the earliest days of commercial computers. In fact, automation of data processing tasks predates computers. view more..
Ans: A primary goal of a database system is to retrieve information from and store new information into the database. People who work with a database can be categorized as database users or database administrators. view more..
Ans: The database schema is the logical design of the database. view more..
Ans: A super-key is a set of one or more attributes that, taken collectively, allow us to identify uniquely a tuple in the relation. view more..
Ans: DBMS typically includes a database security and authorization subsystem that is responsible for ensuring the security of portions of a database against unauthorized access view more..
Ans: The typical method of enforcing discretionary access control in a database system is based on the granting and revoking of privileges. Let us consider privileges in the context of a relational DBMS. view more..
Ans: This chapter discusses techniques for securing databases against a variety of threats. It also presents schemes of providing access privileges to authorized users. view more..
Ans: This chapter discusses techniques for securing databases against a variety of threats. It also presents schemes of providing access privileges to authorized users. view more..
Ans: Object databases is the power they give the designer to specify both the structure of complex objects and the operations that can be applied to these objects view more..
Ans: XML (Extensible Markup Language)—has emerged as the standard for structuring and exchanging data over the Web. XML can be used to provide information about the structure and meaning of the data in the Web pages rather than just specifying how the Web pages are formatted for display on the screen view more..
Ans: A database schema, along with primary key and foreign key dependencies, can be depicted by schema diagrams. view more..
Ans: A query language is a language in which a user requests information from the database. view more..
Ans: All procedural relational query languages provide a set of operations that can be applied to either a single relation or a pair of relations. view more..
Ans: An object database is a database management system in which information is represented in the form of objects as used in object-oriented programming. Object databases are different from relational databases which are table-oriented. Object-relational databases are a hybrid of both approaches. view more..
Ans: IBM developed the original version of SQL, originally called Sequel, as part of the System R project in the early 1970s. view more..
Ans: The set of relations in a database must be specified to the system by means of a data-definition language (DDL). view more..
Ans: The basic structure of an SQL query consists of three clauses: select, from, and where. view more..

Rating - 4/5