Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

Data Management Concepts > Chapter 6 > Flashcards

Chapter 6 Flashcards

(49 cards)

Start Studying
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

Study These Flashcards
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?

Study These Flashcards
A

DROP TABLE

Example:

DROP TABLE CUSTOMER_T

22
Q

What does an insert statement do?

Study These Flashcards
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?

Study These Flashcards
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?

Study These Flashcards
A

Example:

INSERT INTO CaCustomer_T

SELECT * FROM Customer_T

WHERE CustomerState = ‘CA’;

25
What does a DELETE statement do?
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
What does an UPDATE statement do?
Modified data in existing rows. Example: UPDATE Product\_T SET ProductStandardPrice = 775 WHERE ProductID = 7;
27
What does a MERGE statement do?
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
What can be done to control processing/storage efficiency with Schemas?
- Choice of indexes - File organizations for base tables - File organizations for indexes - Data clustering - Statistics maintenance
29
Why create indexes?
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
What is the SELECT statement used for?
Used for queries on single or multiple tables
31
What are the clauses of the SELECT statement?
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
SQL Statement processing order, visual
33
What is Alias?
An alternative column or table name Example: SELECT **CUST**.CUSTOMERNAME AS **NAME,** CUST.CUSTOMERADDRESS FROM CUSTOMER\_V **CUST** WHERE **NAME** = 'Home Furnishings';
34
What does the COUNT aggregate function do?
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
What are the boolean operations in the SELECT FUNCTION?
AND, OR, and NOT Operators for customizing conditions in the WHERE clause. Example:
36
What does the LIKE operator allow you to do?
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
What is the default processing order for boolean operators?
NOT, then AND, then OR
38
How do you override the normal procedence of Boolean operators?
With parentheses example:
39
What does the ORDER BY clause do?
Sorts the results by specified order. Example:
40
What can be used with aggregate functions?
- 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
What does the HAVING clause do?
It's used with GROUP BY, it's like a WHERE clause, but it operates on groups (categories), not on individual rows. Example:
42
What are views used for?
They provide users controlled access to tables.
43
What is a Base Table?
Table containing the raw data
44
What are the two types of views?
Dynamic View Materialized View
45
What is Dynamic View?
- 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
What is materialized view?
- Copy or replication of data - Data actually stored - Must be refreshed periodically to match corresponding base tables
47
When does the CHECK\_OPTION work?
Works only for updateable views and prevents updates that would create rows not included in the view.
48
What are the advantage of views?
- 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
What are the disadvantages of views?
- 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.
© 2025 Bold Learning Solutions. Terms and Conditions