WGU IT D426 Data Management Foundations | Study Guide, OA Exam Prep, SQL, Databases & Normalization

D426 Database Management Foundations Terms Study Guide
1.1 Database basics
 database application
o A database application is software that helps business users
interact with database systems.
 database administrator
o A database administrator is responsible for securing the
database system against unauthorized users. A database
administrator enforces procedures for user access and database
system availability.
1.2 Database systems
 Authorization
o . Many database users should have limited access to specific
tables, columns, or rows of a database. Database systems
authorize individual users to access specific data.
 Rules
o Database systems
ensure data is consistent with structural and business rules.
 query processor
o The query processor interprets queries, creates a plan to
modify the database or retrieve data, and returns query results
to the application.
 query optimization
o The query processor performs query optimization to ensure the
most efficient instructions are executed on the data.
 storage manager
o The storage manager translates the query processor
instructions into low-level file-system commands that modify
or retrieve data.
 indexes
o The storage manager uses indexes to quickly locate data.
 transaction manager
o The transaction manager ensures transactions are properly
executed.
 Metadata
o Metadata is data about the database, such as column
names and the number of rows in each table.
 relational database
o A relational database stores data in tables, columns, and
rows, similar to a spreadsheet.

, o All relational database systems support the SQL query
language.
 relational databases are good for what?
o Relational systems are ideal for databases that require
an accurate record of every transaction, such as banking,
airline reservation systems, and student records.
 SQL
o SQL stands for Structured Query Language and includes
statements that read and write data, create and delete tables,
and administer the database system.
o All relational database systems support the SQL query
language.
 big data
o The growth of the internet in the 1990s generated massive
volumes of online data, called big data, often with poorly
structured or missing information.
o MongoDB: big data, open source, noSQL.
 NoSQL
o The newer non-relational systems are called NoSQL, for 'not
only SQL', and are optimized for big data.
o MongoDB: big data, open source, noSQL
1.3 Query languages
 INSERT
o INSERT inserts rows into a table.
INSERT INTO table_name (column_name1, column_name2, ...)
VALUES (DEFAULT, 'bob', 30, 150);
 SELECT
o SELECT retrieves data from a table.
SELECT column1, column2, ...
FROM table_name; -- return all data from those columns.
 UPDATE
o UPDATE modifies data in a table.
UPDATE table_name
SET column_name = 2
WHERE column_id = 3;
 DELETE
o DELETE deletes rows from a table.
DELETE FROM table_name; -- all rows deleted!
DELETE FROM table_name WHERE column_name = 'value'; -- delete row
 CREATE TABLE

, o The SQL CREATE TABLE statement creates a new table by
specifying the table and column names.
CREATE TABLE Customers (
customerId INT NOT NULL UNIQUE,
first_name VARCHAR(255) NOT NULL,
age INT CHECK(age > 18)
driverid INT NOT NULL,
PRIMARY KEY (customerId),
FOREIGN KEY (LOCAL_COLUMN_NAME) REFERENCES TABLE_NAME
(COLUMN_NAME),
);
1.4 Database design and programming
 database design: analysis
o The analysis phase specifies database requirements without
regard to a specific database system.
o Requirements are represented as entities, relationships, and
attributes.
o Sometimes called: Conceptual Design
 ER diagrams
o Entities, relationships, and attributes are depicted in ER
diagrams.
o




o




 database design: logical design

, o The logical design phase implements database requirements in
a specific database system.
o For relational database systems, logical design converts entities,
relationships, and attributes into tables, keys, and columns.
o The logical design, as specified in SQL and depicted in a table
diagram, is called a database schema.
o




 key
o A key is a column used to identify individual rows of a table.
o Tables, keys, and columns are specified in SQL with CREATE
TABLE statements.
 database design: physical design
o The physical design phase adds indexes and specifies how tables
are organized on storage media.
o Physical design affects query processing speed but never
affects the query result.
 data independence
o The principle that physical design never affects query results is
called data independence.
o Physical design affects query processing speed but never
affects the query result.
o When database designers modify indexes or row
order, applications run faster or slower but always generate
the same results.
 application programming interface / API
o simplify the use of SQL with a general-purpose language.
o An application programming interface, or API, is a library of
procedures or classes that links a host programming
language to a database.
1.5 MySQL
 MySQL Command-Line Client
o The MySQL Command-Line Client is a text interface included
in the MySQL Server download.