Database Exam 1 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

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.
• 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 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.
• 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

1. Redundancy wastes space because you are storing the same data in more than one place. ANS... Hide True
2. A spreadsheet’s data-sharing features allows multiple employees to update data in one spreadsheet at the same time. ANS... Hide False
3. An attribute is a characteristic or property of an entity. ANS... Hide True
4. A relationship is an association between attributes. ANS... Hide False
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 True
6. In a database, each entity has its own table. ANS... Hide True
7. The attributes of an entity become the rows in the table ANS... Hide False
8. In an entity-relationship (E-R) diagram, rectangles represent entities and lines represent relationships between connected entities. ANS... Hide True
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 False
10. Sharing data is one advantage of database processing. ANS... Hide True
11. Eliminating redundancy is always possible when using a database approach. ANS... Hide False
12. A database cannot be password protected to prevent unauthorized users from accessing the data. ANS... Hide False
13. There is a greater impact of failure in a nondatabase, file-oriented system. ANS... Hide False
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 False
15. To support all the complex functions that it provides to users, a DBMS must store data in multiple files. ANS... Hide False
16. A relational database handles entities, attributes, and relationships by storing each entity in its own table. ANS... Hide True
17. The attributes of an entity become the rows or records in a table. ANS... Hide False
18. A relation is essentially just a two-dimensional table. ANS... Hide True
19. In a relation, the order of the columns is immaterial. ANS... Hide True
20. In a relation, the order of rows is important. ANS... Hide False
21. A relational database is a collection of relations. ANS... Hide True
22. An unnormalized relation is a table that has more than one row. ANS... Hide False
23. The secondary key provides an important way of distinguishing one row in a table from another. ANS... Hide False
24. QBE is a visual approach to writing queries. ANS... Hide True
25. MySQL automatically adds double quotation marks around values in a SQL statement. ANS... Hide False
26. The comparison operators are +, *, %, and /. ANS... Hide False
27. The comparison operators are also known as relational operators. ANS... Hide True
28. In an OR criterion, the overall criterion is true if either of the individual criteria is true. ANS... Hide True
29. When a field name contains spaces or SQL reserved words, you must enclose the field name in curly braces ({}). ANS... Hide False
30. The concept of grouping means that statistics will be calculated for individual records. ANS... Hide False
31. Most relational DBMSs use a version of SQL as a data manipulation language. ANS... Hide True
32. To create numbers with decimals in MySQL , you can use the CURRENCY, NUMBER, or DECIMAL data types. ANS... Hide False
33. The rules for naming tables and columns are the same in every version of SQL. ANS... Hide False
34. CHAR data types are numbers without a decimal part. ANS... Hide False
35. Fields will appear in the query results in the order in which they are listed in the SELECT clause. ANS... Hide True
36. In a SELECT statement, the WHERE clause is mandatory. ANS... Hide False
37. Instead of listing all the field names in the SELECT clause, you can use the * symbol. ANS... Hide True
38. When you enter numbers in an SQL command, you do not type commas or dollar signs. ANS... Hide True
39. There are two versions of the “not equal to” operator: <> and =. ANS... Hide True
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 False
41. Preceding a condition by the NOT operator reverses the truth or falsity of the original condition. ANS... Hide True
42. The BETWEEN operator is an essential feature of SQL. ANS... Hide False
43. You can combine values in character fields as well as number fields. ANS... Hide True
44. The IN operator can be used instead or the OR operator in phrasing certain conditions. ANS... Hide True
45. In SQL, you use the SORT BY clause to display results in a desired order. ANS... Hide False
46. You can use the GROUP BY clause and the ORDER BY clause in the same SELECT statement. ANS... Hide True
47. The HAVING clause is to groups what the WHERE clause is to rows. ANS... Hide True
48. When rows are grouped, one line of output is produced for each group. ANS... Hide True
49. A WHERE and a HAVING clause cannot be included in the same query. ANS... Hide False
50. In SQL, you join tables by entering the appropriate conditions in the WHERE clause. ANS... Hide True
51. Views cannot be used to examine table data. ANS... Hide False
52. An individual can use a view to create reports, charts, and other objects that show database data. ANS... Hide True
53. The SELECT command that creates the view, which is called the view query, indicates what to include in the view. ANS... Hide False
54. To create a view in MySQL , you simply create and then save a query. ANS... Hide False
55. To change the field names in a view include the AS clause in the CREATE VIEW command. ANS... Hide True
56. One advantage of a view is that different users can view the same data in different ways. ANS... Hide True
57. Once you add an index to a database, you cannot delete it. ANS... Hide False
58. When you create an index whose key has a single field, you have created what is called a primary index. ANS... Hide False
59. The command DELETE INDEX CustList ; would remove the index named CustList . ANS... Hide False
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 True
61. The following command will revoke the ability to retrieve Customer records from user Jones:
    REVOKE GRANT ON Customer FROM Jones ; ANS... False True
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 True
63. Information about tables in the database is kept in the system cataloge. ANS... Hide True
64. A stored procedure is placed on a client computer. ANS... Hide False

Essay

1. List the advantages of database processing. ANS... Hide
   • 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
2. Explain why it is better to try to control redundancy rather than eliminate it. ANS... Hide 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.
3. Discuss how the database approach and the nondatabase approach differ in terms of ensuring the security of the database. ANS... Hide 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.
4. List the disadvantages of database processing. ANS... Hide Larger file size, Increased complexity, Greater impact of failure, More difficult recovery
5. Explain why the impact of failure is greater in database processing, compared with the nondatabase approach. ANS... Hide
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.
7. Provide a definition for the term relation. ANS... Hide 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
8. What is the difference between an AND criterion and an OR criterion? How is each criterion created in QBE? ANS... Hide 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.
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 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.
10. Discuss the difference between the major sort key and the minor sort key. ANS... Hide 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).
11. Explain what relational algebra is and how it is used. ANS... Hide 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.
12. What are some common restrictions place on table and column names by DBMSs? ANS... Hide 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.
13. Describe five data types that you will often encounter when working with databases. ANS... Hide 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.
    
14. Describe how to construct a detailed query in a step-by-step fashion. ANS... Hide 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.
15. Discuss the restriction concerning the structure of two tables involved in a union. ANS... Hide
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.
17. What are the advantages of having views only contain the fields required by a given user? ANS... Hide
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.
19. Under which conditions should you create an index on a field (or combination of fields)? ANS... Hide 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.
20. Explain what is meant by a null value. Is a null value the same as a value of zero? ANS... Hide 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.
21. What is the difference between entity integrity and referential integrity? ANS... Hide 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.