1. Introduction to Databases

Question 1

What is data?

Answer 1

Data is numeric, textual, visual, or audio information that describes real-world systems.

Data is used for various tasks such as forecasting weather and analyzing financial investments.

Question 2

Name the important ways data can vary.

Answer 2

• Scope
• Format
• Access

Scope pertains to the amount of data, format refers to the type of data (numbers, text, etc.), and access relates to whether data is private or public.

Question 3

What is the historical difference between analog and digital data?

Answer 3

Historically, data was mostly analog, while today it is mostly digital, encoded as zeros and ones.

Analog data was stored on physical media like vinyl disks and celluloid tapes.

Question 4

What is a database?

Answer 4

A database is a collection of data in a structured format, typically stored on computers.

Databases ensure that similar data is stored in a standardized manner.

Question 5

What is a database management system (DBMS)?

Answer 5

A DBMS is software that reads and writes data in a database, ensuring data security and availability.

DBMSs are complex due to the challenges of managing large databases with many users.

Question 6

What is a query?

Answer 6

A query is a request to retrieve or change data in a database.

Query languages are specialized for efficient interaction with databases.

Question 7

What does a database application do?

Answer 7

A database application helps business users interact with database systems.

It simplifies user experience and ensures efficient and secure data access.

Question 8

Fill in the blank: A _______ is a request to retrieve or change data in a database.

Answer 8

query

Question 9

What roles interact with databases?

Answer 9

• Database Administrator
• Database Designer
• Database Programmer
• Database User

Each role has specific responsibilities related to database management and use.

Question 10

What is the responsibility of a database administrator?

Answer 10

A database administrator secures the database system against unauthorized users and manages user access.

They enforce procedures for database availability.

Question 11

What does a database designer do?

Answer 11

A database designer determines the format of each data element and the overall database structure.

This role balances priorities like storage and response time.

Question 12

What is the difference between a database programmer and a database user?

Answer 12

A database programmer develops applications using database query languages, while a database user consumes data from the database.

Users typically interact through applications.

Question 13

What are the limitations of file systems compared to database systems?

Answer 13

• Performance
• Authorization
• Security
• Rules
• Recovery

File systems cannot adequately manage large, complex databases with many users.

Question 14

What is a transaction in the context of databases?

Answer 14

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

This ensures data consistency and correctness.

Question 15

True or False: A transaction can be partially completed.

Answer 15

False

All queries in a transaction must succeed or fail together to maintain data integrity.

Question 16

What is the role of a database user?

Answer 16

A database user requests, updates, or generates reports from stored data.

Users can access databases via applications or directly submit queries.

Question 17

Fill in the blank: A _______ is responsible for defining the detailed database design.

Answer 17

database designer

Question 18

What does a database system ensure regarding data?

Answer 18

Database systems ensure data is consistent with structural and business rules.

This includes synchronizing multiple copies of data across locations.

Question 19

What is a transaction in database systems?

Answer 19

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

Question 20

What happens if some queries in a transaction succeed while others fail?

Answer 20

It results in inconsistent or incorrect data.

Question 21

What must database systems ensure when processing transactions?

Answer 21

• Transactions are processed completely or not at all
• Prevent conflicts between concurrent transactions
• Ensure transaction results are never lost

Question 22

True or False: A transaction must process either both queries or neither query.

Answer 22

True

Question 23

What is the role of the transaction manager in a database system?

Answer 23

Ensures transactions are properly executed and restores the database to a consistent state in case of failure.

Question 24

What is the purpose of the log in a database system?

Answer 24

Contains a complete record of all inserts, updates, and deletes processed by the database.

Question 25

What is the catalog in a database system?

Answer 25

A directory of tables, columns, indexes, and other database objects.

Question 26

Fill in the blank: The SQL stands for ______.

Answer 26

Structured Query Language

Question 27

What are the four common types of database queries?

Answer 27

* Insert * Select * Update * Delete

Question 28

What does a select query do in a database?

Answer 28

Retrieves information from the database.

Question 29

True or False: An update query can update data that isn't in the database.

Answer 29

False

Question 30

What is a key characteristic of relational databases?

Answer 30

Stores data in tables, columns, and rows.

Question 31

What is a distinguishing feature of NoSQL databases?

Answer 31

Optimized for big data generated on the internet.

Question 32

What does CRUD stand for?

Answer 32

Create, Read, Update, Delete

Question 33

True or False: All relational database systems support SQL.

Answer 33

True

Question 34

What are the key components of a database system architecture?

Answer 34

* Query Processor * Storage Manager * Transaction Manager * Log * Catalog

Question 35

What is the function of the storage manager in a database system?

Answer 35

Translates query processor instructions into low-level file-system commands.

Question 36

Fill in the blank: The term NoSQL originally meant '______'.

Answer 36

does not support SQL

Question 37

What type of database system has become popular since 2000?

Answer 37

Open source database systems

Question 38

What happens to transactions in the event of a system failure?

Answer 38

The transaction manager and log are necessary to restore the database.

Question 39

True or False: SQL was first developed at Microsoft.

Answer 39

False

Question 40

What does the acronym ANSI stand for in relation to SQL?

Answer 40

American National Standards Institute

Question 41

What is the primary purpose of an INSERT statement in SQL?

Answer 41

Inserts rows into a table.

Question 42

What does a DELETE statement do in SQL?

Answer 42

Deletes rows from a table.

Question 43

What is the preferred pronunciation of SQL?

Answer 43

'S-Q-L'

Question 44

What does the Account table in a bank database contain?

Answer 44

Three columns: ID, Name, and Balance

Question 45

What does the INSERT statement do in SQL?

Answer 45

Adds a new row to a table

Question 46

What is the purpose of the SELECT statement?

Answer 46

Retrieves data from a database

Question 47

What is the effect of the following UPDATE statement: UPDATE Account SET Balance = 4500 WHERE ID = 831?

Answer 47

Changes Raul's balance from 3300 to 4500

Question 48

What does the DELETE statement do in SQL?

Answer 48

Removes rows from a table

Question 49

Fill in the blank: The SQL CREATE TABLE statement creates a new table by specifying the table and _______.

Answer 49

column names

Question 50

What data type does INT represent in SQL?

Answer 50

Stores integer values

Question 51

What does VARCHAR store in SQL?

Answer 51

Textual values

Question 52

Fill in the blank: The logical design phase converts entities, relationships, and attributes into ______, keys, and columns.

Answer 52

tables

Question 53

What is depicted in an ER diagram?

Answer 53

Entities, relationships, and attributes

Question 54

True or False: The logical design phase is specific to a database system.

Answer 54

True

Question 55

What does data independence allow database designers to do?

Answer 55

Tune query performance without changing application programs

Question 56

What is an API in the context of database programming?

Answer 56

A library of procedures or classes linking a programming language to a database

Question 57

Fill in the blank: The physical design phase specifies how tables are organized on _______.

Answer 57

storage media

Question 58

What is MySQL?

Answer 58

A leading relational database system sponsored by Oracle

Question 59

What are the two editions of MySQL?

Answer 59

* MySQL Community (free edition) * MySQL Enterprise (paid edition)

Question 60

True or False: SQL is a general-purpose programming language.

Answer 60

False

Question 61

What is the main function of a cursor in database programming?

Answer 61

Extract individual rows of query results

Question 62

What does the CREATE INDEX statement do?

Answer 62

Adds indexes to a database for optimization

Question 63

Fill in the blank: An entity is a ______, place, activity, or thing.

Answer 63

person

Question 64

What does the term 'schema' refer to in database design?

Answer 64

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

Question 65

What is the significance of the solid bullet in a table diagram?

Answer 65

Indicates key columns

Question 66

What is the purpose of the analysis phase in database design?

Answer 66

Specifies database requirements without regard to a specific database system

Question 67

What does the DELETE statement do when the WHERE clause specifies an ID that does not exist?

Answer 67

Deletes no rows

Question 68

True or False: The physical design phase affects query results.

Answer 68

False

Question 69

What type of data does the DECIMAL data type store?

Answer 69

Fractional numeric values

Question 70

What is the first step in the database design process for large, complex databases?

Answer 70

Analysis phase

Question 71

What SQL command is used to create a new table?

Answer 71

CREATE TABLE

Question 72

What does the SELECT statement retrieve from the Account table?

Answer 72

Names of accounts with a balance larger than $3000

Question 73

What is the purpose of the WHERE clause in SQL statements?

Answer 73

Specifies conditions for data retrieval or manipulation

Question 74

What is the significance of the empty bullet in a table diagram?

Answer 74

Indicates columns that refer to keys

Question 75

True or False: The CREATE TABLE statement includes data types for each column.

Answer 75

True

Question 76

What is MySQL Server Community?

Answer 76

A free edition suitable for non-commercial applications such as education.

Question 77

What is MySQL Enterprise?

Answer 77

A paid edition for managing commercial databases that includes MySQL Server and additional administrative applications.

Question 78

Which version of MySQL Server is this book based on?

Answer 78

MySQL Server release 8.0.

Question 79

What is the title of the MySQL documentation page?

Answer 79

MySQL Documentation.

Question 80

List the six documentation categories in MySQL documentation.

Answer 80

* General * Administrator Guide * Developers and Functionality * HA/Scalability * Connectors and APIs * MySQL Enterprise

Question 81

What are the subcategories of the General category?

Answer 81

* Getting started with MySQL * Tutorial * Server administration * SQL syntax * InnoDB storage engine * Alternative storage engines * Server option/variable reference * MySQL release notes * MySQL version reference * FAQs

Question 82

What does the Administrator Guides category include?

Answer 82

* Installation and upgrades * MySQL server-tool compatibility * MySQL yum repository * Quick guides for MySQL yum, APT, and SLES repository * Installation using unbreakable Linux network (ULN) * MySQL installer * Security * Secure deployment guide * Startup/shutdown * Backup and recovery overview * Platform guides * Building from source * MySQL port reference

Question 83

What subcategories are included in the Developers and Functionality category?

Answer 83

* MySQL workbench * MySQL as a document store * Globalization * Optimization * Functions and operators * Views and stored programs * Partitioning * Precision math * Information scheme * Performance scheme * Spatial extensions * Restrictions and limitations

Question 84

What are the subcategories in the HA/Scalability category?

Answer 84

* MySQL InnoDB cluster * MySQL NDB Cluster 8.0 (GA) * MySQL NDB Cluster 7.5/7/6 (GA) * MySQL NDB Cluster 7.3/7/4 (GA) * Memcached * Memcached with NDB cluster * Memcached with InnoDB * Replication * Semisynchronous replication

Question 85

What is included in the Connectors and APIs category?

Answer 85

* Connectors and APIs * Connector/J * Connector/ODBC * Connector/NET * Connector/Python * PHP * C API * Connector/C++ * MySQL for Excel * MySQL Notifier * MySQL for Visual Studio * NDB Cluster API developer guide

Question 86

What subcategories does MySQL Enterprise include?

Answer 86

* MySQL Enterprise Edition * MySQL Enterprise Monitor * Oracle Enterprise Manager for MySQL Database * MySQL Enterprise Backup * MySQL Enterprise Security * Secure Deployment Guide * MySQL Enterprise Encryption * MySQL Enterprise Audit * MySQL Enterprise Firewall * MySQL Thread Pool

Question 87

What must a user enter when installing MySQL Server?

Answer 87

A password for the root account.

Question 88

How does MySQL Server run after installation?

Answer 88

As a service in the background.

Question 89

What command starts the MySQL Command-Line Client?

Answer 89

mysql -u root -p

Question 90

What does the MySQL Command-Line Client allow developers to do?

Answer 90

Connect to the database server, perform administrative functions, and execute SQL statements.

Question 91

What happens when an SQL statement is syntactically incorrect?

Answer 91

MySQL Server returns an error code and description.

Question 92

True or False: The root account password is set when installing MySQL.

Answer 92

True

Question 93

True or False: The database server must be manually started each time the user runs the MySQL Command-Line Client.

Answer 93

False

Question 94

What allows some developers to interact with MySQL Server via a graphical interface?

Answer 94

MySQL Workbench.

Question 95

What does the SQL query panel in MySQL Workbench do?

Answer 95

Where the user enters SQL statements.

Question 96

True or False: MySQL Workbench and MySQL Command-Line Client both allow the user to type SQL statements.

Answer 96

True

Question 97

True or False: The SQL statements in the SQL query panel are executed until the lightning bolt is clicked.

Answer 97

True

Question 98

What databases are shown in the MySQL Workbench Navigator sidebar?

Answer 98

The company, sys, and world databases.