Week 2: Data Extraction Flashcards
REVERSED
- Physical level: describes how a record is stored
- Logical level: describes what data stored in the database and the relationships among the data e.g. name: string, salary: real
- View level: application programs which hide details of data types. Views can also hide information for security/privacy purposes
What are the 3 database levels of abstraction?
REVERSED
-structures: e.g. rows and columns, nodes and edges, key-value pairs
-constraints: e.g. all rows must have same number of columns, age must be positive, c
* operations: e.g. return the values of record x, find rows where the column “name” is “Jordan”
What are the 3 main components of data models?
REVERSED
- Schema: specifies name of relation, plus name and type of each column
- Instance: a table with rows and columns. rows = records = tuples, column = attributes = fields = features (instance is a snapshot of the schema at a point in time)
What are the 2 parts that a relation is made up of?
REVERSED
The number of columns
What is the degree of a relation?
REVERSED
The number of columns
What is the arity of a relation?
REVERSED
The number of rows
What is the cardinality of a relation?
REVERSED
attribute values are normally required to be atomic: indivisible (not containing more than one value)
What is the requirement of attribute values in a relation?
REVERSED
K is a superkey of R if values for K are sufficient to identify a unique tuple of each possible relation
What is a superkey of R?
REVERSED
Superkey K is a candidate key if K is minimal
What is a candidate key of R?
REVERSED
A selected candidate key used to represent the relation
What is the primary key of R?
REVERSED
column or set of columns in table whose values correspond to the primary key in another table
What is a foreign key of R?
REVERSED
- used for defining the database schema
- DDL is used to create and modify database objects such as tables, indexes and users
What is data definition language?
REVERSED
Language for accessing and manipulating the data in a model
DML is used for adding, deleting and modifying the records in a database
What is data manipulation language?
REVERSED
a statement requesting the retrieval of information
What is a query?
REVERSED
- and (^): retrieves records that satisfy both conditions
- or (v): retrieves records that satisfy one of the conditions
- not (~): retrieves records that don’t satisfy the condition
What are the three boolean operators?
REVERSED
A^F = F AvF = A A^T = A AvT = T
What are the 4 boolean laws including true and false
REVERSED
A^A = A AvA = A
What is A^A and AvA?
REVERSED
Av ~A = T
A^ ~A = F
What are the complement laws?
REVERSED
~(~A) = A
What is the double negation law?
REVERSED
~(A^B) = ~A v ~B ~(AvB) = ~A ^ ~B
What are de morgans laws?
REVERSED
A ^ (BvC) = (A^B) v (A^C) A v (B^C) = (AvB) ^ (AvC)
What are the distributive laws?
REVERSED
A v (A^B) = A A ^ (AvB) = A
What are the absorptive laws?
REVERSED
- the output of a relational algebra (RA) is a relation that can be input for another RA operator
- don’t need heaps of functions for each line of code
What are the advantages of composing relational algebra operators?
REVERSED
Union (U): total set of records in both relations, only records each record once even if it is in both
intersection: common records
difference (-): records in one table but not the other
selection (sigma): a specific set of rows
projection (pi): a specific set of columns. Duplicate tuples are removed
join: the natural join of two tables
What are the 6 main relational algebra operators?