Lecture2 - Relational Models and Database Schema

Flashcards covering key concepts from Lecture 2: Relational Models and Database Schema, including data models, ER and relational models, keys and constraints, schema vs state, three-schema architecture, data independence, DBMS languages, and client-server architectures.

What is a data model?

A collection of high-level description concepts for describing data; it hides low-level storage details and defines the structure and constraints of the data.

What is a schema in a data model context?

A description of a particular collection of data using the given data model.

Name the three broad categories of data models.

Conceptual (high-level), Physical (low-level internal), and Implementation/Relational (between, e.g., relational) data models.

What are the characteristics of a conceptual data model?

High-level, semantic; uses concepts like entities, attributes, and relationships; close to how users perceive data.

What is the ER (Entity-Relationship) model used for?

A model for database design using entities and relationships; usually converted to a relational design for storage.

What is the relational data model?

Data stored in relations (tables); schema defined as RelationName(field1:type1, …, fieldn:typen); supported by SQL.

What is a schema diagram?

A diagrammatic display of a database schema showing relation names, attributes, keys, and constraints.

How is a relation defined in the relational model?

As a table with columns (attributes) and rows (tuples).

What is a domain in relational terms?

The set of allowable values for a given attribute.

What is the degree of a relation?

The number of attributes (columns) in the relation.

What is the cardinality of a relation?

The number of tuples (rows) in the relation.

What is a primary key?

A candidate key selected to uniquely identify tuples in a relation.

What is a foreign key?

An attribute or set of attributes in one relation that references the primary key of another relation.

What is a superkey?

An attribute or set of attributes that uniquely identifies a tuple within a relation.

What is a candidate key?

A minimal superkey whose proper subset is not a superkey.

What is an alternate key?

A candidate key that is not chosen as the primary key.

What is a NULL value and its meanings?

Represents unknown or inapplicable data; meanings include value unknown, exists but not available, or not applicable.

What are the main types of database integrity constraints?

Entity integrity, referential integrity, domain constraints, and semantic integrity constraints.

What is entity integrity?

Primary key values cannot be NULL.

What is referential integrity?

Foreign key values must reference an existing primary key value in the referenced relation, or be NULL in certain cases.

What is a domain constraint?

Constraints on the data type and allowed values for an attribute (e.g., integer, date, string).

What are semantic (business) integrity constraints?

Constraints based on application semantics that may require a constraint language to express (e.g., max hours per employee).

What is a database schema vs a database instance/state?

Schema (intension) describes the structure; instance/state (extension) is the actual data at a moment in time.

What is the three-schema architecture?

External (views), Conceptual (logical schema), Internal (physical storage).

What is the purpose of an external schema?

Describe user views and provide customized access to data; multiple external schemas can exist.

What does data independence mean?

The ability to change one level’s schema without affecting higher-level schemas; includes logical and physical independence.

What is the difference between logical data independence and physical data independence?

Logical: change in the conceptual schema without changing external schemas; Physical: change in the internal schema without changing the conceptual schema.

What type of language is SQL considered in DBMS terminology?

A high-level, non-procedural (declarative) language that specifies what data to retrieve, not how to retrieve it.

What are the main components of the DBMS architecture described?

Storage manager and query processor.

What does the storage manager do?

Interacts with the file system to store, retrieve, and update data; translates DDL/DML; manages data dictionary.

What is the query processor responsible for?

DDL interpreter, DML compiler, and the query evaluation engine that executes queries.

What data structures are part of the storage manager?

Data files, data dictionary, and indices.

What is the two-tier client architecture?

First tier: client (user interface); second tier: database server (data storage and validation); enables direct client-server interaction.

What are advantages of the two-tier architecture?

Wider access to databases, simplified deployment, and potential performance benefits; fewer layers can mean lower overhead.

What is the three-tier client architecture?

Three tiers: client (UI), application server (business logic), and database server (data storage and validation).

What are advantages of the three-tier architecture over the two-tier?

Less powerful clients needed, centralized maintenance, easier to modify individual tiers, and better suitability for web environments.