Brainscape's Knowledge GenomeTM

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


See full index

CS 442: Database Management Systems > Chapter 2: Relational Model > Flashcards

Chapter 2: Relational Model Flashcards

1
Q

What is the language of the Relational Model?

A

Relational Algebra

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

Why is it simple to access information within a relational model?

A

It is easy to access information because the Relational Model is built as a grid (table)

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

How do the operators act in Relational Algebra?

A

They act horizontally and vertically.

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

Should all attributes in a relation be atomic or composite and why?

A

Atomic so everything will be in first normal form. Having attributes being composite leads to issues later down the line.

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

What is null?

A

A special value that is an unknown value.

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

What is a relation (mathematical definition)?

A

A subset of the complete cartesian product of a set of n-tuples.

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

What is the domain of the attribute?

A

a set of allowed values for each attribute

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

What are attribute values (normally) required to be?

A

atomic

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

When is a domain atomic?

A

When all of its members are atomic

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

What value is a member of every domain?

A

null

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

What is a database (in context of relational models)?

A

It is a set of tables and relations between tables.

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

What is the purpose of foreign keys?

A

They serve as identifiers to the parent.

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

What must children do in relations and RDB?

A

The child must know who the parent is in RDB.

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

What is a foreign key?

A

The parent’s primary key that the child is holding.

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

What does the relational algebra select operator do?

A

It chooses rows/tuples

  • operates horizontally
  • SQL equivalent: WHERE
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What does the relational algebra project operator do?

A

Displays the contents of a column

  • operates vertically
  • SQL equivalent: SELECT
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What does the relational algebra union operator do?

A

brings tables together

  • operates horizontally
  • SQL equivalent: UNION
  • UNION ALL gives you a multi-set output
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What does the relational algebra set difference operator do?

A

Removes the items that relation 1 has in common with relation 2 and displays relation 1 after this

  • operates horizontally
  • relations must be compatible
  • SQL equivalent: EXCEPT, MINUS
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What does the relational algebra cartesian product operator do?

A

Joins two tables together

  • operates horizontally and vertically
  • SQL equivalent: , (a comma)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What does the relational algebra rename operator do?

A

Renames an attribute within a query

  • does not operate horizontally or vertically
  • SQL equivalent: AS
21
Q

What is a set operation?

A

An operation that only works on sets. They also automatically eliminate all duplicates in the output.

22
Q

What are the attributes that sets must have?

A
  1. uniqueness (no duplicates)

2. no order

23
Q

Do set operations produce duplicates?

A

no

24
Q

What makes relations compatible?

A
  • they have the same arity (number of attributes)

- attribute domains of both relations must be compatible

25
Q

What relational algebra operations are set operations?

A
  • cartesian-product
  • set difference
  • intersect
  • union
26
Q

What functions the same as a join operation?

A 
select on a cartesian product of two relations
in SQL this looks like
SELECT something
FROM r,s
WHERE r.value=s.value
27
Q

What does the rename operation do?

A

Renames the local instance of a specific value, the rename only occurs within the query

28
Q

What is the rename operation in SQL

A

AS

29
Q

What is set difference?

A

Everything that is common within two relations is removed.

30
Q

What is the symbol for set difference in relational algebra?

A

-

31
Q

What is the representation of set difference in SQL?

A

EXCEPT

32
Q

What is intersect in relational algebra and SQL?

A

Set operation that returns what is common between two sets.

  • relational algebra: mathematical symbol for intersect
  • sql: INTERSECT
33
Q

What is natural join?

A

A join operation that has two implicit conditions:

1) schema for the joined relations must share at least one attribute
2) the relations must share the same values in the common values

34
Q

What is returned by natural join?

A

r U s with all of the elements in r followed by all element in s w/o repeats
-this is done row by row

35
Q

How is natural join written in SQL

A

NATURAL JOIN

36
Q

How does the division operation work?

A

when there is a common column between two relations the following steps are done

1) When the denominator column is found within the numerator relation, remove the denominator column in the numerator relation
2) Collect the unique set of rows remaining in the numerator
3) Take the numerators remaining values and for each value in the unique numerator (result of step 2) go through original numerator (state before step 1) and match unique numerator values with the original numerator values, if the values within the divisor are all present within the original numerator (with none missing and no extra i.e. [Column A, Column B] -> (alpha, 1) (alpha, 2) in ogNumerator and denominator is [Column B] -> (1) (2)) return the unique numerator with that value.

37
Q

What is generalized projection?

A

It means you can project not only values but the results of functions as well.

38
Q

What are aggregate functions and operations?

A

Functions and operations that summarize what is within a set

39
Q

What are some common aggregate functions?

A 
avg (returns average of value)
min (returns minimum value)
max (returns maximum value)
sum (returns the sum of values)
count (returns the number of values, doesn't ignore null but rest do)
40
Q

What is an aggregate operation?

A

GROUP BY

41
Q

How is group by structured

A

groupByClause G aggregateFunction(table)

2 G 3(1)
-groupByClause is optional

42
Q

What is the group by clause if no group by clause is declared?

A

The entire table that the clause is being utilized on.

43
Q

What is outer join?

A

Returns the result of an inner join (natural join) as well as the values that have no match

  • non-matched values are in a tuple with null, i.e. (5, null, null, …)
  • can be broken down into left, right and full
44
Q

What is the difference between left, right, and full outer join?

A

left outer join = natural inner join + values from left table
right outer join = natural inner join + values from right table
full outer join = natural inner join + values from left table + values from right table

45
Q

How is null accounted from in boolean algebra?

A

Instead of defaulting to T and F, it can also be null

and/or T F null
T T/T F/T null/T
F F/T F/F null/F
null null/T null/F null/null

46
Q

What are the database modification operations?

A

DELETE (operates on rows)
INSERT (operates on rows)
UPDATE (operates on columns)

47
Q

What is the SQL for deletion?

A

DELETE
FROM table
WHERE f(x)

48
Q

What is the SQL for insertion?

A

INSERT
INTO table
VALUES (…, …., …)

49
Q

What is the SQL for update?

A 
UPDATE   table
SET           f(x)
WHERE     g(x)

CS 442: Database Management Systems (6 decks)

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