Module 2 Flashcards
Relational Data Model
4 basic concepts of the relational model?
- relations
- attributes
- domains
- tuples
relation?
the main construct for representing data. Informally, it is a set of records and similar to a table with columns and rows
is every table a relation?
no, every relation is a table but not every table is a relation
what are the key components of a relation?
- relation name
- attribute names
- tuples (rows)
- attribute values from the same domain
domain types?
the data type or format that a certain attribute will accept.
E.g. only accepting positive integers
attributes?
attribute is the name of a role played by some domain in the relation
E.g. the attribute salary must belong to the domain of salary
The number of attributes in a relation R is called the degree of R
domain/attribute restrictions?
same attribute name does not necessarily imply same domain
E.g. attribute ID exists for two entities but the format/range is different (1int vs. 4ints)
vice versa
different attribute name does not necessarily imply different domain
E.g. ID vs managerID (different attribute names but both are 4ints)
tuples?
tuple (t) is an ordered list of n values
where each value is an element of the corresponding attribute or the special NULL value
E.g. (1234, Ephraim, 23, NULL) is a 4-tuple
relation schema?
denoted by R[A1, A2, A3, …, A(n)] includes a relation name (R) and list of attributes A1, A2, A3, …, A(n)
E.g. A relation schema of degree 5
Employee [id, name, sex, salary, department]
integer n is “degree of relation” (how many attributes)
relation instance?
relation instance (r) of the relation schema (R), denoted by r(R), is a set of n-tuples r = {t1, t2, …, t(m)}
in normal English, a set (instance) of tuples of relational type R
E.g. a table of the same type of tuple (employee table)
ordering of tuples?
mathematically, no implied order
semantically, order irrelevant
physically, reside in blocks of storage with partial ordering therefore TUPLES HAVE AN ORDERING
ordering of values within a tuple?
syntactically, all tuples in a relation have same order
semantically, order chosen is irrelevant as long as the values are matched with the correct attribute
database integrity constraints?
rules, that every instance, of the schema must hold (as well as the transitions of the schema)
- domain
- key
- entity integrity
- referential integrity
- semantic integrity
domain constraints?
using incorrect data type
E.g. using string when attribute is type integer
key contraint?
keys must remain unique at all times
entity integrity constraint?
no primary key can be NULL
for primary keys with multiple attributes, no part of the primary key may be NULL
referential integrity constraint?
one relation references something that doesn’t exist in another relation
E.g. student goes to school called ACIT but ACIT doesn’t exist in the school table/relation
semantic integrity constraint?
known as business rules
cannot be directly expressed in the schema of data model
E.g. “salary of employee should not exceed supervisor’s salary”
uniqueness constraint?
all tuples in a relation must be distinct
superkey?
subset of attributes that make the tuple unique
every relation has AT LEAST ONE SUPERKEY
E.g. combination of attributes that can uniquely identify a tuple aka set of all attributes (assuming uniqueness constraint isn’t broken)
key?
also known as minimal superkey
smallest set of attributes that uniquely identify a tuple
schema may have more than one key, these are known as CANDIDATE KEYS
ONE candidate key is selected as the primary key
foreign key?
FK in R1 is a foreign key referencing PK in R2
FK and PK have same domain
self referential relations?
possible for table to reference itself
E.g. employee relation has attribute ID and attribute ManagerID, some employees will have NULL ManagerID’s since they are a manager
relations with composite keys?
FK’s to relations with multi-attribute PK’s is possible
E.g. different parts of a multi-attribute PK reference different relations
constraints effect of operations?
constraints ensure database remains consistent
if a suggested change violates any constraints it must be rejected
DBMS enforces constraints
potential effect of deletion on constraints?
referential integrity may potentially be broken
tuple being deleted is referenced in another relation
transaction concept
an executing program that includes some type of database interaction
E.g. insert, delete, update, etc…
END of transaction must leave database in valid or consistent state
E.g. between two valid + consistent states some constraints may be broken in between but the FINAL state must be valid + consistent
transactions allow execution of a suite of queries where constraint violation in INTERMEDIATE STEPS are allowed
7 + 1 steps for mapping?
- entity mapping
- weak entity mapping
- binary relationship 1: 1 mapping
- binary relationship 1:M mapping
- binary relationship N:M mapping
- mulit-valued attribute mapping
- n-ary relationship mapping
- super & subclasses
1 - 7 mapping of ER
8 mapping of EER
entity mapping?
non-weak entity with simple attributes
DON’T include multi-valued and derived attributes
CHOOSE ONE primary key attribute
weak entity mapping?
create relation for weak entity and their corresponding owner entity
include FK attributes as weak entity PK with dotted underline
PK of weak entity is combination of owner entity + weak entity PK’s
rules do not change when multiple owners exist
DON’T FORGET WEAK ENTITIES AND THEIR RELATIONS ARE REPRESENTED WITH DOUBLE LINES
