CS 348 Intro to Database Management
Objectives
Viewpoints
- database user
- database designer
- database manager
Sub-objectives
- SQL
- data modeling
- management
- database management system (DBMS)
- Relational model
- Application programming
- Transaction and concurrency
File-Processing Systems
Disadvantages with File-Processing Systems
- Data redundancy and inconsistency
- various copies leads to higher storage
- Difficulty in accessing or modifying data
- Integritty problems
- New constraints require changing the program
- Atomicity problem
- Difficult to ensure atomic property and to restore state after failure
- Concurrency issues
- Security
Databases
Database Definition
A large and persistent collection of (more-or-less similar) pieces of information organized in a way that facilitates efficient retrieval and modification.
Database Management System (DBMS)
A program (or set of programs) that manages details related to storage and access for a database.
- Data model
- Access control
- Concurrency control
- Database recovery
Scheme Definition
A schema is a description of the data interface to the database
Database Instance Definition
A database instance is a database (real data) that conforms to a given schema ) i.e., the information stored in the database at a particular moment
Levels
- Physical
- Virtual
- External
The Relational Model
A database is a collection of relations (tables). Each relation has a set of attributes (columns). Each attribute has a name and an atomic (indivisible) domain (type). Each relation has a set of tuples (rows). Duplicate tuples are not allowed. Two tuples are duplicates if all their attributes agree.
Data Model
- structure
- operations
- constraints
Schema and Instance
author(aid:int, name:string)
publication(pubid:int, title:string)
journal(pubid, volume)
A database can be displayed tabularly with a table for each relation.1
Integrity Constraints
Tuple-Level
- Domain restrictions (e.g. type string)
- Restricting possible values (e.g.
["Winter", "Summer", "Fall"]
)
Relation-level
- Superkey: attributes where tuples will never agree on
- Candidate key: minimal superkey (minimal set of attributes that unique identify the tuple)
- Primary key: designated candidate key
Database-level
- Foreign key: an attribute of this relation is the primary key for another relation (Relation one is referencing and the second relation is referenced )
- Foreign key constraints: Foreign key must match the primary key value of a tuple in the
referenced
relation - Referential integrity constraints: foreign key cannot be a primary key in the referencing relation
The Relational Algebra
Consists of a set of operators
Operators
- one or two relations as inputs
- single output relation in terms of the input relation(s)
- can be composed to express the definition of a new relation in terms of existing relations
Selection
subset of tuples of a relation and thus schema is the same
- Find teachers who are in the physics department
- conditions include any column of R, comparisons, boolean algebra
- select applies to single row at a time, not many
Projection
- Returns a subset of a relation but only of the specified attributes
- Duplicates are eliminated
Cross Product
- Result has attributes of both input relations
- Result is the tuple for each possible pair from relation one and relation two
Conditional Join
- Perform cross product but join pairs only if the boolean involving attributes from both relations is true
Natural Join
If both relations have the same attribute, then the join will only filter out tuples that don’t have those attributes matching. During the first cross product, duplicate attributes are renamed (e.g. ID, ID -> ID, ID_1) but at the end the duplicated attributes are thrown out.
Set-Based Relational Operators
- Union: Same number of fields and corresponding fields have the same type
- Difference: Returns stuff in first relation not present in second
- Intersection: Return stuff in both rrelations
- Division: Attributes of second relation must be a subset of the first. Inverse of product. Useful for all. Example (which tuples of X always references Y but returns a new tuple without the attributes in Y).
Algebraic Equivalences
Relational Completeness
The Relational Calculus
SQL - Structured Query Language
KEYWORD (statements is implied)
- SQL Data Manipulation Language (DML)
- SELECT for queries
- INSERT, UPDATE, DELETE modify the instance of a table
- SQL Data Definition Language (DDL)
- CREATE, DROP modify the database schema
- SQL Data Control Language (DCL)
- GRANT, REVOKE enforce the security model
Schema used for Examples
Data Types
- integer or int (32 bit or 4 byte)
- smallint (16 bit or 2 bytes)
- numeric(p, q): p digit numbers with q digits to the right
- real, double precision:
- float(n): user-specified precision of at least n digits
- char(n): fixed length character strings (n is length)
- varchar(n): variable length character to a max length of n
- date: describes year, month, day
- time: describes an hour, minute, second
- timestamp: describes a date and time
- interval: allows date/time computations
Tables
create table r
(A1 D1, ..., An, Dn),
(integrity-constraint-1),
...
(integrity-constraint-k),
r: relation, A: attribute, D: data type
integrity-contraints:
primary key (A1, ..., An )
foreign key (Am, ..., An ) references r2
Queries
SQL allows duplicate tuples in relations as well as in query results. need to use
the distinct
keyword after select.
SELECT [distinct] dept_name, salary from instructor
[OPTIONAL]
To return all attributes, use an asterisk (*)
Arithmetic Expressions
You can operate on the data through the select call itself. For example, if we wanted the monthly salary instead of an annual one:
select ID, name, salary/12 as monthly_salary from instructor
Filtering (Where)
select name from instructor where dept_name = ’Comp. Sci.’
- logical connectives: and, or, and not
- comparison operations: <, <=, >, >=, =, and <> (inequality)
- comparisons can be applied to results of arithmetic expressions
Select Cross Product
Performs Cross product by specifying multiple relations
select * from instructor, teaches
Use where to join and remove duplicate attributes.
select * from instructor, teaches where (instructor.ID = teaches.ID and instructord.dept_name = 'Comp. Sci' and year = 2017)
FROM Inner Join
... from instructor inner join teaches on instructor.ID = teaches.ID...
If attributes have the same name, then both will show up with a relation.
prefix
FROM Natural Join Clause
select * from instructor natural join teaches
Be careful since this does it to all attributes. For specific attributes,
use using
select * from T join S using(A)
SELECT as
Renaming the attribute in the query result
select T.ID, T.name from instructor as T, instructor as S where T.salary > S.salary and S.ID = '12121'
String Operations
- ‘5"6’ (allows double quotes)
- ‘Datebase’ = ‘database’ (false)
- DBMS might not differentitae (MYSQL)
- concat
- to upper or to lower
- string length, extracting substrings, etc.
WHERE like
- %: match any substring
- _: match any charater (one)
- escape using
escape '%'
orescape '_'
WHERE attribute like '%pattern%'
ORDER
select name from instructor order by name asc -- default is asc
select name from instructor order by dept_name, name desc
Union
select course_id
from section
where semester=’Fall’ and year=2017
union
select course_id
from section
where semester=’Spring’ and year=2018
-- union all
select course_id
from section
where semester=’Fall’ and year=2017
union all
select course_id
from section
where semester=’Spring’ and year=2018
Aggregate Pt. 1
- avg: average value
- min: minimum value
- max: maximum value
- sum: sum of values
- count: number of values
- count (*) to count number of tuples
Usage
SELECT count(attribute), ...
- count (*) to count number of tuples
Usage
Group by: group tuples into another attribute.
select dept_name, avg(salary) group by dept_name having avg(salary) > 25000
The having condition applies on each group and not on the aggregation. The having applies before the select returns but after the grouping.
Null: null indicates unknown or missing data.
Comparing null with anything else always results in unknown even with null.
Unknown always takes precedence.
Use is null
for a null comparison
Subqueries
Use ( ) to use a temorary relation
Table Subqueries
select dept_name, avg_salary
from (
select dept_name, avg(salary) as avg_salary from instructor group by dept_name
) where avg_salary > 42000
select max (tot_salary) from (
select dept_name, sum(salary) as tot_salary from instructor group by dept_name
)
Scalar Subqueries
A query that returns a single tuple.
select name from instructor where salary >
(select avg(salary) from instructor)
Set Membership
Subquery can also be a hardcoded set
x in (subquery) /* intersect */
x not in (subquery) /* except */
select distinct name from instructor where name in (’Mozart’, ’Einstein’)
select distinct name from instructor where name not in (’Mozart’, ’Einstein’)
<> means not equal
All Clause
select name from instructor where salary >
all (select salary from instructor where dept_name = 'Comp. Sci.')
Some (at least one) Clause
select name from instructor where salary >
some (select salary from instructor where dept_name = 'Comp. Sci.')
Exists Clause
Can also use not exists
select course_id from section as S where semester = 'Fall' and year = 2017 and
exists (select * from section as T where semester = 'Spring' and year= 2018
and S.course_id = T.course_id)
Test Non-Empty Relations
Triple query.
Assert inner query is non-empty (not exists) where inner query does a search based on condition 1 which also asserts it satisfies condition 2 which could be another query.
Find all students who have taken all courses offered in the Biology department.
return student if for all biology courses available student has taken that course. -> return students where the following relation is empty (all bio courses except the ones the student has taken)
Unique
Returns true if subquery contains no duplicates (one find). Add not
before unique
for “at least twice”
find all courses that were offered at most once in 2017
For all courses, find unique instances where the course was offered in 2017
select * from course as T where unique
(select * from course as R where T.course_id = R.course_id and R.year = 2017)
Correlated Subqueries
For each tuple obtained from the outer query, compute the
inner query. Need to use the lateral
prefix to do so.
select name, salary, (select avg(salary)
from instructor where dept_name = S.dept_name) as dept_avg
from instructor as S
select name, salary, dept_avg from instructor T,
lateral (select avg(salary) as dept_avg from instructor S
where T.dept_name = S.dept_name)
With Clause
with temp_relation (list_of_attributes) as (subquery)
Find all departments where the total salary is greater than the average of the total salary at all departments
with dept_total (dept_name, value) as (select dept_name, sum(salary)
from instructor group by dept_name),
dept_total_avg (value) as (select avg(value) from dept_total)
select dept_name from dept_total, dept_total_avg
where dept_total.value > dept_total_avg.value
Data Modifications
alter table r add attribute data_typ
Existing tuples in r
will have null
as the value for the new attribute
alter table r drop A
- Not supported on many databases like
SQLite
alter table r rename column old_name to new_name
alter table r modify A data_typ
Deleting a Table
drop table instructor
Removing from Table
Remove all:
delete from instructor
delete from instructor where dept_name= ’Finance’
Delete instructors who earn less than the average
delete from instructor where salary < (select avg(salary) from instructor)
Insertion
insert into course values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4)
insert into course (course_id, title, dept_name, credits) values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4)
insert into student values (’3003’, ’Green’, ’Finance’, null)
insert into student values (’3003’, ’Green’, ’Finance’) /* omitted values will be null */
Inserting into new table from another based on some condition. Note that without a primary key on the student table, there would be a infinite tuples added.
insert into instructor select ID, name, dept_name, 18000 from student
where dept_name = ’Music’ and total_cred > 144
Updates
update instructor set salary = salary * 1.05;
update instructor set salary = salary * 1.05 where salary < 70000;
update instructor set salary = salary * 1.05 where salary < (select avg (salary) from instructor);
Update instructor salaries based on how much they make (multiple queries):
update instructor set salary = salary * 1.03 where salary > 100000;
update instructor set salary = salary * 1.05 where salary <= 100000;
/* alternative using case */
update instructor set salary = case
when salary <= 100000 then salary * 1.05
else salary * 1.03
end
SQL Intermediate Topics
Check Constraints
create table department (
...
budget numeric (12,2) check (budget > 0),
...
)
Foreign Key Contraints
create table instructor (
...
dept_name varchar(20)
...
foreign key (dept_name) references department )
Deleting in Referenced Relation
When creating a table, we can use the references R on delete set null
Cascade: on delete cascade
rows referencing key also get deleted
Views
virtual table
create view faculty as select ID, name, dept_name from instructor
We can use views to hide data
Modifying Views
create view instructor_info as select ID, name, building from instructor, department where instructor.dept_name = department.dept_name;
-- insert into instructor_info values('69987', 'White', 'Taylor'); -- won't work
Updateable Views
Access Control
Roles
Indexes
- An index is an auxiliary persistent data structure
- Search tree (e.g., B+-tree), lookup table (e.g., hash table), etc
create index ins_name_index on instructor (name);
create unique index ins_name_index on instructor (name);
drop index ins_name_index
Typically the index already exists on primary key and unique constraints.