Data Management Foundations, D426 > Relational DB > Flashcards

Relational DB Flashcards

(102 cards)

Study These 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
- Describe the statement `CREATE DATABASE {database name}`
creates a new db
26
- Describe the statement `DROP DATABASE {database name}`
deletes a db, including all tables
27
- Describe the keyword SHOW
provides information about database and its contents
28
- Describe some commonly used SHOW statements
- 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
29
- In relational databases, how many values can exist in one cell?
1
30
- Are duplicate column names allowed?
no
31
- Are duplicate rows allowed?
no
32
- How does row order affect query status?
- No row order. The organization of the rows never affects query results **data independence**
33
Define data independence
- The result of a database query is not affected by the physical organization of data on storage devices
34
- Describe the Integer data type
- **Integer** data 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.
35
- Describe the decimal data type
- **Decimal** data 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.
36
- Describe the Character data type
- **Character** data 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.
37
- Describe the Date and time data types
- **Date and time** data 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.
38
- Describe the Binary data type
- **Binary** data 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.
39
- Describe the Spatial data type
- **Spatial** data 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.
40
- Describe the Document data type
- **Document**  data types contain textual data in a structured format such as XML or JSON.
41
- Describe the MySQL datatype TINYINT
1 byte Signed range: -128 to 127 Unsigned range: 0 to 255
42
- Describe the MySQL datatype SMALLINT
2 bytes Signed range: -32,768 to 32,767 Unsigned range: 0 to 65,535
43
- Describe the MySQL datatype MEDIUMINT
3 bytes Signed range: -8,388,608 to 8,388,607 Unsigned range: 0 to 16,777,215
44
- Describe the MySQL datatype INTEGER or INT
4 bytes Signed range: -2,147,483,648 to 2,147,483,647 Unsigned range: 0 to 4,294,967,295
45
- Describe the MySQL datatype DECIMAL(M,D)
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
- Describe the MySQL datatype FLOAT
4 bytes Approximate decimal numbers with range: -3.4E+38 to 3.4E+38
47
- Describe the MySQL datatype DOUBLE
8 bytes Approximate decimal numbers with range: -1.8E+308 to 1.8E+308
48
- Describe the MySQL datatype DATE
3 bytes Format: YYYY-MM-DD. Range: '1000-01-01' to '9999-12-31'
49
- Describe the MySQL datatype TIME
3 bytes Format: hh:mm:ss
50
- Describe the MySQL datatype DATETIME
5 bytes Format: YYYY-MM-DD hh:mm:ss. Range: '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
51
- Describe the MySQL datatype CHAR(N)
N bytes Fixed-length string of length N; 0 ≤ N ≤ 255
52
- Describe the MySQL datatype VARCHAR(N)
Length of characters + 1 bytes Variable-length string with maximum N characters; 0 ≤ N ≤ 65,535
53
- Describe the MySQL datatype TEXT
Length of characters + 2 bytes Variable-length string with maximum 65,535 characters
54
- What is an operator?
A symbol that computes a value from one or more other values called **operands**
55
- What is an operand?
56
- Describe an arithmetic operator
operators compute numeric values from numeric operands
57
- Describe a comparison operator
compute logical values TRUE and FALSE. Operands can be numeric, character, and other data types.
58
- Describe a logical operator
operators compute logical values from logical operands
59
- Describe operator precedence
is the order in which an expression is operated
60
- Describe the logical operator AND
- Returns TRUE when only both values are TRUE
61
- Describe the logical operator OR
- Returns FALSE when only both values are FALSE
62
- Describe the logical operator NOT
- Reverses a logical value
63
- Describe NULL
- Unknown or inapplicable data
64
- Describe NOT NULL
- A constraint added to columns which prevents a NULL value being added as data
65
- How do you return a row with a value of NULL?
- WHERE ___ IS NULL. ‘IS NULL’
66
- Describe MySQL truth table
67
- In MySQL how does x=TRUE AND y=NULL evaluate?
null
68
- In MySQL how does x=NULL AND y=TRUE evaluate?
null
69
- In MySQL how does x=TRUE OR y=NULL evaluate?
true
70
- In MySQL how does x=NULL OR y=TRUE evaluate?
true
71
- In MySQL how does x=FALSE OR y=NULL evaluate?
null
72
- In MySQL how does x=NULL OR y=FALSE evaluate?
null
73
- In MySQL how does x=FALSE AND y=NULL evaluate?
false
74
- In MySQL how does x=NULL AND y=FALSE evaluate?
false
75
- In MySQL how does x=NULL AND y=NULL evaluate?
null
76
- In MySQL how does x=NULL OR y=NULL evaluate?
null
77
- Describe the INSERT statement
- 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
- Describe the INTO keyword
- `INSERT INTO` clause names the table and columns where data is to be added. - Note that `INTO` is optional
79
- Describe DEFAULT clause
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
- Describe the UPDATE statement
- `UPDATE` modifies existing rows
81
- What clause is used with an UPDATE statement
the `SET` clause is used to specify the new value
82
- Describe the WHERE clause
Specifies a condition
83
- Describe a primary key
- A column or group of columns used to identify a row - Usually the tables first column, but the position isn’t significant
84
- Describe the characteristics of a primary key
- **Primary keys must be unique** - **Primary keys must not be NULL**
85
- Describe composite primary keys
- A composite primary key is used when multiple columns have to identify a row - denoted with parentheses: `(ColumnA, ColumnB)`
86
- What is the PRIMARY KEY constraint?
- names the table’s primary key - Ensures that a column or group of columns is always unique and non-null
87
- Describe AUTO_INCREMENT
numeric column assigned an automatically incrementing value when a new row is inserted
88
- Describe referential integrity
relational rule that requires foreign keys to either be fully NULL or match primary key values
89
- Describe the concept ‘fully NULL’
a foreign key in which all columns are null - Consider the opposite, partially null, in a composite key context
90
- What are the 4 ways referential integrity can be violated?
- 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
- What are the 4 constraints usually used to maintain referential integrity?
- `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
- Describe `RESTRICT`
rejects an insert, update, or delete that violates referential integrity
93
- Describe `SET NULL`
sets invalid foreign keys to NULL
94
- Describe `SET DEFAULT`
sets invalid foreign keys to the default foreign key value
95
- Describe `CASCADE`
*propagates primary key changes to foreign keys*. Matching foreign key rows are deleted
96
- What does MySQL allow for maintaining referential integrity as far as inserting or updating foreign keys?
For foreign key inserts and updates MySQL supports only `RESTRICT`
97
- Describe ‘constraints’
rule that governs allowable values in a db - If a statement would violate a constraint it is rejected
98
- What is a column constraint?
- appears after column name and data type in `CREATE TABLE` statement
99
- What is a table constraint?
- Appears in a statement other than CREATE TABLE and governs values in one or more columns
100
- What is the UNIQUE constraint?
- Ensures that a values in a column or group of columns is unique
101
- Describe the CHECK constraint
- Specifies an expression on one or more columns, and returns TRUE or FALSE
102
- When is the CHECK constraint considered a table constraint?
If the `CHECK` involves multiple columns, it is considered a table constraint and must be declared in a separate clause.
Data Management Foundations, D426
flashcards
Decks in class (5)
# Cards
Brainscape helps you reach your goals faster, through stronger study habits.
© 2026 Bold Learning Solutions. Terms and Conditions