Practice Midterm COMP 440

A view table provides which benefit when created in a db with multiple tables?

A consolidated view of specific data without changing the underlying db structure

INSERT INTO Department (DepartmentName) VALUES ('Shipping');

A row ('Shipping') is inserted into the table

What data type stores binary values?

BLOB

Refer to the Teacher and Class tables. Which foreign key action updates the TeacherID for the Web Development course to 45672 when Bryan McNeal's TeacherID is updated to 45672?

CASCADE

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

CREATE DATABASE reservationDB;

A ____ constraint specifies a column value that is used when no value is provided in an INSERT statement

DEFAULT

Which language defines statements used for creating and dropping tables?

Data Definition Language

ShipPartCode is a foreign key in Shipment table. ShipPartCode refers to the primary key PartCode of the Part table. What replaces XXX in the following statement? CREATE TABLE Shipment ( ShipNumber INT UNSIGNED, ShipPartCode CHAR (3) NOT NULL, Quantity SMALLINT UNSIGNED, PRIMARY KEY (ShipNumber), XXX );

FOREIGN KEY (ShipPartCode) REFERENCES Part (PartCode)

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

INT

What foreign ket 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

Which principle defines data independence?

Physical design never affects query results

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

Query language

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

Root

A DBA uses which 2 sql statements to view and then modify existing customer balance with a late fee?

SELECT, UPDATE

Set VS Tuple

Set is unordered elements in brackets Tuple is ordered elements in parentheses

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

Structuring data properly on storage media.

What is wrong in this query? SELECT Sales.OrderId, Customer.FirstName, Customer.LastName FROM Sales INNER JOIN Customer ON CustomerId = CustomerId;

The CustomerId prefix is missing

An Inventory view table is defined with a WHERE clause that specifies ShelfNumber = 25. An INSERT adds a new item to ShelfNumber 20. What happens when the WITH CHECK OPTION is not specified in the CREATE VIEW statement?

The item appears in the base table but not in the view table.

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 indicates the database already exists.

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

VARCHAR(50), DECIMAL (8,2)

When a user interacts with a db they can use a ___ to modify data with commands

query language

Three relational operations

select, project, union

A___is a collection of values with no inherent order

set