Brainscape's Knowledge GenomeTM

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


See full index

Data Management Foundations, D426 > Relational DB > Flashcards

Relational DB Flashcards

1
Q
  • What are the three components of a database model?
A

Data structure → describes how data is organized

Operations → manipulates data structures

Rules → govern valid data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q
  • What is a tuple?
A

a tuple is an ordered collection

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q
  • What is a set?
A

a set is an unordered collection of elements enclosed in braces
- {a,b,c} and {c,b,a} are the same because order doesn’t matter

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q
  • How is a relational database structure organized?
A

a table has a name, a fixed tuple of columns, and varying sets of rows
- a column has a name and a data type
- a row is an unnamed tuple of values. each value corresponds to a column and belongs to the columns data type
- 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
5
Q

Describe ‘select’

A

Selectselects a subset of rows of a table.

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

Describe ‘project’

A

Project eliminates one or more columns of a table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q
  • Describe ‘product’
A
  • Productlists all combinations of rows of two tables.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q
  • Describe ‘join’
A
  • Joincombines two tables by comparing related columns.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q
  • Describe ‘union’
A
  • Unionselects all rows of two tables.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q
  • Describe ‘intersect’
A

Intersect selects rows common to two tables.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q
  • Describe ‘difference’
A

Difference selects rows that appear in one table but not another.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q
  • Describe ‘rename’
A
  • Renamechanges a table name.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q
  • Describe ‘aggregate’
A
  • Aggregate 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
  • What are the three logical constraints in relational database management that ensure data is valid?
A
  • Unique primary key → all tables have a primary key column, or group of columns, in which values may not repeat
  • Unique column names → Different columns of the same table have different names
  • No duplicate rows → No two rows of the same table have identical values in all columns
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q
  • What does SQL stand for?
A

Structured Query Language

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q
  • What is a ‘statement’?
A
  • A statement is a complete command composed of one or more clauses
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q
  • What is a ‘clause’?
A
  • A clause groups SQL keywords like SELECT, FROM, and WHERE with table names
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q
  • What are some key words in SQL?
A

SELECT, FROM, WHERE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q
  • How do you end a statement in MySQL?
A

a semi-colon, “ ; “

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q
  • What is DDL and what is it responsible for?
A
  • Data definition language (DDL) → Defines the structure of the database
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q
  • What is DQL and what is it responsible for?
A
  • Data query language (DQL) → Retrieves data from the database
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q
  • What is DML and what is it responsible for?
A
  • Data manipulation language (DML)→ Manipulates data stored in a database
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q
  • What is DCL and what is it responsible for?
A
  • Data control language (DCL) → Controls database user access
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q
  • What is DTL and what is it responsible for?
A
  • Data transaction language (DTL) → Manages database transactions. commits data to a database, rolls back data from a database, and creates save points.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q
  • Describe the statement CREATE DATABASE {database name}
A

creates a new db

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q
  • Describe the statement DROP DATABASE {database name}
A

deletes a db, including all tables

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q
  • Describe the keyword SHOW
A

provides information about database and its contents

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q
  • Describe some commonly used SHOW statements
A
  • SHOW DATABASES → lists dbs available in the system
  • SHOW TABLES → lists tables available in the currently selected db
  • SHOW COLUMNS → Lists columns in a specified table named by a FROM clause
  • SHOW CREATE TABLE → shows the CREATE TABLE statement for a given table
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q
  • In relational databases, how many values can exist in one cell?
A

1

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q
  • Are duplicate column names allowed?
A

no

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q
  • Are duplicate rows allowed?
A

no

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q
  • How does row order affect query status?
A
  • No row order. The organization of the rows never affects query results data independence
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

Define data independence

A
  • The result of a database query is not affected by the physical organization of data on storage devices
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q
  • Describe the Integer data type
A
  • Integerdata types represent positive and negative integers. Several integer data types exist, varying by the number of bytes allocated for each value. Common integer data types include INT, implemented as 4 bytes of storage, and SMALLINT, implemented as 2 bytes.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q
  • Describe the decimal data type
A
  • Decimaldata types represent numbers with fractional values. Decimal data types vary by number of digits after the decimal point and maximum size. Common decimal data types include FLOAT and DECIMAL.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q
  • Describe the Character data type
A
  • Characterdata types represent textual characters. Common character data types include CHAR, a fixed string of characters, and VARCHAR, a string of variable length up to a specified maximum size.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q
  • Describe the Date and time data types
A
  • Date and timedata types represent date, time, or both. Some date and time data types include a time zone or specify a time interval. Some date and time data types represent an interval rather than a point in time. Common date and time data types include DATE, TIME, DATETIME, and TIMESTAMP.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q
  • Describe the Binary data type
A
  • Binarydata types store data exactly as the data appears in memory or computer files, bit for bit. The database manages binary data as a series of zeros and ones. Common binary data types include BLOB, BINARY, VARBINARY, and IMAGE.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q
  • Describe the Spatial data type
A
  • Spatialdata types store geometric information, such as lines, polygons, and map coordinates. Examples include POLYGON, POINT, and GEOMETRY. Spatial data types are relatively new and consequently vary greatly across database systems.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q
  • Describe the Document data type
A
  • Document data types contain textual data in a structured format such as XML or JSON.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q
  • Describe the MySQL datatype TINYINT
A

1 byte Signed range: -128 to 127
Unsigned range: 0 to 255

42
Q
  • Describe the MySQL datatype SMALLINT
A

2 bytes Signed range: -32,768 to 32,767
Unsigned range: 0 to 65,535

43
Q
  • Describe the MySQL datatype MEDIUMINT
A

3 bytes Signed range: -8,388,608 to 8,388,607
Unsigned range: 0 to 16,777,215

44
Q
  • Describe the MySQL datatype INTEGER or INT
A

4 bytes Signed range: -2,147,483,648 to 2,147,483,647
Unsigned range: 0 to 4,294,967,295

45
Q
  • Describe the MySQL datatype DECIMAL(M,D)
A

DECIMAL(M,D) Varies depending on M and D Exact decimal number where M = number of significant digits, D = number of digits after decimal point

46
Q
  • Describe the MySQL datatype FLOAT
A

4 bytes Approximate decimal numbers with range: -3.4E+38 to 3.4E+38

47
Q
  • Describe the MySQL datatype DOUBLE
A

8 bytes Approximate decimal numbers with range: -1.8E+308 to 1.8E+308

48
Q
  • Describe the MySQL datatype DATE
A

3 bytes Format: YYYY-MM-DD. Range: ‘1000-01-01’ to ‘9999-12-31’

49
Q
  • Describe the MySQL datatype TIME
A

3 bytes Format: hh:mm:ss

50
Q
  • Describe the MySQL datatype DATETIME
A

5 bytes Format: YYYY-MM-DD hh:mm:ss. Range: ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.

51
Q
  • Describe the MySQL datatype CHAR(N)
A

N bytes Fixed-length string of length N; 0 ≤ N ≤ 255

52
Q
  • Describe the MySQL datatype VARCHAR(N)
A

Length of characters + 1 bytes Variable-length string with maximum N characters; 0 ≤ N ≤ 65,535

53
Q
  • Describe the MySQL datatype TEXT
A

Length of characters + 2 bytes Variable-length string with maximum 65,535 characters

54
Q
  • What is an operator?
A

A symbol that computes a value from one or more other values called operands

55
Q
  • What is an operand?
A
56
Q
  • Describe an arithmetic operator
A

operators compute numeric values from numeric operands

57
Q
  • Describe a comparison operator
A

compute logical values TRUE and FALSE. Operands can be numeric, character, and other data types.

58
Q
  • Describe a logical operator
A

operators compute logical values from logical operands

59
Q
  • Describe operator precedence
A

is the order in which an expression is operated

60
Q
  • Describe the logical operator AND
A
  • Returns TRUE when only both values are TRUE
61
Q
  • Describe the logical operator OR
A
  • Returns FALSE when only both values are FALSE
62
Q
  • Describe the logical operator NOT
A
  • Reverses a logical value
63
Q
  • Describe NULL
A
  • Unknown or inapplicable data
64
Q
  • Describe NOT NULL
A
  • A constraint added to columns which prevents a NULL value being added as data
65
Q
  • How do you return a row with a value of NULL?
A
  • WHERE ___ IS NULL. ‘IS NULL’
66
Q
  • Describe MySQL truth table
A
67
Q
  • In MySQL how does x=TRUE AND y=NULL evaluate?
A

null

68
Q
  • In MySQL how does x=NULL AND y=TRUE evaluate?
A

null

69
Q
  • In MySQL how does x=TRUE OR y=NULL evaluate?
A

true

70
Q
  • In MySQL how does x=NULL OR y=TRUE evaluate?
A

true

71
Q
  • In MySQL how does x=FALSE OR y=NULL evaluate?
A

null

72
Q
  • In MySQL how does x=NULL OR y=FALSE evaluate?
A

null

73
Q
  • In MySQL how does x=FALSE AND y=NULL evaluate?
A

false

74
Q
  • In MySQL how does x=NULL AND y=FALSE evaluate?
A

false

75
Q
  • In MySQL how does x=NULL AND y=NULL evaluate?
A

null

76
Q
  • In MySQL how does x=NULL OR y=NULL evaluate?
A

null

77
Q
  • Describe the INSERT statement
A
  • Adds a row to a table
    Contains two clauses:
    • INSERT INTO clause names the table and columns where data is to be added.
      • Note that INTO is optional
    • VALUES clause specifies the column values to be added
      • May list any number of rows in parentheses to insert multiple rows
78
Q
  • Describe the INTO keyword
A
  • INSERT INTO clause names the table and columns where data is to be added.
  • Note that INTO is optional
79
Q
  • Describe DEFAULT clause
A

The DEFAULT key word can be used on column creation to specify a value if one is not provided when INSERT-ing a new column

80
Q
  • Describe the UPDATE statement
A
  • UPDATE modifies existing rows
81
Q
  • What clause is used with an UPDATE statement
A

the SET clause is used to specify the new value

82
Q
  • Describe the WHERE clause
A

Specifies a condition

83
Q
  • Describe a primary key
A
  • A column or group of columns used to identify a row
    • Usually the tables first column, but the position isn’t significant
84
Q
  • Describe the characteristics of a primary key
A
  • Primary keys must be unique
  • Primary keys must not be NULL
85
Q
  • Describe composite primary keys
A
  • A composite primary key is used when multiple columns have to identify a row
  • denoted with parentheses: (ColumnA, ColumnB)
86
Q
  • What is the PRIMARY KEY constraint?
A
  • names the table’s primary key
  • Ensures that a column or group of columns is always unique and non-null
87
Q
  • Describe AUTO_INCREMENT
A

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

88
Q
  • Describe referential integrity
A

relational rule that requires foreign keys to either be fully NULL or match primary key values

89
Q
  • Describe the concept ‘fully NULL’
A

a foreign key in which all columns are null
- Consider the opposite, partially null, in a composite key context

90
Q
  • What are the 4 ways referential integrity can be violated?
A
  • A primary key is updated
  • A foreign key is updated
  • A row containing a primary key is deleted
  • A row containing a foreign key is inserted
91
Q
  • What are the 4 constraints usually used to maintain referential integrity?
A
  • RESTRICT→ rejects an insert, update, or delete that violates referential integrity
  • SET NULL→ sets invalid foreign keys to NULL
  • SET DEFUALT→ sets invalid foreign keys to the default foreign key value
  • CASCADE→ propagates primary key changes to foreign keys
    • Matching foreign key rows are deleted
92
Q
  • Describe RESTRICT
A

rejects an insert, update, or delete that violates referential integrity

93
Q
  • Describe SET NULL
A

sets invalid foreign keys to NULL

94
Q
  • Describe SET DEFAULT
A

sets invalid foreign keys to the default foreign key value

95
Q
  • Describe CASCADE
A

propagates primary key changes to foreign keys. Matching foreign key rows are deleted

96
Q
  • What does MySQL allow for maintaining referential integrity as far as inserting or updating foreign keys?
A

For foreign key inserts and updates MySQL supports only RESTRICT

97
Q
  • Describe ‘constraints’
A

rule that governs allowable values in a db
- If a statement would violate a constraint it is rejected

98
Q
  • What is a column constraint?
A
  • appears after column name and data type in CREATE TABLE statement
99
Q
  • What is a table constraint?
A
  • Appears in a statement other than CREATE TABLE and governs values in one or more columns
100
Q
  • What is the UNIQUE constraint?
A
  • Ensures that a values in a column or group of columns is unique
101
Q
  • Describe the CHECK constraint
A
  • Specifies an expression on one or more columns, and returns TRUE or FALSE
102
Q
  • When is the CHECK constraint considered a table constraint?
A

If the CHECK involves multiple columns, it is considered a table constraint and must be declared in a separate clause.

Data Management Foundations, D426 (5 decks)

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