Databases Exam 1

Front 1

What is a Database Management system?

Back 1

System software for creating and managing a database that provides:

1. Users with a way to Create, Update, Delete, retrieve, and manage data
2. Serves as an interface between end users or application programmers to ensure that data is organized and easily accessible

Front 2

What does a Database Management System manage? (3)

Back 2

1. Data

2. Database Engine

3. Database schema

provide concurrency, data integrity and uniform administrative procedures

Front 3

Why are DBs important?

Back 3

It's difficult.

A lot of research has gone into doing it the right way.

Learn how to deal with "big data" in real-time. i.e. create salable reliable systems

Front 4

What makes a database a database?

Back 4

Accessibility and concurrent access

Put a lot of info in as little space (no duplicates)

Data integrity. Modification is done across all instances.

Security.

Front 5

Why do we need DBMS'? (4)

Back 5

1. Redundancy

2. File Formats

3. Data Access

4. Physical Data Indpendence

Front 6

Redundancy

Back 6

A flat file has no optimization so that data doesn't appear more than once.

Databases optimize to reduce redundancy/duplicates to make queries run faster

Relational model - put as much data in as small a space as possible

Front 7

Multiple files, duplicate info

Back 7

You can either:
1. Put every single database on the DBMS into 1 file (easier to cross reference)
2. Break each DB into 3 files for each table (Faster.)

Front 8

Accessing Data from multiple applications

Back 8

Databases mean you don't have to rewrite new implementation each time a program needs access to the data. Standardization with SQL.

Front 9

Data Isolation

Back 9

Databases allow you to put locks on certain types of data or prevent data from being modified until certain conditions are met. This way data is kept safe and secure from rogue code or malicious input.

Front 10

Data integrity

Back 10

Databases maintain the data in a consistent and accurate way so that it isn't corrupted (through a variety of means)

Front 11

Atomicity of updates

Back 11

Databases ensure that updates are applied to all elements of affected data in a binary way. Either the transaction happens completely or not at all. There is no "in progress" which maintains the state of the database.

i.e. bank balance transfers. Either the money is moved from one account to another or it is not. There is no partial transfer and then failure.

Front 12

Concurrent Access

Back 12

Multiple users can access at the same time and it appears as if the database is only being used by one user.

Also ensures data integrity when one piece of data is accessed by the multiple users at once.

Front 13

Data Abstraction

Back 13

Databases provide levels of abstraction so that you don't have to manipulate the data on the lowest level. Provides usability.

Phsyical: Logical: View

Front 14

Physical Data Indpendence

Back 14

As an application writer you don't want to deal with how the data is actually physically stored.

Instead, we work with a MODEL of the data not the data itself. The DB abstracts away stuff we don't want to think about.

Allows us to switch implementations of code and leave the SQL or Database section intact

Front 15

Schema vs. Instance

Back 15

A schema is the logical design/description of the data.

An instance is a snapshot of the data stored in a DB at any given time.

Class vs. object in Java

Front 16

Why modeling your DB what should you think about?

Back 16

The level of detail accuracy you need. Don't want it too detailed or else it becomes bloated/excessive but it also needs to represent the real world accurately.

Front 17

What is a relation?

Back 17

A relation deals with tuples and attributes within a table (which holds rows & columns)

Think of the relation as individual data within a table (schema vs. instance)

Front 18

Super key vs. Candidate key vs. Primary key

Back 18

Super key - any attributes that can uniquely identify a tuple

Candidate key - minimal number of attributes that can uniquely identify a tuple (minimal super key)

Primary key - pick one candidate key to be the most important key: the one used to actually identify the tuple

Front 19

What makes a good primary key?

Back 19

1. Doesn't change

2. Unique

3. Non-null such that everyone has that attribute

Front 20

Relation vs. table

Back 20

Relation are the rows in the table while the table is just the schema identifying what it looks like or how the data should be laid out.

Table:

- Has an order, is a list, can have duplicates

Relation:

- No order, no duplicates, is a SET

Front 21

DDL vs. DML

Back 21

Data Definition Language

Data Manipulation Language

Both part of the SQL language;

One sets up the schema (DDL) through things like CREATE and ALTER while DML effects the instance of the table with SELECT, INSERT, UPDATE, DELETE or whatever

Same part of one query language just different functions

Front 22

Do Sailors and Bank RA examples

Back 22

Do Sailors and Bank examples

Front 23

Select (RA)

Back 23

sigma (o-)p(r) where p is the predicate and r is the relation (table)

Predicates can compare to a value or to another attribute

Limits the specific tuples or rows from a relation or table

SELECT in SQL is Project in RA

Front 24

Projection (RA)

Back 24

pi..a1,a2,..an where A is an attribute

returns the attributes (columns) from a relation

duplicates are removed

Front 25

Rename (RA)

Back 25

rho - (p)

(p)x(E) renames the result of expression E to x while (p)x(a1..a2..a3..etc.)(E) renames the attributes of that expression to a1,a2,a3, etc.

Front 26

Union (RA)

Back 26

U - combine tuples from two relations.

r U s:

1. r and s must have same degree (num attributes)

2. The corresponding domains must have a compatible type

Keyword: "or" in English

Front 27

Intersect (RA)

Back 27

r (-) s where r and s are two relations:

combine tuples that are present in both relations; same rule as union

Keyword: "and" in English

Front 28

Set Difference (RA)

Back 28

- r-s, where r and s are two relations

Find tuples that are in one relation but NOT in another

1. r and s must have same degree

2. Corresponding attribute domains must have a compatible type

Keyword: "are not"

Front 29

Cartesian (Cross) Product (RA)

Back 29

(x) r x s where r and s are relations

The attributes of the 1st relation followed by the attributes of the 2nd for all of the attributes of the 1st relation

NOT commutative

Front 30

Natural Join (Inner Join) (RA)

Back 30

Combines a selection and a cross product

1. Cross products

2. Selects forcing equality on those attributes that appear in both relation schemas

3. Removes duplicates

Keep all tuples that have the same value for the shared attributes

Front 31

Natural Join - Flo's method

Back 31

Cross product, if the columns shared by both tables match, add that row to the new one with non matching columns.

Front 32

Outer join

Back 32

Takes all tuples in the left relation that did not match with any tuples in the right relations, pads tuples with null values for all other attributes and adds them to the result of the natural join

Front 33

Outer join - Flo's interpretation

Back 33

Same as Natural Join just fills the non matching/present columns with nulls if the overlapping attribute does not match

Front 34

Aggregate Functions (RA)

Back 34

G1,G2,..Gm (G) F1(A1),F2(A2),...Fn(An) (R)

G are the attributes on which to group

F is the aggregate function on attribute

R is relation

Keyword: "of each"

Front 35

Group By

Back 35

Groups by some attribute and then applies all of the aggregate functions to that group

Front 36

Division (RA)

Back 36

r (division symbol) s where s is a subset of r

Find A for all B where A and B are attributes

All A's paired with their Bs / all possible B's

Result: List of A's

Keyword: "For all"

Front 37

Division - Flo's take

Back 37

Opposite of cross product.

If attribute from A do not have all possible B matches (as from cross product) then it is NOT added. If A has all possible B's then it IS added.

Front 38

What are ER diagrams?

Back 38

Used to design a database. Consists of entites and relationships between them (Entity-Relationship Diagram)

Front 39

Entity?

Back 39

Any object that is distinguishable from any other

- Have attributes

- Entity set is a set of entites of the same type

- Value domain: set of permitted values for each attribute

- Simple or composite: composite has multiple attributes stemming out of

- Single or multiple values: multiple phone numbers per person

- derived

- null or not null

Front 40

Relationship?

Back 40

An association between 2 or more entities

Think: participation and cardinality

Can have their own attributes represented by dashed line.

Front 41

Participation

Back 41

How many entities in the set are involved in the relationship?

Total - double edge - every entity in the entity set participates in the relationship

Partial - single edge - not every entity in the entity set required to participate

Front 42

Cardinality

Back 42

How are the entity sets involved together?

1-to-1: one entity from set A with one entity from set B. Represented by arrow both directions.

1-to-many: one entity from set A with many entities from set B. Arrow points to single side.

many-to-many: many entities from set A associated with many entities from set B.

Cardinality says NOTHING about participation. Can be ZERO.

Front 43

Recursive (self-referential) relationships

Back 43

An entity set can relate to itself in a self relating relationship. The relationship from and back need to be labeled and they are called "roles."

Primary keys renamed as roles.

Front 44

Entity sets in ER Diagrams

Back 44

Rectangle

Front 45

Relationship sets in ER Diagrams

Back 45

Diamonds w/attributes stemming off

Front 46

Cardinality in ER Diagrams

Back 46

Number of lines (single or double)

Front 47

Ellipses in ER Diagrams

Back 47

Attributes (single value)

Front 48

Double Ellipses in ER Diagrams

Back 48

Multi-valued attributes

Front 49

Dashed Elipses in ER Diagrams

Back 49

Derived attribute

Front 50

Arrow in ER Diagrams

Back 50

"one" relationship (cardinality)

Front 51

Undirected arrow in ER Diagrams

Back 51

"many" relationship

Front 52

What makes a good entity set?

Back 52

Set of entities within it that share the same type and the same properties

Front 53

What makes a good relationship?

Back 53

Is there an association between them and is it meaningful?

Front 54

When should it be an entity or attribute?

Back 54

If you need more info about it, make it an entity set. If it is the info, make it the attribute.

Front 55

Binary vs. n-ary Relationship sets

Back 55

How many entity sets are participating in this relationship?

Binary is two, anything more is n-ary

Front 56

Weak entity set?

Back 56

Not enough info contained within it to uniquely identify it. Requires a strong entity set's primary key(s) and a discriminator to be identified.

Primary key: primary key of A's and discriminator. Represented by double rectangle and double diamond (relationship). Only the double rectangle represented as table in DB.

MUST be total participation.

Front 57

Reducing ER Schema to tables

Back 57

Primary keys allow entity sets & relationships to be expressed uniformly as tables.

Database conforming to ER can be represented as a collection of tables.

Each entity set and relationship gets to be a table.

Front 58

Strong entity set to Table

Back 58

table_name(primary_key (underlined), other_attributes)

Front 59

Strong entity set with Composite Attribute to Table

Back 59

Composite attributes take on the composite first underdash higher attribute

i.e. name becomes first_name, middle_name, last_name. within the table definition.

Front 60

Strong entity set with multivalued attribute to Table

Back 60

Attribute M on an entity set E is represented by a separate table named E_M (phone in instructor becomes instructor_phone)

EM table has primary key of E and attributes of multivalued. ALL ARE PRIMARY KEYS.

Front 61

Many to Many relationship set to Table

Back 61

Primary keys of the two entity sets and any attributes on the relationship itself along with attribute of the relationship itself (dotted line to).

Front 62

One to many/many to one to Table

Back 62

IF total participation on the many side.

Primary key comes from the many side with added extra attribute the primary key of the one side.

Must be many because the one side has a lot associated with it so it doesn't discriminate.

Front 63

One to one to Table

Back 63

Either side can take the primary of the other; whichever makes more sense.

Front 64

Can you specify limits on cardinality?

Back 64

NOT in the DB. This is done programmatically instead.

Front 65

Weak entity set to Table

Back 65

All primary keys of the identifying strong entity set plus the discriminator (as primary key) and any other attributes.

Front 66

ISA (to table)

Back 66

Points to the specialization

Primary key of superclass is the primary key and then extra attributes. Each represented as a sepreate table.

Front 67

Derived attributes (in tables)

Back 67

Derived attributes are not represented in the table itself (since they are derived)

Front 68

See bank-enterprises pdf

Back 68

See bank-enterprises pdf

Front 69

RA vs. SQL functionality

Back 69

RA was for query and manipulation

SQL does table/schema definition, query and modification, transaction control, embedded SQL, integrity contstraints, authorization

Front 70

SELECT in SQL vs. in RA

Back 70

SELECT FROM is the same as Project

Front 71

WHERE in SQL vs. in RA

Back 71

Where is project and select

Front 72

Aggregate functions in SQL

Back 72

Use Group by to specify the column_name and perform the actual function in the SELECT

HAVING restricts what is returned by the aggregate function (places a condition on it)

Front 73

Review SQL (text & cheat sheets)

Back 73

Review SQL (text & cheat sheets)

Front 74

Views

Back 74

temporary table created for running queires on. Usually only necessary to save typing time/etc.

Front 75

Data Types

Back 75

Simplest way to control what kind of data is in your database. Forces all data populating an attribute to look a certain way.

Front 76

Integrity Constraints

Back 76

Makes sure data in your DB is valid.

Foreign keys make sure the data exists in another table before putting into yours.

NULL or not. Refuse to let something be entered into DB without a value.

Primary key, default values

Front 77

Checks

Back 77

A truth statement that you make about a single field in one table that is checked any time there is an insert or an update on that table.

Update not allowed if check fails. Most are numeric. Incurs time cost.

Front 78

Assertion

Back 78

An assertion checks one table and multiple attributes that oculd involve a second table.

"The sum in another table must be greater than the min in this table.

More complex and powerful

Front 79

Triggers

Back 79

Event driven, react to updates, deletes or insertions. Once this event is detected they "do something" like perform a query.

This is where building in business logic comes in handy.

Front 80

Problem with Advanced SQL business logic?

Back 80

Incurs more overhead as you get more complicated!

Front 81

Read heavy vs. write heavy database

Back 81

Be careful how many triggers used in write-heavy database as it will drastically negatively affect performance.

Front 82

Stored Procedures

Back 82

A function call. A stored procedure is a stored select or insert that you can pass parameters to.

Front 83

SQL data types (basic)

Back 83

char

varchar

int

smallint

numeric

real, double

float

Front 84

Basic Schema Definition

Back 84

CREATE TABLE r

( A1 D1,

A2 D2,

....,

An Dn, (integrity constraint),

....

);

Front 85

Basic Schema Definition Example

Back 85

CREATE TABLE course

(course_id varchar(70)

dept_name varchar(50) NOT NULL

PRIMARY KEY (course_id)

FOREIGN KEY (dept_name) references department);

Front 86

Typical SQL query form

Back 86

SELECT attribute1, attribute2, attribute(n)

FROM r1,r2,r3

WHERE P;

attributes are column names

relations are tables

P is a predicate condition for the select statement

Front 87

Renaming in SQL

Back 87

Use "as"

Front 88

Pattern matching on strings in SQL

Back 88

use LIKE operator

% indicates it can match any substring

_ matches character

Front 89

Sorting in SQL

Back 89

Use ORDER BY attribute followed by ASC or DESC depending on which you want

Front 90

Union and intersect operations

Back 90

Same as RA just with wrapping SELECT clauses

Front 91

How to find all but in SQL?

Back 91

Use the except operation

Front 92

Null values in boolean

Back 92

and - defaults to unknown unless false in which case it's false

or - defaults to unknown unless true in which case it is true (this makes sense think of how or and and works)

not - not unknown is unknown

Front 93

Basic aggregation in SQL

Back 93

perform it in the SELECT line

Front 94

Group By in SQL

Back 94

GROUP BY placed at the end with corresponding SELECT from beginning. Groups all of those matching that SELECT and then performs the aggregate function upon them

Front 95

Having in SQL

Back 95

Way to drop Grouped by rows that do not match the predicate/condition specfied by having.

Front 96

How to check if result is in a set in SQL?

Back 96

Use the "in" or "not in " keywords

Front 97

How to test if a result is greater than at least one or all of another set?

Back 97

Use the >= some or the >= all for every one

Front 98

Test for empty relations?

Back 98

Use "exists"

Front 99

"with" clause in SQL?

Back 99

with allows you to define a temporary relationship

Front 100

Deleting in SQL

Back 100

DELETE FROM r

WHERE P;

Where r is a relation(table) and P is a predicate.

Front 101

Insertion in SQL (single)

Back 101

INSERT INTO r

VALUES(values defined by table, add null if need be)

OR

INSERT INTO r (attributes named)

VALUES (corresponding attributes, rest will fill with NULL)

Front 102

Insertion in SQL (multiple)

Back 102

INSERT INTO r

SELECT attributes/constants

FROM r

WHERE P;

Same order but must specify if coming from SELECT or if constant.

Front 103

Updates in SQL

Back 103

UPDATE r

SET attribute to whatever

WHERE P;