COMP 251 Final Flashcards
(128 cards)
1
Q
Relationship level integrity is part of overall data integrity
A
True
2
Q
2 tables can participate in multiple types of relationships with each other.
A
False
3
Q
A one-to-many relationship can only exist between 2 tables.
A
False
4
Q
You will need a Primary Key and a Foreign Key when you establish a 1-M relationship.
A
True
5
Q
An advantage of relating tables is that you can draw data from multiple tables simultaneously.
A
True
6
Q
1:M + 1:M = 2:M
A
False
7
Q
It is appropriate for a Foreign Key to have the same name as a Primary Key.
A
True
8
Q
Cascading deletes will delete records in a parent table and all associated records in a child table.
A
True
9
Q
(Open Quiz 2 Diagram image)
The diagram represents a self-referencing relationship.
A
True
10
Q
(Open Quiz 2 Diagram image)
It is mandatory for employees and mentors to exist in the table.
A
False
11
Q
(Open Quiz 2 Diagram image)
A deletion rule is present that restricts deletions in certain situations.
A
False
12
Q
(Open Quiz 2 Diagram image)
A mentor can have only one employee mentee.
A
False
13
Q
(Open Quiz 2 Diagram image)
Degree of participation is correctly diagramed.
A
False
14
Q
A SQL join is not a physical nor a logical data base structure.
A
True
15
Q
Application oriented rules are not valid business rules.
A
False
16
Q
Which item describes a relationship type?
A. one-to-many
B. mandatory
C. maximum
D. cascade
E. referential
A
A
17
Q
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
A
B
18
Q
A table with curved corners represents a __________.
A. data table
B. linking table
C. validation table
D. subset table
E. none of these
A
D
19
Q
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
A
B
20
Q
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
A
E
21
Q
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
A
A
22
Q
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
A
D
23
Q
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”
A
E
24
Q
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
A
A
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"
E
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
A
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
C
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
D
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"
E
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
A
31
Types of Cardinality:
1-1, 1-M, M-M
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)
1-1
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.
1-M
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.
M-M
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)
Self-Referencing Relationships (aka recursive relationships)
36
Type of Table:
For M-M relationships
2 keys serve as this table's composite primary key (CPK) as well as FK's
Linking Table
37
Relational database's leading advantage is speed of processing
False
38
In a relational DB, it is important to know where the RDBMS stores the data to expedite data retrieval
False
(don't have to know where it's stored)
39
The RDBMS will detect a logic error if you store NULL in a value
False
(null CAN be an acceptable value)
40
An Index is a special type of logical DB structure used to build table relationships
False
(Index is a PHYSICAL structure, indexes are a special object that allows the user to quickly retrieve records from the database)
41
Calculated fields should not be stored in a table
True
42
Table-level integrity ensures no duplicate records in a table and that the primary key is unique
True
43
Relationship level integrity is also known as Referential Integrity
True
44
Duplicate fields in your DB are never okay
False
45
IS NULL and =' ' are filtering operations that achieve the same results
False
46
!= and <> are operators that achieve the same results
True
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
C
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
D
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
B
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
E
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
D
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
D
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
E
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
E
55
One standard Concatenation symbol used in SQL is
:
a. *
b. /*
c. C
d. ||
e. none of these
D
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
e
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
c
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
B
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
A
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"
F
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
e
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
B
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
C
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
E
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
B
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
C
67
Alternative to Relational DB where speed is the leading priority, but reliability can be a concern
NoSQL
68
Includes focus on complex data sets that may include structured and unstructured data
Big Data
69
Examples include Saas, Paas, laas
Cloud
70
Requires the evaluation of data redundancy and geo-graphic redundancy
High Availability
71
Avoids over-head tied to disk utilization for read/write
In-Memory DB
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.
SELECT employee_lastname, employee_firstname, employee_hiredate
FROM EMPLOYEE;
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
SELECT employee_firstname, employee_lastname, employee_hourlywage
FROM EMPLOYEE
WHERE store_number = '222' AND employee_hourlywage < 19.50
ORDER BY employee_Iastname ASC;
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)
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;
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.
SELECT store_number
FROM STORE
WHERE store_violations = 0 AND (store_zip LIKE '606%' OR store_zip LIKE '601%');
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.”
Deny (D)
77
RDBMS does not delete the parent table record if it had related records in the child record
Restrict (R)
(most popular deletion rule)
78
RDBMS will delete the parent table record and all related records in the child table.
Cascade (C)
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.”)
Nullify (N)
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
Set Default (SD)
81
Types of Participation Symbols
l and O
82
Type of Participation: need at least 1 rec in "this" table before you can enter recs in related table
Mandatory
83
Type of Participation: there is no requirement for any rec to exist in "this" table before you enter records in related table
Optional
84
Determines the minimum number of records a given table must have and the maximum number of records allowed to have in the relationship
Degree of Participation
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?
(0, 10) Clients
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.
SELECT vendor_name, product_name, product_price
FROM vendors, products
WHERE vendors.vendor_ID = products.vendor_ID
87
Business Rule: within logical DB design, done via field specifications and/or relationship characteristics
Database oriented
88
Business Rule: can't be done in logical DB design, and therefore
is established during physical design or within the application design
Application oriented
89
DB Oriented Business Rule: imposes constraints on the elements of a field.
Ex: DOB must be shown as MM/DD/YYYY
Field Specifications
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)
Relationship Specific
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.
SELECT cust_id, cust_name, cust_contact
FROM Customer
WHERE cust_name = (SELECT cust_name
FROM Customers
WHERE cust_contact = 'Jim Jones');
92
A type of join in SQL. Allows you to include rows that have no related rows.
Outer Join
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
Views
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
Aggregate
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)
Validation (view)
96
Notes about views
▪ Views must have unique names – like other tables
▪ Security access needed to create Views
▪ Views can be nested (View built on other views)
97
View SQL Example
/*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;
98
Process of modifying the physical DB structure to improve performance of the run-time environment.
Physical Database Design
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
Index
ex: Vendor ID 123000 - index 50
100
Clustering Files/Tables
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
101
Splitting Tables - partitioning (horizontal)
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)
102
Splitting Tables - partitioning (vertical)
store groups of frequently used columns of tables where columns most used together are stored CLOSE on disk
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.
INSERT INTO Salespeople (Salesperson_ID, Salesperson_name)
VALUES ('12345', 'Robert Downey');
104
What does this SQL do?
INSERT INTO Table A (Coll, Col2, Col3)
SELECT (Coll, Col2, Col3)
FROM Table B
WHERE Col_X = 'condition';
Moves data from table B to table A
105
SQL Update Example: You are updating the customer table. For customers that have over 1000 miles, their Customer_status is changed to Preferred.
UPDATE Customer
SET Customer_status = 'Preferred'
WHERE Number_of_miles > 1000;
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.
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
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.
DELETE FROM Vendor
WHERE Vendor_ID = '98001';
108
Specifies how the transmitted message is broken up into small "packets" on sending end and reassembled at receiving end
(break data into packets)
TCP
109
deals with address of the computers involved in the message exchange (used to know where to send data)
IP
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)
HTTP
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
Database persistance
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
Canning
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
Edge Computing
114
create an added hardware layer to run software looking for viruses or other vulnerabilities
firewalls
115
markup language that instructs the browser on how to DISPLAY the webpage
HTML
116
markup language that focuses on the meaning of the data - tells how to INTERPRET the data that is being exchnaged
XML
117
XML: Holds the data characteristics that are tied to the relational table fields and other pertinent XML info (no actual values)
DTD
118
Holds the *actual values* and appropriate tags that provide the "meaning" of the data
(holds the actual data - like names and values)
XML Document (file)
119
DTD Content models: text only, no element content allowed
(#PCDATA)
120
DTD Content models: empty element; null
EMPTY
121
DTD Content models: all well formed content allowed
ANY
122
DTD Content models: text and element content
MIXED
123
DTD Content models: one or more child element names (think fields in a table)
(elementname)
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.
(click edit to see correct answer)
DTD:
XML Doc:
SS999
Super Soap
0.50
125
DTD: indicates options, but can occur 0 or only once (0,1)
?
126
DTD: indicates optional, but can occur 0 or many times (0, M)
*
127
DTD: indicates required, min 1 occurrence, and max unlimited (1, M)
+
128
DTD: exactly one occurrence (1,1),
If not explicitly defined (no symbol given after the field)
