Lecture 3: Relational Databases Flashcards
What is a relational DB
- Popular database type
- Each relation is a table. Relational databases are organized as tables
- Tables: Rows, Columns
- Poweful tool for storing and accessing data
- Simple data representation
- Ease to customise
- Using SQL
- Implemented in most popular DBMS
What do the columns contain?
Fields / attributes (field names)
What do the rows contain?
Records / tuples
What is an attribute domain?
Data type = allowed value, bijv. ’integer’, ‘string’, ‘real number’
Can tuples be identical?
No
Which two concepts exist on the model level of a DB?
Schema vs. instance
Explain schema
meta-data = column head for the data, i.e. how data has to be structured at the logic level.
Specifies name of table, attributes and the attribute domains.
Rarely changes (high costs to update!)
Explain instance
Tuple, content of the data
Changes regularly and easy, but conforms to schema
Define keys
A set of one or more attributes (columns) in the
table that have certain properties
More formally = Set or combination of attributes in table that have certain properties.
Define compound key
Key that includes two or more attributes (and can be used to look up tuples)
Define superkey
“Unique key.” Any set of attributes of columns which help to identify rows in a table uniquely. No two tuples have the same values. Can uniquely identify a specific tuple in a table (for example in enrollment database the SID and CID)
Define cadidate key
Minimum super key
Define primary key
Used to uniquely identify or find the records in a table. A table can have only one primary key.
– Every tuple in a relational database has its own
primary key
- Primary key cannot be null!
Define secondary key
Key used to lookup tuple, but may not guarantee uniqueness
Define foreign key
Key in another table. Used to locate records in another table.
Key is used a as constraint rather that to find a tuple
What is an index?
Database structure making it easier to find tuple based on the values of one or more tuples (is not a key!, but closely related).
For example: use name to find SID. “Attributes most likely needed to search” (because nobody knows their SID, but do know their name). Then you place an index on the attribute “Name”. It takes time to create, so only put indexes on those attributes most likely needed.
What is a constraint?
Restriction on data allowed in database. It is not part of the database.
Define basic constraint
Constraint related to particular attribute. Bijv. make attribute required (not NULL), or attribute domain (cannot put string in integer field)
Check constraint
Evaluates Boolean expression to see if data is allowed in the database (true or false).
- Attribute-level check constraint validates single column (bijv. age > 0).
- Table-leve check constraint can be placed on more of tuple’s attributes to see if the data is valid
Primary key constraint
No two records can have the same value for primary key attribute of a table (and not NULL)
Is called “entity integrity”
Foreign key constraint
Key in another table. Used to locate records in another table. “Referential integrity constraint”
Constraint requires that a tuple’s value in one or more attributes in one table must match value in another table (i.e. reference table, often the primary key in the foreign table)
What does the DB operation ‘projection’ mean?
Drops attributes from a table or selection (bijv. list only student’s names and not their ages)
What does the DB operation ‘union’ mean?
Combines tables with similar columns and remove duplicates (bijv. all students enrolled in this course ever)
What does the DB operation ‘intersection’ mean?
Find records that are the same in two tables
What does the DB operation ‘difference’ mean?
Selects tuples in one table that that are not present in second table
What does the DB operation ‘Cartesian product’ mean?
Creates new table containing all tuples in first tables combined with every tuple in second table (see slide 1,2,3 x A,B,C)
The join operation a Cartesian product only if tuples meet some conditions
What does the DB operation ‘divide’ mean?
Opposite of Cartesian product. Use one table to partition tuples in another table (so go back to 1,2,3 x A,B,C)
