advanced database traditional 1

In a relational database, data is stored in one or more

Tables

The columns of a relational database table are referred to as

Fields

The rows of a relational database table are referred to as

Records

The column that uniquely identifies each row in the table is called

Primary key

A primary key that consists of two or more columns is called a

Composite primary key

An is automatically created for each key you define.

Index

Like a key, an index can include one or more

Columns

When one row in a table is related to one or more rows in another table, this is called a

One-to-many relationship

Typical relationships exist between the primary key in one table and the

Foreign key in another table.

One-to-one relationships are frequently used.

True

Which type of relationship is the most common?

One-to-many relationship

If two tables have a one-to-one relationship, the data in the two tables could really be stored in a single table.

True

A many-to-many relationship is usually implemented by using a(n)

Intermediate table.

A many-to-many relationship can usually be broken down into two one-to-many relationships.

True

To define a column in a table, you assign what are called

Properties.

The most critical column property is its

Data type.

When choosing a data type, try to assign the type that minimizes disk storage use in order to improve performance of the

Queries.

Null represents a value that is

Not applicable, unknown, unavailable.

The NN column property represents

Not Null.

If you don't allow null values for a column, you must provide a value for that column.

True

A is a value assigned to a column when another value isn't provided.

Default value.

An auto column is a numeric column whose value is generated automatically when a row is added to the table.

Increment.

Two types of diagrams: ER diagram and an

Enhanced entity-relationship (EER) diagram.

The EER diagram uses the icon to show a primary key column.

Key.

The vendor_state column is assigned what data type?

CHAR.

The table begins with two primary key columns.

Invoicelineitems.

The OneSymbol.png symbol refers to the part of a one-to-many relationship.

One.

The Crow’s Feet symbol refers to the part of a one-to-many relationship.

Many.

SQL programmers typically work with statements.

DML.

The SELECT clause names the

Columns to be retrieved.

The FROM clause names the

Tables where columns come from.

A calculated column only exists in the results of a query.

True.

In the SELECT statement, the WHERE clause gives criteria for which are selected.

Rows.

In a SELECT statement, the clause returns sorted rows.

ORDER BY.

The SELECT statement returns what is called a result set, also called the result table.

Temporarily.

The SELECT statement is commonly referred to as a

Query.

When you retrieve data from two tables, this type of operation is called a

Join.

This is the most common type of join where the rows from the tables are only included in the result set if their related columns match.

INNER join.

Use the clause on the SELECT statement to retrieve data from two tables.

FROM.

An INSERT statement adds a to a table, as demonstrated in Figure 1-14.

Row.

What statement is used to change the data in one or more rows of a table?

UPDATE.

Use the statement to delete rows from a table.

DELETE.

Because inserting, updating, and deleting causes permanent changes to a database, the textbook recommends that you do not perform any of these commands until you understand the effect that they can have on the database.

True.

SQL is a freeform language. That means that you can include without affecting the way the database interprets the code.

Spaces, indentation, line breaks.

SQL is case-sensitive.

False.

Coding recommendations include: Capitalize keywords, Start each clause on a new line, Separate words with underscores, Break long clauses into multiple lines and indent continued lines.

True.

Use /* and */ to code a comment.

Block.

Use -- to code a comment.

Single-line.

Before you can work with a database, you need to connect to the database

Server.

If a password appears when making a connection, an option can be checked to save the password in a.

Vault.

To clear the password from the vault, right-click the connection, select Edit Connection, and click Clear.

True.

To connect to a MySQL server on another computer, right-click the connection and select Edit Connections.

True.

To add a new connection, click the icon to the right of MySQL Connections.

Plus.

The database server is also referred to as the database

Engine.

After you connect to a database server, use the category of the Navigator window to navigate objects.

SCHEMAS.

Database objects include:

Tables, functions, stored procedures, views.

To work with a node or object, right-click to display a .

Context-sensitive menu.

Double-click the node for a database to select it and the objects it contains.

View.

To navigate through objects, click the to the left of each node.

Arrows.

To view table data, right-click the table name and select 'Select Rows – Limit 1000.'

True.

The table data is displayed in the .

Result Grid.

To insert, edit, and delete rows, use the buttons at the .

Top of the Result Grid.

To apply changes, click Apply at the .

Bottom of the Result Grid.

Use the button to cancel changes.

Revert.

Column information includes .

The column’s data type, the column name.

Use the tab to add, delete, or modify a column.

Columns.

Add a new column by .

Entering it at the bottom.

Delete a column by right-clicking on the column and selecting Delete.

True.

Change the name of a column by selecting the column and editing it.

True.

Later you’ll learn how to use to edit column definitions.

Scripts.

To view column definitions, right-click the table name and select .

Alter.

A SQL tab automatically opens when you connect to a server.

True.

Keywords in the editor are displayed in .

Blue.

New SQL tab button:

B.

SQL editor area:

D.

Execute current SQL statement:

C.

Result grid area:

E.

Window to select a database:

A.

If an error occurs, Workbench displays:

A brief description, the error number.

Errors can be caused by:

Misspelling a keyword, Selecting the wrong database, Misspelling a table/column name, Omitting a closing quote.

Error messages appear in the tab.

Output.

In MySQL, is a file containing SQL statements.

Script.

Script files use the extension.

.sql.

To switch between open scripts, select the appropriate tab.

True.

The Open SQL Script File button is in the part of Workbench.

Upper left.

A SQL script usually contains .

Multiple SQL statements.

End each statement with a .

; (semicolon).

To execute one statement, move the insertion point into it and click Execute Current Statement.

True.

To execute all statements, press Ctrl+Shift+Enter or click .

Execute SQL Script.

You can also execute selected statements only.

True.

It’s important to be aware of the MySQL Command Line Client.

True.

The MySQL Command Line Client prompt looks like:

mysql>.

To stop the Command Line Client, enter exit or quit followed by a .

; (semicolon).

If you enter the show databases statement into the MySQL Command Line and this appears on the screen: mysql> show databases -> This means: you forgot to type a semicolon at the end of the show databases statement.

True.

To list the names of the databases stored on a server, use the statement.

show databases.

After displaying the database names, you can select a database to use. What full SQL statement would you type to select the database named ex?

use ex;.

SQL statements are not case sensitive.

False.

A scroll bar indicates columns than displayed.

More.

Without ORDER BY, rows appear in primary key sequence.

True.

The symbol means retrieve all columns.

True.