Main Flashcards

Question

What does union-compatible mean?

Answer 1

Two relations are union-compatible if: * They have the same number of attributes (they have the same arity) * The domain of each attribute in column order is the same in both relations (the i'th attribute of both R and S have the same domain)

Answer 2

**Projection:** The result is a relation of n columns obtained by removing the columns that are not specified.

Answer 3

**Selection:** Selects/filters rows of a table according to the selection predicate * Notation: σF * Selection predicate F consists of: * Logic operators: \/(or), /\(and), ¬(not) * Arithmetic comparison operators: \<, ≤, =, \>, ≥, ≠ * Attribute names of the argument relations or constants as operands

Answer 4

Rename: * Renaming relation R to S: _ρS(R) * Renaming attribute B to A: _ρA←B(R)

Answer 5

Cartesian product (aka. cross product) ## Footnote The cartesian product (R x S) between relations R and S consists of all possible combinations (|R| \* |S| pairs) of tuples from both relations. Attributes in the result are referenced as R.A or S.A to resolve ambiguity.

Answer 6

**Union:** The result of a union (R U S) between two relations R and S contains all tuples from both relations without duplicates. ## Footnote R and S have to be union-compatible.

Answer 7

**Difference:** The difference (R — S or R \ S) of two relations R and S removes all tuples from the first relation that are also contained in the second relation. ## Footnote R and S have to be union-compatible.

Answer 8

**Intersection:** The intersection (R ∩ S) of two relations R and S consists of a set of tuples that occur in both relations. ## Footnote R and S have to be union-compatible.

Answer 9

**Natural join:** The natural join combines two relations via **common attributes** (same names and domains) by combining only tuples with the same values for common attributes.

Answer 10

**Left outer join:** keep dangling tuples in the left operand relation

Answer 11

**Right outer join:** keep danging tuples in the right operand relation

Answer 12

**(Full) outer join:** keep dangling tuples of both operand relations

Answer 13

**Left semi join:** ⋉ **Right semi join:** ⋊ Find all tuples in a relation for which there are **matching tuples** in the other relation.

Answer 14

**Grouping:** tuples with the same attribute values (for a specific list of attributes) are grouped. An aggregate function is applied to each group (computing one value for each group). Typical aggregate functions: * count - number of tuples in a group * sum - sum of attribute values in a group * min, max, avg - minimum, maximum and average of attribute values in a group

Answer 15

Division ## Footnote Example: Find all wines that have been reviewed by both Parker and Clarke

Answer 16

To combine tuples from two relations where the combination condition is not simply the equality of shared attributes then it is convenient to have a more general form of join operator, which is the θ-join. You can use any binary operator in the set {\<, ≤, =, \>, ≥} in between the two variables, Example: price \< myMoney **or** price \< 800

Answer 17

A variable is free if: * It is not quantified by a universal quantifier * It is not quantified by an extential quantifier

Answer 18

A universal quantification is a type of quantifier, a logical constant, which is interpreted as (∀) or (⇒).

Answer 19

An existential quantification is a type of quantifier, a logical constant, which is interpreted as "there exists" (∃)

Answer 20

P ⇒ Q means "P implies Q", i.e., "if P is true, then Q must be true"

Answer 21

When it returns only a finite number of tuples

Answer 22

All three languages are equal regarding expressiveness (relationally complete)

Answer 23

By having a line under it

Answer 24

Entities are objects of the real world about which we want to store information Entities are grouped into entity types (represented as a square).

Answer 25

An entity set is a set of entities of the same type (e.g., all persons having an account at a bank).

Answer 26

Attributes model characteristics of entities or relationships. * All entities of an entity type have the same characteristics * Attributes are declared for entity types * Attributes have a domain or value set

Answer 27

Ex: A person might have multiple phone numbers (or a single one)

Answer 28

Ex: An address can be modeled as a string or composed of street and city

Answer 29

Ex: Birthday and age. ## Footnote Age is computed several times using birthday.

Answer 30

* A functional dependency is a constraint between two sets of attributes in a relation from a database. * In other words, functional dependency is a constraint that describes the relationship between attributes in a relation. * Given a relation R, a set of attributes X in R is said to functionally determine another set of attributes Y, also in R, (written X → Y) if, and only if, each X value in R is associated with precisely one Y value in R; R is then said to satisfy the functional dependency X → Y.

Answer 31

Primary key attributes are marked by underlining.

Answer 32

A set of attributes which can uniquely identify an entity. Example: 1. Finding the super key of a *Person(name, address, licencePlateNumber, phoneNumber)* 2. We consider that the same phoneNumber can appear in several countries so phone number is not enough to uniquely identify a person 3. But the key: *(address, phoneNumber, name)* is a super key Remember that super keys are not necessarily minimal. Both: *(address, phoneNumber)* and *(address, phoneNumber, name)* are super keys

Answer 33

A canditate key is a minimal super key (if you remove any attribute from the super key, then you cannot uniquely identify the entity) One of the candidate keys for an entity is chosen as the primary key.

Answer 34

Relationships describe connections between entities. Relationships between entities are grouped into relationship types.

Answer 35

An association between two or more entities is called relationship (instance). A relationship set is a collection of relationship instances.

Answer 36

Role names are optional and used to characterize a relationship type. ## Footnote Especially useful for recursive relationship types, i.e., an entity type is participating multiple times in a relationship type.

Answer 37

Relationship types can also have (descriptive) attributes.

Answer 38

* Number of participating entity types * Mostly: binary * Rarely: ternary * In general: n-ary or n-way (multiway relationship types)

Answer 39

* Cardinality ratio (Chen notation): 1:1, 1:N, N:M * Cardinality limits ([min,max] notation): [min,max] * Participation constraint: partial or total

Answer 40

Each entity of an entity type must participate in a relationship, i.e., it cannot exist without any participation. * Double line means that it has to participate * Example: * Wine has to be produced * A producer does not have to produce

Answer 41

Each entity of an entity type ***can*** participate in a relationship, i.e., it can exist without any participation. * Single line means partial

Answer 42

A weak entity is an entity that cannot be uniquely identified by its attributes alone; therefore, it must use a foreign key in conjunction with its attributes to create a primary key. The foreign key is typically a primary key of an entity it is related to. * The weak entity type's key attributes are marked by underlining with a dashed line (partial key, discriminator). * The weak entity is marked by a double line around it * Total participation of the weak entity type. * Only in combination with 1:N (N:1) (or rarely also 1:1) relationship types * The strong entity type is always on the '1'-side

Answer 43

Specialization and generalization is expressed by the isa relationship type (inheritance).

Answer 44

* New relation with relationship type's attributes * Add attributes referencing the primary keys of the involved entity type relations * Primary key: set of foreign keys

Answer 45

* Add information to the entity type relation of the "N"-side: * Add foreign key referencing the primary key of the "1"-side entity type relation * Add attributes of the relationship type

Answer 46

* Add information to one of the involved entity type relations: * Add foreign key referencing the primary key of the other entity type relation * Add attributes of the relationship type

Answer 47

All participating entity types are mapped according to the standard rules.

Answer 48

Create a separate relation for each multi-valued attribute. ## Footnote **Example:** person(_PID_, name) phoneNumber: (_PID --\> person, number)_

Answer 49

Include the component attributes in the relation Ex: Person(_PID_, name, street, city)

Answer 50

Ignore them during mapping to relations, can be added later by using views.

Answer 51

Ex: A teacher moves from office 226 to office 338. There is an update anomaly if only some tuples are changed but not all.

Answer 52

When you cannot insert a new row without using null values.

Answer 53

Ex: When a course is created a teacher is assigned to the course. When the course ends all information about the teacher may also disappear.

Answer 54

The functional dependency α → β is called trivial if β ⊆ α. ## Footnote **Example:** α = {A, B, C}, β = {A, C} then α → β is trivial

Answer 55

A full functional dependency occurs when you already meet the requirements for a functional dependency and the set of attributes on the left side of the functional dependency statement cannot be reduced any further. ## Footnote Examples: “{CPR, age} → name” is a functional dependency, but it is not a full functional dependency because you can remove age from the left side of the statement without impacting the dependency relationship.

Answer 56

The opposite of full functional dependency. If you remove an attribute from the left-hand side the FD still holds. ## Footnote Eg. if both {A,B} → {C} and {A} → {C} then {C} is **partially dependent** on {A,B}.

Answer 57

Given a set of FDs F we can derive additional FDs * F⁺ contains all FDs that can be derived from F, i.e., all FDs **logically implied** by dependencies in F. * F⁺ is F's **closure** * Inference rules (Armstrong axioms) help computing F⁺

Answer 58

The closure of a set of attributes (α⁺) with respect to a set of FDs F and a set of attributes α is: α⁺ = { A | α → A ∈ F⁺ } Observation: If α → β is in F⁺, then β is in α⁺

Answer 59

α, β, γ, δ are subsets of attributes in ℛ * Reflexivity: * If β ⊆ α then α → β * Augmentation * If α → β then αγ → β * That is adding attributes in dependencies, does not change the basic dependencies * Transitivity * If α → β and β → γ then α → γ

Answer 60

α, β, γ, δ are subsets of attributes in ℛ * Union * If α → β and α → γ then α → βγ * Decomposition * If α → βγ then α → β and α → γ * Pseudotransitivity * If α → β and βγ → δ then αγ → δ

Answer 61

F ≡ G if: * Their closures are the same, i.e., F⁺ = G⁺ * Both sets allow for deriving the same set of FDs

Answer 62

1. Make sure that the right-hand side only contains singletons 1. **Ex:** AB → CD turns into AB → C and AB → D 2. Remove extraneous attributes on the left-hand side 1. **Ex:** If B+ contains A then AB → C turns into B → C 3. Remove redundant FDs 1. For each FD 1. Pretend the FD doesn't exist 2. If α+ (left-hand side) contains the right-hand side then there is another way to reach the right-hand side and the FD can be removed. 4. Combine all FDs with the same left-hand side

Answer 63

Normalization involves decomposing a table into less redundant (and smaller) tables without losing information, and then linking the data back together by defining **foreign keys** in the old table referencing the **primary keys** of the new ones. ## Footnote The objective is to isolate data so that additions, deletions, and modifications of an attribute can be made in just **one table** and then propagated through the rest of the database using the defined **foreign keys**.

Answer 64

A decomposition is valid if ℛ = ℛ1 U ℛ2, i.e., no attributes in ℛ get lost

Answer 65

A decomposition of R into R₁ and R₂ is lossless if R = R₁ ⋈ R₂

Answer 66

Means that the reconstruction leads to additional tuples.

Answer 67

All functional dependencies that hold for R must be verifiable in the new schemas R₁, ..., R_n * We can check all dependencies locally on R₁, ..., R_n * We avoid the alternative: computing the join R₁ ⋈ ... ⋈ R_n to test if a FD is violated A decomposition is dependency preserving if: * F_R ≡ (F_R1∪ ... ∪ F_Rn) i.e, * F_R⁺ = (F_R1 ∪ ... ∪ F_Rn)⁺

Answer 68

**Prime attribute:** an attribute which is in at least one candiate key. **Non-prime attribute:** an attribute which is not in **any** candiate key. Example: *Person(name, _phoneNumber, address_, age)* * Prime attributes: *phoneNumber* and *address* * Non-prime attributes: *name* and *age*

Answer 69

A relation ℛ is in 1NF if the domains of all its attributes are atomic (no composite or set-valued domains). ## Footnote Example:

Answer 70

A relational schema is in 2NF if each non-prime attribute is fully functionally dependent on each candidate key Cheatsheet: * Any table which does not have composite primary keys are automatically in 2NF

Answer 71

A relation schema R is in 3NF if at least one of the following conditions holds for each of its functional dependencies α → β: * α → β is a trivial functional dependency. * α is a superkey of R. * β is prime (part of a candidate key)

Answer 72

BCNF is a stricter form of 3NF. To be in BCNF there are only the two first criteria from 3NF. * α → β is a trivial functional dependency. * α is a superkey of R.

Answer 73

Retrieves information SELECT \* FROM tableName;

Answer 74

Creates a cartesian product of the tables listed

Answer 75

GROUP BY is used in collaboration with the SELECT statement to group together those rows in a table that have identical data. This is done to eliminate redundancy in the output and/or compute aggregates that apply to these groups.

Answer 76

* 'abc' LIKE 'abc' true (must match exactly) * 'abc' LIKE 'a%' true (must start with 'a' and can be followed by anything) * 'abc' LIKE '\_b\_' true (three letters, the middle one is 'b') * 'abc' LIKE 'c' false ## Footnote * *Syntax:** SELECT tname FROM Trainer WHERE tname LIKE '%s' * *Returns:** every trainer whose name ends on an 's'

Answer 77

**Syntax:** SELECT empId FROM Company LEFT OUTER JOIN Department ON Company.Id = Department.empId ## Footnote **The different joins:**

Answer 78

Ex: The "age" field is set to -20 or dateOfDeath happened before dateOfBirth

Answer 79

A correlated subquery is a subquery (a query nested inside another query) that uses values from the outer query. ## Footnote Because the subquery is evaluated once for each row processed by the outer query, it can be inefficient.

Answer 80

* Commit = success = "going forward" * Rollback = failure = "in reverse"

Answer 81

* Atomicity * Consistency * Isolation * Durability

Answer 82

Atomicity requires that each transaction be "all or nothing": if one part of the transaction fails, then the entire transaction fails, and the database state is left unchanged. * An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes. * To the outside world, a committed transaction appears (by its effects on the database) to be indivisible ("atomic"), and an aborted transaction does not happen.

Answer 83

The consistency property ensures that any transaction will bring the database from one valid state to another. ## Footnote Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof. This does not guarantee correctness of the transaction in all ways the application programmer might have wanted (that is the responsibility of application-level code) but merely that any programming errors cannot result in the violation of any defined rules.

Answer 84

The database should be durable enough to hold all its latest updates even if the system fails or restarts. * If a transaction updates a chunk of data in a database and commits, then the database will hold the modified data. * If a transaction commits but the system fails before the data could be written on to the disk, then that data will be updated once the system springs back into action.

Answer 85

In a database system where more than one transaction are being executed simultaneously and in parallel, the property of isolation states that all the transactions will be carried out and executed as if it is the only transaction in the system. ## Footnote No transaction will affect the existence of any other transaction.

Answer 86

A sequence of operations from one or more transactions. In concurrent transactions, the operations are interleaved.

Answer 87

The transactions are executed non-interleaved i.e., a serial schedule is one in which no transaction starts until a running transaction has ended.

Answer 88

* read(Q, q) * Read the value of the database item Q and store in the local variable q. * write(Q, q) * Store the local variable q in the database item Q * Arithmetic operations * commit * rollback

Answer 89

We say two operations performed by different transactions on the same resource conflict if the resulting state depends on the order in which they are executed **Conflicting operations:** * Read / Write * Write / Read * Write / Write

Answer 90

**Definition1**: Concurrent execution of transactions must leave the database in a consistent state. **Definition2**: Concurrent execution of transactions must be (result) equivalent to some serial execution of the transactions. (*Result equivalent means final database states must be identical.*)

Answer 91

Two schedules would be view equivalence if the transactions in both the schedules perform similar actions in a similar manner. **Conditions:** 1. If T reads the initial data (data which has not had a write operation performed on it yet) in S₁, then it also reads the initial data in S₂. 2. If T reads the value written by J in S₁, then it also reads the value written by J in S₂. 3. If T performs the final write on the data value in S₁, then it also performs the final write on the data value in S₂.

Answer 92

A schedule is view serializable iff it is view equivalent to a serial schedule

Answer 93

If two sechedules S1 and S2 perform the same conflicting operations (W/R, R/W, W/W) they are conflict equivalent ## Footnote **Example below (S1 and S2 are conflict equivalent):**

Answer 94

A schedule is conflict serializable if it is conflict equivalent to a serial schedule. ## Footnote A conflict serializable schedule ensures that after execution the database is in a consistent state.

Answer 95

The transactions T₁ and T₂ have write instructions without read instructions, termed **blind writes**.

Answer 96

A schedule is conflict serializable if its conflict graph is acyclic (no loops).

Answer 97

For each pair of transactions T₁ and T₂ where T₂ reads data items written by T₁ , T₁ must commit before T₂ commits.

Answer 98

If T₂ reads data items written by T₁, the commit operation of T₁ must appear before the read done by T₂.

Answer 99

Ensures serializable schedules by delaying transactions that could violate serializability.

Answer 100

* Two types of locks can be set on an item Q: * Exclusive lock: LX Q * An exclusive lock gives write or read access on an item * Shared lock: LS Q * A shared lock gives read access request on an item * Locks can be released. * Unlock: UN Q

Answer 101

* In **strict** two-phase locking, exclusive locks are not released until after commit time. * E.g. shared locks can be unlocked before commit * In **rigorous** two-phase locking, no locks are released until after commit time.

Answer 102

When T₁ requests a conflicting lock from T₂: * If T₁ is older than T₂ * Then T₁ waits until the data-item is available. * If T₁ is younger than T₂ * Then T₁ dies. * T₁ is restarted later with a random delay but with the same timestamp. This scheme allows the older transaction to wait but kills the younger one.

Answer 103

When T₁ requests a conflicting lock from T₂: * If T₁ is older than T₂ * Then T₂ is rolled back. * T₂ is restarted later with a random delay but with the same timestamp. * If T₁ is younger than T₂ * Then T₁ waits until the resource is available. This scheme, allows the younger transaction to wait; but when an older transaction requests an item held by a younger one, the older transaction forces the younger one to abort and release the item.

Answer 104

Precedence graphs

Answer 105

* **Volatile** storage: does not survive system crashes * Main memory * Cache memory * **Nonvolatile** storage: survives system crashes * Disk, both magnetic and solid-state * Tape * **Stable** storage: a mythical form of storage that survives all failures * Approximated by maintaining multiple copies of distinct nonvolatile media with independent failure modes.

Answer 106

Following a failure, the following is done. * Undo all transactions for which the log has “start” entry but no “commit” entry. * Redo all transactions for which the log has both “start” and “commit” entries.

Answer 107

Algorithm: * No-undo → do not change the database during a transaction * No-redo → on commit, write changes to the database in a single atomic action

Answer 108

A checkpoint identifies the state of the database at a point in time. * Modifications to database are performed in memory and are not necessarily written to disk after every update. * Therefore, periodically, the database system must perform a checkpoint to write these updates which are held in-memory to the storage disk. * A checkpoint creates a known good point from which the database system can start applying changes contained in the log during recovery after an unexpected shutdown or crash.

Answer 109

* The database is stored in a collection of **files** * A file is a split into a set of fixed size **blocks** * A block contains a number of **records** * A record contains a number of **fields** ## Footnote Note: Files reside in secondary memory (usually a hard disk).

Answer 110

**Clustered index (sometimes primary index):** * Data is stored in the order of the clustered index * Example: Phonebook, the entire book is an index; The phone number is stored next to the name * Only one clustered index per table * Usually the primary key is what is sorted by **Non-clustered index (sometimes secondary index):** * Like the index of a text book; The index in the back of the book points to which pages the information is stored

Answer 111

Main idea: Records are stored in blocks in no particular order

Answer 112

* Main idea: Records are stored in blocks in one particular order * Can do binary search because data is sorted

Answer 113

Ordered Indexing is of two types: sparse or dense In **dense index**, there is an index record for every search key value in the database. This makes searching faster but requires more space to store index records itself. In **sparse index**, index records are not created for every search key. An index record here contains a search key and an actual pointer to the data on the disk. To search a record, we first proceed by index record and reach at the actual location of the data. If the data we are looking for is not where we directly reach by following the index, then the system starts sequential search until the desired data is found.

Answer 114

A B+ tree is a balanced binary search tree that follows a multi-level index format. ## Footnote The leaf nodes of a B+ tree denote actual data pointers. B+ tree ensures that all leaf nodes remain at the same height, thus balanced. Additionally, the leaf nodes are linked using a link list; therefore, a B+ tree can support random access as well as sequential access.

Answer 115

Properties: * **Balanced**, i.e., all path from root to leaf has the same length * Nice because predictable performance * **Bushy**, each node has between ceiling(d/2) and d children * Except the root node that has between 2 and d children * Leaf nodes have between ceiling(d/2) and d - 1 children * Nice because each level adds extra I/O * **Ordered** * The key values are sorted in each node, i.e, k_x \< k_y , if x \< y

Answer 116

If queries are sped up it is usually at the expense of slowing down modifications, and vice-versa.

Answer 117

* Linear search * Expensive, but always applicable * Binary search * Applicable only when the file is appropriately ordered * Hash table search * Single record retrieval; does not work for range queries * Retrieval of multiple records * Secondary index search * Implemented with multiple pointers, each to a single record.

Answer 118

If we: A ⋈ B Then for each tuple of A, we scan the entirety of B. * Can be used for all join types * Can be quite expensive **Reason it is called 'nested':** for each (tuple of A) for each (tuple of B) compare common attributes of A and B

Answer 119

* The relations R & S have to be sorted on the join attributes (the foreign key between the two relations) * Then both relations are merged, starting with a pointer at the top of each relation * When the two foreign keys at the pointers are equal the joined product is added to the new relation, and R's pointer is moved to the next entry * If they are still equal another join product is added. If they aren't equal S's pointer is moved to the next entry.

Answer 120

A hash join is performed by hashing one data set into memory based on join columns and reading the other one and probing the hash table for matches. ## Footnote The hash join is very low cost when the hash table can be held entirely in memory, with the total cost amounting to very little more than the cost of reading the data sets. The cost rises if the hash table has to be spilled to disk in a one-pass sort, and rises considerably for a multipass sort.

Answer 121

* Push down selections * Push down projections * Avoid cross products

Answer 122

The arity of a relation is its number of columns (attributes) * The arity of *Person(name, cpr, age, height)* is 4 * The arity of *Job(name, title, salary)* is 3 * Then the arity of Person x Job is 4 + 3 = 7

Answer 123

The intersection (R ∩ S) of two relations R and S consists of a set of tuples that occur in both relations. Intersection can be expressed as dffierence: (R ∩ S = R — (R — S))

Answer 124

The natural join can be expressed as a cartesian product followed by selections and projections R⋈S = πA₁,...,A_m,R.B₁,...R.B_k,C₁,...,C_n(σR.B₁ = S.B₁Λ...ΛR.B_k = S.B_k(R X S))

Answer 125

L ⋉ R = π_L(L⋈R) L ⋊ R = R ⋉ L = π_R(L⋈R)

Answer 126

We don't need to know what makes up the division operator. All we need to know at the exam is that an alternative way to describe it exists.

Answer 127

A binary operation is commutative if changing the order of the operands does not change the result. The following joins at **not** commutative: * Natural join * Left outer join * Right outer join * Semi join

Answer 128

They are: 1. Declarative language (also called non-prodecural) 2. Declarative language (also called non-prodecural)

Answer 129

* A relationship type * An entity type * A composite attribute * A weak entity type

Answer 130

An identifying relationship means that the child table cannot be uniquely identified without the parent. Example: * Account (AccountID, AccountNum, AccountTypeID) * PersonAccount (AccountID, PersonID, Balance) * Person(PersonID, Name) The *Account* to *PersonAccount* relationship and the *Person* to *PersonAccount* relationship are identifying because the child row (*PersonAccount*) cannot exist without having been defined in the parent (*Account* or *Person*). In other words: there is no *PersonAccount* when there is no *Person* or when there is no *Account*.

Answer 131

... that the number of relations becomes larger.

Answer 132

1. Modification anomalies are avoided 2. The information redundancy in the schema is minimized

Answer 133

2PL is a concurrency control method that guarantees serializability. The protocol utilizes locks, applied by a transaction to data, which may block other transactions from accessing the same data during the transaction's life. By the 2PL protocol locks are applied and removed in two phases: 1. Expanding phase: locks are acquired and no locks are released. 2. Shrinking phase: locks are released and no locks are acquired.

Answer 134

Durability and atomicity

Answer 135

1. NUMBER 2. TIME 3. VARCHAR 4. DATETIME 5. DATE 6. CHAR

Answer 136

* A point query, e.g., SELECT name FROM employee WHERE id = 45 * A range query, e.g., SELECT name FROM employee WHERE id BETWEEN 120 AND 130

Answer 137

Speeding up queries and slowing down modification

Answer 138

A point query, e.g., SELECT name FROM employee WHERE id = 45

Answer 139

Any attribute (combination of attributes) of a table

Answer 140

* A query plan describes the logic operations whose executions will deliver the correct result. * A query execution plan describes the algorithms whose executions will deliver the correct result.

Answer 141

A **disjunctive** query is simply two or more predicates joined by 'OR', whereas a **conjunctive** query would be two or more predicates joined by 'AND'.

Answer 142

An arrow is at the '1' side of a 1:N or N:1 relationship and at both sides at a 1:1 relationship ## Footnote **Example:** A grandma takes care of exactly one cat

Answer 143

The cardinality ratio is attached to the entity which it governs. N-amount of people can have only 1 location as their birthplace

Answer 144

The cardinality limit is the minimum and maximum values allowed. * How many times can a person participate in the relationship "Birthplace"? * One time. * How many times can a location be the birthplace of people? * Zero to N times.

Answer 145

**Syntax:** ## Footnote SELECT person.Name AS nameOfPerson, person.money AS money FROM person

Answer 146

There is a requirement that the argument to the left of AND be less than or equal to the argument on the right ## Footnote **Syntax:** a BETWEEN x AND y **is equivalent to:** a \>= x AND a \<= y

Answer 147

**Syntax:** SELECT city, max(temp) FROM weather GROUP BY city HAVING max(temp) \< 40;

Answer 148

A dense index in databases is a file with pairs of keys and pointers for every record in the data file. Every key in this file is associated with a particular pointer to a recordin the sorted data file. In clustered indices with duplicate keys, the dense index points to the first record with that key.

Answer 149

A sparse index in databases is a file with pairs of keys and pointers for every block in the data file. Every key in this file is associated with a particular pointer to the blockin the sorted data file. In clustered indices with duplicate keys, the sparse index points to the lowest search key in each block.

Answer 150

decide if a schedule is serializable.

Answer 151

* The physical order of the rows is not the same as the index order. * The indexed columns are typically non-primary key columns used in JOIN, WHERE, and ORDER BY clauses.

Answer 152

* Clustering alters the data block into a certain distinct order to match the index, resulting in the row data being stored in order. * Therefore, only one clustered index can be created on a given database table. Clustered indices can greatly increase overall speed of retrieval, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items is selected.

Answer 153

When you get a set of key values and a dimension (number of pointers per block). * number of keys on non-leafs to keep on the old leaf when splitting: celing(d / 2) - 1 * number of keys to keep on the old leaf when splitting: celing( (d-1) / 2)

Main Flashcards

(181 cards)