3
THE RELATIONAL MODEL
Exercise 3.1 Define the following terms: relation schema, relational database schema,
domain, attribute, attribute domain, relation instance, relation cardinality,andrelation
degree.
Answer 3.1 Arelation schema can be thought of as the basic information describing
a table or relation. This includes a set of column names, the data types associated
with each column, and the name associated with the entire table. For example, a
relation schema for the relation called Students could be expressed using the following
representation:
Students(sid: string,name: string,login: string,
age: integer,gpa: real)
There are five fields or columns, with names and types as shown above.
Arelational database schema is a collection of relation schemas, describing one or more
relations.
Domain is synonymous with data type.Attributes can be thought of as columns in a
table. Therefore, an attribute domain refers to the data type associated with a column.
Arelation instance is a set of tuples (also known as rows or records) that each conform
to the schema of the relation.
The relation cardinality is the number of tuples in the relation.
The relation degree is the number of fields (or columns) in the relation.
Exercise 3.2 How many distinct tuples are in a relation instance with cardinality 22?
Answer 3.2 Answer omitted.
16
The Relational Model 17
Exercise 3.3 Does the relational model, as seen by an SQL query writer, provide
physical and logical data independence? Explain.
Answer 3.3 The user of SQL has no idea how the data is physically represented in the
machine. He or she relies entirely on the relation abstraction for querying. Physical
data independence is therefore assured. Since a user can define views, logical data
independence can also be achieved by using view definitions to hide changes in the
conceptual schema.
Exercise 3.4 What is the difference between a candidate key and the primary key for
a given relation? What is a superkey?
Answer 3.4 Answer omitted.
53831
53832
53650
53688
53666
50000 3.3
3.4
3.2
3.8
1.8
2.0
19
18
18
19
11
12
madayan@music
guldu@music
smith@math
smith@ee
jones@cs
dave@cs
Madayan
Guldu
Smith
Smith
Jones
Dave
sid age gpaloginname
TUPLES
(
RECORDS, ROWS)
FIELDS (ATTRIBUTES, COLUMNS)
Field names
Figure 3.1 An Instance S1 of the Students Relation
Exercise 3.5 Consider the instance of the Students relation shown in Figure 3.1.
1. Give an example of an attribute (or set of attributes) that you can deduce is not
a candidate key, based on this instance being legal.
2. Is there any example of an attribute (or set of attributes) that you can deduce is
a candidate key, based on this instance being legal?
Answer 3.5 Examples of non-candidate keys include the following: {name},{age}.
(Note that {gpa}can not be declared as a non-candidate key from this evidence alone
even though common sense tells us that clearly more than one student could have the
same grade point average.)
You cannot determine a key of a relation given only one instance of the relation. The
fact that the instance is “legal” is immaterial. A candidate key, as defined here, is a
18 Chapter 3
key, not something that only might be a key. The instance shown is just one possible
“snapshot” of the relation. At other times, the same relation may have an instance (or
snapshot) that contains a totally different set of tuples, and we cannot make predictions
about those instances based only upon the instance that we are given.
Exercise 3.6 What is a foreign key constraint? Why are such constraints important?
What is referential integrity?
Answer 3.6 Answer omitted.
Exercise 3.7 Consider the relations Students, Faculty, Courses, Rooms, Enrolled,
Teaches, and Meets In defined in Section 1.5.2.
1. List all the foreign key constraints among these relations.
2. Give an example of a (plausible) constraint involving one or more of these relations
that is not a primary key or foreign key constraint.
Answer 3.7 There is no reason for a foreign key constraint (FKC) on the Students,
Faculty, Courses, or Rooms relations. These are the most basic relations and must be
free-standing. Special care must be given toenteringdataintothesebaserelations.
In the Enrolled relation, sid and cid should both have FKCs placed on them. (Real
students must be enrolled in real courses.) Also, since real teachers must teach real
courses, both the fid and the cid fields in the Teaches relation should have FKCs.
Finally, Meets In should place FKCs on both the cid and rno fields.
It would probably be wise to enforce a few other constraints on this DBMS: the length
of sid,cid,andfid could be standardized; checksums could be added to these iden-
tification numbers; limits could be placed on the size of the numbers entered into the
credits, capacity, and salary fields; an enumerated type should be assigned to the grade
field (preventing a student from receiving a grade of G, among other things); etc.
Exercise 3.8 Answer each of the following questions briefly. The questions are based
on the following relational schema:
Emp(eid: integer,ename: string,age: integer,salary: real)
Works(eid: integer,did: integer,pcttime: integer)
Dept(did: integer,dname: string,budget: real,managerid: integer)
1. Give an example of a foreign key constraint that involves the Dept relation. What
are the options for enforcing this constraint when a user attempts to delete a Dept
tuple?
The Relational Model 19
2. Write the SQL statements required to create the preceding relations, including
appropriate versions of all primary and foreign key integrity constraints.
3. Define the Dept relation in SQL so that every department is guaranteed to have
a manager.
4. Write an SQL statement to add John Doe as an employee with eid = 101, age =32
and salary =15,000.
5. Write an SQL statement to give every employee a 10 percent raise.
6. Write an SQL statement to delete the Toy department. Given the referential
integrity constraints you chose for this schema, explain what happens when this
statement is executed.
Answer 3.8 Answer omitted.
sid name login age gpa
53831 Madayan madayan@music 11 1.8
53832 Guldu guldu@music 12 2.0
