CIS275 - Chapter 2: Relational Databases

Question 1

A _____ is a conceptual framework for database software.

Answer 1

database model

Question 2

The _____ is not optimized for big data.

Answer 2

relational model

Question 3

Many non-relational databases have come to market since 2000, optimized for big data and are collectively called _____.

Answer 3

NoSQL databases

Question 4

NoSQL stands for _____ and encompasses a variety of database models.

Answer 4

‘Not only SQL’

Question 5

Answer 5

Question 6

The _____ is a database model based on mathematical principles, with three parts:

A data structure that prescribes how data is organized.

Operations that manipulate data structures.

Rules that govern valid relational data.

Answer 6

relational model

Question 7

A ___ is a collection of values, or elements, with no inherent order.

Answer 7

set

Sets are denoted with braces. Ex: {apple, banana, lemon} is the set containing three kinds of fruit. Since sets have no order, {apple, banana, lemon} is the same set as {lemon, banana, apple}.

Question 8

A _____ is a named set of possible database values, such as integers, dictionary words, or logical values TRUE and FALSE.

Answer 8

domain

Question 9

A _____ is a finite sequence of values, each drawn from a fixed domain.

Answer 9

tuple

Ex: (3, apple, TRUE) is a tuple drawn from domains (Integers, DictionaryWords, LogicalValues).

Question 10

A _____ is a named set of tuples, all drawn from the same sequence of domains.

Answer 10

relation

Ex: The relation below is named Grocery and contains three tuples.

Question 11

In the relational model, each tuple position is called an _____ and given a unique name.

Answer 11

attribute

Ex: In the Grocery relation, the first, second, and third positions might be named Quantity, FruitType, and OrganicCertification.

Question 12

Domain, tuple, relation, and attribute =

Answer 12

Data type, row, table, and column

Question 13

Answer 13

Question 14

The relational model stipulates a set of operations on tables, collectively called _____.

Answer 14

relational algebra

Like the relational data structure, relational operations are based on set theory.

Question 15

combines two tables with identical columns into one table.

Answer 15

Union

Question 16

removes all rows of one table from another table.

Answer 16

Difference

Question 17

eliminates one or more columns of a table.

Answer 17

Projection

Question 18

selects a subset of rows of a table.

Answer 18

Selection

Question 19

combines two tables with different columns into one table.

Answer 19

Join

Question 20

lists all possible combinations of rows of two tables.

Answer 20

Product

Question 21

Answer 21

Question 22

_____, also known as integrity rules, are logical constraints that ensure data is valid and conforms to business policy.

Answer 22

Relational rules

Question 23

_____ are relational rules that govern data in every relational database.

Answer 23

Structural rules

The relational model stipulates a number of structural rules, such as:

Unique primary key — all tables should have a column with no repeated values, called the primary key and used to identify individual rows.

Unique column names — different columns of the same table must have different names.

No duplicate rows — no two rows of the same table may be have identical values in all columns.

Question 24

_____ are relational rules specific to a particular database and application.

Answer 24

Business rules

Example business rules include:

Unique column values — in a particular column, values may not be repeated.

No missing values — in a particular column, all rows must have known values.

Delete cascade — when a row is deleted, automatically delete all related rows.

Question 25

Answer 25

Question 26

Answer 26

Question 27

A _____ is a collection of data organized as columns and rows.

Answer 27

table

A table must have at least one column and any number of rows. A table without rows is called an empty table.

Question 28

A _____ is a set of values of the same type. Each column has a name, different from other column names in the table.

Answer 28

column

Question 29

A ____ is a set of values, one for each column.

Answer 29

row

Question 30

A ____ is a single column of a single row. In relational databases, each ____ contains exactly one value.

(same word in both fields)

Answer 30

cell

Question 31

In addition to a name, each column has a_____, which defines the format of the values stored in each row.

Answer 31

data type

Question 32

Answer 32

Question 33

_____data types represent positive and negative integers. Several _____ data types exist, varying by the number of bytes allocated for each value.

(same word)

Answer 33

Integer

Common integer data types include INT, implemented as 4 bytes of storage, and SMALLINT, implemented as 2 bytes.

Question 34

_____data types represent numbers with fractional values.

Answer 34

Decimal

Decimal data types vary by number of digits after the decimal point and maximum size. Common decimal data types include FLOAT and DECIMAL.

Question 35

_____data types represent textual characters.

Answer 35

Character

Common character data types include CHAR, a fixed string of characters, and VARCHAR, a string of variable length up to a specified maximum size.

Question 36

_____data types represent date, time, or both.

Answer 36

Time

Some time data types include a time zone or specify a time interval. Some time data types represent an interval rather than a point in time. Common time data types include DATE, TIME, DATETIME, and TIMESTAMP.

Question 37

_____data types store data exactly as the data appears in memory or computer files, bit for bit.

Answer 37

Binary

The database manages binary data as a series of zeros and ones. Common binary data types include BLOB, BINARY, VARBINARY, and IMAGE.

Question 38

_____data types store geometric information, such as lines, polygons, and map coordinates.

Answer 38

Spatial

Examples include POLYGON, POINT, and GEOMETRY. Spatial data types are relatively new and consequently vary greatly across database systems.

Question 39

_____data types contain textual data in a structured format such as XML or JSON.

Answer 39

Document

Question 40

Data types

Relational databases support many different data types. Most data types fall into one of the following categories:

Answer 40

Question 41

Answer 41

Question 42

Answer 42

Question 43

Tables obey three rules:

Answer 43

1. Tables are normalized — exactly one value exists in each cell.
2. No duplicate column names — duplicate column names are not allowed in one table. However, the same column name can appear in different tables.
3. No duplicate rows — no two rows may have identical values in all columns.

Question 44

In addition to the three rules, relational databases obey the principle of _____, which states that rows and columns of a table have no inherent order.

Answer 44

data independence

Although values in rows and columns are stored sequentially on a storage device, such as a disk drive, the sequence is arbitrary and does not affect the results of a database query.

Question 45

Answer 45

Question 46

a special value that represents missing data.

Answer 46

Null value

NULL represents either ‘unknown’ or ‘inapplicable’.

NULL is not the same as zero for numeric data types or blanks for character data types.

Question 47

Answer 47

Question 48

a symbol that computes a value from one or more other values (called operands).

Answer 48

operator

Arithmetic operators, such as +, -, *, and /, compute numeric values from numeric operands.

Comparison operators, such as , and =, compute logical values TRUE or FALSE. Operands may be numeric, character, and other data types.

Logical operators, AND, OR, and NOT, compute logical values from logical operands.

Question 49

Answer 49

Question 50

arithmetic functions

returns the total of selected values.

returns the average of selected values.

returns the largest selected value.

returns the smallest selected value.

Answer 50

SUM

AVG

MAX

MIN

SUM, AVG, MIN, and MAX ignore NULL values.

Question 51

Answer 51

Question 52

Answer 52

Question 53

Answer 53

Question 54

Answer 54

Question 55

Answer 55

Question 56

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

Answer 56

primary key

Primary keys obey two rules:

Values must be unique within the column. This rule ensures that each value identifies at most one row.

Values may not be NULL. This rule ensures that each value identifies at least one row.

Question 57

A unique column that is not the primary key is called an:

Answer 57

alternate key

Question 58

Collectively, the primary key and all alternate keys of a table are called

Answer 58

candidate keys

Question 59

Answer 59

Question 60

A _____ key consists of a single column.

Answer 60

simple primary

Question 61

A _____ key consists of multiple columns.

Answer 61

composite primary key

Composite primary keys are denoted with parentheses. Ex: (ColumnA, ColumnB).

Composite primary keys obey three rules:

Column values, when grouped together, must be unique. Ex: The combination (2538, 1) is unique within (ID, Number).

Columns may not contain NULL.

Composite primary keys must be minimal.

Question 62

In a _____, all columns are necessary for uniqueness.

Answer 62

minimal primary key

Ex: (ID, Number, Relationship) is not minimal. Since (ID, Number) is unique, Relationship is unnecessary.

Question 63

Answer 63

Question 64

A _____ is a column or group of columns, that refers to a primary key.

Answer 64

foreign key

Foreign keys do not obey the same rules as primary keys:

Foreign key values may be repeated. Ex: Sales and Marketing have the same manager.

Foreign key values may be NULL. Ex: Technical support currently has no manager.

Non-NULL foreign key values must match some primary key value.

Question 65

Answer 65

Question 66

Answer 66

Question 67

Answer 67

Question 68

Answer 68

Question 69

Answer 69

Question 70

Answer 70

Question 71

Answer 71

Question 72

Referential integrity actions:

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

Answer 72

RESTRICT

Question 73

Referential integrity actions

sets invalid foreign keys to NULL.

Answer 73

SET NULL

Question 74

Referential integrity actions

sets invalid foreign keys to a default primary key value, specified in SQL.

Answer 74

SET DEFAULT

Question 75

Referential integrity actions

propagates primary key changes to foreign keys.

Answer 75

CASCADE

Question 76

Answer 76

Question 77

Answer 77

Question 78

Answer 78

Question 79

Answer 79

Question 80

Answer 80

Question 81

a SELECT statement that combines data from two tables, known as the left table and right table, into a single result.

Answer 81

join

The tables are combined by comparing columns from the left and right tables, usually with the = operator. The columns must have comparable data types.

Question 82

Answer 82

Question 83

An ____ selects only matching left and right table rows.

Answer 83

inner join

Question 84

A _____ selects all left table rows, but only matching right table rows.

Answer 84

left join

Question 85

A _____ selects all right table rows, but only matching left table rows.

Answer 85

right join

Question 86

A ____ selects all left and right table rows, regardless of match.

Answer 86

full join

Question 87

An _____ is any join that selects unmatched rows, including left, right, and full joins.

Answer 87

outer join

Question 88

Answer 88

Question 89

An _____ compares columns of two tables with the = operator. Most joins are _____ , including the examples above.

(same word)

Answer 89

equijoin

Question 90

A _____ compares columns with an operator other than =, such as .

Answer 90

non-equijoin

In the example below, a non-equijoin selects all buyers along with properties priced below the buyer’s maximum price.

Question 91

In a _____, a table is joined to itself.

Answer 91

self-join

A self-join can compare any columns of a table, as long as the columns have comparable data types. If a foreign key and the referenced primary key are in the same table, a self-join might compare key columns.

Question 92

An ____ is a temporary name assigned to a column or table.

Answer 92

alias

In the example below, A is the left table’s alias, and B is the right table’s alias. A.Name is the Name column of the left table, representing the employee. B.Name is the Name column of the right table, representing the employee’s manager. The result shows employees along with each employee’s manager.

Question 93

A _____ combines two tables without comparing columns.

Answer 93

cross-join

A cross-join has no WHERE or ON clause. As a result, all possible combinations of rows from both tables appear in the result.

In the example below, all configurations of iPhone models and memory appear, along with total price.

Question 94

Answer 94

Question 95

A _____ is a table name associated with a SELECT statement, called the _____.

Answer 95

view table

view query

Question 96

The _____ statement creates a view table and specifies the view name, query, and, optionally, column names.

Answer 96

CREATE VIEW

Question 97

Answer 97

Question 98

A table specified in the view query’s FROM clause is called a _____.

Answer 98

base table

Question 99

A _____ is a view for which data is stored at all times.

Answer 99

materialized view

Whenever a base table changes, the corresponding view tables can also change, so materialized views must be refreshed. To avoid the overhead of refreshing views, MySQL and many other databases do not support materialized views.

Question 100

Answer 100

Question 101

Advantages of views: protect sensitive data.

Explain

Answer 101

A table may contain sensitive data.

Ex: The Employee table contains compensation columns such as Salary and Bonus. A view can exclude sensitive columns but include all other columns. Authorizing users and programmers access to the view but not the underlying table protects the sensitive data.

Question 102

Advantages of views: save complex queries.

Explain

Answer 102

Complex or difficult SELECT statements can be saved as a view. Database users can reference the view without writing the SELECT statement.

Question 103

Advantages of views: Save optimized queries.

Explain

Answer 103

Often, the same result table can be generated with equivalent SELECT statements. Although the results of equivalent statements are the same, performance may vary. To ensure fast execution, the optimal statement can be saved as a view and distributed to database users.

Question 104

Answer 104

Question 105

When _____ is specified, the database rejects inserts and updates that do not satisfy the view query WHERE clause. Instead, the database generates an error message that explains the violation.

Answer 105

WITH CHECK OPTION

1. Two employees are in department 51, Sales.
2. CREATE VIEW specifies new names for SalesEmployee columns.
3. SalesEmployee view only includes employees in department 51.
4. The user query inserts a new employee in department 80 into the view table.
5. The new employee is inserted into Employee but is not in department 51 and does not appear in the view table.

WITH CHECK OPTION prevents inserts and updates that do not satisfy the view query WHERE clause.

Question 106

Answer 106

Question 107

In a database, a ___ is a pair of related values, and ___ is the repetition of a fact.

Answer 107

fact

redundancy

Redundancy causes database management problems. When a fact is updated or deleted, all copies must be changed, which makes queries slow and complex. If copies are not updated or deleted uniformly, the copies become inconsistent and the correct fact is uncertain.

1. The fact that passenger number 222 is named Elvira Yin is repeated.
2. The fact that first class fares board first is repeated.

Question 108

_____ are rules for designing tables with less redundancy.

Answer 108

Normal forms

Normal forms are numbered, first normal form through fifth normal form. An additional normal form, named Boyce-Codd normal form, is considered an improved version of third normal form. The six normal forms comprise a sequence, with each normal form allowing less redundancy than the prior normal form.

Question 109

Answer 109

Question 110

Column A ____ column B means each B value is related to at most one A value. For a table to be in _____, all non-key columns must depend on the primary key.

Answer 110

depends on

first normal form

In a relational database, all tables with a primary key are in first normal form, as illustrated in the animation below.

Question 111

Answer 111

Question 112

For a table to be in _____, all non-key columns must depend on the whole primary key.

Answer 112

second normal form

n other words, a non-key column cannot depend on part of a composite primary key. A table with a simple primary key is automatically in second normal form.

1. PassengerName depends on PassengerNumber. Dependence on part of the primary key causes repetition of (222, Elvira Yin).
2. Removing PassengerName from Booking eliminates redundancy. Booking is now in second normal form.
3. PassengerName moves to the Passenger table. The Passenger table has no redundancies, since PassengerName depends on the whole primary key.

Question 113

Answer 113

Question 114

Answer 114

Question 115

A _____ is a simple or composite column that is unique and minimal.

Answer 115

candidate key

Minimal means all columns are necessary for uniqueness. A table may have several candidate keys. The database designer designates one candidate key as the primary key.

Question 116

A _____ column is a column that is not contained in a candidate key.

Answer 116

non-key

Ex: The Employee table contains columns ID, SocialNumber, and Name:

• ID and SocialNumber are candidate keys, since both are unique and minimal.
• (ID, Name) is unique but not minimal, since Name is not necessary for uniqueness. Therefore (ID, Name) is not a candidate key.
• Name is a non-key column.

Question 117

A table is in _____ if, whenever a non-key column A depends on column B, then B is unique.

Answer 117

third normal form

In this definition, columns A and B may be simple or composite. Although B is unique, B is not necessarily minimal and therefore is not necessarily a candidate key.

The definition of third normal form states that all non-key dependencies are on a unique column. The definition generalizes the informal description, above, which states that all non-key dependencies are on the primary key.

The definition of third normal form applies to non-key columns only, which allows for occasional redundancy.

Question 118

A table is in _____ if, whenever column A depends on column B, then B is unique. This definition is identical to the definition of third normal form with the term ‘non-key’ removed.

Answer 118

Boyce-Codd normal form

Question 119

A _____ is any simple or composite column that is unique. Unlike candidate keys, superkeys are not necessarily minimal.

Answer 119

superkey

An alternative definition of Boyce-Codd normal form replaces unique with superkey: A table is in Boyce-Codd normal form if, whenever column A depends on column B, then B is a superkey. This definition is equivalent to the definition above.

Question 120

Answer 120