ISDS 402 Midterm

The five SQL built-in functions

-MAX -MIN -COUNT -SUM -AVG

A candidate key is:

Both required to be unique and a candidate to be the primary key

A table that meets the requirements of a relation is said to be in which normal form?

First normal form

SQL view instances are retrieved using the:

SQL SELECT statement.

SQL stands for:

Structured Query Language.

In MS Access, the relationship between two tables is not actually created until:

The create button in the edit relationships dialog box is clicked

A relational database is:

a self-describing collection of related tables.

The Microsoft Access 2010 data type of AutoNumber is used when there is a specific need for a:

surrogate key.

Which of the following is a function of the database application in a database system?

Create and transmit queries

The creation of a database and its tables is a function of which component of the database system?

DBMS

Which of the following is not true of surrogate keys?

They are meaningful to the users.

Which of the following is the correct SQL clause to restrict the results of a SELECT query to only records that have a value in the range of 10 to 50 in the Hours column?

WHERE Hours BETWEEN 10 AND 50

A subquery is appropriate only if the final result contains only data from a single table.

false

In a nested query, the outer query is evaluated first.

false

Which of the following functional dependency diagrams accurately represents the following situation: ∙ A campus has many buildings. ∙ Each building has a unique name. ∙ Each building has many rooms. ∙ All rooms in any given building are numbered sequentially starting at "101." ∙ Each room has a certain capacity although many rooms in the same building or different buildings may have the same capacity. ∙ Each room is assigned to a single department. ∙ A department may have many rooms in one or more buildings, each with the same or different capacities.

(BuildingName, RoomNumber) → (Capacity, Department)

Which of the following can be done using the CONSTRAINT phrase?

-Create a single attribute primary key. -Define a foreign key. -Establish a referential integrity constraint.

Which of the following problems associated with storing data in a list is avoided by storing data in a relational database?

-Maintaining the data may require changing the same data value in many locations. -Inconsistency when a data item is used multiple times. -Duplication of data items. -Inability to store partial data

A database may be used to help people:

-know the current inventory levels of products their company sells -look up their checking account balance over the Internet. -check on the estimated arrival time of an incoming flight at an airport. -track which student is assigned to a particular advisor.

In the normalization process, if you find a candidate key that is not a determinant then you should

-make the determinant of the functional dependency the primary key of the new relation. -leave a copy of the determinant as a foreign key in the original relation. -place the columns of the functional dependency in a new relation.

SQL views are used:

-to layer built-in functions. -to hide columns or rows. -to display the results of computations. -to hide complicated SQL syntax.

A primary key is:

-used to identify unique rows -required to be unique -used to represent rows in relationships -a candidate key

Microsoft Access 2010 database files are stored using the files extension of:

.accdb

Which of the following is not true about a relation?

A relation may have duplicate column names.

The SQL statement used to modify a view is:

ALTER VIEW ViewName AS

The default file format for Microsoft Access 2010 database files is the:

Access 2007 format.

Given the relations below, such that each student is assigned to one advisor, which of the following is true? Student (SID, StudentName, Major, AdvisorID) Advisor(AdvisorID, AdvisorName, Office, Phone)

AdvisorID is a foreign key.

Which of the following is true about a relation?

All entries in any column must be of the same kind.

Conditions after the WHERE require single quotes around the values for columns that have which data type?

Both Char and VarChar

Which of the following is true about a key?

Both It may be unique and It may be non-unique

A surrogate key may be appropriate under which of the following circumstances?

Both The candidate keys available would produce a lot of data duplication when representing relationships and The candidate keys available would be prone to typographical errors

Based on the tables below, which of the following SQL commands would create an SQL view named CustomerSalesRep that could be used to display CustNo, CustName, RepName?.

CREATE VIEW CustomerSalesRep AS SELECT CustNo, CustName, RepName FROM SALESREP, CUSTOMER WHERE SALEREP,SalesRepNo = CUSTOMER,SalesRepNo;

Which of the following is not a function of the database application in a database system?

Control concurrency

The SQL statement used to delete a view from a database is:

DROP VIEW ViewName

Which of the following is not a basic component of a database system?

ERD

Given the table STUDENT(StudentID, Name, Advisor), which of the following SQL statements would be used to add new student data to the STUDENT table?

INSERT INTO STUDENT VALUES (123, 'Jones', 'Smith');

Given the functional dependency below, which of the following statement is not known to be true? MedicineCode--> (MedicineName, ShelfLife, Manufacturer, Dosage)

MedicineName is a determinant.

Which of the following is not true about null values?

Null values cannot be avoided.

Which of the following is the correct SQL clause to sort the results of a SELECT query in reverse-alphabetic order using the Department field?

ORDER BY Department DESC

Which of the following is a function of the DBMS in a database system?

Perform backup and recover

Which of the following is not true about primary keys?

Primary keys must be a single attribute.

Which of the following would not be an example of database metadata?

Queries against records in the database tables

Which of the following terms is synonymous with "tuple"?

Row

Given the table CUSTOMER(CustID, Name, PhoneNum, AcctBalance), what is the standard SQL query phrase to retrieve the Name and Phone Number of customers?

SELECT Name, PhoneNum

Although it cannot be used in creating a SQL VIEW, the SQL SELECT statements retrieving view instances can include:

SQL keyword ORDER BY

SQL views are constructed from

Select statements

When the OR operator connects simple conditions, the compound condition will be true whenever any one of the simple conditions is true.

TRUE

Which of the following terms is synonymous with "relation" ?

Table

Given the table CUSTOMER(CustID, Name, PhoneNum, AcctBalance), what is the standard SQL query phrase to retrieve data for customers with an account balance greater than 50?

WHERE AcctBalance > 50

Which symbol is used in standard SQL as a wildcard to represent a single, unspecified character?

_ (underscore)

A rule that requires that the values in a foreign key must have a matching value in the primary key to which the foreign key corresponds is called:

a referential integrity constraint.

During the normalization process, the remedy for a relation that is not well formed is to:

break it into two or more relations that are well formed

To open a new Microsoft Access Query window:

click the Query Design button on the Command tab.

key that contains more than one attribute is called a(n):

composite key.

A composite primary key can be defined using the CONSTRAINT phrase in which SQL command?

create table

The Microsoft Access application generator is not responsible for:

creating tables.

Microsoft SQL Server is an example of a:

database management system.

Which SQL keyword is used to eliminate duplicate rows in the results of a SQL SELECT query?

distinct

One important relational design principle is that:

every determinant must be a candidate key

When you connect two or more simple conditions with the AND operator, each condition must be listed on a separate line.

false

You must use a comma or a period to separate a table name from its alias.

false

When the primary key of one relation is placed into a second relation, it is called a:

foreign key

Which SQL keyword is used to apply conditions to restrict groups that appear in the results of a SELECT query that uses GROUP BY?

having

The first step of the normalization process is to:

identify all the candidate keys of a relation.

In the normalization process, it is not necessary to:

identify all the foreign keys of a relation.

DBMS objects are displayed:

in the Object Browser.

When an SQL SELECT statement is used to retrieve a view instance, the maximum number of columns that can be specified in the SELECT is the same as the number of columns:

in the VIEW specification.

The component of a database that makes it self-describing is the:

metadata.

The order of the columns returned by an SQL SELECT statement are determined by the:

order they are listed in following SELECT.

Which type of join, although not included in standard SQL, was created to allow unmatched rows to appear in the result of a join operation?

outer join

To qualify a column name, precede the name of the column with the name of the table, followed by a(n) ____.

period (.)

SQL statements can be run individually or as part of a related group of SQL statements known as a(n):

script

A relational database stores data in the form of:

tables.

Which of the following is not a standard data type used in SQL?

text

Microsoft Access is a personal database system, and a personal database system is characterized by:

the DBMS product taking the role of the DBMS and the database application generator.

SQL statements used to construct views cannot contain:

the ORDER BY clause.

In the normalization process, if you find that every determinant in a relation is a candidate key then you have determined that:

the relation is well formed.

Today almost every commercial database is based on:

the relational model.

A new SQL Server database is created using the New Database dialog box.

true

An important reason for using SQL Server 2008 R2 Express is that it has full SQL capabilities.

true

Database objects are displayed in the Object Explorer window in the SQL Server Management Studio.

true

Database objects are displayed in the object explorer window in the sql server management studio

true

SQL query results are displayed in a tabbed Resultset window.

true

SQL query results are displayed in a tabbed results window

true

SQL statements can be run individually or as part of a related group of SQL statements known as a script.

true

The Microsoft SQL Server 2008 Management Studio is included with Microsoft SQL Server 2008 R2 Express Advanced.

true

To create a new SQL query, click the New Query button

true

To run an SQL script, click the Execute button on the Query Toolbar.

true

To start working with SQL Server 2008 R2 Express use the command Start | All Programs | Microsoft SQL Server 2008 R2 | SQL Server management Studio Express.

true

Users log into SQL Server 2008 R2 Express using the Connect to Server dialog box.

true

You can assign names to computed columns.

true

t is a good idea to use a SELECT command to display the data you changed to verify that the correct update was made.

true

Which SQL keyword is used to specify a condition that rows must meet to be included in the results of an SQL SELECT query?

where