COMPSCI345 Midterm

database

a collection of data with a specific structure

DBMS

Database Management System

Create Operation

CREATE

Read Operation

SELECT

Update Operation

INSERT, UPDATE

Delete Operation

DELETE

Relational Database

organized as records in tables and relationships between them, with each row being unique

CAP Theorem

a system must choose between Consistency and Availability

Consistency

read requests get the most current view (Relational DBs)

Availability

system provides a non-error response to requests (non-relational DBs)

Network partition tolerance

degree to which a system operation continues despite data interruption

Select query (SQL)

SELECT [attrs] FROM [tbl];

Create table (SQL)

CREATE TABLE [tblname] ( [attrname] [attrtype] [constraints] );

Insert (SQL)

INSERT INTO [tbl] ([attrs]) VALUES [vals];

WHERE clause (SQL)

SELECT … WHERE [constraints];

LIKE operator (SQL)

SELECT … WHERE [attr] LIKE [string/wildcard];

M:N (ER Model)

requires “bridge” table because RDBs only allow implementation of 1:1 and 1:M

Weak Entity Implementation (ER Model)

[Customer PK cid], [Loan PK cid,lid]

Weak Entity Implementation (SQL)

CREATE TABLE [tblname] ( … FOREIGN KEY [attrname] REFERENCES [tbl] [attrname] );

Primary Key Constraint

specifies uniqueness (each row has 1 unique non-null PK)

Foreign Key Constraint

implements a relationship between tables, commonly 1:M (M side FK references 1 side PK)

UNIQUE constraints

CREATE TABLE [tblname] ( … UNIQUE [attrs] );

CHECK constraints

CREATE TABLE [tblname] ( ... CHECK [constraint] );

CASCADE constraints

CREATE TABLE [tblname] ( … [ON DELETE/UPDATE] [CASCADE/SET NULL] );

COUNT ([attr])

gives count of column rows (or other)

SUM ([attr])

gives sum of column values (or other)

AVG ([attr])

gives average of column values (or other)

GROUP BY [attr];

groups output by attribute

HAVING [aggregation] [constraints];

applies constraints to groups in GROUP BY clause

Join query (SQL)

SELECT [attrs] FROM [tbls] WHERE [attr] = [attr];

Left Outer Join

SELECT [attrs] FROM [tblA] LEFT OUTER JOIN [tblB] ON tblA.[attr] = tblB.[attr];

Right Outer Join

SELECT [attrs] FROM [tblA] RIGHT OUTER JOIN [tblB] ON tblA.[attr] = tblB.[attr];

Full Outer Join

SELECT [attrs] FROM [tblA] FULL OUTER JOIN [tblB] ON tblA.[attr] = tblB.[attr];

Subquery

SELECT … WHERE [varname] IN ( [subquery] );

IN

checks for membership

ANY

used with comparison operator, any record that meets condition

ALL

used with comparison operator, all records must meet condition

EXISTS

checks for existence

Uncorrelated query

subquery does not reference outer query’s tables

Correlated query

subquery references outer query’s tables