Query Processor - Interprets queries. Compiles queries into low-level instructions for the
storage engine. Creates a plan to modify the database or retrieve data, and returns
query results to the application.
Query optimization - The query processor performs query optimization to ensure the
most efficient instructions are executed on the data.
Storage manager/engine - 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. The transaction
manager prevents conflicts between concurrent transactions. The transaction manager
also restores the database to a consistent state in the event of a transaction or system
failure.
CRUD - Create, Read, Update, Delete
Insert(create) inserts rows into a table
Select(read) retrieves data from a table
Update modifies data
Delete deletes rows
Analysis / Conceptual design - The analysis phase specifies database requirements
without regard to a specific database system.
Requirements are represented as entities, relationships, and attributes.
Logical desgin - The logical design phase implements database requirements in a
specific database system. For relational database systems, logical design converts
entities, relationships, and attributes into tables, keys, and columns.
Also called a schema
Physical design - The physical design phase adds indexes and specifies how tables are
organized on storage media. Physical design affects query processing speed but never
affects the query result.
Data independence - The principle that physical design never affects query results.
API (Application programming interface) - A library of procedures or classes that links a
host programming language to a database. Using one simplifies the use of SQL
MySQL Command-Line Client - a text interface included in the MySQL Server
download.
, Set - An unordered collection of elements enclosed in braces {}
Tuple - is an ordered collection of elements enclosed in parentheses ()
Business rules - Based on business policy and specific to a particular database.
Relational rules govern data in all relational databases
DDL - Data Definition Language (DDL) defines the structure of the database. Controls
tables. Includes CREATE, DROP, ALTER, TRUNCATE, RENAME
DQL - Data Query Language (DQL) retrieves data from the database. SELECT
DML - Data Manipulation Language (DML) manipulates data stored in a database.
Adds, modifies and deletes data. INSERT UPDATE DELETE
DCL - Data Control Language (DCL) controls database user access. Used to create
user accounts, manage permissions and access.
DTL - Data Transaction Language (DTL) manages database transactions. Can rollback
database changes
UPDATE - The UPDATE statement modifies existing rows in a table. The UPDATE
statement uses the SET clause to specify the new column values.
DELETE - The DELETE statement deletes existing rows in a table. The FROM keyword
is followed by the table name whose rows are to be deleted.
Primary key - A primary key is a column, or group of columns, used to identify a row.
The primary key is usually the table's first column. It is a simple primary key if it consists
of a single column
Composite primary key - A composite primary key consists of multiple columns.
Foreign key - A foreign key is a column, or group of columns, that refer to a primary key.
Referential integrity - Referential integrity requires foreign key values must either be
NULL or match some value of the referenced primary key.
LIKE - The LIKE operator, when used in a WHERE clause, matches text against a
pattern using the two wildcard characters % and _.
INNER JOIN - selects only matching left and right table rows.
FULL JOIN - selects all left and right table rows, regardless of match.
The benefits of buying summaries with Stuvia:
Guaranteed quality through customer reviews
Stuvia customers have reviewed more than 700,000 summaries. This how you know that you are buying the best documents.
Quick and easy check-out
You can quickly pay through credit card or Stuvia-credit for the summaries. There is no membership needed.
Focus on what matters
Your fellow students write the study notes themselves, which is why the documents are always reliable and up-to-date. This ensures you quickly get to the core!
Frequently asked questions
What do I get when I buy this document?
You get a PDF, available immediately after your purchase. The purchased document is accessible anytime, anywhere and indefinitely through your profile.
Satisfaction guarantee: how does it work?
Our satisfaction guarantee ensures that you always find a study document that suits you well. You fill out a form, and our customer service team takes care of the rest.
Who am I buying these notes from?
Stuvia is a marketplace, so you are not buying this document from us, but from seller VasilyKichigin. Stuvia facilitates payment to the seller.
Will I be stuck with a subscription?
No, you only buy these notes for $13.48. You're not tied to anything after your purchase.
