Midterm Vocabulary Flashcards
What is a database?
an organized collection of structured data
what is a database management system?
a program that allows us to manage efficiently a large DB and allows data to persist over long periods of time
data model
abstraction that describes the data
schema
describes a specific database using the language of the data model
query language
high level language to allow a user to store and retrieve data from the DB
relation
a table
domain
what values are allowed (each attribute has an atomic type)
instance of a relation
a set of tuples or records
instance of a database
a collection of relation instances
what is the difference between a schema and an instance?
schema is the type, stable over long periods (static)
instance is the value, changes constantly
SQL
structured query language, used to query and manipulate data
it is a declarative language
primary key
a minimal subset of attributes that is a unique identifier of tuples in a relation
unique key
a subset of attributes that uniquely define a row
what can we use in where clauses?
attribute names, comparison operators, arithmetic operations, AND OR NOT, operations on strings, pattern matching, special functions for comparing dates and time
foreign key
similar to a pointer that directs to information in another table
how are referenced attributes declared?
must be declared as primary key or unique
what are the two violations between foreign key constraints (let R and S be relations where attributes of S are the primary key of R)?
- an insert or update to R introduces values not found in S
- a deletion or update to S causes some tuples of R to dangle
what are the 3 ways to enforce foreign key constaints?
reject, cascade update/delete, set NULL
reject
insert/delete/update is rejected
cascade update
update propagates to the tuples that reference it
cascade delete
the deletion propagates to the tuples that reference it
set NULL
when a delete/update occurs, the values that reference the deleted tuple are set to NULL
uncorrelated subquery
the inner query doesn’t depend upon outer query for its execution (can complete execution on its own)
correlated subquery
inner query depends upon the outcome of the outer query in order to perform its executions
different NULL contextes
- missing value
- inapplicable
entity
an object distinguishable from another object
entity set
a collection of similar entities
functional dependencies
a form of constraint
Armstrong Axiom 1
reflexivity
every trivial FD is true
ex. A,B -> A,B
Armstrong Axiom 2
augmentative
adding attributes in dependencies does not change the basic dependencies
ex. A->B implies A,C->B,C
Armstrong Axiom 3
transitivity
ex. If A->B and B->C, then A->C
attribute closure
If X is an attribute set, the closure X+ is the set of all attributes B s.t X->B
superkey
a set of attributes that determines another attribute in a relation
key
a minimal superkey
decomposition
taking a relation and creating subrelations
what should a good decomposition achieve?
- minimize redundancy
- avoid information loss (lossless-join)
- preserve the FDs (dependency reserving)
- ensure good query performance
how do you check for lossless join decomposition?
chase algorithm
dependency preserving
a decomposition is dependency preserving if by enforcing F1 over R1 and F2 over R2, we can enforce F over R where F = F1 and F2
Boyce Codd Normal Form (BCNF)
a relation is in BCNF when if X is a superkey in R, R is in BCNF
is normalization always good?
no, might produce unacceptable performance loss
relational query language
allow the manipulation and retrieval of data from a database
what are the two types of query languages?
declarative and procedural
declarative language
describe what a user wants, rather than now to compute it
procedural language
operational, useful for representing execution plans (relation algebra)
relational algebra
an algebra whose operands are relations or variables that represent relations
what operations are symmetric?
union, cross product, intersection, theta join, equi join, natural join
theta join
cross product followed by a selection
equi join
selection only contains equalities
natural join
equi join on all common fields (attributes with same name)
semi join
natural join followed by projection on the attributes of R1
selection
selection for certain condition
projection
gets distinct chosen attributes
one to one
a record in one entity is associated with exactly one record in another entity
ex. each capital only has one country, and each country only has one capital
one to many
a record in one entity is associated with more than one record in another entity, but not the other way around
ex. each mother has more than one child, but each child only has one mother
many to many
a record in one entity is associated with more than one record in another entity and vice versa
ex. each author has more than one book and each book can have more than one author
weak entity
an entity set that can only be identified uniquely by considering some of its attributes with the primary key of another entity
restrictions on weak entity sets
the owner entity set, and the weak entity set must have a one-to-many relationship (an owner is associated with one or more weak entities, but each weak entity has a single owner)
what are the three approached to create entity set subclasses
- create a relation for each class with all its attributes
- create one relation for each subclass with only the key attribute(s) and attributes attached to it
- create one relation; entities have null in attributes that do not belong to them
what is a view in sql?
allows you to create a virtual table based on an SQL query referring to other tables in the database
