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