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 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).
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.
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.
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
Redundancy wastes space because you are storing the same data in more than one place. ANS...HideTrue
A spreadsheet’s data-sharing features allows multiple employees to update data in one spreadsheet at the same time. ANS...HideFalse
An attribute is a characteristic or property of an entity. ANS...HideTrue
A relationship is an association between attributes.ANS...HideFalse
A database will not only hold information about multiple types of entities, but also information about the relationships among these multiple entities.ANS...HideTrue
In a database, each entity has its own table.ANS...HideTrue
The attributes of an entity become the rows in the tableANS...HideFalse
In an entity-relationship (E-R) diagram, rectangles represent entities and lines represent relationships between connected entities.ANS...HideTrue
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...HideFalse
Sharing data is one advantage of database processing.ANS...HideTrue
Eliminating redundancy is always possible when using a database approach.ANS...HideFalse
A database cannot be password protected to prevent unauthorized users from accessing the data.ANS...HideFalse
There is a greater impact of failure in a nondatabase, file-oriented system.ANS...HideFalse
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...HideFalse
To support all the complex functions that it provides to users, a DBMS must store data in multiple files.ANS...HideFalse
A relational database handles entities, attributes, and relationships by storing each entity in its own table.ANS...HideTrue
The attributes of an entity become the rows or records in a table.ANS...HideFalse
A relation is essentially just a two-dimensional table.ANS...HideTrue
In a relation, the order of the columns is immaterial.ANS...HideTrue
In a relation, the order of rows is important.ANS...HideFalse
A relational database is a collection of relations.ANS...HideTrue
An unnormalized relation is a table that has more than one row.ANS...HideFalse
The secondary key provides an important way of distinguishing one row in a table from another.ANS...HideFalse
QBE is a visual approach to writing queries.ANS...HideTrue
MySQL automatically adds double quotation marks around values in a SQL statement.ANS...HideFalse
The comparison operators are +, *, %, and /.ANS...HideFalse
The comparison operators are also known as relational operators.ANS...HideTrue
In an OR criterion, the overall criterion is true if either of the individual criteria is true.ANS...HideTrue
When a field name contains spaces or SQL reserved words, you must enclose the field name in curly braces ({}).ANS...HideFalse
The concept of grouping means that statistics will be calculated for individual records.ANS...HideFalse
Most relational DBMSs use a version of SQL as a data manipulation language.ANS...HideTrue
To create numbers with decimals in MySQL , you can use the CURRENCY, NUMBER, or DECIMAL data types.ANS...HideFalse
The rules for naming tables and columns are the same in every version of SQL.ANS...HideFalse
CHAR data types are numbers without a decimal part.ANS...HideFalse
Fields will appear in the query results in the order in which they are listed in the SELECT clause.ANS...HideTrue
In a SELECT statement, the WHERE clause is mandatory.ANS...HideFalse
Instead of listing all the field names in the SELECT clause, you can use the * symbol.ANS...HideTrue
When you enter numbers in an SQL command, you do not type commas or dollar signs.ANS...HideTrue
There are two versions of the “not equal to” operator: <> and =.ANS...HideTrue
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...HideFalse
Preceding a condition by the NOT operator reverses the truth or falsity of the original condition.ANS...HideTrue
The BETWEEN operator is an essential feature of SQL.ANS...HideFalse
You can combine values in character fields as well as number fields.ANS...HideTrue
The IN operator can be used instead or the OR operator in phrasing certain conditions.ANS...HideTrue
In SQL, you use the SORT BY clause to display results in a desired order.ANS...HideFalse
You can use the GROUP BY clause and the ORDER BY clause in the same SELECT statement.ANS...HideTrue
The HAVING clause is to groups what the WHERE clause is to rows.ANS...HideTrue
When rows are grouped, one line of output is produced for each group.ANS...HideTrue
A WHERE and a HAVING clause cannot be included in the same query.ANS...HideFalse
In SQL, you join tables by entering the appropriate conditions in the WHERE clause.ANS...HideTrue
Views cannot be used to examine table data.ANS...HideFalse
An individual can use a view to create reports, charts, and other objects that show database data.ANS...HideTrue
The SELECT command that creates the view, which is called the view query, indicates what to include in the view.ANS...HideFalse
To create a view in MySQL , you simply create and then save a query.ANS...HideFalse
To change the field names in a view include the AS clause in the CREATE VIEW command.ANS...HideTrue
One advantage of a view is that different users can view the same data in different ways.ANS...HideTrue
Once you add an index to a database, you cannot delete it.ANS...HideFalse
When you create an index whose key has a single field, you have created what is called a primary index.ANS...HideFalse
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...HideTrue
The following command will revoke the ability to retrieve Customer records from user Jones: REVOKE GRANT ON Customer FROM Jones ;ANS...FalseTrue
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...HideTrue
Information about tables in the database is kept in the system cataloge.ANS...HideTrue
A stored procedure is placed on a client computer.ANS... Hide False
Essay
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
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.
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.
List the disadvantages of database processing.ANS... Hide Larger file size, Increased complexity, Greater impact of failure, More difficult recovery
Explain why the impact of failure is greater in database processing, compared with the nondatabase approach.ANS... Hide
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.
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
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.
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.
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).
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.
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.
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.
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.
Discuss the restriction concerning the structure of two tables involved in a union.ANS... Hide
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.
What are the advantages of having views only contain the fields required by a given user?ANS... Hide
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.
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.
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.
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.