Chapter 1: Intro to Databases Flashcards
Data
Numeric, textual, visual, or audio information that describes real-world systems. Can vary in scope, format, and access.
Analog
Encodings as continuous variations on various physical media. Historically, data was mostly this.
Digital
Encodings as ones and zeros on electronic and magnetic media. Most data is this today.
Database
A collection of data in a structured format.
Database Management System (DBMS)
Software that reads and writes data in a database. Ensures data is secure, internally consistent, and available at all times.
Query
A request to retrieve or change data in a database.
Query Language
A specialized programming language, designed specifically for database systems. Differ from general-purpose languages.
Database application
Software that helps business users interact with database systems.
Database administrator
Responsible for securing the database system against unauthorized users. Enforces procedures for user access and database system availability.
Database designer
Determines the format of each data element and the overall database structure. Balances storage, response time, and support for the rules governing the data.
Database programmer
Develops computer programs that utilize a database. Write the applications that combine database query languages and general-purpose programming languages.
Database user
A consumer of data in a database.
Special requirements of a large database
Performance, authorization, security, rules, and recovery
Transaction
A group of queries that must either be completed or rejected as a whole.
When processing transactions, database systems must:
- Ensure transactions are processed completely or not at all.
- Prevent conflicts between concurrent transactions.
- Ensure transaction results are never lost.
Architecture
Describes the internal components and the relationships between components.
Query processor
Interprets queries, creates a plan to modify the database or retrieve data, and returns query results. Performs query optimization.
Query optimization
Performed by the query processor to ensure the most efficient instructions are executed on the data.
Storage manager
Translates the query processor instructions into low-level file-system commands that modify or retrieve data. Uses indexes to quickly locate data
Transaction manager
Ensures transactions are properly executed.
Log
A file containing a complete record of all inserts, updates, and deletes processed by the database. The transaction manager writes log records before applying changes to the database.
Catalog/Data Dictionary
A directory of tables, columns, indexes, and other database objects
Relational database
Stores data in tables, columns, and rows, similar to a spreadsheet. All data in a column has the same format, all data in a row represents a single object.
SQL
All relational database systems support this query language. Stands for Structured Query Language.
NoSQL
Stands for Not Only SQL. Non-relational database
CRUD
Create, Read, Update, and Delete data
CREATE TABLE statement
Creates a new table by specifying the table and column names and data types
Database Design
A specification of database objects such as tables, columns, data types, and indexes. Has three phases:
1. Analysis
2. Logical Design
3. Physical design
Analysis phase
Specifies database requirements without regard to a specific database system. Requirements are represented as entities, relationships, and attributes
ER Diagram
Depicts entities, relationships, and attributes, where rectangles represent entities, lines represent relationships, and text represents attributes
Logical design phase
Implements database requirements in a specific database system. Converts entities, relationships, and attributes into tables, keys, and columns. Depicted in a table diagram
Key
A column used to identify individual rows of a table
Table Diagram
Depicts logical design, where rectangles represent tables, text represents table names and columns, solid bullets represent keys, and empty bullets represent foreign keys.
Schema
The logical design, as specified in SQL and depicted in a table diagram.
Physical Design
Adds indexes and specifies how tables are organized on storage media.
Data Independence
Physical design never affects query results. Allows designers to tune query performance without changes to application programs.
Application Programming Interface (API0
A library of procedures or classes that links a host programming language to a database.
MySQL
Leading relational database system. Has the MySQL Server and MySQL Enterprise editons.
Root account
Administrative account that has full control of MySQL
MySQL Command-Line Client
Text interface included in the MySQL Server download. Allows developers to connect to the database server, perform administrative functions, and execute SQL statements. Similar to MySQL Workbench, but is not a GUI.
