Databases Ch1.txt

to help people keep track of things

relational database

Data: recorded facts and figures

• Information: knowledge derived from data
•                   data presented in a meaningful context
•                   data processed by summing, ordering, averaging, grouping, comparing or other similar operations.

• means of uniquiely identifying each row in a relation/table
• only one per relation
• may be a composite key
• ideal primary key is short, numeric and never changes

a primary key containing numbers that are automatically generated and assigned in the database itself

one is input and one is output

a key that consists of two or more columns

a key that determines all other columns (attributes) in a relation

a combination of one or more columns that is used to identify rows in a relation

occurs when the value of one (set of) attribute(s) determines the value of a second (set of) attribute(s)

a determinant of a functional dependency that consists of more than one attribute

recorded facts and numbers

• knowledge derived from data
• data presented in a meaningful context
• data processed by summing, ordering, averaging, grouping, comparing, etc.

• has rows and columns like a spreadsheet
• breaks databases into related things

stores a characteristic common to all rows

a row containing (recording) data for a specific instance

an occurance of a thing

contains data about a particular occurance or instance of a thing of interest

• stores data and relationships
• records data in such a way that we can produce information from the data

users, database application, database management system (DBMS), and the database

Structured Query Language (SQL)

Structured Query Language - an internationally recognized standard language that is understood by all commercial DBMS products.

Database management system - a computer program used to create, process, and administer the database.

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

• create and process forms
• process user queries
• create and process reports
• execute application logic
• control application

tells the database what the user needs to communicate - entry or input

where the database is asked a question and it returns an answer

the answer to a query in an unformatted display - generally columnar

answer generated by a query but instead of coming out in a result set, it comes out in a formatted display - through a report generator

a self-describing collection of integrated tables.  Tables are integrated because they store data about the relationships between the rows of data, self-describing because it stores a description of itself.

rules that are enforced by the DBMS

• create database
• create tables
• create supporting structures (e.g., indexes)
• read database data
• modify (insert, update, or delete) database data
• maintain database structures
• enforce rules
• control concurrency
• provide security
• perform backup and recovery

the number of people hitting the database at the same time

concurrency - the number of people hitting the database at the same time (e.g. one user's work interfering with another user's work)

• Microsoft Access - low end not for professionals
• Oracle Database
• MySQL

tables that store both data and the relationships among the data

data about data - tables contained within a database that describe the data within that database (table names, number of columns, primary keys, etc.)

• tables of user data
• metadata
• indexes
• stored procedures
• triggers
• security data
• backup/recovery data

• 1. From existing data
• 2. New systems development
• 3. Database redesign

• some identifiable thing that users want to track (e.g. customers, computers, sales, etc.)
• provides the content that goes into the relation

the structure that will become the table in the database

• rows contain data about an entity
• columns contain data about attributes of the entity
• all entries in a column are of the same kind
• each column has a unique name
• cells of the table hold a single value
• the order of the columns is unimportant
• the order of the rows is unimportant
• no two rows may be identical

• two
• 2 tables = 1 referential integrity constraint
• 3 tables = 2 referential integrity constraints
• 4 tables = 3 referential integrity constraints etc.

A value cannot exist in a foreign key if it does not exist in the primary key.