IE 224 Chapter 1-5 Questions

Lakukan tugas rumah & ujian kamu dengan baik sekarang menggunakan Quizwiz!

In an SQL query, which SQL keyword is used to join two conditions that both must be true for the rows to be selected? A) AND B) EXISTS C) HAVING D) IN E) OR

In an SQL query, which SQL keyword is used to specify the names of tables to be joined? A) FROM B) HAVING C) JOIN D) SELECT E) WHERE

In an SQL query, which SQL keyword is used with built-in functions to group together rows that have the same value in a specified column? A) GROUP BY B) ORDER BY C) SELECT D) SORT BY E) DISTINCT SET

In an SQL query, which built-in function is used to compute the average value of numeric columns? A) AVG B) MEAN C) MAX D) MIN E) SUM

Explain the concept of a surrogate key.

A surrogate key is an artificial key that is created to act as the primary key for a relation. The surrogate key is a unique, numeric value that is appended to the relation. Surrogate keys are used in situations when no suitable primary key exists within the user data, or when all available primary keys within the data are too cumbersome for an efficient design. Surrogate key values have no meaning to the users and are normally hidden on all forms, reports, and displays. Most DBMS products have the ability to automatically generate values for surrogate keys as needed.

Given the functional dependency (A, B) → C, (A, B) is a(n) ________. A) independent variable B) dependent variable C) determinant D) composite determinant E) C and D

Given the functional dependency (A, B) → C, then ________. A) A → B B) A → C C) B → A D) B → C E) None of the above is correct.

If a relation is in BCNF, and each multivalued dependency has been moved to a relation of its own, then the first relation is in ________. A) First Normal Form B) Second Normal Form C) Third Normal Form D) Boyce-Codd Normal Form E) Fourth Normal Form

In an SQL query, the built-in function COUNT works with columns containing data of which of the following data types? A) Integer B) Numeric C) Char D) A and B E) A, B, and C

In an SQL query, which SQL keyword is used to specify the column names to be used in a join? A) FROM B) HAVING C) JOIN D) SELECT E) WHERE

In an SQL query, which SQL keyword is used to state the condition that specifies which rows are to be selected? A) EXISTS B) FROM C) SELECT D) SET E) WHERE

In an SQL query, which built-in function is used to total numeric columns? A) AVG B) COUNT C) MAX D) MEAN E) SUM

In an enterprise-class database system, ________. A) the database application(s) interact(s) with the DBMS B) the database application(s) access(es) the database data C) the DBMS accesses the database data D) A and B E) A and C

In an enterprise-class database system, the database application ________. A) creates queries B) creates forms C) creates reports D) A and B E) B and C

In database systems, the DBMS ________. A) inserts data B) modifies data C) reads data D) deletes data E) All of the above

A defining requirement for Boyce-Codd Normal Form (BCNF) is that every candidate key must be a determinant.

A relation can have only one candidate key.

A relation is a three-dimensional table.

A relationship's maximum cardinality indicates the maximum number of entities that can participate in the relationship.

A single relationship class involves only one entity class.

A tuple is a group of one or more columns that uniquely identifies a row.

An exclusive subtype pattern has one supertype entity that relates to one or more subtype entities.

An identifier of an entity instance must consist of one and only one attribute.

An identifier serves the same role for a table that a key does for an entity.

Any table that meets the definition of a relation is in 2NF.

Arithmetic in SQL statements is limited to the operations provided by the built-in functions.

Business organizations have resisted adopting object-oriented database systems because the cost of purchasing OODBMS packages is prohibitively high.

Candidate keys are called interlocking candidate keys when they share one or more attributes.

Data modelers agree that weak, non-ID-dependent entities exist and are important.

Database design is important, and fortunately it is simple to do.

Design guidelines and priorities are the same whether you're working with an updatable database or a read-only database.

Enterprise Resource Planning (ERP) is an example of a data mining application.

Entities containing optional sets of attributes are often represented using subtypes.

Every subquery can be alternatively expressed by a join.

General-purpose remarks columns rarely contain important data.

Given the functional dependency (A, B) → C, then it is true that A → C and B → C.

Given the functional dependency A → B, then it is necessarily true that B → A.

ID-dependent entities are associated by a nonidentifying relationship.

If a table meets the minimum definition of a relation, it has an effective or appropriate structure.

In E-R modeling, entities within an entity class may have different attributes.

In Microsoft Access, you can use the Oracle DBMS in place of the ADE DBMS.

In a 1:N relationship, the term parent refers to the N side of the relationship.

In a database system, applications write data to the database.

In a database, each row in a spreadsheet has data about a particular instance.

In addition to being a data sublanguage, SQL is also a programming language, like Java or C#.

In an E-R model, the three types of minimum cardinality are mandatory, optional and indeterminate.

In an Enterprise-class database system, a database application accesses the database data.

In an Enterprise-class database system, business users interact directly with the DBMS, which directly accesses the database data.

In every database, not just the databases discussed in this book, table names are capitalized.

In the functional dependency shown as A → B, B is the determinant.

In today's E-R models, attributes of relationships are still used.

It is possible to have a relation that does not have a key.

Microsoft Access is just a DBMS.

Most DBMS products will let you define a primary key on a column that contains null values.

Normalization eliminates modification anomalies and data duplication.

Normalization is an advantage for a read-only database.

One example of a database design using a strong relationship is the multivalued attribute pattern.

Only two tables can be queried by using a subquery.

Outer joins can be either up joins or down joins.

Paradox is the only major survivor of the "bloodbath of PC DBMS products."

Read-only databases are often updated.

Read-only databases seldom use more than one copy of a set of same data.

Recursive relationships only exist for one-to-one relationships.

Relationships among instances of a single entity class are called redundant relationships.

SQL can only query a single table.

SQL is only a data manipulation language (DML).

SQL stands for Standard Query Language.

SQL statements end with a colon.

SQL, although very popular, has never become a national standard.

Sequenced Query Language (SQL) is an internationally recognized standard language that is understood by all commercial database management system products.

Small databases typically have simple structures.

Sorting is specified by the use of the SORT BY phrase.

Surrogate keys usually slow performance.

The CODASYL DBTG mode structured data relationships as a tree structure.

The DBMS ranked as being the "easiest to use" in the text is Microsoft's SQL Server.

The DBMS ranked as having the "most power and features" in the text is IBM's DB2.

The Microsoft Access wildcard character "_" (underscore) indicates a single, unspecified character in a specific location in a Microsoft Access SQL query.

The SQL DELETE TABLE statement can be used to remove unneeded tables after the normalized tables are created and populated.

The SQL built-in function ADDUP totals values in numeric columns.

The SQL keyword WHERE is used to specify the table(s) that contain(s) the data to be retrieved.

The SQL wildcard character "#" indicates a single, unspecified character in a specific location in an SQL query.

The WHERE clause contains the condition that specifies which columns are to be selected.

The built-in function SUM can be used with any column.

The columns of a relation are sometimes called tuples.

The columns to be obtained by an SQL command are listed after the FROM keyword.

The database application is responsible for concurrency control.

The degree of a relationship is expressed as the relationship's maximum cardinality.

The functional dependency noted as A → B means that the value of A can be determined from the value of B.

The largest databases in e-commerce are the order entry databases.

The problem of misspelled data entries is an entirely different problem than the inconsistent values problem.

The standard sales order/line item pattern is a classic example of unneeded normalization.

The supremetype entity contains the attributes that are common to all subtypes.

To determine the number and type of columns in a table, use the SQL construct COUNT(*).

To eliminate multivalued dependencies, normalize your tables so that they are all in BCNF.

To exclude one or more values using a condition, the SQL OUT keyword must be used.

To have SQL automatically eliminate duplicate rows from a result, use the keyword DISTINCT with the FROM keyword.

To sort the rows of the result table, the ORDER BY clause is specified.

Two or more tables are joined by giving the table names in the WHERE clause and specifying the equality of the respective column names as a condition in the GROUP BY clause.

When designing a database, first identify the entities, then determine the attributes, and finally create the relationships.

When examining data values as a part of assessing table structure, there is no need to try to determine candidate keys other than the table's primary key.

When examining data values as a part of assessing table structure, there is no need to try to determine foreign keys.

When you are creating a database from existing data, you will have only minor problems with inconsistent values.

dBase was the first PC-based DBMS to implement true relational algebra on a PC.

A database system is typically defined as its four components: users, database applications, the DBMS and the databases.

A defining requirement for BCNF is that every determinant must be a candidate key.

A deletion anomaly exists when deleting data about one entity results in the loss of data about another entity.

A determinant of a functional dependency may or may not be unique in a relation.

A foreign key is one or more columns in one relation that also is the primary key in another table.

A functional dependency is a relationship between attributes such that if we know the value of one attribute, we can determine the value of the other attribute.

A key can be composed of a group of attributes taken together.

A key is a combination of one or more columns that is used to identify particular rows in a relation.

A missing value is called a null value.

A multivalued dependency exists when a determinant is matched to a set of values.

A nested SELECT statement (one that appears within the WHERE clause of another SQL statement) is called a subquery and must be enclosed in parentheses.

A null value in a column may indicate that there is an appropriate value for that attribute, and although the value is known no one has entered the value into the database.

A null value in a column may indicate that there is an appropriate value for that attribute, but it is unknown.

A null value in a column may indicate that there is no appropriate value for that attribute.

A primary key is a candidate key that has been selected to uniquely identify rows in a relation.

A referential integrity constraint is used to make sure the values of a foreign key match a valid value of a primary key.

A referential integrity constraint limits the values of a foreign key.

A relation is a table composed of columns and rows.

A relation is in 2NF if and only if it is in 1NF and all non-key attributes are determined by the entire primary key.

A relation is in 3NF if and only if it is in 2NF and there are no non-key attributes determined by another non-key attribute.

A relation is in 4NF if it is in BCNF and it has no multivalued dependencies.

A relation is in 4NF when multivalued dependencies are isolated in their own relation.

A relation is in Boyce-Codd Normal Form (BCNF) if and only if it is in 3NF and every determinant is a candidate key.

A relation that is in domain/key normal form is assured to be free from all anomalies.

A relationship's minimum cardinality indicates the number of entity instances that must participate in the relationship.

A relationship's minimum cardinality indicates whether or not an entity must participate in the relationship.

A row can be uniquely identified by a key.

A surrogate key is an artificial column that is added to a relation to be its primary key.

A weak entity is an entity that cannot exist in the database without (and is logically dependent upon) another type of entity also existing in the database.

All database applications get and put database data by sending SQL statements to the DBMS.

All relations are tables, but not all tables are relations.

All weak entities must have a minimum cardinality of 1 on the entity on which it depends.

An ID-dependent entity is an entity whose identifier includes the identifier of another entity.

An alternative to combining tables by a subquery is to use a join.

An asterisk (*) following the SELECT verb means that all columns are to be displayed.

An attribute is considered to be a non-key attribute when it is a non-prime attribute, which means that the attribute is not contained in any candidate key.

An attribute that determines which subtype is appropriate is called a discriminator.

An entity class is described by the structure of the entities in that class.

An entity instance is the occurrence of a particular entity.

An entity instance of an entity class is the representation of a particular entity and is described by the values of the attributes of the entity.

An entity is something that users want to track.

An entity that represents something that can exist on its own is called a strong entity.

An identifier of an entity instance is one or more attributes that name or identify entity instances.

An inconsistent values problem is created when different users have coded the same data entries differently.

Any table that meets the definition of a relation is said to be in first normal form (1NF).

Applications are computer programs used directly by users.

Attribute Y is functionally dependent on attribute X if the value of attribute X determines the value of Y.

Bill Gates has said that "XML is the lingua-franca of the Internet Age."

Columns can be sorted in descending sequence by using the SQL DESC keyword.

Creating a read-only database is a job often given to beginning database professionals.

Data Language/I (DL/I) structured data relationships as a tree structure.

Data is recorded facts and figures; information is knowledge derived from data.

Database design varies depending on whether you're building an updatable database or a read-only database.

Databases are a key component of e-commerce order entry, billing, shipping and customer support.

Databases record data in such a way that they can produce information.

Denormalization is simple—join the data together and store it in a table.

Denormalization is the process of joining previously normalized tables back together.

Denormalization reduces the complexity of the SQL statements needed in an application to read required data.

Domain/key normal form requires that every constraint be a logical consequence of the definition of domains and keys.

E-R modeling recognizes both relationship classes and relationship instances.

E-R models use an identifying relationship to connect entities that are ID-dependent.

Entities can be associated with one another in relationships.

Entities of a given type are grouped into entity classes.

Entities with an IS-A relationship should have the same identifier.

Every time we break up a relation during the normalization process, we may have to create a referential integrity constraint.

Functional dependencies can involve groups of attributes.

Given the functional dependency (A, B) → C, the attributes (A, B) are referred to as a composite determinant.

Given the functional dependency A → (B, C), then it is true that A → B and A → C.

ID-dependent entities are a common type of weak entity.

If by knowing the value of A we can find the value of B, then we would say that B is functionally dependent on A.

If you have a table with a set of columns named "Child01", "Child02" and "Child03", the table has the multivalued, multicolumn problem.

If you see a column name Notes, it is likely that this is a general-purpose remarks column.

In E-R modeling, an attribute describes the characteristics of an entity.

In a 1:N relationship, the parent entity is on the one side of the relationship, and the child entity is on the many side of the relationship.

In a database processing system, indexes are held by the database management system (DBMS).

In a database, each table stores data about a different type of thing.

In an E-R model, the three types of maximum cardinality are 1:1, 1:N and N:M.

In an Enterprise-class database system, a database application interacts with the DBMS.

In an SQL query, a join operation is achieved by specifying the equality of the respective column names as a condition in the WHERE clause.

In database systems, indexes are held by the database.

In functional dependencies, the attribute whose value is known or given is referred to as the determinant.

In relational terms as defined by E.F. Codd, a column is called an attribute.

In relational terms as defined by E.F. Codd, a row is called a tuple.

Information systems that stored groups of records in separate files were called file processing systems.

Integrated tables store both data and the relationships among the data.

Joins that show only matching rows from the joined tables in their results are called inner joins.

Joins that show the matching rows from the joined tables plus unmatched rows from one other table in their results are called outer joins.

Microsoft Access is a low-end product intended for individuals and small workgroups.

Multivalued dependencies create anomalies so serious that multivalued dependencies must always be eliminated.

Multivalued dependencies show up under a different name as the multivalued, multicolumn problem.

Normalization requires applications to use more complex SQL since they will need to write subqueries and joins to recombine data stored in separate relations.

Null values are a problem because they are ambiguous.

One example of a database design using an ID-dependent relationship is the archetype/instance pattern.

One example of a database design using an ID-dependent relationship is the association pattern.

One situation created by the multivalued, multicolumn problem is that the maximum number of data values for an attribute is limited.

Read-only databases often use several copies of a set of the same data, where each copy is modified for a specific use.

Referential integrity constraints are rules about what data values are allowed in certain columns.

Relations are classified into "normal forms" based on the types of modification anomalies that they are vulnerable to.

Relations are sometimes left unnormalized to improve performance.

Relations in BCNF have no modification anomalies in regard to functional dependencies.

Relationships are classified by their cardinality.

Relationships between supertypes and subtypes are called IS-A relationships.

Relationships of degree two are referred to as binary relationships.

SQL commands can be embedded in application programs.

SQL includes a data definition language, a data manipulation language, and SQL/Persistent stored modules.

SQL is not a complete programming language. Rather it is a data sublanguage.

SQL statements that can be used to create referential integrity statements for normalized tables are created during the normalization process.

SQL was developed by IBM in the late 1970s.

Subtype entities contain only attributes unique to the subtypes.

Subtypes can be exclusive or inclusive.

Surrogate keys are normally not shown on forms or reports.

The 1977 edition of this text contained a chapter on the relational model, and that chapter was reviewed by E. F. Codd.

The American National Standards Institute (ANSI) maintains the standards for SQL.

The DBMS ranked as being the "most difficult to use" in the text is Oracle Corporation's Oracle Database.

The DBMS ranked as having the "least power and features" in the text is Microsoft Access.

The Microsoft Access wildcard character "*" (asterisk) indicates a sequence of one or more unspecified characters in a Microsoft Access SQL query.

The NoSQL movement should really be called a NoRelational movement.

The SQL INSERT statement can be used to populate normalized tables.

The SQL IS NULL keyword can be used to count the number of nulls in a column.

The SQL WHERE clause contains the condition that specifies which rows are to be selected.

The SQL built-in function AVG computes the average of values in numeric columns.

The SQL built-in function COUNT computes the number of rows in a query.

The SQL built-in function MIN obtains the smallest value in a numeric column.

The SQL built-in function MOST obtains the largest value in a numeric column.

The SQL keyword FROM is used to specify the table to be used.

The SQL keyword GROUP BY instructs the DBMS to group together those rows that have the same value in a column.

The SQL keyword LIKE is used in SQL expressions to select on partial values.

The SQL keyword SELECT is used to specify the columns to be listed in the query results. Answer: TRUE

The SQL syntax JOIN . . . ON can be used as alternate way of writing an SQL join statement.

The SQL wildcard character "%" represents a series of one or more unspecified characters.

The clause SELECT COUNT (*) results in a table with a single row and a single column.

The condition in WHERE clauses can refer to a set of values by using the IN operator.

The condition that a non-key attribute determines another non-key attribute is known as transitive dependency.

The current DBMS engine in Microsoft Access is called ADE.

The database management system (DBMS) is responsible for enforcing referential integrity constraints.

The database management system (DBMS) is responsible for inserting, modifying, reading, and deleting data.

The degree of a relationship is the number of entity classes in the relationship.

The description of a database's structure that is stored within the database itself is called the "metadata."

The e-commerce companies use Web activity databases to determine which items on a Web page are popular and successful.

The elimination of modification anomalies and the reduction of duplicated data are advantages of normalization.

The essence of normalization is taking a relation that is not in BCNF and breaking it into multiple relations such that each one is in BCNF.

The first step in assessing table structure is to count rows and examine columns.

The method of constructing data models used in the text is the extended entity-relationship (E-R) model.

The multivalued dependency noted as A → → B, means that the value of A determines a set of values of B.

The multivalued, multicolumn problem occurs when a set of columns are used to store data that should actually be in one column.

The names of tables to be joined in an SQL query are listed in the FROM clause.

The notation 1:N shows the relationship's maximum cardinality.

The presence of one or more foreign keys in a relation means that we cannot eliminate duplicated data in that table.

The principal difference between an entity and a table is that you can express a relationship between entities without using foreign keys.

The problem with a general-purpose remarks column is that the data it contains are likely to be verbal, inconsistent and stored in a verbose manner.

The purpose of a database is to help people keep track of things.

The relational model was first proposed in 1970 by E. F. Codd at IBM.

The result of an SQL SELECT operation can contain duplicate rows.

The rows of the result table can be sorted by the values in one or more columns.

The second step in assessing table structure is to examine data values and determine dependencies and keys.

The third step in assessing table structure is to check the validity of presumed referential integrity constraints.

There are three types of recursive relationships: 1:1, 1:N and N:M.

To limit the number of rows retrieved from a table, use the SQL TOP keyword.

To obtain all columns, use an asterisk (*) wildcard character instead of listing all the column names.

To refer to a set of values in a condition, the values are placed inside parentheses ( ) and separated by commas.

To refer to a set of values needed for a condition, use the SQL IN operator.

To remove duplicate rows from the result of a query, specify the SQL DISTINCT keyword.

Twitter and Facebook use NoSQL databases.

Undesirable consequences of changing the data in a relation are called "modification anomalies."

When designing or normalizing relations, each relation should have only one theme.

When examining data values as a part of assessing table structure, you should try to determine functional dependencies.

When examining data values as a part of assessing table structure, you should try to determine multivalued dependencies.

When examining data values as a part of assessing table structure, you should try to determine the table's primary key.

When examining data values as a part of assessing table structure, you should try to determine three types of keys: the primary key, any candidate keys and any foreign keys.

When examining data values as a part of assessing table structure, you should try to determine two types of dependencies: functional dependencies and functional dependencies.

When people use the term "join" they normally mean an "equijoin."

When transforming a data model into a relational design, relationships of all degrees are treated as combinations of binary relationships.

When two conditions must both be true for the rows to be selected, the conditions are separated by the SQL AND keyword.

When you are given a set of tables and asked to create a database to store their data, the first step is to assess the tables' structure and content.

While many subqueries can be alternatively written as joins, correlated subqueries do work that cannot be duplicated as a join.

Writing SQL subqueries and joins against normalized tables is simple compared to the code that must be written to handle anomalies from multivalued dependencies.

XML Web services allow database processing to be shared across the Internet.

A Customer Resource Management (CRM) system is an example of a(n) ________. A) single-user database application B) multiuser database application C) e-commerce database application D) A or B E) Any of A, B, or C

A tuple is also known as a(n) ________. A) table B) relation C) row D) field E) file

A ________ is used to limit the possible values of a(n) foreign key. A) composite key B) surrogate key C) functional dependency D) referential integrity constraint E) normal form

A database consists of integrated tables, which store ________. A) data B) relationships among the data C) forms D) A and B E) All of the above

To represent an association pattern in an E-R model, ________. A) create a new ID-dependent entity with a 1:1 relationship to one other entity B) create a new weak, but not ID-dependent entity with a 1:1 relationship to one other entity C) create a new strong entity with a 1:1 relationship to one other entity D) create a new ID-dependent entity with a 1:N relationship to one of two parent entities E) create a new weak, but not ID-dependent entity with a 1:N relationship to one of two parent entities

What are the three types of database design situations?

Database designs can be based on (1) existing data, (2) new systems development and (3) database redesign. Database designs from existing data may be based on data in spreadsheets or other data tables, or on data extracted from other databases. New systems development gathers user requirements for needed applications and designs a database to meet those requirements. Database redesign may be needed to migrate existing databases to a newer DBMS, or to integrate multiple existing databases.

Explain denormalization, and why it may be appropriate to denormalize a set of tables.

Denormalization is the process of taking a normalized set of relations and converting them into a smaller set of relations that is in a lower normal form and thus vulnerable to a greater number of anomalies. While the fully normalized set of relations is more desirable from a theoretical perspective, it does have some disadvantages. The greater the number of relations in the database, the more work that is required by the database whenever data must be retrieved from multiple relations to reconstruct the users' view of the data. In some circumstances, the anomalies introduced by denormalizing a data model may be considered an acceptable cost for the performance improvement from reducing the overhead work performed by the database.

A database may be designed ________. A) from existing data B) as a new systems development project C) as a redesign of an existing database D) A and B E) A, B, and C

A database may contain ________. A) tables B) metadata C) triggers D) stored procedures E) All of the above

A database stores: A) data. B) relationships. C) metadata. D) A and B E) A, B, and C

An advantage of denormalization is ________. A) faster updating B) faster querying C) less complex SQL in application code D) A and B E) B and C

A WHERE clause can contain only one condition.

A binary relationship is a relationship based on numerical entity instance identifiers.

A binary relationship is a relationship between two or more entities.

A characteristic of a relation is that the rows of a relation may hold identical values.

A combination key is a group of attributes that uniquely identifies a row.

To count the number of rows in a table, use the SQL construct COUNT(ROWS).

Describe the steps in assessing table structure when you are given a set of tables and asked to construct a new database to store the existing data.

First, you should count the number of rows in the tables and determine the type of data stored in each column. Second, you should analyze the existing data values and work with users to determine (1) multivalued dependencies, (2) functional dependencies, (3) candidate keys, (4) primary keys and (5) foreign keys. Finally, you should check the validity of the possible referential integrity constraints.

What are general-purpose remarks columns, and why are they a problem?

General-purpose remarks columns are columns in a table with labels such as "Remarks", "Comments" or "Notes." They are a problem because these columns often contain important data that is stored in an inconsistent, verbal and verbose way. Further, they may contain multiple data items.

What are inconsistent values, and why are they a problem?

Inconsistent values are different forms of the same data value. For example, in a database of dog owners and their dogs, we might find a border collie listed as "b. collie", "collie, border", and "border collie". This will create query problems. For example, a query for "border collie" will not return the dogs listed in the other formats. Or, alternatively, we would have to write a query that includes all possible (or at least known) variations of "border collie," and that would be a real pain.

What is "metadata" and how does it relate to the definition of a database?

Metadata is data about the structure of the database itself. This includes data about the names of all the tables in the database, the names of all the columns in each of the tables, the data type of each column in each table, the properties of the tables and the columns, etc. Metadata accounts for the "self-describing" aspect of the definition of a database as a "self-describing collection of integrated tables."

Explain the relationships among primary keys and functional dependencies.

Relations store instances of functional dependencies in their rows. All primary keys represent a functional dependency, since each primary key functionally determines the entire row. Since the primary key determines the entire row, if a primary key were duplicated in a relation, then the entire row would have to be duplicated. Since a relation can contain no duplicate rows, the primary key must be unique.

How is minimum cardinality expressed in IE Crow's Foot E-R diagrams?

The Crow's Foot E-R diagram notation uses a circle to indicate a minimum cardinality of zero (or "optional") and a hash mark to indicate a minimum cardinality of one (or "mandatory"). Each symbol is placed on the relationship line just beyond the symbol indicating maximum cardinality.

The following database will be used in this question: GENERAL SALES DATABASE: SALESREP SalesRepNo RepName HireDate 654 Jones 01/02/2005 734 Smith 02/03/2007 345 Chen 01/25/2004 434 Johnson 11/23/2004 CUSTOMER CustNo CustName Balance SalesRepNo 9870 Winston 500 345 8590 Gonzales 350 434 7840 Harris 800 654 4870 Miles 100 345 Explain the use of the SQL keyword LIKE. Include an example based on the CUSTOMER table from the General Sales database.

The LIKE keyword is used in the WHERE clause of an SQL query to select rows based on partial values. Through the use of wildcard characters that can represent one or more unspecified characters, the LIKE operator can search for a given string of characters within a column value. The ANSI wildcard "%" represents one or more unspecified characters, and the wildcard " " (underscore) represents any single unspecified character. For example, SELECT * FROM CUSTOMER WHERE CustName LIKE 'H%'; is a query that returns all information in the table CUSTOMER about customers whose CustName starts with the letter H.

Explain the ambiguity in the broad definition of a weak entity.

The broad definition of a weak entity includes any entity that relies on another entity for its existence in the database. This is ambiguous since this definition can apply to any entity that is involved in a relationship with a minimum cardinality of 1. If the minimum cardinality is 1, then an entity instance in that entity class cannot exist in the database unless there is a related entity instance in the associated entity class. The more restrictive definition of a weak entity indicates that an entity is weak if it logically depends on the other entity for its existence.

Briefly describe the various tasks of the primary key.

The primary key is used for four primary tasks. First, it is used to uniquely identify the rows in a table. Second, it is used to represent rows in relationships. Third, most DBMS products use the values of the primary key to organize the storage of the relation. Finally, primary keys are used in indexes and other structures to improve performance for search operations.

Explain the steps in the process of putting a relation straight into Boyce-Codd Normal Form (BCNF).

The steps are: (1) Identify all functional dependencies in the relation. (2) Identify every candidate key in the relation. (3) Check to see if all determinants are candidate keys. a. If so, the relation is in BCNF-STOP. b. If not, then: i. Move the columns of the functional dependency of the determinant that is not a candidate key to a new relation. ii. Make the determinant of that functional dependency the primary key of the new relation. iii. Leave a copy of the determinant in the original relation as a foreign key. iv. Create a referential integrity constraint between the relations. NOTE: If there is more than one functional dependency that needs to be moved, move the functional dependency with the most columns first. (4) Repeat step (3) until you reach STOP.

When accessing table structure, how do you determine how many rows are in a table?

To determine how many rows are in a table, use a SELECT query that contains the SQL COUNT(*) function. This function will return the number of rows in the table.

When accessing table structure, how do you determine the number and types of columns in a table?

To determine the number and types of how many rows are in a table, use a SELECT query that contains the SQL * expression. This expression will return all the columns in the table, and the data types can be determined by examining the values in each column.

When accessing table structure, and determining the number and types of columns in a table, how can you control how many rows are returned?

When determining the number and types of how many rows are in a table, use a SELECT query that contains the SQL SELECT TOP ## expression, where ## is the number of rows that you want returned. This function will return only the number of rows specified by the TOP expression, which can make a table with a large number of rows manageable.

A database is a self-describing collection of non-integrated tables.

A database is called "self-describing" because it reduces data duplication.

A database system as typically defined can be modified to include CODASYL.

A data model is a plan for a database design.

A database design may be part of a new systems development project.

A database management system (DBMS) creates, processes and administers databases.

A database shows data in tables and the relationships among the rows in those tables.

A classic example of unneeded normalization is when we are dealing with ________. A) ZIP codes B) sales orders and line items C) association patterns D) multivalued dependencies E) general purpose remarks columns

A database designed using spreadsheets from the Sales department is a database being designed ________. A) from existing data B) as a new systems development project C) as a redesign of an existing database D) A and B E) A, B, and C

A form of multivalued dependency is found in ________. A) the multivalued, multicolumn problem B) the inconsistent values problem C) the missing values problem D) the general-purpose remarks column problem E) None of the above is correct.

A relation is also known as a(n) ________. A) table B) tuple C) relationship D) attribute E) field

A relation is in second normal form (2NF) if and only if it is in 1NF and ________. A) all non-key attributes are determined by the entire primary key B) there are no non-key attributes determined by another non-key attribute C) every attribute is a candidate key D) every candidate key is a determinant E) every determinant is a candidate key

A sales contact manager used by a salesperson is an example of a(n) ________. A) single-user database application B) multiuser database application C) e-commerce database application D) A or B E) Any of A, B, or C

A table designed to store PhoneNumber01, PhoneNumber02 and PhoneNumber03 contains ________. A) the multivalued, multicolumn problem B) the inconsistent values problem C) the missing values problem D) the general-purpose remarks column problem E) None of the above is correct.

A table that meets the definition of a relation is in ________. A) First Normal Form B) Second Normal Form C) Third Normal Form D) Boyce-Codd Normal Form E) Fourth Normal Form

Database professionals use ________ as specific data sources for studies and analyses. A) data marts B) normalization C) data models D) entity-relationship data modeling E) data migration

Entities of a given type are grouped into an ________. A) entity class B) entity relationship C) entity instance D) entity attribute E) None of the above.

For a relationship to be considered a binary relationship it must satisfy which of the following conditions? A) It must involve exactly two entity classes. B) It must have a maximum cardinality of 1:1. C) It must have a maximum cardinality of 1:N. D) A and B E) A and C

In an SQL query, which of the following symbols is used by ANSI SQL to represent a single unspecified character? A) _ (underscore) B) ? (question mark) C) * (asterisk) D) % (percent) E) # (pound)

In general, each relation should have ________. A) one and only one theme B) one or more themes C) exactly two themes D) one or two themes E) exactly three themes

Maximum cardinality refers to ________. A) the most instances of one entity class that can be involved in a relationship instance with another entity class B) the minimum number of entity classes involved in a relationship C) whether or not an instance of one entity class is required to be related to an instance of another entity class D) whether or not an entity is a weak entity E) None of the above.

The advantages of normalization include ________. A) the elimination of modification anomalies B) the elimination of duplicated data C) more complex SQL for multitable subqueries and joins D) A and B E) A, B, and C

The only reason(s) for having relations is to ________. A) store instances of functional dependencies B) store equation components C) store equation results D) B and C E) A, B, and C

The predecessor(s) of database processing was (were) ________. A) file managers B) hierarchical models C) network models D) the relational data model E) All of the above were predecessors of database processing.

The purpose of a database is to: A) help people keep track of things. B) store data in tables. C) create tables of rows and columns. D) maintain data on different things in different tables. E) All of the above.

To represent a multivalued attribute in an E-R model, ________. A) create a new ID-dependent entity with a 1:N relationship B) create a new weak, but not ID-dependent entity with a 1:N relationship C) create a new strong entity with a 1:1 relationship D) create a new ID-dependent entity with a 1:1 relationship E) create a new weak, but not ID-dependent entity with a 1:1 relationship

To represent an archetype/instance pattern in an E-R model, ________. A) create a new ID-dependent entity with a 1:N relationship B) create a new weak, but not ID-dependent entity with a 1:N relationship C) create a new strong entity with a 1:1 relationship D) create a new ID-dependent entity with a 1:1 relationship E) create a new weak, but not ID-dependent entity with a 1:1 relationship

When you are given a set of tables and asked to create a database to store their data, the first step is to ________. A) assess the existing tables' structure and content B) design the database structure C) create one or more new tables D) move the data into the new database E) design the applications that will use the database

Why do we say a database is "self-describing" and why is this an advantage?

A database is considered self-describing because it contains a description of its own structure within the database itself. This description is called the metadata, and it is stored in specialized tables in relational databases. The advantage is that the database is self-documenting, and that a knowledgeable user or a database designer can easily access the metadata. All DBMS vendors provide tools to access the metadata within their products.

What are null values, and why are they a problem?

A null value is a missing value, a value that has never been input into a cell in a table. Null values are a problem because they are ambiguous. A null value can represent three different conditions: (1) the value is inappropriate, (2) the value is appropriate but unknown, or (3) the value is appropriate and known but not entered into the database.

What are "referential integrity constraints"? Give an example.

A referential integrity constraint is a rule that restricts certain actions on the database data. A referential integrity constraint is used to ensure that the values in a field in one table have matching tables in a corresponding field in another table. These constraints are enforced by the DBMS, which will not allow changes to the values of the database that would result in violations of this rule. For example, a database has an EMPLOYEE table and a VEHICLE table that are used to store data on employees and the vehicles that they are assigned to drive. The EMPLOYEE table has a column called EmployeeID that is used to distinguish one employee record from another. The VEHICLE table also has an EmployeeID column that is used to associate a vehicle with the appropriate employee. A referential integrity constraint could be used to prevent a vehicle from being assigned to an employee with an EmployeeID that does not appear in the EMPLOYEE table by requiring that all values in EmployeeID in the VEHICLE table have a matching value in EmployeeID in the EMPLOYEE table.

Distinguish between an entity class and an entity instance.

An entity class is the collection of all entities of a given type. The entity class is described by the structure of the entities in that class. An entity instance is a representation of a particular entity within the entity class. The entity instance is described by the values of the attributes of the entity. An entity class, then, is a collection containing many entity instances.

Distinguish between entity identifiers and keys.

An entity identifier is an attribute or group of attributes that is used to name or identify instances of an entity class. Unlike a key in the relational model, an entity identifier is more of a logical concept. It is simply an attribute that the users think of as distinguishing between entity instances in their environment.

A database designed to implement requirements for a reporting application needed by the Sales department is a database being designed ________. A) from existing non-database data B) as a new systems development project C) as a redesign of an existing database D) A and B E) A, B, and C

A missing value is called a(n) ________. A) empty value B) null value C) missing value D) Any of A, B, or C can be used. E) None of the above is correct.

A program whose job is to create, process and administer databases is called a ________. A) Database Modeling System B) Database Management System C) Data Business Model System D) Relational Model Manager E) Data Business Management Service

A relation is in fourth normal form if it is in BCNF and it has no ________. A) transitive dependencies B) multivalued dependencies C) partial dependencies D) deletion dependencies E) referential integrity conflicts

A relation is in third normal form (3NF) if and only if it is in 2NF and ________. A) all non-key attributes are determined by the entire primary key B) there are no non-key attributes determined by another non-key attribute C) every attribute is a candidate key D) every candidate key is a determinant E) every determinant is a candidate key

An entity that holds specialized attributes that distinguish it from one or more other similar entities is a ________. A) supertype B) subtype C) discriminator D) A and C E) B and C

An entity whose existence depends on the presence of another entity but whose identifier does not include the identifier of the other entity is a(n) ________. A) strong entity B) weak entity C) ID-dependent entity D) A and C E) B and C

Based on the tables below, which of the following ANSI SQL commands would return the average customer balance grouped by SalesRepNo? GENERAL SALES DATABASE: SALESREP SalesRepNo RepName HireDate 654 Jones 01/02/2005 734 Smith 02/03/2007 345 Chen 01/25/2004 434 Johnson 11/23/2004 CUSTOMER CustNo CustName Balance SalesRepNo 9870 Winston 500 345 8590 Gonzales 350 434 7840 Harris 800 654 4870 Miles 100 345 A) SELECT AVG (Balance) FROM CUSTOMER WHERE SalesRepNo; B) SELECT AVG (Balance) FROM CUSTOMER GROUP BY SalesRepNo; C) SELECT AVG (Balance) FROM CUSTOMER, SALESREP WHERE SALESREP.SalesRepNo = CUSTOMER.SalesRepNo; D) SELECT AVG (Balance) FROM CUSTOMER ORDER BY SalesRepNo; E) SELECT AVG (Balance) FROM CUSTOMER, SALESREP WHERE CUSTOMER.SalesRepNo = CUSTOMER.SalesRepNo HAVING SalesRepNo;

Database professionals use a set of principles called ________ to guide and assess database design. A) data marts B) normalization C) data models D) entity-relationship data modeling E) data migration

For a number of reasons, ________ is not often an advantage for a read-only database. A) updating B) normalization C) denormalization D) A and B E) B and C

In an SQL query, which SQL keyword is used to sort the result table by the values in one or more columns? A) GROUP BY B) ORDER BY C) SELECT D) SORT BY E) WHERE

In an SQL query, which SQL keyword is used to specify the table(s) to be used? A) EXISTS B) FROM C) SELECT D) SET E) WHERE

In an SQL query, which SQL keyword must be used to remove duplicate rows from the result table? A) DELETE B) DISTINCT C) NOT EXISTS D) UNIQUE E) KEY

In an SQL query, which built-in function is used to compute the number of rows in a table? A) AVG B) COUNT C) MAX D) MIN E) MEAN

In an SQL query, which of the following symbols is used by Microsoft Access to represent a single unspecified character? A) _ (underscore) B) ? (question mark) C) * (asterisk) D) % (percent) E) # (pound)

In an enterprise-class database system, reports are created by ________. A) the user B) the database application C) the database management system (DBMS) D) the database E) All of the above

Modern microcomputer personal DBMS products ________. A) are supplied by several well-established manufacturers B) were essentially made obsolete by Microsoft Access C) have poor response time D) are not true DBMS products E) are really just programming languages with generalized file-processing capabilities

Regarding the interchangeability of subqueries and joins, A) a join can always be used as an alternative to a subquery, and a subquery can always be used as an alternative to a join. B) a join can sometimes be used as an alternative to a subquery, and a subquery can sometimes be used as an alternative to a join. C) a join can always be used as an alternative to a subquery, and a subquery can sometimes be used as an alternative to a join. D) a join can sometimes be used as an alternative to a subquery, and a subquery can always be used as an alternative to a join. E) a join can never be used as an alternative to a subquery, and a subquery can never be used as an alternative to a join.

Saying that two entities are functionally dependent means that ________. A) the entities are always connected by a mathematical equation B) for one of the entities, if we are given the value of that entity, we can determine the value of one other entity C) for both of the entities, if we are given the value of one entity, we can determine the value of the other entity D) the functional dependency will have to be removed through normalization E) All of the above.

Supertype/subtype entities are said to have a(n) ________ relationship. A) HAS-A B) IS-A C) recursive D) redundant E) multivalue

The SQL function COUNT ________. A) counts the number of columns in a table B) counts the number of rows in a table C) counts the number of tables in a database D) A and C E) B and C

The SQL keyword TOP ________. A) limits the number of columns retrieved from a table B) limits the number of rows retrieved from a table C) limits the number of tables retrieved from a database D) A and C E) B and C

The presence of one or more foreign keys in a relation prevents ________. A) the elimination of modification anomalies B) the elimination of duplicated data C) more complex SQL for multitable subqueries and joins D) A and B E) A, B, and C

The problem of misspelled data entries is really the same as ________. A) the multivalue, multicolumn problem B) the inconsistent values problem C) the missing values problem D) the general-purpose remarks column problem E) None of the above is correct.

To limit the number of rows retrieved from a table, use the SQL construct ________. A) SELECT * B) SELECT TOP n * C) SELECT COUNT(TOP n) D) SELECT COUNT(*) E) SELECT COUNT *

When a table is created using existing data from multiple sources, you are likely to find that the different sources code data in slightly different ways. This is an example of ________. A) the multivalued, multicolumn problem B) the inconsistent values problem C) the missing values problem D) the general-purpose remarks column problem E) None of the above is correct.

When designing a database, one of the candidate keys in a relation is selected as the ________. A) composite key B) primary key C) foreign key D) surrogate key E) dependency

When making an SQL query, we are using SQL as a(n) ________. A) DDL B) DML C) embedded language D) SET E) WHERE

You are creating a PRODUCT table using existing data from multiple sources. Examining the data, you find that you have "large red hat", "large hat, red", "red hat large" and "hat, large, red." This is an example of ________. A) the multivalue, multicolumn problem B) the inconsistent values problem C) the missing values problem D) the general-purpose remarks column problem E) None of the above is correct.

You are given an E-R diagram with two entities, ORDER and CUSTOMER, as shown above, and are asked to draw the relationship between them. If a given customer can place only one order and a given order can be placed by at most one customer, which of the following should be indicated in the relationship symbol between the two entities? A) 0:1 B) 1:1 C) 1:N D) N:1 E) N:M

You have been given two tables, CUSTOMER and SALE. You want to check the referential integrity constraint: SALE.CustomerNumber must exist in CUSTOMER.CustomerNumber You run the following SQL query: SELECT CustomerNumber FROM SALE WHERE CustomerNumber NOT IN (SELECT CustomerNumber FROM SALE, CUSTOMER WHERE SALE.CustomerNumber = CUSTOMER.CustomerNumber); What is shown in the results of this query? A) All values of CustomerNumber that match the constraint. B) All values of CustomerNumber that violate the constraint. C) All values of CustomerNumber where SALE.CustomerNumber = CUSTOMER.CustomerNumber. D) A and C E) B and C

Distinguish between the primary key and a candidate key.

Both the primary key and a candidate key can uniquely identify the rows in a table. The primary key is the candidate key that is chosen by the database designer, working with the users, to uniquely identify rows and to represent relationships. Although any candidate key could, by definition, be selected to act as the primary key, the choice of primary key is often based on design decisions such as the amount of foreign key data that would be generated.

A circle across the relationship line near an entity indicates ________. A) a maximum cardinality of "zero" B) a maximum cardinality of "one" C) a minimum cardinality of "optional" D) a minimum cardinality of "required" E) None of the above.

A combination of one or more columns used to identify particular rows in a relation is a ________. A) record B) field C) key D) tuple E) dependency

A combination of two or more columns used to identify particular rows in a relation is a ________. A) record B) field C) composite key D) foreign key E) surrogate key

A composite attribute is an attribute that ________. A) is multivalued B) describes a characteristic of the relationship C) consists of a group of attributes D) is calculated at run-time E) is an identifier

A database designed to combine two databases used by the Sales department is a database being designed ________. A) from existing data B) as a new systems development project C) as a redesign of an existing database D) A and B E) A, B, and C

A database is considered "self-describing" because ________. A) all the users' data is in one place B) it reduces data duplication C) it contains a description of its own structure D) it contains a listing of all the programs that use it E) All of the above

A key consisting of one or more columns that is a primary key in another relation is a ________. A) composite key B) candidate key C) foreign key D) surrogate key E) dependency

An attribute that determines which subtype should be used is a ________. A) supertype B) subtype C) discriminator D) A and C E) B and C

An online drugstore such as Drugstore.com is an example of a(n) ________. A) single-user database application B) multiuser database application C) e-commerce database application D) A or B E) Any of A, B, or C

For database development, a challenge to the relational model and the use of SQL. This challenge is known as: A) the Web services movement. B) the NoSQL movement. C) the SOAP movement. D) the NoSOAP movement. E) the UDDI movement.

For database development, the most important Web-related technology to emerge in recent years is: A) FTP. B) HTTP. C) XML. D) OODBMS. E) All of the above.

Given a table with the structure: EMPLOYEE (EmpNo, Name, Salary, HireDate), which of the following would find all employees whose name begins with the letter "S" using Microsoft Access? A) SELECT * FROM EMPLOYEE WHERE Name IN ['S']; B) SELECT EmpNo FROM EMPLOYEE WHERE Name LIKE 'S'; C) SELECT * FROM Name WHERE EMPLOYEE LIKE 'S*'; D) SELECT * FROM EMPLOYEE WHERE Name LIKE 'S%'; E) None of the above.

Given the functional dependency A → (B, C), A is a(n) ________. A) independent variable B) dependent variable C) determinant D) composite determinant E) C and D

In a relation ________. A) entities in a column vary as to kind B) the order of the columns is important C) the order of the rows is unimportant D) more than one column can use the same name E) All of the above.

In an SQL query, which SQL keyword actually creates the query? A) EXISTS B) FROM C) SELECT D) SET E) WHERE

In an SQL query, which SQL keyword is used with GROUP BY to select groups meeting specified criteria? A) AND B) EXISTS C) HAVING D) IN E) WHERE

In an SQL query, which built-in function is used to obtain the largest value of numeric columns? A) AVG B) COUNT C) MAX D) MIN E) SUM

In an SQL query, which of the following symbols is used by ANSI SQL to represent all the columns in a single table? A) _ (underscore) B) ? (question mark) C) * (asterisk) D) % (percent) E) # (pound)

Minimum cardinality refers to ________. A) the most instances of one entity class that can be involved in a relationship with one instance of another entity class B) the minimum number of entity classes involved in a relationship C) whether or not an instance of one entity class is required to be related to an instance of another entity class D) whether or not an entity is a weak entity E) None of the above.

Referential integrity constraints are used to limit the possible values of a ________. A) composite key B) candidate key C) foreign key D) surrogate key E) dependency

Reviewing the work done on a table that was created using existing data from multiple sources, you are likely to find that some data values that were provided were never entered into the table. This is an example of ________. A) the multivalued, multicolumn problem B) the inconsistent values problem C) the missing values problem D) the general-purpose remarks column problem E) None of the above is correct.

Suppose that you need to update one value of the column SalesCost in a relation. The way the relation is constructed, this value actually needs to be changed in three different rows. However, you only change the value in two of the rows. You have just created an a(n) ________. A) normalization anomaly B) insertion anomaly C) update anomaly D) deletion anomaly E) removal anomaly

The SQL keyword IS NULL can be used to ________. A) count the number of columns in a table B) count the number of rows in a table C) count the number of null values in a column D) A and C E) B and C

The disadvantages of normalization include ________. A) the elimination of modification anomalies B) the elimination of duplicated data C) more complex SQL for multitable subqueries and joins D) A and B E) A, B, and C

The industry standard supported by all major DBMSs that allows tables to be joined together is called ________. A) Sequential Query Language (SQL) B) Structured Question Language (SQL) C) Structured Query Language (SQL) D) Relational Question Language (RQL) E) Relational Query Language (RQL)

The occurrence of a particular entity is called an ________. A) entity class B) entity relationship C) entity instance D) entity attribute E) None of the above.

The second step in assessing table structure includes ________. A) counting rows B) examining columns C) examining data values D) A and B E) B and C

To check for null values in a column in a table, use the SQL phrase ________. A) IS B) IS NOT C) IS NULL D) COUNT(IS NOT) E) COUNT(IS NULL)

When a table is created using existing data from multiple sources, you are likely to find that some data values have never been provided. This is an example of ________. A) the multivalued, multicolumn problem B) the inconsistent values problem C) the missing values problem D) the general-purpose remarks column problem E) None of the above is correct.

When an entity has a relationship to itself, we have a(n) ________. A) supertype/subtype relationship B) archetype/instance relationship C) recursive relationship D) A or C E) B or C

When one SQL query is embedded in another SQL query, the second SQL query is embedded in the ________ of the first query. A) SELECT B) FROM C) WHERE D) GROUP BY E) HAVING

Which of the following is not a key element of an E-R model? A) Identifiers B) Entities C) Objects D) Attributes E) Relationships

Which of the following is true about the functional dependency (A, B) → (C, D)? A) A is the determinant of C. B) A and B together are determined by C and D together. C) A and B together determine D. D) C and D together determine A. E) A determines B.

You are given an E-R diagram with two entities, ORDER and CUSTOMER, as shown above. What does the symbol next to the ORDER entity indicate? A) A maximum cardinality of "zero" B) A maximum cardinality of "one" C) A minimum cardinality of "optional" D) A minimum cardinality of "required" E) None of the above.

________ is the process of joining two or more tables and storing the result as a single table. A) Querying B) Normalization C) Denormalization D) A and B E) B and C

During the second step of assessing table structure, you are trying to determine ________. A) primary keys B) candidate keys C) foreign keys D) A and B E) A, B, and C

A database records: A) facts. B) figures. C) information. D) A and B E) A, B, and C

A determinant that determines all the other columns in a relation is a ________. A) record B) field C) foreign key D) candidate key E) surrogate key

A hash mark across the relationship line near an entity indicates ________. A) a maximum cardinality of "zero" B) a maximum cardinality of "one" C) a minimum cardinality of "optional" D) a minimum cardinality of "required" E) None of the above.

A null value can indicate which of the following conditions? A) The value is inappropriate. B) The value is appropriate but unknown. C) The value is appropriate and known, but not entered into the database. D) A, B, and C E) None of the above is correct.

A relation is in domain/key normal form if ________. A) every key of the relation is a logical consequence of the definition of constraints and determinants B) every key of the relation is a logical consequence of the definition of constraints and domains C) every constraint on the relation is a logical consequence of the definition of keys and determinants D) every constraint on the relation is a logical consequence of the definition of keys and domains E) every domain of the relation is a logical consequence of the definition of keys and constraints

A very popular development technique used by database professionals for database design is known as ________. A) data marts B) normalization C) data models D) entity-relationship data modeling E) data migration

An artificial column added to a relation to serve as the primary key is a ________. A) composite key B) candidate key C) foreign key D) surrogate key E) dependency

An attribute is also known as a(n) ________. A) table B) relation C) row D) field E) file

An identifier may be ________. A) composite B) a single attribute C) a relationship D) A and B E) A, B, and C

Anomalies caused by functional dependencies can be eliminated by putting tables into ________. A) 1NF B) 2NF C) 3NF D) BCNF E) 4NF

Attributes may be ________. A) composite B) element C) multivalued D) A and C E) B and C

Based on the tables below, which of the following commands in ANSI SQL would return only the name of the sales representative and the name of the customer for each customer that has a balance greater than 400? GENERAL SALES DATABASE: SALESREP SalesRepNo RepName HireDate 654 Jones 01/02/2005 734 Smith 02/03/2007 345 Chen 01/25/2004 434 Johnson 11/23/2004 CUSTOMER CustNo CustName Balance SalesRepNo 9870 Winston 500 345 8590 Gonzales 350 434 7840 Harris 800 654 4870 Miles 100 345 A) SELECT * FROM SALESREP, CUSTOMER WHERE Balance > 400; B) SELECT DISTINCT RepName, CustName FROM SALESREP, CUSTOMER WHERE Balance > 400; C) SELECT * FROM SALESREP, CUSTOMER WHERE SALESREP.SalesRepNo = CUSTOMER.SalesRepNo AND Balance > 400; D) SELECT RepName, CustName FROM SALESREP, CUSTOMER WHERE SALESREP.SalesRepNo = CUSTOMER.SalesRepNo AND Balance > 400; E) SELECT RepName, CustName FROM SALESREP, CUSTOMER WHERE Balance > 400 GROUP BY SalesRepNo;

Discriminators can be ________. A) exclusive only B) inclusive only C) decisive only D) A or B E) B or C

Given a table with the structure: EMPLOYEE (EmpNo, Name, Salary, HireDate), which of the following is not a valid ANSI SQL command? A) SELECT * FROM EMPLOYEE WHERE Name LIKE 'Ja%'; B) SELECT COUNT(*) FROM EMPLOYEE WHERE Salary < 30000; C) SELECT COUNT(EmpNo) FROM EMPLOYEE; D) SELECT HireDate, COUNT(*) FROM EMPLOYEE WHERE Salary < 30000; E) SELECT HireDate, COUNT(*) FROM EMPLOYEE GROUP BY HireDate;

Given a table with the structure: EMPLOYEE (EmpNo, Name, Salary, HireDate), which of the following would find all employees whose name begins with the letter "S" using standard SQL? A) SELECT * FROM EMPLOYEE WHERE Name IN ['S']; B) SELECT EmpNo FROM EMPLOYEE WHERE Name LIKE 'S'; C) SELECT * FROM Name WHERE EMPLOYEE LIKE 'S*'; D) SELECT * FROM EMPLOYEE WHERE Name LIKE 'S%'; E) None of the above.

If a table is designed so that every determinant is a candidate key, then that relation is in ________. A) First Normal Form B) Second Normal Form C) Third Normal Form D) Boyce-Codd Normal Form E) Fourth Normal Form

If the removal of facts about one entity results in the unintentional loss of data about another entity, this is referred to as a(n) ________. A) normalization anomaly B) insertion anomaly C) update anomaly D) deletion anomaly E) removal anomaly

In a minimum cardinality, minimums are generally stated as ________. A) 0 B) 1 C) N D) A or B E) A, B, or C

In an SQL query of two tables, which SQL keyword indicates that we want data from all the rows of one table to be included in the result, even if the row does not correspond to any data in the other table? A) LEFT JOIN B) RIGHT JOIN C) INCLUDE D) A and B E) A, B, and C

In an SQL query, the built-in functions SUM and AVG work with columns containing data of which of the following data types? A) Integer B) Numeric C) Char D) A and B E) A, B, and C

In an SQL query, which SQL keyword is used to determine if a column value is equal to any one of a set of values? A) AND B) EXISTS C) HAVING D) IN E) OR

In an SQL query, which SQL keyword is used to implement a subquery? A) GROUP BY B) HAVING C) ORDER BY D) SELECT E) SORT BY

In an SQL query, which built-in function is used to obtain the smallest value of numeric columns? A) AVG B) COUNT C) MAX D) MIN E) SUM

In database systems, the DBMS enforces rules about which user can perform which action when. The rules are known as ________. A) data insertion control B) data modification control C) data reading control D) concurrency control E) referential integrity constraints

Microsoft Access includes: A) a DBMS. B) an application generator. C) a Web server. D) A and B E) A, B, and C

Microsoft Access may use which of the following DBMS engines? A) ADE B) SQL Server C) Oracle D) A and B E) A, B, and C

Reviewing the work done on a table that was created using existing data from multiple sources, you find that a column name Remarks has been included, and it is populated with inconsistent and verbose verbal data. This is an example of ________. A) the multivalued, multicolumn problem B) the inconsistent values problem C) the missing values problem D) the general-purpose remarks column problem E) None of the above is correct.

The characteristics of a thing are described by its ________. A) identifiers B) entities C) objects D) attributes E) relationships

The defining characteristic of BCNF is that a table is in BCNF if ________. A) all rows are unique B) all columns are consistent C) the primary key is a candidate key D) all determinants are candidate keys E) all candidate keys are determinants

The first step in assessing table structure includes ________. A) counting rows B) examining columns C) examining data values D) A and B E) B and C

To count the number of rows in a table, use the SQL construct ________. A) SELECT * B) SELECT TOP n * C) SELECT COUNT(TOP n) D) SELECT COUNT(*) E) SELECT COUNT *

Unlike the anomalies from functional dependencies, the anomalies from ________ are so serious that they should always be eliminated. A) ZIP codes B) sales orders and line items C) association patterns D) multivalued dependencies E) general purpose remarks columns

When one SQL query is embedded in another SQL query, this is referred to as a ________. A) subset B) join C) WHERE Query D) subquery E) set query

You are given an E-R diagram with two entities, ORDER and CUSTOMER, as shown above, and are asked to draw the relationship between them. If a given customer can place many orders and a given order can be placed by at most one customer, which of the following should be indicated in the relationship symbol between the two entities? A) 0:1 B) 1:1 C) 1:N D) N:1 E) N:M

You are given an E-R diagram with two entities, ORDER and CUSTOMER, as shown above. What does the symbol next to the CUSTOMER entity indicate? A) A maximum cardinality of "zero" B) A maximum cardinality of "one" C) A minimum cardinality of "optional" D) A minimum cardinality of "required" E) None of the above.

A database design is rarely a redesign of an existing database.

A relation ________. A) has rows containing data about an entity B) has columns containing data about attributes of the entity C) has cells that hold only a single value D) has no two identical rows E) All of the above.

A relation is in Boyce-Codd normal form (BCNF) if and only if it is in 3NF and ________. A) all non-key attributes are determined by the entire primary key B) there are no non-key attributes determined by another non-key attribute C) every attribute is a candidate key D) every candidate key is a determinant E) every determinant is a candidate key

A very popular development technique used by database professionals to adopt a database design to a new or changing requirement is known as ________. A) data marts B) normalization C) data models D) entity-relationship data modeling E) data migration

An entity whose identifier includes the identifier of another entity is a(n) ________. A) strong entity B) weak entity C) ID-dependent entity D) A and C E) B and C

Business organizations have resisted adopting object-oriented database management systems because ________. A) object-oriented programming uses simplified data structures that fit easily into relational databases B) the cost of purchasing OODBMS packages is prohibitively high C) the cost of converting data from relational databases to OODBMSs is too high D) most large organizations have older applications that are not based on object oriented programming E) C and D

Common variants of the E-R model include: A) IE Crow's Foot B) IDEF1X C) UML D) A and B E) A, B, and C

During the second step of assessing table structure, you are trying to determine ________. A) multivalued dependencies B) functional dependencies C) foreign keys D) A and B E) A, B, and C

In database systems, the DBMS enforces rules about which data can be written in certain columns. The rules are known as ________. A) data insertion control B) data modification control C) data reading control D) concurrency control E) referential integrity constraints

In database systems, the database ________. A) holds user data B) holds metadata C) holds indexes D) holds stored procedures E) All of the above

Normalization is a process used to deal with which of the following modification anomalies? A) Insertion anomaly B) Update anomaly C) Deletion anomaly D) A and B E) A, B, and C

Read-only databases are used for ________. A) updating B) querying C) reporting D) A and B E) B and C

Recursive relationships can have which of the following maximum cardinalities? A) 1:1 B) 1:N C) N:M D) A or B E) A, B, or C

SQL is a ________. A) data sublanguage B) product of IBM research C) national standard D) combination that includes a data definition language and a data manipulation language E) All of the above

The relational model ________. A) was first proposed in 1970 B) was developed by E. F. Codd C) was developed at IBM D) resulted in the DBMS product DB2 E) All of the above

When one SQL query is embedded in another SQL query, the first SQL query can still contain an SQL ________ clause. A) FROM THE B) WHERE THE C) ORDER BY D) GROUP BY E) C and D

Which of the following are basic components of an enterprise-class database system? A) The user B) The database application C) The database management system (DBMS) D) The database E) All of the above

Which of the following is considered to be a weak entity? A) ID-dependent entity B) subtype entity C) association entity D) B and C E) A, B, and C

Which of the following is not true about subtype entities? A) Subtypes may be exclusive. B) The supertype and subtypes will have the same identifier. C) Subtypes are used to avoid a situation in which some attributes are required to be null. D) Subtypes have attributes that are required by the supertype. E) Subtypes can produce a closer-fitting data model.

Which of the following is true about the functional dependency A → (X, Y)? A) X is functionally dependent on A. B) A determines Y. C) A is a determinant. D) X and Y are functionally dependent on A. E) All of the above.

You are given an E-R diagram with two entities, ORDER and CUSTOMER, as shown above, and are asked to draw the relationship between them. If a given customer can place many orders and a given order can be placed by one or more customers, which of the following should be indicated in the relationship symbol between the two entities? A) 0:1 B) 1:1 C) 1:N D) N:1 E) N:M

A composite identifier is defined as a composite attribute that is an identifier.

A compound identifier is an identifier consisting of two or more attributes.

A constraint that requires an instance of an entity to exist in one relation before it can be referenced in another relation is called an insertion anomaly.

Describe the requirements that a table must meet to qualify as a relation.

In order for a table to meet the requirements of a relation, it must satisfy certain conditions. First, the rows contain data about an entity or some portion of the entity. Second, the columns contain data that represent attributes of the entity. Third, each column must contain the same kind of data for all rows. Fifth, each column must have a unique name. Sixth, the cells must contain single values—no group or array values are allowed. Seventh, the order of the columns and the rows is insignificant. Finally, the rows must be unique.

What is meant by the degree of a relationship?

In the E-R model, the degree of a relationship indicates how many entity classes are participating in the relationship. If two entity classes are in the relationship, then the relationship is said to be of degree two and is called a binary relationship. If three entity classes are in the relationship, then the relationship is said to be of degree three and is called a ternary relationship. So, for example, if we have the entity classes CUSTOMER and PRODUCT in a relationship, then they will have a relationship of degree two—a binary relationship.

What is meant by the cardinality of a relationship?

In the E-R model, there are two types of cardinality—the maximum cardinality and the minimum cardinality. The maximum cardinality is the maximum number of entity instances that can participate in a relationship instance. There are three types of maximum cardinality—one-to-one (1:1), one-to-many (1:N) and many-to-many (N:M). The minimum cardinality is the minimum number of entity instances that must participate in a relationship instance. This number is generally zero (0) or one (1). If the number is zero, then participation by that entity instance is optional (O)—it does not have to be in a relationship with an instance of the other entity. If the number is one, then participation by that entity instance is mandatory (M)—it must participate in a relationship with an instance of the other entity. In a binary relationship there are four (4) possible sets of minimum cardinalities—(O-O), (O-M), (M-O) and (M-M).

Explain why it is important to learn SQL.

Most modern DBMS products support SQL as a standardized data language. These products usually provide graphical tools to perform the tasks associated with SQL, but there are some tasks that cannot be performed using these graphical tools. SQL is text-oriented, and SQL code must be written in order to embed SQL commands within program applications.

What is SQL?

Structured Query Language (SQL) is used to create and use databases, tables and relationships. SQL is divided into two categories: SQL statements for database definition and SQL statements for database processing (querying and updating). The database definition commands are referred to as a data definition language (DDL), and the database query and update commands are referred to as a data manipulation language (DML). SQL was developed by IBM, and is endorsed as a national standard by the American National Standards Institute (ANSI). Although a newer standard, SQL3, exists, the most widely implemented version of SQL is the ANSI SQL-92 standard. SQL is not a full-featured programming language, but rather it is considered to be a data sublanguage.

Briefly describe subqueries and joins. Explain when each is not an acceptable alternative for the other.

Subqueries and joins are both methods for retrieving data from multiple tables. Subqueries involve nesting one SELECT statement within another. The nested SELECT is used as part of a condition in the WHERE clause of the first SELECT statement. The nested SELECT statement can return a set of records from one table, which are then used in a logical operator within the parent SELECT query. A join combines records from each table into concatenated records containing the fields of both tables. The records are concatenated based on matching values in similar columns in the two tables. Subqueries cannot be used in situations where the results to be displayed include attributes from more than one table. Joins cannot be used as an alternative to a correlated subquery.

A SELECT statement used in a WHERE clause is called a subquery.

A WHERE clause can contain another SELECT statement enclosed in parentheses.

A candidate key is one of a group of keys that may serve as the primary key in a relation.

A characteristic of a relation is that the cells of the relation hold a single value.

You are creating a BOAT table using existing data from multiple sources, and you find that you have "power boat blue", "boat, power, blue" and "blue power boat" as data values for the same column. This is an example of the inconsistent values problem.

How is maximum cardinality expressed in IE Crow's Foot E-R diagrams?

The Crow's Foot E-R diagram notation uses a hash mark to indicate a maximum cardinality of one and a crow's foot to indicate a maximum cardinality of many. Each symbol is placed immediately next to the rectangle symbolizing an entity. These symbols are combined in various forms to signify 1:1, 1:N and N:M relationships.

Briefly describe the function of the DBMS in a database system.

The DBMS creates the database and the tables and structures within it. The DBMS also reads and updates the database data. It receives requests from application programs to perform data maintenance tasks. These requests are translated into actions that are performed on the database. In addition to maintaining the user data within the database, the DBMS also maintains the database structures. The DBMS also enforces any rules that have been defined to govern the values of the data, such as data type requirements and referential integrity constraints. The DBMS controls concurrency issues, which deal with the unwanted interruption of one user's work by another user's work. As the only point of entry into the database, the DBMS also provides security for the database to restrict users' access to only the data that they have authority to read or modify. Finally, the DBMS is responsible for the creation of backup copies of the database data and for restoring the database in case a recovery is required.

Describe and discuss the main elements of the Entity-Relationship (E-R) model.

The E-R model is basically made up of entities, attributes and relationships. Entities represent the things people want to keep track of, such as customers and products. Each "thing" is presented by an entity or entity class—in this case CUSTOMER and PRODUCT. Individual members of each entity class are called entity instances—for example CUSTOMER John Smith and PRODUCT Canned Tomato Soup. Each entity is described by a set of attributes. For example, a CUSTOMER will have a CustomerNumber, a LastName and a FirstName, while a PRODUCT will have a ProductNumber and a Description. These, of course, have specific values for each entity instance. Finally, the entities are connected by relationships. There are relationship classes between entity classes and relationship instances between entity instances. For example, in general a CUSTOMER buys a PRODUCT, and a PRODUCT is sold to a CUSTOMER. Specifically, CUSTOMER John Smith buys a PRODUCT Canned Tomato Soup.

The following database will be used in this question: GENERAL SALES DATABASE: SALESREP SalesRepNo RepName HireDate 654 Jones 01/02/2005 734 Smith 02/03/2007 345 Chen 01/25/2004 434 Johnson 11/23/2004 CUSTOMER CustNo CustName Balance SalesRepNo 9870 Winston 500 345 8590 Gonzales 350 434 7840 Harris 800 654 4870 Miles 100 345 Explain the use of the of SQL keyword SELECT. Include an example based on the CUSTOMER table in the General Sales database.

The SQL keyword SELECT forms the basis for all SQL querying. Although SELECT technically specifies columns to be used in a query, it is always the first SQL keyword or command in an SQL phrase that includes at least the SQL keyword FROM and often the SQL keyword WHERE. The SQL keyword WHERE is used to specify which tables are used in the query, while the SQL keyword WHERE is used to specify conditions or constraints imposed on the query. For example, SELECT * FROM CUSTOMER WHERE CustNo = 34567; is a query that returns all information in the table CUSTOMER about the customer whose customer number is 34567.

What are the advantages and disadvantages of normalization?

The advantages of normalization are that it (1) eliminates modification anomalies and (2) reduces duplicated data. Reducing duplicated data will eliminate data integrity problems and save file space. The disadvantages of normalization are that it (1) will require application developers to write more complicated SQL statements for multitable subqueries and joins, and (2) may slow down the applications running against the database.

Briefly describe the function of an application program in a database system.

The application program is responsible for creating and processing forms. The application displays the form to the user, allows the user to complete the data entry, evaluates the form to determine which data management tasks need to be performed, and transmits the appropriate requests to the DBMS. The application creates and transmits queries. The queries are requests for data that are created in a language like SQL, and transmitted to the DBMS to have the requested data returned to the application program. The application also creates and processes reports. The query to retrieve the necessary data for the report is sent to the DBMS. When the DBMS returns the needed data, the application manipulates it as necessary to create the requested report. The application program also applies application logic to control the manipulation of data in accordance with the business rules. Finally, the application program is responsible for providing control. Control must be exercised to allow the users to make choices for functions and tasks as appropriate for their jobs. Also, control must be exercised to manage the activities of the DBMS.

What components are included in a database?

The database contains user data, metadata, indexes and other overhead data, and application metadata. User data is the data from the user's environment that they want to track. Metadata is data about the structure of the database. Indexes and other overhead data are structures that the database uses to improve performance. Finally, the application metadata is data about forms, reports, and other application components that some databases, particularly those created with desktop DBMS products, store with the database.

Explain the essence of normalization that is implemented through the use of normal forms.

The essence of normalization is to create relations that contain data on a single theme, and for each row in a relation to contain all the data about that theme for a given entity. This essence is implemented in the normal forms by taking a relation that contains data on more than one theme and splitting it into multiple relations that each contains data on a single theme. The different normal forms protect against the occurrence of modification anomalies. In practice, making sure all relations are in Boyce-Codd Normal Form (BCNF), or in Fourth Normal Form (4NF) if multivalued dependencies are found, insures high reliability of the database.

What are the four components of a database system?

The four components in a database system are: the user, the database application, the database management system (DBMS) and the database. The user interacts with the database application, which interacts with the DBMS, which controls the database. The functions of the database application include creating and processing forms, creating and transmitting queries and creating and processing reports. The DBMS creates databases, tables and supporting structures, manages database data, enforces rules and provides security. The database stores the user data, the database metadata, indexes, triggers, stored procedures and application metadata.

What is the multivalue, multicolumn problem, and why is it a problem?

The multivalue, multicolumn problem occurs when a table used multiple columns to store multiple values of an attribute. For example, a database of dog owners might have a table with the structure: DOG_OWNER (OwnerID, LastName, FirstName, DogName01, DogName02, DogName03) This is a problem because (1) the number of DogNames is fixed, and (2) this structure creates query problems. In fact, this structure is another form of the multivalued dependency, and requires a similar solution-a DOG table should be created to store the DogNames and with a foreign key to link the dogs to their owner.

Briefly describe the history of database processing.

The predecessor of database processing was file processing, where data were maintained on magnetic tape. Database processing as we know it today became possible with the availability of direct access disk storage in the 1960s. Using this storage, both the hierarchical data model and then the network data model were developed. In 1970, E. F. Codd of IBM proposed the relational model, which is the standard model used today. Current DBMSs such as DB2, Oracle and SQL Server are based on the relational model. The appearance of microcomputer-based DBMSs in the 1980s led to a "bloodbath" from which Microsoft Access emerged as the dominant PC workstation DBMS. More recent events include the introduction of object-oriented DBMSs (OODBMSs), and the development of tools such as XML to allow the use of database systems over the Internet.

What is the purpose of a database, and how does the database accomplish this purpose?

The purpose of a database is to help people keep track of things. It accomplishes this purpose by storing data in tables. Each table has rows and columns, similar to a spreadsheet. A database usually has multiple tables in order to keep track of different but related things. For example, we might have a CUSTOMER table to keep track of customers and a PRODUCT table to keep track of the things we sell. Each row in each table holds data about a particular instance, i.e., one customer or one product. The database also stores the links between the tables, so that we can track which customers bought which products (note: this will require an additional table if one customer can buy more than one product and one product can be sold to more than one customer).

Explain the terms relation, tuple, and attribute.

The terms relation, tuple, and attribute are used primarily by database theoreticians. These terms are synonymous with the terms table, row, and column, respectively, in regards to a relational database. They are also equivalent to the terms file, record, and field, which tend to be used by many traditional data processing professionals.

What is an ID-Dependent entity?

There are some entities that cannot be completely identified without being associated with another entity that provides additional identifying information. A common example is an apartment, where just the apartment number by itself (for example, the number 12) does not tell you which apartment is being described—you have to know which building the apartment is in. Thus the entity APARTMENT needs to be associated with the entity BUILDING. In fact, the entity APARTMENT needs to include the identifier of BUILDING in the identifier of APARTMENT for the identifier of APARTMENT to be complete. An entity that requires the inclusion of another entity's identifier as part of its own composite identifier is called an ID-Dependent entity.

Explain the concept of a foreign key.

To implement a relationship within a relational database, the primary key of one relation is placed as an attribute in another relation. This attribute is called a foreign key in the second relation because it is the primary key of a relation that is foreign to the table in which the field resides.

What is a functional dependency? Give an example not used in the text.

We say that a functional dependency exists when the value of one or more attributes determines the value of another attributes. For example, suppose we have a six-sided die numbered and colored as follows: Number Color 1 Red 2 Blue 3 White 4 Green 5 Green 6 Green In this case, Number functionally determines Color, written as Number → Color. Then, if we know the Number is 3, we know the Color is White. Note that the reverse is not true—Color does not determine Number since if we are given the Color Green the number could be 4, 5, or 6. The attribute on the left-hand side of the dependency is called the "determinant." Thus, Number is a determinant. Although equations can be expressed as functional dependencies [as when the equation TotalCost = NumberSold * ItemCost is written as the functional dependency (NumberSold, ItemCost) → TotalCost] functional dependencies are not equations. Also note that a determinant can have more than one attribute [as does (NumberSold, ItemCost) in the last example] and when it does it is called a composite determinant.

What are supertype and subtype entities?

When instances of an entity can be categorized into different types with each type sharing some common characteristics while having certain characteristics that are unique to each type, the common attributes can be separated from the unique ones. This is represented in E-R diagrams through the use of supertype and subtype entities. The supertype entity class contains the attributes that are common to all of the subtypes. The subtype entity classes represent the different types, or categories, of the supertype. Only the attributes that are unique to a given subtype tend to be listed as the attributes of that subtype. Each subtype has a relationship with the supertype. All subtypes should have the same identifier as the supertype since they represent different perspectives of the same thing.

Lihat semua set pelajaran

Set pelajaran terkait

Insurance Ch. 8 (Retention & Ch. Exam Questions)

Neonate & Infant G&D- Sherpath

Las actividades de viaje

PACKRAT: Pulm

PHY1020C Exam 1

Pharm Study Guide

Applied A&P Exam 2, Mastering A&P chapter 12, Mastering A&P Chapter 12 - The Central Nervous System

312 Adaptive Quizes for Exam 3

Respiratory System

Question for topic 2

Chapter 30 Ch 4 Building Healthy Relationships and Communicating Effectively

Biology Questions

NFS 1110 Chapter 11 Questions

MAN Exam 2 Quizzes

Skeletal System

Chapter 9 Culture

BIT 3554 Exam 1 KC

Chem Ch. 12 LS, Chem 132 LearnSmart Ch. 12.3, 13.1-4, Chemistry Chapter 12, Chapter 12, Chem 102 Connect 12.5-12.6, Chapter 12 SmartBook, Chem 2 Chapter 12, Chemistry 124 Chapter 12: Molecular Nature of Matter (Jursich), Phase Change Reading