Brainscape's Knowledge GenomeTM

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


See full index

CP363, Database I > Test 2 Notes > Flashcards

Test 2 Notes Flashcards

1
Q

Relational algebra

A

a theory that uses algebraic structures with well-founded semantics for modeling data and defining queries.

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

Sets

A

a set is a collection of elements
{a,b,c},{a,d,e,f} ← no duplications

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

Bags

A

collection of elements with duplications

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

Query

A

a function over relations; Q(R1,…,Rn) = Rresult
Because the result of a query is a relation it can be used as input to another query. → nested queries

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

Set Operations

A

Set union: It is the set of all elements in the collection.
Set intersection: it is the set of all objects that are members of both sets A and B
Set difference; S-T is the set that consists of elements of S which are not elements of T

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

Relational Algebra Operators

A

Selection (sigma): takes the horizontal subset of rows of a single table
Projection (pi): takes a vertical subset from the columns of a single table
Cross product (X)
Join (infinity): a combination of cross product, selection, and projection
Union (U)
Set diff (-)
Intersection(Upside down U)

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

Selection operator

A

Selection operator, (sigma) is to specify the rows to be retained from input relation. It takes the horizontal subset of rows of a single table that satisfies a particular condition.

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

Projection

A

(pi): vertical subset from columns of single table

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

Cross product(X)

A

Merges both tables together

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

Join (infinite looking thing)

A

combination of cross product, selection, and projection.

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

Union

A

A UNION B, this will simply add A and B together, but they must be union compatible. To combine result-set of two or more SELECT statements.

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

Compatibility test

A

A and B have same number of fields or attributes, and field in each schema has same type.

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

Set difference

A

A and B must be compatible once again, defines relation consisting of tubles in A but not in B.

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

Intersection

A

Must be compatible, gets a relation consisting of set of all tuple which occur in both A and B

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

Database design consists of

A

Requirement analysis, Architecture design, Implementation, Testing, Maintenance.

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

Database Design Process

A

Phase 1: Conceptual Model
Phase 2: Relational Model
Phase 3: Normalization
Phase 4: Physical Schema

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

Entities

A

An entity is an object or a class of real world objects having common characteristics and properties.

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

Attribute

A

An attribute is a characteristic of an entity or relationship

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

Relationship

A

an association among two or more entities

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

Every entity must have a primary key (T/F)

A

True

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

ERD (Entity Relationship Diagram)

A

a diagram that shows relationships of entity sets stored in a database. It is a conceptual diagram. Crucial to categorize what are entities in db design.

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

Relational schema

A

a set of relational tables and associated items that are related to one another

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

Entity-relationship model

A

A high level data model used to determine data elements and relationship for specified system

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

Relational model

A

a model that represents the database as a collection of relations. A relation is nothing but a table.

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

ER Model shows real world objects, for anyone, entities attributes relationships and arrows, conceptual or high level.
(T/F)

A

True

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

Relational Model does not show objects in tables and how they relate, for programmers, tables, columns, domains, Representational. (T/F)

A

False

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

Conceptual model procedure

A

Step 1: identify entities
Step 2: decide attributes for entity
Step 3: decide datatype for attribute
Step 4: build entity relationships diagrams
Step 5: Create relational schemes - design primary and foreign keys
Step 6: create physical database
Step 7: develop test cases for testing

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

Weak entities

A

cannot exist by itself. Depends on strong entity to ensure existence. Has partial discriminator key (no primary key), double rectangle.

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

Strong entity

A

does not depend on any other entity for existence. Is also known as an independent entity (Single rectangle)

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

Key attribute

A

uniquely identifies an entity

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

Composite attribute

A

combination of other attributes

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

Multivalued attribute

A

holds multiple values, for example a person can have several phone numbers

33
Q

Derived attribute

A

Dynamic and derives from other attributes, for example persons age is derived from date of birth

34
Q

Normalization

A

involves decomposing relations with anomalies to produce smaller well-structured relations.

35
Q

Types of anomalies

A

Redundancy
Update anomalies
Delete anomalies
Insert anomalies

36
Q

Inheritance

A

enables you to share attributes between entities

37
Q

Functional dependency

A

dependent and determinant. X–> Y. X is determinant and Y is dependent

38
Q

Armstrongs axioms

A

1: if Y ⊆ X, then X → Y (trivial FD)
2: if X→ Y and Y→ Z, then X→ Z (transitivity)
3: if X→ Y and X→ Z, then X→ YZ (composition) , 4. opposite of composition is (decomposition)

39
Q

Full functional dependency

A

if determinant attribute functionally determines all other attributes in a table

40
Q

Partial functional dependency

A

if a determinant attribute functionally determines only some of attributes in a table.

41
Q

Full FD is not desirable in practical DB design. (T/F)

A

False

42
Q

Advantages of FD

A

Avoids data redundancy.
Helps maintain quality of data in database
Helps defined meanings and constraints of database
Helps identify bad designs
Helps find facts regarding db design

43
Q

1NF

A

a property that indicates a table in relational db satisfies following six req.
1. Each column must have unique name
2. Order of rows and order of columns doesnt matter
3. Each column must have single data type
4. No two rows can contain identical values
5. Each column must contain single value
6. Columns cannot contain repeating groups

44
Q

2NF

A

Indicates table is in 1NF and non key attributes are fully functional dependent on primary key

45
Q

3NF

A

is a property that indicates a table in relational db is in 2ND and constraints no transitive dependencies

46
Q

Decomposition is used to

A

eliminate some problems of bad design like anomalies, inconsistencies, and redundancy.

47
Q

Types of decomposition

A

Lossless decomposition: is a decomposition that does not lose any information after the decomposition. Is lossless if natural joins of all the decomp give original relation.

Dependency Preserving decomposition: in which each functional dependency X→ Y specified in F appeared directly in one of the relation schemas R1 in the decomposed relations (i=1, 2, …n)

Unnecessary decomposition: no redundancy; schema is more complicated (and uid is stored twice)

48
Q

Partitioning

A

Database process where very large tables are divided into multiple smaller parts

49
Q

Types of partitioning

A

Vertical partitioning
Horizontal partitioning

50
Q

Advantages of database partitioning

A

Improve scalability
Improve performance
Provide operational flexibility
Improve availability

51
Q

Vertical Partitioning

A

divides a table into multiple tables that contain fewer columns.

52
Q

Vertical partitioning advantages and disadvantages

A

Advantages:
Speeds up queries that touch only a small fraction of columns
Single column can be compressed effectively, reducing disk I/O
Disadvantages:
Updates are expensive!
Need many joins to access many columns
Repeated key columns add overhead

53
Q

Horizontal Partitioning (sharding)

A

divides a table into multiple tables that contain the same number of columns, but fewer rows.

54
Q

Horizontal partitioning advantages and disadvantages

A

Advantages:
Efficiency, Better performance, Security
Disadvantages:
Improper horizontal partitions may cause performance issue

55
Q

Transaction

A

A series of DB queries, the execution of a sequence of one or more operations on a shared database to perform some higher level function.

56
Q

Concurrency

A

execution of several transactions at same time

57
Q

Scheduling

A

mechanism that makes sure all transactions run in proper order and in proper states

58
Q

Atomic action

A

an indivisible sequence of primitive operations without interruption

59
Q

Write-read conflict

A

known as dirty/inconsistent read. A transaction reads value written by another transaction that has not yet committed.

60
Q

Read-write conflict

A

known as unrepeatable read. Another transaction modifies that value in between the two reads.

61
Q

Write-write conflict

A

called lost update. The second one to write the value overwrite the first change.

62
Q

ACID Properties

A

Atomicity: Either all changes performed by a transaction occur or none occurs.
Consistency: A transaction as a whole does not violate integrity
Isolation: Transactions appear to execute one after the other in sequence
Durability: If a transaction commits, its changes will survive failures

63
Q

State of transactions

A

Active: the transaction is executing
Partially Committed: A transaction enters this state after performing its final operation
Committed: after successful completion checks
Failed: when the normal execution can no longer proceed.
Aborted: after the transaction has been rolled back

64
Q

Types of scheduling

A

Serializable Schedule and Concurrent Schedule

65
Q

Serial Schedule

A

is a type of schedule where one transaction is executed completely before starting another transaction

66
Q

A serializable schedule always leaves the database in a consistent state (T/F)

A

True

67
Q

Concurrent Schedule

A

a type of DBMS schedule in which many transactions can run concurrently

68
Q

Serial Schedule Advantages

A

Always gives guarantee for data consistency

69
Q

Concurrent Schedule Advantages

A

Reduce waiting time. Improve responses time and throughput.

70
Q

Serial Schedule Disadvantages

A

High average waiting time. Low response time an low throughput could be possible.

71
Q

Concurrent Schedule Disadvantages

A

Possible data inconsistency. Some time too much context switching.

72
Q

Two operations conflict if and only if

A

They are by different transactions, and
They are on the same object, and
And at least one of them is a write.

73
Q

Locking Scheduler

A

Each element has a unique lock
Each transaction must first acquire the lock before reading/writing that element
If the lock is taken by another transaction, then wait
The transaction must release the lock after completing its work.
By using lock scheduler we ensure conflict-free operation

74
Q

Two Phase Locking

A

2PL is a concurrency control method which divides execution phase of a transaction into two parts: Growing Phase and Shrinking Phase

75
Q

2PL Protocol

A

In transactions, all lock requests must precede all unlock requests
Always obtain a S (shared) lock on object before reading.
Always obtain an X (exclusive) lock on object before riting
If a X lock on object, no other locks (S or X) can be obtained on that object

76
Q

Shared lock

A

prohibits any other process from requesting a write lock on specified parts of file.
Exclusive lock: gives process exclusive access for writing to the specified part of file.

77
Q

Recoverable schedule

A

a schedule is recoverable if each transaction commits only after all transactions from which it has read and has committed in a certain order.

78
Q

Causes of recovery

A

data corruption, system failures, data center outage

79
Q

Principles in recovery

A

Write-ahead log = A file that records every action of all running transactions
Force log entries to disk
After crash, recovery manager reads log entries and finds out exactly which transactions were in flight

CP363, Database I (2 decks)

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