Exam 1 Flashcards
Database
Interrelated collection of data
Why databases
Easy for software engineers to manage datasets.
They are efficient and robust
DBMS
Application that facilities inserting, deleting, updating, and querying data within a database.
Why DBMS
Users don’t need to know how data is physically stored. They only need to see simple views of the data.
Keeps data in a good state
Efficient
DML
Data manipulation language
Either declarative or procedural
SQL = declarative
relational algebra = procedural
DDL
Data Definition Language
provides the ability to define the structure (schema) of data
Identify data types, provide data constraints, specify referential integrity, make assertions, define auth levels
SQL
Structured Query Language
select
filters tuples from a relation
σ (predicate) Relation
project
filters columns from a relation
π (attr1, attr2, …) Relation
join
Creates pairs from like attributes
Relation ⨝ (attr) Relation
Union
found in either
relation U relation
query1 union query2
add ALL to keep all duplicates
query1 UNION ALL query2
Intersection
Found in both
Relation ∩ Relation
query1 intersect query2
Set Difference
provides set based filtering
A - B =
set that contains those elements of A that are NOT in B
query1 except query2
And
this sign ^
AND in sql
Or
V
schema
defines how records can be described by identifying attributes and their primary keys
ex) Course_offering(ID, name, credits)
Relation Instance
Specific instance of such a table
A singular view of it
Attribute Domain
Describes the possible range of values an attribute can take
ex) student year {fr, so, jr, sr}
Super Key
Set of attributes that uniquely identify tuples within a relation.
No 2 tuples in the relation may have the same value for the key
Primary Key
Key chosen by the DB designer as the principal means of uniquely identifying tuples within a relation.
No 2 tuples in the relation may have the same value for the key
Can’t have 2 students whose ids are the same
Foreign Key
A relation may include the primary key of another relation. Foreign keys are used to reference the other relation
May have 0, 1, or Many
Functional Dependencies - what
An understanding that you can determine the values for one set of attributes from another
ex) an employees SSN will allow us to determine their salary
Functional Dependencies - why
Whenever a change is made, we must check all the functional dependencies that exist to determine if we should force the modification to fail.
Must preserve the functional dependencies at all cost!
TLDR
-Data integrity (avoid anomalies)
-Normalization (condensing tables into smaller, more efficient tables)
-efficient database design (avoid redundancy)
Query Types
Select, Insert, Delete, Update
select
Basic Select Query Structure
SELECT ___ FROM ___ WHERE ___
Insert
Insert into tableName (attr1, attr2, attr3) values (“value1”, “value2”, value3);
Delete
DELETE FROM tableName WHERE attr1 = value1 or attr2 > value2;
DELETE FROM tableName; will delete all rows in tableName
Update
UPDATE tableName SET attr1=value1 WHERE attr2>value2
without a where clause all records are updated
UPDATE employees SET salary=salary*1.04
give every employee a 4% raise
where
Identify any desired restrictions
group by & having
group by allows you to preform aggregation on groups of tuples that have similar values
ex)
select dept_name, count(*) as numStudents
from student group by dept_name
having numStudents > 2;
predicates cam be applied to the results of group by using HAVING
aggregation
avg()
min()
max()
sum()
count()
