Ch 1 & 2 Database Concepts

Function of DBMS

- Create databases - Create tables - Create supporting structures - Read database data - Modify database data (insert, update, and delete) - Maintain database structures - Enforce rules - Control concurrency - Provide security - Perform backup and recovery

Basic components of database system

- Users - Database Application - Database Management Systems (DBMS) - Database

Row Properties

- contain data about an entity - No two rows may be identical

Column Properties

- contain data about attributes of the entity - has a unique name - All entries in a column are of the same kind

Normalization Process

1. Identify all candidate keys 2. Identify all functional dependencies 3. Examine determinants of the F.D.

Metadata

Data about the structure of a database

Relational Database

Stores information in tables. Each informational topic is stored in its own table. Relational database will break-up a list into several parts.

SQL

Structured Query Language - an international standard for creating, processing and querying databases and their tables

Determinant

The attribute (or attributes) that we use as the starting point (the variable on the left side of the equation)

Self describing

The description of the structure of the database is contained within the database

Normalization

The process of analyzing a relation to ensure that it is well formed. Rows can be inserted, deleted, or modified without creating update anomalies.

Referential Integrity

a database concept that ensures that relationships between tables remain consistent, every value of a foreign key must match a value of an existing primary key.

Composite Key

a key that contains two or more attributes.

Database Applications

a set of one or more computer programs that serves as an intermediary between the user and the DBMS

Relation

a two-dimensional table, rows and columns, that has specific characteristics.

Candidate Key

a unique key that is a candidate to become the primary key.

Referential Integrity Constraints

ensure that the values of a column in one table are valid based on the values in another table.

Primary Key

is a candidate key chosen to be the main key for the relation.

Foreign Key

is a primary key from one table placed into another table.

Surrogate Key

is a unique, numeric value that is added to a relation to serve as the primary key.

Null Value

means that no data was entered.

Functional Dependency

relationship between attributes in which one attribute (or group of attributes) determines the value of another attribute in the same table EX. price of one cookie can determine the price of a box of 12 cookies (Cookies, Qty) --->BoxPrice

Entity

represents one theme or topic.

Database Management System (DBMS)

serves as an intermediary between database applications and the database.

Unique Key

the key uniquely identifies a row.

Users

use a database application to track things; use forms to enter, read, delete and query data; and produce reports

SQL Select Statement

SELECT Column Name FROM Table Name WHERE Column Name = Operator Value

Well-Formed Relation

Every determinant must be a candidate key. Any relation that is not well formed should be broken into two or more well-formed relations.

Table

Holds data about one and only one theme

List

In a list, each row is intended to stand on its own. As a result, the same information may be entered several times.

Relational Model

Methodology that most commercial databases are based on