Brainscape's Knowledge GenomeTM

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


See full index

Data Management Concepts > Chapter 6 > Flashcards

Chapter 6 Flashcards

1
Q

What is SQL?

A

Structure Query Language

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

What is the standard for relational database management systems (RDBMS)?

A

Stucture Query Language (SQL)

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

What is a relational database management systems (RDBMS)?

A

A database management system that manages data as a collection of tables in which all relationship are represented by common values in related tables.

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

What is the purpose of SQL Standard?

A
  • Specify syntax/semantics for data definition and manipulation
  • Define data structures and basic operations
  • Enable portability of database definition and application modules
  • Specify minimal (level 1) and complete (level 2) standards
  • Allow for later growth/enhancement to standard (referential integrity, transcation management, user-defined functions, extended join operations, national character sets)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What are the benefits of a standardized relational language?

A
  • Reduced training costs
  • Productivity
  • Application portability
  • Application longevity
  • Reduced dependence on a single vendor
  • Cross-system communication
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What is a catalog?

A
  • A set of schemas that constitute the description of a database
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What is a Schema?

A
  • The structure that contains descriptions of objects created by a user (base tables, views, constraints)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What is Data Definition Language (DDL)?

A
  • Commands that define a database, including creating, altering, and dropping tables and establishing constraints
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What is Data Manipulation Language (DML)?

A
  • Commands that maintain and query a database
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What is Data Control Language (DCL)?

A
  • Commands that control a database, including administering privileges and committing data.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Simplified SQL environment example

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

How DDL, DML, and DCL affect the database development process. Visual

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

What are the major CREATE statements in Data Definition Language (DDL)?

A
  • CREATE SCHEMA - defines a portion of the database owned by a particular user
  • CREATE TABLE - defines a new table and its columns
  • CREATE VIEW - defines a logical table from one or more tables or views

*Other CREATE statements*

CHARACTER SET, COLLATION, TRANSLATION, ASSERTION, DOMAIN

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

SQL Data Types - Visual

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

What are the steps in table creation?

A
  1. Identify data types for attributes
  2. Identify columns that can and cannot be null
  3. Identify columns that must be unique (candidate keys)
  4. Identify primary key - foreign key mates
  5. Determine default values
  6. Identify constraints on columns (domain specifications)
  7. Create the table and associated indexes
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

General Syntax for CREATE TABLE in DDL

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

What is a constraint that ensures that foreign key values of a table must match primary key values of a related table in 1:M relationships?

A

Referential Integrity

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

What do ALTER TABLE statements allow you to do?

A

To change column specifications:

Example:

ALTER TABLE table_name alter_table_action;

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

What are Table Actions?

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

Example of adding a new column with a dafault value

A

ALTER TABLE CUSTOMER_T

ADD COLUMN CustomerType VARCHAR2 (10) DEFAULT “Commercial”;

21
Q

What command statement allows you to remove tables from your schema?

A

DROP TABLE

Example:

DROP TABLE CUSTOMER_T

22
Q

What does an insert statement do?

A

Adds one or more rows to a table

Example:

INSERT INTO Customer_t VALUES

(001, ‘Contemporary Casuals’, ‘1355 S. Himes Blvd.’, ‘Gainsville’, ‘FL’,32601);

23
Q

What is required when inserting a record that has some null attributes?

A

It requires identifying the fields that actually get data.

Example:

INSERT INTO Product_T (ProductID, ProductDescription, ProductFinish, ProductStandardPrice) VALUES (1, ‘End Table’, ‘Cherry’, 175, 8);

24
Q

How do you insert from another table?

A

Example:

INSERT INTO CaCustomer_T

SELECT * FROM Customer_T

WHERE CustomerState = ‘CA’;

25
Q

What does a DELETE statement do?

A

Removes rows from a table

Examples:

To Delete certain rows:

DELETE FROM CUSTOMER_T WHERE CUSTOMERSTATE = ‘HI’;

To Delete all rows

DELETE FROM CUSTOMER_T;

26
Q

What does an UPDATE statement do?

A

Modified data in existing rows.

Example:

UPDATE Product_T

SET ProductStandardPrice = 775

WHERE ProductID = 7;

27
Q

What does a MERGE statement do?

A

Makes it easier to update a table… allows combination of Insert and Update in one statement.

Useful for updating master tables with new data

Example:

28
Q

What can be done to control processing/storage efficiency with Schemas?

A
  • Choice of indexes
  • File organizations for base tables
  • File organizations for indexes
  • Data clustering
  • Statistics maintenance
29
Q

Why create indexes?

A

To speed up random/sequential access to base table data

Example:

CREATE INDEX NAME_IDX ON

CUSTOMER_T(CUSTOMERNAME)

(This makes an index for the CUSTOMERNAME field of the CUSTOMER_T table)

30
Q

What is the SELECT statement used for?

A

Used for queries on single or multiple tables

31
Q

What are the clauses of the SELECT statement?

A

SELECT - List the columns (and expressions) to be returned from the query

FROM - Indicate the table(s) or view(s) from which data will be obtained

WHERE - Indicate the conditions under which a row will be included in the result

GROUP BY - Indicate categorization of results

HAVING - Indicate the conditions under which a category (group) will be included

ORDER BY - Sorts the result according to specfied criteria

32
Q

SQL Statement processing order, visual

A
33
Q

What is Alias?

A

An alternative column or table name

Example:

SELECT CUST.CUSTOMERNAME AS NAME, CUST.CUSTOMERADDRESS

FROM CUSTOMER_V CUST

WHERE NAME = ‘Home Furnishings’;

34
Q

What does the COUNT aggregate function do?

A

Used to find totals

Example:

SELECT COUNT(*) FROM ORDERLINE_T

WHERE ORDERID = 1004;

*Note: With aggregate functions you can’t have single-valued columns included in the SELECT clause, unless they are included in the GROUP BY clause.

35
Q

What are the boolean operations in the SELECT FUNCTION?

A

AND, OR, and NOT Operators for customizing conditions in the WHERE clause.

Example:

36
Q

What does the LIKE operator allow you to do?

A

Allows you to compare strings using wildcards.

For example, the % wildcard in ‘%Desk’ indicates that all strings that have any number of character preceding the word “Desk” will be allowed.

37
Q

What is the default processing order for boolean operators?

A

NOT, then AND, then OR

38
Q

How do you override the normal procedence of Boolean operators?

A

With parentheses

example:

39
Q

What does the ORDER BY clause do?

A

Sorts the results by specified order.

Example:

40
Q

What can be used with aggregate functions?

A
  • Scalar aggregate - single value returned from SQL query with aggregate function
  • Vector aggregate - multiple values returned from SQL query with aggregate function (via GROUP BY)

Example:

41
Q

What does the HAVING clause do?

A

It’s used with GROUP BY, it’s like a WHERE clause, but it operates on groups (categories), not on individual rows.

Example:

42
Q

What are views used for?

A

They provide users controlled access to tables.

43
Q

What is a Base Table?

A

Table containing the raw data

44
Q

What are the two types of views?

A

Dynamic View

Materialized View

45
Q

What is Dynamic View?

A
  • A “virtual table” created dynamically upon request by a user
  • No data actually stored; instead data from base table made available to user
  • Based on SQL SELECT statement on base tables or other views
46
Q

What is materialized view?

A
  • Copy or replication of data
  • Data actually stored
  • Must be refreshed periodically to match corresponding base tables
47
Q

When does the CHECK_OPTION work?

A

Works only for updateable views and prevents updates that would create rows not included in the view.

48
Q

What are the advantage of views?

A
  • Simplify query commands
  • Assist with data security (but don’t rely on views for security, there are more important security measures)
  • Enhance programming productivity
  • Contain most current base table data
  • Use little storage space
  • Provide customized view for user
  • Establish physical data independence
49
Q

What are the disadvantages of views?

A
  • Use processing time each time view is referenced
  • May or may not be directly updateable

Data Management Concepts (12 decks)

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