Database Test 3

Data Definition Language (DDL)

Commands that define a database, including creating, altering, and dropping tables and establishing constraints

Data Manipulation Language (DML)

Commands that maintain and query a database

Materialized view

Copy or replication of data Data actually stored Must be refreshed periodically to match corresponding base tables

Mapping many-to-many

Create a new relation with the primary keys of the two entities as its primary key

Second normal form

1NF PLUS every non-key attribute is fully functionally dependent on the ENTIRE primary key; Every non-key attribute must be defined by the entire key, not by only part of the key; No partial functional dependencies

Third normal form

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

Dynamic View

A "virtual table" created dynamically upon request by a user No data actually stored; instead data from base table made available to user Based on SQL SELECT statement on base tables or other views

physical file

A named portion of secondary memory allocated for the purpose of storing physical records

Catalog

A set of schemas that constitute the description of a database

Candidate key

A unique identifier. One of the candidate keys will become the primary key; Each non-key field is functionally dependent on every candidate key.

boolean operators

AND, OR, and NOT Operators With parentheses...these override the normal precedence

Dynamic SQL

Ability for an application program to generate SQL code on the fly, as the application is running

Domain Constraints

Allowable values for an attribute

Data Control Language (DCL)

Commands that control a database, including administering privileges and committing data

SQL commands for transactions

BEGIN TRANSACTION/END TRANSACTION Marks boundaries of a transaction COMMIT Makes all updates permanent ROLLBACK Cancels updates since the last COMMIT

list partitioning

Based on predefined lists of values for the partitioning key

Mapping Weak entities

Becomes a separate relation with a foreign key taken from the superior entity

Multivalued Attribute

Becomes a separate relation with a foreign key taken from the superior entity

Field data Integrity

Default value-assumed value if no explicit value Range control-allowable value limitations (constraints or validation rules) Null value control-allowing or prohibiting empty fields Referential integrity-range control (and null value allowances) for foreign-key to primary-key match-ups

vertical partitioning

Distributing the columns of a logical relation into several separate physical tables

horizontal partitioning

Distributing the rows of a logical relation into several separate tables

nonunique (secondary) index

Done for fields that are often used to group individual entities (e.g. zip code, product category)

Simple Attributes

E-R attributes map directly onto the relation

types of file organizations

Heap - no particular order Sequential Indexed Hashed

clustering files

In some relational DBMSs, related records from different tables can be stored together in the same disk area Useful for improving performance of join operations Primary key records of the main table are stored adjacent to associated foreign key records of the dependent table

Embedded SQL

Including hard-coded SQL statements in a program written in another language such as C or Java

Data integrity

Mechanisms for implementing business rules that maintain integrity of manipulated data

First Normal form

No multivalued attributes Every attribute value is atomic All relations are in this form

Entity Integrity

No primary key attribute may be null. All primary key fields MUST contain data values.

types of subqueries

Noncorrelated-executed once for the entire outer query Correlated-executed once for each row returned by the outer query

Database

Organized collection of logically related data

query optimization

Parallel query processing-possible when working in multiprocessor systems Overriding automatic query optimization-allows for query writers to preempt the automated optimization

range partitioning

Partitions defined by range of field values Could result in unbalanced distribution of rows Like-valued fields share partitions

hash partitioning

Partitions defined via hash functions Will guarantee balanced distribution of rows Partition could contain widely varying valued fields

Data manipulation

Powerful SQL operations for retrieving and modifying data

Data Normalization

Primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data; The process of decomposing relations with anomalies to produce smaller, well-structured relations

Mapping one-to-one

Primary key on mandatory side becomes a foreign key on optional side

Mapping one-to-many

Primary key on the one side becomes a foreign key on the many side

routines

Program modules that execute on demand

advantages to database approach

Program-data independence Planned data redundancy Improved data consistency Improved data sharing Increased application development productivity Enforcement of standards Improved data quality Improved data accessibility and responsiveness Reduced program maintenance Improved decision support

Physical database design

Purpose-translate the logical description of data into the technical specifications for storing and retrieving data Goal-create a design for storing data that will provide adequate performance and ensure database integrity, security, and recoverability

Referential Integrity

Rules that maintain consistency between the rows of two related tables. rule states that any foreign key value (on the relation of the many side) MUST match a primary key value in the relation of the one side. (Or the foreign key can be null)

clauses of the select statement

SELECT List the columns (and expressions) to be returned from the query FROM Indicate the table(s) or view(s) from which data will be obtained WHERE Indicate the conditions under which a row will be included in the result GROUP BY Indicate categorization of results HAVING Indicate the conditions under which a category (group) will be included ORDER BY Sorts the result according to specified criteria

tablespace components

Segment - a table, index, or partition Extent-contiguous section of disk space Data block - smallest unit of storage

Purpose of SQL Standard

Specify syntax/semantics for data definition and manipulation Define data structures and basic operations Enable portability of database definition and application modules Specify minimal (level 1) and complete (level 2) standards Allow for later growth/enhancement to standard

indexed file organization

Storage of records sequentially or nonsequentially with an index that allows software to locate individual records

SQL

Structured Query Language - often pronounced "Sequel" The standard for relational database management systems (RDBMS)

User-defined data types (UDT)

Subclasses of standard types or an object type

data dictionary facilities

System tables that store metadata Users usually can view some of these tables Users are restricted from updating them

Data structure

Tables (relations), rows, columns

file organization

Technique for physically arranging records of a file on secondary storage

Schema

The structure that contains descriptions of objects created by a user (base tables, views, constraints)

Functional dependency

The value of one attribute (the determinant) determines the value of another attribute

Denormalization

Transforming normalized relations into non-normalized physical record specifications

unique (primary) index

Typically done for primary keys, but could also apply to other unique fields

Composite Attributes

Use only their simple, component attributes

outer join

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

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

join

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

Database Management System (DBMS)

a software system that is used to create, maintain, and provide controlled access to user databases

index

a table or other data structure used to determine in a file the location of records that satisfy some condition

insertion anomaly

adding new rows forces user to create duplicate data

insert statement

adds one or more rows to a table

count

aggregate function used to find totals

alias

an alternative column or table name

natural join

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

design tools

automated tools used to design databases and application programs

repository

centralized storehouse of metadata

Modification anomaly

changing data in a row forces changes to other rows because of duplication

CREATE TABLE LIKE

create a new table similar to an existing one

metadata

data that describes the properties and context of user data

relational database

database technology involving tables (relations) representing entities and primary/ foreign keys representing relationships

deletion anomaly

deleting rows may cause a loss of data that would be needed for other future rows

advantages of partitioning

efficiency: Records used together are grouped together Local optimization: Each partition can be optimized for performance Security: data not relevant to users are segregated Recovery and uptime: smaller files take less time to back up Load balancing: Partitions stored on different disks, reduces contention

data model

graphical diagram capturing nature and relationship of data

Foreign Keys

identifiers that enable a dependent relation (on the many side of a relationship) to refer to its parent relation (on the one side of the relationship).

union join

includes all data from each table that was joined

disadvantages of partitioning

inconsistent access speed: Slow retrievals across partitions Complexity: Non-transparent partitioning Extra space or update time: Duplicate data; access from multiple partitions

update statement

modifies data in existing rows

Vector aggregate

multiple values returned from SQL query with aggregate function (via GROUP BY)

tablespace

named logical storage unit in which data from multiple tables/views/objects can be stored

relation

named, two-dimensional table of data; Columns correspond with attributes. Rows correspond with entity instances and with many-to-many relationship instances.

entity

noun describing a person, place, object, event, or concept; composed of attributes

database administrators

personnel responsible for maintaining the database

subquery

placing an inner query (SELECT statement) inside an outer query

disadvantages of file processing

program-data dependence, duplication of data, limited data sharing, lengthy development times, excessive program maintenance

views

provide users controlled access to tables

well-structured relation

relation that contains minimal data redundancy and allows users to insert, delete, and update rows without causing data inconsistencies; goal is to avoid anomalies

Procedures

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

Triggers

routines that execute in response to a database event (INSERT, UPDATE, or DELETE)

functions

routines that return values and take input parameters

scalar aggregate

single value returned from SQL query with aggregate function

Field

smallest unit of application data recognized by system software

application programs

software using the data

ALTER TABLE

statement allows you to change column specifications

DROP TABLE

statement allows you to remove tables from your schema

base table

table containing the raw data

user interface

text, graphical displays, menus, etc. for the user

Primary Keys

unique identifiers of the relation. Examples include employee numbers, social security numbers, etc. This guarantees that all rows are unique.

inner join

will only return rows from each table that have matching rows in the other.