Databases Chapter 3

relational database model (RDBMS)

logical database model that represents a database as a collection of related tables

relational schema

term associated with a visual depiction of the relational database model

relation

table in a relational database

Each column must have a unique name; each must be unique, all values in each column must be from the same (predefined) domain, within each row each value in each column must be single valued

relational database

collection of related relations within each relation has a unique name, every table name must be unique

primary key

column (or set of columns) in each relation whose value (or composite value) is unique for each row

mapping entities into relations

each regular entity becomes a relation

each regular attribute of a regular entity becomes a column of the newly created relation

if an entity has a single unique attribute then that attribute becomes the primary key resulting mapped relation otherwise must pick one

mapping entities with composite attributes into relations

each component of a composite attribute is mapped as a column of a relation and the composite attribute itself does not appear in the mapped relation

relational schema should include

data type and size, key designation, required constraints for the column (Not Null, Unique, Check, Default)

composite primary key

a primary key that is composed of multiple columns

entity integrity constraints

in a relational table, no primary key column can have null (empty) values

foreign key

column in a relation that refers to a primary key column in another (referred) relation

mapping 1:M relationships

foreign key goes on the many side

mapping many to many

intermediary table with two foreign keys

mapping entities with multivalued attributes into relational database constructs

The multi-valued attribute is mapped as a separate relation that has a column representing the multivalued attribute and a foreign key column referring to the primary key of the relation resulting from the entity itself

mapping derived attributes

not mapped as a part of the relational schema, implemented as apart of the database front end application

mapping 1:1 relationships

pick which one has the foreign key

referential integrity constraint

In each row of a relation containing a foreign key, the value of the foreign key either matches one of the values in the primary key column of the referred relation or the value of the foreign key is null

mapping unary relationships (1:M and 1:1)

The relation mapped from an entity involved in a 1:M unary

relationship contains a foreign key that corresponds to its own

primary key

mapping M:N unary relationships

This new relation has two foreign keys, both of them corresponding

to the primary key of the relation representing the entity involved in

the unary M:N relationship

Mapping weak entities

same as regular entities but has a composite primary key that is composed of the partial identifier and the foreign key corresponding to the primary key of the owner entity

mapping associative entities

like M:N relationships

auto number data type option

enables automatic generation of consecutive numeric data values in a column

designer created primary key

primary key column, not called for by the original requirements, added to a table by the database designer

implicit constraints

Each relation in a relational schema must have a different name

• Each relation must satisfy the following conditions:

o Each column must have a different name

o Each row must be unique

o Domain constraint - all values in each column must be from the same

predefined domain

o In each row, each value in each column must be single valued

o The order of columns is irrelevant

o The order of rows is irrelevant

primary key constraint

ach relation must have a primary key,

which is a column (or a set of columns) whose value is unique for

each row

entity integrity constraint