Introduction to the Relational Model
This chapter prents the relational model and a brief introduction to the relational-
algebra query language.The short introduction to relational algebra is sufficient
for cours that focus on application development,without going into databa
internals.In particular,the chapters on SQL do not require any further knowl-
edge of relational algebra.However,cours that cover internals,in particular
query processing,require a more detailed coverage of relational algebra,which
is provided in Chapter6.
Exercis
2.9Consider the bank databa of Figure2.15.
a.What are the appropriate primary keys?
employee(person name,street,city)
works(person name,company name,salary)
company(company name,city)
订婚仪式
Figure2.14Relational databa for Exercis2.1,2.7,and2.12.
branch(branch name,branch city,asts)
customer(customer name,customer street,customer city)
loan(loan number,branch name,amount)
borrower(customer name,loan number)
account(account number,branch name,balance)
depositor(customer name,account number)
Figure2.15Banking databa for Exercis2.8,2.9,and2.13.
河水不洗船
7
8Chapter2Introduction to the Relational Model
申请法院调查取证
b.Given your choice of primary keys,identify appropriate foreign keys.
Answer:
a.The primary keys of the various schema are underlined.Although
梦见孩子死了in a real bank the customer name is unlikely to be a primary key,
since two customers could have the same name,we u a simplified
schema where we assume that names are unique.We allow customers
to have more than one account,and more than one loan.
branch(branch name,branch city,asts)
customer(customer name,customer street,customer city)
loan(loan number,branch name,amount)
预备党员自我鉴定
borrower(customer name,loan number)
account(account number,branch name,balance)
depositor(customer name,account number)
b.The foreign keys are as follows
i.For loan:branch name referencing branch.
ii.For borrower:Attribute customer name referencing customer and
loan number referencing loan
iii.For account:branch name referencing branch.
iv.For depositor:Attribute customer name referencing customer and
account number referencing account
2.10Consider the advisor relation shown in Figure2.8,with s id as the primary
key of advisor.Suppo a student can have more than one advisor.Then,
would s id still be a primary key of the advisor relation?If not,what should
the primary key of advisor be?
Answer:No,s id would not be a primary key,since there may be two(or
more)tuples for a single student,corresponding to two(or more)advisors.
The primary key should then be s id,i id.
2.11Describe the differences in meaning between the terms relation and relation
schema.
Answer:A relation schema is a type definition,and a relation is an instance
of that schema.For example,student(ss#,name)is a relation schema and
123-456-222John
234-567-999Mary
is a relation bad on that schema.
2.12Consider the relational databa of Figure2.14.Give an expression in the
老电影英雄儿女relational algebra to express each of the following queries:
a.Find the names of all employees who work for“First Bank Corpora-
tion”.
Exercis9
蓝蓝的天空白云飘
b.Find the names and cities of residence of all employees who work for
“First Bank Corporation”.
c.Find the names,street address,and cities of residence of all employees
who work for“First Bank Corporation”and earn more than$10,000.
Answer:
a. person name(company name=“First Bank Corporation”(works))
b. person name,city(employee1
(company name=“First Bank Corporation”(works)))
c. person name,street,city
((company name=“First Bank Corporation”∧salary>10000)
(works1employee)) 2.13Consider the bank databa of Figure2.15.Give an expression in the rela-
tional algebra for each of the following queries:
a.Find all loan numbers with a loan value greater than$10,000.
媳妇用英语怎么说b.Find the names of all depositors who have an account with a value
greater than$6,000.
c.Find the names of all depositors who have an account with a value
greater than$6,000at the“Uptown”branch.
Answer:
a. loan number(amount>10000(loan)
b. customer name(balance>6000(depositor1account))
c. customer name(balance>6000∧branch name=“Uptown”(depositor1account))
2.14List two reasons why null values might be introduced into the databa.
Answer:Nulls may be introduced into the databa becau the actual value is either unknown or does not exist.For example,an employee who address has changed and who new address is not yet known should be retained with a null address.If employee tuples have a composite attribute dependents,and a particular employee has no dependents,then that tuple’s dependents attribute should be given a null value.
2.15Discuss the relative merits of procedural and nonprocedural languages.
Answer:Nonprocedural languages greatly simplify the specification of queries(at least,the types of queries they are designed to handle).The free the ur from having to worry about how the query is to be evaluated;not only does this reduce programming effort,but in fact in most situations the query optimizer can do a much better task of choosing the best way to evaluate a query than a programmer working by trial and error.
On the other hand,procedural languages are far more powerful in terms of what computations they can perform.Some tasks can either not be
10Chapter2Introduction to the Relational Model
done using nonprocedural languages,or are very hard to express using
nonprocedural languages,or execute very inefficiently if specified in a
nonprocedural manner.
