D426: Data Management Foundations 1.3

CRUD operations

Create, Read, Update, and Delete; the four most common database queries

insert/create

a query that adds completely new information to a database

select/read

a query that retrieves information from a database

update

a query that changes existing data in a database

delete

a query that removes data from a database

CREATE TABLE()

SQL statement where table name, column names, and column data types are specified. Table name is specified before parentheses. Column name and data type are separated by a space while commas separate the info for each column.

VARCHAR()

data type for storing textual values; accepts argument for character limits

DECIMAL()

SQL equivalent of a float; accepts arguments for # of significant digits, # of digits after the decimal

database design

a specification of database objects such as tables, columns, data types, and indexes. Also refers to the process used to develop the specification.

large database design process

1. Analysis

2. Logical design

3. Physical design

Analysis/conceptual design/entity-relationship modeling/requirements definition

the database design phase that specifies requirements without regard to a specific database system. Requirements are represented as entities, relationships, and attributes.

ER diagrams

diagrams that depict entities, relationships and attributes of a database.

• entities = rectangles with names at the top

• relationships = lines between relationships with action/function names above them

• attributes = text inside rectangles below the entity names

logical design

database design phase that implements database requirements in a specific database system. i.e. for relational database systems, this phase converts entities, relationships, and attributes into tables, keys, and columns

key

a column used to identify individual rows of a table (i.e. the index)

table diagram

a more detailed version of an ER diagram used in the logical design phase

• tables = rectangles w/ table names at top

• columns = text within the rectangles

• key = marked with a solid bullet

• empty bullets and arrows indicate columns that refer to keys

database schema

specifies the result of logical design and is depicted in a table diagram

physical design

database design phase that adds indexes and specifies how tables are organized on storage media

data independence

the principle that physical design affects query processing speed but never the query result, which is affected by logical design. This allows database designers to tune query performance without changes to application programs.

application programming interface (API)

a library of procedures or classes that links a host programming language to a database. The host language calls library procedures, which handle details such as connecting to the database, executing queries, and returning results.

cursor objects

objects in APIs that help extract query results by bridging the gap between SQL, which generates multiple rows in one statement, and host languages, which generally process one row at a time in a loop

MySQL

a leading relational database system sponsored by Oracle, which runs on all major operating systems

root account

the administrative account that has full control of MySQL