CIS 323 Exam 2

CIS 323 with Eric Sutherland. Based on the slides/textbook

relation

relation

named, two-dimensional table of data. consists of rows (records) and columns (attributes)

requirements for a relation

• unique name

• be atomic

• row must be unique

• attributes must have unique names

• order of columns/rows are irrelevant

primary keys

unique identifiers of a relation

foreign keys

identifiers that enable a dependent relation

domain constraints

allowable values for an attribute

entity integrity

no primary key attribute may be null. all primary key fields must contain data values.

referential integrity

rule that states that any foreign key value must match a primary key value in the relation of the one side

data normalization

primary tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data

first normal form

no multivalued attributes; every attribute value is atomic

second normal form

1NF plus every non-key attribute is fully functionally dependent on the entire primary key

third normal form

2NF plus no transitive dependencies (functional dependencies on non primary key attributes)

relational database management systems (RDBMS)

a database management system that manages data as a collection of tables in which all relationships are represented by common values in related tables

benefits of a standardized relational language

• reduced training costs

• productivity

• application portability

• application longevity

• reduced dependence on a single vendor

• cross-system communication

SQL data types

• strings

• binary

• number

• temporal

• boolean

SQL environment

• catalog - description of the database

• schema - contains desc. of objects created by the user (tables, views, constraints)

• data definition language (DDL) - creating, altering, dropping tables; establishing constraints

• data manipulation language (DML) - maintain and query a database

• data control language (DCL) - control a database; administering privileges and committing data

join

relational operation that causes two or more tables with a common domain to be combined into a single table or view

equi-join

a join in which the joining condition is based on equality between values in the common columns; common columns appear redundantly in the result table

natural (inner) join

an equi-join in which one of the duplicate columns is eliminated in the result table

outer join

join in which rows that do not have matching values in common columns are nonetheless included in the result table

union join

includes all data from each table that was joined

subquery

placing an inner query (SELECT statement) inside an outer query. they can be non-correlated (executed once for the entire outer query) or correlated (executed once for each row returned by the outer query)

dynamic view

a “virtual table“ created dynamically upon request by a user. no data is actually stored; instead data from base table made available to the user

materialized view

copy or replication of data, data is actually stored. must be refreshed periodically to match corresponding base tables

advantages of dynamic views

• simplify query commands

• assist with data security

• enhance programming productivity

• contain most current base table data

• use little storage space

• provide customized view for user

• established physical data independence

• use processing time each time view is referenced

• may or may not be directly updateable

routines

program modules that execute on demand

functions

routines that return values and take input parameters

procedures

routines that do not return values and can take input or output parameters

triggers

routines that execute in response to a database event

data dictionary facilities

• system tables that store metadata

• users usually can view some of these tables

• users are restricted from updating them

SQL extensions

• user-defined data types (UDT) - subclasses of standard types or an object type

• analytical functions (for OLAP)

• new data types

• CREATE TABLE LIKE

• MERGE

• programming extensions

data structure

tables (relations), rows, columns

data manipulation

powerful SQL operations for retrieving and modifying data

data integrity

mechanisms for implementing business rules that maintain integrity of manipulated data

fat client

client PC that is responsible for processing presentation logic, extensive application and business rules logic, and many DBMS functions

thin client

an application where the client (PC) accessing the application primarily provides the user interfaces and some application processing, usually with no or limited local data storage

web application components

• database server - hosts DBMS

• web server - receives and responds to browser requests using HTTP protocol

• application server - software building blocks for creating dynamic web sites

• web browser - client program that sends web requests and receives web pages

middleware

software that allows an application to interoperate with other software without requiring user to understand and code low-level operations

application program interface (API)

routines that an application uses to direct the performance of procedures by the computer’s operating system

atomic (ACID rules)

transaction cannot be subdivided

consistent (ACID rules)

constraints don’t change from before transaction to after transaction

isolated (ACID rules)

database changes not revealed to users until after transaction has completed

durable (ACID rules)

database changes are permanent

concurrency control

the process of managing simultaneous operations against a database so that data integrity is maintained and the operations do not interfere with each other in a multi-user environment

controlling concurrent access problems

simultaneous access to data can result in interference and data loss

controlling concurrent access solution - concurrency control

managing simultaneous operations against a database so that data integrity is maintained and the operations do not interfere with each other in a multi-user environment

serializability (concurrency control technique)

finish one transaction before starting another

locking mechanisms (concurrency control technique)

data that is retrieved for the purpose of updating is locked for the updater

shared lock

read but no update permitted. used when just reading to prevent another user from placing an exclusive lock on record

exclusive lock

no access permitted. used when preparing to update

deadlock

an impasse that results when two or more transactions have locked common resources, and each waits for the other to unlock their resources

versioning

optimistic approach to concurrency control instead of locking; the system will create a new version of a record instead of replacing the old one

database security

protection of the data against accidental or intentional loss, destruction, or misuse