Week 3 Flashcards
What does UNION do?
What does DIFFERENCE do?
What does entity integrity mean?
What does relational integrity mean?
What are superkeys?
An attribute or set of attributes which identify a particular tuple.
Their only property is that they are unique.
What are candidate keys?
These are a subset of superkeys that have no subset that can be a superkey.
They are also referred to as MINIMAL superkeys.
A primary key is chosen from the candidate keys.
What are the attributes needed to allow a union between relations?
What are secondary keys?
What does SELECT/RESTRICT do? Notation
Returns a horizontal subset within a relation.
Sigma
What does PROJECT do? Notation?
Returns a vertical subset in a relation.
Pi
Are candidate keys superkeys? Whats the difference?
Yes. Candidate keys have no redundant components. All the components are required to indentify whatever the candidate key is for.
What does a relation consist of?
- Heading
- Body
What is a relation heading/relational schema?
Consists of all fixed set of attributes for relation R
What are tuples?
Instances of the heading,
What is a relation body/instance
Consists of a time varying set of n-tuples.
What is referred by relation cardinality?
The number of tuples
What is the relation degree?
The number of valies in tuple (number of attribute)
What are the fundamental relation properties?
- NO duplicate tuples
- Tuples are UNORDERED within a relation
- NO ordering of attributes within a tuple
What are the fundamental relation properties?
- NO duplicate tuples
- Tuples are UNORDERED within a relation
- can only be accesed via data within tuple
- NO ordering of attributes within a tuple
- Tuple values are atomic (cant be divided)
Are tables and relations the same?
NO, tables are visual/tabula representations of relations. THEY ARE NOT THE SAME.
Are multivalued/repeating attributes allowed in a tuple?
No
What is functional dependancy?
When one attribute determines another attribute.
Attribute A can be be used to determine attribute B.
Attribute A = unique
What are alternate keys?
These are candidate keys that were NOT chosen as PKs
What are alternate keys?
These are candidate keys that were NOT chosen as PKs
What is a surrogate key?
This is an attribute that is soley present to act as a primary key.
Should primary keys be free of semantics?
Yes, they should not provide any additional information (especially important info)
E.g. Although TFN could be a PK, best not to use TFN in a public setting as it is private info.
What is a NULL?
This refers to when there is NO VALUE.
NOTE: 0 IS NOT A NULL VALUE
Do NULLs exist in the relational model?
NO, they were only implemented by the relation database system.
What is the naming convention for relations?
- Name is all caps and singular
- PK is start of tuple and underlines
- Rest of attributes NOT ordered
Is there redundant data in a relational database?
YES, a minimal amount in foreign keys/composite keys.
What are the functions of a PK?
- To ensure that a tuple is unique
- To act as a foreign key to relate (logical connection between) tuple
What are the rules for FKs?
- They MUST point to a COMPLETE PK
- If no match with full PK then NULL
What are the rules for FKs?
- They MUST point to a COMPLETE PK
- If no match with full PK then NULL
Do FKs need the same name as PKs?
No, they can have different names.
What is domain/column integrity?
All values in a given column must come from the same domain (same datatype and within the range)
What does a “closure” property refer to?
Queries on relations produce relations.
What does a “closure” property refer to?
Queries on relations produce relations.
What is procedural?
You have to specify the instructions as well as the order of instructions.
What are the basic operations in relational algebra?
single relation: selection, projection
two relations:
- cartesian product, join
- union
- intersection
- difference
- division
What does JOIN do?
Combines data from two or more relations based on a common attribute or attributes
Types of joins?
- Theta (generalised join)
- Equi
- Natural
What is a theta join?
RELATION.attribute F RELATION2.attribute
F is an operator ( etc.)
What is a natural join?
Projection of the results from an equijoin ???
What is an equijoin?
A join where two attributes are made to be equal
What is a canonical query?
What is a canonical query?
Ways to be more efficient when querying?
Grabbing data as early as possbile
