D426 Study Sheet Summary
The D426 OA is ALL multiple choice and heavily definition based. Understanding the Zybooks material
will help set you up for the D427 course which will require you to write SQL code from memory. In
other words, spending a bit more time on this course will pay dividends in the next course!
A database application is software that helps business users interact with database systems.
Database Roles
People interact with databases in a variety of roles:
• 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.
• Authorization. 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. Database systems ensure data is consistent with structural and business rules. Ex: When
multiple copies of data are stored in different locations, copies must be synchronized as data is
updated. Ex: When a course number appears in a student registration record, the course must exist
in the course catalog.
• The query processor interprets queries, creates a plan to modify the database or retrieve data, and
returns query results to the application. The query processor performs query optimization to
ensure the most efficient instructions are executed on the data.
• The storage manager translates the query processor instructions into low-level file-system
commands that modify or retrieve data. Database sizes range from megabytes to many terabytes,
so the storage manager uses indexes to quickly locate data.
• The 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. DML CODE – Data
Manipulation Language (ROWS of data) - DUIS
• DELETE deletes rows from a table.
• UPDATE modifies rows of data in a table.
• INSERT inserts rows into a table.
• SELECT retrieves data from a table.
The SQL CREATE TABLE statement creates a new table by specifying the table and column names. Each
column is assigned a data type that indicates the format of column values. Data types can be numeric,
textual, or complex. Ex:
• INT stores integer values.
• DECIMAL stores fractional numeric values.
• VARCHAR stores textual values.
• CHAR stores textual values.
• DATE stores year, month, and day.
The analysis phase specifies database requirements without regard to a specific database system.
Requirements are represented as entities, relationships, and attributes. An entity is a person, place,
,activity, or thing. A relationship is a link between entities, and an attribute is a descriptive property of an
entity.
Analysis has many alternative names, such as conceptual design, entity-relationship modeling, and
requirements definition.
Logical design
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.
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. The principle that
physical design never affects query results is called data independence.
To simplify the use of SQL with a general-purpose language, database programs typically use an application
programming interface. An application programming interface, or API
MySQL Command-Line Client
The MySQL Command-Line Client is a text interface included in the MySQL Server download. MySQL
Server returns an error code and description when an SQL statement is syntactically incorrect or the
database cannot execute the statement.
A tuple is an ordered collection of elements enclosed in parentheses. Ex: (a, b, c) and (c, b, a) are different,
since tuples are ordered.
The data structure organizes data in tables:
• A table has a name, a fixed tuple of columns, and a varying set of rows.
• A column has a name and a data type.
• A row is an unnamed tuple of values. Each value corresponds to a column and belongs to the
column's data type.
• A data type is a named set of values, from which column values are drawn.
Since a table is a set of rows, the rows have no inherent order.
• Synonyms:
• Table, File, Relation
• Row, Record, Tuple
• Column, Field, Attribute
All data in a relational database is structured in tables:
• A table has a name, a fixed sequence of columns, and a varying set of rows.
• A column has a name and a data type.
• A row is an unnamed sequence of values. Each value corresponds to a column and belongs to the
column's data type.
• A cell is a single column of a single row.
A table must have at least one column but any number of rows. A table without rows is called an empty
table.
, Rule 7 is called data independence. Data independence allows database administrators to improve query
performance by changing the organization of data on storage devices, without affecting query results.
The CREATE TABLE statement creates a new table by specifying the table name, column names, and column
data types. Example data types are:
• INT or INTEGER — integer values
• VARCHAR(N) — values with 0 to N characters
• DATE — date values
• DECIMAL(M, D) — numeric values with M digits, of which D digits follow the decimal point
The DROP TABLE statement deletes a table, along with all the table's rows, from a database.
The DROP VIEW statement deletes a virtual view, from a database.
ALTER TABLE statement
The ALTER TABLE statement adds, deletes, change, or modifies COLUMNS on an existing table.
Business rules are based on business policy and specific to a particular database
Type Description Examples
Explicit values that are string,
numeric, or binary.
Strings must be surrounded 'String'
by single quotes or double "String" 123
Literals
quotes. x'0fa2'
Binary values are represented
with x'0' where the 0 is any hex
value.
Keywords Words with special meaning. SELECT, FROM, WHERE
Objects from the database like
Identifiers City, Name, Population
tables, columns, etc.
Statement intended only for
-- single line comment
humans and ignored by the
Comments /* multi-line
database when parsing an
Comment */
SQL statement.
The D426 OA is ALL multiple choice and heavily definition based. Understanding the Zybooks material
will help set you up for the D427 course which will require you to write SQL code from memory. In
other words, spending a bit more time on this course will pay dividends in the next course!
A database application is software that helps business users interact with database systems.
Database Roles
People interact with databases in a variety of roles:
• 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.
• Authorization. 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. Database systems ensure data is consistent with structural and business rules. Ex: When
multiple copies of data are stored in different locations, copies must be synchronized as data is
updated. Ex: When a course number appears in a student registration record, the course must exist
in the course catalog.
• The query processor interprets queries, creates a plan to modify the database or retrieve data, and
returns query results to the application. The query processor performs query optimization to
ensure the most efficient instructions are executed on the data.
• The storage manager translates the query processor instructions into low-level file-system
commands that modify or retrieve data. Database sizes range from megabytes to many terabytes,
so the storage manager uses indexes to quickly locate data.
• The 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. DML CODE – Data
Manipulation Language (ROWS of data) - DUIS
• DELETE deletes rows from a table.
• UPDATE modifies rows of data in a table.
• INSERT inserts rows into a table.
• SELECT retrieves data from a table.
The SQL CREATE TABLE statement creates a new table by specifying the table and column names. Each
column is assigned a data type that indicates the format of column values. Data types can be numeric,
textual, or complex. Ex:
• INT stores integer values.
• DECIMAL stores fractional numeric values.
• VARCHAR stores textual values.
• CHAR stores textual values.
• DATE stores year, month, and day.
The analysis phase specifies database requirements without regard to a specific database system.
Requirements are represented as entities, relationships, and attributes. An entity is a person, place,
,activity, or thing. A relationship is a link between entities, and an attribute is a descriptive property of an
entity.
Analysis has many alternative names, such as conceptual design, entity-relationship modeling, and
requirements definition.
Logical design
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.
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. The principle that
physical design never affects query results is called data independence.
To simplify the use of SQL with a general-purpose language, database programs typically use an application
programming interface. An application programming interface, or API
MySQL Command-Line Client
The MySQL Command-Line Client is a text interface included in the MySQL Server download. MySQL
Server returns an error code and description when an SQL statement is syntactically incorrect or the
database cannot execute the statement.
A tuple is an ordered collection of elements enclosed in parentheses. Ex: (a, b, c) and (c, b, a) are different,
since tuples are ordered.
The data structure organizes data in tables:
• A table has a name, a fixed tuple of columns, and a varying set of rows.
• A column has a name and a data type.
• A row is an unnamed tuple of values. Each value corresponds to a column and belongs to the
column's data type.
• A data type is a named set of values, from which column values are drawn.
Since a table is a set of rows, the rows have no inherent order.
• Synonyms:
• Table, File, Relation
• Row, Record, Tuple
• Column, Field, Attribute
All data in a relational database is structured in tables:
• A table has a name, a fixed sequence of columns, and a varying set of rows.
• A column has a name and a data type.
• A row is an unnamed sequence of values. Each value corresponds to a column and belongs to the
column's data type.
• A cell is a single column of a single row.
A table must have at least one column but any number of rows. A table without rows is called an empty
table.
, Rule 7 is called data independence. Data independence allows database administrators to improve query
performance by changing the organization of data on storage devices, without affecting query results.
The CREATE TABLE statement creates a new table by specifying the table name, column names, and column
data types. Example data types are:
• INT or INTEGER — integer values
• VARCHAR(N) — values with 0 to N characters
• DATE — date values
• DECIMAL(M, D) — numeric values with M digits, of which D digits follow the decimal point
The DROP TABLE statement deletes a table, along with all the table's rows, from a database.
The DROP VIEW statement deletes a virtual view, from a database.
ALTER TABLE statement
The ALTER TABLE statement adds, deletes, change, or modifies COLUMNS on an existing table.
Business rules are based on business policy and specific to a particular database
Type Description Examples
Explicit values that are string,
numeric, or binary.
Strings must be surrounded 'String'
by single quotes or double "String" 123
Literals
quotes. x'0fa2'
Binary values are represented
with x'0' where the 0 is any hex
value.
Keywords Words with special meaning. SELECT, FROM, WHERE
Objects from the database like
Identifiers City, Name, Population
tables, columns, etc.
Statement intended only for
-- single line comment
humans and ignored by the
Comments /* multi-line
database when parsing an
Comment */
SQL statement.
