RE assignment 1 #

account_number is a unique value.

Relational model cont. #

State cont. #

R(color, bit)
dom(color) = {blue, green, red}
dom(bit)   = {0, 1}

This is all the possible combinations, but lets say we have a state like

Which visualizes that the state is a subset of all possible combinations.

Specifying an attribute’s value #

Constraints #

As a programmer we don’t have much of selection when it comes to model-based constraints.

DDL = data definition language

Note: We are not currently covering data dependencies.

Key constraints #

Versus a key, superkeys do not have to be minimal.

• {ssn, name} is a superkey
• {building, room_no} is a key, and a superkey

True or false

• Any superkey is a key
  • false
• Any key is a superkey
  • true

Primary key #

We have two candidate keys license_number and engine_serial_number. We will need to pick 1 to be the primary key.

Note on notation in relational model: we only underline the primary key.

Constraints on null values #

Entity integrity constraint #

Multiple relations – referential integrity constraints #

Foreign keys #

Foreign keys can be

• 1 attributes
• or, \( n \) attributes

The foreign key must

• have the same domain as the primary keys on the other schema.
• obey t1[foreign] = t2[primary]

DNO \( \to \) DNUMBER is a foreign key with 1 attribute

Remember, to be a foreign key, we need to verify that DNO and DNUMBER have the same domain. They do not have to have the same name.

If we change the requirement so that employees don’t have to work for a department, the keys must reference to existing attributes:

But if we have an employee that doesn’t have a department, we must put a null value:

Another example of a foreign key:

Notice that foreign keys create duplication among tuples.

Application based constraints #

Triggers and assertions are part of SQL.