customer-name
Download
Report
Transcript customer-name
Banking Example
branch (branch-name, branch-city, assets)
customer (customer-name, customer-street, customer-city)
account (account-number, branch-name, balance)
loan (loan-number, branch-name, amount)
depositor (customer-name, account-number)
borrower (customer-name, loan-number)
Database System Concepts
3.1
©Silberschatz, Korth and Sudarshan
Example Queries
Find the loan-number, for loans of over $1200
{t.loan-number | t loan t [amount] 1200}
Find the loan number for each loan of an amount greater than $1200
{t.loan_number | t loan s loan (t[loan-number] = s[loannumber] s [amount] 1200)}
Notice that a relation on schema [loan-number] is implicitly defined by the
query
Database System Concepts
3.2
©Silberschatz, Korth and Sudarshan
Example Queries
Find the names of all customers having a loan, or an account
{t.customer_name | t customer s borrower( t[customer-name] =
s[customer-name])
u depositor( t[customer-name] = u[customer-name])
Find the names of all customers who have a loan and an account at the
bank
{ t.customer_name | t customer s borrower( t[customer-name]
= s[customer-name])
u depositor( t[customer-name] = u[customer-name])
Database System Concepts
3.3
©Silberschatz, Korth and Sudarshan
Example Queries
Find the names of all customers having a loan at the Perryridge
branch
{t.customer_name | t customer s borrower(t[customer-name] = s[customer-name]
u loan(u[branch-name] = “Perryridge” u[loan-number] = s[loan-number]))}
Find the names of all customers who have a loan at the Perryridge
branch, but no account at any branch of the bank
{ t.customer_name | t customer s borrower( t[customer-name] =
s[customer-name] u loan(u[branch-name] = “Perryridge” u[loan-number]
= s[loan-number]))
not v depositor (v[customer-name] = t[customer_name]) }
Database System Concepts
3.4
©Silberschatz, Korth and Sudarshan
Example Queries
Find the names of all customers having a loan from the
Perryridge branch, and the cities they live in
{ t.customer_name,t.customer_city |
t customer s loan(s[branch-name] = “Perryridge”
u borrower (u[loan-number] = s[loan-number]
t [customer-name] = u[customer-name])
v customer (u[customer-name] = v[customer-name]
t[customer-city] = v[customer-city])))}
Note, v is redundant!
Database System Concepts
3.5
©Silberschatz, Korth and Sudarshan
Example Queries
Find the names of all customers who have an account at all
branches located in Brooklyn:
{t.customer_name | t customer
b branch(b[branch-city] = “Brooklyn”
u account ( b[branch-name] = u[branch-name]
s depositor ( t[customer-name] = s[customer-name]
s[account-number] = u[account-number] )) )}
Also
{t.customer_name | t customer
b branch(b[branch-city] != “Brooklyn” V
u account ( b[branch-name] = u[branch-name]
s depositor ( t[customer-name] = s[customer-name]
s[account-number] = u[account-number] )) )}
Database System Concepts
3.6
©Silberschatz, Korth and Sudarshan
Example Queries
Find the names of all customers who have an account at all
branches located in Brooklyn:
{t.customer_name | t customer
not ( b) b branch(b[branch-city] = “Brooklyn”
not ( u account ( b[branch-name] = u[branch-name]
s depositor ( t[customer-name] = s[customer-name]
s[account-number] = u[account-number] )) ) )}
For each output customer, there does not exist a branch in Brooklyn such that
There does not exist an account for that customer in that branch
Database System Concepts
3.7
©Silberschatz, Korth and Sudarshan
Transforming the Universal and Existential Quantifiers
( x) (P(x)) not ( x)(not (P(x)))
( x) (P(x)) not ( x)(not (P(x)))
( x) (P(x) and Q(x)) not ( x) (not (P(x)) or not (Q(x)))
( x) (P(x) or Q(x)) not ( x) (not (P(x)) and not (Q(x)))
( x) (P(x)) or Q(x)) not ( x) (not (P(x)) and not (Q(x)))
( x) (P(x) and Q(x)) not ( x) (not (P(x)) or not (Q(x)))
A => B not(A) or B
Notice also that the following is true, where the => symbol stands for implies:
( x) (P(x)) => ( x) (P(x))
Not ( x) (P(x)) => not ( x) (P(x))
Database System Concepts
3.8
©Silberschatz, Korth and Sudarshan
Safety of Expressions
It is possible to write tuple calculus expressions that generate
infinite relations.
For example, {t | t r} results in an infinite relation if the
domain of any attribute of relation r is finite
To guard against the problem, we restrict the set of allowable
expressions to safe expressions.
An expression {t | P(t)} in the tuple relational calculus is safe if all
t values which cause P to be true, are taken from dom (p), where
dom (P) is the cartezian product of the domains of all relations
appearing in P.
E.g. { t | t[A]=5 true } is not safe --- it defines an infinite set with
attribute values that do not appear in any relation or tuples or
constants in P.
Database System Concepts
3.9
©Silberschatz, Korth and Sudarshan
Safety of Expressions
{ x1, x2, …, xn | P(x1, x2, …, xn)}
is safe if all of the following hold:
1.All values that appear in tuples of the expression are values from dom(P)
• The values appear either in P or in a tuple of a relation mentioned in P.
2.For every “there exists” subformula of the form x (P1(x))
The subformula is true if and only if there is a value of x in dom(P1) such that P1(x)
is true.
3. For every “for all” subformula of the form x (P1 (x)), the sub formula is
true if and only if P1(x) is true for all values x from dom (P1).
Database System Concepts
3.10
©Silberschatz, Korth and Sudarshan
Database System Concepts
3.11
©Silberschatz, Korth and Sudarshan
TRC – Additional Examples
QUERY 1
Retrieve the name and address of all employees who work in the ‘Research’ department.
Q1 : {t.FNAME, t.LNAME, t.ADDRESS |
EMPLOYEE(t) and (
d)
(DEPARTMENT (d) and d.DNAME = ‘Research’ and d.DNUMBER=t.DNO) }
QUERY 2
for every project located in ‘Stafford’, list the project number, the controlling department
number, and the department manager’s last name, birthday, and address.
Q2 : {p.PNUMBER, p.DNUM, m.LNAME, m.BDATE, m.ADDRESS | PROJECT(p) and
EMPLOYEE (m) and p.PLOCATION=‘Stafford’ and
((
d)(DEPARTMENT(d) and p.DNUM=d.DNUMBER an d.MGRSSN=m.SSN)) }
QUERY 3
find the name of each employee who works on a project controlled by department number 5.
Q3 : {e.LNAME, e.FNAME | EMPLOYEE(e) and ( (
x)
(
w)
(PROJECT(x) and WORKS_ON(w) and x.DNUM=5 and w.ESSN=e.SSN and
x.PNUMBER=w.PNO) ) }
Database System Concepts
3.12
©Silberschatz, Korth and Sudarshan
TRC – Additional Examples
QUERY 3
Find the name of employees who work on all the projects controlled by
department number 5. One way of specifying this query is by using the universal
quantifier as shown.
Q3: {e.LNAME, e.FNAME | EMPLOYEE(e) and (( x)(not(PROJECT(x))or
not(x.DNUM=5) Or( ( w)(WORKS_ON(w) and w.ESSN=e.SSN and
x.PNUMBER=w.PNO) ) ) ) }
QUERY 4
Make a list of project numbers for projects that involve an employee whose last
name is ‘Smith’, either as a worker or as manager of the controlling department
for the project.
Q4 : {p.PNUMBER | PROJECT(p) and
( ( e)( w)(EMPLOYEE(e) and WORKS_ON(w) and
w.PNO=p.PNUMBER and e.LNAME=‘Smith’ and e.SSN=w.ESSN) )
Or( ( m)( d)(EMPLOYEE(m) and DEPARTMENT(d) and
p.DNUM=d.DNUMBER and d.MGRSSN=m.SSN and m.LNAME=‘Smith’) ) ) }
Database System Concepts
3.13
©Silberschatz, Korth and Sudarshan
TRC – Additional Examples cont.
QUERY 6
find the names of employees who have no dependents.
Q6 : {e.FNAME, e.LNAME
EMPLOYEE(e) and (not (
d)(DEPENDENT(d) and
e.SSN=d.ESSN))}
Using the general transformation rule, we we can rephrase Q6 as follows:
Q6A : {e.FNAME, e.LNAME
EMPLOYEE(e) and (( d)(not (DEPENDENT(d)) or not
(e.SSN=d.ESSN)))}
QUERY 7
List the names of managers who have at least one dependent.
Q7 : {e.FNAME, e.LNAME
EMPLOYEE(e)and (( d)(
p)
(DEPARTMENT(d) and DEPENDENT(p) and e.SSN=d.MGRSSN and
p.ESSN=e.SSN))}
Database System Concepts
3.14
©Silberschatz, Korth and Sudarshan
Domain Relational Calculus
A nonprocedural query language equivalent in power to the tuple
relational calculus
Each query is an expression of the form:
{ x1, x2, …, xn | P(x1, x2, …, xn)}
• x1, x2, …, xn represent domain variables
• P represents a formula similar to that of the predicate calculus
• NOTE: The attribute Xi represents is by its location!
Database System Concepts
3.15
©Silberschatz, Korth and Sudarshan
Example Queries
Find the loan-number, branch-name, and amount for loans of over
$1200
{ l, b, a | l, b, a loan a > 1200}
Find the names of all customers who have a loan of over $1200
{ c | l, b, a ( c, l borrower l, b, a loan a > 1200)}
Find the names of all customers who have a loan from the Perryridge
branch and the loan amount:
{ c, a | l ( c, l borrower b( l, b, a loan
b = “Perryridge”))}
or { c, a | l ( c, l borrower l, “Perryridge”, a loan)}
Database System Concepts
3.16
©Silberschatz, Korth and Sudarshan
Example Queries
Find the names of all customers having a loan, an account, or
both at the Perryridge branch:
{ c | l ({ c, l borrower
b,a( l, b, a loan b = “Perryridge”))
a( c, a depositor
b,n( a, b, n account b = “Perryridge”))}
Find the names of all customers who have an account at all
branches located in Brooklyn:
{ c | s, n ( c, s, n customer)
x,y,z( x, y, z branch y = “Brooklyn”)
a,b( a,x,b account c,a depositor)}
Database System Concepts
3.17
©Silberschatz, Korth and Sudarshan
QUERY 1
DRC Examples
Retrieve the name and address of all employees who work for the ‘Research’
department.
Q1 : {qsv l (
z) ( l) ( m) (EMPLOYEE(qrstuvwxyz) and
DEPARTMENT(lmno) and l=‘Research’ and m=z)} note implicit existenial notation
QUERY 2
For every project located in ‘stafford’, list the project number, the controlling
department number, and the department manager’s last name, birthdate and
address.
Q2 : {iksuv l ( j) ( m) ( n) ( t)(PROJECT(hijk)and EMPLOYEE(qrstuvwxyz)
and DEPARTMENT(lmno) and k=m and n=t and j=‘stanfford’)}
QUERY 6
find the names of employees who have no dependents.
Q6 : {qs l ( t) (EMPLOYEE(qrstuvwxyz) and (( l)(not(DEPENDENT(lmnop))
or not (t=l))))}
Query 6 can be restated using universal quantifiers instead of the existensial
quantifiers, as shown in Q6A:
Q6A : {qs l ( t) (EMPLOYEE(qrstuvwxyz) and (( l)
(not(DEPENDENT(lmnop))or not (t=l))))}
Database System Concepts
3.18
©Silberschatz, Korth and Sudarshan
Four ways of specifying the query Q0 in
QBE
Database System Concepts
3.19
©Silberschatz, Korth and Sudarshan
The notion of Relational Complete
Theorem:
The Relational algebra (without functions), the Tuple relational
calculus, and the Domain relational calculus are equivalent
Database System Concepts
3.20
©Silberschatz, Korth and Sudarshan
Views
In some cases, it is not desirable for all users to see the entire
logical model (i.e., all the actual relations stored in the database.)
Consider a person who needs to know a customer’s loan number
but has no need to see the loan amount. This person should see
a relation described, in the relational algebra, by
customer-name, loan-number (borrower
loan)
Any relation that is not of the conceptual model but is made
visible to a user as a “virtual relation” is called a view.
Database System Concepts
3.21
©Silberschatz, Korth and Sudarshan
View Definition
A view is defined using the create view statement which has the
form
create view v as <query expression >
where <query expression> is any legal relational algebra query
expression. The view name is represented by v.
Once a view is defined, the view name can be used to refer to
the virtual relation that the view generates.
View definition is not the same as creating a new relation by
evaluating the query expression
• Rather, a view definition causes the saving of an expression; the
expression is substituted into queries using the view.
Database System Concepts
3.22
©Silberschatz, Korth and Sudarshan
View Examples
Consider the view (named all-customer) consisting of branches
and their customers.
create view all-customer as
branch-name, customer-name (depositor
account)
branch-name, customer-name (borrower
loan)
We can find all customers of the Perryridge branch by writing:
branch-name
(branch-name = “Perryridge” (all-customer))
Database System Concepts
3.23
©Silberschatz, Korth and Sudarshan
Updates Through View
Database modifications expressed as views must be translated
to modifications of the actual relations in the database.
Consider the person who needs to see all loan data in the loan
relation except amount. The view given to the person, branchloan, is defined as:
create view branch-loan as
branch-name, loan-number (loan)
Since we allow a view name to appear wherever a relation name
is allowed, the person may write:
branch-loan branch-loan {(“Perryridge”, L-37)}
Database System Concepts
3.24
©Silberschatz, Korth and Sudarshan
Updates Through Views (Cont.)
The previous insertion must be represented by an insertion into the
actual relation loan from which the view branch-loan is constructed.
An insertion into loan requires a value for amount. The insertion
can be dealt with by either.
• rejecting the insertion and returning an error message to the user.
• inserting a tuple (“L-37”, “Perryridge”, null) into the loan relation
Some updates through views are impossible to translate into
database relation updates
• create view v as branch-name = “Perryridge” (account))
v v (L-99, Downtown, 23)
Others cannot be translated uniquely
• all-customer all-customer {(“Perryridge”, “John”)}
Have to choose loan or account, and
create a new loan/account number!
Database System Concepts
3.25
©Silberschatz, Korth and Sudarshan
Data Dictionary Storage
Data dictionary (also called system catalog) stores metadata:
that is, data about data, such as
Information about relations
names of relations
names and types of attributes of each relation
names and definitions of views
integrity constraints
User and accounting information, including passwords
Statistical and descriptive data
number of tuples in each relation
Physical file organization information
How relation is stored (sequential/hash/…)
Physical location of relation
operating system file name or
disk addresses of blocks containing records of the relation
Information about indices (Chapter 12)
Database System Concepts
3.26
©Silberschatz, Korth and Sudarshan
Data Dictionary Storage (Cont.)
Catalog structure: can use either
•
specialized data structures designed for efficient access
•
a set of relations, with existing system features used to ensure efficient
access
•
The latter alternative is usually preferred
A possible catalog representation:
Relation-metadata = (relation-name, number-of-attributes,
storage-organization, location)
Attribute-metadata = (attribute-name, relation-name, domain-type,
position, length)
User-metadata = (user-name, encrypted-password, group)
Index-metadata = (index-name, relation-name, index-type,
index-attributes)
View-metadata = (view-name, definition)
Database System Concepts
3.27
©Silberschatz, Korth and Sudarshan
System Catalogs
For each index:
• structure (e.g., B+ tree) and search key fields
For each relation:
• name, file name, file structure (e.g., Heap file)
• attribute name and type, for each attribute
• index name, for each index
• integrity constraints
For each view:
• view name and definition
Plus statistics, authorization, buffer pool size, etc.
*
Catalogs are themselves stored as relations!
Database System Concepts
3.28
©Silberschatz, Korth and Sudarshan
Attr_Cat(attr_name, rel_name, type, position)
attr_name
attr_name
rel_name
type
position
sid
name
login
age
gpa
fid
fname
sal
Database System Concepts
rel_name
Attribute_Cat
Attribute_Cat
Attribute_Cat
Attribute_Cat
Students
Students
Students
Students
Students
Faculty
Faculty
Faculty
3.29
type
string
string
string
integer
string
string
string
integer
real
string
string
real
position
1
2
3
4
1
2
3
4
5
1
2
3
©Silberschatz, Korth and Sudarshan
End of Chapter 3