Exam 2

Question 1

Keys

Answer 1

a group of one or more attributes which uniquely defines a row

Question 2

Surrogate keys

Answer 2

: a DBMS-supplied identifier of each row of a table.

Question 3

Foreign keys

Answer 3

is an attribute (or collection of attribute) in one table that uniquely identifies a row of another table.

Question 4

triggers

Answer 4

modules of code that are invoked by the DBMS when specific events occur.

Question 5

cascading updates

Answer 5

propagating a change from the parent’s primary key to the children’s foreign key.

Question 6

cascading deletes

Answer 6

deleting the children along with the parent.

Question 7

how many relationships are needed and which keys go where for an id-dependent weak entity relationship

Answer 7

an entity whose identifier includes the identifier of another entity
two relations
one parent, one child
key of child is combination of parent key plus its own key
Used for multivalued attributes, archetype/version, and associations

Question 8

how many relationships are needed and which keys go where for an 1:1 has-a entity relationship

Answer 8

each entity gets its own relation
the key of one is a non-key FK in the other
doesn’t matter which
if 1:1 and mandatory both ways, then they should probably be combined into one relation.

Question 9

how many relationships are needed and which keys go where for an 1:M has-a entity relationship

Answer 9

each entity gets its own relation
key of parent must be FK in the child
usually, but not always, not part of key
child points to parent

Question 10

how many relationships are needed and which keys go where for an M:N has-a weak entity relationship

Answer 10

three relations must be used
one for each original entity
one for “intersection” (assoc) relation
key to intersection will contain, at least, the keys of both parents
always a composite key
often contains additional attributes, for uniqueness
Always ID-dependent

Question 11

how many relationships are needed and which keys go where for an recursive 1:1, 1:M, and M:N entity relationship

Answer 11

same as non-recursive has-a type relationships
you will have multiple columns with the same syntactic content but they will have different semantics
some rows are parent rows, some are child, some both, some neither

Question 12

how many relationships are needed and which keys go where for an is-a entity relationship

Answer 12

one relation for supertype

one relation for each subtype

Question 13

Define a BOMP relationship

Answer 13

Bill of Materials Processing is a class M:N recursive pattern.

Question 14

what is the structure of a BOMP relationship

Answer 14

\_\_\_\_\_\_\_\_\_
|PartName|
|OtherData|>-------|
        V                |
         |\_\_\_\_\_\_\_\_ |

Question 15

define relational algebra DML

Answer 15

procedural, complex, not common

Question 16

define relational calculus DML

Answer 16

nonprocedural, theoretical, not used

Question 17

define transform oriented DML

Answer 17

somewhat procedural, somewhat nonprocedural
simple, most common
SEQUEL, SQL, etc

Question 18

define the graphical DML

Answer 18

QBE/QBF

simple, limited

Question 19

define union operation

Answer 19

adding the tuples from one relation to those of a second relation to produce a third relation.

Question 20

define the difference operation

Answer 20

a third relation containing tuples that occur in the first relation but not in the third.

Question 21

define the intersection operation

Answer 21

a third relation of the tuples that appear in both, the first and second relation.

Question 22

define theproduct operation

Answer 22

concatenation of every tuple of one relation with every tuple of the second relation.

Question 23

define the project operation

Answer 23

an operation that selects specified attributes from a relation. (picks columns)

Question 24

define the select operation

Answer 24

identifies tuples to be included in the new relation. (picks rows)

Question 25

define the equijoin operation

Answer 25

cross + select

Question 26

define the natural join operation

Answer 26

project eliminates duplicate column

Question 27

define the inner join operation

Answer 27

must match in both tables

Question 28

define the outer join left and right operations

Answer 28

-left/right: only needed in one table

Question 29

define union compatibility

Answer 29

corresponding columns from same domains

Question 30

what criteria is need for the joins

Answer 30

Joins restore what normalization destroys.

Question 31

give a background on SQL

Answer 31

Structured Query Language (SQL) is a data sublanguage that has constructs for defining and processing a database

Question 32

explain SEQUEL

Answer 32

SQL: was developed by IBM in late 1970s

Question 33

explain SQL-92

Answer 33

was endorsed as a national standard by ANSI in 1992

Question 34

explain SQL3

Answer 34

incorporates some object-oriented concepts but has not gained acceptance in industry

Question 35

memorize the chart

Answer 35

``` OPERATION || METADATA || DATA create || create || insert change || alter || update delete || drop || delete display || show || select ```

Question 36

SELECT statements

Answer 36

can be used to obtain values of specific columns, specific rows, or both

Question 37

Select clause

Answer 37

specifies which columns are to be listed in the query results

Question 38

From clause

Answer 38

specifies which tables are to be used in the query

Question 39

Where clause

Answer 39

specifies which rows are to be listed in the query results

Question 40

Comparison operators

Answer 40

less than and greater than symbols

Question 41

LIKE

Answer 41

to specify a single or multiple unknown characters

Question 42

IN, NOT IN

Answer 42

indicate 'match any' and 'match all' sets of values

Question 43

DISTINCT

Answer 43

eliminate duplicates

Question 44

AND/OR

Answer 44

may be used for compound conditions

Question 45

BETWEEn

Answer 45

specify ranges of data values

Question 46

ORDER BY

Answer 46

sort the results generated

Question 47

GROUP BY

Answer 47

rows can be grouped according to common values

Question 48

COUNT

Answer 48

returns the number of values for a specified column name. Variations: COUNT(*) returns the number of records in a table COUNT(DISTINCT column_name) returns the number of distinct values

Question 49

SUM

Answer 49

Returns the total sum of a given numeric column. | Ex: SELECT SUM(quantity) AS TotalNumItemsOrdered FROM orders;

Question 50

AVG

Answer 50

Returns the average value of a given numeric column. | Ex: SELECT AVG(price) AS PriceAvg FROM products;

Question 51

MAX

Answer 51

Returns the largest value of a selected column. | Ex: SELECT MAX(price) AS HighestPrice FROM products;

Question 52

MIN

Answer 52

Returns the smallest value of a selected column. | Ex: SELECT MIN(price) AS LowesttPrice FROM products;