Database Management 1504
If you want to select records containing the text Consulting in the ProjectType field, how does Access save the criterion in the query grid of Query Design View?
"Consulting"
Relational databases are often implemented on tables in what normal form?
3NF
Which of the following criteria selects all records containing a value greater than 250?
>250
If you use ____________________ criteria, each criterion must be true for a record to be selected.
AND
You can create compound criteria (conditions) in a query by using which of the following two keywords?
AND, OR
What happens when you run a select query in Access?
Access selects current data from underlying tables.
How are aggregate functions different from other types of functions?
Aggregate functions make calculations on groups of records.
For a one-to-many relationship in the Access Relationships window, how can you tell which table is the "many" table?
An infinity symbol identifies the "many" table.
If you use an aggregate function in a query to determine the average values in a Bonus field, what default field name does Access use for the column with the calculations?
AvgOfBonus
Third normal form is also often referred to as _____.
Boyce-Codd normal form
Which of the following SQL commands creates an index named LastName on the LastName field in the Customers table?
CREATE INDEX LastName ON Customers (LastName);
Which of the following SQL commands creates a view named ClientList?
CREATE VIEW ClientList
When two tables in a query share no common fields, each record in one table connects to each record in the other table, creating a(n) _____.
Cartesian join
The Access query design grid contains the ProductID, ProductName, and Rating fields. How can you omit the Rating field from the query results but use it to set a criterion for the query?
Clear the checkmark from the Rating field's Show check box.
Which of the following tables should be converted to third normal form?
Clients (ClientID, ClientFirst, ClientLast, ProjectID, ProjectType)
When you build a relational database for an organization, what is a good starting point for identifying required user views?
Collect the organization's forms and reports
How might you resolve an inappropriate Cartesian join?
Connect the two tables through a third junction table
Which of the following Access functions can you use to return the number of records in a group of records?
Count
What type of software programs are Access, Oracle, DB2, MySQL, and SQL Server?
DBMSs
Which of the following is a valid reason for creating a one-to-one relationship between two tables?
Data needs to be imported from separate sources
In Access Query Design View, which of the following expressions calculates the number of days between today's date and a field named DueDate?
Date() - [DueDate]
How are entities represented in a relational database?.
Each entity is stored as a table.
Which of the following fields would be the best candidate for a primary key field in a table of employee data?
EmployeeID
Which of the following clauses sets the ProductID field as the foreign key to the Orders table?
FOREIGN KEY (ProductID) REFERENCES Orders
What SQL keyword do you use in a query that saves the results of the query as a table?
INTO
What is the foreign key in the following table? Classes (ClassID, ClassName, ClassDate, StartTime, EndTime, ClassPrice, InstructorNum)
InstructorNum
Which of the following statements describes a table in first normal form?
It contains no repeating groups
Which of the following expressions in Access Query Design View creates a computed field named MonthlySalary using a value from the Salary field?
MonthlySalary: [Salary] / 12
In the following Projects table, which field is functionally dependent on the ClientID field?Projects (ProjectID, ProjectType, ProjectName, ProjectManager, Budget, ClientID)
None of the fields in the Projects table is functionally dependent on ClientID.
How do you free data from the problems associated with insertion, update, and deletion anomalies?
Normalize the data by converting a single list into multiple tables
What type of relationship do two tables have when their primary keys match?
One to One
In relational algebra, what command is used to identify certain columns?
PROJECT
Based on the following table, which relational algebra command creates a new table named CustState containing the fields CustomerNum and State?Customer (CustomerNum, CustomerName, Street, City, State, PostalCode)
PROJECT Customer OVER (CustomerNum, State) GIVING CustState
Which of the following tables should be converted to second normal form?
Players (PlayerID, PlayerFirst, PlayerLast, CoachID, CoachFirst, CoachLast, Team)
In Access, if you want a query to list the ProductID, Category, and Description field values and sort the records by description within each category, how should you arrange the fields from left to right in the query design grid?
ProductID, Category, Description
A query includes the DeptID and DeptName fields from the Departments table and the EmployeeLast field from the Employees table. Both tables are shown below. What must you change before you can reassign the DeptID field value for a particular employee in the query results? Departments (DeptID, DeptName) Employees (EmpID, EmployeeFirst, EmployeeLast, DeptID)
Replace the DeptID field from the Departments table with the DeptID field from the Employees table
In relational algebra, which command retrieves rows from a table based on specified criteria?
SELECT
Based on the following notation, which relational algebra command would list all information from the table concerning the customer with numeric CustomerNum 260? Customer (CustomerNum, CustomerName, Street, City, State, PostalCode)
SELECT Customer WHERE CustomerNum=260 GIVING Answer
Based on the SQL shown, which query lists the name and balance of all customers with balances greater than or equal to $2,000 and less than or equal to $5,000?
SELECT CustomerName, Balance FROM Customers WHERE Balance BETWEEN 2000 AND 5000;
Based on the table description and SQL shown, which query lists the states in the Students table and displays the number of students from each state?Students (StudentID, FirstName, LastName, Street, City, State, PostalCode)
SELECT State, COUNT(*) AS CountOfState FROM Students GROUP BY State;
In an Access query, how can you list records in A-Z order by last name?
Set an ascending sort order on the LastName field.
Which of the following tables is most likely a lookup table?
Specialties (Specialty)
Which aggregate function do you use to total the values in a numeric field in a group of records?
Sum
A query includes the DeptID and DeptName fields from the Departments table and the EmployeeLast field from the Employees table. Both tables are shown below. What happens when you update the DeptName field value for an employee? Departments (DeptID, DeptName) Employees (EmpID, EmployeeFirst, EmployeeLast, DeptID)
The DeptName field value changes for all employees in that department
In general, what should be in place before you select fields from more than one table in Access Query Design View?
The tables should be related in a proper one-to-many relationships.
Which SQL commands do you use to update the values of an existing field?
UPDATE and SET
Which SQL keyword can you use to join two tables that have a one-to-many relationship?
WHERE
Which command joins the related Customers and Orders tables based on the common CustomerID field?
WHERE Customers.CustomerID = Orders.CustomerID
Which of the following expressions combines the atomic values from two fields in a LastName, FirstName format in an expression in an Access query?
[LastName] & ", " & [FirstName]
If you are creating a computed field that adds 10 percent to the UnitPrice field, how do you enter the UnitPrice field name in the expression?
[UnitPrice]
What type of functions are Count, Sum, Avg, Max, and Min?
aggregate
A(n) _____ is a field that contains unique data for each record in the table but is not used as the primary key field for security or other reasons.
alternate key
A(n) _____ is a piece of data that cannot be meaningfully divided.
atomic value
In an entity-relationship model (ERM), what information appears in ovals?
attributes
What do you call each key that meets the criteria for a primary key?
candidate key
In Database Design Language (DBDL), which items do you underline?
columns that make up the primary key
In an entity-relationship model (ERM) for a many-to-many relationship, what do you call the middle entity that links two other tables?
composite entity
When the primary key field consists of a combination of two or more fields, it is called a _____.
composite key
A ____________________ in a record is the result of a calculation using data from the rest of the record.
computed field
When you can determine the value of a field using information from existing fields in a record in a query, you should create a(n) _____.
computed field
Which of the following is a type of E-R diagram?
crow's foot notation
In Access a(n) _____ query permanently removes all the records from the selected table(s) that satisfy the criteria entered in the query.
delete
What type of query could you run to remove records with a product ID of 702 from the Products table?
delete query
A table is in second normal form when it is in first normal form and _____.
each nonkey column is dependent on the entire primary key field
Which fields should you index in a table?
fields that are commonly used for sorting
A table with no repeating groups in any attribute and atomic values in each column is, at a minimum, in what normal form?
first normal form
What do you call the field used to join two tables on the "many" side of a one-to-many relationship?
foreign key field
In the Access QBE, where do you specify criteria for a query?
in the Criteria row of the query design grid
How does the Access Relationships window indicate the "many" field in a one-to-many relationship?
infinity symbol
During which process do you typically create an entity-relationship (E-R) diagram?
information-level design
A(n) _____ results if you cannot add data to the database due to absence of other data.
insertion anomaly
A _____ serves on the "many" side of a one-to-many relationship with each of two other tables.
junction table
Because you cannot directly create a many-to-many relationship between two tables, what must you create to link the tables together?
junction table
A(n) _____ helps constrain the values in a single field to a specific list, which eliminates update anomalies.
lookup table
What type of Access query creates a new table using the query results?
make-table
What is a major goal of normalization?
minimize redundancy
In a database for a health care clinic, a medical specialty is listed only once in the Specialties table, but each specialty may be linked to many doctors in the Doctors table. What type of relationship do the Specialties and Doctors tables have?
one-to-many
Building the entities, attributes, constraints, and relationships in a particular RDBMS is called the _____.
physical level design
In Access, what uniquely identifies a row in a table?
primary key
Which field in a table does Access index by default?
primary key field
If you are sorting records by more than one field, which field is the first field that is evaluated for sorting purposes?
primary sort key
A _____ is a question about the data structured in a way that the DBMS can recognize and process.
query
A database that enforces _____ prevents users from creating orphan records.
referential integrity
A one-to-many relationship with _____ means that a record in the parent table must be established before a related record in the child table can be entered.
referential integrity
A(n) _____ can store information about multiple types of entities and the relationships among the entities.
relational database
The E-R model (ERM) represents entities, attributes, and _____.
relationships
A(n) _____ represents a field or combination of fields that may not be unique, but is commonly used for retrieval and thus should be indexed.
secondary key
In the following Database Design Language (DBDL) notation, what does SK stand for? Employees (EmpID, LastName, FirstName, SSN, DeptNum) AK SSN SK LastName, FirstName FK DeptNum à Departments
secondary key
The field you use to sort records is called the _____.
sort key
When you add or delete a table or change the structure of a table, where does the DBMS record these changes?
system catalog
Data stored in a single list often creates redundant data when _____.
the list contains multiple subjects or topics
The nonkey fields of a table should be functionally dependent on _____.
the primary key field
In an entity-relationship model (ERM), what does a diamond represent?
the relationship
A user view of data is _____.
the specific data needed by a person or process for a particular task
A(n) _____ occurs if a nonkey attribute determines another nonkey attribute.
transitive dependency
Which database term refers to a row in a table?
tuple
What do you call a table that contains multiple field entries in a single column?
unnormalized relation
1.What type of Access query changes data?
update
ProductType is a foreign key field in the Orders table and is constrained to a list of valid entries stored in the ProductTypes table. What problem does this design prevent?
update anomalies
What type of query should you create to populate new EmployeeFirst and EmployeeLast fields with atomic values from an EmployeeName field?
update query
