Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

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
ER Model shows real world objects, for anyone, entities attributes relationships and arrows, conceptual or high level. (T/F)
True
26
Relational Model does not show objects in tables and how they relate, for programmers, tables, columns, domains, Representational. (T/F)
False
27
Conceptual model procedure
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
28
Weak entities
cannot exist by itself. Depends on strong entity to ensure existence. Has partial discriminator key (no primary key), double rectangle.
29
Strong entity
does not depend on any other entity for existence. Is also known as an independent entity (Single rectangle)
30
Key attribute
uniquely identifies an entity
31
Composite attribute
combination of other attributes
32
Multivalued attribute
holds multiple values, for example a person can have several phone numbers
33
Derived attribute
Dynamic and derives from other attributes, for example persons age is derived from date of birth
34
Normalization
involves decomposing relations with anomalies to produce smaller well-structured relations.
35
Types of anomalies
Redundancy Update anomalies Delete anomalies Insert anomalies
36
Inheritance
enables you to share attributes between entities
37
Functional dependency
dependent and determinant. X--> Y. X is determinant and Y is dependent
38
Armstrongs axioms
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
Full functional dependency
if determinant attribute functionally determines all other attributes in a table
40
Partial functional dependency
if a determinant attribute functionally determines only some of attributes in a table.
41
Full FD is not desirable in practical DB design. (T/F)
False
42
Advantages of FD
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
1NF
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
2NF
Indicates table is in 1NF and non key attributes are fully functional dependent on primary key
45
3NF
is a property that indicates a table in relational db is in 2ND and constraints no transitive dependencies
46
Decomposition is used to
eliminate some problems of bad design like anomalies, inconsistencies, and redundancy.
47
Types of decomposition
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
Partitioning
Database process where very large tables are divided into multiple smaller parts
49
Types of partitioning
Vertical partitioning Horizontal partitioning
50
Advantages of database partitioning
Improve scalability Improve performance Provide operational flexibility Improve availability
51
Vertical Partitioning
divides a table into multiple tables that contain fewer columns.
52
Vertical partitioning advantages and disadvantages
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
Horizontal Partitioning (sharding)
divides a table into multiple tables that contain the same number of columns, but fewer rows.
54
Horizontal partitioning advantages and disadvantages
Advantages: Efficiency, Better performance, Security Disadvantages: Improper horizontal partitions may cause performance issue
55
Transaction
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
Concurrency
execution of several transactions at same time
57
Scheduling
mechanism that makes sure all transactions run in proper order and in proper states
58
Atomic action
an indivisible sequence of primitive operations without interruption
59
Write-read conflict
known as dirty/inconsistent read. A transaction reads value written by another transaction that has not yet committed.
60
Read-write conflict
known as unrepeatable read. Another transaction modifies that value in between the two reads.
61
Write-write conflict
called lost update. The second one to write the value overwrite the first change.
62
ACID Properties
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
State of transactions
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
Types of scheduling
Serializable Schedule and Concurrent Schedule
65
Serial Schedule
is a type of schedule where one transaction is executed completely before starting another transaction
66
A serializable schedule always leaves the database in a consistent state (T/F)
True
67
Concurrent Schedule
a type of DBMS schedule in which many transactions can run concurrently
68
Serial Schedule Advantages
Always gives guarantee for data consistency
69
Concurrent Schedule Advantages
Reduce waiting time. Improve responses time and throughput.
70
Serial Schedule Disadvantages
High average waiting time. Low response time an low throughput could be possible.
71
Concurrent Schedule Disadvantages
Possible data inconsistency. Some time too much context switching.
72
Two operations conflict if and only if
They are by different transactions, and They are on the same object, and And at least one of them is a write.
73
Locking Scheduler
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
Two Phase Locking
2PL is a concurrency control method which divides execution phase of a transaction into two parts: Growing Phase and Shrinking Phase
75
2PL Protocol
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
Shared lock
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
Recoverable schedule
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
Causes of recovery
data corruption, system failures, data center outage
79
Principles in recovery
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.
© 2025 Bold Learning Solutions. Terms and Conditions