Difference: StudyGuideDB2013MT (1 vs. 2)

Revision 22013-10-22 - JimSkon

Line: 1 to 1
 
META TOPICPARENT name="CSC3032DatabaseManagement2013"

Database Midterm Study Guide

Chapter 1 Topics

Line: 17 to 17
 
  • A field name is qualified by preceding it with the table name and a period (for example, RepNum ).
  • A table’s primary key is the field or fields that uniquely identify a given row within the table.
  • Query-By-Example (QBE) is a visual tool for manipulating relational databases. QBE queries are created by completing on-screen forms.
Changed:
<
<
  • To include a field in an Access query, place the field in the design grid and make sure a check mark appears in the field’s Show check box.
  • To indicate criteria in an Access query, place the criteria in the appropriate columns in the design grid of the Query window.
  • To indicate AND criteria in an Access query, place both criteria in the same Criteria row of the design grid; to indicate OR criteria, place the criteria on separate Criteria rows of the design grid.
  • To create a computed field in Access, enter an appropriate expression in the desired column of the design grid.
  • To use functions to perform calculations in Access, include the appropriate function in the Total row for the appropriate column of the design grid.
  • To sort query results in Access, select Ascending or Descending in the Sort row for the field or fields that are sort keys.
>
>
  • To indicate criteria in an MySQL table search, select an appropriate operator, and enter an appropriate value beside the Column name.
  • To indicate AND criteria in an MySQL query, click the AND radio button below the first Column in the AND.
  • To sort query results in MySQL , pull down and select Asending or Desending for the desired column(s).
 
  • When sorting query results using more than one field, the leftmost sort key in the design grid is the major sort key (also called the primary sort key) and the sort key to its right is the minor sort key (also called the secondary sort key).
Changed:
<
<
  • To join tables in Access, place field lists for both tables in the upper pane of the Query window.
>
>
  • To join tables in MySQL , you must use the Visual Builder to associate the correct foreign and primary keys.
 
  • To make the same change to all records that satisfy certain criteria, use an update query.
  • To delete all records that satisfy certain criteria, use a delete query.
  • To save the results of a query as a table, use a make-table query.
Line: 61 to 58
 
  • The INTO clause is used in a SELECT command to create a table containing the results of the query.

Chapter 4

Changed:
<
<
  • Views are used to give each user his or her own view of the data in a database. In SQL, a defining query creates a view. When you enter a query that references a view, it is merged with the defining query to pro- duce the query that is actually executed. In Access, views are created by saving queries that select the data to use in the view.
>
>
  • Views are used to give each user his or her own view of the data in a database. In SQL, a defining query creates a view. When you enter a query that references a view, it is merged with the defining query to pro- duce the query that is actually executed.
 
  • Indexes are often used to facilitate data retrieval from the database. You can create an index on any field or combination of fields.
  • Security is provided in SQL systems by using the GRANT and REVOKE statements.
  • Entity integrity is the property that states that no field that is part of the primary key can accept null values.
Changed:
<
<
  • Referential integrity is the property that states that the value in any foreign key field must be null or must match an actual value in the primary key field of another table. Referential integrity is specified in SQL using the FOREIGN KEY clause. In Access, foreign keys are specified by creating relationships.
  • Legal-values integrity is the property that states that the value entered in a field must be one of the legal values that satisfies some particular condition. Legal-values integrity is specified in SQL using the CHECK clause. In Access, legal-values integrity is specified using validation rules.
  • The ALTER TABLE command allows you to add fields to a table, delete fields, orchange the characteristics of fields. In Access, you can change the structure of a table by making the desired changes in the table design.
  • The DROP TABLE command lets you delete a table from a database. In Access, you can delete a table by selecting the Delete command on the table’s shortcut menu in the Navigation Pane.
>
>
  • Referential integrity is the property that states that the value in any foreign key field must be null or must match an actual value in the primary key field of another table. Referential integrity is specified in SQL using the FOREIGN KEY clause. In MySQL , foreign keys are specified by creating relationships.
  • Legal-values integrity is the property that states that the value entered in a field must be one of the legal values that satisfies some particular condition. Legal-values integrity is specified in SQL using the CHECK clause. In MySQL , legal-values integrity is specified using validation rules.
  • The ALTER TABLE command allows you to add fields to a table, delete fields, orchange the characteristics of fields. In MySQL , you can change the structure of a table by making the desired changes in the table design.
  • The DROP TABLE command lets you delete a table from a database. In MySQL , you can delete a table by selecting the Drop command after selecting the table.
 
  • The system catalog is a feature of many relational DBMSs that stores information about the structure of a database. The system updates the catalog automatically. Each DBMS includes features to produce docu- mentation of the database structure using the information in the catalog.
  • A stored procedure is a query saved in a file that users can execute later.
Changed:
<
<
  • A trigger is an action that occurs automatically in response to an associated database operation such as an INSERT, UPDATE, or DELETE command. Like a stored procedure, a trigger is stored and compiled on the server. Unlike a stored procedure, which is executed in response to a user request, a trigger is executed in response to a command that causes the associated database operation to occur. Access provides the functionality of triggers through the use of data macros.
>
>
  • A trigger is an action that occurs automatically in response to an associated database operation such as an INSERT, UPDATE, or DELETE command. Like a stored procedure, a trigger is stored and compiled on the server. Unlike a stored procedure, which is executed in response to a user request, a trigger is executed in response to a command that causes the associated database operation to occur.
 

Sample Questions

True or False
Changed:
<
<
  1. Redundancy wastes space because you are storing the same data in more than one place.
  2. A spreadsheet’s data-sharing features allows multiple employees to update data in one spreadsheet at the same time.
  3. An attribute is a characteristic or property of an entity.
  4. A relationship is an association between attributes.
  5. A database will not only hold information about multiple types of entities, but also information about the relationships among these multiple entities.
  6. In a database, each entity has its own table.
  7. The attributes of an entity become the rows in the table
  8. In an entity-relationship (E-R) diagram, rectangles represent entities and lines represent relationships between connected entities.
  9. Programs created with Visual Basic, Java, Perl, PHP, or C++ can access the database directly, rather than having to access it through the DBMS.
  10. Sharing data is one advantage of database processing.
  11. Eliminating redundancy is always possible when using a database approach.
  12. A database cannot be password protected to prevent unauthorized users from accessing the data.
  13. There is a greater impact of failure in a nondatabase, file-oriented system.
  14. A good DBMS provides integrity constraints, which are features that let you change the structure of the database without changing the programs that access the database.
  15. To support all the complex functions that it provides to users, a DBMS must store data in multiple files.
  16. A relational database handles entities, attributes, and relationships by storing each entity in its own table.
  17. The attributes of an entity become the rows or records in a table.
  18. A relation is essentially just a two-dimensional table.
  19. In a relation, the order of the columns is immaterial.
  20. In a relation, the order of rows is important.
  21. A relational database is a collection of relations.
  22. An unnormalized relation is a table that has more than one row.
  23. The secondary key provides an important way of distinguishing one row in a table from another.
  24. QBE is a visual approach to writing queries.
  25. Access automatically adds double quotation marks around values in the design grid that are formatted as Text fields when you run the query or move the insertion point to another cell in the design grid.
  26. The comparison operators are +, *, %, and /.
  27. The comparison operators are also known as relational operators.
  28. In an OR criterion, the overall criterion is true if either of the individual criteria is true.
  29. When a field name contains spaces or SQL reserved words, you must enclose the field name in curly braces ({}).
  30. The concept of grouping means that statistics will be calculated for individual records.
  31. Most relational DBMSs use a version of SQL as a data manipulation language.
  32. To create numbers with decimals in Access, you can use the CURRENCY, NUMBER, or DECIMAL data types.
  33. The rules for naming tables and columns are the same in every version of SQL.
  34. CHAR data types are numbers without a decimal part.
  35. Fields will appear in the query results in the order in which they are listed in the SELECT clause.
  36. In a SELECT statement, the WHERE clause is mandatory.
  37. Instead of listing all the field names in the SELECT clause, you can use the * symbol.
  38. When you enter numbers in an SQL command, you do not type commas or dollar signs.
  39. There are two versions of the “not equal to” operator: <> and =.
  40. When a query involves a character field, such as CustomerName , you must enclose the value to which the field is being compared in percent symbols (%).
  41. Preceding a condition by the NOT operator reverses the truth or falsity of the original condition.
  42. The BETWEEN operator is an essential feature of SQL.
  43. You can combine values in character fields as well as number fields.
  44. The IN operator can be used instead or the OR operator in phrasing certain conditions.
  45. In SQL, you use the SORT BY clause to display results in a desired order.
  46. You can use the GROUP BY clause and the ORDER BY clause in the same SELECT statement.
  47. The HAVING clause is to groups what the WHERE clause is to rows.
  48. When rows are grouped, one line of output is produced for each group.
  49. A WHERE and a HAVING clause cannot be included in the same query.
  50. In SQL, you join tables by entering the appropriate conditions in the WHERE clause.
  51. Views cannot be used to examine table data.
  52. An individual can use a view to create reports, charts, and other objects that show database data.
  53. The SELECT command that creates the view, which is called the view query, indicates what to include in the view.
  54. To create a view in Access, you simply create and then save a query.
  55. To change the field names in a view include the AS clause in the CREATE VIEW command.
  56. One advantage of a view is that different users can view the same data in different ways.
  57. Once you add an index to a database, you cannot delete it.
  58. When you create an index whose key has a single field, you have created what is called a primary index.
  59. The command DELETE INDEX CustList ; would remove the index named CustList .
  60. The following command will enable Jones to retrieve data from the Customer table, but not to take any other action: GRANT SELECT ON Customer TO Jones ;
  61. The following command will revoke the ability to retrieve Customer records from user Jones:
    REVOKE GRANT ON Customer FROM Jones ;
  62. Legal-values integrity is the property that states that no record can exist in the database with a value in the field other than one of the legal values.
  63. Information about tables in the database is kept in the system cataloge
  64. A stored procedure is placed on a client computer.
>
>
  1. Redundancy wastes space because you are storing the same data in more than one place. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  2. A spreadsheet’s data-sharing features allows multiple employees to update data in one spreadsheet at the same time. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  3. An attribute is a characteristic or property of an entity. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  4. A relationship is an association between attributes. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  5. A database will not only hold information about multiple types of entities, but also information about the relationships among these multiple entities. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  6. In a database, each entity has its own table. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  7. The attributes of an entity become the rows in the table ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  8. In an entity-relationship (E-R) diagram, rectangles represent entities and lines represent relationships between connected entities. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  9. Programs created with Visual Basic, Java, Perl, PHP, or C++ can access the database directly, rather than having to access it through the DBMS. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  10. Sharing data is one advantage of database processing. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  11. Eliminating redundancy is always possible when using a database approach. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  12. A database cannot be password protected to prevent unauthorized users from accessing the data. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  13. There is a greater impact of failure in a nondatabase, file-oriented system. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  14. A good DBMS provides integrity constraints, which are features that let you change the structure of the database without changing the programs that access the database. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  15. To support all the complex functions that it provides to users, a DBMS must store data in multiple files. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  16. A relational database handles entities, attributes, and relationships by storing each entity in its own table. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  17. The attributes of an entity become the rows or records in a table. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  18. A relation is essentially just a two-dimensional table. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  19. In a relation, the order of the columns is immaterial. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  20. In a relation, the order of rows is important. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  21. A relational database is a collection of relations. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  22. An unnormalized relation is a table that has more than one row. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  23. The secondary key provides an important way of distinguishing one row in a table from another. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  24. QBE is a visual approach to writing queries. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  25. MySQL automatically adds double quotation marks around values in a SQL statement. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  26. The comparison operators are +, *, %, and /. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  27. The comparison operators are also known as relational operators. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  28. In an OR criterion, the overall criterion is true if either of the individual criteria is true. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  29. When a field name contains spaces or SQL reserved words, you must enclose the field name in curly braces ({}). ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  30. The concept of grouping means that statistics will be calculated for individual records. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  31. Most relational DBMSs use a version of SQL as a data manipulation language. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  32. To create numbers with decimals in MySQL , you can use the CURRENCY, NUMBER, or DECIMAL data types. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  33. The rules for naming tables and columns are the same in every version of SQL. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  34. CHAR data types are numbers without a decimal part. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  35. Fields will appear in the query results in the order in which they are listed in the SELECT clause. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  36. In a SELECT statement, the WHERE clause is mandatory. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  37. Instead of listing all the field names in the SELECT clause, you can use the * symbol. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  38. When you enter numbers in an SQL command, you do not type commas or dollar signs. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  39. There are two versions of the “not equal to” operator: <> and =. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  40. When a query involves a character field, such as CustomerName , you must enclose the value to which the field is being compared in percent symbols (%). ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  41. Preceding a condition by the NOT operator reverses the truth or falsity of the original condition. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  42. The BETWEEN operator is an essential feature of SQL. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  43. You can combine values in character fields as well as number fields. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  44. The IN operator can be used instead or the OR operator in phrasing certain conditions. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  45. In SQL, you use the SORT BY clause to display results in a desired order. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  46. You can use the GROUP BY clause and the ORDER BY clause in the same SELECT statement. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  47. The HAVING clause is to groups what the WHERE clause is to rows. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  48. When rows are grouped, one line of output is produced for each group. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  49. A WHERE and a HAVING clause cannot be included in the same query. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  50. In SQL, you join tables by entering the appropriate conditions in the WHERE clause. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  51. Views cannot be used to examine table data. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  52. An individual can use a view to create reports, charts, and other objects that show database data. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  53. The SELECT command that creates the view, which is called the view query, indicates what to include in the view. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  54. To create a view in MySQL , you simply create and then save a query. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  55. To change the field names in a view include the AS clause in the CREATE VIEW command. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  56. One advantage of a view is that different users can view the same data in different ways. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  57. Once you add an index to a database, you cannot delete it. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  58. When you create an index whose key has a single field, you have created what is called a primary index. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  59. The command DELETE INDEX CustList ; would remove the index named CustList . ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
  60. The following command will enable Jones to retrieve data from the Customer table, but not to take any other action: GRANT SELECT ON Customer TO Jones ; ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  61. The following command will revoke the ability to retrieve Customer records from user Jones:
    REVOKE GRANT ON Customer FROM Jones ; ANS... False
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  62. Legal-values integrity is the property that states that no record can exist in the database with a value in the field other than one of the legal values. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  63. Information about tables in the database is kept in the system cataloge. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    True
    <--/twistyPlugin-->
  64. A stored procedure is placed on a client computer. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    False
    <--/twistyPlugin-->
 
Essay
Changed:
<
<
  1. List the advantages of database processing.
  2. Explain why it is better to try to control redundancy rather than eliminate it.
  3. Discuss how the database approach and the nondatabase approach differ in terms of ensuring the security of the database.
  4. List the disadvantages of database processing.
  5. Explain why the impact of failure is greater in database processing, compared with the nondatabase approach.
  6. Provide a definition for the term relation.
  7. What is the difference between an AND criterion and an OR criterion? How is each criterion created in QBE?
  8. List at least six of the aggregate functions available in Access. Explain how to use any of these functions in a query.
  9. Discuss the difference between the major sort key and the minor sort key.
  10. Explain what relational algebra is and how it is used.
  11. What are some common restrictions place on table and column names by DBMSs?
  12. Describe five data types that you will often encounter when working with databases.
  13. How does the format for dates in queries in Access differ from other implementations of SQL?
  14. Describe how to construct a detailed query in a step-by-step fashion.
  15. Discuss the restriction concerning the structure of two tables involved in a union.
  16. What are the advantages of having views only contain the fields required by a given user?
  17. Under which conditions should you create an index on a field (or combination of fields)?
  18. Explain what is meant by a null value. Is a null value the same as a value of zero?
  19. What is the difference between entity integrity and referential integrity?
>
>
  1. List the advantages of database processing. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    • Getting more information from the same amount of data
    • Sharing data
    • Balancing conflicting requirements
    • Controlling redundancy
    • Facilitating consistency
    • Improving integrity
    • Expanding security
    • Increasing productivity
    • Providing data independence
      <--/twistyPlugin-->
  2. Explain why it is better to try to control redundancy rather than eliminate it. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    Although eliminating redundancy is the ideal, it is not always possible. Sometimes, for reasons having to do with performance, you might choose to introduce a limited amount of redundancy into a database. However, even in these cases, you would be able to keep the redundancy under tight control, thus obtaining the same advantages. This is why it is better to say that you control redundancy rather than eliminate it.
    <--/twistyPlugin-->
  3. Discuss how the database approach and the nondatabase approach differ in terms of ensuring the security of the database. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    A DBMS has many features that help ensure the enforcement of security measures. For example, a DBA can assign passwords to authorized users; then only those users who enter an acceptable password can gain access to the data in the database. Further, a DBMS lets you assign users to groups, with some groups permitted to view and update data in the database and other groups permitted only to view certain data in the database. With the nondatabase approach, you have limited security features and are more vulnerable to intentional and accidental access and changes to data.
    <--/twistyPlugin-->
  4. List the disadvantages of database processing. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    Larger file size, Increased complexity, Greater impact of failure, More difficult recovery
    <--/twistyPlugin-->
  5. Explain why the impact of failure is greater in database processing, compared with the nondatabase approach. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
  6. In a nondatabase, file-oriented system, each user has a completely separate system; the failure of any single user’s system does not necessarily affect any other user. On the other hand, if several users are sharing the same database, a failure on the part of any one user that damages the database in some way might affect all the other users.
    <--/twistyPlugin-->
  7. Provide a definition for the term relation. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    A relation is a two-dimensional table in which: 1. The entries in the table are single-valued; that is, each location in the table contains a single entry. 2. Each column has a distinct name 3. All values in a column are values of the same attributes 4. The order of columns is immaterial 5. Each row is distinct 6. The order of rows is immaterial
    <--/twistyPlugin-->
  8. What is the difference between an AND criterion and an OR criterion? How is each criterion created in QBE? ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    In an AND criterion, both criteria must be true for the compound criterion to be true. In an OR criterion, the overall criterion is true if either of the individual criteria is true. In QBE, to create an AND criterion, place the criteria for multiple fields on the same Criteria row in the design grid; to create an OR criterion, place the criteria for multiple fields on different Criteria rows in the design grid.
    <--/twistyPlugin-->
  9. List at least six of the aggregate functions available in SQL. Explain how to use any of these functions in a query. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    All products that support SQL, including Access, support the following built-in functions: Count, Sum, Avg (average), Max (largest value), Min (smallest value), StDev (standard deviation), Var (variance), First, and Last. To use any of these functions in a query, you include them in the Total row for the desired column in the design grid. By default, the Total row does not appear automatically in the design grid. To include it, you must click the Totals button in the Show/Hide group on the Query Tools Design tab.
    <--/twistyPlugin-->
  10. Discuss the difference between the major sort key and the minor sort key. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    To list the records in query results in a particular way, you need to sort the records. The field on which records are sorted is called the sort key; you can sort records using more than one field when necessary. When you are sorting records by more than one field (such as sorting by rep number and then by customer name), the first sort field (RepNum ) is called the major sort key (also called the primary sort key) and the second sort field (CustomerName ) is called the minor sort key (also called the secondary sort key).
    <--/twistyPlugin-->
  11. Explain what relational algebra is and how it is used. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    Relational algebra is a theoretical way of manipulating a relational database. Relational algebra includes operations that act on existing tables to produce new tables, similar to the way the operations of addition and subtraction act on numbers to produce new numbers in the mathematical algebra with which you are familiar. Retrieving data from a relational database through the use of relational algebra involves issuing relational algebra commands to operate on existing tables to form a new table containing the desired information. Sometimes you might need to execute a series of commands to obtain the desired result.
    <--/twistyPlugin-->
  12. What are some common restrictions place on table and column names by DBMSs? ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    Some common restrictions are: 1. The names cannot exceed 18 characters. 2. The names must start with a letter. 3. The names can contain only letters, numbers, and underscores. 4. The names cannot contain spaces.
    <--/twistyPlugin-->
  13. Describe five data types that you will often encounter when working with databases. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    INTEGER: Stores integers, which are numbers without decimal places. SMALLINT: Stores integers, but uses less space than the INTEGER data type. DECIMAL(p,q): Stores a decimal number p digits long with q of these digits being decimal places. CHAR(n): Stores a character string n characters long. DATE: Stores dates.
    <--/twistyPlugin-->
  14. Describe how to construct a detailed query in a step-by-step fashion. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    To construct a detailed query in a step-by-step fashion, do the following: 1. List in the SELECT clause all the columns you want to display. If the name of a column appears in more than one table, precede the column name with the table name. 2. List in the FROM clause all the tables involved in the query. Usually you include the tables that contain the columns listed in the SELECT clause. Occasionally, however, there might be a table that does not contain any columns used in the SELECT clause but that does contain columns used in the WHERE clause. In this case, you must also list the table. 3. Take one pair of related tables at a time and indicate in the WHERE clause the condition that relates the tables. Join these conditions with the AND operator. When there are other conditions, include them in the WHERE clause and connect them to the other conditions with the AND operator.
    <--/twistyPlugin-->
  15. Discuss the restriction concerning the structure of two tables involved in a union. ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
  16. The two tables involved in a union must have the same structure, or be union compatible; in other words, they must have the same number of fields and their corresponding fields must have the same data types. If, for example, the first field in one table contains customer numbers, the first field in the other table also must contain customer numbers.
    <--/twistyPlugin-->
  17. What are the advantages of having views only contain the fields required by a given user? ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
  18. This practice has two advantages. First, because the view will, in all probability, contain fewer fields than the overall database and the view is conceptually a single table, rather than a collection of tables, it greatly simplifies the user’s perception of the database. Second, views provide a measure of security. Fields that are not included in the view are not accessible to the view’s user. Likewise, rows that are not included in the view are not accessible.
    <--/twistyPlugin-->
  19. Under which conditions should you create an index on a field (or combination of fields)? ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    You should create an index on a field (or combination of fields) when one or more of the following conditions exist: 1. The field is the primary key of the table. 2. The field is the foreign key in a relationship you have created.3. You will frequently use the field as a sort field. 4. You will frequently need to locate a record based on a value in this field.
    <--/twistyPlugin-->
  20. Explain what is meant by a null value. Is a null value the same as a value of zero? ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    Essentially, setting the value in a given field to null is similar to not entering a value in the field at all. Nulls are used when a value is missing, unknown, or inapplicable. It is not the same as a blank or zero value, both of which are actual values. For example, a value of zero in the Balance field for a particular customer indicates that the customer has a zero balance. A value of null in a customer’s Balance field, on the other hand, indicates that, for whatever reason, the customer’s balance is unknown.
    <--/twistyPlugin-->
  21. What is the difference between entity integrity and referential integrity? ANS... Hide
    <--/twistyPlugin twikiMakeVisibleInline-->
    Entity integrity is the rule that no field that is part of the primary key may accept null values. Entity integrity guarantees that each record will indeed have its own identity. In other words, preventing the primary key from accepting null values ensures that you can distinguish one record from another. Referential integrity is the rule that if table A contains a foreign key that matches the primary key of table B, the values of this foreign key must either match the value of the primary key for some row in table B, or be null.
    <--/twistyPlugin-->

Revision 12013-10-14 - JimSkon

Line: 1 to 1
Added:
>
>
META TOPICPARENT name="CSC3032DatabaseManagement2013"

Database Midterm Study Guide

Chapter 1 Topics

  • Problems with nondatabase approaches to data management include redundancy, difficulties accessing related data, limited security features, limited data sharing features, and potential size limitations.
  • An entity is a person, place, object, event, oridea for which you want to store and process data. An attribute,field, or column is a characteristic orproperty of an entity. A relationship is an association between entities.
  • A one-to-many relationship between two entities exists when each occurrence of the first entity is related to many occurrences of the second entity and each occurrence of the second entity is related to only one occurrence of the first entity.
  • A database is a structure that can store information about multiple types of entities, the attributes of the entities, and the relationships among the entities.
  • An entity-relationship (E-R) diagram represents a database visually by using a rectangle for each entity that includes the entity’s name above the rectangle and the entity’s columns inside the rectangle, using a line to connect two entities that have a relationship, and placing a dot at the end of a line to indicate the “many” part of a one-to-many relationship.
  • A database management system (DBMS) is a program, or a collection of programs, through which users interact with a database. DBMSs let you create forms and reports quickly and easily, as well as obtain answers to questions about the data stored in a database.
  • Database processing offers the following advantages: getting more information from the same amount of data, sharing data, balancing conflicting requirements, controlling redundancy, facilitating consistency, improving integrity, expanding security, increasing productivity, and providing data independence. The disadvantages of database processing include the following: larger file size, increased complexity, greater impact of failure, and more difficult recovery.

Chapter 2 Topics

  • A relation is a two-dimensional table in which the entries are single-valued, each field has a distinct name, all the values in a field are values of the same attribute (the one identified by the field name), the order of fields is immaterial, each row is distinct, and the order of rows is immaterial.
  • A relational database is a collection of relations
  • An unnormalized relation is a structure in which entries need not be single-valued but that satisfies all the other properties of a relation.
  • A field name is qualified by preceding it with the table name and a period (for example, RepNum ).
  • A table’s primary key is the field or fields that uniquely identify a given row within the table.
  • Query-By-Example (QBE) is a visual tool for manipulating relational databases. QBE queries are created by completing on-screen forms.
  • To include a field in an Access query, place the field in the design grid and make sure a check mark appears in the field’s Show check box.
  • To indicate criteria in an Access query, place the criteria in the appropriate columns in the design grid of the Query window.
  • To indicate AND criteria in an Access query, place both criteria in the same Criteria row of the design grid; to indicate OR criteria, place the criteria on separate Criteria rows of the design grid.
  • To create a computed field in Access, enter an appropriate expression in the desired column of the design grid.
  • To use functions to perform calculations in Access, include the appropriate function in the Total row for the appropriate column of the design grid.
  • To sort query results in Access, select Ascending or Descending in the Sort row for the field or fields that are sort keys.
  • When sorting query results using more than one field, the leftmost sort key in the design grid is the major sort key (also called the primary sort key) and the sort key to its right is the minor sort key (also called the secondary sort key).
  • To join tables in Access, place field lists for both tables in the upper pane of the Query window.
  • To make the same change to all records that satisfy certain criteria, use an update query.
  • To delete all records that satisfy certain criteria, use a delete query.
  • To save the results of a query as a table, use a make-table query.
  • Relational algebra is a theoretical method of manipulating relational databases.
  • The SELECT command in relational algebra selects only certain rows from a table.
  • The PROJECT command in relational algebra selects only certain columns from a table.
  • The JOIN command in relational algebra combines data from two or more tables based on common columns.
  • The UNION command in relational algebra forms the union of two tables. For a union operation to make
  • sense, the tables must be union compatible.
  • Two tables are union compatible when they have the same number of columns and their corresponding columns represent the same type of data.
  • The INTERSECT command in relational algebra forms the intersection of two tables.
  • The SUBTRACT command in relational algebra forms the difference of two tables.
  • The product of two tables (mathematically called the Cartesian product) is the table obtained by concatenating every row in the first table with every row in the second table.
  • The division process in relational algebra divides one table by another table.

Chapter 3

  • Structured Query Language (SQL) is a language that is used to manipulate relational databases.
  • The basic form of an SQL query is SELECT-FROM-WHERE.
  • Use the CREATE TABLE command to describe a table’s layout to the DBMS, which creates the table in the database.
  • In SQL retrieval commands, fields are listed in the SELECT clause, tables are listed in the FROM clause, and conditions are listed in the WHERE clause.
  • In conditions, character values must be enclosed in single quotation marks.
  • Compound conditions are formed by combining simple conditions using either or both of the following
  • operators: AND and OR.
  • Sorting is accomplished using the ORDER BY clause. The field on which the records are sorted is called the sort key. When the data is sorted in more than one field, the more important field is called the major sort key or primary sort key. The less important field is called the minor sort key or secondary sort key.
  • Grouping is accomplished in SQL by using the GROUP BY clause. To restrict the rows to be displayed, use the HAVING clause.
  • Joining tables is accomplished in SQL by using a condition that relates matching rows in the tables to be joined.
  • SQL has the built-in (also called aggregate) functions COUNT, SUM, AVG, MAX, and MIN.
  • One SQL query can be placed inside another. The subquery is evaluated first.
  • The union of the results of two queries is specified by placing the UNION operator between the two queries.
  • Computed fields are specified in SQL queries by including the expression, followed by the word AS, fol- lowed by the name of the computed field.
  • The INSERT command is used to add a new row to a table.
  • The UPDATE command is used to change existing data.
  • The DELETE command is used to delete records.
  • The INTO clause is used in a SELECT command to create a table containing the results of the query.

Chapter 4

  • Views are used to give each user his or her own view of the data in a database. In SQL, a defining query creates a view. When you enter a query that references a view, it is merged with the defining query to pro- duce the query that is actually executed. In Access, views are created by saving queries that select the data to use in the view.
  • Indexes are often used to facilitate data retrieval from the database. You can create an index on any field or combination of fields.
  • Security is provided in SQL systems by using the GRANT and REVOKE statements.
  • Entity integrity is the property that states that no field that is part of the primary key can accept null values.
  • Referential integrity is the property that states that the value in any foreign key field must be null or must match an actual value in the primary key field of another table. Referential integrity is specified in SQL using the FOREIGN KEY clause. In Access, foreign keys are specified by creating relationships.
  • Legal-values integrity is the property that states that the value entered in a field must be one of the legal values that satisfies some particular condition. Legal-values integrity is specified in SQL using the CHECK clause. In Access, legal-values integrity is specified using validation rules.
  • The ALTER TABLE command allows you to add fields to a table, delete fields, orchange the characteristics of fields. In Access, you can change the structure of a table by making the desired changes in the table design.
  • The DROP TABLE command lets you delete a table from a database. In Access, you can delete a table by selecting the Delete command on the table’s shortcut menu in the Navigation Pane.
  • The system catalog is a feature of many relational DBMSs that stores information about the structure of a database. The system updates the catalog automatically. Each DBMS includes features to produce docu- mentation of the database structure using the information in the catalog.
  • A stored procedure is a query saved in a file that users can execute later.
  • A trigger is an action that occurs automatically in response to an associated database operation such as an INSERT, UPDATE, or DELETE command. Like a stored procedure, a trigger is stored and compiled on the server. Unlike a stored procedure, which is executed in response to a user request, a trigger is executed in response to a command that causes the associated database operation to occur. Access provides the functionality of triggers through the use of data macros.

Sample Questions

True or False
  1. Redundancy wastes space because you are storing the same data in more than one place.
  2. A spreadsheet’s data-sharing features allows multiple employees to update data in one spreadsheet at the same time.
  3. An attribute is a characteristic or property of an entity.
  4. A relationship is an association between attributes.
  5. A database will not only hold information about multiple types of entities, but also information about the relationships among these multiple entities.
  6. In a database, each entity has its own table.
  7. The attributes of an entity become the rows in the table
  8. In an entity-relationship (E-R) diagram, rectangles represent entities and lines represent relationships between connected entities.
  9. Programs created with Visual Basic, Java, Perl, PHP, or C++ can access the database directly, rather than having to access it through the DBMS.
  10. Sharing data is one advantage of database processing.
  11. Eliminating redundancy is always possible when using a database approach.
  12. A database cannot be password protected to prevent unauthorized users from accessing the data.
  13. There is a greater impact of failure in a nondatabase, file-oriented system.
  14. A good DBMS provides integrity constraints, which are features that let you change the structure of the database without changing the programs that access the database.
  15. To support all the complex functions that it provides to users, a DBMS must store data in multiple files.
  16. A relational database handles entities, attributes, and relationships by storing each entity in its own table.
  17. The attributes of an entity become the rows or records in a table.
  18. A relation is essentially just a two-dimensional table.
  19. In a relation, the order of the columns is immaterial.
  20. In a relation, the order of rows is important.
  21. A relational database is a collection of relations.
  22. An unnormalized relation is a table that has more than one row.
  23. The secondary key provides an important way of distinguishing one row in a table from another.
  24. QBE is a visual approach to writing queries.
  25. Access automatically adds double quotation marks around values in the design grid that are formatted as Text fields when you run the query or move the insertion point to another cell in the design grid.
  26. The comparison operators are +, *, %, and /.
  27. The comparison operators are also known as relational operators.
  28. In an OR criterion, the overall criterion is true if either of the individual criteria is true.
  29. When a field name contains spaces or SQL reserved words, you must enclose the field name in curly braces ({}).
  30. The concept of grouping means that statistics will be calculated for individual records.
  31. Most relational DBMSs use a version of SQL as a data manipulation language.
  32. To create numbers with decimals in Access, you can use the CURRENCY, NUMBER, or DECIMAL data types.
  33. The rules for naming tables and columns are the same in every version of SQL.
  34. CHAR data types are numbers without a decimal part.
  35. Fields will appear in the query results in the order in which they are listed in the SELECT clause.
  36. In a SELECT statement, the WHERE clause is mandatory.
  37. Instead of listing all the field names in the SELECT clause, you can use the * symbol.
  38. When you enter numbers in an SQL command, you do not type commas or dollar signs.
  39. There are two versions of the “not equal to” operator: <> and =.
  40. When a query involves a character field, such as CustomerName , you must enclose the value to which the field is being compared in percent symbols (%).
  41. Preceding a condition by the NOT operator reverses the truth or falsity of the original condition.
  42. The BETWEEN operator is an essential feature of SQL.
  43. You can combine values in character fields as well as number fields.
  44. The IN operator can be used instead or the OR operator in phrasing certain conditions.
  45. In SQL, you use the SORT BY clause to display results in a desired order.
  46. You can use the GROUP BY clause and the ORDER BY clause in the same SELECT statement.
  47. The HAVING clause is to groups what the WHERE clause is to rows.
  48. When rows are grouped, one line of output is produced for each group.
  49. A WHERE and a HAVING clause cannot be included in the same query.
  50. In SQL, you join tables by entering the appropriate conditions in the WHERE clause.
  51. Views cannot be used to examine table data.
  52. An individual can use a view to create reports, charts, and other objects that show database data.
  53. The SELECT command that creates the view, which is called the view query, indicates what to include in the view.
  54. To create a view in Access, you simply create and then save a query.
  55. To change the field names in a view include the AS clause in the CREATE VIEW command.
  56. One advantage of a view is that different users can view the same data in different ways.
  57. Once you add an index to a database, you cannot delete it.
  58. When you create an index whose key has a single field, you have created what is called a primary index.
  59. The command DELETE INDEX CustList ; would remove the index named CustList .
  60. The following command will enable Jones to retrieve data from the Customer table, but not to take any other action: GRANT SELECT ON Customer TO Jones ;
  61. The following command will revoke the ability to retrieve Customer records from user Jones:
    REVOKE GRANT ON Customer FROM Jones ;
  62. Legal-values integrity is the property that states that no record can exist in the database with a value in the field other than one of the legal values.
  63. Information about tables in the database is kept in the system cataloge
  64. A stored procedure is placed on a client computer.
Essay
  1. List the advantages of database processing.
  2. Explain why it is better to try to control redundancy rather than eliminate it.
  3. Discuss how the database approach and the nondatabase approach differ in terms of ensuring the security of the database.
  4. List the disadvantages of database processing.
  5. Explain why the impact of failure is greater in database processing, compared with the nondatabase approach.
  6. Provide a definition for the term relation.
  7. What is the difference between an AND criterion and an OR criterion? How is each criterion created in QBE?
  8. List at least six of the aggregate functions available in Access. Explain how to use any of these functions in a query.
  9. Discuss the difference between the major sort key and the minor sort key.
  10. Explain what relational algebra is and how it is used.
  11. What are some common restrictions place on table and column names by DBMSs?
  12. Describe five data types that you will often encounter when working with databases.
  13. How does the format for dates in queries in Access differ from other implementations of SQL?
  14. Describe how to construct a detailed query in a step-by-step fashion.
  15. Discuss the restriction concerning the structure of two tables involved in a union.
  16. What are the advantages of having views only contain the fields required by a given user?
  17. Under which conditions should you create an index on a field (or combination of fields)?
  18. Explain what is meant by a null value. Is a null value the same as a value of zero?
  19. What is the difference between entity integrity and referential integrity?
 
This site is powered by the TWiki collaboration platformCopyright &© by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback