Week 01 Notes 02

Relation definition

Here is the figure from the book showing the characteristics a table must possess to be a relation:

Notes:

· To be a relation, a table must meet all of the conditions shown above.

· An entity is like a class in Java – it defines the attributes (instance variables in Java) that each instance of the class possesses. A row in the relation is like a Java object built from the class.

· Some of the attributes of an entity may refer to other entities. In Java, class A may have an instance variable holding a reference to an object built from class B.

· For our purposes, Column and Attribute are words that mean virtually the same thing. (See terminology equivalence in the text – bottom of p. 55.) In the Java metaphor, an attribute is a particular instance variable defined by the class, which we might also think of as a column ‘heading’.

· An entry in a column means a cell in a particular row in the table. “Of the same kind” means “will be interpreted in the same way as other entries in this column”.

See terminology equivalence bot. p. 55.

Keys

An field or set of fields whose value uniquely identifies a row in the table. (Ullman, “Principles of Database Systems”, Computer Science Press, 1980.)

Uniquely identifying things is a very hard problem in computer science with wide ranging applications, e.g., security.

(These days the term non-unique key is used in database systems, but it is not helpful to us at this point.)

In Mysql and MS Access, if no field(s) is(are) specified as a key, the table may not be a relation, i.e., it may contain duplicate rows.

In general, every table should have a key, though on rare occasions, it is useful to allow duplicate records. Note: tables having duplicate records are not relations.

Multiple relations

Two themes / entities / relations / tables can be related – hence the name relational model.

Students (theme1) live at a particular address (theme 2).

Like designing in java – identify the nouns in the problem – each noun or theme should be stored in its own table.

A student has a name and an address.

An address has a street, city, zip

Q. Why put the two entities in different tables?

A. So we can associate multiple instances of one entity with a single instance of the other, e.g., many students can share the same address.

Tying tables together

Add a column to one table that holds key values in a different table.

Example

Many students may live at the same address

Give each address a unique identifier (addressID) and make addressID the key of the address table.

Add an addressID column to the student table

Exercise

Use MySQL Workbench to create two tables in your database:

Student:

sid

name

Address:

aid

street

city

zip

Modify to be able to associate a student’s address with their student record.

Enter an address and two students such that both students live at the same address.

Create Student Course Schedule tables - https://webpages.sou.edu/~nordquip/cs360/demos/studentCourseScheduleDDL.sql

Initialize Student Course Schedule tables - https://webpages.sou.edu/~nordquip/cs360/demos/studentCourseScheduleDML.sql

Kinds of Keys

Primary Key – the candidate key the table designer specifies as the key that will be used to uniquely identify rows in the table.

Foreign Key – a field that is a primary key in a different table. The field name for a foreign key is not required to be the same as the field name used in the table in which it is the primary key, but we usually do give these fields the same name for clarity.

Theory about Keys

Composite Key – key composed of two or more fields

Pet(name,ownerName,age) -- assuming owners give each of their pets a unique name

Candidate key – field or set of fields that uniquely identifies all rows in a table. Key is only a candidate key if it is “irreducible”, meaning no subset of the attributes in the candidate key also make a key.

In the Pet relation above, notice Pet(name, ownerName, age), is a composite key, but it is not a candidate key, because it is Pet(name,ownerName,age) also uniquely identifies all of the rows.

Another example:

Person:

name	btime	motherName
jim	4/5/15 12:01	emma
jane	4/5/15 12:00	emma
tim	4/5/16 5:00	emma
jane	4/5/15 12:01	summer

Candidate Keys for the relation as it is shown above:

1. Person(name, bTime, motherName)

no two people can be birthed by the same mother at the same time

2. Person(name, bTime, motherName)

no two people born from the same mother have the same name

Notes:

1. The candidate keys shown hold for the relation given above, but not in general, because names can be duplicated – jane in the second row is clearly not the same person as jane in the last row, because they have different mothers.

When answering questions about candidate keys, always work from some example table.

2. Person(name, bTime, motherName) is a key, but not a candidate key, because the attributes in either of the candidate keys above, (bTime, motherName) or (name, motherName) are a subset of (name, bTime, motherName)

It is both natural and good practice to structure relations so there is only one candidate key.

Surrogate Key – an auto-number field whose values are maintained by the DBMS. When a table has a surrogate key, the DBMS assigns a new value to that field whenever a new row is added. The DBMS automatically uses this field as the primary key.