COMP 251 Final

Question 1

Relationship level integrity is part of overall data integrity

Answer 1

True

Question 2

2 tables can participate in multiple types of relationships with each other.

Answer 2

False

Question 3

A one-to-many relationship can only exist between 2 tables.

Answer 3

False

Question 4

You will need a Primary Key and a Foreign Key when you establish a 1-M relationship.

Answer 4

True

Question 5

An advantage of relating tables is that you can draw data from multiple tables simultaneously.

Answer 5

True

Question 6

1:M + 1:M = 2:M

Answer 6

False

Question 7

It is appropriate for a Foreign Key to have the same name as a Primary Key.

Answer 7

True

Question 8

Cascading deletes will delete records in a parent table and all associated records in a child table.

Answer 8

True

Question 9

(Open Quiz 2 Diagram image)
The diagram represents a self-referencing relationship.

Answer 9

True

Question 10

(Open Quiz 2 Diagram image)
It is mandatory for employees and mentors to exist in the table.

Answer 10

False

Question 11

(Open Quiz 2 Diagram image)
A deletion rule is present that restricts deletions in certain situations.

Answer 11

False

Question 12

(Open Quiz 2 Diagram image)
A mentor can have only one employee mentee.

Answer 12

False

Question 13

(Open Quiz 2 Diagram image)
Degree of participation is correctly diagramed.

Answer 13

False

Question 14

A SQL join is not a physical nor a logical data base structure.

Answer 14

True

Question 15

Application oriented rules are not valid business rules.

Answer 15

False

Question 16

Which item describes a relationship type?
A. one-to-many
B. mandatory
C. maximum
D. cascade
E. referential

Answer 16

A

Question 17

A relationship exists between two tables that are ___________________ to each other.
A. physically related
B. logically related
C. RDBMS related
D. joined via SQL
E. none of these

Answer 17

B

Question 18

A table with curved corners represents a __________.
A. data table
B. linking table
C. validation table
D. subset table
E. none of these

Answer 18

D

Question 19

A table with distinct columns on both the right and left ends represents a _________.
A. data table
B. linking table
C. validation table
D. subset table
E. none of these

Answer 19

B

Question 20

You completed the process for identifying a relationship between a Client Table and an Account Executives Table. You concluded that a single record in the Account Executives table can have one or more records in the Client Table and that a single record in the Client Table can have one or more records in the Account Executives table. You concluded that you need to:
A. define a one-to-many relationship
B. define a self-referencing relationship
C. define a subset relationship
D. define an alias field for the relationship
E. none of these

Answer 20

E

Question 21

You have a Foreign Key in Table B that is associated with a Primary Key in Table A. Further, you have a 1-M relationship between Table A and Table B. In the logical elements field specifications for the Foreign Key, you will set the Uniqueness Element as:
A. non-unique
B. unique
C. FK
D. Replica
E. No Nulls

Answer 21

A

Question 22

You have a parent Table A that has a 1-1 relationship with Table B. You want to ensure that a record in Table A is not deleted and remains active when you have a record in Table B. You establish a deletion rule of:
A. Set Default
B. Nullify
C. Cascade
D. Restrict
E. Deny

Answer 22

D

Question 23

Refer to this SQL for questions 8 and 9.

SELECT employee_number, employee_last_name, employee_first_name, annual_salary
FROM Employee, Salary
WHERE Employee.employee_number = Salary.employee_number;

In this SQL, you will:
A. Get a valid result as long as the right table relationships have been established.
B. Get an error since going across tables in this manner is a relationship violation.
C. Get a valid results set, but the output will be restricted.
D. Get an error since you can’t compare fields across tables on a WHERE clause.
E. “a” and “c”

Answer 23

E

Question 24

Refer to this SQL for questions 8 and 9.

SELECT employee_number, employee_last_name, employee_first_name, annual_salary
FROM Employee, Salary
WHERE Employee.employee_number = Salary.employee_number;

In this SQL, you have:
A. fully qualified column names
B. alias table names
C. outer join syntax
D. “a” and “c”
E. all of these

Answer 24

A

Question 25

In defining Business Rules for your data base, you can set up rules through:
A. Field Specifications
B. Relationship Characteristics
C. Validation Tables
D. “a” and “b”
E. “a”, “b”, and “c”

Answer 25

E

Question 26

“Required values = Yes” is an example of business rules set up through:
A. Field Specifications
B. Relationship Characteristics
C. Validation Tables
D. “a” and “b”
E. None of these

Answer 26

A

Question 27

In your data base design for an International Import and Export Company, you have been asked to restrict the countries that we will trade with on any given month; you further learn that the list of restricted countries can change frequently. Your best approach would be to:
A. Place business rules in the application programs
B. Establish Range of Value in the Field Specification Tables
C. Establish a Validation Table
D. Establish a Linking Table
E. Establish a Subset Table

Answer 27

C

Question 28

Which of the following isn’t a valid join type:
A. Inner Join
B. Left Outer Join
C. Self Join
D. Cross table Join
E. Natural Join

Answer 28

D

Question 29

A linking table will include:
A. Composite Primary keys that are also primary key in the associated tables
B. More than one foreign key
C. Other non-key fields when appropriate
D. “a” and “b”
E. “a”, “b”, and “c”

Answer 29

E

Question 30

Which of the following is not a recommended diagramming technique for an Entity Relationship Diagram:
A. Note the Index field
B. Relationship line should connect PK and FK
C. Show type of relationship
D. Show degree of participation
E. Show business rules where possible
F. All are recommended

Answer 30

A

Question 31

Types of Cardinality:

Answer 31

1-1, 1-M, M-M

Question 32

Type of Cardinality: usually a subset table. A single record in Table A related to only one record in Table B (and vice versa)

Answer 32

1-1

Question 33

Type of Cardinality: the most common table relationship
A single record in Table A is related to one or more records in Table B, but a single record in Table B is related to only one record in Table A.

Answer 33

1-M

Question 34

Type of Cardinality: Single record in Table A can be related to one or more records in Table, and a single record in Table B can be related to one or more records in Table A. Will need a linking table.

Answer 34

M-M

Question 35

Relationships that exist between recs in the same table (can be any type of cardinality)
Ex: Each employee is tied to a mentor (mentorID would be an alias of employeeID and these two columns would be in the same table)

Answer 35

Self-Referencing Relationships (aka recursive relationships)

Question 36

Type of Table:
For M-M relationships
2 keys serve as this table’s composite primary key (CPK) as well as FK’s

Answer 36

Linking Table

Question 37

Relational database’s leading advantage is speed of processing

Answer 37

False

Question 38

In a relational DB, it is important to know where the RDBMS stores the data to expedite data retrieval

Answer 38

False
(don’t have to know where it’s stored)

Question 39

The RDBMS will detect a logic error if you store NULL in a value

Answer 39

False
(null CAN be an acceptable value)

Question 40

An Index is a special type of logical DB structure used to build table relationships

Answer 40

False
(Index is a PHYSICAL structure, indexes are a special object that allows the user to quickly retrieve records from the database)

Question 41

Calculated fields should not be stored in a table

Answer 41

True

Question 42

Table-level integrity ensures no duplicate records in a table and that the primary key is unique

Answer 42

True

Question 43

Relationship level integrity is also known as Referential Integrity

Answer 43

True

Question 44

Duplicate fields in your DB are never okay

Answer 44

False

Question 45

IS NULL and =’ ‘ are filtering operations that achieve the same results

Answer 45

False

Question 46

!= and <> are operators that achieve the same results

Answer 46

True

Question 47

System Software Used to create, maintain, modify and manipulate a relational database with tools for the applications to interact with stored data
a. Database
b. SQL
c. RDBMS
d. XML
e. both a and b

Answer 47

C

Question 48

The advantages of a relational database do not include:
a. data integrity at multiple levels
b. data accuracy
c. ease of access
d. built-in referential integrity

Answer 48

D

Question 49

2. 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. Decomposition

Answer 49

B

Question 50

Known as a Tuple in relational database theory, the ______ represents an instance in the table
a. Field
b. Column
c. Primary Key
d. Table
e. None of these

Answer 50

E

Question 51

Elements of an Ideal Field include
a. Has only 1 value
b. Can easily be decomposed into small parts
c. Is a distinct characteristic of the subject
d. a and c
e. all of these

Answer 51

D

Question 52

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
e. a or d

Answer 52

D

Question 53

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

Answer 53

E

Question 54

which SOL keyword can you use to select unique values from the same column
a. DISTINCT
b. SINGLE
c. ONCE
d. NONDUP
e. none of these

Answer 54

E

Question 55

One standard Concatenation symbol used in SQL is
:
a. *
b. /*
c. C
d. ||
e. none of these

Answer 55

D

Question 56

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. Gather artifacts
e. Define mission statement

Answer 56

e

Question 57

The characteristics list is primarily used to:
a. define primary keys
b. define rows
c. define fields
d. define tables
e. all of these

Answer 57

c

Question 58

Which discipline below addresses unstructured data as a key part of its value offering:
a. NoSQL
b. Big Data
c. Cloud
d. In Memory Computing
e. RDBMS

Answer 58

B

Question 59

The debate on __________has reintroduced some of the old discussions of non-structured DB focused on lower-level data management vs the benefits of structured relational DB.
a. NoSQL
b. Big Data
c. Cloud
d. In Memory Computing
e. RDBMS

Answer 59

A

Question 60

Which of the following best describes an example of a Relational Data Base Management System?
a. Oracle
b. SQL
c. DB2
d. “a” and “b”
e. “b” and “c”
f. “a” and “c”

Answer 60

F

Question 61

Which of the items below is not a valid table type:

a. Linking Table
b. Data Table
c. Subset Table
d. Validation Table
e. all are valid

Answer 61

e

Question 62

An ideal table should have all of the following with the exception of:
a. Primary key
b. Foreign key
c. No calculated field
d. a and c
e. all are needed

Answer 62

B

Question 63

Which CK below is the best for a Primary Key:

a. Employee_Social_Security#
b. employee_last_name
c. employee_number
d. all are good
e. none

Answer 63

C

Question 64

A query is written to return sales representatives’ annual sales figures had the following filtering clause:


WHERE (sales_region = ‘Central’)| OR (sales_region = ‘South’ AND annual_sales > 0)


The results would be:

a. All sales reps with annual sales greater than 0 that are in either the central or south region
b. Only sales reps in the south region with sales greater than O
c. Sales reps that sold in both regions and have sales greater than 0
d. All sales reps in both regions regardless of sales
e. Sales reps in the south region with sales greater than 0 & all sales reps in the central region regardless of sales

Answer 64

E

Question 65

When defining field specifications, the data types, lengths, decimal places, masks, and format are all considered
a. logical elements
b. physical elements
c. general elements
d. data elements
e. attribute elements

Answer 65

B

Question 66

in a WHERE clause using a wildcard compare statement of LIKE ‘%999’, the compared field results will be:
a. any values that have three 9s anywhere
b. will not work since an invalid wildcard symbol used
c. any values ending with three 9s
d. any values starting with three 9s
e. any values as long as it ends in 3 numeric values

Answer 66

C

Question 67

Alternative to Relational DB where speed is the leading priority, but reliability can be a concern

Answer 67

NoSQL

Question 68

Includes focus on complex data sets that may include structured and unstructured data

Answer 68

Big Data

Question 69

Examples include Saas, Paas, laas

Answer 69

Cloud

Question 70

Requires the evaluation of data redundancy and geo-graphic redundancy

Answer 70

High Availability

Question 71

Avoids over-head tied to disk utilization for read/write

Answer 71

In-Memory DB

Question 72

Write a simple SQL statement to return all employees last name, employee first name, and hire date from the EMPLOYEE table. The employee table has many more fields, but you don’t want the others; you can make up valid descriptive column names for required fields to satisfy this request.

Answer 72

SELECT employee_lastname, employee_firstname, employee_hiredate
FROM EMPLOYEE;

Question 73

Using the tables in 03 above, write a SQL that produces a list of employees making less Than $19.50 per hour that work at store number 222. This list will be provided to the branch manager and she only needs the first and last names of the employees and their hourly rate. The manager wants it in alpha order by last name. Your field specifications state that the store number is defined as a character and the hourly rate as a decimal.

Fields: employee_firstname, employee_lastname, employee_hourlywage, store_number

Answer 73

SELECT employee_firstname, employee_lastname, employee_hourlywage
FROM EMPLOYEE
WHERE store_number = ‘222’ AND employee_hourlywage < 19.50
ORDER BY employee_Iastname ASC;

Question 74

Using the same tables above, the construction manager needs to visit each store that is over 1000 square ft (stored as numeric) to review existing city violations. However, the manager only wants to visit those stores that actually have a violation open. The store violations field stores a numeric value equal to the number of violations that are open. Although the manager doesn’t want to see the number of violations, he wants a special indicator for you to add next to the store square footage number of a “V” to remind him that there are violations open. You have been asked to create a report that looks as follows:

Fields: store_number, store_address, store_city, store_state, store_squareft (ex: 1500 V)

Answer 74

SELECT store_number, store_address, store_city, store_state, store _squareft || ‘ V’ AS store_squareft
FROM STORE
WHERE storee_squareft > 1000 AND store_violations > 0;

Question 75

Write an SQL that will return store number (no other info needed) that are in zip codes starting with 606 or 601, but only if they have zero (decimal) violations.

Answer 75

SELECT store_number
FROM STORE
WHERE store_violations = 0 AND (store_zip LIKE ‘606%’ OR store_zip LIKE ‘601%’);

Question 76

Deletion Rule:
The RDBMS will not delete the record in the parent table, but will instead keep the record and designate it as “inactive.”

Answer 76

Deny (D)

Question 77

RDBMS does not delete the parent table record if it had related records in the child record

Answer 77

Restrict (R)
(most popular deletion rule)

Question 78

RDBMS will delete the parent table record and all related records in the child table.

Answer 78

Cascade (C)

Question 79

Deletion Rule:
The RDBMS will delete the record in the parent table and will then update the foreign key values of related records in the child table to null.
(If you are going to use this deletion rule, you must modify the foreign key’s field specifications and set the Null Support logical element to “Nulls Allowed.”)

Answer 79

Nullify (N)

Question 80

RDBMS will delete the parent table record and updates the FK value of the related child table record with “default value” from the logical specification setting

Answer 80

Set Default (SD)

Question 81

Types of Participation Symbols

Answer 81

l and O

Question 82

Type of Participation: need at least 1 rec in “this” table before you can enter recs in related table

Answer 82

Mandatory

Question 83

Type of Participation: there is no requirement for any rec to exist in “this” table before you enter records in related table

Answer 83

Optional

Question 84

Determines the minimum number of records a given table must have and the maximum number of records allowed to have in the relationship

Answer 84

Degree of Participation

Question 85

Degree of Participation Example: if an agent can exist with 0 clients, and the maximum number of clients it can have is 10, what is the degree of participation and what table do you put it next to?

Answer 85

(0, 10) Clients

Question 86

Do a inner table join for the vendors and products table. The PK of vendors table is vendor_ID. This is a FK in products table. The fields you’ll need are vendor_name, product_name, and product_price.

Answer 86

SELECT vendor_name, product_name, product_price
FROM vendors, products
WHERE vendors.vendor_ID = products.vendor_ID

Question 87

Business Rule: within logical DB design, done via field specifications and/or relationship characteristics

Answer 87

Database oriented

Question 88

Business Rule: can’t be done in logical DB design, and therefore
is established during physical design or within the application design

Answer 88

Application oriented

Question 89

DB Oriented Business Rule: imposes constraints on the elements of a field.
Ex: DOB must be shown as MM/DD/YYYY

Answer 89

Field Specifications

Question 90

DB Oriented Business Rule: imposes constraints on the characteristics of a relationship between 2 tables.
Ex: A manager must have at least 5 employees, but not more than 30 –> user degree of participation (5, 30)

Answer 90

Relationship Specific

Question 91

Self Join Example: You want to send mail to all customer contacts that work at the same company where Jim Jones works. So, we have to first find what company (cust_name) Jim Jones works for and then pull all contacts at that same company.

Answer 91

SELECT cust_id, cust_name, cust_contact
FROM Customer
WHERE cust_name = (SELECT cust_name
FROM Customers
WHERE cust_contact = ‘Jim Jones’);

Question 92

A type of join in SQL. Allows you to include rows that have no related rows.

Answer 92

Outer Join

Question 93

Virtual tables composed of fields from one or more base tables and possibly fields from other views. So, although not a real table, it uses the concept of rows and columns, but does not have its own PK.
Example: From the Students and Classes tables you could make a “Class Roster” table with the fields: Class Name, Student first name, and Student last name

Answer 93

Views

Question 94

Views that can use functions such as sum, average, min, max, or count
Ex: A view table called “Class Registration” could have the fields Class Name and Total Student Registered

Answer 94

Aggregate

Question 95

Similar to a validation table; helps with data integrity
Draws data from base tables, unlike a Validation Table that actually stores its own data
Restricts Fields that the view will have from the base tables (limits access)

Answer 95

Validation (view)

Question 96

Notes about views

Answer 96

▪ Views must have unique names – like other tables
▪ Security access needed to create Views
▪ Views can be nested (View built on other views)

Question 97

View SQL Example

Answer 97

/A view to return list of customers who have ordered any products/
CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id AND Orderitems.order_num = Orders.order_num;

Question 98

Process of modifying the physical DB structure to improve performance of the run-time environment.

Answer 98

Physical Database Design

Question 99

a DB ______ is a cross-reference attribute that has a pointer to take the system to the data source quickly and not touch the file/table makeup

Answer 99

Index
ex: Vendor ID 123000 - index 50

Question 100

Clustering Files/Tables

Answer 100

A technique used to bring data from multiple tables closer together on disk that is often used together - as in a join
downside is that extracting records strictly from one of the tables slows down since those record are dispersed over a greater part of the disk

Question 101

Splitting Tables - partitioning (horizontal)

Answer 101

store groups of frequently used records on a different area of the disk or even on a different disk (store near each other on concentrated space for fast access)

Question 102

Splitting Tables - partitioning (vertical)

Answer 102

store groups of frequently used columns of tables where columns most used together are stored CLOSE on disk

Question 103

SQL Example:
We want you to insert new data into the table called “Salespeople”. You’re adding a new Salesperson_ID of 12345 and the Salesperson_name is Robert Downey.

Answer 103

INSERT INTO Salespeople (Salesperson_ID, Salesperson_name)
VALUES (‘12345’, ‘Robert Downey’);

Question 104

What does this SQL do?
INSERT INTO Table A (Coll, Col2, Col3)
SELECT (Coll, Col2, Col3)
FROM Table B
WHERE Col_X = ‘condition’;

Answer 104

Moves data from table B to table A

Question 105

SQL Update Example: You are updating the customer table. For customers that have over 1000 miles, their Customer_status is changed to Preferred.

Answer 105

UPDATE Customer
SET Customer_status = ‘Preferred’
WHERE Number_of_miles > 1000;

Question 106

SQL Example:
We want you to delete some stuff from the Customer table. If the customer_status is “delinquent” (they didn’t pay their bills), please delete this customer.

Answer 106

DELETE FROM Customer
WHERE Customer_status = ‘delinquent’;

delinquent = didn’t pay bills
what happens: every row where that condition is met gets deleted

Important Notes on Deletion:
- deletes specific entire rows
-cannot delete the entire table, but without the WHERE clause, all the rows in a table can be deleted

Question 107

SQL Practice

ACME Supplies has sold the last product from Easy Street vendor (vendor #98001). The ACME Supplier Management team has decided to stop doing business with Easy Street and you have been asked to delete them as a vendor from the database and also delete all its products.

Answer 107

DELETE FROM Vendor
WHERE Vendor_ID = ‘98001’;

Question 108

Specifies how the transmitted message is broken up into small “packets” on sending end and reassembled at receiving end
(break data into packets)

Answer 108

TCP

Question 109

deals with address of the computers involved in the message exchange (used to know where to send data)

Answer 109

IP

Question 110

the protocol for exchanging hypertext info and also indicates the type of browser on the client and other information needed to format the right web pages
(explains how the info is FORMATTED)

Answer 110

HTTP

Question 111

Performance Gains: holding a copy of the retrieved data outside of the DB for other requests; often at webserver level (achieved via “query cache” which is specialized and dedicated memory)
ex: log-in credentials

Answer 111

Database persistance

Question 112

Performance Gains: a technique where frequently run queries are stored so that the query doesn’t have to go through a process of query optimization every time it is run

Answer 112

Canning

Question 113

Performance Gains: emerging space where cloud services are used to place data closer to use
Ex: someone in IL and someone in CA visiting the same website – may look the same, but they are both probably accessing different data based on what’s closest to each of them

Answer 113

Edge Computing

Question 114

create an added hardware layer to run software looking for viruses or other vulnerabilities

Answer 114

firewalls

Question 115

markup language that instructs the browser on how to DISPLAY the webpage

Answer 115

HTML

Question 116

markup language that focuses on the meaning of the data - tells how to INTERPRET the data that is being exchnaged

Answer 116

XML

Question 117

XML: Holds the data characteristics that are tied to the relational table fields and other pertinent XML info (no actual values)

Answer 117

DTD

Question 118

Holds the actual values and appropriate tags that provide the “meaning” of the data
(holds the actual data - like names and values)

Answer 118

XML Document (file)

Question 119

DTD Content models: text only, no element content allowed

Answer 119

(#PCDATA)

Question 120

DTD Content models: empty element; null

Answer 120

EMPTY

Question 121

DTD Content models: all well formed content allowed

Answer 121

ANY

Question 122

DTD Content models: text and element content

Answer 122

MIXED

Question 123

DTD Content models: one or more child element names (think fields in a table)

Answer 123

(elementname)

Question 124

You designed a vendor database and you have allowed your vendor partners the ability to electronically send you updated product information via the web that you will use to update your vendor product information using an application that resides on your application server. Product ID is the only required field. Other fields are optional, but can occur multiple times. Your product table defined on your database server has the following look:

Product: Product_ID (PK), Product_name, Product_description, Product_cost, Vendor_id (FK)

Create the DTD and XML Doc showing content received from your vendor for product SS999 where they changed the product name to “Super Soap” and set a new cost at .50 cents. Also include a comment in the prologue of XML Doc describing that there has been a product name and cost change.

Answer 124

(click edit to see correct answer)

DTD:
<!ELEMENT Product (Product_ID, Product_Name, Product_description, Product_cost*)>
<!ELEMENT Product _ID (#PCDATA) >
<!ELEMENT Product_Name (#PCDATA) >
<!ELEMENT Product_Description (#PCDATA) >
<!ELEMENT Product_cost (#PCDATA) >

XML Doc:
<!--Product_name + cost change-->

<Product>
<Product_ID> SS999 </Product_ID>
<Product_name> Super Soap </Product_name>
<Product_cost> 0.50 </Product_cost>
</Product>

Question 125

DTD: indicates options, but can occur 0 or only once (0,1)

Answer 125

?

Question 126

DTD: indicates optional, but can occur 0 or many times (0, M)

Answer 126

*

Question 127

DTD: indicates required, min 1 occurrence, and max unlimited (1, M)

Answer 127

+

Question 128

DTD: exactly one occurrence (1,1),

Answer 128

If not explicitly defined (no symbol given after the field)