Database
You have been given two tables, CUSTOMER and SALE. You want to check the referential integrity constraint:
All values of CustomerNumber that violate the constraint.
In an SQL query, which built-in function is used to compute the number of rows in a table?
COUNT
Which SQL keyword is used to name a new table and describe the table's columns?
CREATE
Which of the following illustrates the authors' preferred style of defining a primary key?
CREATE TABLE CUSTOMER ( CustomerID Integer Not Null LastName Char(35) Not Null First Name Char(35) Null CONSTRAINT CustomerPK PRIMARY KEY(CustomerID) );
A database is considered self-describing because ________.
Correct! it contains a description of its own structure
Referential integrity constraints are implemented in SQL using the ________ syntax.
FOREIGN KEY
A composite identifier is defined as a composite attribute that is an identifier.
False
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.
False
A database design is rarely a redesign of an existing database.
False
A database is called self-describing because it reduces data duplication.
False
A single relationship class involves only one entity class.
False
A special constraint that is created to improve database performance is called an Index.
False
A surrogate key is a key typically used in place of a foreign key.
False
According to the SQL-92 standard, statements used to construct views cannot contain the WHERE clause.
False
Although correlated subqueries are useful in database redesign, they cannot be used to verify functional dependencies.
False
An ad-hoc SQL query is typically written within an application program.
False
An intersection table can have additional attributes besides the keys of its parent tables.
False
By default, the identifier of the entity becomes the foreign key of the corresponding table.
False
Candidate keys are called interlocking candidate keys when they share one or more attributes.
False
Data models are part of the logical schema.
False
Design guidelines and priorities are the same whether you're working with an updatable database or a read-only database.
False
General-purpose remarks columns rarely contain important data.
False
If the table PRODUCT has a column PRICE, and PRICE has the data type Numeric (8,2), the value 98765 stored in that field will be displayed by the DBMS as 98765.00.
False
In E-R modeling, entities within an entity class may have different attributes.
False
In addition to being a data sublanguage, SQL is also a programming language, like Java or C#.
False
In an E-R model, the three types of minimum cardinality are mandatory, optional, and indeterminate.
False
In every database, not just the databases discussed in this book, table names are capitalized.
False
In today's E-R models, attributes of relationships are still used.
False
Microsoft SQL Server 2019 Express Edition can be used as a learning tool for large production databases.
False
Normalization eliminates modification anomalies and data duplication.
False
The DELETE keyword is used to delete a table's structure.
False
The MODIFY keyword is used to change a column value.
False
The MODIFY keyword is used to change the structure, properties or constraints of a table.
False
The Microsoft Access wildcard character "*" (asterisk) indicates a sequence of one or more unspecified characters in a Microsoft Access SQL query.
False
The RENAME TABLENAME command can be used to change table names.
False
The SQL command CREATE USER VIEW is used to create a virtual table.
False
The SQL keyword WHERE is used to specify the table(s) that contain(s) the data to be retrieved.
False
The WHERE clause contains the condition that specifies which columns are to be selected.
False
The degree of a relationship is expressed as the relationship's maximum cardinality.
False
The largest databases in e-commerce are the order entry databases.
False
The problem of misspelled data entries is an entirely different problem than the inconsistent values problem.
False
There are several difficulties with increasing cardinalities from 1:1 to 1:N, including preserving the existing relationships.
False
To count the number of rows in a table, use COUNT(ROWS).
False
To drop a foreign key column from a table, no preliminary steps are needed, and we can simply use the ALTER TABLE DROP COLUMN statement.
False
To have SQL automatically eliminate duplicate rows from a result, use the keyword DISTINCT with the FROM keyword.
False
To represent a 1:N relationship in a relational database design, an intersection table is created.
False
When creating a relational database design from E-R diagrams, first create a relation for each relationship.
False
When designing a database, first identify the entities, then determine the attributes, and finally create the relationships.
False
When the parent entity has a surrogate key, the enforcement actions are the same for both parent and child.
False
When transforming supertype/subtype entities into a relational database design, all of the attributes for the supertype table are placed into the subtype relations.
False
A database is a self-describing collection of non-integrated tables
False - They are integrated.
Lesson 13 Quiz
QUIZ 13 LESSON
Question 1 To obtain all columns, use an asterisk (*) wildcard character instead of listing all the column names. ***** True Question 2 Suppose your company stores EMPLOYEE and CUSTOMER data in separate tables. If you want to find all employees who are also customers, which SQL keyword would you most likely use? ***** INTERSECT Question 3 The wildcard character "#" indicates a single, unspecified character. ***** False Question 4 The SQL keyword GROUP BY instructs the DBMS to group together those rows that have the same value in a column. ***** True Question 5 Outer joins can be either up joins or down joins. ***** False Question 6 The SQL wildcard character "%" represents a series of one or more unspecified characters. ***** True Question 7 Microsoft SQL Server 2019 Express Edition can be used as a learning tool for large production databases. ***** False Question 8 The database management system (DBMS) is responsible for inserting, modifying, reading, and deleting data. ***** True Question 9 For database development, a challenge to the relational model and the use of SQL is known as ________. ***** the NoSQL movement Question 10 A(n) ________ is used by a senior manager to extract, summarize, and consolidate operational databases. ***** digital dashboard Question 11 Which of the following statements is not true for an enterprise-class database system? ***** The database application(s) directly access(es) the database data. Question 12 According to the text, Oracle Corporation's Oracle Database is considered the most difficult DBMS to use. ***** True Question 13 A tuple is a group of one or more columns that uniquely identifies a row. ***** False Question 14 In relational terms as defined by E.F. Codd, a column is called an attribute. ***** True Question 15 Given the functional dependency A → (B, C), A is a(n) ________. independent variable ***** determinant Question 16 In relational terms as defined by E.F. Codd, a row is called a tuple. ***** True Question 17 If a table is designed so that every determinant is a candidate key, then that relation is in ________. ***** Boyce-Codd Normal Form Question 18 A determinant of a functional dependency may or may not be unique in a relation. ***** True Question 19 Read-only databases seldom use more than one copy of a set of the same data. ***** False Question 20 Reviewing the work done on a table that was created using existing data from multiple sources, you find that a column named Remarks has been included, and it is populated with inconsistent and verbose verbal data. This is an example of ________. ***** the general-purpose remarks column problem Question 21 A null value can indicate which of the following conditions? ***** The value is appropriate but unknown. Question 22 An advantage of denormalization is ________. ***** faster querying Question 23 Denormalization reduces the complexity of the SQL statements needed in an application to read required data. ***** True Question 24 General-purpose remarks columns rarely contain important data. ***** False Question 25 Supertype/subtype entities are said to have a(n) ________ relationship. ***** IS-A Question 26 A relationship's maximum cardinality indicates the maximum number of entities that can participate in the relationship. ***** False Question 27 Distinguish between an entity class and an entity instance. ***** entity class is the type of entity that can be grouped. ***** entity instance is the moment in time that an entity is related Question 28 A recursive relationship occurs when an entity type has many relationships with other recursive entities. ***** False Question 29 A compound identifier is an identifier consisting of two or more attributes. ***** False Question 30 An entity's minimum cardinality in a relationship indicates the number of entity instances that must participate in the relationship. ***** True Question 31 When the parent entity is required (M) in a relationship, every row of the child table must have a valid, non-null value of the foreign key. ***** True Question 32 The binary constraint must not indicates that ________. ***** a binary relationship includes value combinations that must not occur in a ternary relationship Question 33 The identifier of the entity becomes the ________ of the corresponding table. ***** primary key Question 34 Each attribute of an entity becomes a(n) ________ of a table. ***** column Question 35 A referential integrity constraint policy that insures foreign key values in a table are correctly maintained when there is a change to the primary key value in the parent table is called a cascading insertion. ***** False Question 36 The first step in the database design process is to create tables and columns from entities and attributes. ***** True Question 37 A stored program that is stored within the database and compiled when used is called a trigger. ***** False Question 38 One or more rows can be added to a table by using the INSERT statement. ***** True Question 39 Which SQL keyword is used to add one or more rows of data to a table? ***** INSERT Question 40 Which SQL keyword is used to delete a table's structure? ***** DROP Question 41 SQL views are updatable when the view is based on a single table with no computed columns, and all non-null columns are present in the view. ***** True Question 42 The CREATE TABLE statement is used to name a new table and describe the table's columns. ***** True Question 43 Because SQL statements are set-oriented, whereas programs are element-oriented, the results of SQL statements used in programs are treated as ________. ***** pseudofiles Question 44 When dropping a primary key column from a table, which of the following steps is included in the process? (The order of the steps listed below is not relevant, only the steps themselves.) ***** Drop the foreign keys in other tables based on the primary key column Question 45 To add a NULL column to a table, we simply use the MODIFY TABLE statement. ***** False Question 46 When dropping a foreign key column from a table, which of the following steps is (are) included in the process? (The order of the steps listed below is not relevant, only the steps themselves.) ***** Both Drop the foreign key constraint from the table and Drop the foreign key column from the table are correct Question 47 Because of the need to know the functional dependencies in a database, it is a good idea to create a(n) ________. ***** dependency graph Question 48 Database redesign is fairly easy when ________. ***** there is no data in the database Question 49 In the database redesign process, before proceeding with the redesign it is often useful to ________. ***** check whether certain conditions or assumptions about the data are valid Question 50 Changing cardinalities ________. ***** commonly occurs in database redesign
Question 12 / 2 pts To obtain all columns, use an asterisk (*) wildcard character instead of listing all the column names. Correct! True False Question 22 / 2 pts Suppose your company stores EMPLOYEE and CUSTOMER data in separate tables. If you want to find all employees who are also customers, which SQL keyword would you most likely use? UNION ALL EXCEPT UNION Correct! INTERSECT Question 32 / 2 pts The wildcard character "#" indicates a single, unspecified character. True Correct! False Question 42 / 2 pts The SQL keyword GROUP BY instructs the DBMS to group together those rows that have the same value in a column. Correct! True False Question 50 / 2 pts Outer joins can be either up joins or down joins. You Answered True Correct Answer False Question 60 / 2 pts The SQL wildcard character "%" represents a series of one or more unspecified characters. Correct Answer True You Answered False Question 72 / 2 pts Microsoft SQL Server 2019 Express Edition can be used as a learning tool for large production databases. True Correct! False Question 82 / 2 pts The database management system (DBMS) is responsible for inserting, modifying, reading, and deleting data. Correct! True False Question 90 / 2 pts For database development, a challenge to the relational model and the use of SQL is known as ________. You Answered the SOAP movement Correct Answer the NoSQL movement the XML movement the Web services movement Question 102 / 2 pts A(n) ________ is used by a senior manager to extract, summarize, and consolidate operational databases. customer relationship management application Correct! digital dashboard e-commerce site application data mining application Question 112 / 2 pts Which of the following statements is not true for an enterprise-class database system? The DBMS accesses the database data. Correct! The database application(s) directly access(es) the database data. The database application(s) interact(s) with the DBMS. The application generates SQL statements. Question 120 / 2 pts According to the text, Oracle Corporation's Oracle Database is considered the most difficult DBMS to use. Correct Answer True You Answered False Question 132 / 2 pts A tuple is a group of one or more columns that uniquely identifies a row. True Correct! False Question 142 / 2 pts In relational terms as defined by E.F. Codd, a column is called an attribute. Correct! True False Question 150 / 2 pts Given the functional dependency A → (B, C), A is a(n) ________. independent variable You Answered composite determinant Correct Answer determinant dependent variable Question 162 / 2 pts In relational terms as defined by E.F. Codd, a row is called a tuple. Correct! True False Question 172 / 2 pts If a table is designed so that every determinant is a candidate key, then that relation is in ________. Second Normal Form Correct! Boyce-Codd Normal Form First Normal Form Third Normal Form Question 180 / 2 pts A determinant of a functional dependency may or may not be unique in a relation. Correct Answer True You Answered False Question 192 / 2 pts Read-only databases seldom use more than one copy of a set of the same data. True Correct! False Question 200 / 2 pts Reviewing the work done on a table that was created using existing data from multiple sources, you find that a column named Remarks has been included, and it is populated with inconsistent and verbose verbal data. This is an example of ________. Correct Answer the general-purpose remarks column problem You Answered the inconsistent values problem the missing values problem the multivalued, multicolumn problem Question 212 / 2 pts A null value can indicate which of the following conditions? The value can never be changed. The value has been updated. The value is incorrect. Correct! The value is appropriate but unknown. Question 222 / 2 pts An advantage of denormalization is ________. faster copying of data faster design faster updating Correct! faster querying Question 232 / 2 pts Denormalization reduces the complexity of the SQL statements needed in an application to read required data. Correct! True False Question 242 / 2 pts General-purpose remarks columns rarely contain important data. True Correct! False Question 250 / 2 pts Supertype/subtype entities are said to have a(n) ________ relationship. HAS-A Correct Answer IS-A recursive You Answered redundant Question 260 / 2 pts A relationship's maximum cardinality indicates the maximum number of entities that can participate in the relationship. You Answered True Correct Answer False Question 272 / 2 pts Distinguish between an entity class and an entity instance. Your Answer: entity class is the type of entity that can be grouped. entity instance is the moment in time that an entity is related Question 282 / 2 pts A recursive relationship occurs when an entity type has many relationships with other recursive entities. True Correct! False Question 290 / 2 pts A compound identifier is an identifier consisting of two or more attributes. You Answered True Correct Answer False Question 302 / 2 pts An entity's minimum cardinality in a relationship indicates the number of entity instances that must participate in the relationship. Correct! True False Question 310 / 2 pts When the parent entity is required (M) in a relationship, every row of the child table must have a valid, non-null value of the foreign key. Correct Answer True You Answered False Question 320 / 2 pts The binary constraint must not indicates that ________. Correct Answer a binary relationship includes value combinations that must not occur in a ternary relationship You Answered a specific binary relationship must not be included in a ternary relationship a table includes values that must not occur in a ternary relationship a table includes values that must not occur in a binary relationship Question 330 / 2 pts The identifier of the entity becomes the ________ of the corresponding table. Correct Answer primary key subtype supertype You Answered foreign key Question 342 / 2 pts Each attribute of an entity becomes a(n) ________ of a table. alternate key foreign key Correct! column primary key Question 350 / 2 pts A referential integrity constraint policy that insures foreign key values in a table are correctly maintained when there is a change to the primary key value in the parent table is called a cascading insertion. You Answered True Correct Answer False Question 360 / 2 pts The first step in the database design process is to create tables and columns from entities and attributes. Correct Answer True You Answered False Question 370 / 2 pts A stored program that is stored within the database and compiled when used is called a trigger. You Answered True Correct Answer False Question 382 / 2 pts One or more rows can be added to a table by using the INSERT statement. Correct! True False Question 392 / 2 pts Which SQL keyword is used to add one or more rows of data to a table? Correct! INSERT DELETE SELECT UPDATE Question 402 / 2 pts Which SQL keyword is used to delete a table's structure? DISPOSE DELETE ALTER Correct! DROP Question 412 / 2 pts SQL views are updatable when the view is based on a single table with no computed columns, and all non-null columns are present in the view. Correct! True False Question 422 / 2 pts The CREATE TABLE statement is used to name a new table and describe the table's columns. Correct! True False Question 432 / 2 pts Because SQL statements are set-oriented, whereas programs are element-oriented, the results of SQL statements used in programs are treated as ________. Correct! pseudofiles tables files rows Question 440 / 2 pts When dropping a primary key column from a table, which of the following steps is included in the process? (The order of the steps listed below is not relevant, only the steps themselves.) You Answered Drop the primary key(s) of any table(s) to which this one is related Correct Answer Drop the foreign keys in other tables based on the primary key column Drop all constraints from the table Drop the foreign keys from the table Question 452 / 2 pts To add a NULL column to a table, we simply use the MODIFY TABLE statement. True Correct! False Question 460 / 2 pts When dropping a foreign key column from a table, which of the following steps is (are) included in the process? (The order of the steps listed below is not relevant, only the steps themselves.) You Answered Drop the primary key in the other table referenced by the referential integrity constraint Correct Answer Both Drop the foreign key constraint from the table and Drop the foreign key column from the table are correct Drop the foreign key column from the table Drop the foreign key constraint from the table Question 470 / 2 pts Because of the need to know the functional dependencies in a database, it is a good idea to create a(n) ________. You Answered table-relationship diagram Correct Answer dependency graph conceptual schema internal schema Question 482 / 2 pts Database redesign is fairly easy when ________. the design was done correctly the first time good backups of the database are available Correct! there is no data in the database information systems and organizations influence each other Question 490 / 2 pts In the database redesign process, before proceeding with the redesign it is often useful to ________. find out why the design was not done properly the first time You Answered set standards for user behavior Correct Answer check whether certain conditions or assumptions about the data are valid stop information systems and users from influencing each other Question 502 / 2 pts Changing cardinalities ________. always occurs in database redesign never occurs in database redesign Correct! commonly occurs in database redesign rarely occurs in database redesign
Quiz #7 (2nd trial)
Quiz #7 (2nd trial)
Quiz #7 - Question #1
Quiz #7 - Question #1
Quiz #8
Quiz #8
Given the SQL statement: CREATE TABLE SALESREP ( SalesRepNo Int Not Null RepName char(35) Not Null HireDate date Not Null CONSTRAINT SalesRepPK PRIMARY KEY (SalesRepNo) CONSTRAINT SalesRepAK1 UNIQUE (RepName) We know that ________.
RepName is a candidate key
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?
SELECT *FROM EMPLOYEEWHERE Name LIKE 'S*
Given a table with the structure: EMPLOYEE (EmpNo, Name, Salary, HireDate), which of the following is not a valid ANSI SQL command?
SELECT HireDate, COUNT(*)FROM EMPLOYEEWHERE Salary < 30000;
START
START
In an SQL query, which built-in function is used to total numeric columns?
SUM
The industry standard supported by all major DBMSs that allows tables to be joined together is called ________.
Structured Query Language (SQL)
Which of the following is considered to be a weak entity?
The association entity in an association pattern
Which of the following statements is not true for an enterprise-class database system?
The database application(s) directly access(es) the database data.
Explain the essential format of the CREATE TABLE statement. Include an example.
Your Answer: The format of the CREATE TABLE statement is : CREATE TABLE tablename ( column1 datatype, column2 datatype, column3 datatype. "etc..." ); CREATE TABLE Customer ( custId INT, FirstName VARCHAR(20), LastName VARCHAR(25) );
Domain/key normal form requires that every constraint be a logical consequence of the definition of domains and keys.
True
Entities can be associated with one another in relationships.
True
Every time we break up a relation during the normalization process, we may have to create a referential integrity constraint.
True
Given the functional dependency (A, B) → C, the attributes (A, B) are referred to as a composite determinant.
True
If a DEFAULT constraint is included when a new column is added to a table, the default value is only applied to new rows and not to the existing rows at the time the new column is added.
True
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.
True
If the parent is required, then a new child row must be created with a valid foreign key value.
True
In a multivalued attribute pattern many different multivalued attributes can be created.
True
In a real sense, information systems and organizations do not just influence each other, but rather they create each other.
True
In a relational database design, all relationships are expressed by creating a foreign key.
True
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
True
Joins that show only matching rows from the joined tables in their results are called inner joins.
True
Multivalued dependencies create anomalies that must always be eliminated.
True
Multivalued dependencies show up under a different name as the multivalued, multicolumn problem.
True
One of the important properties of an attribute is data type.
True
One or more rows can be added to a table by using the INSERT statement.
True
Referential integrity constraints are rules about what data values are allowed in certain columns.
True
Relations in BCNF have no modification anomalies regarding functional dependencies.
True
Relationships of degree two are referred to as binary relationships.
True
SQL triggers use the ANSI SQL keywords BEFORE, INSTEAD OF, and AFTER.
True
SQL views can be used to hide columns.
True
Stored procedures have the advantage of greater security, decreased network traffic, SQL optimized by the DBMS compiler, and code sharing.
True
The CONSTRAINT keyword can be used in conjunction with the SQL keywords PRIMARY KEY and FOREIGN KEY.
True
The INSERT statement can be used to populate normalized tables.
True
The IS NULL keyword can be used to count the number of nulls in a column.
True
The SQL built-in function AVG computes the average of values in numeric columns.
True
The SQL keyword FROM is used to specify the table to be used.
True
The authors refer to the data model produced by reverse engineering as the RE data model.
True
The continuous circular process of changes is known as the Systems Development Life Cycle (SDLC).
True
The design produced by reverse engineering may be described as a table-relationship diagram.
True
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.
True
The rows of the result table can be sorted by the values in one or more columns.
True
The use of a doubly nested set of NOT EXISTS SELECT statements is a famous pattern in SQL use.
True
There are no good SQL commands that can be used to change table names.
True
There is a common trap in writing a correlated subquery, which will cause no rows to ever be displayed in the results.
True
To refer to a set of values in a condition, the values are placed inside parentheses () and separated by commas.
True
To refer to a set of values needed for a condition, use the IN operator.
True
Unless it is being used to copy data from one table to another, the INSERT statement can be used to insert only a single row into a table.
True
When a trigger is fired, the DBMS makes the appropriate data available to the trigger code.
True
When the child entity is required (M) in a relationship, there needs to always be at least one child row for each parent row.
True
When transforming a data model into a relational design, relationships of all degrees are treated as combinations of binary relationships.
True
When transforming an extended E-R model into a relational database design, recursive relationships are treated fundamentally the same as other HAS-A relationships.
True
When transforming supertype/subtype entities into a relational database design, the key of the supertype table is placed into the subtype table typically as the key.
True
You can combine the SQL INSERT and SQL UPDATE statements into one statement that can insert or update data depending upon whether some condition is met.
True
The purpose of a database is to help people keep track of things.
True - The purpose of a database is to
Which SQL keyword is used to change one or more rows in a table?
UPDATE
shows how to use sp_addumpdevice to define a logical backup device for a disk file. The example adds the disk backup device named mydiskdump, with the physical name c:\dump\dump1.bak.
USE AdventureWorks2012 ; GO EXEC sp_addumpdevice 'disk', 'mydiskdump', 'c:\dump\dump1.bak' ; GO
Explain denormalization, and why it may be appropriate to denormalize a set of tables.
Use redundant data on a pre-normalized database to increase performance.
In an SQL query, which SQL keyword is used to specify the column names to be used in a join?
WHERE
Explain the representation of a one-to-many strong entity relationship in a relational database design.
When one entity's attributes can relate to many of another table and is not reliant on another table for it's existence.
What are SQL triggers and how are they used?
Your Answer: A database object which functions when an event occurs in a database.
A relation is in second normal form (2NF) if and only if it is in 1NF and ________.
all non-key attributes are determined by the entire primary key
To set a column value to an initial value that is selected according to some business logic, you would use ________.
an SQL trigger
Many-to-many relationships are represented by ________.
an intersection table which has 1:N relationships with the two tables
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 existing tables' structure and content
The characteristics of a thing are described by its ________.
attributes
Database redesign because of ________ may not be the best idea.
automation
An attribute is also known as a ________.
column
Hypertext Transport Protocol (HTTP) is a technology used to ________.
connect to web pages over the Internet
Because SQL statements are set-oriented, whereas programs are element-oriented, the results of SQL statements used in programs are treated as ________.
pseudofiles
A ________ is used to limit the possible values of a foreign key.
referential integrity constraint
In database systems, the DBMS enforces rules about which data can be written in certain columns. These rules are known as ________.
referential integrity constraints
In relational database design, ID-dependent entities are not used to ________.
represent relationships where the child identifier does not include the key of the parent
An artificial column added to a relation to serve as the primary key is a ________.
surrogate key
Maximum cardinality refers to ________.
the most instances of one entity class that can be involved in a relationship instance with another entity class
A form of multivalued dependency is found in ________.
the multivalued, multicolumn problem
A relation is in third normal form (3NF) if and only if it is in 2NF and ________.
there are no non-key attributes determined by another non-key attribute
Database redesign is fairly easy when ________.
there is no data in the database
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.
unanswered
When accessing table structure, how do you determine how many rows are in a table?
use SELECT query with COUNT(*) function to return the number of table rows.
An entity whose identifier includes the identifier of another entity is a(n) ________.
weak entity
Modern microcomputer personal DBMS products ________.
were essentially made obsolete by Microsoft Access
If you have a foreign key in a CUSTOMER table that references the primary key in a SALESREP table, the ON DELETE CASCADE syntax means that ________.
when a SALESREP is deleted, all CUSTOMERs of that SALESREP are also deleted
lesson 10 Quiz
Lesson 10 Quiz
Lesson 11 Quiz
Lesson 11 Quiz
Lesson 12 Quiz
Lesson 12 Quiz
Lesson 14 Quiz
Lesson 14 Quiz
Lesson 9 Quiz
Lesson 9 Quiz
Question 1 Discuss the DBMS security model. MY ANSWER: ***** It is a model system implemented to protect the database structure and to secure the integrity of the data information stored in the database. Password and other authentication policies. Permissions/authorization to add, alter, delete, move, etc... Creating views to control users abilities to manipulate the data. Applying constraints. ***** EXTRA COMMENT: ***** (I am sorry but I do not remember anywhere in this learning process thus far that prepared any of us to actually have knowledge to answer this. I am confused... ***** Question 2 Explain the purpose of transaction logs and checkpoints. ***** Transaction logs are the records of transactions made in the database. The stored record could be used by the database to roll back or undo the transaction to restore the data should there be a transaction failure. The checkpoints writes the current transactions that are stored in memory and the transaction logs from memory to disk. It also records the transactions writing them into the transaction log. ***** Question 3 Two-phased locking has a growing phase and a shrinking phase. ***** True Question 4 The task of diagnosing errors due to changes in the database structure is eased by ________. ***** database structure change documentation Question 5 Ensuring that all rows impacted by the actions of a transaction are protected from changes until the entire transaction is completed is called ________. ***** transaction level consistency Question 6 The DBA should periodically analyze run-time statistics of database performance to help manage the DBMS. ***** True Question 7 Recovering a database via rollforward involves ________. ***** restoring the database from the save and reapplying all the changes made by transactions since the save Question 8 Which of the following is true of forward only cursors? ***** Changes made by the transaction are visible only if they occur on rows ahead of the cursor. Question 9 The DBA is responsible for managing changes to the database structure, but is rarely involved in the original design of the structure. ***** False Question 10 Locks with large granularity are easy for the DBMS to administer, but frequently cause conflicts. ***** True Question 11 A dirty read happens when one transaction reads a changed record that has not been committed to the database. ***** True Question 12 In general, the overall responsibility of the DBA is to facilitate the development and use of the database system. ***** True Question 13 A standby data repository is one which requires a person to generate the metadata and place it in the repository. ***** False Question 14 A lock placed automatically by the DBMS is called a(n) ________ lock. ***** implicit Question 15 Recovering a database via rollback involves ________. ***** undoing the changes made by erroneous or partially processed transactions and restarting the valid transactions that were in process at the time of the failure Question 16 Which of the following is not true of DBMS security features? ***** A role may be assigned to only one user. Question 17 Resource locking is one remedy to the lost update problem. ***** True Question 18 Processing responsibilities should be documented and encoded into manual procedures. ***** True Question 19 When a transaction reads a row that has been changed, but the change has not been committed, is known as a ________. ***** Dirty read Question 20 An exclusive lock locks the item from change, but not from read access. ***** False Question 21 With optimistic locking, the assumption is made that no conflict will occur. ***** True Question 22 Which type of data repository is composed of metadata that is created automatically as the system components are created? ***** Active Question 23 A checkpoint is a point of synchronization between the database and the transaction log. ***** True Question 24 Which of the following is allowed by "Repeatable Read Isolation"? ***** Phantom reads Question 25 According to ANSI SQL, the serializable isolation level will not allow phantom reads, dirty reads, and nonrepeatable reads. ***** True
Lesson 9 Quiz
Which of the following database systems is not enterprise-class?
Microsoft Access
Mid-term Midterm Mid term test quiz below - 50 Questions
Mid-term Midterm Mid term test quiz below - 50 Questions
In the database redesign process, an SQL tool that is useful for testing whether certain conditions or assumptions are valid is ________.
NOT EXISTS
How is maximum cardinality expressed in IE Crow's Foot E-R diagrams?
Next to the entity's box a line or hash for maximum of one and a crow's foot for the maximum of many.
Which of the following is not a key element of an E-R model?
Objects
Which of the following does not describe a user-defined function?
It computes a numeric value
How do you create a view?
***** To create a view, a user must have the appropriate system privilege according to the specific implementation. ***** CREATE VIEW view_name AS SELECT column1, column2..... FROM table_name WHERE [condition]; ***** You can include multiple tables in your SELECT statement in a similar way as you use them in a normal SQL SELECT query.*****
The following T-SQL uses msdb.dbo.backup_history table to analyze the growth of the database over a given period. The metrics are useful for capacity planning and forecasting with reference to backup
-- Transact-SQL script to analyse the database size growth using backup history. DECLARE --Define the range to fetch growth metrics @months smallint=6 @endDate datetime=getdate(), ;WITH HISTORY AS (SELECT DBS.database_name AS DBName ,YEAR(DBS.backup_start_date) * 100 + MONTH(DBS.backup_start_date) AS YearMonth ,CONVERT(numeric(10, 1), MIN(DBF.file_size / 1048576.0)) AS MinMB ,CONVERT(numeric(10, 1), MAX(DBF.file_size / 1048576.0)) AS MaxMB ,CONVERT(numeric(10, 1), AVG(DBF.file_size / 1048576.0)) AS AvgMB FROM msdb.dbo.backupset as DBS INNER JOIN msdb.dbo.backupfile AS DBF ON DBS.backup_set_id = DBF.backup_set_id WHERE NOT DBS.database_name IN ('master', 'msdb', 'model', 'tempdb') AND DBF.file_type = 'D' AND DBS.backup_start_date BETWEEN DATEADD(mm, - @months, @endDate ) AND @endDate GROUP BY DBS.database_name ,YEAR(DBS.backup_start_date) ,MONTH(DBS.backup_start_date)) SELECT H.DBName ,H.YearMonth ,H.MinMB ,H.MaxMB ,H.AvgMB ,H.AvgMB - (SELECT TOP 1 H1.AvgMB FROM HISTORY AS H1 WHERE H1.DBName = H.DBName AND H1.YearMonth < H.YearMonth ORDER BY H1.YearMonth DESC) AS DBGrowthMB FROM HISTORY AS H ORDER BY H.DBName ,H.YearMonth
Explain the steps in the process of putting a relation straight into Boyce-Codd Normal Form (BCNF).
1. Identify all functinal dependencirs in the relation. 2. Identify all candidate key in the relation. 3a. If all determinnts are candidate keys...the relation is in BCNF. 3b. If not, then...move columns of functional dependency of the determinant to a new relation that's not a candidate key. 4. Then make the determinant of that functional dependency the primary key. 5. Leave a copy of the determinant in the original relation as a foreign key. 6. Create a referential integrity constraint between the relations.
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?
1:1
In an SQL query, which of the following symbols is used by Microsoft Access to represent a single unspecified character?
? (question mark)
Which of the following is true about the functional dependency (A, B) → (C, D)?
A and B together determine D.
Which one of the following is NOT a principle of database processing.
A logical model must be completed to map all needed changes to the database
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 minimum cardinality of "required"
What is a correlated subquery? Include an example.
A subquery that refers to a column of a table that is not in its FROM clause. EXAMPLE : SELECT StudentID, FirstName, LastName FROM students WHERE grades > ( SELECT AVG (gpa) FROM studentGrades WHERE course = std.course);
In an SQL query, which SQL keyword is used with GROUP BY to select groups meeting specified criteria?
AND
What is an SQL view, and what is it used for? Include an example based on the CUSTOMER table of the General Sales Database.
An SQL view is a virtual table you can use to combine data from multiple areas in a way that is useful to the user without creating a table. It can also be used to hide rows & columns, and complicated SQL syntax. It can be used as security protection, enabling users to query data without granting permissions to directly access to the view's underlying base tables. CREATE VIEW Limited_Cust AS SELECT CustNo, CustName, SalesRepNo FROM customer WHERE balance <= 500;
What is the purpose of a database, and how does the database accomplish this purpose?
As stated above, the purpose is to help people keep track of things. It does this by keeping organization of data in rows and columns of relational tables.
Anomalies caused by functional dependencies can be eliminated by putting tables into ________.
BCNF
For the M-O (parent mandatory, child optional) case, what action(s) should be taken to ensure minimum cardinality is maintained?
Both Define referential integrity constraint from child to parent and Make foreign key NOT NULL are correct
When dropping a foreign key column from a table, which of the following steps is (are) included in the process? (The order of the steps listed below is not relevant, only the steps themselves.)
Both Drop the foreign key constraint from the table and Drop the foreign key column from the table are correct
When dropping tables and relationships, which of the following steps is/are included in the process? (The order of the steps listed below is not relevant, only the steps themselves.)
Both Drop the foreign key constraints from the tables and Drop the tables are correct
Microsoft Access includes ________.
Both a DBMS and an application generator are correct
In a supertype-subtype structure, discriminator attributes
Both cannot be represented in a relational design and require application logic to determine which subtypes correspond to a specific supertype instance are correct
The first step in assessing table structure includes ________.
Both counting rows and examining columns are correct
SQL views are always updatable when ________.
Both the view is based on a single table with no computed columns, and all non-null columns are present in the view and the view is based on any number of tables, with or without computed columns, and the INSTEAD OF trigger is defined for the view are correct
The following transact-SQL script can be used to fetch the growth trend of the database using the backup history table. The backup information is always stored in the msdb database. In some cases, you may get a limited data, based on the maintenance tasks or clean up tasks created to purge data. The backup size details can be found in the dbo.backupset table which contains a row for every successful backup operation.
DECLARE @endDate datetime=getdate(), -- last six month trends @months smallint=6 SELECT [database_name] AS DatabaseName, MAX(YEAR(backup_start_date) * 100 + MONTH(backup_start_date)) AS YearMonth , CONVERT(numeric(10, 1), AVG([backup_size]/1024/1024)) AS BackupSizeMB, CONVERT(numeric(10, 1), AVG([compressed_backup_size]/1024/1024)) AS Compressed-BackupSizeMB, CONVERT(numeric(10, 1), AVG([backup_size]/[compressed_backup_size])) AS Compres-sionRatio FROM msdb.dbo.backupset WHERE [database_name] = N'MES_DW2_PROD' AND [type] = 'D' and backup_start_date BETWEEN DATEADD(mm, - @months, @endDate) AND @endDae GROUP BY [database_name],DATEPART(mm,[backup_start_date]) Order by YearMonth desc
A dependency graph should include all the following except ________.
DEFAULT values
Which keyword is used to remove one or more rows from a table?
DELETE
In an SQL query, which SQL keyword must be used to remove duplicate rows from the result table?
DISTINCT
Which SQL keyword is used to delete a table's structure?
DROP
Which of the following is not an SQL trigger Oracle supports?
DURING
Describe and discuss the main elements of the Entity-Relationship (E-R) model. Include an example.
Entities - are the real world objects that are used in a database, An example of an entity would be like a television or something intangible like a company's list of employees. Attributes - are tje characteristic that describe the entities of the database. Relationships - are how tables are related using the above attributes with similarities.
Explain the relationships among primary keys and functional dependencies.
Functional dependency is a relationship that typically exists between the primary key and non-key attribute within a table. Primary key is the determinant for each of the nonkeys.
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 or columns?
GROUP BY
What is an ID-Dependent entity? Include an example.
ID-dependent entity is an entity whose identifier includes the identifier of another entity. For example, using MODEL_NUM and SERIAL_NUM. As many devices will share model number. Many devices in the world may also share serial number, however, only one is most likely going to share a model number and a serial number.
Which SQL keyword is used to add one or more rows of data to a table?
INSERT
Explain the pragmatic reason for using surrogate keys.
Instead of frequently altering or manipulating large amounts of data created by a lengthy primary key text field, use a surrogate key that is generated system and typically smaller and more manageable.
What is the main difference between an entity and a table?
The principal difference between an entity and a table is that you can express a relationship between entities without using foreign keys. In the E-R model, you can specify a relationship just by drawing a line connecting two entities. Because you are doing logical data modeling and not physical database design, you need not worry about primary and foreign keys, referential integrity constraints and the like.
The following database will be used in this question: GENERAL SALES DATABASE: SALESREP Explain the use of the SQL statement CREATE TABLE. Do NOT discuss the ALTER statement in your answer but DO include an example based on the SALESREP table in the General Sales database.
The use of the CREATE TABLE statement is to create a table and supply column data. CREATE TABLE SALESREP ( SalesRepNo INTEGER, RepName VarChar(40), HireDate Date );
A null value can indicate which of the following conditions?
The value is appropriate but unknown.
What are referential integrity constraints? Give an example.
They are foreign key constraints. Rules set that control the values in columns of tables
Which of the following is not true about surrogate keys?
They are non-unique within a table.
A characteristic of a relation is that the cells of the relation hold a single value.
True
A deletion anomaly exists when deleting data about one entity results in the loss of data about another entity.
True
A determinant of a functional dependency may or may not be unique in a relation.
True
A key of an intersection table is always the combination of the keys of both parents.
True
A multivalued dependency exists when a determinant is matched to a set of values.
True
A primary key is a candidate key that has been selected to uniquely identify rows in a relation.
True
A referential integrity constraint limits the values of a foreign key.
True
A set of SQL statements stored in an application written in a standard programming language is called embedded SQL.
True
A surrogate key is appropriate when the primary key of a table contains a lengthy text field.
True
According to the text, Oracle Corporation's Oracle Database is considered the most difficult DBMS to use.
True
All identifying relationships are 1:N.
True
All relations are tables, but not all tables are relations.
True
An SQL virtual table is called a view.
True
An alternative to combining tables by a subquery is to use a join.
True
An asterisk (*) following the SELECT verb means that all columns are to be displayed.
True
An attribute is a non-key attribute when it is a non-prime attribute, which means that the attribute is not contained in any candidate key.
True
An entity class is described by the structure of the entities in that class.
True
An entity that represents something that can exist on its own is called a strong entity.
True
An entity's minimum cardinality in a relationship indicates the number of entity instances that must participate in the relationship.
True
An inconsistent values problem is created when different users have coded the same data entries differently.
True
Applications are computer programs used directly by users
True
Attribute Y is functionally dependent on attribute X if the value of attribute X determines the value of Y.
True
Because SQL statements are table-oriented, whereas programs are variable-oriented, the results of SQL statements used in programs are accessed using an SQL cursor.
True
Because SQL statements are table-oriented, whereas programs are variable-oriented, the results of SQL statements used in programs are treated as pseudofiles.
True
Because SQL stored procedures allow and encourage code sharing among developers, stored procedures give database application developers the advantages of less work, standardized processing, and specialization among developers.
True
Because of the need to know the functional dependencies in a database, it is a good idea to create a dependency graph.
True
Business Intelligence (BI) systems typically store their data in data warehouses.
True
Data values to be added to a table are specified by using the VALUES clause.
True
Database design varies depending on whether you're building an updatable database or a read-only database.
True
The following database will be used in this question:GENERAL SALES DATABASE: SALESREP What is an SQL view, and what is it used for? Include an example based on the CUSTOMER table of the General Sales Database.
Your Answer: An SQL view is a virtual table you can use to combine data from multiple areas in a way that is useful to the user without creating a table. It can also be used to hide rows & columns, and complicated SQL syntax. It can be used as security protection, enabling users to query data without granting permissions to directly access to the view's underlying base tables.
Distinguish between Char and VarChar data types. Include examples and tradeoffs.
Your Answer: Char uses static memory location, whereas, VarChar uses dynamic. For example: Char always knows where the end of string is and VarChar has to first find the end of the string before searching. Char is fixed-length, while VarChar is variable length. These characteristics make Char faster than VarChar. ------------------------------------------------------------------ The tradeoff between the two are...speed vs size. If you want a faster search process, go with Char. If you want to use less space...go with VarChar. For example... By VarChar being variable length, it uses only as much space as needed. With Char's fixed length, that declared amount of space will be used regardless of the actual size.
What is embedded SQL, and what considerations are necessary when using it in an application?
Your Answer: SOL statements stored in an application written in a standard programming language. Using the power of the programming language with the database manipulation capabilities of SQL.
When redesigning a database, how can we check assumptions about functional dependencies?
Your Answer: Use a correlated subquery with the SQL IN keyword Use SQL EXISTS and / or NOT EXISTS keywords with a correlated subquery structure.
Explain how relationships are created using SQL. Include an example.
Your Answer: when a function key in one table is the primary key of another table. For example... The primary key of the table, "SALESREP" (SalesRepNo), is also the foreign key of the table "CUSTOMER"
What is the process for adding a NOT NULL column to a table?
add a column as NULL column, add information to the column using UpDATE, use ALTER, changing the column from NULL to NOT NULL.
How is minimum cardinality expressed in IE Crow's Foot E-R diagrams?
a circle ("O") listed as the inside (minimum) cardinality for the option of zero or no items.
Regarding the interchangeability of subqueries and joins, ________.
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
To represent a multivalued attribute in an E-R model, ________.
create a new ID-dependent entity with a 1:N relationship
To add a NOT NULL column to a table, we ________.
create a new NULL column, insert data values into every row, and change the NULL constraint to NOT NULL
A program whose job is to create, process and administer databases is called a ________.
database management system
A very popular development technique used by database professionals to adopt a database design to a new or changing requirement is known as ________.
database migration
A set of SQL statements stored in an application written in a standard programming language is called ________.
embedded SQL
Entities of a given type are grouped into an ________.
entity class
Stored procedures have all these advantages except ________.
faster development
The predecessors of database processing were _
file managers
Having to enter facts about two entities when we want to enter facts about only one is an example of ________.
insertion anomaly
A combination of one or more columns used to identify rows in a relation is a ________.
key
The data model produced by reverse engineering is a(n) ________.
table-relationship diagram
What components are included in a database?
tables columns rows database Language
What components are included in a database?
tables, columns, rows, database language
testing
testing
testing 2
testing 2
testing 3
testing 3
testing 4
testing 4
testing 5
testing 5
SQL triggers are created using ________.
the SQL CREATE TRIGGER statement
Reviewing the work done on a table that was created using existing data from multiple sources, you find that a column named Remarks has been included, and it is populated with inconsistent and verbose verbal data. This is an example of ________.
the general-purpose remarks column problem
The problem of misspelled data entries is really the same as ________.
the inconsistent values problem
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 ________.
the inconsistent values problem
A surrogate key should be considered when ________.
the key contains a lengthy text field
When representing a 1:1 relationship in a relational database design, ________.
the key of either table may be placed as a foreign key into the other
To represent a one-to-many relationship in a relational database design, ________.
the key of the parent is placed as a foreign key into the child
In many-to-many relationships in a relational database design,
the keys of both tables are placed in a third table
In many-to-many relationships in a relational database design, ________.
the keys of both tables are placed in a third table
In many-to-many relationships in a relational database design,
the keys of both tables are placed in a third table and the keys of both tables are joined into a composite key
