Mod4-Relational Database Flashcards
Tables (relations), rows, columns
data structure
◦ Mechanisms for implementing business rules that
maintain integrity of manipulated data
data integrity
Powerful SQL operations for retrieving and modifying data
data manipulation
is a named, two-dimensional table of data.
relation
consists of rows (records) and columns (attribute or field).
table/relation
➢ Every attribute value must be atomic (not multivalued, not composite).
true
➢ Every row must be unique (can’t have two rows with exactly the same values
for all their fields).
true
➢ Attributes (columns) in tables must have unique names.
➢ The order of the columns must be irrelevant.
➢ The order of the rows must be irrelevant.
true
correspond with entity types and with many-to-many
relationship types.
relation(tables)
correspond with entity instances and with many-to-many relationship
instances.
rows
correspond with attributes.
columns
The word relation (in relational database) is NOT the same as the
word relationship(in E-R model).
true
are unique identifiers of the relation. Examples include employee
numbers, social security numbers, etc. This guarantees that all rows are unique.
primary keys
are identifiers that enable a dependent relation (on the many side of a
relationship) to refer to its parent relation (on the one side of the relationship).
foreign keys
can be simple (a single field) or composite
(more than one field).
keys
usually are used as indexes to speed up the
response to user queries.
keys
domain constraints
entity integrity
integrity constraints
▪ Allowable values for an attribute
domain contraints
No primary key attribute may be null. All primary key fields MUST have data.
entity integrity
rule states that any foreign key value (on the relation of the many
side) MUST match a primary key value in the relation of the one side. (Or the foreign
key can be null)
are drawn via arrow from dependent to parent table
are implemented with foreign key to primary key references
referential integrity
: E-R attributes map
directly onto the relation
Mapping Regular Entities to Relations
simple attributes
Use only their
simple, component attributes
Mapping Regular Entities to Relations
composite attributes
Becomes a separate
relation with a foreign key taken from the
superior entity
Mapping Regular Entities to Relations
multivalued attributes
▪Becomes a separate relation with a foreign
key taken from the superior entity
▪Primary key composed of:
✓Partial identifier of weak entity
✓Primary key of identifying relation (strong
entity)
mapping weak entities
➢ One-to-Many–Primary key on the one side
becomes a foreign key on the many side
➢ Many-to-Many–Create a new relation with the
primary keys of the two entities as its primary key
➢ One-to-One–Primary key on mandatory side
becomes a foreign key on optional side
mapping binary relationships
▪ Identifier Not Assigned
▪ Default primary key for the association relation is
composed of the primary keys of the two entities
(as in M:N relationship)
▪ Identifier Assigned
▪ It is natural and familiar to end-users
▪Default identifier may not be unique
mapping associative entities
▪One-to-Many–Recursive foreign key in the same
relation
▪Many-to-Many–Two relations:
▪ One for the entity type
▪ One for an associative relation in which the primary
key has two attributes, both taken from the primary
key of the entity
mapping unary relationships
➢ One relation for each entity and one for the
associative entity
➢ Associative entity has foreign keys to each
entity in the relationship
mapping ternary(and n-ary) relationships
➢ One relation for supertype and for each subtype
➢ Supertype attributes (including identifier and subtype
discriminator) go into supertype relation
➢ Subtype attributes go into each subtype; primary key of
supertype relation also becomes primary key of subtype
relation
➢ 1:1 relationship established between supertype and each
subtype, with supertype as primary table
mapping supertype/subtype relationships
