Database Concepts Review

This flashcard set covers fundamental concepts in databases, SQL, and ER modeling.

Which of the following is NOT a characteristic of the database approach?

Flat file storage

What is the function of a database catalog?

To store metadata

What is the function of the conceptual level in the three-schema architecture?

Describes the entire database for a community of users

Which architecture adds a layer between client and database server to process logic?

Three-tier

Which relational constraint requires values in a column to be unique and not null?

Entity Integrity

Which SQL keyword is used to retrieve data from a table?

SELECT

Which SQL clause is used to filter rows based on condition?

WHERE

What type of relationship occurs when one entity is associated with many instances of another?

One-to-Many

Which ER component is used to uniquely identify an entity?

Key

What does the entity integrity constraint ensure?

Primary key values must be unique and not NULL

Which SQL clause sorts the result set in ascending or descending order?

ORDER BY

What is the default behavior of SQL when duplicate rows exist in SELECT results?

Display all

What is the role of a database administrator?

Maintains the DBMS and ensures efficient operation

Which schema describes how data is actually stored?

Internal schema

What is a superkey?

A set of attributes that uniquely identifies a tuple

What does the term 'metadata' refer to in databases?

Data about data

Which constraint ensures that a foreign key must match a primary key?

Referential Integrity

What is a weak entity?

An entity dependent on another entity that does not have a primary key of its own.

What is the purpose of the WHERE clause in SQL?

To specify conditions for selection

Which SQL keyword removes duplicate results?

DISTINCT

Which ERD shape represents a relationship?

Diamond

Which SQL clause groups rows sharing a property?

GROUP BY

What kind of attribute can be broken down into sub-parts?

Composite attribute

Which command defines a table in SQL?

CREATE TABLE

Which component optimizes a query in DBMS?

Query optimizer

What does VDL stand for in DBMS?

View Definition Language

Which architecture separates presentation, logic, and data?

Three-tier

Which command adds a row in SQL?

INSERT INTO

Which SQL command deletes a table?

DROP TABLE

Which component translates DML to low-level instructions?

DML Compiler

Which language specifies the internal schema?

Storage Definition Language

What is a derived attribute?

An attribute calculated from other attributes

Which data model uses tables?

Relational model

Which clause filters groups in SQL?

HAVING

What is the output of a SELECT statement?

A relation (table)

Which user interacts with the database through queries?

Naive or casual user

What is the domain of an attribute?

A set of allowed values

What is a relation in relational databases?

A table with rows and columns

What is tuple in relational model?

A row in a table

What is schema?

The structure that defines the database

Which DBMS property ensures all-or-nothing transactions?

Atomicity

What is normalization?

Process of minimizing redundancy

What is denormalization?

Process of improving performance by adding redundancy

Which SQL function returns the number of rows?

COUNT()

Which key links two tables?

Foreign key

Which clause renames output columns in SQL?

AS

Which clause filters results before aggregation?

WHERE

What is functional dependency?

Relationship between attributes in a relation

Which user designs the database structure?

Database designer

What is a candidate key?

A minimal superkey

Which component manages physical data storage?

File manager

What is transaction in DBMS?

A sequence of operations as one unit

Which SQL clause is used to join tables?

JOIN

Which model preceded the relational model?

Hierarchical model

Which SQL statement changes a table structure?

ALTER TABLE

What is a view in SQL?

A virtual table

Which command retrieves unique rows only?

SELECT DISTINCT

What does SQL stand for?

Structured Query Language

Which model supports inheritance in databases?

Object-oriented model

What is a primary key?

An attribute that uniquely identifies each tuple

What is a relationship set in ER modeling?

A collection of similar relationships

Which type of participation ensures an entity must be involved in a relationship?

Total participation

Hierarchical Model

Organizes data in a tree-like structure with parent-child relationships.

Network Model

Data is organized as records connected through multiple relationships (graphs).

Relational Model

Stores data in tables (relations) with rows and columns.It uses primary keys to uniquely identify records and foreign keys to establish relationships between tables.

Object-Oriented Model

Integrates object-oriented programming with databases (objects = data + methods).

Document Model

Stores semi-structured data as documents, often in JSON or XML.

Key-Value Model

Stores data as a collection of key-value pairs. Allows for quick retrieval using unique keys.

Column-Family Model

Data stored in columns grouped into families (used in wide-column stores).

Graph Model

Data stored as nodes and edges to represent entities and relationships.It is commonly used for exploring complex relationships in social networks or web data.