|
UNIT – 2 |
|||||||||||||||||||||||||||||||||
|
TOPIC |
|||||||||||||||||||||||||||||||||
|
Unit-02/Lecture-01 |
|||||||||||||||||||||||||||||||||
|
Relational
Model Terminology : u A relation is a table with rows
and columns. –
Only applies to
logical structure of the database,not the physical structure. u Attribute is a named column of a
relation. u Domain is the set of allowable values
for one or more attributes. u Tuple is a row of a relation. u Degree is the number of attributes in
a relation. u Cardinality is the number of tuples in a
relation. u Relational Database is a
collection of normalized relations with distinct relation names. Relational
Model: ·
A database is a collection of 1 or more ‘relations’, where
each relation is a table with rows and columns. ·
This is the primary data model for commercial data
processing applications. ·
The major advantages of the relational model over
the older data models are, 1.
It is simple and elegant. 2.
Simple data representation. 3.
The ease with which even complex queries can be expressed. Introduction: ·
The main construct for representing data in the relational
model is a ‘relation’. ·
A relation consists of 1.
Relation Schema. 2.
Relation Instance. Explanation
is as below. 1.Relation Schema: ·
The relation schema describes the column heads for the
table. ·
The schema specifies the relation’s name, the name of each
field (column, attribute) and the ‘domain’ of each field. ·
A domain is referred to in a relation schema by the domain
name and has a set of associated values. ·
Example: Student
information in a university database to illustrate the parts of a relation
schema. Students
(Sid: string, name: string, login: string, age: integer, gross: real) ·
This says that the field named ‘sid’ has a domain named
‘string’. ·
The set of values associated with domain ‘string’ is the
set of all character strings. 2.Relation Instance: ·
This is a table specifying the information. ·
An instance of a relation is a set of ‘tuples’, also
called ‘records’, in which each tuple has the same number of fields as the
relation schemas. ·
A relation instance can be thought of as a table in which
each tuple is a row and all rows have the same number of fields. ·
The relation instance is also called as ‘relation’. ·
Each relation is defined to be a set of unique tuples or
rows. Example:
Field
names Tuples
(Records, Rows) This example is an instance of the
students relation, which consists 4 tuples and 5 fields. No two rows are
identical. ·
Degree: The
number of fields is called as ‘degree’. This
is also called as ‘arity’. ·
Cardinality: The
cardinality of a relation instance is the number of tuples in it. Example: In
the above example, the degree of the relation is 5 and the cardinality is 4. ·
Relational database: It
is a collection of relations with distinct relation names. ·
Relational database schema: It
is the collection of schemas for the relations in the database. ·
Instance: An
instance of a relational database is a collection of relation instances, one
per relation schema in the database schema. Each
relation instance must satisfy the domain constraints in its schema. References: ·
Date C J, “An Introduction To Database
System”, Pearson Educations ·
2. Korth, Silbertz,Sudarshan, “Fundamental
of Database System”, McGraw Hill ·
Elmasri, Navathe, “Fundamentals Of Database
Systems”, Pearson Educations.
|
Unit-02/Lecture-02 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Database
Relations u Relation
schema –
Named relation
defined by a set of attribute anddomain name pairs. u Relational
database schema – Set of relation schemas, each with a distinct
name.
u Each attribute has a distinct
name. u Values of an attribute are all
from the same domain. u Each tuple is distinct; there
are no duplicate tuples. u Order of attributes has no
significance. u Order of tuples has no
significance, theoretically. Relational Keys u Superkey –
An attribute, or a
set of attributes, that uniquely identifies a tuples within a relation. u Candidate Key – Superkey (K) such
that no proper
subset is a superkey within the relation. –
In each tuple of R,
values of K uniquely identifythat tuple (uniqueness). –
No proper subset of
K has the uniqueness property(irreducibility). u Primary Key –
Candidate key
selected to identify tuples uniquelywithin relation. u Alternate Keys –
Candidate keys that
are not selected to be primarykey. u Foreign Key – Attribute, or set of attributes,
within one relationthat matches candidate key of some (possibly same)
relation. Relational Integrity u Null –
Represents value
for an attribute that is currentlyunknown or not applicable for tuple. – Deals with incomplete or exceptional data.
u Referential Integrity –
If foreign key
exists in a relation, either foreignkey value must match a candidate key
value of some tuple in its home relation or foreign key value must be wholly
null. u Enterprise Constraints –
Additional rules
specified by users or databaseadministrators. Views u Base Relation – Named
relation corresponding to
an entity in conceptual schema, whose tuples are
physically stored in database. u View
–
Dynamic result of
one or more relational operationsoperating on base relations to produce
another relation. u A virtual relation that does not
necessarily actually exist in the database but is produced upon request, at
time of request. u Contents of a view are defined
as a query on one or more base relations. u Views are dynamic, meaning that
changes made to base relations that affect view attributes are immediately
reflected in the view. Purpose of View u Provides powerful and flexible
security mechanism by hiding parts of database from certain users. u Permits users to access data in
a customized way, so that same data can be seen by different users in
different ways, at same time. u Can simplify complex operations
on base relations. Updating Views u All updates to a base relation
should be immediately reflected in all views that reference that base
relation. u If view is updated, underlying
base relation should reflect change. u There are restrictions on types
of modifications that can be made through views: u Updates are allowed if query
involves a single base relation and contains a candidate key of base
relation. u Updates are not allowed
involving multiple base relations. u Updates are not allowed
involving aggregation or grouping operations. u Classes of views are defined as:
– theoretically not updateable; – theoretically updateable; – partially updateable. References: ·
Date C J, “An Introduction To Database System”,
Pearson Educations ·
2. Korth, Silbertz,Sudarshan, “Fundamental
of Database System”, McGraw Hill ·
Elmasri, Navathe, “Fundamentals Of Database
Systems”, Pearson Educations.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Unit-02/Lecture-03 |
||||||||||||||||||||
|
Integrity
constraints over relations ·
An
integrity constraint (IC) is a condition that is specified on a database
schema and restricts the data can be stored in an instance of the database. ·
Various
restrictions on data that can be specified on a relational database schema in
the form of ‘constraints’. ·
DBMS
enforces integrity constraints; it permits only legal instances to be stored
in the database. ·
Integrity
constraints are specified and enforced at different times as below. 1.When
the DBA or end user defines a database schema, he or she specifies
the ICs that must hold on any instance of this database. 2.When
a data base application is run, DBMS checks for violations and
disallows changes to the data that violate the specified ICs. ·
Legal
Instance: If
the database instance satisfies all the integrity constraints specified on
the database schema. ·
The
constraints can be classified into 4 types as below. 1.
Domain Constraints. 2.
Key Constraints. 3.
Entity Integrity Constraints. 4.
Referential Integrity Constraints. Explanation is
as below. 1.Domain
Constraints ·
Domain
constraints are the most elementary form of integrity constraints. They are
tested easily by the system whenever a new data item is entered into the
database. ·
Domain
constraints specify the set of possible values that may be associated with an
attribute. Such constraints may also prohibit the use of null values for
particular attributes. ·
The
data types associated with domains typically include standard numeric data
types for integers ·
A
relation schema specifies the domain of each field or column in the relation
instance. ·
These
domain constraints in the schema specify an important condition that each
instance of the relation to satisfy: The values that appear in a column must
be drawn from the domain associated with that column. ·
Thus
the domain of a field is essentially the type of that field. 2.Key
Constraints: ·
A
key constraint is a statement that a certain minimal subset of the fields of
a relation is a unique identifier for a tuple. ·
Example: The ‘students’
relation and the constraint that no 2 students have tha same student id
(sid). ·
These
can be classified into 3 types as below. a.
Candidate Key or Key. b.
Super Key. c.
Primary Key. Explanation is as below. a.
Candidate Key: ·
A
set of fields that uniquely identifies a tuple according to a key constraint
is called as a ‘Candidate Key’ for the relation. ·
From
the definition of candidate key, we have, 1.Two
distinct tuples in a legal instance cannot have identical values in
all the fields of a key.i.e, in any legal instance, the values in the key fields
uniquely identify a tuple in the instance. i.e,the
values in the key fields uniquely identify a tuple in the instance. 2. No subset of the set
of fields in key is a unique identifier for a tuple, i.e., the set of fields {sid,
name} is not a key for Students. ·
A
relation schema may have more than one key. ·
Example:
In the above Students relation, the ‘sid’ field is a
candidate key. {sid}.
b.
Super Key: ·
The
set of 1 or more attributes that allows us to identify uniquely an entity in
the entity set. ·
A super
key specifies a uniqueness constraint that no 2 distinct tuples can have the
same value. ·
Every
relation has at least 1 default super key as the set of all attributes. ·
Example: (Relation) Name (Fields) Login Age Gross One of the super key = {Sid, Name, Login,
Age, Gross}
c.
Primary Key: ·
This
is also a candidate key, whose values are used to identify tuples in the
relation. ·
It
is common to designate one of the candidate keys as a primary key of the
relation. ·
The
attributes that form the primary key of a relation schema are underlined. ·
It
is used to denote a candidate key that is chosen by the database designer as
the principal means of identifying entities with an entity
set. ·
Example: ‘Sid’ of Students relation. d.
Specifying Key Constraints in SQL-92: ·
In
SQL, we are declaring the set of fields of a table consisting a key by using ‘UNIQUE’ constraint. ·
This ‘UNIQUE’ constraint specifies that 2
distinct tuples cannot have identical Values. ·
Candidate
keys can be declared as a ‘primary key’ using the constraint ‘PRIMARY KEY’. ·
We
can name a constraint by using the syntax as below. CONSTRAINT
constraint_name KEY_NOTATION (key_names); ·
If
the constraint is violated, then the constraint_name is returned and it can
be used to
identify the error. ·
Example: Express ‘sid’ as a primary key and the
combination {name, age} as a key.
CREATE
TABLE Students (sid CHAR (20), name
CHAR (30), login CHAR(20),
age INTEGER, gross REAL, UNIQUE (name, age), ` CONSTRAINT sid1 PRIMARY
KEY (sid)); 3.Entity
Integrity Constraints ·
This
states that no primary key value can be null. ·
The
primary key value is used to identify individual tuples in a relation. ·
Having
null values for the primary key implies that we cannot identify some tuples. ·
NOTE:
Key Constraints, Entity Integrity Constraints are specified on individual
relations. ·
PRIMARY
KEYS comes under this. 4.Referential
Integrity Constraints ·
The
Referential Integrity Constraint is specified between 2 relations and is used
to maintain the consistency
among tuples of the 2 relations. ·
Informally,
the referential integrity constraint states that ‘a tuple in 1 relation that refers to another
relation must refer to an existing tuple in that relation. ·
We
can diagrammatically display the referential integrity constraints by drawing
a directed arc from each foreign key to the relation it
references. The arrowhead may point to the primary key
of the referenced relation. References: ·
Date C J, “An Introduction To Database
System”, Pearson Educations ·
2. Korth, Silbertz,Sudarshan, “Fundamental
of Database System”, McGraw Hill ·
Elmasri, Navathe, “Fundamentals Of Database
Systems”, Pearson Educations.
|
|
UNIT 2/LECTURE 4 |
||||||||
|
Relational
Query Language:SQL Objectives of SQL u Ideally, database language
should allow user to: – create the database and relation structures; –
perform insertion,
modification, deletion of datafrom relations; – perform simple and complex queries. u Must perform these tasks with
minimal user effort and command structure/syntax must be easy to learn. u It must be portable. u SQL is a transform-oriented
language with 2 major components: – A DDL for defining database structure. – A DML for retrieving and updating data. u Until SQL3, SQL did not contain
flow of control commands. These had to be implemented using a programming or
job-control language, or interactively by the decisions of user. u SQL is relatively easy to learn:
–
it is
non-procedural - you specifywhatinformationyou require, rather than how
to get it; – it is essentially free-format. u Consists of standard English
words. u Can be used by range of users
including DBAs, management, application developers, and other types of end
users. u An ISO standard now exists for
SQL, making it both the formal and de facto standard language for
relational databases. History of SQL u In 1974, D. Chamberlin (IBM San
Jose Laboratory) defined language called ‘Structured English Query Language’
(SEQUEL). u A revised version, SEQUEL/2, was
defined in 1976 but name was subsequently changed to SQL for legal reasons. u Still pronounced ‘see-quel’, though official Pronunciation
is ‘S-Q-L’. u IBM subsequently produced a
prototype DBMS called SystemR, based on SEQUEL/2. u Roots of SQL, however, are in
SQUARE (Specifying Queries as Relational Expressions), which predates System
R project. u In late 70s, ORACLE appeared and
was probably first commercial RDBMS based on SQL. u In 1987, ANSI and ISO published
an initial standard for SQL. u In 1989, ISO published an
addendum that defined an ‘Integrity Enhancement Feature’. u In 1992, first major revision to
ISO standard occurred, referred to as SQL2 or SQL/92. u In 1999, SQL3 was released with
support for object-oriented data management. Importance of SQL u SQL has become part of
application architectures such as IBM’s Systems Application Architecture. u It is strategic choice of many
large and influential organizations (e.g. X/OPEN). u SQL is Federal Information
Processing Standard (FIPS) to which conformance is required for all sales of
databases to American Government. u SQL is used in other standards
and even influences development of other standards as a definitional tool.
Examples include: –
ISO’s Information
Resource Directory System (IRDS)Standard – Remote Data Access (RDA) Standard. Writing SQL Commands u SQL statement consists of reserved
words and user-definedwords. –
Reserved words are
a fixed part of SQL and must be speltexactly as required and cannot be split
across lines. – User-defined
words are made up by user and represent names of various database objects
such as relations, columns, views. u Most components of an SQL
statement are case insensitive, except for literal character data. u More readable with indentation
and lineation: – Each clause should begin on a new line. –
Start of a clause
should line up with start of otherclauses. –
If clause has
several parts, should each appear on aseparate line and be indented under
start of clause. u Use extended form of BNF
notation: - Upper-case letters represent
reserved words. - Lower-case letters represent
user-defined words. - | indicates a choice
among alternatives. - Curly braces indicate a required
element. - Square brackets indicate an optional
element. - … indicatesoptional
repetition (0 or more). Literals u Literals are constants used in SQL statements. u All non-numeric literals must be enclosed in single quotes
(e.g. ‘London’). u All numeric literals must not be
enclosed in quotes (e.g. 650.00). References: ·
Date C J, “An Introduction To Database
System”, Pearson Educations ·
2. Korth, Silbertz,Sudarshan, “Fundamental
of Database System”, McGraw Hill ·
Elmasri, Navathe, “Fundamentals Of Database
Systems”, Pearson Educations.
|
|
UNIT 2/LECTURE 5 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
DDL and DML: Data Definition u SQL DDL allows database objects
such as schemas, domains, tables, views, and indexes to be created and
destroyed. u Main SQL DDL statements are:
u Relations and other database
objects exist in an environment. u Each environment contains one or
more catalogs, and each catalog consists of set of schemas. u Schema is named collection of
related database objects. u Objects in a schema can be
tables, views, domains, assertions, collations, translations, and character
sets. All have same owner. CREATE TABLE CREATE TABLE TableName [CHECK
searchCondition] [,...]} [PRIMARY KEY (listOfColumns),] {[UNIQUE
(listOfColumns),] […,]} {[FOREIGN KEY (listOfFKColumns) REFERENCES ParentTableName
[(listOfCKColumns)], [ON UPDATE referentialAction] [ON DELETE referentialAction ]]
[,…]} {[CHECK (searchCondition)] [,…] }) u Creates a table with one or more
columns of the specified dataType.
u Can specify a DEFAULT value for
the column. u Primary keys should always be
specified as NOT NULL. u FOREIGN KEY clause specifies FK
along with the referential action ALTER
TABLE u Add a new column to a table. u Drop a column from a table. u Add a new table constraint. u Drop a table constraint. u Set a default for a column. u Drop a default for a column. Change Staff table by removing
default of ‘Assistant’ for position column and setting default for sex column
to female (‘F’). ALTER
TABLE Staff ALTER position DROP DEFAULT; ALTER
TABLE Staff ALTER sex SET DEFAULT ‘F’; Remove constraint from
PropertyForRent that staff not allowed to handle more than 100 properties at
time. Add new column to Client table. ALTER
TABLE PropertyForRent DROP
CONSTRAINT StaffNotHandlingTooMuch; ALTER
TABLE Client ADD
prefNoRoomsPRooms; DROP
TABLE DROP
TABLE TableName [RESTRICT | CASCADE] u Removes named table and all rows
within it. u With RESTRICT, if any other
objects depend for their existence on continued existence of this table, SQL
does not allow request. u With CASCADE, SQL drops all
dependent objects (and objects dependent on these objects). View Dynamic result of one or more relational
operations operating on base relations to produce another relation. u Virtual relation that does not
necessarily actually exist in the database but is produced upon request, at
time of request. u Contents of a view are defined
as a query on one or more base relations. u With view
resolution, any operations on
view
u With view materialization, the
view is stored as a temporary table, which is maintained as the underlying
base tables are updated. SQL - CREATE VIEW CREATE VIEW ViewName [ (newColumnName
[,...]) ] AS subselect [WITH [CASCADED | LOCAL] CHECK
OPTION] u Can assign a name to each column
in view. u If list of column names is
specified, it must have same number of items as number of columns produced by
subselect. u If omitted, each column takes name of corresponding column in subselect. u List must be specified if there
is any ambiguity in a column name. u The subselectis known as
the defining query. u WITH CHECK OPTION ensures that
if a row fails to satisfy WHERE clause of defining query, it is not added to
underlying base table. u Need SELECT privilege on all
tables referenced in subselect and USAGE privilege on any domains used in
referenced columns. SQL - DROP VIEW DROP VIEW ViewName [RESTRICT |
CASCADE] u For example: DROP VIEW Manager3Staff; u With CASCADE, all related
dependent objects are deleted; i.e. any views defined on view being dropped. u With RESTRICT (default), if any
other objects depend for their existence on continued existence of view being
dropped, command is rejected Restrictions on Views SQL imposes several restrictions
on creation and use of views.
FROM
StaffPropCnt; u Similarly, following query would also fail: SELECT
* FROM StaffPropCnt WHERE cnt> 2; (b) Grouped view may never be joined with a
base table or a view. u For example, StaffPropCnt view
is a grouped view, so any attempt to join this view with another table or
view fails. Advantages of Views u Data independence u Currency u Improved security u Reduced complexity u Convenience u Customization u Data integrity References: ·
Date C J, “An Introduction To Database
System”, Pearson Educations ·
2. Korth, Silbertz,Sudarshan, “Fundamental
of Database System”, McGraw Hill ·
Elmasri, Navathe, “Fundamentals Of Database
Systems”, Pearson Educations.
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Unit-02/Lecture-06 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
DML Statements:
u Order of the clauses cannot be
changed. u Only SELECT and FROM are
mandatory. List
full details of all staff. SELECT staffNo, fName, lName, address, FROM
Staff; u Can use * as an abbreviation for
‘all columns’: SELECT * FROM
Staff; Specific Columns, All Rows Produce a list of salaries for all staff,
showing only staff number, first and last names, and salary. SELECT
staffNo, fName, lName, salary FROM
Staff; List the property numbers of all properties
that have been viewed. SELECT
propertyNo FROM Viewing; Use of DISTINCT Use
DISTINCT to eliminate duplicates:
SELECT
DISTINCT propertyNo FROM
Viewing Calculated Fields Produce a list of monthly salaries for all
staff, showing staff number, first and last names, and salary details. SELECT
staffNo, fName, lName, salary/12 FROM
Staff; To
name column, use AS clause: SELECT
staffNo, fName, lName, salary/12 AS
monthlySalary FROM
Staff; Comparison Search Condition List all staff with a salary
greater than 10,000. SELECT staffNo, fName, lName, position,
salary FROM Staff WHERE
salary > 10000; Compound Comparison Search
Condition List addresses of all branch
offices in London or Glasgow. SELECT * FROM
Branch WHERE
city = ‘London’ OR city = ‘Glasgow’; Range Search Condition List
all staff with a salary between 20,000 and 30,000. SELECT staffNo, fName, lName, position,
salary FROM Staff WHERE
salary BETWEEN 20000 AND 30000; u BETWEEN test includes the endpoints of range. u Also a negated version NOT BETWEEN.
FROM
STAFF; WHERE salary>=20000 AND
salary <= 30000; u Useful, though, for a range of values. Set Membership List all managers and
supervisors. SELECT staffNo, fName, lName,
position FROM Staff WHERE position IN (‘Manager’, ‘Supervisor’); u There is a negated version (NOT
IN). u IN does not add much to SQL’s
expressive power. u Could have expressed this as: SELECT staffNo, fName, lName,
position FROM Staff WHERE position=‘Manager’ OR
position=‘Supervisor’; u IN is more efficient when set
contains many values Pattern Matching Find all owners with the string
‘Glasgow’ in their address. SELECT
clientNo, fName, lName, address, telNo FROM
PrivateOwner WHERE
address LIKE ‘%Glasgow%’; u SQL has two special pattern
matching symbols: – %: sequence of zero or more characters; – _ (underscore): any single character. u LIKE ‘%Glasgow%’ means a
sequence of characters of any length containing ‘Glasgow’. NULL Search Condition List details of all viewings on property
PG4 where a comment has not been supplied. u There
are 2 viewings
for property PG4,
one with and
one without
a comment. Aggregate Functions u ISO standard defines five aggregate functions: ·
COUNT
returns number of values in specified column. ·
SUM
returns sum of values in specified column. ·
AVG
returns average of values in specified column. ·
MIN
returns smallest value in specified column. ·
MAX
returns largest value in specified column. u Each operates on a single column
of a table and returns a single value. u COUNT, MIN, and MAX apply to
numeric and non-numeric fields, but SUM and AVG may be used on numeric fields
only. u Apart from COUNT (*), each
function eliminates nulls first and operates only on remaining non-null
values.
u Can use DISTINCT before column
name to eliminate duplicates. u DISTINCT has no effect with
MIN/MAX, but may have with SUM/AVG.
is no GROUP BY clause, SELECT
list cannot reference a column out with an aggregate function. For example,
the following is illegal: SELECT
staffNo, COUNT(salary) FROM
Staff; Use of COUNT(DISTINCT) How many different properties
viewed in May ‘01? SELECT
COUNT(DISTINCT propertyNo) AS count FROM
Viewing WHERE
viewDate BETWEEN ‘1-May-01’ AND
‘31-May-01’; Find number of Managers and sum
of their salaries. SELECT COUNT(staffNo) AS count, SUM(salary) AS sum FROM
Staff WHERE
position = ‘Manager’; Use of MIN, MAX, AVG Find minimum, maximum, and
average staff salary. SELECT MIN(salary) AS min, MAX(salary) AS max, AVG(salary)
AS avg FROM
Staff; References: ·
Date C J, “An Introduction To Database
System”, Pearson Educations ·
2. Korth, Silbertz,Sudarshan, “Fundamental
of Database System”, McGraw Hill ·
Elmasri, Navathe, “Fundamentals Of Database
Systems”, Pearson Educations.
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
UNIT 2/LECTURE 7 |
||||||||||||||||||||
|
Query
Languages: Relational Algebra : Introduction u Relational algebra and
relational calculus are formal languages associated with the relational
model. u Informally, relational algebra
is a (high-level) procedural language and relational calculus a
non-procedural language. u However, formally both are
equivalent to one another. u A language that produces a
relation that can be derived using relational calculus is relationally
complete. Relational Algebra u Relational algebra operations
work on one or more relations to define another relation without changing the
original relations. u Both operands and results are
relations, so output from one operation can become input to another
operation. u Allows expressions to be nested,
just as in arithmetic. This property is called closure. u Five basic
operations in relational
algebra: Selection, Projection, Cartesian product,
Union, and Set Difference. u These perform most of the data
retrieval operations needed. Also have Join,
Intersection, and Division operations, which can be expressed in terms of 5
basic operations. Relational Algebra Operations Selection (or Restriction) u s predicate (R) –
Works on a single
relation R and defines a relationthat contains only those tuples (rows) of R
that satisfy the specified condition (predicate). – Example - Selection (or
Restriction) u List all staff with a salary
greater than £10,000. s salary > 10000 (Staff) Projection u P col1, . . . , coln(R) –
Works on a single
relation R and defines a relationthat contains a vertical subset of R,
extracting the values of specified attributes and eliminating duplicates. Example
- Projection u Produce a list of salaries for
all staff, showing only staffNo, fName, lName, and salary details. PstaffNo, fName,
lName, salary(Staff) Union u R È S –
Union of two
relations R and S defines a relationthat contains all the tuples of R, or S,
or both R and S, duplicate tuples being eliminated. – R and S must be union-compatible. u If R and S have I and J
tuples, respectively, union is obtained by concatenating them into one
relation with a maximum of (I + J) tuples. Example
- Union u List all cities where there is
either a branch office or a property for rent. city(Branch) È Pcity(PropertyForRent) Set Difference u R – S –
Defines a relation
consisting of the tuples that arein relation R, but not in S. – R and S must be union-compatible. Example - Set Difference u List all cities where there is a
branch office but no properties for rent. Pcity(Branch) – Pcity(PropertyForRent) Intersection u R Ç S –
Defines a relation
consisting of the set of alltuples that are in both R and S. – R and S must be union-compatible. u Expressed using basic
operations: R Ç S = R –
(R – S) Example – Intersection u List all cities where there is
both a branch office and at least one property for rent. Pcity(Branch)
Ç
Pcity(PropertyForRent) Cartesian product u R X S – Defines
a relation that is the concatenation of every tuple of relation R with every
tuple of relation S. Example
- Cartesian product u List the names and comments of
all clients who have viewed a property for rent. (PclientNo, fName,
lName(Client))
X (PclientNo, propertyNo,
comment (Viewing)) References: ·
Date C J, “An Introduction To Database
System”, Pearson Educations ·
2. Korth, Silbertz,Sudarshan, “Fundamental
of Database System”, McGraw Hill ·
Elmasri, Navathe, “Fundamentals Of Database
Systems”, Pearson Educations.
|
|
Unit-02/Lecture-08 |
|||||||||||||||||||||||||||||||||||||||||||||
|
Join
Operations u Join is a derivative of Cartesian product. Example
- Cartesian product and Selection u Use selection operation to
extract those tuples where Client.clientNo = Viewing.clientNo. s Client.clientNo
= Viewing.clientNo((PclientNo, fName, lName(Client))C(PclientNo, propertyNo,
comment(Viewing))) u Cartesian product and Selection
can be reduced to a single operation called a Join. u Equivalent to performing a Selection, using join predicate as
selection formula, over Cartesian product of the two operand relations. u One of the most difficult
operations to implement efficiently in an RDBMS and one reason why RDBMSs
have intrinsic performance problems. u Various forms of join operation u Theta join u Equijoin (a particular type of
Theta join) u Natural join u Outer join u Semijoin Theta
join (q-join) u R FS – Defines a relation that contains
tuples satisfyingthe predicate F from the Cartesian product of R and S.
Cartesian
product operations. R FS = sF(R C
S) u Degree of a Theta join is sum of
degrees of the operand relations R and S. If predicate F contains only
equality (=), the term Equijoin is used. Natural
join u R S –
An Equijoin of the
two relations R and S over allcommon attributes x. One occurrence of
each common attribute is eliminated from the result. Example
- Natural join u List the names and comments of
all clients who have viewed a property for rent. (PclientNo, fName, lName(Client)) (PclientNo, propertyNo,
comment(Viewing)) Outer
join u To display rows in the result
that do not have matching values in the join column, use Outer join.
Example
- Left Outer join u Produce a status report on
property viewings. P propertyNo,
street, city(PropertyForRent) Division u R ¸ S –
Defines a relation
over the attributes C that consistsof set of tuples from R that match
combination of every tuple in S. u Expressed using basic
operations: T1 ¬ PC(R) T2 ¬ PC((S X T1) – R) T ¬ T1 – T2 Example
- Division u Identify all clients who have
viewed all properties with three rooms. (PclientNo, propertyNo(Viewing))¸(PpropertyNo(s rooms
= 3 (PropertyForRent))) References: ·
Date C J, “An Introduction To Database
System”, Pearson Educations ·
2. Korth, Silbertz,Sudarshan, “Fundamental
of Database System”, McGraw Hill ·
Elmasri, Navathe, “Fundamentals Of Database
Systems”, Pearson Educations.
|
|||||||||||||||||||||||||||||||||||||||||||||
|
UNIT 2/LECTURE 9 |
||||||||||||
|
Relational Calculus u Relational calculus query
specifies what is to be retrieved rather than how to retrieve
it. – No description of how to evaluate a query.
u When we substitute values for
the arguments, function yields an expression, called a proposition,
which can be either true or false. u If predicate contains a variable
(e.g. ‘x is a member of staff’), there must be a range for x. u When we substitute some values
of this range for x, proposition may be true; for other values, it may
be false. u When applied to databases,
relational calculus has forms: tuple and domain. Tuple Relational Calculus u Interested in finding tuples for
which a predicate is true. Based on use of tuple variables. u Tuple variable is a variable
that ‘ranges over’ a named relation: i.e., variable whose only permitted
values are tuples of the relation. u Specify range of a tuple
variable S as the Staff relation as: Staff(S) u To find set of all tuples S such
that P(S) is true: {S | P(S)} Tuple Relational Calculus -
Example u To find details of all staff
earning more than £10,000: {S | Staff(S) ÙS.salary> 10000} u To find a particular attribute,
such as salary, write: {S.salary | Staff(S) ÙS.salary> 10000} Query-by-Example (QBE) u Visual approach for accessing
information in a database through use of query templates. u Example values are entered into template to represent what
access to database is to achieve, such as the answer to a query. u Originally developed by IBM in
1970s and has proved so popular that QBE (or similar) is now provided by most
DBMSs. u When user constructs a QBE - in
background, DBMS creates an equivalent SQL statement. References: ·
Date C J, “An Introduction To Database
System”, Pearson Educations ·
2. Korth, Silbertz,Sudarshan, “Fundamental
of Database System”, McGraw Hill ·
Elmasri, Navathe, “Fundamentals Of Database
Systems”, Pearson Educations.
|