Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Database Management > Chapters 7 and 9 > Flashcards

Chapters 7 and 9 Flashcards

1
Q

Into what two broad categories do SQL functions fit into?

A

data definition language

data manipulation language

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

creates a database schema

A

CREATE SCHEMA AUTHORIZATION

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

creates a new table in the user’s database schema

A

create table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

ensures that a column will not have null values

A

not null

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

ensures that a column will not have duplicate values

A

unique

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

defines a primary key for a table

A

primary key

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

defines a foreign key for a table

A

foreign key

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

defines a default value for a column when no value is given

A

default

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

validates data in an attribute

A

check

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

creates an index for a table

A

create index

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

creates a dynamic subset of rows and columns from one or more tables

A

create view

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Modifies a table’s definition (adds, modifies, or deletes attributes
or constraints)

A

Alter table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Creates a new table based on a query in the user’s database schema

A

create table as

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

permanently deletes a table

A

drop table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

permanently deletes an index

A

drop index

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

permanently deletes a view

A

drop view

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

the process the DBMS uses to verify that only registered users access the database

A

authentification

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

schema

A

logical group of database objects

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

logical group of database objects

A

schema

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

a symbol that can be used as a general substitute for other characters or commands.

A

wildcard character

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

wildcard character

A

a symbol that can be used as a general substitute for other characters or commands.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

field of mathematics dedicated to the use of logical operators

A

boolean algebra

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

list the six basic data types

A

number, numeric, char, varchar, date

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

provides for data collection, storage, and retrieval.

A

Information system

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

process that establishes the need for an information system and its extent.

A

systems analysis

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

process of creating an information system

A

systems development

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

what three factors does the performance of an information system depend on?

A

database design/implementation
application design and implementation
administrative procedures

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

process of database design and implementation.

A

database development

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

what is the primary objective of database design?

A

create complete, normalized, nonredundant, and fully integrated conceptual, logical, and physical database models

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

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

A

Systems Development Life Cycle

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

What are the five steps of the Systems Development Lifecycle?

A 
Planning
Analysis
Detailed systems designed
Implementation
Maintenance
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

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.

A

Computer-Aided systems engineering (CASE)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

What are the six phases of the Database Life Cycle (DBLC)?

A 
Database initial study
Database design
implementation and loading
testing and evaluation
operation
maintenance and evolution
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

defines the extent of the

design according to operational requirements.

A

system scope

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

technique that creates logical representations of computing resources that are independent of the underlying physical computing resources.

A

virtualization

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

all database objects are backed up in their

entirety.

A

full backup (dump)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

only the objects that have been updated or modified since

the last full backup are backed up.

A

differential backup

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

backs up only the transaction log operations that are not reflected in a
previous backup copy of the database.

A

transaction log backup

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

what are the five most common sources of database failure?

A 
software
hardware
external factors
programming exemptions
transactions
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

first stage in the database design process

A

conceptual design

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

minimal data rule

A

all that is needed is there, and all that is there is needed.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

what are the four steps of conceptual design?

A

data analysis and design
entity relationship modeling and normalization
data model verification
distributed database design

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

document that provides a
precise, up-to-date, and thoroughly reviewed description of the activities that define an organization’s operating environment.

A

description of operations

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

is an information system component that handles a specific business function,
such as inventory, orders, or payroll.

A

module

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
45
Q

cohesivity describes the strength of the relationships found among
the module’s entities.

A

describes the strength of relationships found among the module’s entities

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
46
Q

describes the extent to which modules are independent of one another.

A

Module coupling

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

true or false, modules must display high coupling

A

false, the modules must be independent of other modules

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
48
Q

a subset of a database that is stored at a given location.

A

database fragment

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
49
Q

the process of determining the data storage organization and data access characteristics of the
database to ensure its integrity, security, and performance.

A

physical design

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
50
Q

stores related rows from two related tables in adjacent data blocks on disk.

A

clustered tables

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
51
Q

is a set of database privileges that could be assigned as a unit to a user or group.

A

database role

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
52
Q

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.

A

top-down database design

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
53
Q

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.

A

bottom-up design

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
54
Q

productive when the data component has a relatively small number of objects and procedures.

A

centralized design

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
55
Q

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.

A

decentralized design

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
56
Q

Provides for data collection, storage and retrieval

A

information systems

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
57
Q

What are information systems composed of?

A 
people
hardware
software
Databases,
applications
procedures
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
58
Q

Process that establishes need for and extent of information system

A

systems analysis

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
59
Q

process of creating information system

A

systems development

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
60
Q

What are the three steps of database design?

A

conceptual
logical
physical

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
61
Q

designs a database independent of database software and physical details
designed as software and hardware independent
Conceptual data model

A

Components of Conceptual Design

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
62
Q

Describes main data entities, attributes, relationships and constraints

A

Conceptual data model

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
63
Q

T/F, the minimum data rule applies to the logical design process of database design

A

F

Conceptual

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
64
Q

Verification against proposed system processes

Revision of original desgin

A

Data model verification

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
65
Q

Designs an enterprise-wide database that is based on a specific data model but independent of physical -level details

A

logical design

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
66
Q

How does one validate a logical model?

A

Normalization
Integrity constraints
Check against user requirements

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
67
Q

Process of data storage organization and data access characteristics of the database

A

physical design

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
68
Q

Causes two tables with a common field to be combined into a single table or view

A

Join operation

69
Q

A join in which rows that do not have matching values in common columns are still included in the result table is called:

A

Outer Join

70
Q

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)

A

outer join

71
Q

a type of query that is placed within a WHERE or HAVING clause of another query is called

A

subquery

72
Q

EXISTS is what type of operator

A

relational set operator

73
Q

the _____ clause is used to restrict the output of a GROUP BY query by applying a conditional criteria to the grouped rows

A

partition by

74
Q

a____ subquery is a subquery that executes once for each row in the outer query

A

correlated subquery

75
Q

T/F A join operation causes tow disparate tables to be combined into a single table or view

A

F

Causes 2 tables with a common field to be combined into a single table or view

76
Q

T/F A report is desired that lists all customers and the total of their orders is called an outer-join

A

F

77
Q

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

A

SQL Standard

78
Q

Set of commands used to update and query a database

A

DML

79
Q

DELETE FROM Customer_T WHERE state=”HI”

A

Deletes all records from customer_t where the state is equal to HI

80
Q

Provides rapid random and sequential access to data

A

Purpose of indexes

81
Q

In a SQL statement, which of the following parts states the conditions for row selection?

A

Where

82
Q

Select* from Customer Where Cust_Type=”Best”

A

Selects all the fields from the customer table for each row with a customer labeled “Best”

83
Q

Select Avg(standard_price) as average from product_V

A

The average standard_price of all products in Product_V

84
Q

Select Count (product_description) from product_T

A

How many products have product descriptions in the product table?

85
Q

Select item_no, description, from item where weight>100 and weight

A

The item_no and description for all items weighing between 101 and 199

86
Q

Select driver_no, count(*) as num_deliveries from deliveries where state=’MA’ group by driver_no

A

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
Q

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

A

Partial functional

88
Q

A functional dependency between two or more nonkey attributes is called

A

transitive dependency

89
Q

When all repeating groups have been removed from a table that has PK, it is said to be in which normal form?

A

1NF

90
Q

Purpose of normalization

A

eliminate redundancies and anomolies

91
Q

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’);

A

FALSE

92
Q

Which query type relies on the outputs of the outer query to run?

A

Correlated subquery

93
Q

What do we use to add conditional restrictions to the SELECT statement that limits the rows returned by the query?

A

WHERE

94
Q

What is the wild card in SQL Server that can select any kind of characters/numbers and any number of characters/numbers?

A

%

95
Q

To specify syntax and semantics of SQL data definition and manipulation

A

Purpose of SQL standard

96
Q

This is the dependency that occurs when a non-key attribute determines another non-key attribute.

A

Transitive dependency

97
Q

CRSESEC (Course#, Section#, Course name, Location)

FD: Course# > Course Name

A

1NF

98
Q

When is a table in third normal form (3NF)?

A

It is in 2NF and contains no transitive dependencies

99
Q

What is taking the primary key of the one side and store it as a foreign key in the many side?

A

This is how you create a relationship.

100
Q

Atomicity

A

An attribute that cannot be further subdivided

101
Q

What is the difference between an inner join and an outer join?

A

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
Q

A _______ is invoked before or after a row is inserted, updated, or deleted and is associated with a database table?

A

Trigger

103
Q

What is one of the advantages of stored procedures?

A

They can reduce network traffic, and the same code can be used repeatedly without retyping

104
Q

How do we join Customer and Salesman Tables and select salesmen whose last name is Gold?

A

where customer.salesman_id = salesman.salesman_idand salesman.lname = ’Gold’;

105
Q

What is the abbreviation of Structured Query Language?

A

SQL

106
Q

In order to have a HAVING clause, you also often need this clause.

A

Group By

107
Q 
What is
SELECT
  FROM
    WHERE
       GROUP BY
           HAVING
              ORDER BY ;  
?
A

The order how SQL statements are written.

108
Q

What is a subquery

A

This is a query inside a query.

109
Q

When running a query on two or more different tables, we often have to do this for the query to run properly.

A

join

110
Q

R, SQL Server, Tableau, Web Servers, MS Excel, Desktops…

A

Examples of information systems

111
Q

What is the purpose of data base design (development)?

A

In order to create complete, normalized, nonredundant, and fully integrated conceptual, logical, and physical database models

112
Q

Stage where storage capacity, DBMS vendors, access rights, security measures, performance measures and so on are discussed, selected, and implemented

A

Physical design of databases

113
Q

What is the NOT EQUAL sign in SQL Server?

A
114
Q

How do we execute a stored procedure?

A

EXEC

115
Q

What’s the function for calculating the difference of two dates?

A

DATEDIFF()

116
Q

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE = 21344;

A

we select product description, indate, price, and vender code from the product table where the vendor code is 21344

117
Q

What is the heart of the organization

A

data

118
Q

What are the two opposite goals

when you designing a database?

A

Efficiency vs. Redundancy

119
Q

T/F the systems development life cycle is iterative rather than sequential

A

true

120
Q

T/F NOT is sometimes considered an arithmetic operator

A

F it is a logical operator

121
Q

checks whether attribute values matches given string pattern

A

LIKE

122
Q

checks whether attribute value is null

A

IS NULL

123
Q

checks whether attribute value matches any value within a value list

A

IN

124
Q

checks if subquery returns any rows

A

EXISTS

125
Q

clause produces list of only values that are different from one another

A

DISTINCT

126
Q

T/F Max and Min are aggregate functions

A

T

127
Q

T/F COUNT is not an aggregate function

A

F

128
Q

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.

A

Partial dependency

129
Q

Exists when there are functional dependencies such that X → Y, Y → Z, and X is the primary key

A

Transitive dependency

130
Q

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

A

CAST function

131
Q

similar to the CAST function in that they both change the value from one data type to another.

A

CONVERT

132
Q

combine together the results from several different fields.

A

CONCATENATE

133
Q

used to return a portion of string

A

SUBSTRING

134
Q

used to remove specified prefix or suffix from a string

A

TRIM

135
Q

used to get the length of a string

A

LENGTH

136
Q

returns the current date and time

A

GETDATE()

137
Q

SELECT GETDATE() AS CurrentDateTime

A

Obtaining the current date and time

138
Q

adds or subtracts a specified time interval from a date.

A

DATEADD()

139
Q

removes all space characters from the left-hand side of a string.

A

LTRIM

140
Q

returns a character expression with lowercase character data converted to uppercase.

A

UPPER

141
Q

Syntax for UPPER

A

UPPER ( character_expression )

142
Q

Syntax for LTRIM

A

LTRIM ( character_expression )

143
Q

Syntax for DATEADD()

A

DATEADD(datepart,number,date)

144
Q

Syntax for TRIM

A

SELECT TRIM(‘ Sample ‘);

145
Q

Syntax for CONCATENATE

A

CONCAT (str1, str2, str3, …)

146
Q

Syntax for CONVERT

A

CONVERT (expression, [data type])

147
Q

Syntax for CAST

A

CAST (expression AS [data type])

148
Q

Returns all rows when there is at least one match in BOTH tables

A

inner join

149
Q

Return all rows from the left table, and the matched rows from the right table

A

left join

150
Q

Return all rows from the right table, and the matched rows from the left table

A

right join

151
Q

Return all rows when there is a match in ONE of the tables

A

full join

152
Q

Inner join syntax

A

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

153
Q

Syntax for LEFT JOIN

A

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

154
Q

Syntax for RIGHT Join

A

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

155
Q

Syntax for full join

A

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

156
Q

used to combine the result-set of two or more SELECT statements.

A

UNION

157
Q

UNION syntax

A 
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
158
Q

vocabulary used to define data structures

A

Definition Data Language (DDL)

159
Q

vocabulary used to retrieve and work with data

A

Data manipulation language (DML)

160
Q

used to return the results of 2 or more SELECT statements.

A

INTERSECT

161
Q

INTERSECT syntax

A

SELECT expression1, expression2, … expression_n
FROM tables
WHERE conditions
INTERSECT
SELECT expression1, expression2, … expression_n
FROM tables
WHERE conditions;

162
Q

T/F The feasibility study generally occurs during the analysis phase of the systems development lifecycle

A

F it occurs during the planning phase

163
Q

T/F performing existing system evaluation occurs during the analysis phase of the systems development lifecycle

A

T

164
Q

T/F Enhancement of the database occurs during the maintenance phase of the Systems development life cycle

A

T

165
Q

In the database life cycle, the operation phase generally occurs after the maintenance phase

A

F it directly precedes it

166
Q

T/F defining the problems and constraints in the database life cycle occurs during the initial study

A

T

167
Q

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?

A

Database designer

168
Q

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?

A

Manager

169
Q

syntax for correlated subquery

A

SELECT * FROM t1
WHERE column1 = ANY (SELECT column1 FROM t2
WHERE t2.column2 = t1.column2);

Database Management (4 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions