Midterm MIS 3123

Consider the following SQL query - SELECT P1.PRODNR FROM PRODUCT P1 WHERE 5<=(SELECT COUNT (*) FROM PRODUCT P2 WHERE P1.PRODNR < P2.PRODNR) This query selects which of the following? a. The five highest product numbers. b. The five lowest product numbers c. All product numbers except for the five highest product numbers. d. all product numbers except for the five highest product numbers.

d. all product numbers except for the five highest product numbers.

If you want to use a DBMS architecture that can access multiple data sources (often from disparate systems across the organization) itself and provides a uniform interface hiding the low-level details, which of the following would be the most appropriate? a. cloud b. n-tier c. client-server d. federated

d. federated

Which of the following represents a recursive relationship type? a. binary 1:1 b. binary M;N c. unary d. n-ary

d. n-ary

When, during crash recovery, aborted transactions need to be undone, that is a task of the _________________, while the part of the storage manager that guarantees the ACID properties is the _______________-. a. recovery manager / buffer manager b. lock manager / recovery manager c. lock manager / lock manager d. recovery manager / transaction manager

d. recovery manager / transaction manager

Why can data incompleteness prove to be useful information? a. We can track down faults in the database model, such as updating errors that cause inconsistencies. b. We can track down the source of the incompleteness and thereby eliminate the cause of it. c. We can track down certain patterns in the incomplete fields, which can lead to more information about a certain user. d. all of the above

d. all of the above

The following table with purchase orders is created: CREATE TABLE PURCHASE_ORDER (PONR CHAR(7) NOT NULL PRIMARY KEY, PODATE DATE, SUPNR CHAR(4) NOT NULL, FOREIGN KEY (SUPNR) REFERENCES SUPPLIER (SUPNR) ON DELETE CASCADE ON UPDATE CASCADE); What happens upon the deletion of a supplier? a. All purchase order records tied to that supplier are also deleted. b. The SUPNR of this supplier is replaced by a NULL value in PURCHASE_ORDER c. The SUPNR of this supplier is deleted in PURCHASE_ORDER. d. The SUPNR of this supplier is only deleted in SUPPLIER.

a. All purchase order records tied to that supplier are also deleted.

A foreign key can be NULL in a unary relationship. a. True b. False

a. True

Consistency issues can arise due to sharing data across multiple departments. a. True b. False

a. True

In a relational database model, a foreign key can be NULL. a. True b. False

a. True

The DDL compiler complies data definitions specified in the DDL. it is possible that there is only one DDL with three instruction sets. a. True b. False

a. True

The accuracy of a database depends on its representational and contextual characteristics. a. True b. False

a. True

The catalog forms the heart of the database. It can be an integral part of the DBMS or a standalone component. a. True b. False

a. True

Which of the following is not a KPI used to measure database performance? a. bandwidth utilization b. response time c. space utilization d. throughput rate

a. bandwidth utilization

What type of independence exists between the external layer and the conceptual/logical layer in the data model? a. logical data independence b. conceptual layer independence c. the internal layer d. psychical layer independence

a. logical data independence

A relation is in 3 NF if it satisfies 2NF and which of the following? a. no non-prime attribute type of R is transitively dependent on the primary key. b. no prime attribute type of R is transitively dependent on the primary key. c. no prime key of R is transitively dependent on a prime attribute type. d. no non-primary key of R is transitively dependent on a prime attribute type.

a. no non-prime attribute type of R is transitively dependent on the primary key.

in CODASYL, a mulit-valued composite attribute can be representaed as a ____________________. a. record type b. data item c. vector d. repeated group

a. record type

Which of these is NOT part of the storage manager in the DBMS architecture? a. Recovery manager b. Connection manager c. Transaction manager d. Buffer manager

b. Connection manager

If a hierarchical model contains N:M relationship types that have been integrated by repeating the child nodes where necessary, what are the dangers of updating the database? a. Slower retrieval of data b. Creating data inconsistency c. Creating unnecessary records d. All of the above

b. Creating data inconsistency

Which concept specifies the various data items, their characteristics, and relationships, constraints, storage details, etc., and is specified during the database design? a. Catalog b. Database modeling c. Database state d. External modeling

b. Database modeling

If a database is set up correctly data quality does not need to be checked regularly, as it was established during the implementation phase. a. True b. False

b. False

In CODASYL, a member record has to be associated with at least one owner record. a. True b. False

b. False

In a relational database model, a foreign key of a relation A cannot refer to the primary key of the same relation A. a. True b. False

b. False

In a relational database model, a relation cannot have more than one foreign key. a. True b. False

b. False

In a relational database model, every relation must have a foreign key. a. True b. False

b. False

It is possible to model the following constraint with a hierarchical database; the human resources department of a university wants to make sure that every course has exactly one main professor. a. True b. False

b. False

SQL is a DML language to retrieve, insert, delete, and modify data. It is stored in the catalog. a. True b. False

b. False

Subjectivity does not cause data issues and can be very useful in summarizing data. a. True b. False

b. False

The Boyce-Codd normal form is more strict than the fourth normal form. a. True b. False

b. False

The DML in a hierarchical DBMS that is procedural, record-oriented, and includes a query processor. a. True b. False

b. False

The catalog does not contain the integrity rules of the database. Those are contained in the middleware. a. True b. False

b. False

We are interested in wine stores in which "wine" is in the name of the store. We want to return the SUPNR and SUPNAME of any stores that meet this criterion. Will the following query return the required results? SELECT SUPNR, SUPNAME FROM SUPPLIER WHERE SUPNAME = "WINE" a. True b. False

b. False

With procedural DML, the DBMS determines the access part and navigational strategy to locate and modify the data specified in the query. a. True b. False

b. False

Consider the following two queries - 1. SELECT COUNT(DISTINCT SUPNR) FROM PURCHASE_ORDER 2. SELECT COUNT(SUPNR) FROM PURCHASE_ORDER Which of the following statements is correct? a. Result query 1 is always = result query 2 b. Result query 1 is always <= result query 2 c. Result query 1 is always >= result query 2 d. Result query 1 can be >= sometimes or <= other times depending on the input

b. Result query 1 is always <= result query 2

Which statement regarding the database state is correct? a. The database state specifies the various data items, their characteristics, and the relationships, and is specified during the database design. b. The database state changes when data are updated or removed. c. The database state is stored in the catalog. d. The database state represents the data in the database when the database is first created.

b. The database state changes when data are updated or removed.

Consider the following query - SELECT PRODNAME FROM PRODUCT WHERE PRODNR IN (SELECT PRODNR FROM SUPPLIES WHERE SUPNR IN (SLELECT SUPNRFROM SUPPLIERWHERE SUPCITY = 'New York')) AND PRODNR IN (SELECT PRODNR FROM SUPPLIES WHERE SUPNR IN (SELECT SUPNRFROM SUPPLIERWHERE SUPCITY = 'Washington')) What is the result of the query? a. The query retrieves the product name of each product that has a supplier in New York or Washington. b. The query retrieves the product name of each product that has both a supplier in New York and a supplier in Washington. c. The query retrieves the product name of each product along with all possible supplier cities. d. The query incorrectly combines every product name and supplier city.

b. The query retrieves the product name of each product that has both a supplier in New York and a supplier in Washington.

Consider the following query - SELECT PRODNR, AVG(QUANTITY) AS AVG_QUANTITY FROM PO_LINE GROUP BY PRODNR HAVING SUM(QUANTITY) < 15 What is the result? a. The query returns the PRODNR and average QUANTITY of each purchase order that has fewer than 15 purchase order lines. b. The query returns the PRODNR and average QUANTITY of each product that has fewer than 15 purchase order lines. c. each product that has fewer than 15 orders. d. The query returns the PRODNR and average QUANTITY of each purchase order that has fewer than 15 orders

b. The query returns the PRODNR and average QUANTITY of each product that has fewer than 15 purchase order lines.

Consider a data model for the Olympics storing information about countries and athletes. There is a 1:N relationship type between country and athlete and an athlete always had to belong to exactly one country. A relational data model containing only one table leads to which of the following? a. Unnecessary replication of data about athletes. b. Unnecessary replication of data about countries. c. Unnecessary replication of data about athletes and countries. d. No unnecessary replication of data.

b. Unnecessary replication of data about countries.

Which of the following contains all the descriptions of the metadata components that are defined in the metamodel? a. database b. catalog c. EER model d. representation categories

b. catalog

The part of the storage manager that ensures that more than one end-user cannot write to the same record simultaneously is which of the following? a. buffer manager b. lock manager c. transaction manager d. recovery manager

b. lock manager

Which of the following architectures would you use if you wanted to had multiple servers you wanted to include with your implementation of a client-server solution. You would need to manage the servers with a middleware solution. a. cloud b. n-tier c. in memory d. federated

b. n-tier

Consider the following relational model, (primary keys are underlined, foreign keys are italicized.) STUDENT (student_number, name, address, phone number) Which fields are candidates for 1NF? Select all that apply. a. phone number b. name c. address d. all fields are normalized

b. name c. address

Which statement is not correct? a. Examples of implementation of logical data models are hierarchical, CODASYL, relational, or object-oriented models. b. A logical data model translates the conceptual data model to a specific implementation environment. c. A conceptual data model is implementation-dependent d. In a conceptual data model, the data requirements from the business should be captured and modeled.

c. A conceptual data model is implementation-dependent

Which of the following DBMS types is NOT a classification based on a data model? a. Hierarchical b. Network c. Cloud d. Object - relational

c. Cloud

Which statement is not correct? a. In a database approach, maintenance of data and metadata is easier. b. In a database approach, applications don't have their own files, but all applications access the same version of the data by interfacing with the DBMS. c. In a database approach, the data definitions or metadata are stored in the applications accessing the data. d. In a database approach, there is typically less storage needed compared to the file approach.

c. In a database approach, the data definitions or metadata are stored in the applications accessing the data.

Which statement is not correct? a. In a file-based approach to data management, different applications could be using older and newer versions of the same information. b. In a file-based approach to data management, the data definitions are included in each application separately. c. In a file-based approach to data management, a change in the structure of a data file is easily handled because each application has it's own data files. d. The file-based approach to data management causes the same information to be stored separately for different applications.

c. In a file-based approach to data management, a change in the structure of a data file is easily handled because each application has it's own data files.

Fill in the blanks A(n) _________________ data model is the mapping of a(n) ___________________ data model to a model that describes which data are stored where and in what format when developing a database. a. Logical/Internal b. Internal/Conceptual c. Internal/Logical d. Conceptual/Internal

c. Internal/Logical

Which statement is not correct? a. In a file-based approach, every application has its own query and access procedures, even if they want to access the same data. b. SQL is a database language that allows different applications to access different subsets of the data necessary for each application. c. SQL is a database language that focuses on how to access and retrieve the data. d. SQL is a database language to manage DBMSs without having to write a substantial amount of programming code.

c. SQL is a database language that focuses on how to access and retrieve the data.

Consider the following query: SELECT P.PRODNR, P.PRODNAME FROM PRODUCT P WHERE EXISTS (SELECT * FROM PO_LINE POL WHERE P.PRODNR = POL.PRODNR GROUP BY POL.PRODNR HAVING SUM(POL.QUANTITY) > P.AVAILABLE_QUANTITY) The query retrieves which of the following? a. The name and number of the product with the highest ordered quantity. b. The name and number of all products that are ordered and do not exceed their available quantity. c. The name and number of all products that are ordered and exceed their available quantity. d. The name and number of the product with the lowest ordered quantity.

c. The name and number of all products that are ordered and exceed their available quantity.

Consider the following query - SELECT P.PRODNR, P.PRODNAME, P.AVAILABLE_QUANTITY, SUM(L.QUANTITY) AS ORDERED_QUANTITY FROM PRODUCT AS P LEFT OUTER JOIN PO_LINE AS L ON (P.PRODNR=L.PRODNR) GROUP BY P.PRODNR Which of the following statements is NOT CORRECT? a. The query retrieves the product number, product name, and available quantity of each product thanks to the left outer join. b. The query retrieves for each product the total ordered quantity. c. The query result can never contain NULL values d. If we remove the GROUP BY statement and P.PRODNR, "P.PRODNAME", P.AVAILABLE_QUANTITY from the SELECT statement, the query will result in one row containing the total outstanding ordered quantity over all products in column "ORDERED_QUANTITY".

c. The query result can never contain NULL values

Consider the following query - SELECT P.PRODNR, P.PRODNAME FROM PRODUCT P EXCEPT SELECT POL.PRODNR FROM PO_LINE POL The query retrieves which of the following? a. The name and number of all the products with no outstanding order. b. The number and name of all the products that are ordered. c. The query will not execute because both queries do not select the same columns. d. The query will not execute because both queries do not select the same rows.

c. The query will not execute because both queries do not select the same columns.

Consider the following View definition and update statement - CREATE VIEW TOPPRODUCTS (PRODNR, PRODNAME, QUANTITY) AS SELECT PRODR, PRODNAME, AVAILABLE_QUANTITY FROM PRODUCT WHERE AVAILALBLE_QUANTITY>100 WITH CHECK OPTION UPDATE TOPPRODUCTS SET QUANTITY=80 WHERE PRODNR=0153 What will be the results of this statement? a. The update can be successfully made but only the PRODUCTT table will be updated. b. The update can be successfully made and both the View and PRODUCT table will be updated. c. The update will be halted because of the WITH CHECK OPTION d. The update can be successfully made but only the View will be updated.

c. The update will be halted because of the WITH CHECK OPTION

A bank needs to store the following information: customer names, customer addresses, city of a branch, number of accounts, account IDs, and account balances. How many record types do you need to construct a hierarchical database with this information? a. One b. Two c. Three d. Four

c. Three

A data steward notices that part of the database contains values in a different language. Which type of data quality error is this? a. intrinsic b. contextual c. representational d. accessibility

c. representational

Which of these is part of the query processor in the architecture of a DBMS? a. Transaction manager b. DDL complier c. Security manager d. DML complier

d. DML complier

The extent to which data are available, or easily and swiftly retrievable is which of the following categories? a. intrinsic b. contextual c. representation d. access

d. access