Exam 2 Flashcards

Question

Null values

Answer 1

Null can mean value unknown, not available, or does not apply We cannot compare null values. For instance, 2 customers with null addresses do not have the same address! It’s best to avoid null values as much as possible in database design

Answer 2

Assertion is a predicate expressing a condition we wish the database to always satisfy Predicate The values in each tuple are values that satisfy the predicate

Answer 3

The only true facts in the universe are those present within the extension of the relation

Answer 4

A relation schema R of degree n is denoted by R(A 1 , A 2 , ... , A n ). The uppercase letters Q, R, S denote relation names. The lowercase letters q, r, s denote relation states. The letters t, u, v denote tuples.

Answer 5

Restrictions on values in the database state Derived from rules in the miniworld

Answer 6

Constraints that are inherent in the data model Also called implicit constraints Example: can’t have duplicate tuples

Answer 7

Constraints directly expressed in the schema Also called explicit constraints Domain constraints, key constraints, constraints on ulls, entity integrity, etc

Answer 8

Constraints that can’t be expressed in schema but is enforced by the application programs Also called semantic constraints or business rules

Answer 9

Within each tuple, the value of each attribute must be an atomic value from the domain

Answer 10

A key that can possibly be a primary key in a table Each attribute for a row is unique

Answer 11

The key chosen to maintain uniqueness in a table A candidate key that is used to identify tuples in a relation

Answer 12

This is basically a combination of multiple keys that form a primary key Specifies a uniqueness constraint that no two distinct tuples in any state r of R can have the same value for SK Similar to a primary key? Can be used to uniquely identify each tuple

Answer 13

Other candidate keys that are not chosen to be a primary key

Answer 14

A relational database schema S is a set of relation schema and a set of integrity constraints IC The tables and the columns

Answer 15

The rows of a table Also called snapshot or instance

Answer 16

A database that does not obey all its integrity constraints is considered not valid Otherwise it is called a valid state Integrity constraints are expected to hold for every valid database state

Answer 17

No primary key value can be NULL This means we can’t identify some tuples

Answer 18

Used to maintain the consistency among tuples in two relations For example, a department number in the EMPLOYEE tuple must match an actual department number in the DEPARTMENT table

Answer 19

If a relational schema R1 is a foreign key that references R2, it must have the same domain and must actually refer to a real value

Answer 20

R1 is the referencing relation and R2 is the referenced relation If this holds, the referential integrity constraint holds

Answer 21

Not part of DDL For example: age of child should not exceed age of parent

Answer 22

How to specify more general constraints

Answer 23

Can specify some of the constraints in SQL However, more common to check in the actual application code itself

Answer 24

Define constraints on a valid state of the database

Answer 25

Deal with changes to the database Typically enforced by application programs

Answer 26

The answer to a user’s query

Answer 27

3 basic mechanics: insert, delete, update (modify)

Answer 28

A new tuple t gets inserted into relation R Can potentially violate constraints Violate domain constraint Attribute value isn’t in domain Violate key constraints New tuple t is already in the relation Violate entity integrity If any part of the primary key is NULL Violate referential integrity If the foreign key is bad The default action is to reject an insert if it violates something

Answer 29

Removing a tuple Can only violate referential integrity if it’s being referenced from other tuples in the database Several ways to fix this: Restrict Reject the deletion Cascade Propagate the deletion by deleting tuples that reference the tuple being deleted Set null / set default Modify the referencing attribute instead Can cause a violation if part of primary key

Answer 30

Change value or one or more attributes in a tuple Only need to check if data type and domain are correct Modifying a primary key can cause problems similar to Delete

Answer 31

Executing a database operation Can include any number of retrieval operations or update operations

Answer 32

Higher level declarative language for specifying relational queries No order of operations to specify how to retrieve the query result

Answer 33

SELECT and PROJECT operate on single relations

Answer 34

Operate on two tables by combining related tuples based on join conditions

Answer 35

Conditions that can summarize data from the tables

Answer 36

Choose a subset of the tuples from a relation that satisfies a selection condition σ Salary \> 30000 ( EMPLOYEE ) σ (sigma) is used to denote the SELECT operator Each select is unary applying to a single relation and each tuple individually Degree is the number of attributes is the same as R for the relation Selectivity Fraction of tuples selected by a selection condition

Answer 37

Selects certain columns from the table and discards other columns π Lname, Fname, Salary ( EMPLOYEE ) π (pi) is the symbol used to represent the PROJECT operation Degree is equal to the number of attributes in the list Project operation removes any duplicate tuples (known as duplicate elimination) since the result is a set (if duplicates weren’t removed it would be a multiset or bag) Similar to SELECT DISTINCT in SQL

Answer 38

In-line expression for relational algebra π Fname, Lname, Salary (σ Dno = 5 ( EMPLOYEE )) assignment operation DEP5\_EMPS ← σ Dno = 5 ( EMPLOYEE ) RESULT ← π Fname, Lname, Salary ( DEP5\_EMPS ) Rename TEMP ← σ Dno = 5 ( EMPLOYEE ) R ( First\_name, Last\_name, Salary ) ← π Fname, Lname, Salary ( TEMP )

Answer 39

ρ S ( B1, B2, ... , Bn ) ( R ) the symbol ρ (rho) is used to denote the RENAME operator

Answer 40

Union, intersection, set difference

Answer 41

R ∪ S Includes tuples that are in R or S or both

Answer 42

R ∩ S Includes tuples that are in both R and S

Answer 43

R – S Includes tuples that are in R but not in S Not commutative

Answer 44

Denoted by × Concatenates all possible combination of tuples in the two relations (tables)

Answer 45

JOIN operation, denoted by ⋈ Used to combine related tuples from two relations Cartesian product followed by a select Where two id numbers match

Answer 46

A join with an = is an equijoin For equijoins we have extra attributes with identical values Natural join is basically an EQUIJOIN followed by the removal of superfluous attributes Natural join is denoted by \* Join attribute (which attribute to join on) Join selectivity: expected size of join result divided by maximum size These are types of Inner joins

Answer 47

The DIVISION operation, denoted by ÷ Retrieve the names of employees who work on all the projects that “john smith” works on Can be expressed as sequence: T1 ← π Y (R) T2 ← π Y ((S × T1) – R) T ← T1 – T2 Confusing Produces a relation R(X) that includes all tuples t[X] in R 1 (Z) that appear in R 1 in combination with every tuple from R 2 (Y), where Z = X ∪ Y.

Answer 48

Data structure that corresponds to a relational algebra expression The result for each step needs to be done before the next one can execute

Answer 49

Allows functions of attributes to be included in the projection list For example, tax \* 0.5

Answer 50

Sum, maximum, minimum, count F in cursive is used

Answer 51

Joining on the attributes of the same type

Answer 52

Used to keep leftover attributes in R or S or both after joining Left outer join ⟕ Keeps every tuple in first relation Right outer join ⟖ Keeps every tuple in second relation Full Outer join ⟗ Keeps every tuple in both relations

Answer 53

There is no order given how to evaluate a query This is a nonprocedural language Expressive power is identical SQL is based on tuple relational calculus

Answer 54

{t | COND(t)} T is a tuple variable and COND(t) is a conditional expression that’s either true or false {t | EMPLOYEE(t) AND t.Salary \> 50000 } {t.Fname, t.Lname | EMPLOYEE(t) AND t.Salary \> 50000 } Range relation We need to specify a range relation R of t, otherwise it will pick tuples from everything in the universe Condition A condition to select a particular combination of tuples. Requested attributes A set of attributes to be retrieved

Answer 55

{t 1 .A j , t 2 .A k , ... , t n .A m | COND (t 1 , t 2 , ..., t n , t n+1 , t n+2 , ..., t n+m )} COND is condition or formula Formula is made up of atoms Atoms can be A relation and tuple variable A comparison operator between two attributes A comparison between an attribute and a regular number Truth value: a relation that yields true or false Every atom is a formula

Answer 56

Universal quantifier ∀ Existential quantifier ∃

Answer 57

A tuple variable in F that is an atom is free in F The free variables [hereafter abbreviated to FV] in a statement stand for objects that the statement says something about...The fact that you can plug in different values for a free variable means that it is free to stand for anything. Bound variables [hereafter abbreviated to BV], on the other hand, are simply letters that are used as a convenience to help express an idea and should not be thought of as standing for any particular object. A bound variable can always be replaced by a new variable without changing the meaning of the statement, and often the statement can be rephrased so that the bound variables are eliminated altogether.

Answer 58

There exists some tuple that makes F true

Answer 59

For all situations To make the quantified formula TRUE , the inner formula must be TRUE for all tuples in the universe.

Answer 60

Don’t involve complex quantification

Answer 61

Graph of how the query is structured Represented by relation nodes Constant nodes

Answer 62

Guaranteed to yield a finite number of tuples If not, it is unsafe {t | NOT (EMPLOYEE(t)) } is unsafe

Answer 63

While SQL was being developed by IBM Research, another language QBE was being developed based on domain relational calculus Instead of variables ranging over tuples, the variables range over single values from domains of attributes {x 1 , x 2 , ..., x n | COND (x 1 , x 2 , ..., x n , x n+1 , x n+2 , ..., x n+m )}

Answer 64

Also known as data model mapping How to create a relational schema from an EER diagram

Answer 65

For each entity type, create a relation R that has all the simple attributes of E. Include only the simple component attributes of a composite attribute Entities created from this are called entity relations because each tuple is an instance

Answer 66

For each weak entity type W, create a relation R and include all simple attributes of W as attributes of R Add a key to point to the parent attribute

Answer 67

3 approaches 1: foreign key approach Choose 1 relation and include it as a foreign key of the other 2: merge relation approach Merge the two entity types into a single relation 3: cross-reference / relationship relation approach Set up a third relation for cross referencing the primary keys of the two relations S and T

Answer 68

Two approaches: 1: foreign key approach Identify the relation S that specifies the participating entity type at the N side of the relationship. Include a foreign key for the other relation 2: relationship relation approach Create a cross reference table including primary keys and foreign keys to tie

Answer 69

The only option here is to use a relationship relation (cross reference) option

Answer 70

For each multivalued attribute A, create a new relation R Include foreign key and the name in the new table. The primary key can be listed multiple times

Answer 71

Use the relationship relation (cross reference) option For each n-ary relationship where n \> 2, create a new cross reference table S to represent the relation. Add foreign keys to tie things together

Answer 72

Two main options: Map the specialization into a single table Map into multiple tables

Answer 73

Multiple relations: superclass and subclass Multiple relations: subclass relations only Single relation with one TYPE attribute Single relation with multiple TYPE attributes

Answer 74

Must all have the same key

Answer 75

Add a new surrogate key to correspond to the union type

Answer 76

The identifying attribute, A, becomes the primary key of the ET table. All the attributes become keys in the table

Answer 77

Map each part of the composite attribute (C is composed of D and E, so only map D and E). The relational model is flat.

Answer 78

ET table with attribute A for primary key ET-F table with A and F as part of the primary key. BOTH ARE UNDERLINED!

Answer 79

ET1 with primary key A and foreign key B ET2 with primary key B OR ET1 with primary key A ET2 with primary key B and foreign key B

Answer 80

If ET2 has a mandatory link to relationship R: ET1 with primary key A ET2 with primary key B and foreign key B since B can never be NULL in ET2

Answer 81

If ET2 is on the many side: ET1 with primary key A ET2 with primary key B and foreign key B

Answer 82

Add new table for R with A and B underlined together as foreign keys as part of the primary key to tie everything together.

Answer 83

If Et2 is the weak attribute: ET 1 table has A as the primary key ET2 has a table with A and B underlined together as the primary key

Answer 84

Two tables for each of the two types. ET1 has all the attributes specific to ET1, and all the attributes in its parent type ET ET2 has all the attributes specific to ET2, and all the attributes in its parent type ET

Answer 85

For parent class: ET: Primary Key A, B ET1: Foreign Key A, C ET2: Foreign Key A, D

Answer 86

For parent class: ET: Primary Key A, B ET1: Foreign Key A, C ET2: Foreign Key A, D

Answer 87

For parent class: ET: Primary Key A, B ET1: Foreign Key A, C ET2: Foreign Key A, D

Answer 88

Make new primary key ET-ID for parent type with key B Table for ET1: Primary Key C with ET-ID foreign key Table for ET2: Primary Key D with ET-ID foreign key

Answer 89

natural join

Answer 90

Finding rows

Answer 91

Finding columns

Answer 92

o looking thing

Answer 93

intersection

Answer 94

Set difference in one set but not the other

Answer 95

theta works with less than and greater than natural join only works with =

Answer 96

theta join

Answer 97

inner join doesn't add extra values

Answer 98

preserve left operand tuples even if it doesn't match the join conditions (leave as NULL)

Answer 99

combines each combination of tuples!

Answer 100

For A divided by B Return rows of A that match all of B must match up with every single value in divisor

Answer 101

∈ t∈R means that t is a tuple of relation R

Answer 102

range expression, attribute, comparing two constants

Answer 103

an atom is a predicate

Answer 104

{r | r ∈ Regular User)

Answer 105

{r.Email, r.BirthYear, r.Sex | r ∈ RegularUser and r.HomeTown = 'Atlanta')}

Answer 106

{s.City | there exists(r ∈ RegularUser)(s.City = r.CurrentCity) or there exists (t ∈ RegularUser)(s.City = t.HomeTown)

Answer 107

instead of a series of operations, you're describing the result (there exists something that fulfills these rules)

Answer 108

{s.City | there exists(r ∈ RegularUser)(s.City = r.CurrentCity) and there exists (t ∈ RegularUser)(s.City = t.HomeTown)

Answer 109

r.Year = s.Year and t.Email = r.Email and t.Year = r.Year r ranges over regular user s ranges over major 60s events t is output

Answer 110

{r, s | r ranges over regular user and s ranges over user interests} remember this creates a combination of all combinations in both tables {t | ∃ p ∈ r ∃ q ∈ s (t[A] = p[A] ∧ t[B] = p[B] ∧ t[C] = q[C] ∧ t[D] = q[D])}

Answer 111

find email for all users with at least all of the interests of user 1 written manually just like cartesian product there must be a tuple that matches all of these other tuples {t | ∃ p ∈ r ∀q ∈ s (p[B] = q[B] ⇒ t[A] = p[A])}