A relational database is based on the relational model and uses a collection of
tables to represent both data and the relationships among those data. It also in-
cludes a DML and DDL. In Chapter 2 we present a gentle introduction to the
fundamentals of the relational model. Most commercial relational database sys-
tems employ the SQL language, which we cover in great detail in Chapters 3, 4,
and 5. In Chapter 6 we discuss other influential languages.
1.5.1 Tables
Each table has multiple columns and each column has a unique name. Figure 1.2 presents a sample relational database comprising two tables: one shows details of university instructors and the other shows details of the various university departments.
The first table, the instructor table, shows, for example, that an instructor named Einstein with ID 22222 is a member of the Physics department and has an annual salary of $95,000. The second table, department, shows, for example, that the Biology department is located in the Watson building and has a budget of $90,000. Of course, a real-world university would have many more departments and instructors. We use small tables in the text to illustrate concepts. A larger example for the same schema is available online.
The relational model is an example of a record-based model. Record-based models are so named because the database is structured in fixed-format records of several types. Each table contains records of a particular type. Each record type defines a fixed number of fields, or attributes. The columns of the table correspond to the attributes of the record type.
It is not hard to see how tables may be stored in files. For instance, a special character (such as a comma) may be used to delimit the different attributes of a record, and another special character (such as a new-line character) may be used to delimit records. The relational model hides such low-level implementation details from database developers and users.
We also note that it is possible to create schemas in the relational model that have problems such as unnecessarily duplicated information. For example, sup- pose we store the department budget as an attribute of the instructor record. Then, whenever the value of a particular budget (say that one for the Physics depart- ment) changes, that change must to be reflected in the records of all instructors associated with the Physics department. In Chapter 8, we shall study how to distinguish good schema designs from bad schema designs.
1.5.1 Tables
Each table has multiple columns and each column has a unique name. Figure 1.2 presents a sample relational database comprising two tables: one shows details of university instructors and the other shows details of the various university departments.
The first table, the instructor table, shows, for example, that an instructor named Einstein with ID 22222 is a member of the Physics department and has an annual salary of $95,000. The second table, department, shows, for example, that the Biology department is located in the Watson building and has a budget of $90,000. Of course, a real-world university would have many more departments and instructors. We use small tables in the text to illustrate concepts. A larger example for the same schema is available online.
The relational model is an example of a record-based model. Record-based models are so named because the database is structured in fixed-format records of several types. Each table contains records of a particular type. Each record type defines a fixed number of fields, or attributes. The columns of the table correspond to the attributes of the record type.
It is not hard to see how tables may be stored in files. For instance, a special character (such as a comma) may be used to delimit the different attributes of a record, and another special character (such as a new-line character) may be used to delimit records. The relational model hides such low-level implementation details from database developers and users.
We also note that it is possible to create schemas in the relational model that have problems such as unnecessarily duplicated information. For example, sup- pose we store the department budget as an attribute of the instructor record. Then, whenever the value of a particular budget (say that one for the Physics depart- ment) changes, that change must to be reflected in the records of all instructors associated with the Physics department. In Chapter 8, we shall study how to distinguish good schema designs from bad schema designs.
1.5.2 Data-Manipulation Language
The SQL query language is nonprocedural. A query takes as input several tables (possibly only one) and always returns a single table. Here is an example of an SQL query that finds the names of all instructors in the History department:
The SQL query language is nonprocedural. A query takes as input several tables (possibly only one) and always returns a single table. Here is an example of an SQL query that finds the names of all instructors in the History department:
select instructor.name
from instructor
where instructor.dept
name = ’History’;
The query specifies that those rows from the table instructor where the dept
History must be retrieved, and the name attribute of these rows must be displayed.
More specifically, the result of executing this query is a table with a single column labeled name, and a set of rows, each of which contains the name of an instructor
Queries may involve information from more than one table. For instance, the
following query finds the instructor ID and department name of all instructors
associated with a department with budget of greater than $95,000.
where instructor.dept
department.budget > 95000;
(12121, Finance), (45565, Computer Science), (10101, Computer Science), (83821,
Computer Science), and (76543, Finance).
SQL is not as powerful as a universal Turing machine; that is, there are some
computations that are possible using a general-purpose programming language
but are not possible using SQL. SQL also does not support actions such as input
from users, output to displays, or communication over the network. Such com-
putations and actions must be written in a host language, such as C, C++, or Java,
with embedded SQL queries that access the data in the database. Application
programs are programs that are used to interact with the database in this fashion.
whose dept
will consist of two rows, one with the name El Said and the other with the name Califieri.
will consist of two rows, one with the name El Said and the other with the name Califieri.
name, is History. If the query is run on the table in Figure 1.2, the result
select instructor.ID, department.dept
from instructor, department
name
name= department.dept
If the above query were run on the tables in Figure 1.2, the system would find
that there are two departments with budget of greater than $95,000—Computer
Science and Finance; there are five instructors in these departments. Thus, the
name and
result will consist of a table with two columns (ID, dept
1.5.3 Data-Definition Language
SQL provides a rich DDL that allows one to define tables, integrity constraints, assertions, etc.
For instance, the following SQL DDL statement defines the department table: create table department
SQL provides a rich DDL that allows one to define tables, integrity constraints, assertions, etc.
For instance, the following SQL DDL statement defines the department table: create table department
name) and five rows:
(dept
building
budget
char (20),
char (15), numeric (12,2));
char (15), numeric (12,2));
name
Execution of the above DDL statement creates the department table with three
columns: dept
associated with it. We discuss data types in more detail in Chapter 3. In addition, the DDL statement updates the data dictionary, which contains metadata (see Section 1.4.2). The schema of a table is an example of metadata.
1.5.4 Database Access from Application Programs
associated with it. We discuss data types in more detail in Chapter 3. In addition, the DDL statement updates the data dictionary, which contains metadata (see Section 1.4.2). The schema of a table is an example of metadata.
1.5.4 Database Access from Application Programs
Examples in a university system are programs that allow students to register for
courses, generate class rosters, calculate student GPA, generate payroll checks, etc.
To access the database, DML statements need to be executed from the host
language. There are two ways to do this:
language. There are two ways to do this:
-
By providing an application program interface (set of procedures) that can
be used to send DML and DDL statements to the database and retrieve the
results.
The Open Database Connectivity (ODBC) standard for use with the C language is a commonly used application program interface standard. The Java Database Connectivity (JDBC) standard provides corresponding features to the Java language.
-
By extending the host language syntax to embed DML calls within the host
language program. Usually, a special character prefaces DML calls, and a
preprocessor, called the DML precompiler, converts the DML statements to
normal procedure calls in the host language.
Post a Comment