Database Midterm

What is the correct order of operator precedence in SQL (listed from higher to lower precedence)? NOT * + = AND OR NOT * + AND OR = * + = NOT AND OR = NOT * + AND OR

* + = NOT AND OR

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

DECIMAL

In the figure shown below, which of the following business rules would apply? Each vendor can supply many parts to any number of warehouses, but need not supply any parts. Each part must be supplied by exactly one vendor to any number of warehouses. Each warehouse can be supplied with any number of parts from more than one vendor, and each warehouse could be supplied with no parts. VENDOR is not allowed.

Each vendor can supply many parts to any number of warehouses, but need not supply any parts.

Referential integrity constraints are implemented in SQL using the _______ syntax. PRIMARY KEY FOREIGN KEY DEFAULT UNIQUE

FOREIGN KEY

chapter 3 Which of the following questions is answered by the SQL statement? Select Count (Product_Description) from Product_T; How many products are in the table Product_T? How many products have product descriptions in the Product Table? How many characters are in the field name "Product_Description"? How many different columns named "Product_Description" are there in table Product_T?

How many products have product descriptions in the Product Table?

Which principle defines data independence? Logical design maintains schema integrity. Physical design never affects query results. Tuning query performance requires application modifications. Modification of indexes generates different results.

Physical design never affects query results.

How does a database system protect data when concurrent transactions occur? By ensuring authorized users only access permissible data. Through the reversal of a whole or partial transaction due to a failure. Documenting any lost transactions by always recording results. Preventing multiple transactions with the same data at the same time.

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

chapter 1 Which role focuses on creating software that interacts with a database? Programmer Designer Administrator User

Programmer

For the database shown below... Name is unique on Library table. LIBID, AUTHID, and BOOKID are primary keys for their tables and (LIBID, BOOKID) is the primary key for LibraryBook. Foreign keys exist on LibraryBook.LIBID (referencing Library), on LibraryBook.BOOKID (referencing Book), and on Book.AUTHID (referencing Author). No cascades have been defined. Which book title is returned by the following SQL query? SELECT Title FROM BOOK WHERE YearPublished IN (SELECT MIN(YearPublished) FROM Book); Brave New World Mere Christianity Don't Make Me Think The Screwtape Letters Romeo & Juliet No book title is returned None of the above

Romeo & Juliet

The analysis phase of database design includes which process? Specifying requirements that are dependent on a specific database system. Specifying requirements that are not dependent on a specific database system. The creation of a table diagram and all relationships. The conversion of discovered entities into tables

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

What design consideration would apply to a database that has special performance requirements? Structuring data properly on storage media. Offer limited access to specific data. Maintain a consistent state without the loss of data. Ensuring data is consistent with structural rules.

Structuring data properly on storage media.

Refer to the Teacher and Class tables. What is the result of the SET NULL foreign key action when Bryan McNeal's TeacherID is changed to 45672? The Web Development TeacherID is set to 45672. The Web Development TeacherID is set to NULL. The Web Development course is deleted. The change is rejected.

The Web Development TeacherID is set to NULL.

Which statement is NOT an advantage of using a view table? Sensitive table data can be hidden from users and programmers. Optimal SELECT statements can be saved in a view table. Complex SELECT statements can be saved in a view table. The creation of a new base table is always up to date.

The creation of a new base table is always up to date.

A pair of related values in a database is a(n) _____. table fact key index

fact

A relationship where the minimum and maximum cardinality are both one isa(n) ________ relationship. optional unidirectional mandatory link mandatory one

mandatory one

A student can attend five classes, each with a different professor. Each professor has 30 students. The relationship of students to professors is a ________ relationship. one-to-one one-to-many many-to-many strong

many-to-many

When a database stores view data, it uses a _____ that depends on data in a corresponding _____. base table, view table materialized view, base table view table, materialized view materialized view, view table

materialized view, base table

A view may not be updated directly if it contains: the SELECT clause. the HAVING clause. the FROM clause. the WHERE clause.

the HAVING clause.

An entity type whose existence depends on another entity type is called a ________ entity. strong weak codependent variant

weak

In the Reservation table above, 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, ModeOfPayment) (RoomNumber, Guest, ModeOfPayment) (RoomNumber, DateOfStay)

(RoomNumber, DateOfStay)

When a user issues the DELETE FROM tablename command without specifying a WHERE condition.... ...no rows will be deleted ...the first row will be deleted ...the last row will be deleted ...all rows will be deleted

...all rows will be deleted

For the database shown below... Name is unique on Library table. LIBID, AUTHID, and BOOKID are primary keys for their tables and (LIBID, BOOKID) is the primary key for LibraryBook. Foreign keys exist on LibraryBook.LIBID (referencing Library), on LibraryBook.BOOKID (referencing Book), and on Book.AUTHID (referencing Author). No cascades have been defined. How many new rows will be inserted into the database by the following SQL query? INSERT INTO LibraryBook (BOOKID, LIBID, NumCopies) VALUES (2,6, 32); Answer

0

For the database shown below... Name is unique on Library table. LIBID, AUTHID, and BOOKID are primary keys for their tables and (LIBID, BOOKID) is the primary key for LibraryBook. Foreign keys exist on LibraryBook.LIBID (referencing Library), on LibraryBook.BOOKID (referencing Book), and on Book.AUTHID (referencing Author). No cascades have been defined. How many new rows will be inserted into the database by the following SQL query? INSERT INTO Book VALUES (8, 'Hamlet', 'Tragedy', 1597, 5);

1

For the database shown below... Name is unique on Library table. LIBID, AUTHID, and BOOKID are primary keys for their tables and (LIBID, BOOKID) is the primary key for LibraryBook. Foreign keys exist on LibraryBook.LIBID (referencing Library), on LibraryBook.BOOKID (referencing Book), and on Book.AUTHID (referencing Author). No cascades have been defined. How many rows of data will the following SQL query return as a result? SELECT DISTINCT(BOOKID) FROM LibraryBook WHERE NumCopies=7;

1

For the database shown below... Name is unique on Library table. LIBID, AUTHID, and BOOKID are primary keys for their tables and (LIBID, BOOKID) is the primary key for LibraryBook. Foreign keys exist on LibraryBook.LIBID (referencing Library), on LibraryBook.BOOKID (referencing Book), and on Book.AUTHID (referencing Author). No cascades have been defined. How many rows of data will the following SQL query return as a result? SELECT Name, TitleFROM Author AS aLEFT OUTER JOIN Book AS bON a.AUTHID = b.AUTHID;

8

What will be returned when the following SQL query is executed? Select driver_no, count(*) as num_deliveriesfrom deliveriesgroup by driver_nohaving count(*) > 2; A listing of all drivers who made more than 2 deliveries as well as a count of the number of deliveries A listing of all drivers A listing of the number of deliveries greater than 2 A listing of all drivers who made more than 2 deliveries

A listing of all drivers who made more than 2 deliveries as well as a count of the number of deliveries

Which of the following statements is true about the figure shown below? A rental unit must be either an apartment or a house, and cannot be both at the same time. A rental unit can be an apartment, house or just a rental unit; it may not be more than one at the same time. A rental unit must be either an apartment or a house, and could be both. A rental unit can be an apartment, a house or just a rental unit. It could be both an apartment and a house at the same time.

A rental unit must be either an apartment or a house, and could be both.

In which of the following situations would one have to use an outer join in order to obtain the desired results? A report is desired that lists all customers who placed an order. A report is desired that lists all customers and the total of their orders. A report is desired that lists all customers and the total of their orders during the most recent month, and includes customers who did not place an order during the month (their total will be zero). There is never a situation that requires only an outer join.

A report is desired that lists all customers and the total of their orders during the most recent month, and includes customers who did not place an order during the month (their total will be zero).

Which statement is true about the following diagram? A person can only be a faculty, student or staff. A student can be both an undergraduate and a graduate student at the same time. All attributes of person and student are inherited by undergraduate. All attributes of graduate are inherited by person.

All attributes of person and student are inherited by undergraduate.

What does the following SQL statement do? Alter Table Customer_TAdd (Type Varchar (2)); Alters the Customer_T table to accept Type 2 Varchars Alters the Customer_T table to be a Type 2 Varchar Alters the Customer_T table, and adds a field called "Type" Alters the Customer_T table by adding a 2-byte field called "Varchar"

Alters the Customer_T table, and adds a field called "Type"

In the following diagram, which of the answers below is TRUE? Each patient has one or more patient histories. Each patient has one and only one visit. Each patient history belongs to zero and one patient. Each patient history belongs to many patients.

Each patient has one or more patient histories.

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? EmployeeLastName EmployeeID WorkspaceID WorkHours

EmployeeLastName

The first part of an SQL query to be read (executed by the DBMS) is the ________ statement. SELECT FROM WHERE ORDER BY

FROM

Which of the following can produce scalar and vector aggregates? Order By Group By Having Sort

Group By

Which concept relates to software that reads and writes data in a database? Application Management system Language Design

Management system

Which type of database system is optimized for big data? Oracle SQL NoSQL Relational

NoSQL

What does a user that interacts with a database use to read and write data? Application programming Query language Database design Database management system

Query language

Refer to the Teacher and Class tables. To maintain referential integrity, which foreign key action rejects the deletion of the row containing Rosa Lopez? RESTRICT SET NULL SET DEFAULT CASCADE

RESTRICT

With SQL, how do you select all the records from a table named "Person" where the value of the column "FirstName" starts with an "a"? SELECT * FROM Person WHERE FirstName = '%a%' SELECT * FROM Person WHERE FirstName LIKE 'a%' SELECT * FROM Person WHERE FirstName = 'a' SELECT * FROM Person WHERE FirstName LIKE '%a'

SELECT * FROM Person WHERE FirstName LIKE 'a%'

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

SELECT, UPDATE

Relational databases are heavily based on the mathematical concept of: Set Theory. Bet Theory. Get Theory. Met Theory.

Set Theory

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? Transaction manager Storage manager Data dictionary Data indexes

Storage manager

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. An error is recorded, and the successful query results are saved. The two successful queries are recorded to storage, and the third query is executed until successful. Only the transaction queries that did not conflict are saved

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

Given a table named store with 5 fields: store_id, address, city, state, zipcode, why would the following insert command not work? INSERT INTO store values ('234 Park Street'); It would work just fine. You must specify the fields to insert if you are only inserting some of the fields. There is no table keyword. Insert into should be INSERT to.

You must specify the fields to insert if you are only inserting some of the fields.

Given a table named store with 5 fields: store_id, address, city, state, zipcode, why would the following insert command not work? insert into store values ('234 Park Street') It would work just fine. You must specify the fields to insert if you are only inserting some of the fields. There is no table keyword. Insert into should be INSERT to.

You must specify the fields to insert if you are only inserting some of the fields.

In the ER diagram shown above, what does 'AlbumTitle' represent? Entity Attribute Relationship Key

attribute

An attribute that uniquely identifies an entity and consists of a composite attribute is called a(n): composite attribute composite identifier identifying attribute relationship identifier

composite identifier

The logical representation of an organization's data is called a(n): database entity diagram relationship systems design entity-relationship model database model

entity-relationship model

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 _____. candidate key alternate key foreign key primary key

foreign key

Which goes in the table on the "many" side of a one-many relationship? Primary key Foreign key Artificial key Optional attribute

foreign key

In a one-one relationship, the _____ key is often placed in the table with fewer rows. This minimizes the number of _____ values. primary, NULL primary, unique foreign, NULL foreign, unique

foreign, NULL

A(n) ________ is the relationship between a weak entity type and its owner. member chain identifying relationship jump path chain link

identifying relationship

All _____ columns depend on the _____ for a table to be in first normal form. primary key, foreign key artificial key, primary key non-key, primary key non-key, foreign key

non-key, primary key

When a user interacts with a database, they can use a _____ to modify data with commands. query processor query language data dictionary transaction manager

query language

chapter 2 A relational database uses _____ to structure all data. queries columns tables cells

tables

When an entity-relationship model diagram is implemented within SQL, entities typically become _____, and relationships typically become _____. tables, primary keys tables, foreign keys rows, tables foreign keys, rows

tables, foreign keys

Which relational algebra expression is equivalent to the following SQL statement? SELECT PassengerName FROM Booking WHERE TicketPrice 1000; Π(TicketPrice<1000)(σ(PassengerName)(Booking)) σ(TicketPrice<1000)(Π(PassengerName)(Booking)) Π(PassengerName)(σ(TicketPrice<1000)(Booking)) σ(PassengerName)(Π(TicketPrice<1000)(Booking))

Π(PassengerName)(σ(TicketPrice<1000)(Booking))Π(PassengerName)(σ(TicketPrice<1000)(Booking)) The capital PI is the project operation which selects columns (in this case PassengerName) and the lowercase sigma is the SELECT with the condition as a subscript.

For the database shown below... Name is unique on Library table. LIBID, AUTHID, and BOOKID are primary keys for their tables and (LIBID, BOOKID) is the primary key for LibraryBook. Foreign keys exist on LibraryBook.LIBID (referencing Library), on LibraryBook.BOOKID (referencing Book), and on Book.AUTHID (referencing Author). No cascades have been defined. How many rows of data will the following SQL query return as a result? SELECT COUNT(*) FROM BOOK;

1

Evaluate the given data and determine the correct result from the statement. 450, 35 125, 200, 160 485 250, 35

485

For the database shown below... Name is unique on Library table. LIBID, AUTHID, and BOOKID are primary keys for their tables and (LIBID, BOOKID) is the primary key for LibraryBook. Foreign keys exist on LibraryBook.LIBID (referencing Library), on LibraryBook.BOOKID (referencing Book), and on Book.AUTHID (referencing Author). No cascades have been defined. How many rows of data will the following SQL query return as a result? SELECT * FROM Book, Author;

49

For the database shown below... Name is unique on Library table. LIBID, AUTHID, and BOOKID are primary keys for their tables and (LIBID, BOOKID) is the primary key for LibraryBook. Foreign keys exist on LibraryBook.LIBID (referencing Library), on LibraryBook.BOOKID (referencing Book), and on Book.AUTHID (referencing Author). No cascades have been defined. The following SQL query will return a result consisting of?SELECT * FROM Book; 3 rows and 2 columns 5 rows and 5 columns 5 rows and 7 columns 7 rows and 5 columns None of the above

7 rows and 5 columns

For the relationship represented in the figure below, which of the following is true? An employee can work in more than one department but does not have to work for any department. A department must have at least one employee. A department can have more than one employee. An employee has to work for more than one department.

A department can have more than one employee.

What links a host programming language to a database system? API SQL Key ID

API

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

CASCADE

For the database shown below... Name is unique on Library table. LIBID, AUTHID, and BOOKID are primary keys for their tables and (LIBID, BOOKID) is the primary key for LibraryBook. Foreign keys exist on LibraryBook.LIBID (referencing Library), on LibraryBook.BOOKID (referencing Book), and on Book.AUTHID (referencing Author). No cascades have been defined. What is the FIRST ROW of data returned by the SQL query? SELECT Name, SUM(NumCopies) AS COUNT FROM Library L INNER JOIN LibraryBook LB ON L.LIBID = LB.LIBID GROUP BY Name ORDER BY COUNT DESC; CBU, 3 CBU, 38 Dartmouth, 5 Dartmouth, 29 RPI, 5 Harvard, 7 No records are returned None of the above

CBU, 38

Which of the following ONLY counts rows that contain a value? Count(column_name) Count(*) Tally(*) Checknum

Count(column_name)

Which language defines statements used for creating and dropping tables? Data Manipulation Language Data Query Language Data Definition Language Data Control Language

Data Definition Language

Which database role focuses on database storage, response times, and optimization? Administrator Programmer Designer User

Designer

The _____ SQL statement does not alter any database data. INSERT SELECT UPDATE RETRIEVE

SELECT

In the following diagram, what type of relationship is depicted? Unary Binary Ternary Quad

Ternary

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. The number of significant digits allowed for the data type. The initial default value for the data type. The ID for the data type. The numeric integer value for the data type.

The number of characters allowed for the data type

Which two rules apply to primary keys? Values can have duplicates and may not be NULL Values must be unique and can be NULL Values must be unique and may not be NULL Values can have duplicates and can be NULL

Values must be unique and may not be NULL

An entity type name should be all of the following EXCEPT: concise. specific to the organization. as short as possible. a singular noun.

as short as possible.

chapter 4 The property by which subtype entities possess the values of all attributes of a supertype is called: hierarchy reception. class management. attribute inheritance. generalization.

attribute inheritance.

A(n) ________ constraint is a type of constraint that addresses whether an instance of a supertype must also be an instance of at least one subtype. disjoint overlap weak completeness

completeness

To eliminate duplicate rows in a query, the ________ qualifier is used in the SQL Select command. alter distinct check specific

distinct

Any create command may be reversed by using a(n) ________ command. undo drop delete unpack

drop

A database administrator uses _____ to eliminate redundancy. This is done by decomposing a table into two or more tables in higher normal form. normalization denormalization partitioning referential integrity

normalization

A database _____ is the implementation of database requirements in SQL with CREATE TABLE statements. system attribute entity schema

schema

Redundancy is possible in a _____ normal form table when one non-key column depends on another non-key column. second first third Boyce-Codd

second

The following code is an example of a: SELECT CustomerName, CustomerAddress, CustomerCity, CustomerState,CustomerPostalCodeFROM Customer_TWHERE Customer_T.CustomerID =(SELECT Order_T.CustomerID FROM Order_T WHERE OrderID = 1008); Correlated subquery. Subquery. JOIN. FULL OUTER JOIN.

subquery

The primary key of a _____ table is identical to the primary key of a supertype table. subtype third normal form dependent master

subtype

The following figure is an example of: partial specialization completeness total specialization disjointness

total specialization

In an E-R diagram, there are/is ________ business rule(s) for every relationship. zero one two three

two