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?
Edit | Attach | Print version | History: r3 < r2 < r1 | Backlinks | Raw View | Raw edit | More topic actions...
Topic revision: r1 - 2013-10-14 - JimSkon
 
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