Midterm

Question 1

database design has 2 levels - logical and discrete

Answer 1

false

Question 2

OLAP focuses on analytical processing

Answer 2

True

Question 3

relational DBs leading advantage is speed of processing

Answer 3

false

Question 4

SQL is a DB that helps user create, modify, maintain and query data

Answer 4

false

Question 5

a field is represented as a row in a table

Answer 5

false

Question 6

the primary key value must be unique in each row of a table

Answer 6

true

Question 7

information is data that is processed in a way that is meaningful to a user

Answer 7

true

Question 8

NULL is a value in a DB that can also be described as 0

Answer 8

false

Question 9

lowest level structure in a relational table is the ________

Answer 9

field

Question 10

a view is a virtual table composed of fields from one or more tables

Answer 10

true

Question 11

a many to many relationship is not a valid table relationship

Answer 11

false

Question 12

SELECT cust_number, cust_name

FROM customer;

This is a valid SQL statement that will return output composed of one column showing all customer numbers in a DB

Answer 12

false

Question 13

SELECT cust_number, cust_name

FROM customer;

above SQL statement output will be in ascending order by cust-name

Answer 13

fasle

Question 14

looking for subjects is a good technique for identifying your table list

Answer 14

true

Question 15

calculated fields should be stored in a values table for easy access

Answer 15

false

Question 16

field level integrity ensures no duplicate records in a table

Answer 16

false

Question 17

relationship level integrity is also known as referential integrity

Answer 17

true

Question 18

mission statements need to make it clear how your database will work

Answer 18

false

Question 19

The collection of data used for modeling processes is the:

a. Database

b. RDBMS

c. OSI layers

d. OLTP

e. none of these

Answer 19

a

Question 20

2 main DB types are:

a. operational & analytical

b. logical & discrete

c. analytical & logical

d. all valid types

Answer 20

a

Question 21

The advantages of a relational database include:

a. data integrity at multiple levels

b. data accuracy

c. ease of access

d. all of these

e. “b” and “c”

Answer 21

d

Question 22

The most common SQL statement to pull information from a DB uses the syntax:

a. PULL

b. PICK

c. SELECT

d. GET

e. EXTRACT

Answer 22

c

Question 23

Set of rules that decomposes data tables to minimize redundancy, minimize dependency, and enforce relationship integrity is known as:

a. Abstraction

b. Normalization

c. Data modeling

d. 3 Tiered Architecture

Answer 23

b

Question 24

In order to store acceptable values that a program can use for reference checks, one creates a:

a. Data table

b. Normalization table

c. Reference check table

d. Validation table

Answer 24

d

Question 25

In order to establish a relationship between 2 tables, one would take the primary key of one subject table and set up a ________ in the other table. a. cross reference key b. secondary key c.foreign key d. index

Answer 25

c

Question 26

1:1, M:M, and 1:M are known as: a. table types b. cardinality c. normalization d. participation

Answer 26

b

Question 27

The standard wild card symbol used in SQL is: a. * b. /* c. W d. # e. none of these

Answer 27

a

Question 28

The first step in the design process of a good database should be: a. Analyze current DB b. Define table c. Define business rules d. Define the mission statement

Answer 28

d

Question 29

The characteristics list will eventually be used to: a. define views b. define rows c. define fields d. define tables e. all of these

Answer 29

c

Question 30

Which of the following is contributing to data explosion and need for advanced database capabilities? a. smart phones b. internet c. broad band d. computer availability e. all of these

Answer 30

e

Question 31

Which of the following best describes an example of an RDBMS? a. Oracle b. SQL c. DB2 d. "a" and "b" e. "a" and "c" f. "b" and "c"

Answer 31

e

Question 32

When analyzing the current DB, what should you do?. a. retrieve current DB structure b. pull available reports c. look at programs d. "a" and "c" e. all of these

Answer 32

e

Question 33

1 subject = 1 _______ 1 characteristic = 1 _____________

Answer 33

table, field

Question 34

table that contains fields (with sensitive information) that are related to a particular parent table(s) clue is that a table exists where certain fields often don't have values

Answer 34

subset table

Question 35

a table with typically static date; important for data integrity

Answer 35

validation/look-up table

Question 36

Table descriptions need:

Answer 36

what: kind of info is contained, people, places, etc. why: the data contained is important AVOID saying how the data is used

Question 37

When is it okay to have duplicate fields?

Answer 37

1. needed to relate tables together (this is the reason most of the time) 2. show multiple occurrences of a value (ex: supervisor_no is an alias of the supervisor's employee_no b/c supervisor is an employee too) 3. perceived need for supplemental value

Question 38

Type of key: a field or set of fields that uniquely identifies a single instance of the table's subject. Each table must have at least one. Elements: - It cannot be a multipart field. - It must contain unique values. - It cannot contain null values - Its value cannot cause a breach of the organization's security or privacy rules. - Its value is not optional in whole or in part. - Its values must uniquely and exclusively identify each record in the table. - Its value can be modified only in rare or extreme cases.

Answer 38

Candidate key

Question 39

Type of key: -Picked from the list of candidate keys -exclusively identifies the table -uniquely identifies each record in a table -table name should be included in its name (i.e. employee number) - there must only be ONE per table - 2 tables can't share one unless it's a subset table

Answer 39

primary key

Question 40

What is the proper SQL order with everything we've learned so far?

Answer 40

SELECT (column name) FROM (table name) WHERE (column name = value/'character string') ORDER BY (column name ASC/DESC);

Question 41

What makes a field name a character and not a decimal?

Answer 41

When you will never do mathematical equations with it (such as address number)

Question 42

What can be used after a WHERE clause? (5 answers)

Answer 42

AND, OR, BETWEEN AND, IN (), LIKE

Question 43

Field Specification -- General Elements, Physical Elements, or Logical Elements: Field Name, Parent Table, Label, Specification Type, Source Specification, Shared By, Alias(es), Description

Answer 43

General Elements (pertains to the basic attributes of the field)

Question 44

Field Specification -- General Elements, Physical Elements, or Logical Elements: Data Type, Length, Decimal Places, Character Support, Input Mask, Display Format

Answer 44

Physical Elements (pertains to the structure of the field)

Question 45

Field Specification -- General Elements, Physical Elements, or Logical Elements: Key Type, Key Structure (e.g PK), Uniqueness, Accepts Nulls or Doesn't, Values Entered By (User or System), Required Value, Default Value, Range of Values, Edit Rule, Comparisons Allowed (<, >=, etc.), Operations Allowed (+ x - /)

Answer 45

Logical Elements (pertains to values within the field)

Question 46

What SQL input would you type if you want the output to show all product names (prod_name) that start with "folder" in their name

Answer 46

WHERE prod_name LIKE 'folder%'

Question 47

What SQL input would you type if you want the output to show all product names (prod_name) that have "folder" anywere in their name

Answer 47

WHERE prod_name LIKE '%folder%'

Question 48

True or false: WHERE prod_name LIKE '%' The output of this line would include 0, blanks/spaces, but not NULL values

Answer 48

true

Question 49

What SQL input would you type to get a list of all the product names (prod_name) for each number of pencils (e.g. No2 Pencil, No3 Pencil, No4 Pencil...)

Answer 49

WHERE prod_name LIKE 'No_ Pencil'

Question 50

SQL input to get all products (prod_name) that start with P or F (pencils, paper, folder, etc.)

Answer 50

WHERE prod_name LIKE '[P F]%'

Question 51

SQL input to get all products (prod_name) that start with ANYTHING BUT P or F

Answer 51

WHERE prod_name LIKE '[^ P F]%'

Question 52

What WHERE clause searches text fields that contain a certain part of a value

Answer 52

LIKE

Question 53

When do you put ' ' around a typed value

Answer 53

when it's a character (you will never do mathematical calculations with it)

Question 54

SQL: Make a calculated temporary field called "employee_name". Join the values employee_lastname and employee_first name. (Should look like: "Doe, Jane")

Answer 54

SELECT employee_lastname || ',' || employee_firstname AS employee_name

Question 55

What is AS used for and which part does it go in?

Answer 55

to make an alias in SELECT

Question 56

Types of NoSQL DB (4)

Answer 56

NoSQL is "not only SQL". There are no tables, primary keys, or foreign keys. Instead, they can use: wide-column, document, key-value, graph

Question 57

Pros of NOSQL

Answer 57

Flexible (non-structured) Elastic

Question 58

Cons of NoSQL

Answer 58

More redundancy Lack of consistency Lack of standards (b/c non-structured)

Question 59

Types of Cloud DB

Answer 59

Software as a System (SaaS, ex Microsoft Offcie), Infrastructure as a System (IaaS, ex Amazon), Platform as a System (PaaS, ex Google App Engine), Database as a System (DBaaS)

Question 60

Pros of Cloud

Answer 60

Elasticity (pay for what you use) Scalable/flexible Reliable

Question 61

cons of cloud

Answer 61

can't be accessed without the internet, security risk

Question 62

Big Data DB

Answer 62

HUGE data sets, can be unstructured, structured, or semi-structured, used to analyze big data and find trends

Question 63

Pros of Big Data

Answer 63

real-time data analytics (better decision-making) efficiency personalization

Question 64

Cons of Big Data

Answer 64

Costly Data cleansing needed