3.8 View tables 4.1 Entities, relationships, and attributes

materialized view

• is a view for which view table data is stored at all times.

• Whenever a view table changes, the corresponding view tables can also change, so materialized views must be refreshed.

WITH CHECK OPTION

is specified, the database rejects inserts and updates that do not satisfy the view query WHERE clause, the database generates an error message that explains the violation.

entity

a person, place, product, concept, or activity.

relationship

a statement about two entities.

attribute

a descript­ive property of an entity.

reflexive relationship

relates an entity to itself.

entity-relationship diagram / ER diagram

• is a schematic picture of entities, relationships, and attributes.

• Entities are drawn as rectangles.

entity type

is a set of things. Ex: All employees in a company.

relationship type

is a set of related things. Ex: Employee-Manages-Department is a set of (employee, department) pairs, where the employee manages the department.

attribute type

is a set of values. Ex: All employee salaries.

entity instance

is an individual thing. Ex: The employee Sam Snead.

relationship instance

is a statement about entity instances. Ex: "Maria Rodriguez manages Sales." .

attribute instance

is an individual value. Ex: The salary $35,000.

Analysis for Database Design

• Conceptual Design

• Analysis develops an entity-relationship model, capturing data requirements while ignoring implementation details.

Analysis Steps

Discover entities, relationships, and attributes

Determine cardinality

Distinguish strong and weak entities

Identify and Create supertype and subtype entities

Logical design

converts the entity-relationship model into tables, columns, and keys for a particular database system.

Logical design steps

Implement entities

Implement relationships

Implement attributes

Apply normal form

Physical design

adds indexes and specifies how tables are organized on storage media.

Strong Entity

• entity with own primary key.

• can exist independent of other entities.

Weak Entity

• entity with composite key.

• must depend on strong entities to exist.

• e.g. Orders (dependent on Customers), phone numbers (dependent on Customers).