Database Programming Final Exam Review

Which option is found on the Schemas tab in MySQL Workbench?

A list of available databases.

The statement below is an example from which SQL sublanguage? SELECT ProductName FROM Product;

Data Query Language

What design consideration would apply to a database that has special performance requirements?

Ensuring data is consistent with structural rules

A database _____ is the implementation of database requirements in SQL with CREATE TABLE statements.

Entity

A database administrator creates a MySQL statement with incorrect syntax. What does MySQL Workbench display when the statement is executed?

Error code

Which statement is not a special case of foreign keys?

Foreign key referring to a foreign key in the same table

Which data type should a database use to store negative numbers?

INT

When using the MySQL Workbench GUI, which icon will execute an SQL statement?

Lighting bolt

The analysis phase of database design includes which process?

Specifying requirements that are not dependent on a specific database system.

In terms of database architecture, which component translates the query processor instructions into low-level file-system commands and is responsible for indexing the data?

Storage manager

What does SQL stand for?

Structured Query Language

Which two rules apply to primary keys?

Values must be unique and may not be NULL

The EmployeeWorkspace table has a composite key of (EmployeeID, WorkspaceID). WorkHours depends on (EmployeeID, WorkspaceID), and EmployeeLastName depends on EmployeeID. Which column must be removed so EmployeeWorkspace is in second normal form?

WorkspaceID

A pair of related values in a database is a(n) _____.

fact

Refer to the Teacher and Class tables. The TeacherID in the Class table references the TeacherID in the Teacher table. The TeacherID in the Class table is a/an _____.

foreign key

When data is produced and stored as numeric, textual, or visual information, it varies in _____.

format

UPDATE, SELECT, and ORDER BY are _____ in an SQL statement.

keywords

When only one value exists in each cell, a table is known to be _____.

normalized

What should be added to the SQL statements to produce the Result table below? USE _____; SHOW _____;

onlineShop, TABLES

A _____ is a type of UNIQUE constraint applied to two or more columns.

table-level constraint

Which type of database system is optimized for big data?

NoSQL

Which principle defines data independence?

Physical design never affects query results.

Which statement selects all rows and just the ProductName and Size columns from the Product table?

SELECT ProductName, Size FROM Product;

A database administrator creates a new relational database, and a primary key is assigned within a table to assist with governing data. What does the administrator accomplish with this structural rule?

Unique identification of individual rows in the table

Which of the following values violates the CHECK constraint below? CREATE TABLE Customer ( CustomerId INT AUTO_INCREMENT, Name VARCHAR(60), Age INT CHECK (Age BETWEEN 18 AND 50), ShippingAddress VARCHAR(200), PRIMARY KEY (CustomerId) );

(456, "Sarah Mcgraw", 61, "12301 270th Pl, Seattle, WA 98126")

Evaluate the given data and determine the correct result from the statement. SELECT SUM(cost) + SUM(markup) FROM profit;

485

How many columns are created by the SQL statement below? CREATE TABLE Supplier ( SupplierId INT NOT NULL AUTO_INCREMENT, CompanyName VARCHAR(40), ContactName VARCHAR(50), City VARCHAR(40), Country VARCHAR(40), Phone VARCHAR(30) );

6

When using the MySQL Command-Line Client, which character ends a command?

;

Which of the following is not true about the INSERT statement?

A single INSERT statement can only add one row.

In a relational database model, an attribute refers to which concept?

A uniquely named tuple position

Which SQL statement adds a new column Fax to the Supplier table?

ALTER TABLE Supplier ADD Fax VARCHAR(30);

In the following ER diagram, what does 'AlbumTitle' represent?

Attribute

What data type stores binary values?

BLOB

What is the correct statement for creating a database called reservationDB?

CREATE DATABASE reservationDB;

When using a SQL statement to create a table, which data type is used to store a fractional value?

DECIMAL

A _____ constraint is used to define a column value that is not NULL when no value is provided.

DEFAULT

What is the correct statement for deleting a database?

DROP DATABASE (database name);

What is the correct SQL statement for deleting the table Supplier?

DROP TABLE Supplier;

Which language defines statements used for creating and dropping tables?

Data Definition Language

What does a user that interacts with a database use to read and write data?

Database Management System

Which concept relates to software that reads and writes data in a database?

Management system

In MySQL, what is the result of TRUE AND NULL?

NULL

What foreign key action should be added to ensure that if a supplier is removed from the Supplier table, the products associated with the same supplier are also removed? CREATE TABLE Product ( ProductId INT NOT NULL AUTO_INCREMENT, ProductName VARCHAR(50), UnitPrice DECIMAL(5,2), SupplierId INT, PRIMARY KEY (ProductId), FOREIGN KEY (SupplierId) REFERENCES Supplier(SupplierId) _____ );

ON DELETE CASCADE

How does a database system protect data when concurrent transactions occur?

Preventing multiple transactions with the same data at the same time.

In the SQL code below, which of the following is an identifier? UPDATE Product SET UnitPrice = 9.50 WHERE ProductId = 20;

Product

Complete the ORDER BY clause to sort the Products by ProductName alphabetically, then in decreasing Quantity. SELECT ProductName, Size, Quantity, SupplierId FROM Product ORDER BY _____ ;

ProductName, Quantity DESC

Which role focuses on creating software that interacts with a database?

Programmer

When a user interacts with a database, they can use a _____ to modify data with commands.

Query Language

A database designer installs MySQL Community Edition to create a database. Which account does the designer use to gain full control of MySQL?

Root

The _____ SQL statement does not alter any database data.

SELECT

A database administrator uses which two SQL statements to view and then modify existing customer balances with a late fee?

SELECT, UPDATE

A _____ is a collection of values with no inherent order.

Set

A database designer wants to create three tables: Supplier, Product, and Country. The Supplier table has a CountryId column with values that must appear in the Country table's CountryId column. The Product table has an auto-increment column. Which table's CREATE TABLE statement(s) must specify a FOREIGN KEY?

Supplier

Which column is best to replace XXX in the SQL statement below? CREATE TABLE Supplier ( SupplierId INT NOT NULL AUTO_INCREMENT, CompanyName VARCHAR(40), ContactName VARCHAR(50), Phone VARCHAR(30), PRIMARY KEY (XXX) );

SupplierId

In MySQL, what is the result of TRUE OR NULL?

TRUE

What is the result of a relational operation?

Table

In a relational database model, which concept refers to a finite sequence of values that are derived from defined domains?

Tuple

A user creates a table by using a SQL statement. The data type VARCHAR(11) is part of the statement. What does the value of (11) represent?

The number of characters allowed for the data type.

A database system has a database called onlineShop. What is the result of a CREATE statement that tries to create onlineShop a second time?

The statement produces an error that the database already exists.

If a database system is processing three queries as part of a transaction and the third query fails, what happens to the transaction?

The successful query results are reversed, and the transaction is canceled.

What should be added so NULL values are not allowed in ProductName? CREATE TABLE Product ( ProductId INT AUTO_INCREMENT, ProductName _____, UnitPrice DECIMAL(5,2), SupplierId INT, PRIMARY KEY (ProductId) );

VARCHAR(50) NOT NULL

Choose the best data types to replace XXX and YYY. CREATE TABLE Product ( ProductId INT, ProductName XXX, UnitPrice YYY );

VARCHAR(50), DECIMAL(8, 2)

All _____ columns depend on the _____ for a table to be in first normal form.

non-key, primary key

A value that is used in a computation is known as a/an _____.

operand

Which SQL statement deletes the City column from the Supplier table?

ALTER TABLE Supplier DROP City;

What links a host programming language to a database system?

API

Which database role focuses on database storage, response times, and optimization?

Designer

A database management system reads and writes data in a database, and _____.

Ensures consistency and availability

A _____ is a collection of values that are of the same type.

column

A/An _____ is a rule enforced on a table's data.

constraint

A NULL value represents _____ data.

missing

A column, or group of columns, that serves as the unique identifier in a relational database table is called a/an _____.

primary key

Redundancy is possible in a _____ normal form table when one non-key column depends on another non-key column.

second

A relational database uses _____ to structure all data.

tables

When a table is in _____, all non-key columns depend on the _____ and no other columns.

third normal form, whole primary key