Grad dbms final

Which one is NOT true?

• DECIMAL data type is used to store a fractional value.

• Auto-increment is usually applied to primary key columns.

• In MySQL command line, semicolon (;) is used for ending a command

• Delete cascade is an example of a relational rule

• Delete cascade is an example of a relational rule

when a user interacts with a database, they can use a _______ to modify data with commands

Query Language

database _______ role focuses on database storage, response times, and optimization

designer

_______ links a host programming language to a database system

API

Please briefly explain five functional components that consist of any Database systems. 

query processor

storage manager

transaction manager

log

catalog

Which one is NOT true?

• The statement DROP table Employee; deletes the Employee table regardless of records existing in the table

• SQL database system is optimized for big data.

• CHAR(1) is good for storing a student's assigned letter grade, like A or C.

• The VARCHAR data type represents a variable string of characters.

• SQL database system is optimized for big data.

Which is Not True about the functions of transaction management? 

• Once a transaction completes, transaction results must always be saved on storage media, regardless of application or computer failures.

• When concurrent transactions occur, DBMS will prevent multiple transactions with the same data at the same time

• In MySQL, database programmer must put start transaction and commit commands manually to save each statement's result into DB storage.

• If a database system is processing three queries as part of a transaction and the third query fails, the successful query results are reversed, and the transaction is canceled.

• In MySQL, database programmer must put start transaction and commit commands manually to save each statement's result into DB storage.

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

• RETRIEVE, UPDATE

• SELECT, INSERT

• RETRIEVE, INSERT

• SELECT, UPDATE

SELECT, UPDATE

Which principle defines data independence?

 

• Modification of indexes generates different results.

• Tuning query performance requires application modifications.

• Logical design maintains schema integrity.

• Physical design never affects query results.

Physical design never affects query results.

In the following ER diagram, which is Not True?

• Fname is an attribute which will be implemented as a record

• ID in Album entity will become a primary key of Album table.

• Records is a relationship which can be implemented as a foreign key located at Album table (assuming one to many relationship)

• Artist is an entity and will be implemented as Artist table.

Fname is an attribute which will be implemented as a record

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 creates a copy of the database onlineShop as onlineShop_1.

• The statement produces an error that indicates the database already exists.

• The statement replaces onlineShop with a new onlineShop database.

• The statement is ignored.

The statement produces an error that indicates the database already exists.

Which is Not True?

 

• Table diagram is the result of physical design.

• ER diagram is the result of conceptual design.

• Implementation of database requirements as tables, keys, and columns in a specific database system is called logical design.

• Specification of database requirements without regard to implementation is called conceptual design.

Table diagram is the result of physical design.

What should be added to the SQL statements to produce the Result table below?

 

USE _____;
SHOW _____;

 

 

• onlineShop, TABLES

• DATABASE, COLUMNS

• DATABASE, CREATE TABLE

• onlineshop, TABLE

• onlineShop, TABLES

Which SQL statement change column name Fax to FaxNumber  which has the same data type, Varchar(30), to the Supplier table?

 

ALTER TABLE Supplier
CHANGE Fax FaxNumber VARCHAR(30);

 

ALTER TABLE Supplier
ADD FaxNumber VARCHAR(30);

 

ALTER Supplier
CHANGE Fax FaxNumber;

 

ALTER TABLE Supplier
Rename FaxNumber VARCHAR(30);

ALTER TABLE Supplier
CHANGE Fax FaxNumber VARCHAR(30);

(10 % 4 + 10 < 15) AND True

 

True

 

False

True

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 a ProductID column. Which table's CREATE TABLE statement(s) must specify a FOREIGN KEY?

 

• Product

• Supplier 

• Country

• Supplier and Country

• Supplier 

Which is not True?

 

• The result of TRUE AND NULL is FALSE

• COMMIT is a DTL statement that saves a transaction to the database.

• CREATE is a DDL statement that creates a table.

• In a table, columns are ordered and rows are not ordered.

The result of TRUE AND NULL is FALSE

Which is not True?

• In the relational model, an unnamed tuple of values is a record

• SQL stands for Structured Query Language

• INT data type can store negative numbers

• BLOB data type stores float values

• BLOB data type stores float values

Choose the best data types to replace XXX and YYY.

 

CREATE TABLE Employee (
   ID INT,
   Name XXX,
   Salary YYY
);

 

 

• CHAR(50), INT

• VARCHAR(50), DECIMAL(8, 2)

• CHAR(50), FLOAT

• VARCHAR(50), DATE

• VARCHAR(50), DECIMAL(8, 2)

Refer to the Product table. Which products are selected by the query below?

 

SELECT ProductName FROM Product
WHERE Quantity < 15 AND UnitPrice <= 20.00;

 

 

• Sunsuit, Shorts set, Romper

• Onesies set, Pajama set

• Sunsuit, Shorts set

• No products

• Sunsuit, Shorts set

What should be added so NULL values are not allowed in ProductName?

 

CREATE TABLE Product (
   ProductId INT,
   ProductName _____,
   UnitPrice DECIMAL(5,2),
   SupplierId INT
);

 

 

• VARCHAR(50) NOT NULL

• NOT NULL

• VARCHAR(50 NOT NULL)

• NOT NULL VARCHAR(50)

• VARCHAR(50) NOT NULL

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

 

• A single INSERT statement can only add one row.

• The column names can be omitted in an INSERT statement.

• The VALUES order must match the column order in the INTO clause.

• The INSERT statement is used to add new values to a table.

A single INSERT statement can only add one row.

Which one is True?

 

• Two rows may have identical values in all columns

• Duplicate column names are not allowed in different tables

• Minimal means that all primary key columns are necessary for uniqueness

• A primary key must be unique and it can have NULL

Minimal means that all primary key columns are necessary for uniqueness

In the Reservation table below, a room may be reserved several times, so the RoomNumber column is not unique. To ensure that a room can only be reserved by only one guest for the day, the minimal primary key consists of which columns?

 

• (RoomNumber, DateOfStay, Guest)

• (RoomNumber, Guest, ModeOfPayment)

• (RoomNumber, ModeOfPayment)

• (RoomNumber, DateOfStay)

(RoomNumber, DateOfStay)

A ___________ is a simple or composite column that is unique and minimal.

Candidate Key

Physical design never affects query results

Data Independence

Foreign key value must match a candidate key value of some tuple in its home relation or must be wholly null.

Referential integrity

________ contains “data about the data” or meta data

catalog

Which two rules apply to primary keys?

 

• Values must be unique and may not be NULL

• Values must be unique and can be NULL

• Values can have duplicates and can be NULL

• Values can have duplicates and may not be NULL

Values must be unique and may not be NULL

Refer to the Teacher and Class tables. The action ON UPDATE CASCADE  is specified for the TeacherID foreign key. What is the result when Rosa Lopez's TeacherID is changed to 12333?

 

• The change is rejected.

• The TeacherID for Databases and Programming are changed to 12333.

• The TeacherID for Rosa Lopez is set to NULL

• The Databases and Programming courses are deleted.

The TeacherID for Databases and Programming are changed to 12333.

Which is Not True?

 

• In a many-many relationship, a new table contains two foreign keys, referring to the primary keys of the related tables.

• In a one-one relationship, the foreign key is placed in the table with less rows.

• The primary key of a table that implements a many-many relationship is composite.

• In a many-one relationship, foreign key goes in the table on the 'one' side of the relationship

In a many-one relationship, foreign key goes in the table on the 'one' side of the relationship

A model has Shipment, Product, and Shipper entities. The Shipment entity has no unique attributes. Each shipment is identified by ProductNumber, ShipperCode, and ShipmentDate. Shipment is a(n) _____ entity.

 

• identifying

• subtype

• strong

• weak

weak

What is the greatest number of instances of one entity that can relate to a single instance of another entity?

 

• Maximum

• Minimum

• One

• Many-many

Maximum

Each course must be offered by at least one department. In the Department-Offers-Course relationship, Department is a(n) _____ entity.

 

• singular

• required

• optional

• plural

required

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

• EmployeeID

• EmployeeLastName

• WorkHours

EmployeeLastName

What is Not True about denormalization? 

 

• It is achievable by merging tables

• Denormalization typically improves performance of join queries.

• Denormalization is a process of intentionally introducing redundancy.

• Denormalization never results in second-normal-form tables

Denormalization never results in second-normal-form tables

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

 

• Programmer

• Designer

• Administrator

• User

Designer

Which is NOT true?

• NULL value represent missing data or unavailable data

• In table, columns are ordered and rows are unordered

• INT data type can store positive and negative numbers

• BLOB data type can store textual data in a structured format

BLOB data type can store textual data in a structured format

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

 

• Through the reversal of a whole or partial transaction due to a failure.

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

• By ensuring authorized users only access permissible data.

• Documenting any lost transactions by always recording results.

Preventing multiple transactions with the same data at the same time

Each school has at least some graduates. Some students have not yet graduated from any school.

Determine the below relationship minima? 

Student-GraduatesFrom-School

• one-zero

• zero-one

• zero-zero

• one-one

one-zero

Which one is Not True?

• Maxima and minima usually depend on business rules.

• A required attribute becomes a column that is never NULL

• NoSQL database system is optimized for big data.

• Primary keys should contain descriptive information to convey meanings to users

Primary keys should contain descriptive information to convey meanings to users

What is Not True?

 

• Attributes of the supertype apply to all subtypes

• All weak entities are subtype entities.

• In an entity-relationship diagram, attributes are documented within an entity rectangle.

• If a plural attribute has a small, fixed maximum, it can be implemented as multiple columns in the initial table

All weak entities are subtype entities.

A table has:

• columns A, B, C, D

• candidate key (A, B, C)

• primary key (A, B, C)

• dependency D → B (in addition to dependencies on candidate keys)

What is the normal form of the table? 

 

• first NF

• second NF

• boyce-codd NF

• third NF

third NF

A ____ is a SELECT statement that combines data from two tables, known as the left table and right table, into a single result.

join

Which columns can be compared in a join?

any columns with comparable data types

In a join, what are the first and second tables in the FROM clause called?

left table and right table

selects only matching left and right table rows.

INNER JOIN

selects all left and right table rows, regardless of match

FULL JOIN

In a FULL JOIN result table, unmatched left table rows appear with

NULL values in right table columns, and vice versa

The join clause appears between a

FROM clause and an ON clause

The FROM clause specifies the

left table

The INNER JOIN or FULL JOIN clause specifies

the right table

The ON clause specifies the

join columns

MySQL supports INNER JOIN but not

FULL JOIN

selects all left table rows, but only matching right table rows

LEFT JOIN

selects all right table rows, but only matching left table rows

RIGHT JOIN

any join that selects unmatched rows, including left, right, and full joins

outer join

MySQL supports both LEFT JOIN and RIGHT JOIN (t/f)

True

compares columns of two tables with the = operator

equijoin

Most joins are equijoins (t/f)

true

compares columns with an operator other than =, such as < and >

non-equijoin

Which join clauses can be used in a non-equijoin query?

All join clauses

joins a table to itself

self-join

A self-join can compare any columns of a table, as long as the columns have

comparable data types

If a foreign key and the referenced primary key are in the same table, a self-join commonly compares

those key columns

In a self-join, aliases are

necessary to distinguish left and right tables.

combines two tables without comparing columns

cross-join

A cross-join uses a CROSS JOIN clause without an

ON clause

A view table is a table name associated with a SELECT statement, called the

view query

A table specified in the view query's FROM clause is called a

base table

Unlike base table data, view table data is not

normally stored

a view for which data is stored at all times

materialized view

Advantages of views

• Protect sensitive data.

• Save complex queries

• Save optimized queries

Using materialized views always improves database performance.(t/f)

false

The performance of a query on a non-materialized view is identical to the performance of the corresponding merged query on base tables. (t/f)

true

A view query can reference another view table.(t/f)

true

Views can be used to hide rows as well as columns from database users. (t/f)

true

View tables are commonly used in

SELECT statements

the database rejects inserts and updates that do not satisfy the view query WHERE clause. Instead, the database generates an error message that explains the violation.

WITH CHECK OPTION

Imperative languages

contain control flow statements that determine the execution order of program steps

Procedural languages

composed of procedures, also called functions or subroutines.ex: C and CBOL

Object-oriented languages

organize code into classes. A class combines variables and procedures into a single construct

Declarative languages

do not contain control flow statements. Each statement declares what result is desired, using logical expressions, rather than how the result is processed ex: SQL HTML

Declarative language is commonly used for database queries for two reasons

Easier programming
Faster execution

Database programming presents two challenges:

Syntax gap
Paradigm gap

Embedded SQL

codes SQL statements directly in a program written in another language.

Procedural SQL

extends the SQL language with control flow statements, creating a new programming language.

An application programming interface, or API

is a library of procedures or classes

Embedded SQL was the earliest database programming technique, developed along with SQL in the 1980s. Procedural SQL emerged soon after embedded SQL in the late 1980s. The first widely used database API, ODBC, was released in 1992 (T/F)

true

Different database programming techniques cannot be combined in one application. (t/f)

False

The EXEC SQL keyword is used in procedural SQL.(t/f)

false

Procedural SQL is specified in the SQL standard. (t/f)

true

The ODBC API for the Java language consists of Java classes. (t/f)

true

Disadvantages of Embedded SQL

Gaps. Compile steps, Network Traffic

Advantages of Procedural SQL

Gaps. Compile steps, Network Traffic, Optimization level

Advantages of API

Gaps, Application, Database Independence

SQL/Persistent Stored Modules (SQL/PSM) is

a standard for procedural SQL that extends the core SQL standard.

cursor

a special variable that identifies an individual row of a result table

trigger is like a stored procedure or a stored function, with two differences:

Triggers have neither parameters nor a return value
Triggers are not explicitly invoked by a CALL statement or within an expression.