CPSC 304 Midterm

Levels of Abstraction

External/View Level: different parts of db (ex: compare views of student, registrar, and database admin)

Conceptual/Logical Level: how data perceived by users

Physical Level: how data is actually stored (indexes, bits on disk)

Entities

Real-world object distinguishable from other objects, described using set of attributes

Usually nouns (students & courses)

Attributes

Property/characteristic describing entity, each attribute has a domain (float, int, etc)

Entity Set

Collection of similar entities (ex: all movie people), have same set of attributes, and each set has a key composed of all underlined attributes

Key

Minimal set of 1/more attributes that uniquely identify an entity in entity set

Uniqueness - No two entities can have the same values

Minimality - Can't remove any attribute and maintain uniqueness (clearly identify the entity)

Key of 1-to-1 relationship is key of ONE of entities

Key of many-to-1 is key of entity on many side

Primary Key

Key chosen as principal means to identify entities in a set

Relationship

Association among 2 or more entities

Relationship Set

Collection of similar relationships (ex: collection of all MoviePeople worked in Movies)'

May have descriptive attributes

Degree or arity refers to # entity sets in relationship (binary, ternary, etc)

Cardinality

Number of relationships in set that entity can participate in

Let R be relation set between entity A and B (from A to B)

1. One-to-one: A associated w. at most 1 entity in B (VV)

2. One-to-many: A associated w. any # entities in B, B associated w. at most 1 entity in A

3. Many-to-one: VV of one-to-many

4. Many-to-many: A associated w. any # entities in B (VV)

Key Constraint

Restrictions imposed by 1-1 + 1-many ratios, shown w. arrow

“If yk the entity w. the arrow, then yk the relationship”

Arrow points to the thing there is only 1 of

IsA Relationship

If declare A IsA B, every A entity a B entity. Adds desc. attributes specific to a class + restricts entities that participate in a relationship (ex: not all movie ppl are musicians who participate in musicals)

Overlap Constraints

Disjoint: superclass entity belongs to no more than single class

Overlapping: Classes may overlap

Covering Constraint

Total: superclass entity must belong to some class

Partial: some superclass entity may not be in any class

IsA Constraints

Total + Overlap: Must be both A and B or either

Total + DIsjoint: Must be either A or B

Partial + Overlap: Don’t need to be either A or B or both

Partial + Disjoint: Can be none, A, or B, but never both

Weak Entity

Identified uniquely only by additionally considering key of another (owner) entity

Like a belongs to relationship

Owner and weak entity must participate in a 1-to-many relationship set (1 owner, manyw eak entities)

Weak entity must have total participation

Think of building (owner) with apartment numbers (weak entity)

Aggregation

Treating relationship set as an entity set for purposes of participation in other relationships

Physical Data Independence

Ability to modify phys. schema w.out changing logical schema

Logical Data Independence

Can change conceptual schema w.out changing application

Relational Database

Set of relations

Relations made of 2 parts:

• Schema: name of relation + name & domain of each attribute (ex: Student (sid: char[20], name: char[20]…)

• Instance: table w. rows + cols

• # rows = cardinality

• # cols = degree/arity

Its schema is a collection of schemas in db

Database Instance

Collection of instances of its relations

Integrity Constraint

Must be true for ANY instance of DB (ex: domain constraints)

Specified when schema defined, and ICs checked when relations modified

Keys Constraints (for Relations)

1/more attributes in relation form a key (or candidate key) for relation

• No distinct tuples have same values for all attributes in key

• No subset of S is itself a key

Possible key chosen by DBA is primary key

Checked when new values are inserted or preexisting are modified

PRIMARY KEY Constraint in SQL

Specifies table’s primary key

Values must be unique and can’t be null

Other keys specified using UNIQUE constraint → values for group of attributes must be unique if not null (can be null)

FOREIGN KEY Constraint in SQL

Set of attributes in 1 relation used to ‘ref’ tuple in another relation

Must correspond to primary key of other relation

Referential Integrity in SQL

Referential Integrity: All foreign keys reference existing entities, no dangling references

• NO ACTION: default

• CASCADE: updates/deletes all tuples that refer to updated/deleted tuple (referenced tuple)

• SET NULL/SET DEFAULT: referencing tuple value set to default foreign key value