1. Introduction to Databases Flashcards
What is data?
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.
Name the important ways data can vary.
- 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.
What is the historical difference between analog and digital data?
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.
What is a database?
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.
What is a database management system (DBMS)?
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.
What is a query?
A query is a request to retrieve or change data in a database.
Query languages are specialized for efficient interaction with databases.
What does a database application do?
A database application helps business users interact with database systems.
It simplifies user experience and ensures efficient and secure data access.
Fill in the blank: A _______ is a request to retrieve or change data in a database.
query
What roles interact with databases?
- Database Administrator
- Database Designer
- Database Programmer
- Database User
Each role has specific responsibilities related to database management and use.
What is the responsibility of a database administrator?
A database administrator secures the database system against unauthorized users and manages user access.
They enforce procedures for database availability.
What does a database designer do?
A database designer determines the format of each data element and the overall database structure.
This role balances priorities like storage and response time.
What is the difference between a database programmer and a database user?
A database programmer develops applications using database query languages, while a database user consumes data from the database.
Users typically interact through applications.
What are the limitations of file systems compared to database systems?
- Performance
- Authorization
- Security
- Rules
- Recovery
File systems cannot adequately manage large, complex databases with many users.
What is a transaction in the context of databases?
A transaction is a group of queries that must be either completed or rejected as a whole.
This ensures data consistency and correctness.
True or False: A transaction can be partially completed.
False
All queries in a transaction must succeed or fail together to maintain data integrity.
What is the role of a database user?
A database user requests, updates, or generates reports from stored data.
Users can access databases via applications or directly submit queries.
Fill in the blank: A _______ is responsible for defining the detailed database design.
database designer
What does a database system ensure regarding data?
Database systems ensure data is consistent with structural and business rules.
This includes synchronizing multiple copies of data across locations.
What is a transaction in database systems?
A group of queries that must be either completed or rejected as a whole.
What happens if some queries in a transaction succeed while others fail?
It results in inconsistent or incorrect data.
What must database systems ensure when processing transactions?
- Transactions are processed completely or not at all
- Prevent conflicts between concurrent transactions
- Ensure transaction results are never lost
True or False: A transaction must process either both queries or neither query.
True
What is the role of the transaction manager in a database system?
Ensures transactions are properly executed and restores the database to a consistent state in case of failure.
What is the purpose of the log in a database system?
Contains a complete record of all inserts, updates, and deletes processed by the database.
What is the catalog in a database system?
A directory of tables, columns, indexes, and other database objects.
Fill in the blank: The SQL stands for ______.
Structured Query Language
What are the four common types of database queries?
- Insert
- Select
- Update
- Delete
What does a select query do in a database?
Retrieves information from the database.
True or False: An update query can update data that isn’t in the database.
False
What is a key characteristic of relational databases?
Stores data in tables, columns, and rows.
What is a distinguishing feature of NoSQL databases?
Optimized for big data generated on the internet.
What does CRUD stand for?
Create, Read, Update, Delete
True or False: All relational database systems support SQL.
True
What are the key components of a database system architecture?
- Query Processor
- Storage Manager
- Transaction Manager
- Log
- Catalog
What is the function of the storage manager in a database system?
Translates query processor instructions into low-level file-system commands.
Fill in the blank: The term NoSQL originally meant ‘______’.
does not support SQL
What type of database system has become popular since 2000?
Open source database systems
What happens to transactions in the event of a system failure?
The transaction manager and log are necessary to restore the database.
True or False: SQL was first developed at Microsoft.
False
What does the acronym ANSI stand for in relation to SQL?
American National Standards Institute
What is the primary purpose of an INSERT statement in SQL?
Inserts rows into a table.
What does a DELETE statement do in SQL?
Deletes rows from a table.
What is the preferred pronunciation of SQL?
‘S-Q-L’
What does the Account table in a bank database contain?
Three columns: ID, Name, and Balance
What does the INSERT statement do in SQL?
Adds a new row to a table
What is the purpose of the SELECT statement?
Retrieves data from a database
What is the effect of the following UPDATE statement: UPDATE Account SET Balance = 4500 WHERE ID = 831?
Changes Raul’s balance from 3300 to 4500
What does the DELETE statement do in SQL?
Removes rows from a table
Fill in the blank: The SQL CREATE TABLE statement creates a new table by specifying the table and _______.
column names
What data type does INT represent in SQL?
Stores integer values
What does VARCHAR store in SQL?
Textual values
Fill in the blank: The logical design phase converts entities, relationships, and attributes into ______, keys, and columns.
tables
What is depicted in an ER diagram?
Entities, relationships, and attributes
True or False: The logical design phase is specific to a database system.
True
What does data independence allow database designers to do?
Tune query performance without changing application programs
What is an API in the context of database programming?
A library of procedures or classes linking a programming language to a database
Fill in the blank: The physical design phase specifies how tables are organized on _______.
storage media
What is MySQL?
A leading relational database system sponsored by Oracle
What are the two editions of MySQL?
- MySQL Community (free edition)
- MySQL Enterprise (paid edition)
True or False: SQL is a general-purpose programming language.
False
What is the main function of a cursor in database programming?
Extract individual rows of query results
What does the CREATE INDEX statement do?
Adds indexes to a database for optimization
Fill in the blank: An entity is a ______, place, activity, or thing.
person
What does the term ‘schema’ refer to in database design?
The logical design specified in SQL and depicted in a table diagram
What is the significance of the solid bullet in a table diagram?
Indicates key columns
What is the purpose of the analysis phase in database design?
Specifies database requirements without regard to a specific database system
What does the DELETE statement do when the WHERE clause specifies an ID that does not exist?
Deletes no rows
True or False: The physical design phase affects query results.
False
What type of data does the DECIMAL data type store?
Fractional numeric values
What is the first step in the database design process for large, complex databases?
Analysis phase
What SQL command is used to create a new table?
CREATE TABLE
What does the SELECT statement retrieve from the Account table?
Names of accounts with a balance larger than $3000
What is the purpose of the WHERE clause in SQL statements?
Specifies conditions for data retrieval or manipulation
What is the significance of the empty bullet in a table diagram?
Indicates columns that refer to keys
True or False: The CREATE TABLE statement includes data types for each column.
True
What is MySQL Server Community?
A free edition suitable for non-commercial applications such as education.
What is MySQL Enterprise?
A paid edition for managing commercial databases that includes MySQL Server and additional administrative applications.
Which version of MySQL Server is this book based on?
MySQL Server release 8.0.
What is the title of the MySQL documentation page?
MySQL Documentation.
List the six documentation categories in MySQL documentation.
- General
- Administrator Guide
- Developers and Functionality
- HA/Scalability
- Connectors and APIs
- MySQL Enterprise
What are the subcategories of the General category?
- 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
What does the Administrator Guides category include?
- 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
What subcategories are included in the Developers and Functionality category?
- 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
What are the subcategories in the HA/Scalability category?
- 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
What is included in the Connectors and APIs category?
- 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
What subcategories does MySQL Enterprise include?
- 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
What must a user enter when installing MySQL Server?
A password for the root account.
How does MySQL Server run after installation?
As a service in the background.
What command starts the MySQL Command-Line Client?
mysql -u root -p
What does the MySQL Command-Line Client allow developers to do?
Connect to the database server, perform administrative functions, and execute SQL statements.
What happens when an SQL statement is syntactically incorrect?
MySQL Server returns an error code and description.
True or False: The root account password is set when installing MySQL.
True
True or False: The database server must be manually started each time the user runs the MySQL Command-Line Client.
False
What allows some developers to interact with MySQL Server via a graphical interface?
MySQL Workbench.
What does the SQL query panel in MySQL Workbench do?
Where the user enters SQL statements.
True or False: MySQL Workbench and MySQL Command-Line Client both allow the user to type SQL statements.
True
True or False: The SQL statements in the SQL query panel are executed until the lightning bolt is clicked.
True
What databases are shown in the MySQL Workbench Navigator sidebar?
The company, sys, and world databases.
