Brainscape's Knowledge GenomeTM

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


See full index

Databases I > Exam > Flashcards

Exam Flashcards

1
Q

What is a Data Model?

A 
1. Mathematical representation of data.
 Examples: relational model = tables;
semistructured model = trees/graphs.
2. Operations on data.
3. Constraints.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is a Relation?

A

A Relation is a table with Attributes(column headers), Tuples(rows) and Relation name.

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

Relation Schema, Database Schema, Database

A 
Relation schema = relation name and
attribute list. Optionally: types of attributes.
Database schema = set of all relation
schemes in the database.
Database = collection of relations.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

A Key in Relations

A

Key = tuples cannot have

the same value in all key attributes.

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

Creating a Relation in SQL

A 
Simplest form is:
CREATE TABLE  (

);
To delete a relation:
DROP TABLE ;

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

Elements of Table Declarations

A

Most basic element: an attribute and its
type.
The most common types are:
INT or INTEGER (synonyms).
REAL or FLOAT (synonyms).
CHAR(n) = fixed-length string of n characters.
VARCHAR(n) = variable-length string of up to n characters.

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

Declaring Keys

A

An attribute or list of attributes may be
declared PRIMARY KEY or UNIQUE.
Either says that no two tuples of the
relation may agree in all the attribute(s)
on the list.

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

Declaring Single-Attribute Keys

A

Place PRIMARY KEY or UNIQUE after the

type in the declaration of the attribute.

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

Declaring Multi-attribute Keys

A

A key declaration can also be another
element in the list of elements of a CREATE TABLE statement.
This form is essential if the key consists
of more than one attribute.
May be used even for one-attribute keys.

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

PRIMARY KEY vs. UNIQUE

A
  1. There can be only one PRIMARY KEY for a relation, but several UNIQUE attributes.
  2. No attribute of a PRIMARY KEY can ever be NULL in any tuple. But attributes declared UNIQUE may have NULL’s, and there may be several tuples with NULL.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Graphs of Semi-structured Data

A
  1. Nodes = objects.
  2. Arc labels (properties of objects).
  3. Atomic values at leaf nodes (nodes with no arcs out).
  4. Flexibility: no restriction on:
    Labels out of a node.
    Number of successors with a given label.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What is an “Algebra”

A 
Mathematical system consisting of:
 Operands --- variables or values from
which new values can be constructed.
 Operators --- symbols denoting procedures
that construct new values from given
values.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is Relational Algebra?

A 
An algebra whose operands are
relations or variables that represent
relations.
Operators are designed to do the most
common things that we need to do with
relations in a database.
The result is an algebra that can be used
as a query language for relations.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Core Relational Algebra

A 
Union, intersection, and difference.
 Usual set operations, but both operands
must have the same relation schema.
Selection: picking certain rows.
Projection: picking certain columns.
Products and joins: compositions of
relations.
Renaming of relations and attributes.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Selection in RA

A

R1 := σC (R2)
1. C is a condition (as in “if” statements) that
refers to attributes of R2.
2. R1 is all those tuples of R2 that satisfy C.

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

Projection in RA

A

R1 := πL (R2)

  1. L is a list of attributes from the schema of R2.
  2. R1 is constructed by looking at each tuple of R2, extracting the attributes on list L, in the order specified, and creating from those components a tuple for R1.
  3. Eliminate duplicate tuples, if any.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Extended Projection in RA

A

Using the same πL operator, we allow the list L to contain arbitrary expressions involving attributes:

  1. Arithmetic on attributes, e.g., A+B->C.
  2. Duplicate occurrences of the same attribute.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Product in RA

A

R3 := R1 Χ R2
Pair each tuple t1 of R1 with each tuple t2 of R2.
Concatenation t1t2 is a tuple of R3.
Schema of R3 is the attributes of R1 and then R2, in
order.
But beware attribute A of the same name in R1 and R2:
use R1.A and R2.A.

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

Theta-Join in RA

A

R3 := R1 XC R2
Take the product R1 Χ R2.
Then apply σC to the result.
As for σ, C can be any boolean-valued condition.

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

Natural Join in RA

A

A useful join variant (natural join) connects two relations by:
Equating attributes of the same name
Projecting out one copy of each pair of
equated attributes

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

Renaming in RA

A

The ρ operator gives a new schema to a
relation.
R1 := ρR1(A1,…,An)(R2) makes R1 be a relation with
attributes A1,…,An and the same tuples as R2.

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

Precedence of relational operators

A
  1. [σ,π,ρ] (highest).
  2. [Χ,(Theta-Join)].
  3. ∩.
  4. [∪,—]
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Define a Bag

A 
A bag (or multiset ) is like a set, but an
element may appear more than once.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

Operations on Bags

A 
Selection applies to each tuple, so its
effect on bags is like its effect on sets.
Projection also applies to each tuple,
but as a bag operator, we do not
eliminate duplicates.
Products and joins are done on each
pair of tuples, so duplicates in bags
have no effect on how we operate.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

Bag Union

A

An element appears in the union of two
bags the sum of the number of times it
appears in each bag.

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

Bag Intersection

A

An element appears in the intersection
of two bags the minimum of the
number of times it appears in either

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

Bag Difference

A 
An element appears in the difference
A – B of bags as many times as it
appears in A, minus the number of
times it appears in B.
 But never less than 0 times
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

Bag Laws != Set Laws

A

The commutative law for
union (R∪S =S∪R) DOES hold for bags.
Set union is idempotent, meaning that S∪S = S.
However, for bags, if x appears n times in S, then it appears 2n times in S∪S.

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

Select-From-Where Statements

A

SELECT desired attributes
FROM one or more tables
WHERE condition about tuples of
the tables

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

Meaning of Single-Relation Query

A

Begin with the relation in the FROM clause.
Apply the selection indicated by the WHERE clause.
Apply the extended projection indicated by the SELECT clause.

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

Renaming Attributes

A

If you want the result to have different attribute names, use “AS ” to rename an attribute.

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

Patterns in SQL

A

A condition can compare a string to a pattern by:
LIKE or NOT LIKE
Pattern is a quoted string with % = “any string”; _ = “any character.”

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

Comparing NULL’s to Values

A

The logic of conditions in SQL is really 3-valued logic: TRUE, FALSE, UNKNOWN.
Comparing any value (including NULL itself) with NULL yields UNKNOWN.
A tuple is in a query answer iff the WHERE clause is TRUE (not FALSE or UNKNOWN).

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

Three-Valued Logic

A

To understand how AND, OR, and NOT work in 3-valued logic, think of TRUE = 1, FALSE = 0, and UNKNOWN = ½.
AND = MIN; OR = MAX, NOT(x) = 1-x.

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

Subqueries

A

A parenthesized SELECT-FROM-WHERE statement (subquery ) can be used as a value in a number of places, including FROM and WHERE clauses

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

The IN Operator

A

IN () is true if and only if the tuple is a member of the relation produced by the subquery.
Opposite: NOT IN ().
IN-expressions can appear in WHERE clauses.

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

The Exists Operator

A

EXISTS() is true if and only if the subquery result is not empty.

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

The Operator ANY`

A

x = ANY() is a boolean condition that is true iff x equals at least one tuple in the subquery result.
= could be any comparison operator.

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

The Operator ALL

A

x <> ALL() is true iff for every tuple t in the relation, x is not equal to t.
That is, x is not in the subquery result.
<> can be any comparison operator.

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

Union, Intersection, and Difference

A

Union, intersection, and difference of relations are expressed by the following forms, each involving subqueries:
() UNION ()
() INTERSECT ()
() EXCEPT ()

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

Controlling Duplicate Elimination

A

Force the result to be a set by SELECT DISTINCT . . .

Force the result to be a bag (i.e., don’t eliminate duplicates) by ALL, as in . . . UNION ALL . . .

42
Q

Products and Natural Joins in SQL

A

Natural join:
R NATURAL JOIN S;
Product:
R CROSS JOIN S;

43
Q

Theta Join in SQL

A

R JOIN S ON

44
Q

Duplicate Elimination in Ext. RA

A

R1 := δ(R2).

R1 consists of one copy of each tuple that appears in R2 one or more times.

45
Q

Sorting in Ext. RA

A

R1 := τL (R2).
L is a list of some of the attributes of R2.
R1 is the list of tuples of R2 sorted first on the value of the first attribute on L, then on the second attribute of L, and so on.
Break ties arbitrarily.
τ is the only operator whose result is neither a set nor a bag.

46
Q

Grouping Operator in Ext. RA

A

R1 := γL (R2). L is a list of elements that are either:
1. Individual (grouping ) attributes.
2. AGG(A ), where AGG is one of the aggregation
operators and A is an attribute.
An arrow and a new attribute name renames the component.
Group R according to all the grouping attributes on list L.
Within each group, compute AGG(A ) for each aggregation on list L.

47
Q

Outerjoin in Ext. RA

A

Suppose we join R ⋈c S.
A tuple of R that has no tuple of S with which it joins is said to be dangling.
Similarly for a tuple of S.
Outerjoin preserves dangling tuples by padding them NULL.
It is modified by:
Optional NATURAL in front of OUTER.
Optional ON after JOIN.
Optional LEFT, RIGHT, or FULL before OUTER.

48
Q

Aggregations in SQL

A

SUM, AVG, COUNT, MIN, and MAX can be applied to a column in a SELECT clause to produce that aggregation on the column.

49
Q

Grouping in SQL

A

The relation that results from the SELECT-FROM-WHERE is grouped according to the values of all those attributes, and any aggregation is applied only within each group

50
Q

HAVING Clauses

A

HAVING may follow a GROUP BY clause.

If so, the condition applies to each group, and groups not satisfying the condition are eliminated

51
Q

Kinds of Database Modifications

A

Three kinds of modifications:
Insert a tuple or tuples.
Delete a tuple or tuples.
Update the value(s) of an existing tuple or tuples

52
Q

Insertion in SQL

A

To insert a single tuple:
INSERT INTO
VALUES ( );
We may add to the relation name a list of attributes

53
Q

Adding Default Values

A

In a CREATE TABLE statement, we can follow an attribute by DEFAULT and a value.
When an inserted tuple has no value for that attribute, the default will be used.
We may insert the entire result of a query into a relation, using the form:
INSERT INTO
( );

54
Q

Deletion

A

To delete tuples satisfying a condition from some relation:
DELETE FROM
WHERE ;

55
Q

Updates

A

To change certain attributes in certain tuples of a relation:
UPDATE
SET
WHERE ;

56
Q

Constraints and Triggers

A

A constraint is a relationship among data elements that the DBMS is required to enforce.
Triggers are only executed when a specified condition occurs, e.g., insertion of a tuple.

57
Q

Kinds of Constraints

A 
Keys.
Foreign-key, or referential-integrity.
Value-based constraints.
Tuple-based constraints.
Assertions: any SQL boolean expression
58
Q

Foreign Keys

A

Values appearing in attributes of one relation must appear together in certain attributes of another relation.
Use keyword REFERENCES, either:
After an attribute (for one-attribute keys).
As an element of the schema:
FOREIGN KEY ()
REFERENCES ()
Referenced attributes must be declared PRIMARY KEY or UNIQUE.

59
Q

Enforcing Foreign-Key Constraints

A

If there is a foreign-key constraint from relation R to relation S, two violations are possible:
An insert or update to R introduces values not found
in S.
A deletion or update to S causes some tuples of R to
“dangle.”

60
Q

A deletion or update to Beers that removes a beer value found in some tuples of Sells can be handled in three ways. List them

A

Default : Reject the modification.
Cascade : Make the same changes in Sells.
Deleted beer: delete Sells tuple.
Updated beer: change value in Sells.
Set NULL : Change the beer to NULL

When we declare a foreign key, we may choose policies SET NULL or CASCADE independently for deletions and updates.
Follow the foreign-key declaration by:
ON [UPDATE, DELETE][SET NULL CASCADE]

61
Q

Attribute-Based Checks

A

Constraints on the value of a particular attribute.
Add CHECK() to the declaration for the attribute.
The condition may use the name of the attribute, but any other relation or attribute name must be in a subquery.
Attribute-based checks are performed only when a value for that attribute is inserted or updated

62
Q

Tuple-Based Checks

A

CHECK () may be added as a relation-schema element.
The condition may refer to any attribute of the relation.
But other attributes or relations require a subquery.
Checked on insert or update only

63
Q

Assertions

A

These are database-schema elements, like relations or views.
Defined by:
CREATE ASSERTION
CHECK ();
Condition may refer to any relation or attribute in the database schema.
In principle, we must check every assertion after every modification to any relation of the database.
A clever system can observe that only certain changes could cause a given assertion to be violated.

64
Q

Event-Condition-Action Rules (Triggers)

A

Event : typically a type of database modification, e.g., “insert on Sells.”
Condition : Any SQL boolean-valued expression.
Action : Any SQL statements.

65
Q

CREATE TRIGGER

A

CREATE TRIGGER
Or:
CREATE OR REPLACE TRIGGER

66
Q

Trigger Event

A

AFTER can be BEFORE.
Also, INSTEAD OF, if the relation is a view.
INSERT can be DELETE or UPDATE.
And UPDATE can be UPDATE . . . ON a particular
attribute.

67
Q

Trigger FOR EACH ROW

A

Triggers are either “row-level” or “statement-level.”
FOR EACH ROW indicates row-level; its absence indicates statement-level.
Row level triggers : execute once for each modified tuple.
Statement-level triggers : execute once for a SQL statement, regardless of how many tuples are modified

68
Q

Trigger REFERENCING

A

INSERT statements imply a new tuple (for row-level) or new table (for statement-level).
The “table” is the set of inserted tuples.
DELETE implies an old tuple or table.
UPDATE implies both.
Refer to these by
[NEW OLD][TUPLE TABLE] AS

69
Q

Triggering The Condition

A

Any boolean-valued condition.
Evaluated on the database as it would exist before or after the triggering event, depending on whether BEFORE or AFTER is used.
Access the new/old tuple/table through the names in the REFERENCING clause

70
Q

Triggering The Action

A

There can be more than one SQL statement in the action.

Surround by BEGIN . . . END if there is more than one

71
Q

Transactions

A

Transaction = process involving database queries and/or modification.
Normally with some strong properties regarding concurrency.
Formed in SQL from single statements or explicit programmer control.

72
Q

ACID Transactions

A

ACID transactions are:
Atomic : Whole transaction or none is done.
Consistent : Database constraints preserved.
Isolated : It appears to the user as if only one process executes at a time.
Durable : Effects of a process survive a crash

73
Q

COMMIT

A

The SQL statement COMMIT causes a transaction to complete.

It’s database modifications are now permanent in the database.

74
Q

ROLLBACK

A

The SQL statement ROLLBACK also causes the transaction to end, but by aborting.
No effects on the database.
Failures like division by 0 or a constraint violation can also cause rollback, even if the programmer does not request it.

75
Q

Isolation Levels

A

SQL defines four isolation levels = choices about what interactions are allowed by transactions that execute at about the same time.

76
Q

Choosing the Isolation Level

A 
Within a transaction, we can say:
SET TRANSACTION ISOLATION LEVEL X
	where X  =
    SERIALIZABLE
    REPEATABLE READ
    READ COMMITTED
    READ UNCOMMITTED
77
Q

Serializable Transactions

A

If Sally = (max)(min) and Joe = (del)(ins) are each transactions, and Sally runs with isolation level SERIALIZABLE, then she will see the database either before or after Joe runs, but not in the middle

78
Q

Read-Commited Transactions

A

If Sally runs with isolation level READ COMMITTED, then she can see only committed data, but not necessarily the same data each time

79
Q

Repeatable-Read Transactions

A

Requirement is like read-committed, plus: if data is read again, then everything seen the first time will be seen the second time. But the second and subsequent reads may see more tuples as well.

80
Q

Read Uncommitted

A

A transaction running under READ UNCOMMITTED can see data in the database, even if it was written by a transaction that has not committed (and may never).

81
Q

Views

A

A view is a relation defined in terms of stored tables (called base tables ) and other views.
Two kinds:
Virtual = not stored in the database; just a query for
constructing the relation.
Materialized = actually constructed and stored.

82
Q

Declaring Views

A

Declare by:
CREATE [MATERIALIZED] VIEW AS ;
Default is virtual.

83
Q

Indexes

A

Index = data structure used to speed access to tuples of a relation, given values of one or more attributes.
Could be a hash table, but in a DBMS it is always a balanced search tree with giant nodes (a full disk page) called a B-tree.

84
Q

Declaring Indexes

A

Typical syntax:
CREATE INDEX BeerInd ON Beers(manf);
CREATE INDEX SellInd ON Sells(bar, beer);

85
Q

Entity Sets

A

Entity = “thing” or object.
Entity set = collection of similar entities.
Attribute = property of (the entities of) an
entity set.

86
Q

Relationships in ER

A

A relationship connects two or more
entity sets.
It is represented by a diamond, with
lines to each of the entity sets involved

87
Q

Relationship Set

A 
The current “value” of an entity set is
the set of entities that belong to it.
The “value” of a relationship is a
relationship set, a set of tuples with one
component for each related entity set
88
Q

Many-Many Relationships

A

In a many-many relationship, an entity
of either set can be connected to many
entities of the other set

89
Q

Many-One Relationships

A

Some binary relationships are many-one from one entity set to another.
Each entity of the first set is connected to at most one entity of the second set.
But an entity of the second set can be connected to zero, one, or many entities of the first set

90
Q

One-One Relationships

A

In a one-one relationship, each entity of
either entity set is related to at most one
entity of the other set

91
Q

Representing “Multiplicity”

A 
Show a many-one relationship by an
arrow entering the “one” side.
Show a one-one relationship by arrows
entering both entity sets.
Rounded arrow = “exactly one,” i.e.,
each entity of the first set is related to
exactly one entity of the target set.
92
Q

Attributes on Relationships

A

Sometimes it is useful to attach an
attribute to a relationship.
Think of this attribute as a property of
tuples in the relationship set.

93
Q

Roles in ER

A 
Sometimes an entity set appears more
than once in a relationship.
Label the edges between the
relationship and the entity set with
names called roles
94
Q

Subclasses

A 
Subclass = special case = fewer entities = more properties.
Assume subclasses form a tree (no multiple inheritance).
Isa triangles indicate the subclass relationship (Point to the superclass).
E/R entities have representatives in all subclasses to
which they belong
95
Q

Keys

A

A key is a set of attributes for one
entity set such that no two entities in
this set agree on all the attributes of
the key. It is allowed for two entities to agree on
some, but not all, of the key attributes.
We must designate a key for every
entity set

96
Q

Weak Entity Sets

A

Entity set E is said to be weak if in order to identify entities of E uniquely, we need to follow one or more many-one relationships from E and include the key of the related entities from the connected entity sets.
A weak entity set has one or more many-one relationships to other (supporting) entity sets

97
Q

Decomposition

A

d={R1,…,Rk} decomposition, if R1U…URk=R

98
Q

Functional Dependencies

A

X ->Y is an assertion about a relation R that
whenever two tuples of R agree on all the
attributes of X, then they must also agree on all
attributes in set Y

99
Q

Keys of Relations FD

A

K is a superkey for relation R if K functionally determines all of R.
K is a key for R if K is a superkey, but no proper subset of K is a superkey.

100
Q

Armstrong axioms

A

A1 (reflexivity or trivial fd): if Y subset of X then X->Y.
A2 (augmentation): if X->Y then XZ->YZ.
A3 (tranzitivity): if X->Y and Y->Z then X->Z

101
Q

More rules about functional dependencies

A 
Splitting (decomposition) rule
 X->Y and Z->Y then X->Z.
Combining (union) rule
 X->Y and X->Z then X->YZ.
Pseudotranzitivity
 X->Y and WY->Z then XW->Z.
102
Q

Closure of set of attributes

A

X+(F):={A | F|-X->A}
The closure of X under the FD’s in S is the set of
attributes A such that every relation that satisfies
all the FD’s in set S also satisfies XA, that is
XA follows from the FD’s of S.

Databases I (1 decks)

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