Flashcards
Cz2007 Database Flashcards
Flashcards do conjunto (79)
- 1Difference between PRIMARY KEY and UNIQUEPrimary Key:
1. Can automatically Create Index
2. There can only be one primary key
3. CANNOT be NULL
Unique:
1. Can be several unique attributes
2. May have null values - 2SQL UPDATEFunction: change tuple (s) in relation
Syntax:
UPDATE
SET - 3SQL DATA TYPESINT
REAL
CHAR(n) ~ fixed-length: n
VARCHAR(n) ~ var-length, max-length: n
DATE ~ 'yyyy-mm-dd'
TIME ~'hh:mm:ss' - 4SQL FOREIGN KEY1. Referenced attributes must be declared as PRIMARY KEY
2. Requires the referenced attribute to exist in the reference relation - 5SQL CHECKWhen a tuple-based check is invoked for an insert and includes a subquery over the same table, the subquery is evaluated on the table including the inserted tuple.
- 6E/R E/R DIAGRAMDescribe the schema of database informally
- 7E/R ENTITY SET1. Collection of entities
2. A static concept, and emphasize more on the structure of data
3. No methods associated
4. Represented using RECTANGLE - 8E/R ATTRIBUTE1. Property of entity set
2. Same entities in an entity set have same properties
3. Attributes are simple values
4. Represented using OVAL, connected to entity set by a LINE - 9E/R RELATIONSHIP1. Connects two or more entity sets
2. Represented using DIAMOND, connected between entity sets by LINE - 10E/R RELATIONSHIP SETS1. Set of entities that belongs to entity set
2. Value of relationship = set of lists of currently related entities -> looks like a table - 11E/R RELATIONSHIP MANY TO ONE1. Each entity of an entity set E can be connected by relation R to AT MOST ONE entity of entity set F (Many-One from E to F)
2. Entity F can be connected to 0, 1 or many entities of E - 12E/R RELATIONSHIP ONE TO ONE1. Each entity of either entity set is related to AT MOST ONE entity of the other set
- 13E/R RELATIONSHIP MANY TO MANYEach entity of either set related to more than one entity of the other set
- 14E/R ONE-ONE, ONE-MANY, MANY-MANY
- 15E/R MULTIWAY RELATIONSHIPS1. Connects more than two entity sets
2. Using arrow pointing to entity set E: if other entities are selected from other entity sets, those entities will be related to AT MOST ONE entity in E. - 16E/R ATTRIBUTE ON RELATIONSHIP
- 17E/R ROLES1. Label the edge between relationship and the entity set with names
2. Used to differentiate the role of an entity set that appears more than once in single relationship - 18E/R SUBCLASS1. ISA represented using TRIANGLE pointing to SUPERCLASS
2. Never draw arrow: by default its one-one relationship
3. Special case of entity set
4. Have additional properties
5. Fewer entities - 19E/R CONSTRAINTS KEYS1. Set of attributes of one entity set such that no two entitis agree on all attributes of the key (can agree on some, but not all)
2. Must assign key for every entity set
3. Represented with UNDERLINE
4. For subclass: only the root entity has key which serve as key for all entities in hierarchy - 20E/R CONSTRAINTS SINGLE VALUE CONSTRAINTS1. Each attribute of entity set has single value
2. Permissible for the attribute's value to be missing (NULL)
3. Implies MANY-ONE Relationship (for each entity e in E, AT MOST ONE associated entity f in F). - 21E/R CONSTRAINTS REFERENTIAL INTEGRITY1. Rounded arrow F: Entity F is required to exist
2. Either Many-One or One-One from E to F - 22E/R WEAK ENTITY SETS1. Entities which need support from other entity set to be identified uniquely
2. Key is composed of attributes belonged to another entity set
3. Entites of E are subunit of F and cannot be represented by ISA relationship
4. Supporting relationship represented with DOUBLE DIAMOND
5. Weak Entity Set using DOUBLE RECTANGLE
6. Arrow points to the 'stronger' relationship
7. Key in weak entity set is UNDERLINED with DOTTED LINE. - 23E/R WEAK ENTITY SETS KEYKey consist of zero or more of its own attributes +
key attributes from entity set reached by 'one' supporting relationship - 24E/R WEAK ENTIY SETS REQUIREMENTS1. Must be binary
2. Must have referential entity
3. Attributes supplied for the weak entity must be key attributes of the stronger entity - 25E/R ENTITY SET VS ATTRIBUTESEntity Set must satisfy either one of these:
1. More than name: has at least one NONKEY ATTIRBUTE
2. The Many in many-one or many-many relationships - 26RELATIONAL SCHEMA ENTITY SET TO RELATION1. Entity Set -> Relations with same set of attributes: EnitySet( (key, attributes)
2. Entity set is the schema - 27RELATIONAL SCHEMA RELATIONSHIP TO RELATION1. Relationships become relations with the attributes of :
a. Keys of connected entity sets.
b. Attributes of the relationship
c. If entity set is involved multiple times in relationship, the key attributes appear multiple times in the relation: RelationshipName(name1, name2) - 28RELATIONAL SCHEMA COMBINING RELATIONS1. Allow combining relation for N-1 relationships
2. Structure of the combined relations:
a. all attributes of E
b. the key attributes of F
c. any attributes belonged to the combined relationship
3. Tip: take the key attribute of the 'one' relationship and add it to the attribute of the 'many' relation, remove the N-1 relationship - 29RELATIONAL SCHEMA SUBCLASS HIERARCHY1. Object Oriented approach: one relation for superclass, one relation for subclass (all attributes from superclass + additional attributes)
2. E/R approach: one relation for superclass, one relation for subclass (attributes of the subclass + the key of the superclass)
3. NULL approach: only has one superclass relation which includes all the attributes of the subclasses, if does not apply the value becomes NULL - 30RELATIONAL SCHEMA WEAK ENTITY SETS1. Relation of W consist of:
a. All attributes of W
b. All attributes of the supporting relationship for W
c. For each supporting relationship for W, all key attributes of the stronger relationship
2. Rename attributes accordingly - 31RELATIONAL SCHEMA PITFALLS REDUDANCY1. Repeated values
2. NULL - 32RELATIONAL SCHEMA PITFALLS UPDATE ANOMALY1. Inconsistency after update
- 33RELATIONAL SCHEMA PITFALLS INSERTION ANOMALY1. Insertion of fact cannot be done until a fact about another entity is added
- 34RELATIONAL SCHEMA PITFALLS DELETION ANOMALY1. A deletion of a tuple deletes other information about another entities
- 35RELATIONAL SCHEMA GOOD TABLESTables where attributes depends only on the primary key on the whole key
1. Split tables into two or more tables - 36DECOMPOSITIONSplitting 'bad tables' into two or more tables
- 37NORMALIZATIONProcess of decomposing relations with anomalies to produce smaller structured relations.
- 38NORMAL FORMRelation resulting from applying simple rules about functional dependencies between attributes
- 39FUNCTIONAL DEPENDENCIESA constraint between two attributes or two sets of attributes
The functional dependencies of B on A:
A -> B
"Whenever two tuples of R agree on all attributes of A, they must agree on B" - 40FUNCTIONAL DEPENDENCIES PROPERTIES1. Key Property: K determines set of attributes in relation
K -> R
2. Trivial Rule: Any set of attributes X is FD itself
3. Splitting rule:
if A1A2A3 -> B1B2B3, then A1A2A3 -> Bi, i = 1, 2, 3
4. Combining rule: reverse of splitting rule, multiple rules replaced by one rule - 41FUNCTIONAL DEPENDENCIES ARMSTRONG'S AXIOM1. REFLEXIVITY:
2. AUGMENTATION:
3. TRANSITIVITY: - 42NORMALISATION BCNF1. A relation is in BCNF whenever
A1A2..An -> B is a non trivial FD and
{A1A2..An} is a superkey
2. Left side of every nontrivial FD must contain a key - 43NORMALISATION 1NFAll data is atomic
- 44NORMALISATION 2NF1. It is in 1NF
2. Every non key attribute is fully functionally dependent on the whole key
3. All non key attributes must depend on the whole key, not just some part of it. - 45NORMALISATION BCNF DECOMPOSITION1. Look for non-trivial FD which violates BCNF
2. Add to right side as many attributes that are functionally determined by the LHS of the violated FD. Tip: use closure XD
3. Decompose relation into two:
a. All attributes from the violated FD
b. Left side of attribute + attributes not involved in FD
4. Project FDs on the decomposed elations
5. Go back to step 1 until decomposed :) - 46NORMALISATION BCNF DECOMPOSITION FASTER ROUTE1. Look for non-trivial FD that violates BCNF
2. Compute X+ (LHS of the violating FD)
3. Replace R by relations with schemas:
R1 = X+
R2 = (R - X+) U X
4. Project FD's F into R1 and R2
5. Go back to step 1 :D - 47FUNCTIONAL DEPENDENCIES MINIMAL BASISLet B the minimal basis of a relation
1. All FD's in B has singleton right sides
2. If any FD is removed from B, result is no longer basis
3. If for any FD in B one or more attributes is removed from left side of F, result is no longer basis - 48NORMALISATION MINIMAL BASIS ALGORITHM1. Split FDs to have singleton right sides
2. Show no two FDs implies the third:
using transivity or compute closure using the remaining FDs
3. Show attribute elimination from left side is not possible: eliminate and see if it can be implied from original FDs - 49FUNCTIONAL DEPENDENCIES KEY AND SUPERKEYSKey
"A superkey with additional propery, removal of attribute from K will cause K not to be a superkey"
Superkey
" set of attributes that no tuples in relation will have same value " - 50FUNCTIONAL DEPENDENCIES KEY1. Set K functionally determines all atributes of a relation
2. Minimality (no subset of K will cause no.1 to be true) - 51FUNCTIONAL DEPENDENCIES DISCOVERING FDs and KEYS1. E/R gives entity-set keys
2. FDs from many-one relationships
3. Physical laws - 52FUNCTIONAL DEPENDENCIES CLOSURE1. Closure of LHS contains all attributes
2. Minimality check - 53FUNCTIONAL DEPENDENCIES PROJECTIONLet R be a relation, S is set of FD's and R1 is a subset of R
1. Let T be an empty set
2. For each attributes of X, which is a subset of attributes of R1 computes X+ with respect to S
3. Add to T all nontrivial FDs X->A, s.t. A is in X+ and attribute of R1
4. Construct the Minimal Basis - 54SQL BASIC STRUCTURESELECT
FROM
WHERE
Mapping: π (
σ (R1 x R2 x R3 ... x Rn) - 55SQL RENAMINGAS
1. Can be applied to attributes, constraints, table/relations, etc.
Mapping: ρ - 56SQL SELECT CLAUSE1. * : everything
2. Mathematical expressions: simple calculations - 57SQL WHERE CLAUSE1. Complex conditions: AND, OR, NOT and parentheses
2. BETWEEN: range of values - 58SQL PATTERN MATCHING (WHERE CLAUSE)1. Condition where string is compared to a pattern
2. Syntax: LIKE and NOT LIKE
3. Pattern specification:
% = any string, _ = any character (length)
3. Concatenation: using ||
4. CASE SENSITIVE - 59SQL NULL VALUES1. Missing value
2. Inapplicable
3. Use: IS NOT NULL or IS NULL
4. = and <> cannot be used - 60SQL THREE VALUE LOGIC1. TRUE, FALSE, UNKNOWN
2. Comparing to NULL = UNKNOWN
3. Return result if its TRUE (NOT false or unknonwn)
4. AND = MIN, OR = MAX, NOT = 1 - x - 61SQL ORDERINGSYNTAX:
ORDER BY [ASC | DESC] - 62SQL MULTI-RELATION QUERIES1. Combine data from more than one relation
2. Listing all the relations in FROM clause
3. Distinguish attribute of same name using DOT: . - 63SQL TUPLE VARIABLESQuery may use multiple copies of same relation, use
AS
to rename multiple tables - 64SQL SUBQUERIESSELECT CLAUSE
FROM
WHERE - 65SQL SUBQUERIES1. SCALAR subquery: return single value
2. ROW subquery: returns single row with multiple columns
3. TABLE subquery: return multiple rows with one or more columns - 66SQL SUBQUERY OPERATORS: ININ
IN
returns true if the tuple is a member of relation - 67SQL SUBQUERY OPERATORS: ALLx <> ALL ()
true if and only if for every tuple t in relation, x is not equal to t
x = ALL ()
true if and only if for every tuple in relation, x is equa to t - 68SQL SUBQUERY OPERATORS: ANY/SOMEx = SOME ()
x equals at least one tuple in the relation
"Equal to at least one" - 69SQL UNCORRELATED SUBQUERIESNested query can be converted to non-nested query using JOINs
- 70SQL CORRELATED SUBQUERIESNested query is correlated if contains reference to attribute in outer query
Correlated if it must be recomputed for every tuple produced by the outside query - 71SQL CORRELATED SUBQUERY: EXISTSSyntax:
EXISTS (), true if the is not empty
NOT EXISTS (), true if the is empty - 72SQL CORRELATED SUBQUERY: UNIQUEreturns true if the subqury contains no duplicate tuples
- 73SQL SUBQUERY SYNTAX1. ORDER BY clause may not be used
2. Number of attributes in SELECT clause of the subquery should match with number of attributes from the comparison operator
3. Column names in subquery refer to table name in the FROM clause of the subquery
4. Result of the subquery must be the right operand - 74SQL COMBINING SUBQUERYUNION
UNION
INTERSECT
INTERSECT
EXCEPT
EXCEPT - 75SQL BAG VS SET SEMANTICSRelations in SQL -> BAGS (select-from-where)
1. When doing projection, easier to avoid duplicate eliminations
2. When doing set operations, most efficient to sort the relation and hence eliminate the duplicates - 76SQL CHANGE THE DEFAULT BAG VS SEMANTICSDISTINCT
use after SELECT
SELECT DISTINCT FROM
ALL
use after set operations
SELECT FROM EXCEPT ALL - 77NORMALISATION LOSSLESS JOINGet back the original relation by joining the
decomposed relations
Lossless: BCNF - 78NORMALISATION LOSSLESS JOIN CHASE TEST1. Create tableau, for each column t, use the same letter as t in the
components that are in Ri, but subscript
the letter with i if the component is not in i
2. Apply the FDs to equate symbols: if one of the symbol is unsubscripted then
make the other the same. If both are subscripted change either to be
the other - 79NORMALISATION 3NF1. Compute Minimal Basis
2. For each FD make it into relation, remove the relation if its a subset of another relation
3. If none of the relation is superkey, add the relation consisting of the superkey
Informações
- Descrição
- Undergraduate Computer Science FlashCards sobre Cz2007 Database Flashcards, criado por Deka Auliya em 22-04-2015.
- Criado por
- Deka Auliya
- Visualizações
- 4
- Avaliação