Additional Basic Operations-The Rename Operation

Consider this query :

select name, course_id
from instructor, teaches
where instructor.ID= teaches.ID;

The result of this query is a relation with the following attributes:

name, course_id

The names of the attributes in the result are derived from the names of the
attributes in the relations in the from clause.

We cannot, however, always derive names in this way, for several reasons:
First, two relations in the from clause may have attributes with the same name,
in which case an attribute name is duplicated in the result. Second, if we used an
arithmetic expression in the select clause, the resultant attribute does not have
a name. Third, even if an attribute name can be derived from the base relations
as in the preceding example, we may want to change the attribute name in the
result. Hence, SQL provides a way of renaming the attributes of a result relation.
It uses the as clause, taking the form:

old-name as new-name

The as clause can appear in both the select and from clauses.

For example, if we want the attribute name name to be replaced with the name
instructor_name, we can rewrite the preceding query as:

select name as instructor_name, course_id
from instructor, teaches
where instructor.ID= teaches.ID;

The as clause is particularly useful in renaming relations. One reason to
rename a relation is to replace a long relation name with a shortened version that
is more convenient to use elsewhere in the query. To illustrate, we rewrite the
query “For all instructors in the university who have taught some course, find
their names and the course ID of all courses they taught.”

select, S.course_id
from instructor as T, teaches as S
where T.ID= S.ID;

Another reason to rename a relation is a case where we wish to compare
tuples in the same relation. We then need to take the Cartesian product of a
relation with itself and, without renaming, it becomes impossible to distinguish
one tuple from the other. Suppose that we want to write the query “Find the
names of all instructors whose salary is greater than at least one instructor in the
Biology department.” We can write the SQL expression:

select distinct
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = ’Biology’;

Observe that we could not use the notation instructor.salary, since it would not be
clear which reference to instructor is intended.

In the above query, T and S can be thought of as copies of the relation instructor,
but more precisely, they are declared as aliases, that is as alternative names, for the
relation instructor. An identifier, such as T and S, that is used to rename a relation
is referred to as a correlation name in the SQL standard, but is also commonly
referred to as a table alias, or a correlation variable, or a tuple variable.

Note that a better way to phrase the previous query in English would be “Find
the names of all instructors who earn more than the lowest paid instructor in the
Biology department.” Our original wording fits more closely with the SQL that
we wrote, but the latter wording is more intuitive, and can in fact be expressed
directly in SQL.


Frequently Asked Questions

Ans: The natural join operation operates on two relations and produces a relation as the result. 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 basic structure of an SQL query consists of three clauses: select, from, and where. 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..
Ans: Entity types that do not have key attributes of their own are called weak entity types. In contrast,regular entity types that do have a key attribute—which include all the examples discussed so far—are called strong entity types view more..
Ans: If some cardinality ratio or dependency cannot be determined from the requirements, the users must be questioned further to determine these structural constraints view more..
Ans: The individual entity instances in an entity set and the individual relationship instances in a relationship set. In ER diagrams the emphasis is on representing the schemas rather than the instances. view more..
Ans: The UML methodology is being used extensively in software design and has many types of diagrams for various software design purposes. We only briefly present the basics of UML class diagrams here, and compare them with ER diagrams view more..
Ans: we defined the degree of a relationship type as the number of participating entity types and called a relationship type of degree two binary and a relationship type of degree three ternary view more..
Ans: SQL offers the user some control over the order in which tuples in a relation are displayed. view more..
Ans: The rapid advancement of the use of information technology (IT) in industry, government, and academia raises challenging questions and problems regarding the protection and use of personal information. Questions of who has what rights to information about individuals for which purposes become more important as we move toward a world in which it is technically possible to know just about anything about anyone. view more..

Rating - 3/5