Relational database schemas #

IC = integrity constraints

Recall, a state is a collection of all the instances.

Example of schema and state #

Valid/invalid state #

Update operations on relations #

Whenever we do any of these update operations, there is a chance that the constraints may be violated. These violations may propagate to other tables.

REJECT is the most common action taken. CASCADE is also common, but requires setup in DBMS.

Respective examples of these are

• Domain constraints are common from type mismatches
• Key constraints happen if you try to insert another tuple with an already used key
• Null values where they aren’t allowed
• Null values in a primary key slot
• References to things that don’t exist

Note: We will study these last 2 slides more later.

ER-to-relational model mapping #

File: Ch 3 Slides

This chapter is about going from our ER models to the relational model.

Entities #

For example, consider this ER-diagram

This would be

employee(_ssn, salary, fname, minit, lname)

as a relational model. We only use the composite attribute name in the conceptual level for semantics.

Note: There is only one candidate key: ssn. I indicate the key with a leading underscore.

At this point, it is not case sensitive because we are still at the design level.

Consider this ER-diagram

This would be

classroom(_building, _roomNo, capacity)

as a relational model.

Note on notation: I prepend an underscore on both building and roomNo to indicate they are a composite key. Normally they would both be underlined.

Recall only 1 primary key is allowed per relation.

Relationships #

Consider this employee ER-diagram,

We will choose the entity with total participation to hold the foreign key,

employee(                   _ssn)
                            ^
department(dname, _dnumber, mgrssn)

Note on notation: The foreign key mgrssn should have an arrow drawn to ssn on employee. I indicate the arrow using the carrot ^.

If the relationship itself has a simple attribute, ie

then the attribute mgrstartdate should go in department (the entity with the foreign key).

employee(                   _ssn)
                            ^
department(dname, _dnumber, mgrssn, mgrstartdate)

The entity at the many side must include the foreign key to the other.

Consider

employee(_ssn, salary, dno)
                       v
department(dname,      _dnumber)

The foreign key dno references to the primary key dnumber.

If the relationship works_for has an attribute, ie

Then that attribute should also be where the foreign key is, ie

employee(_ssn, salary, dno, empstartdate)
                       v
department(dname,      _dnumber)

The reason we don’t have the department reference to the employee is because we will have many employees, and the department only has 1 foreign key.

The reason we don’t have employee have the foreign key for a project is because then it wouldn’t reflect the many-to-many relationship (an employee could only work on 1 project).

Weak entities #