Database Management Systems_CSCI_6441 Midterm_All in one George Washington University 700 PAGES BEST LAYOUT AND DISPLAY HIGH SCORES

Question 1 Selected Answer: True Correct Answer: False Cloud developers create applications and host them on SaaS provider sites, which provide an operating environment and hardware. Question 2 Selected Answer: Correct Answer: C. SaaS _____________ a complete software application with a user interface. C. SaaS 0 out of 0.5 points 0.5 out of 0.5 pointsCorrect Answer: C. SaaS Question 3 Selected Answer: a. Correct Answer: a. Response Feedback: Which statement would you use to migrate a LONG data type to a LOB data type? ALTER TABLE ALTER TABLE You would use the ALTER TABLE statement to migrate a LONG data type to a LOB data type. You can migrate LONG columns to LOB columns to move existing tables containing LONG columns to use LOBs. To migrate the data from a LONG column to a CLOB or an NCLOB or a LONG RAW to a BLOB, use the ALTER TABLE statement. You can migrate a LONG data type column to a LOB data type. All of the other options are incorrect because they cannot be used to migrate a LONG data type to a LOB data type. 0.5 out of 0.5 pointstype. Question 4 Selected Answer: False Correct Answer: False A DDoS attack employs a single dedicated computer to attack a target site. Question 5 Examine the data in the TEACHER table. Assume the user enters the following SELECT statement to retrieve data from the TEACHER table: SQL SELECT * FROM teacher WHERE INSTR(subject_id, '&1') = 4 AND LOWER(subject_id) LIKE 'HST%'; When prompted for the WHERE clause value, you enter an underscore ( Which result will this statement provide? 0.5 out of 0.5 points 0.5 out of 0.5 pointsSelected Answer: Correct Answer: Response Feedback: It will execute, but it will not retrieve any data. It will execute, but it will not retrieve any data. INSTR(column|expression, 'string' [, m] [, n]) This statement will execute, but it will not retrieve any data. In the second condition of the WHERE clause, the values in the SUBJECT_ID are converted to lowercase and then compared to a character string in uppercase, which results in no rowsbeingreturned.SincethisconditionisALWAYS FALSE,andthisconditionisjoinedbyanANDwiththefirstcondition, thecompoundconditionwillbeALWAYS FALSEaswell.Consequently,thetruthvalueofthefirstconditiondoesnotneedto be evaluated to answer this question. A SELECT statement in which the WHERE clause is ALWAYS FALSE will always give the results " returned". To display information on only teachers whose SUBJECT_ID 'HST', you could add AND subject_id LIKE 'HST%' to the To display the same information regardless of the case in which the SUBJECT_ID is stored, you could add AND UPPER(subject_id) LIKE 'HST%' to the WHERE clause. The UPPER character function converts a mixed case or lowercase value (a column or expression) to uppercase. The syntax of the character function is: UPPER(column|expression) The LOWER character function converts a mixed case or uppercase value (a column or expression) to lowercase. The syntax of the LOWER character function is: LOWER(column|expression) The INSTR character function is used to find the position of an occurrence of a string of characters within a column value or an expression. The Oracle Server begins its character string search at an integer identifying a character in the supplied column value or expression (m). The search will continue for a particular occurrence of the string indicated by an integer (n). The syntax of the INSTR function is: Question 6 A determinant is any attribute whose value determines 0.5 out of 0.5 pointsSelected Answer: False Correct Answer: False A determinant is any attribute whose value determines other values within a column. Question 7 Selected Answer: Correct Answer: The attribute type 'flightNR' can be used to uniquely identify a flight. Which of the following statements about this EER model is correct? The attribute type 'flightNR' can be used to uniquely identify a flight. Question 8 Selected Answer: D. Correct Answer: D. none of these If set S{A,B,C} is a candidate key for the relation R(A,B,C,D,E), then which of the following must be true none of these 0.5 out of 0.5 points 0.5 out of 0.5 pointsResponse Feedback: Complexity: Moderate Section: 6.4 Question 9 Selected Answer: c. caseinsensitive Correct Answer: c. caseinsensitive In the collation, utf8_spanish_ci, the ci stands for: Question 10 SELECT FROM WHERE (SELECT COUNT(*) WHERE P2.AVAILABLE_QUANTITYP1.AVAILABLE_QUANTITY ) Selected Answer: Correct Answer: Product name and available quantity of the 5 products with the highest availability. What result does the following query return? P1.PRODNAME, P1.AVAILABLE_QUANTITY PRODUCT P1 5 FROM PRODUCT P2 Product name and available quantity of the 5 products with the highest availability. Question 11 0.5 out of 0.5 points 0.5 out of 0.5 points 0 out of 0.5 pointsSelected Answer: b. database analysis and requirements Correct Answer: d. distributed database design In the context of the database design process, the conceptual design step that defines the fragmentation and allocation strategy is _____. Question 12 Selected Answer: a. Correct Answer: a. Examine this package specification: CREATE OR REPLACE PACKAGE prod_pack IS PROCEDURE order_product (p1 IN NUMBER, p2 IN NUMBER); PROCEDURE order_product (p1 IN NUMBER, p2 IN VARCHAR2); PROCEDURE order_product; END prod_pack; Which header can be added to this package specification without violating the rules of package overloading? PROCEDURE order_product (p1 VARCHAR2); PROCEDURE order_product (p1 VARCHAR2); 0.5 out of 0.5 pointsResponse Feedback: VARCHAR2); This header can be added without violating the rules of package overloading: PROCEDURE order_product (p1 VARCHAR2); To take advantage of overloading, a package is created with two different subprograms with the same name, but different argument lists. The arguments must differ in number, order, or data type family. All of the other options are incorrect. Subprograms with argument lists that only differ in names are not able to be overloaded, and an error will occur at runtime. Question 13 Selected Answer: a. Use comments to describe what each statement does. Which of the following recommendations won’t improve the readability of your SQL statements? 0.5 out of 0.5 pointsdoes. Correct Answer: a. Use comments to describe what each statement does. Question 14 Selected Answer: A. Correct Answer: A. {A,B,C,D} is also a superkey for R Response Feedback: If set S{A,B,C} is a superkey for the relation R(A,B,C,D,E), then {A,B,C,D} is also a superkey for R Complexity: Moderate Section: 6.4 Question 15 Selected Answer: D. A D Correct Answer: D. Decompose R into R1(A,B,C,D) and R2(D) Response Feedback: In the relation R(A,B,C,D) if D is multivalued and the apparent key is A, which of the following is NOT an acceptable way to create a first normal form table for this schema? Decompose R into R1( ,B,C,D) and R2( ) Complexity: Difficult Section: 6.4 0.5 out of 0.5 points 0.5 out of 0.5 pointsQuestion 16 Selected Answer: d. Physical design Correct Answer: d. Physical design _____ is the process of determining the data storage organization and data access characteristics of the database to ensure its integrity, security, and performance. Question 17 Selected Answer: True Correct Answer: True A cloud file system (CFS) allows users or applications to directly manipulate files that reside on the Question 18 Selected Answer: C. Correct Answer: C. CLUSTER Response Feedback: An SQL index specification that causes records with the same values on the indexed field to be stored close together is CLUSTER Complexity: Easy Section: 5.3 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 pointsQuestion 19 Selected Answer: D. Correct Answer: D. QBE Response Feedback: Which of the following is a graphical relational data language? QBE Complexity: Easy Section: 4.5 Question 20 Selected Answer: False Correct Answer: False A good relational DBMS excels at managing denormalized relations. Question 21 Selected Answer: b. CREATE PUBLIC SYNONYM Correct Answer: b. CREATE PUBLIC SYNONYM To create a public synonym, the user requires this privilege: 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 pointsQuestion 22 Selected Answer: b. Correct Answer: b. Response Feedback: Which is a true statement regarding a bodiless package? A bodiless package is used to initialize global variables. A bodiless package is used to initialize global variables. The option stating that a bodiless package is used to initialize global variables is correct. A bodiless package contains only a package specification. In the package specification, you can define variables, constants, types, and exceptions that do not have any further implementation. You can also define global variables in a bodiless package. These variables can be used by subprograms, such as functions, procedures, and database triggers. For example, to define a global variable v_global , you can create a bodiless package as follows: CREATE PACKAGE bodiless_pkg AS 0.5 out of 0.5 pointsAS v_global CONSTANT REAL := 100.00; v_number INT; END bodiless_pkg; In this code, the v_global variable is assigned the value 100.00 when the bodiless_pkg package is created. This variable is a global variable and can be used by subprograms in the database. All other options are incorrect. Information inside a package can be hidden by specifying the subprogram constructs in the package body. The subprogram constructs are local to the package body, and the information can be read from the package body. The package in this scenario is a bodiless package, and therefore cannot be used to hide private information. A bodiless package cannot be used to create a one-time-only procedure because a one-time-only procedure is declared in the package body. In this scenario, the package created is a bodiless package and cannot be used to create a one-time-onlycreate a one-time-only procedure. A onetime-only procedure is declared in the executable section of a package body and is executed only after the package is invoked for the first time in a session. For a function to be available for all the database users, it must first be declared in the package specification and then defined in the package body. To define a function, the package must include a package body in which the declared function can be defined. The package in this scenario is a bodiless package and cannot be used to define a function. Question 23 Selected Answer: False Correct Answer: False To list the contents of a table, you must use the DISPLAY command. 0.5 out of 0.5 pointsQuestion 24 Selected Answer: True Correct Answer: True RDBMSs enforce integrity rules automatically. Question 25 Selected Answer: C. Correct Answer: C. drop the table Response Feedback: The SQL ALTER TABLE command can be used to do all of the following except drop the table Complexity: Easy Section: 5.3 Question 26 Selected Answer: C. Correct Answer: C. the value is unknown Response Feedback: In the E-R model, if the value of an attribute is null, it often means the value is unknown Complexity: Easy Section: 3.3 Question 27 Each row in a relational table is called a(n) 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 pointsSelected Answer: B. Correct Answer: B. tuple Response Feedback: Each row in a relational table is called a(n) tuple Complexity: Easy Section: 4.2 Question 28 Selected Answer: a. Correct Answer: d. Response Feedback: Which statement about declaring arguments for procedures is true? Precision must be specified for VARCHAR2 arguments. Formal arguments allow you to transfer values to and from the calling environment. Formal arguments allow you to transfer values to and from the calling environment. The three procedural parameter modes are IN , OUT , and IN OUT . The IN parameter mode is used to pass a constant value from the calling environment to the procedure. The OUT parameter mode is used to pass a constant value from the 0 out of 0.5 pointsprocedure to the calling environment. The IN OUT parameter mode is used to pass a value from the calling environment into the procedure and maybe a different value back to the calling environment using the same parameter. The option stating that precision must be specified for VARCHAR2 arguments is incorrect. Specifying the precision for a formal argument is not allowed. The option stating that each declared argument must have a mode specified is incorrect because IN is the default parameter mode. The option stating that as IN argument passes a value from a procedure to the calling environment is incorrect. An IN argument is used to pass a value from the calling environment to the procedure. Question 29 0.5 out of 0.5 pointsSelected Answer: c. temporary Correct Answer: c. temporary An example of denormalization is using a _____ denormalized table to hold report data. This is required when creating a tabular report in which the columns represent data that are stored in the table as rows. Question 30 Selected Answer: D. Correct Answer: D. PRODUCT Response Feedback: In the relational model, if we combine all the tuples of one table with all the tuples of a second table, the result is the PRODUCT Complexity: Easy Section: 4.5 Question 31 Selected Answer: Correct Answer: Response Feedback: Which datatype is a hexadecimal string representing the unique address of a row in its table? ROWID ROWID The ROWID datatype is a hexadecimal string that represents the unique address of a row in its table. ROWID values can be queried just like other values in a table. 0.5 out of 0.5 points 0.5 out of 0.5 pointsvalues in a table. Because ROWID provides a unique identifier for each row in the table, it can be used to locate a row in a table. The RAW datatype allows for the storage of binary data of a specified size. When data is stored as type RAW or LONG RAW, the Oracle Server does not perform character set conversion when the data is transmitted across machines in a network or when data is moved from one database to another. The BFILE datatype stores unstructured data in operating system files. TheVARCHAR2 datatype stores variable-length character data and uses only the number of bytes needed to store the actual column value. Question 32 Selected Answer: Evaluate the following SELECT statement: SELECT DISTINCT emp_id FROM emp e JOIN emp_hist h ON _id = _id; Which SELECT statement will return the same result as the given statement? SELECT emp_id 0 out of 0.5 pointsCorrect Answer: FROM emp UNION SELECT emp_id FROM emp_hist; SELECT emp_id SELECT emp_id FROM emp INTERSECT SELECT emp_id FROM emp_hist; Question 33 Selected Answer: b. Correct Answer: b. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE Which of the following queries will output the table contents when the value of V_CODE is equal to 21344? SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344; FROM PRODUCT WHERE V_CODE = 21344; Question 34 0.5 out of 0.5 points 0.5 out of 0.5 pointsSelected Answer: Correct Answer: C. multitenant Many SaaS solutions are __________, which means that behind the scenes customers may share computing resources. C. multitenant Question 35 Selected Answer: True Correct Answer: True A data dictionary is sometimes described as “the database designer’s database” because it records the design decisions about tables and their structures. Question 36 Selected Answer: B. Correct Answer: B. disjoint Response Feedback: In an EE-R diagram, the letter d in a specialization circle stands for disjoint Complexity: Easy Section: 3.10 Question 37 To analyze the company situation, the database designer must discover what the company's operational components are, how they function, and how they interact. 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 pointsSelected Answer: True Correct Answer: True components are, how they function, and how they interact. Question 38 Selected Answer: a. Correct Answer: d. Response Feedback: The database administrator has informed you that the CREATE PROCEDURE privilege has been granted to your account. Which objects can you now create? procedures only procedures, functions, and packages You can create procedures, functions, and packages. The CREATE PROCEDURE privilege allows a user to create procedures, functions, and packages. All of the other options are incorrect because there are not separate privileges to create functions and packages. 0 out of 0.5 pointsQuestion 39 Selected Answer: a. column name, alias, expression, or column number Correct Answer: a. column name, alias, expression, or column number When you code an ORDER BY clause, you can specify a Question 40 Selected Answer: b. 1:1 Correct Answer: b. 1:1 At the implementation level, the supertype and its subtype(s) depicted in a specialization hierarchy maintain a(n) _____ relationship. Question 41 Selected Answer: True Correct Answer: True In the detailed systems design phase of the Systems Development Life Cycle (SDLC), steps are laid out for the conversion of a database from an old system to a new system. 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 pointsQuestion 42 Selected Answer: C. Correct Answer: C. naive users Response Feedback: Database users who access data only through application programs are called naive users Complexity: easy Section: 1.4 Question 43 Selected Answer: b. Correct Answer: b. SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH*P_PRICE Which of the following queries will use the given columns and column aliases from the PRODUCT table to determine the total value of inventory held on hand? SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH*P_PRICE FROM PRODUCT; FROM PRODUCT; Question 44 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 pointsSelected Answer: b. natural identifier Correct Answer: b. natural identifier If one exists, a data modeler uses a _____ as the primary key of the entity being modeled. Question 45 Selected Answer: c. reduces redundancy and makes maintenance easier Correct Answer: c. reduces redundancy and makes maintenance easier To apply the second normal form, you move columns that don’t depend on the entire primary key to another table and establish a relationship between the two tables. This Question 46 Selected Answer: c. SELECT Correct Answer: c. SELECT A view is a/an ________________ statement that’s stored as an object in the database. 0.5 out of 0.5 points 0.5 out of 0.5 pointsCorrect Answer: c. SELECT Question 47 Selected Answer: d. action Correct Answer: d. action Insert, Update, and Delete statements can be referred to as ________________ queries. Question 48 Selected Answer: c. data dictionary Correct Answer: c. data dictionary A _____ contains at least all of the attribute names and characteristics for each table in the system. Question 49 Selected Answer: d. NOT IN Correct Answer: d. NOT IN The _____ operator could be used in place of MINUS if the DBMS does not support it. 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 pointsQuestion 50 Selected Answer: d. of invoices for each vendor that have a larger balance due than the average balance due for all invoices Correct Answer: d. of invoices for each vendor that have a larger balance due than the average balance due for all invoices Code example 6-1 SELECT VendorName, COUNT(*) AS NumberOfInvoices, MAX(InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID WHERE InvoiceTotal - PaymentTotal - CreditTotal (SELECT AVG(InvoiceTotal - PaymentTotal - CreditTotal) FROM Invoices) GROUP BY VendorName ORDER BY BalanceDue DESC; (Please refer to code example 6-1.) When this query is executed, the NumberOfInvoices column for each row will show the number Question 51 One important inheritance characteristic is that all entity subtypes inherit their _____ key attribute from their 0.5 out of 0.5 points 0.5 out of 0.5 pointsSelected Answer: c. primary Correct Answer: c. primary subtypes inherit their _____ key attribute from their supertype. Question 52 Selected Answer: B. Correct Answer: B. COMMIT Response Feedback: The SQL statement used to end a transaction that makes any changes permanent is COMMIT Complexity: Easy Section: 5.6 Question 53 Selected Answer: b. Correct Answer: d. Response Feedback: In dynamic SQL that performs DML operations, in which phase is the number of processed rows returned? fetch execute In dynamic SQL performing DML operations, the number of processed rows are returned during the execute phase. The execute phase, which is the 0.5 out of 0.5 points 0 out of 0.5 pointsphase, which is the last phase of statement execution, returns the number of rows processed by the SQL statement that performs DML operations, such as INSERT , UPDATE , and DELETE . Bind is an incorrect option because this phase does not return the rows processed by the statement. The bind phase, which is the second phase of statement execution, involves the binding of variables with their values. Fetch is an incorrect option because this phase does not return the rows processed by the statement. The fetch phase of statement execution involves selection and ordering of rows if an ORDER BY clause is used in the SELECT statement. Each successive fetch retrieves one row of result until the last row has been fetched. The fetch phase is involved only in the SELECT statement. Parse is an incorrect option because this phase does not return the rows processed by the statement. The parse phase is the first phase of statement execution in which aexecution in which a parse tree is generated for a SQL statement. This phase involves checking for privileges and syntax used in the SQL statement. Question 54 Selected Answer: True Correct Answer: True Copy of *True or false? Office 365 is a cloud-based implementation of the Microsoft Office suite of software Question 55 Selected Answer: a. 5 rows (or more) starting with row 100 Correct Answer: d. 5 rows starting with row 100