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
