D426 v3

What is data in the context of databases?

Numeric, textual, visual, or audio information that describes real-world systems.

Why is data collected and processed?

To aid in tasks like forecasting weather, analyzing financial investments, and tracking the global spread of pandemics.

List three important ways data can vary.

Scope (amount of data), Format (numbers, text, images, audio, video), Access (private or public).

Give an example of data scope.

A small business might track a few thousand items; a large commerce website might track billions.

Give an example of data format.

A phone's proximity sensor generates raw numbers; a satellite captures images.

Give an example of data access.

A retail company uses private customer data; a government may be required to share certain data sets.

How was most data recorded historically?

Mostly analog, encoded as continuous variations on various physical media (e.g., vinyl disks, film).

How is most data recorded today?

Mostly digital, encoded as zeros and ones on electronic and magnetic media.

What did the shift from analog to digital data facilitate?

The rise of large computer databases.

Name five websites that offer public data sets.

data.gov, cancer.gov/research, kaggle.com, data.nasa.gov, opendata.cityofnewyork.us

What is a database?

A collection of data in a structured format.

Where are modern databases usually stored?

On computers.

Why is database structure important?

It ensures similar data is stored in a standardized manner.

Why do modern databases require sophisticated software tools?

They may contain trillions of bytes of data and support thousands of simultaneous users.

What is a database system (DBMS)?

Software that reads and writes data in a database, ensuring data is secure, internally consistent, and available at all times.

What is a query?

A request to retrieve or change data in a database, written in a specialized query language.

What is a query language?

A specialized programming language designed specifically for database systems.

What is a database application?

Software that helps business users interact with database systems and simplifies the user experience.

Why are database applications necessary?

Most users are not familiar with query languages and direct database access is usually not feasible.

How is database software organized?

In layers: applications interact with a query language, which interacts with the database system.

What is an information management system?

A software application that manages corporate data for a specific business function, usually includes a database system and other components.

How is the term "database" used?

Sometimes refers to the data itself, sometimes to the database system (DBMS); the meaning is usually clear from context.

List four main database roles.

Database administrator, database designer, database programmer, database user.

What does a database administrator do?

Secures the database system and enforces procedures for user access and database system availability.

What does a database designer do?

Determines the format of each data element and the overall database structure, balancing several priorities.

What does a database programmer do?

Develops programs that utilize a database, combining database query languages with general-purpose programming languages.

What does a database user do?

Requests, updates, or uses stored data to generate reports or information.

When might one person have multiple database roles?

In simple databases with a limited amount of data and few users.

When are database roles usually separated?

In large, complex databases.

When can a small database be managed with a text file or spreadsheet?

When it is shared by only one or two users.

Why are text files and spreadsheets inadequate for large complex databases?

They lack performance, authorization, security, rules enforcement, and recovery features required by large, multi-user databases.

List five special requirements for large complex databases.

Performance, Authorization, Security, Rules, Recovery.

Why does query response time degrade in large databases?

Because many users and applications may access large databases simultaneously.

How do database systems maintain fast response times?

By structuring data properly on storage media and processing queries efficiently.

What is authorization in a database system?

Limiting user access to specific tables, columns, or rows.

How is security enforced in database systems?

By restricting access, encrypting data, and protecting against hackers.

What are rules in the context of a database system?

Requirements to keep data consistent with structural and business policies.

Give an example of a business rule in a database.

A course number in student registration must exist in the course catalog.

Why is recovery important in databases?

Computers, database systems, and transactions can fail; systems must restore the database to a consistent state without loss of data.

What is a transaction?

A group of queries that must be either all completed or all rejected as a whole.

Give an example of a transaction.

A debit-credit transaction transferring funds from one bank account to another.

Why must transactions be all-or-nothing?

Partial execution can result in inconsistent or incorrect data.

List three requirements for transaction management.

Transactions must be processed completely or not at all; prevent conflicts between concurrent transactions; ensure transaction results are never lost.

What is the architecture of a database system?

The internal components of a database system and the relationships between those components.

What are the main components of most database systems?

Query processor, storage manager, transaction manager, log, catalog (data dictionary).

What does the query processor do?

Interprets queries, creates a plan to modify or retrieve data, returns query results, and performs query optimization.

What does the storage manager do?

Translates query processor instructions into low-level file-system commands that modify or retrieve data, and uses indexes to quickly locate data.

What does the transaction manager do?

Ensures transactions are properly executed, prevents conflicts between concurrent transactions, and restores database consistency after failures.

What is the log in a database system?

A file containing a complete record of all inserts, updates, and deletes processed by the database.

What is the catalog or data dictionary?

A directory of tables, columns, indexes, and other database objects; used by other components to process and execute queries.

Does the query processor have direct access to the database data on storage media?

No, all access to the database data must go through the storage manager.

Can the storage manager retrieve data without query optimization?

Yes, but query optimization ensures the most efficient instructions are sent to the storage manager.

Does the catalog record queries processed by the database system?

No, the catalog describes database objects; processed queries are recorded in the log.

What is needed to restore the database after a system failure during processing?

Both the transaction manager and the log.

What is metadata?

Data about the database, such as column names and the number of rows in each table, stored in the catalog.

What is a relational database?

A database that stores data in tables, columns, and rows, similar to a spreadsheet.

What is SQL?

Structured Query Language

What are relational systems ideal for?

Databases that require an accurate record of every transaction, such as banking, airline reservation systems, and student records.

What is big data?

Massive volumes of online data, often with poorly structured or missing information.

What are NoSQL databases?

Non-relational databases optimized for big data ("not only SQL").

What is open source software?

Software that anyone can inspect, copy, and modify with no licensing fee.

Give examples of leading database products.

Oracle Database, MySQL, SQL Server, PostgreSQL, MongoDB.

What is a query in a database system?

A command for a database that typically inserts new data, retrieves data, updates data, or deletes data from a database.

What is a query language?

A computer programming language for writing database queries.

Which query does NOT change the database contents?

SELECT (it only retrieves data; insert, update, and delete change data).

Can a SELECT query return nothing?

Yes, if no data matches the query condition.

Are insert select, update, and delete the only necessary commands to interact with a database system?

No, other commands are needed to create and delete databases, tables, columns, and database objects.

Can an update query modify data that isn't in the database?

No, data must be in the database for an update query to make an update.

What is an SQL statement?

A complete, executable database command that performs operations like retrieving or editing data, creating a database, defining transactions, or authorizing users.

What does the SQL CREATE TABLE statement do?

Creates a new table by specifying the table and column names and assigning data types to each column.

Name examples of SQL data types.

INT (integer), DECIMAL (fractional numeric), VARCHAR (text), DATE (year, month, day).

What does VARCHAR(10) mean?

A variable-length character string with a maximum of 10 characters.

What does DECIMAL(10, 3) mean?

A decimal number with up to 10 significant digits, three of which are after the decimal point.

What is database design?

A specification of database objects such as tables, columns, data types, and indexes, or the process used to develop that specification.

What are the three phases of database design for large complex databases?

1. Conceptual design 2. Logical design 3. Physical design

What is the conceptual design phase?

Specifies database requirements without regard to a specific database system.

How are requirements represented in conceptual design?

As entities, relationships, and attributes.

What is an entity?

A person, place, activity, or thing.

What is a relationship in database design?

A link between entities.

What is an attribute in database design?

A descriptive property of an entity.

What is an ER diagram?

An entity-relationship diagram that depicts entities, relationships, and attributes visually.

How are entities shown in ER diagrams?

Rectangles with round corners with the entity name at the top.

How are relationships shown in ER diagrams?

Lines between rectangles.

How are attributes shown in ER diagrams?

Text inside rectangles and below entity names.

What is logical design?

Implements database requirements in a specific database system by converting entities, relationships, and attributes into tables, keys, and columns.

What is a key in a relational database?

A column used to identify individual rows of a table.

What is a table diagram?

A detailed diagram similar to an ER diagram, showing tables as rectangles with square corners, columns inside rectangles, key columns marked with bullets, and arrows for foreign keys.

What is a database schema?

The logical design as specified in SQL and depicted in a table diagram.

What is physical design?

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

Does physical design affect query results in relational databases?

No, physical design only affects performance; logical design affects query results.

What is data independence?

The principle that changes to physical design never affect query results.

Why is data independence important?

It allows designers to tune query performance without changing application programs.

Can relational database applications be programmed before physical design is complete?

Yes, due to data independence.

Does SQL have important programming features like object orientation?

No, most SQL implementations are not object-oriented.

How are database programs typically written?

By combining SQL with a general-purpose programming language like C++, Java, or Python.

What is an API in database programming?

An application programming interface; a library that links a host programming language to a database.

Give an example of a database API.

JDBC (Java Database Connectivity) for Java.

Why are APIs used in database programming?

They simplify the use of SQL with a general-purpose language and handle connection, query execution, and result retrieval.

Do all programming languages use the same database API?

No, each programming language supports different APIs.

Do major languages support multiple database APIs?

Yes, languages like C++ and Java support several APIs.