Skip to content

AEiE0703 Data modeling

Data abstraction and data independence

  • Data abstraction hides lower-level implementation details.
  • Physical data independence means physical storage changes do not force logical schema changes.
  • Logical data independence means logical schema changes minimally affect user views or applications.

Schema and instance

Term Meaning
Schema Overall database design or structure definition
Instance Actual data contents at a particular time

Cue:

  • Schema changes rarely; instances change frequently.

E-R model

  • Entity: distinguishable object or concept.
  • Attribute: property of an entity.
  • Relationship: association among entities.
  • E-R diagram graphically shows entities, attributes, relationships, and participation.

Strong and weak entity sets

Entity type Key cue
Strong entity Has its own primary key
Weak entity Lacks full key of its own and depends on owner entity

A weak entity has a partial key or discriminator that is unique only within one owner. Its full identifying key combines the owner entity's primary key with that partial key.

Attributes and keys

Term Meaning
Super key Any attribute set that uniquely identifies tuple
Candidate key Minimal super key
Primary key Chosen candidate key
Foreign key Attribute referencing another relation's key
Composite key Key formed from multiple attributes

Functional dependency

  • \(X \to Y\) means attribute set \(X\) functionally determines attribute set \(Y\).
  • Functional dependencies drive normalization.

Normal forms

Normal form Main requirement
1NF Atomic attribute values, no repeating groups
2NF In 1NF and no partial dependency of non-key attribute on part of a composite key
3NF In 2NF and no transitive dependency of non-key attribute on key
BCNF For every nontrivial FD \(X\to Y\), \(X\) is a super key

Precise traps:

  • 2NF matters only when composite keys create partial dependency possibilities.
  • 3NF removes transitive dependencies among non-key attributes.
  • BCNF is stricter than 3NF.

Integrity constraints and domain constraints

  • Domain constraint restricts allowed values for an attribute.
  • Entity integrity requires primary key not null.
  • Referential integrity requires foreign-key references be valid or appropriately null.

Relations, joins, derived data, queries, views

  • Join combines related tuples from two or more relations.
  • Derived relation or derived attribute can be computed from existing data.
  • DDL defines schema objects.
  • DML manipulates stored data.
  • A view is a virtual table defined by a query.
SQL class Examples
DDL CREATE, ALTER, DROP
DML SELECT, INSERT, UPDATE, DELETE

Data-modeling examples

  1. If an attribute contains multiple comma-separated values in one field, 1NF is violated.
  2. If StudentID, CourseID is a composite key and StudentName depends only on StudentID, then 2NF is violated.
  3. A view stores query definition, not necessarily an independently materialized base table in the ordinary logical sense.

AEiE0703 revision box

  • Schema is structure; instance is current data.
  • Strong entity has own key; weak entity depends on owner.
  • 1NF atomic, 2NF no partial dependency, 3NF no transitive dependency, BCNF determinant must be super key.
  • Domain, entity, and referential integrity are distinct.
  • DDL defines structure; DML changes or retrieves data.