ISYS 2263 test 2

The database structure in a DBMS is stored as a ____. Answers: a. collection of queries b. collection of files c. set of key/value pairs d. file

collection of files

A ___________________ is a characteristic of a field that determines what type of data it can hold. Answers: designated data type data type category value type column characteristic

data type

The phrase ____ refers to an organization of components that define and regulate the collection, storage, management and use of data within a database environment. Answers: a. database management b. database management system c. management system d. database system

database system

Accurate, relevant, and timely information is the key to... Answers: a. knowledge b. understanding c. data management d. good decision making

good decision making

A workgroup database is a(n)____ database. Answers: a. single-user b. multiuser c. enterprise d. distributed

multi user

After graduation ceremonies at a university, six graduates were asked whether they were in favor of (identified by 1) or against (identified by 0) abortion. Some information about these graduates is shown below. Sex Age Abortion Issue Class Rank F 24 1 1 M 22 1 2 F 41 0 3 M 38 0 20 F 25 1 4 M 19 0 8 a. Are arithmetic operations appropriate for the variable "abortion issue"?

no

We executed the following code fragment in MS-Access during class activities. This code fragment is an example of a ___________________________ SELECT Instructor.InstructorID, Instructor.InstructorLName, Instructor.InstructorFName FROM Instructor WHERE (((Instructor.InstructorLName)=[Enter Last Name]));

parameter query

End-user data is ____. Answers: a. data about data b. raw facts of interest to the end-user c. accurate, relevant and timely information d. raw facts about the end-user

raw facts of interest to the end-user

____ data are the result of formatting to facilitate storage, use and generation of information. Answers: a. Semistructured b. Structured c. Historical d. Unstructured

Structured

When data storage characteristics are changed without affecting the program's ability to access data, it is called data independence. (T/F)

True

A primary key is non-unique identifier of a record in the table. Answers: True False

FALSE

As seen in the class activity with examples such as Bingham and Bing, when executing a SQL query in MS-Access, to retrieve all the records where LastName starts with "Bin", you use the following character with LIKE keyword for searching the matches. Answers: # "" (Empty String) % *

*

A recent issue of Fortune Magazine reported that the following companies had the lowest sales per employee among the Fortune 500 companies. Sales per Employee (In $1,000s) Sales Rank Company Seagate Technology 42.20 285 SSMC 42.19 414 Russel 41.99 480 Maxxam 40.88 485 Dibrell Brothers 22.56 470 How many variables are in the above data set?

2, company doesn't count i guess...

Which of the following is TRUE about the SQL code snippet? CREATE TABLE OrderLine_T (OrderID NUMBER(11,0) NOT NULL, ProductID INTEGER, OrderedQuantity NUMBER(11,0) CONSTRAINT OrderLine_PK PRIMARY KEY (OrderID), CONSTRAINT OrderLine_FK1 FOREIGN KEY (OrderID) REFERENCES Order_T(OrderID), CONSTRAINT OrderLine_FK1 FOREIGN KEY (ProductID) REFERENCES Product_T(ProductID)); Answers: It contains one primary key and one foreign key A constraint is required on OrderedQuantity column It creates 4 columns in the database table - Order ID, ProductID, OrderedQuantity, OrderLine A constraint is required on OrderLine column It contains one foreign key to link to other tables A constraint is required on ProductID. The OrderLine table is linked to two other tables

A constraint is required on ProductID. The OrderLine table is linked to two other tables

Which of the following is not associated with collecting big data? Answers: Transactions Social media networks Automation of operations and devices Business Analytics tools

Business Analytics tools

The number of occurrences of an expression i.e. number of rows with non-null values is determined using the ____ aggregate function. Answers: MAX COUNT SUM AVERAGE

COUNT

The_________________________ SQL command defines the portion of database owned by the particular user.

CREATE SCHEMA

Whenever you execute a query to retrieve data from a database table by using SELECT statements, the following feature helps you in manipulating the column titles to something that is more legible than pre-defined information. Note: As you have seen, the query places the outputs in a structure which looks similar to a table. Answers: Column Aliasing Semantic Column Redefining Semantic Column Renaming Interpretative Column Renaming

Column Aliasing

__________________exists when different versions of the same data appear in different places. Answers: 1. Data integrity 2. Data quality 3. Data security 4. Data inconsistency

Data Inconsistency

In a table,__________________________is a range of possible values a field can take. Note that field means column in Access terminology. One advantage of using this feature in database design is that it allows to show values in drop downs in the user interface design, for example, course grades. Answers: Domain Domain Selections Data Range Domain Range Options

Domain

DELETE TABLE FROM CUSTOMERORDERS_T is one SQL statement to use when you want to completely remove a table from the table schema that you created earlier. As discussed in the class, it is important that you need to exercise caution and discretion when executing this type of SQL statements because it will remove the tables from the database and creates issues during data fetching. (T/F)

FALSE

From the examples you executed in the class, a query can display data from one and only one table in an ordered manner based on the question pre-defined by the user. To retrieve data from multiple tables using the keys and relationships, you need to create a parameter query. This parameter query helps to connect across multiple tables, pre-define the question and take advantage of created table connections to retrieve answers to the question. (T/F)

FALSE

SQL is a non-procedural language and it gives you flexibility to focus on what the program should do rather than what sequence of tasks. Because of this non-procedural advantage of SQL, when you drop the tables which are not helpful for your analysis, the order of dropping need not be considered. This is because SQL allows you to focus on what the program should do. SQL will handle the ordering and sequence of tasks. Your job as data analyst is to identify the tables to drop based on their lack of value in your business tasks. (T/F)

FALSE

When you are creating tables in the database during schema creation stage of your database management project, you should not create queries for table creation. One of the uses of queries you noted in this course is that they are pre-defined by users and display data by creating a view on the main data table. Queries are good for repetitive usage to display data. Hence, it is not a good practice to use them for table creation which is data definition language (DML) based activity you execute in the beginning. It is better to always stay away from table level operations using queries. Note: We discussed about source tables in Excel, Tableau, SQL and Access. Answers: True False

FALSE

You know that a primary key is not mandatory but recommended per the best practices in database design. Let us say you created two tables - Table 1 and Table 2. The foreign key in Table 2 always references Table 1. It is acceptable to define the foreign key column in Table 2 without NOT NULL constraint. It is enough if the primary key column in Table 2 is defined with NOT NULL constraint. However, because the tables are connected, it is mandatory that any primary keys and foreign keys in Table 1 should be NOT NULL because Table 2 depends on Table 1. (T/F)

FALSE

In MS-Access terminology,_________________________ is a table component that contains a category of information that pertains to all records. Answers: Category View Field Query

Field

A ____ is a collection of related records. Answers: a. file b. field c. column d. database

File

_____________________ is the result of processing raw data to reveal its meaning.

Information

Per what you learned in the class, which of the following is TRUE about INSERT statement? Answers: INSERT is a data definition language statement because it creates new tables in the database Input of records that has some null attributes does require identifying the fields that actually get the data INSERT helps to circumvent constraints and insert duplicate values into the primary key field You can insert rows and columns from one table into another table, for example, when taking a subset of data to populate another table It updates current values in one or more rows in a table Response Feedback: One thing to keep in mind is that you should not insert duplicate values into a primary key field once the constraints are placed.

Input of records that has some null attributes does require identifying the fields that actually get the data You can insert rows and columns from one table into another table, for example, when taking a subset of data to populate another table

Which of the following is true about the given SQL code? SELECT * FROM customers WHERE EXISTS (SELECT * FROM orders WHERE customers.customer_id = orders.customer_id); Answers: It selects all Customer and Order information based on primary key and foreign keys relationships that help connect the record level data. CustomerID is the primary key in the Orders table being used to join two tables i.e. Customers and Orders The WHERE command selects the columns based on conditional expression It selects a few columns from the Customer table and a few columns from the Orders table and filters them based on Primary Key, Foreign Key relationship CustomerID is the foreign key in the Orders table being used to join two tables i.e. Customers and Orders

It selects all Customer and Order information based on primary key and foreign keys relationships that help connect the record level data. CustomerID is the foreign key in the Orders table being used to join two tables i.e. Customers and Orders

Which two of the following are FALSE about the following SQL code? SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC; Answers: It sorts columns by Country and then by CustomerName It displays only the data from the Country and CustomerName columns It sorts rows by Country and then by CustomerName It operates on the Customers and Country Tables

It sorts columns by Country and then by CustomerName It displays only the data from the Country and CustomerName columns

When fetching data from two tables, the _______________________ command is used in FROM clause before doing an equality check on common columns using the identifier keys. Answers: MERGE COMBINE CONCATENATE JOIN

JOIN

When you are executing SQL statements specifically in MS-Access database as seen in the course, which of the following pattern match selection criteria would return zip codes with the characters 704 in the first three spaces. Answers: LIKE "704%" LIKE "*704" LIKE "704*" LIKE "%704"

LIKE "704*"

In the University database at the UofA, your student IDs stored as, say, 1001, 2002, 3003, 4004 are examples of:

Nominal

The following information regarding a sample of seven students is provided. Student Identification number Grade Point Average Classification Gender Rank in Class Adam 1234 2.89 Senior Male 15 Brandon 8978 2.01 Junior Male 25 Jason 6578 3.97 Freshman Male 3 Marissa 2345 3.98 Sophomore Female 2 Michelle 8901 2.67 Senior Female 18 Wendy 7789 4.00 Senior Female 1 Webster 6780 3.77 Freshman Male 4 Which measurement of scale is used for the identification number variable?

Nominal

After graduation ceremonies at a university, six graduates were asked whether they were in favor of (identified by 1) or against (identified by 0) abortion. Some information about these graduates is shown below. Gender Age Abortion Issue Class Rank F 24 1 1 M 22 1 2 F 41 0 3 M 38 0 20 F 25 1 4 M 19 0 8 a. Which measurement of scale is used for the Gender variable?

Nominal, Gender is always nominal. Remember it is like a Name.

After graduation ceremonies at a university, six graduates were asked whether they were in favor of (identified by 1) or against (identified by 0) abortion. Some information about these graduates is shown below.... Which measurement of scale is used for the class rank variable?

Ordinal

The following information regarding a sample of seven students is provided. Student Identification number Grade Point Average Classification Gender Rank in Class Adam 1234 2.89 Senior Male 15 Brandon 8978 2.01 Junior Male 25 Jason 6578 3.97 Freshman Male 3 Marissa 2345 3.98 Sophomore Female 2 Michelle 8901 2.67 Senior Female 18 Wendy 7789 4.00 Senior Female 1 Webster 6780 3.77 Freshman Male 4 Which measurement of scale is used for the 'Classification' variable?

Ordinal

In the practice examples, you were searching for records matching a criteria you specified. You searched for Bingham or Bing by hard coding the search criteria into the SQL statement. You have also seen another example where you did it differently to allow the system to show a dialog box for you to enter an input and search by the input which was more flexible. It is like keyword search in Google to act on that column of information. When doing this activity, you were using the feature of MS-Access which is _______________________________ Answers: Search with Column dialog match query Search with Column input match query Search with Keyword match search query Parameter query

Parameter query

To distinguish one row i.e. one record from another, tables contain: Answers: Relational Row Independence Specifications Row Integrity Specifications Primary Key Specifications Existence Independence Specifications

Primary Key Specifications

Any foreign key field must agree with the primary key that is referenced by the foreign key. In other words, for every foreign key, there exists a primary key in another table used as a foreign key in the current table. This is a definition of: Answers: Relational Row Referencing Key Dependence Concurrence Referential Integrity Logical Model Referring Integrity

Referential Integrity

A_____________________database is a type of database that stores information in multiple tables but appears to the user as if it is getting data only from one table.

Relative

A_______________helps to view, format and summarize information in your database. Answers: Field Attribute Query Report

Report

The WHERE Command restricts the selection of_______________based on a conditional expression Answers: Rows Primary Keys Columns Values

Rows

A recent issue of Fortune Magazine reported that the following companies had the lowest sales per employee among the Fortune 500 companies. Sales per Employee (In $1,000s) Sales Rank Company Seagate Technology 42.20 285 SSMC 42.19 414 Russel 41.99 480 Maxxam 40.88 485 Dibrell Brothers 22.56 470 Which variable is the categorical variable in the above data set?

Sales Rank

A recent issue of Fortune Magazine reported that the following companies had the lowest sales per employee among the Fortune 500 companies. Sales per Employee (In $1,000s) Sales Rank Company Seagate Technology 42.20 285 SSMC 42.19 414 Russel 41.99 480 Maxxam 40.88 485 Dibrell Brothers 22.56 470 Which variable is the quantitative variable in the above data set?

Sales per Employee

In the database tables, latest records of the incoming data will be found at the bottom of the tables. Answers: True/False

TRUE

A ___________________is a storage container for data. Answers: View Report Table Query

Table

Which of the following are (more than one) true about the following code? CREATE VIEW [Current Product List] As SELECT ProductID, ProductName FROM Products WHERE Discontinued = No; Answers: The query searches three columns in the database It creates a dynamic subset of rows and columns based on matching the criteria The CREATE statement creates new table 'Current Product List' in the database The WHERE command helps to select and display all discontinued products It fetches results from ProductID and ProductName tables in the database

The query searches three columns in the database It creates a dynamic subset of rows and columns based on matching the criteria

Which options are FALSE about the SQL Statement below (one or more options)? CREATE TABLE inventory ( INV_ID INTEGER NOTNULL, INV_OPER_ID VARCHAR(30) NOTNULL ); Answers: This is a valid DML Statement INV_OPER_ID cannot accommodate IDs with 12 characters Data types will have no major validity issues during compilation This creates three new columns - inventory, INV_ID and INV_OPER_ID in the database This is not a valid SQL code example per the DML notations

This is a valid DML Statement INV_OPER_ID cannot accommodate IDs with 12 characters This creates three new columns - inventory, INV_ID and INV_OPER_ID in the database This is not a valid SQL code example per the DML notations

Which one or more of the following is TRUE about the following code fragment? SELECT SUM(salary) AS "Total Salary" FROM employees WHERE salary > 25000; Answers: It operates on "manager" column before aggregating records The query returns the output as salary. This operates on the employees table This operates on the salary table

This operates on the employees table

When creating a database, which of the following are valid questions you need to ask at the definition stage? Answers: Which forms will I create? What is the schema? What are the tables and fields in tables? What queries to build for visualizing database contents? Which reports will I create? What queries will I build? Are there any other constraints? What are the data types?

What is the schema? What are the tables and fields in tables? Are there any other constraints? What are the data types?