Brainscape's Knowledge GenomeTM

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


See full index

Data Management Concepts > Chapter 7 > Flashcards

Chapter 7 Flashcards

1
Q

What is a Join?

A

A relational operation that causes two or more tables with a common domain to be combined into a single table or view.

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

What is Equi-join?

A

A join in which the joining condition is based on equality between values in the common columns; common columns appear redundantly in the result table

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

What is Natural join?

A

An equi-join in which one of the duplicate columns is eliminated in the result table.

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

What are usually the common columns in joined tables?

A

Usually the primary key of the dominant table and the foreign key of the dependent table in 1:M relationships.

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

What is Outer join?

A

A join in which rows that do not have matching values in common columns are nonetheless included in the result table (as opposed to inner join, in which rows must have matching values in order to appear in the result table)

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

What is Union join?

A

Includes all data from each table that was joined

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

Example of different join types

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

What is a subquery?

A

Placing an inner query (SELECT statement) inside an outer query

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

What are the options for using a subquery?

A
  • In a conditon of the WHERE clause
  • As a “table” of the FROM clause
  • Within the HAVING clause
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What are the two options for a subquerie?

A

Noncorrelated

Correlated

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

What is a noncorrelated subquerie?

A

Executed once for the entire outer query

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

What is a correlated subquerie?

A

Executed once for each row returned by the outer query

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

What are the traits of a noncorrelated subquerie?

A
  • Do not depend on data from the outer query
  • Execute once for the entire outer query
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What are the traits of a correlated subquerie?

A
  • Make use of data from the outer query
  • Execute once for each row of the outer query
  • Can use the EXISTS operator
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is a union querie?

A

Combine the output (union of multiple queries) together into a single result table

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

Why would it be useful to combine queries, through the use of Views?

A

Because production databases contain hundreds or even thousands of tables, and tables could include hundreds of columns.

17
Q

What can you do if you use a view (which is a query)?

A

You could have another query that uses the view as if it were a table.

18
Q

What are some tips for developing queries?

A
  • Be familiar with the data model (entities and relationships)
  • Understand the desired results
  • Know the attributes desired in the results
  • Identify the entities that contain desired attributes
  • Review ERD
  • Construct a WHERE equality for each link
  • Fine tune with GROUP BY and HAVING clauses if needed
  • Consider the effect on unusual data
19
Q

What are some considerations for Query efficiency?

A
  • Instead of SELECT *, identify the specific attributes in the SELECT clause; this helps reduce network traffic of result set
  • Limit the number of subqueries; try to make everything done in a single query if possible
  • If data is to be used many times, make a separate query and store it as a view
20
Q

What are some guidelines for better query design?

A
  • Understand how indexes are used in query processing
  • Keep optimizer statistics up-to-date
  • Use compatible data types for fields and literals
  • Write simple queries
  • Break complex queries into multiple simple parts
  • Don’t nest one query inside another query
  • Don’t combine a query with itself (if possible avoid self-joins)
21
Q

What are some more guidelines for better query design?

A
  • Create temporary tables for groups of queries
  • Combine update operations
  • Retrieve only the data you need
  • Don’t have the DBMS sort without an index
  • Learn!
  • Consider the total query processing time for ad hoc queries
22
Q

What is transaction?

A

A discrete unit of work that must be completely processed or not processed at all

  • May involve multiple updates
  • If any update fails, then all other updates must be cancelled
23
Q

What are the SQL commands for transactions?

A
  • BEGIN TRANSACTION/END TRANSACTION (Marks boundaries of a transaction)
  • COMMIT (Makes all updates permanent)
  • ROLLBACK (Cancels updates since the last COMMIT)
24
Q

What are data dictionary facilities?

A
  • System tables that store metadata
  • Users usually can view some of these tables
  • Users are restricted from updating them
25
Q

Examples of data dictionary facilities in Oracle 12c

A

DBA_TABLES - descriptions of tables

DBA_CONSTRAINTS - description of constraints

DBA_USERS - information abou tthe users of the system

26
Q

Examples of data dictionary facilities in Microsoft SQL Server 2014

A
  • sys.columns - table and column definitions
  • sys.indexes - table index information
  • sys.foreign_key_columns - details about columns in foreign key constraints
27
Q

What are user-defined data types (UDT)?

A

Subclasses of standard types or an object type

28
Q

What are the analytical functions (for OLAP)?

A

CEILING, FLOOR, SQRT, RANK, DENSE_RANK, ROLLUP, CUBE, SAMPLE

WINDOW -improve numerical analysis capabilities

29
Q

What are new data types in SQL:2008?

A

BIGINT, MULTISET (collection), XML

30
Q

What does CREATE TABLE LIKE do?

A

Creates a new table similar to an existing one

31
Q

What are routines?

A

Program modules that execute on demand

32
Q

What are functions?

A

Routines that return values and take input parameters

33
Q

What are procedures?

A

Routines that do not return values and can take input or output parameters.

34
Q

What are triggers?

A

Routines that execute in response to a database event (INSERT, UPDATE, or DELETE)

35
Q

What is Embedded SQL?

A

Including hard-coded SQL statements in a program written in another language such as C or Java

36
Q

What is Dynamic SQL?

A

Ability for an application program to generate SQL code on the fly, as the application is running.

37
Q

What are the reasons to embed SQL in 3GL?

A
  • Can create a more flexible, accessible interface for the user
  • Possible performance improvement
  • Database security improvement; grant access only to the application instead of users

Data Management Concepts (12 decks)

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