Brainscape's Knowledge GenomeTM

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


See full index

D426 Database Management Foundations > Lesson 2 > Flashcards

Lesson 2 Flashcards

1
Q

relational database

A

Conceptual framework for a database systems with 3 parts:
-data structures: how data is organized
-operations: that manipulate data structures
-rules: logical constraints that ensure data is valid

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

set

A

unordered collection of elements enclosed in braces
Ex: {a,b,c} and {c,b,a}

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

tuple

A

ordered collection of elements enclosed in parentheses

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

table

A

has a name, a fixed tuple of columns, and a varying set of rows
Synonyms: Tables, File, Relation

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

column

A

has a name and a data type
Synonyms: Column, Field, Attribute

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

row

A

-unnamed tuple of values
-since rows are a set, rows have no inherit order
Synonyms: Row, Record, Tuple

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

relational data type

A

a data type is a named set of values, from which column values are drawn

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

relational algebra

A

theoretical foundation of the SQL language

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

relational rules

A

-rules are logical constraints that ensure data is valid
-govern data in every relational database

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

Relational Operations: SELECT

A

selects a subset of rows of a table

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

Relational Operations: JOIN

A

combines two tables by comparing related columns

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

Relational Operations: UNION

A

selects all rows of two tables

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

Relational Operations: AGGREGATE

A

computes functions over multiple table rows, such as sum and count

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

Business rules

A

based on business policy and specific to a particular database
Ex: All rows of the ‘Employee’ table must have a valid entry in the ‘DepartCode’ column

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

Structured Query Language/SQL

A

-high-level computer language for storing, manipulating, and retrieving data
-standard language for relational database

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

SQL statement Literals

A

Strings: must be surrounded by single or double quotes
Numeric: 123
Binary: represented with x’0’ where the 0 is any hex values

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

Data Definition Language (DDL)

A

defines the structure of the database

CREATE, ALTER, DROP

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

Data Manipulation Language (DML)

A

manipulates data stored in a database

INSERT, UPDATE, DELETE

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

Data Query Language (DQL)

A

retrieves data from database

SELECT

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

Data Control Language (DCL)

A

controls database user access

GRANT, REVOKE

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

Data Transaction Language (DTL)

A

manages database transactions

SAVEPOINT, ROLLBACK, COMMIT

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

table

A

has a name, fixed sequence of columns (tuples), and a varying set of rows

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

table rules

A

-one value per cel
-Unknown data is represented with NULL
-no duplicate column names in the same table
-no duplicate rows
-no row order

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

column

A

has a name and a data type

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

row

A

unnamed sequence of values

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

cell

A

single column of a single row

27
Q

empty table

A

table without rows

28
Q

ALTER TABLE

A

DDL
adds, delete, or modifies

29
Q

Data types: INT

A

positive or negative integer values

30
Q

Data types: VARCHAR(N)

A

values with 0 to N characters

31
Q

Data types: DATE

A

Date values YYYY-MM-DD

31
Q

Data types: CHAR(N)

A

fixed string value

32
Q

Dat types: TIME

A

hh:mm:ss

33
Q

Data types: DATETIME

A

YYYY-MM-DD HH:MM:SS

34
Q

Data types: DECIMAL

A

numeric values of which digits follow the decimal point

35
Q

Integer Storage

A
36
Q

Operator

A

operator is a symbol that computes a value from one or more values, called operands

37
Q

Arithmetic operators

A

compute numeric values from operands

38
Q

Comparison operators

A

compute logic values TRUE or FALSE

39
Q

Logical operators

A

compute of logical values from logical operand

40
Q

Operator/Operand

A
41
Q

Operator precedence

A
42
Q

NULL

A

value that represents unknown or inapplicable data

43
Q

INSERT

A

adds rows to a table

44
Q

UPDATE

A

uses SET clause to specify new column values
optional WHERE clause specifies which rows are updated

45
Q

DELETE

A

deletes existing rows in a table

46
Q

TRUNCATE

A

deletes all rows from a table

47
Q

MERGE

A

-selects data from one table, called the source, and inserts the data to another table, called the target
- MySQL does not support MERGE

48
Q

Primary Keys

A

a column, or a group of columns, used to identify a row

Must be:
UNIQUE
NOT NULL

49
Q

Simple primary key

A

consists of a single column

50
Q

Composite primary key

A

consist of multiple columns

51
Q

auto-increment

A

numeric column that is assigned an automatically incrementing value when a new row is inserted

52
Q

foreign key

A

column, or group of columns, that refer to primary key

53
Q

foreign key constraint

A

when specified, database rejects insert, update, and delete statements that violate referential integrity

54
Q

fully NULL

A

simple or composite foreign key in which all columns are NULL

55
Q

Referential integrity

A

relational rule that requires foreign key values are NULL or match primary key value

56
Q

RESTRICT

A

rejects an insert update, or delete that violate referential integrity

57
Q

RESTRIC, SET NULL, SET DEFAULT

A

Applies to primary key update and delete

Applies to foreign key insert and update

58
Q

CASCADE

A

propagates primary key changes to foreign keys

applies to primary key update and delete only

59
Q

Constraint

A

rule that governs allowable values in a database

60
Q

UNIQUE

A

ensures that values in a column, or group of columns, are unique

61
Q

CHECK

A

-specifies an expression on one or more columns of a table.
-constraint is violated when the expression is
FALSE and satisfied when the expression is either TRUE or NULL.

62
Q

SQL CONSTRAINT

A

D426 Database Management Foundations (5 decks)

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