Relational Operations
All procedural relational query languages provide a set of operations that can be applied to either a single relation or a pair of relations. These operations have the nice and desired property that their result is always a single relation. This property allows one to combine several of these operations in a modular way. Specifically, since the result of a relational query is itself a relation, relational operations can be applied to the results of queries as well as to the given set of relations.
The most frequent operation is the selection of specific tuples from a single relation (say instructor) that satisfies some particular predicate (say salary > $85,000). The result is a new relation that is a subset of the original relation(instructor). For example, if we select tuples from the instructor relation of Figure 2.1, satisfying the predicate “salary is greater than $85000”, we get the result shown in Figure 2.10.
Another frequent operation is to select certain attributes (columns) from a relation.The result is a new relation having only those selected attributes. For example, suppose we want a list of instructor ID and salaries without listing the name and dept_name values from the instructor relation of Figure 2.1, then the result shown in Figure 2.11, has the two attributes ID and salary. Each tuple in the result is derived from a tuple of the instructor relation but with only selected attributes shown.
The join operation allows the combining of two relations by merging pairs of
tuples, one from each relation, into a single tuple. There are a number of different
ways to join relations. Figure 2.12 shows an example
of joining the tuples from the instructor and department tables with the new tuples
showing the information about each instructor and the department in which she
is working. This result was formed by combining each tuple in the instructor
relation with the tuple in the department relation for the instructor’s department.
In the form of join shown in Figure 2.12, which is called a natural join, a tuple
from the instructor relation matches a tuple in the department relation if the values of their dept_name attributes are the same. All such matching pairs of tuples are present in the join result. In general, the natural join operation on two relations
matches tuples whose values are the same on all attribute names that are common
to both relations.
from the instructor relation matches a tuple in the department relation if the values of their dept_name attributes are the same. All such matching pairs of tuples are present in the join result. In general, the natural join operation on two relations
matches tuples whose values are the same on all attribute names that are common
to both relations.
The Cartesian product operation combines tuples from two relations, but unlike
the join operation, its result contains all pairs of tuples from the two relations,
regardless of whether their attribute values match.
the join operation, its result contains all pairs of tuples from the two relations,
regardless of whether their attribute values match.
Because relations are sets, we can perform normal set operations on relations.
The union operation performs a set union of two “similarly structured” tables
(say a table of all graduate students and a table of all undergraduate students).
For example, one can obtain the set of all students in a department. Other set
operations, such as intersection and set difference can be performed as well.
The union operation performs a set union of two “similarly structured” tables
(say a table of all graduate students and a table of all undergraduate students).
For example, one can obtain the set of all students in a department. Other set
operations, such as intersection and set difference can be performed as well.
As we noted earlier, we can perform operations on the results of queries. For
example, if we want to find the ID and salary for those instructors who have salary
greater than $85,000, we would perform the first two operations in our example
above. First we select those tuples from the instructor relation where the salary
value is greater than $85,000 and then, from that result, select the two attributes
ID and salary, resulting in the relation shown in Figure 2.13 consisting of the ID and salary. In this example, we could have performed the operations in either
order, but that is not the case for all situations, as we shall see.
example, if we want to find the ID and salary for those instructors who have salary
greater than $85,000, we would perform the first two operations in our example
above. First we select those tuples from the instructor relation where the salary
value is greater than $85,000 and then, from that result, select the two attributes
ID and salary, resulting in the relation shown in Figure 2.13 consisting of the ID and salary. In this example, we could have performed the operations in either
order, but that is not the case for all situations, as we shall see.
Sometimes, the result of a query contains duplicate tuples. For example, if we
select the dept_name attribute from the instructor relation, there are several cases
of duplication, including “Comp. Sci.”, which shows up three times. Certain relational languages adhere strictly to the mathematical definition of a set and remove
duplicates. Others, in consideration of the relatively large amount of processing
required to remove duplicates from large result relations, retain duplicates. In
these latter cases, the relations are not truly relations in the pure mathematical
sense of the term.
select the dept_name attribute from the instructor relation, there are several cases
of duplication, including “Comp. Sci.”, which shows up three times. Certain relational languages adhere strictly to the mathematical definition of a set and remove
duplicates. Others, in consideration of the relatively large amount of processing
required to remove duplicates from large result relations, retain duplicates. In
these latter cases, the relations are not truly relations in the pure mathematical
sense of the term.
Of course, data in a database must be changed over time. A relation can be
updated by inserting new tuples, deleting existing tuples, or modifying tuples by
changing the values of certain attributes. Entire relations can be deleted and new
ones created.
updated by inserting new tuples, deleting existing tuples, or modifying tuples by
changing the values of certain attributes. Entire relations can be deleted and new
ones created.
Frequently Asked Questions
+
Ans: A query language is a language in which a user requests information from the database. view more..
+
Ans: A database schema, along with primary key and foreign key dependencies, can be depicted by schema diagrams. 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: 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..
+
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: The natural join operation operates on two relations and produces a relation
as the result. view more..
+
Ans: Reason to rename a relation is a case where we wish to compare
tuples in the same relation . view more..
+
Ans: SQL permits a variety of functions on character strings. Read to know about them. view more..
+
Ans: The company is organized into departments. Each department has a unique name, a unique number, and a particular employee who manages the department. We keep track of the start date when that employee began managing the department. A department may have several locations.
A department controls a number of projects, each of which has a unique name, a unique number, and a single location view more..
+
Ans: Entities and Their Attributes. The basic object that the ER model represents is an entity, which is a thing in the real world with an independent existence. An entity may be an object with a physical existence (for example, a particular person, car, house, or employee) view more..
+
Ans: A database usually contains groups of entities that are similar. For example, a company employing hundreds of employees may want to store similar information concerning each of the employees. These employee entities share the same attributes, but each entity has its own value(s) for each attribut view more..
+
Ans: An entity type DEPARTMENT with attributes Name, Number, Locations, Manager, and Manager_start_date. Locations is the only multivalued attribute. We can specify that both Name and Number are (separate) key attributes because each was specified to be unique view more..
+
Ans: There are several implicit relationships among the various entity types. In fact, whenever an attribute of one entity type refers to another entity type, some relationship exists. For example, the attribute Manager of DEPARTMENT refers to an employee who manages the department; the attribute view more..
+
Ans: Select clause uses. view more..
Recommended Posts:
- Characteristics of the Database Approach
- View of Data
- Subclasses, Superclasses, and Inheritance
- what is database management system
- Database-System Applications
- Specialization and Generalization
- Constraints and Characteristics of Specialization and Generalization Hierarchies
- Modeling of UNION Types Using Categories
- A Sample UNIVERSITY EER Schema, Design Choices, and Formal Definitions
- Example of Other Notation: Representing Specialization and Generalization in UML Class Diagrams
- Data Abstraction, Knowledge Representation, and Ontology Concepts
- Using High-Level Conceptual Data Models for Database Design
- Using High-Level Conceptual Data Models for Database Design
- Using High-Level Conceptual Data Models for Database Design
- Using High-Level Conceptual Data Models for Database Design
Rating - 3/5
Advertisements