Transcript CS186: Introduction to Database Systems

CS 405G: Introduction to
Database Systems
Lecture 14: Midterm Review
Instructor: Chen Qian
Homework 2

(2pt) What is a foreign key constraint? Why are such
constraints important? What is referential integrity?

A foreign key constraint is a statement of the form that one or
more fields of a relation, say R, together refer to a second
relation, say S. That is, the values in these fields of a tuple in R
are either null, or uniquely identify some tuple in S. Thus, these
fields of R should be a (candidate or primary) key.
Foreign key constraints are important because they provide
safeguards for insuring the integrity of data. Users are
alerted/thwarted when they try to do something that does not
make sense.
Referential integrity means all foreign key constraints are
enforced.


7/21/2015
Chen Qian @ Univ. of Kentucky
2
7/21/2015
Chen Qian @ Univ. of Kentucky
3


(a) Convert the ER diagram into relations
(b) Identify the keys and foreign keys of each relation
7/21/2015
Chen Qian @ Univ. of Kentucky
4
7/21/2015
Chen Qian @ Univ. of Kentucky
5

(a) List the names of all European cities with population
of more than 600,000.
7/21/2015
Chen Qian @ Univ. of Kentucky
6
7/21/2015
Chen Qian @ Univ. of Kentucky
7

(b) List the names of all countries for which no cities
have been entered into the City table.
7/21/2015
Chen Qian @ Univ. of Kentucky
8

(c) List names and continents of countries that are either
in Europe or whose capitals have a population of over 1
million.
7/21/2015
Chen Qian @ Univ. of Kentucky
9

Given two relations R1 and R2, where R1 contains N1
tuples, R2 contains N2 tuples, and N2 > N1 > 0, give the
minimum and maximum possible sizes (in tuples) for
the resulting relation produced by each of the following
relational algebra expressions. In each case, state any
assumptions about the schemas for R1 and R2 needed to
make the expression meaningful:
7/21/2015
Chen Qian @ Univ. of Kentucky
10
7/21/2015
Chen Qian @ Univ. of Kentucky
11
Homework 3
7/21/2015
Chen Qian @ Univ. of Kentucky
12
7/21/2015
Chen Qian @ Univ. of Kentucky
13

(b) Find the snames of suppliers who supply every part.
7/21/2015
Chen Qian @ Univ. of Kentucky
14

(c) Find the sids of suppliers who charge more for some
part than the average cost of that part (averaged over all
the suppliers who supply that part).
7/21/2015
Chen Qian @ Univ. of Kentucky
15

(d) Find the sids of suppliers who supply a red part and
a green part.
7/21/2015
Chen Qian @ Univ. of Kentucky
16
7/21/2015
Chen Qian @ Univ. of Kentucky
17

(a). Write the SQL statements required to create these re
lations, including appropriateversions of all primary and
foreign key integrity constraints
7/21/2015
Chen Qian @ Univ. of Kentucky
18

(a). Write the SQL statements required to create these re
lations, including appropriateversions of all primary and
foreign key integrity constraints
7/21/2015
Chen Qian @ Univ. of Kentucky
19



(b). Express each of the following integrity constraints
in SQL unless it is implied by the primary and foreign
key constraint; if so, explain how it is implied. If the
constraint cannot be expressed in SQL, say so.
I. Every class has a minimum enrollment of 5 students
and a maximum enrollment of 30 students.
Add
7/21/2015
Chen Qian @ Univ. of Kentucky
20

II. The department with the most faculty members must
have fewer than twice the number of faculty members in
the department with the fewest faculty members
7/21/2015
Chen Qian @ Univ. of Kentucky
21
Quiz 3
7/21/2015
Chen Qian @ Univ. of Kentucky
22

What is the relational algebra of the query “Find the sids
of suppliers who supply some red part or are at 221
Packer Street”?
7/21/2015
Chen Qian @ Univ. of Kentucky
23
Quiz 3
7/21/2015
Chen Qian @ Univ. of Kentucky
24

What is the relational algebra of the query “Find the sids
of suppliers who supply some red part or are at 221
Packer Street”?
7/21/2015
Chen Qian @ Univ. of Kentucky
25

What is the relational algebra of the query “Find the
pids of parts supplied by at least two different
suppliers”?
7/21/2015
Chen Qian @ Univ. of Kentucky
26

State what the following queries compute using your
words:

Find the Supplier ids of the suppliers who supply a red
part that costs less than 100 dollars and a green part
that costs less than 100 dollars.
7/21/2015
Chen Qian @ Univ. of Kentucky
27
Review: ER model

Entity and Entity type

Difference?

Relationship and relationship type

Relation: Key constraints
 One-to-one (1:1)
 One-to-many (1:N) or Many-to-one (N:1)
 Many-to-many
7/21/2015
Chen Qian @ Univ. of Kentucky
28
ER model



Weak Entity Types, partial key, owner
Identify and Avoid redundancy
To use an entity set should satisfy at least one of the
following conditions:

More than the name; at least one non-key attribute
7/21/2015
Chen Qian @ Univ. of Kentucky
29
7/21/2015
Chen Qian @ Univ. of Kentucky
30
Review: relational model


Relation. What can be translated to relations?
Schema
7/21/2015
Chen Qian @ Univ. of Kentucky
31
Review: relational model

Candidate key, primary key, and foreign key
Foreign key constraint and referential integrity

Translate an ER model to a relational model

7/21/2015
Chen Qian @ Univ. of Kentucky
32
Relational algebra

Core operators

Derived operators
7/21/2015
Chen Qian @ Univ. of Kentucky
33


Expression tree
Tips in writing R.A.




Use temporary variables
Use foreign keys to join tables
A comparison is to identify a relationship
Use difference in non-monotonic results
7/21/2015
Chen Qian @ Univ. of Kentucky
34
SQL

SELECT-FROM-WHERE statements

Renaming operation
Set and bag operations
Aggregation and grouping
Table expressions, subqueries
NULL’s and outerjoins








Modification
Constraints
Triggers
Views
7/21/2015
Chen Qian @ Univ. of Kentucky
35
Operational semantics

SELECT … FROM … WHERE … GROUP BY …
HAVING condition;





Compute FROM
Compute WHERE
Compute GROUP BY: group rows according to the values
of GROUP BY columns
Compute HAVING (another selection over the groups)
Compute SELECT for each group that passes HAVING
7/21/2015
Chen Qian @ Univ. of Kentucky
36