Chapters 7 and 9 Flashcards
1
Q
Into what two broad categories do SQL functions fit into?
A
data definition language
data manipulation language
2
Q
creates a database schema
A
CREATE SCHEMA AUTHORIZATION
3
Q
creates a new table in the user’s database schema
A
create table
4
Q
ensures that a column will not have null values
A
not null
5
Q
ensures that a column will not have duplicate values
A
unique
6
Q
defines a primary key for a table
A
primary key
7
Q
defines a foreign key for a table
A
foreign key
8
Q
defines a default value for a column when no value is given
A
default
9
Q
validates data in an attribute
A
check
10
Q
creates an index for a table
A
create index
11
Q
creates a dynamic subset of rows and columns from one or more tables
A
create view
12
Q
Modifies a table’s definition (adds, modifies, or deletes attributes
or constraints)
A
Alter table
13
Q
Creates a new table based on a query in the user’s database schema
A
create table as
14
Q
permanently deletes a table
A
drop table
15
Q
permanently deletes an index
A
drop index
16
Q
permanently deletes a view
A
drop view
17
Q
the process the DBMS uses to verify that only registered users access the database
A
authentification
18
Q
schema
A
logical group of database objects
19
Q
logical group of database objects
A
schema
20
Q
a symbol that can be used as a general substitute for other characters or commands.
A
wildcard character
21
Q
wildcard character
A
a symbol that can be used as a general substitute for other characters or commands.
22
Q
field of mathematics dedicated to the use of logical operators
A
boolean algebra
23
Q
list the six basic data types
A
number, numeric, char, varchar, date
24
Q
provides for data collection, storage, and retrieval.
A
Information system
25
process that establishes the need for an information system and its extent.
systems analysis
26
process of creating an information system
systems development
27
what three factors does the performance of an information system depend on?
database design/implementation
application design and implementation
administrative procedures
28
process of database design and implementation.
database development
29
what is the primary objective of database design?
create complete, normalized, nonredundant, and fully integrated conceptual, logical, and physical database models
30
traces the history of an information system and provides the big picture within which the database design and application
development can be mapped out and evaluated.
Iterative rather than sequential
Systems Development Life Cycle
31
What are the five steps of the Systems Development Lifecycle?
```
Planning
Analysis
Detailed systems designed
Implementation
Maintenance
```
32
Professional, help produce better systems within a reasonable amount of time
and at a reasonable cost. In addition, they are more structured, better documented, and
especially standardized, which tends to prolong the operational life of systems by making them easier and cheaper to
update and maintain.
Computer-Aided systems engineering (CASE)
33
What are the six phases of the Database Life Cycle (DBLC)?
```
Database initial study
Database design
implementation and loading
testing and evaluation
operation
maintenance and evolution
```
34
defines the extent of the
| design according to operational requirements.
system scope
35
technique that creates logical representations of computing resources that are independent of the underlying physical computing resources.
virtualization
36
all database objects are backed up in their
| entirety.
full backup (dump)
37
only the objects that have been updated or modified since
| the last full backup are backed up.
differential backup
38
backs up only the transaction log operations that are not reflected in a
previous backup copy of the database.
transaction log backup
39
what are the five most common sources of database failure?
```
software
hardware
external factors
programming exemptions
transactions
```
40
first stage in the database design process
conceptual design
41
minimal data rule
all that is needed is there, and all that is there is needed.
42
what are the four steps of conceptual design?
data analysis and design
entity relationship modeling and normalization
data model verification
distributed database design
43
document that provides a
precise, up-to-date, and thoroughly reviewed description of the activities that define an organization’s operating environment.
description of operations
44
is an information system component that handles a specific business function,
such as inventory, orders, or payroll.
module
45
cohesivity describes the strength of the relationships found among
the module’s entities.
describes the strength of relationships found among the module's entities
46
describes the extent to which modules are independent of one another.
Module coupling
47
true or false, modules must display high coupling
false, the modules must be independent of other modules
48
a subset of a database that is stored at a given location.
database fragment
49
the process of determining the data storage organization and data access characteristics of the
database to ensure its integrity, security, and performance.
physical design
50
stores related rows from two related tables in adjacent data blocks on disk.
clustered tables
51
is a set of database privileges that could be assigned as a unit to a user or group.
database role
52
design starts by identifying the data sets and then defines the data elements for each of those sets.
This process involves the identification of different entity types and the definition of each entity’s attributes.
top-down database design
53
design first identifies the data elements (items) and then groups them together in data sets. In
other words, it first defines attributes, and then groups them to form entities.
bottom-up design
54
productive when the data component has a relatively small number of objects and procedures.
centralized design
55
design might be used when the system’s data component has a considerable number of entities and
complex relations on which very complex operations are performed.
decentralized design
56
Provides for data collection, storage and retrieval
information systems
57
What are information systems composed of?
```
people
hardware
software
Databases,
applications
procedures
```
58
Process that establishes need for and extent of information system
systems analysis
59
process of creating information system
systems development
60
What are the three steps of database design?
conceptual
logical
physical
61
designs a database independent of database software and physical details
designed as software and hardware independent
Conceptual data model
Components of Conceptual Design
62
Describes main data entities, attributes, relationships and constraints
Conceptual data model
63
T/F, the minimum data rule applies to the logical design process of database design
F
| Conceptual
64
Verification against proposed system processes
| Revision of original desgin
Data model verification
65
Designs an enterprise-wide database that is based on a specific data model but independent of physical -level details
logical design
66
How does one validate a logical model?
Normalization
Integrity constraints
Check against user requirements
67
Process of data storage organization and data access characteristics of the database
physical design
68
Causes two tables with a common field to be combined into a single table or view
Join operation
69
A join in which rows that do not have matching values in common columns are still included in the result table is called:
Outer Join
70
What type of join is this: A report is desired that lists all customers, the total of their orders during the most recent month, and includes customers who did not place an order during the month (their total will be zero)
outer join
71
a type of query that is placed within a WHERE or HAVING clause of another query is called
subquery
72
EXISTS is what type of operator
relational set operator
73
the _____ clause is used to restrict the output of a GROUP BY query by applying a conditional criteria to the grouped rows
partition by
74
a____ subquery is a subquery that executes once for each row in the outer query
correlated subquery
75
T/F A join operation causes tow disparate tables to be combined into a single table or view
F
| Causes 2 tables with a common field to be combined into a single table or view
76
T/F A report is desired that lists all customers and the total of their orders is called an outer-join
F
77
Specifies syntax and semantics of SQL data definition and manipulation
Specifies minimal and complete standards, which permit different degrees of adoption in products
Defines the data structures and basic operations for SQL databases
SQL Standard
78
Set of commands used to update and query a database
DML
79
DELETE FROM Customer_T WHERE state="HI"
Deletes all records from customer_t where the state is equal to HI
80
Provides rapid random and sequential access to data
Purpose of indexes
81
In a SQL statement, which of the following parts states the conditions for row selection?
Where
82
Select* from Customer Where Cust_Type="Best"
Selects all the fields from the customer table for each row with a customer labeled "Best"
83
Select Avg(standard_price) as average from product_V
The average standard_price of all products in Product_V
84
Select Count (product_description) from product_T
How many products have product descriptions in the product table?
85
Select item_no, description, from item where weight>100 and weight
The item_no and description for all items weighing between 101 and 199
86
Select driver_no, count(*) as num_deliveries from deliveries where state='MA' group by driver_no
A list of each driver who made deliveries to state='MA' as well as the number of deliveries that each driver has made to that state
87
A dependency in which one or more nonkey attributes are functionally dependent on part, but not all, of the primary key is call a _____dependency
Partial functional
88
A functional dependency between two or more nonkey attributes is called
transitive dependency
89
When all repeating groups have been removed from a table that has PK, it is said to be in which normal form?
1NF
90
Purpose of normalization
eliminate redundancies and anomolies
91
The following query will execute without errors (True/False).
Select customer.customer_name, salesman.sales_quota
From customer
Where customer.salesman_id = (select salesman_id where fname = 'Arun');
FALSE
92
Which query type relies on the outputs of the outer query to run?
Correlated subquery
93
What do we use to add conditional restrictions to the SELECT statement that limits the rows returned by the query?
WHERE
94
What is the wild card in SQL Server that can select any kind of characters/numbers and any number of characters/numbers?
%
95
To specify syntax and semantics of SQL data definition and manipulation
Purpose of SQL standard
96
This is the dependency that occurs when a non-key attribute determines another non-key attribute.
Transitive dependency
97
CRSESEC (Course#, Section#, Course name, Location)
FD: Course# > Course Name
1NF
98
When is a table in third normal form (3NF)?
It is in 2NF and contains no transitive dependencies
99
What is taking the primary key of the one side and store it as a foreign key in the many side?
This is how you create a relationship.
100
Atomicity
An attribute that cannot be further subdivided
101
What is the difference between an inner join and an outer join?
The inner join is the traditional join in which only matching rows are returned. The outer join returns not only the matching rows but the rows with unmatched attribute values for one table or both tables to be joined.
102
A _______ is invoked before or after a row is inserted, updated, or deleted and is associated with a database table?
Trigger
103
What is one of the advantages of stored procedures?
They can reduce network traffic, and the same code can be used repeatedly without retyping
104
How do we join Customer and Salesman Tables and select salesmen whose last name is Gold?
where customer.salesman_id = salesman.salesman_id and salesman.lname = ’Gold';
105
What is the abbreviation of Structured Query Language?
SQL
106
In order to have a HAVING clause, you also often need this clause.
Group By
107
```
What is
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY ;
?
```
The order how SQL statements are written.
108
What is a subquery
This is a query inside a query.
109
When running a query on two or more different tables, we often have to do this for the query to run properly.
join
110
R, SQL Server, Tableau, Web Servers, MS Excel, Desktops…
Examples of information systems
111
What is the purpose of data base design (development)?
In order to create complete, normalized, nonredundant, and fully integrated conceptual, logical, and physical database models
112
Stage where storage capacity, DBMS vendors, access rights, security measures, performance measures and so on are discussed, selected, and implemented
Physical design of databases
113
What is the NOT EQUAL sign in SQL Server?
114
How do we execute a stored procedure?
EXEC
115
What’s the function for calculating the difference of two dates?
DATEDIFF()
116
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE = 21344;
we select product description, indate, price, and vender code from the product table where the vendor code is 21344
117
What is the heart of the organization
data
118
What are the two opposite goals
| when you designing a database?
Efficiency vs. Redundancy
119
T/F the systems development life cycle is iterative rather than sequential
true
120
T/F NOT is sometimes considered an arithmetic operator
F it is a logical operator
121
checks whether attribute values matches given string pattern
LIKE
122
checks whether attribute value is null
IS NULL
123
checks whether attribute value matches any value within a value list
IN
124
checks if subquery returns any rows
EXISTS
125
clause produces list of only values that are different from one another
DISTINCT
126
T/F Max and Min are aggregate functions
T
127
T/F COUNT is not an aggregate function
F
128
Exists when there is a functional dependence in which the determinant is only part of the primary key
ONLY possible when the PK is a “composite” PK.
Partial dependency
129
Exists when there are functional dependencies such that X → Y, Y → Z, and X is the primary key
Transitive dependency
130
n many cases, a database will automatically convert one data type into another when needed. On the other hand, there are instances when that is not the case, or when you want to explicitly specify what data type to change into. In these cases, you can use
CAST function
131
similar to the CAST function in that they both change the value from one data type to another.
CONVERT
132
combine together the results from several different fields.
CONCATENATE
133
used to return a portion of string
SUBSTRING
134
used to remove specified prefix or suffix from a string
TRIM
135
used to get the length of a string
LENGTH
136
returns the current date and time
GETDATE()
137
SELECT GETDATE() AS CurrentDateTime
Obtaining the current date and time
138
adds or subtracts a specified time interval from a date.
DATEADD()
139
removes all space characters from the left-hand side of a string.
LTRIM
140
returns a character expression with lowercase character data converted to uppercase.
UPPER
141
Syntax for UPPER
UPPER ( character_expression )
142
Syntax for LTRIM
LTRIM ( character_expression )
143
Syntax for DATEADD()
DATEADD(datepart,number,date)
144
Syntax for TRIM
SELECT TRIM(' Sample ');
145
Syntax for CONCATENATE
CONCAT (str1, str2, str3, ...)
146
Syntax for CONVERT
CONVERT (expression, [data type])
147
Syntax for CAST
CAST (expression AS [data type])
148
Returns all rows when there is at least one match in BOTH tables
inner join
149
Return all rows from the left table, and the matched rows from the right table
left join
150
Return all rows from the right table, and the matched rows from the left table
right join
151
Return all rows when there is a match in ONE of the tables
full join
152
Inner join syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
153
Syntax for LEFT JOIN
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
154
Syntax for RIGHT Join
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
155
Syntax for full join
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;
156
used to combine the result-set of two or more SELECT statements.
UNION
157
UNION syntax
```
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
```
158
vocabulary used to define data structures
Definition Data Language (DDL)
159
vocabulary used to retrieve and work with data
Data manipulation language (DML)
160
used to return the results of 2 or more SELECT statements.
INTERSECT
161
INTERSECT syntax
SELECT expression1, expression2, ... expression_n
FROM tables
WHERE conditions
INTERSECT
SELECT expression1, expression2, ... expression_n
FROM tables
WHERE conditions;
162
T/F The feasibility study generally occurs during the analysis phase of the systems development lifecycle
F it occurs during the planning phase
163
T/F performing existing system evaluation occurs during the analysis phase of the systems development lifecycle
T
164
T/F Enhancement of the database occurs during the maintenance phase of the Systems development life cycle
T
165
In the database life cycle, the operation phase generally occurs after the maintenance phase
F it directly precedes it
166
T/F defining the problems and constraints in the database life cycle occurs during the initial study
T
167
Who should be thinking about these questions: How must the data be structured?
How will the data be accessed?
How are the data transformed into information?
Database designer
168
Who should be thinking about these questions:
What are the problems?
What are the solutions?
What information is needed to implement the solutions?
What data are required to generate desired information?
Manager
169
syntax for correlated subquery
SELECT * FROM t1
WHERE column1 = ANY (SELECT column1 FROM t2
WHERE t2.column2 = t1.column2);
