Database Fundamentals

Data

Collected facts about a topic or item

Information

The result of combining, comparing and performing calculations on data

Database

Is a centralized and structured set of data stored on a computer system

Provides facilities for transforming retrieved data into useful information.

Relational database

stores information in tables with rows and columns.

Table

collection of records.

Row

called a record (or instance)

Column

referred to as a field (or attribute)

Database Management System (DBMS)

a software that controls the storage, organization and retrieval of data.

Hardware

physical parts of computer

Software

instructions to tell hardware what to do

OS

software that directly controls the hardware

Application

performs specific task

Client

workstation used by end users

Server

accepts work requiring more power from clients

1960s

Computers become cost effective for private companies, and storage capacity increase.

1970-72

E. F. Codd proposes the relation model for databases, disconnecting the logical organization from the physical storage.

1976

P. Chen proposes the entity relationship model (ERM) for database design.

Early 1980s

The first commercially-available relational database systems start to appear at the beginning of the 1980s with Oracle Version 2.

Mid-1980s

SQL (structured query language) becomes "intergalactic standard”

Early 1990s

An industry shakeout begins with fewer surviving companies. Oracle survives

Mid-1990s

Kaboom! The usable Internet/World Wide Web (WWW) appears. A mad scramble ensues to allow remote access to computer systems with legacy data.

Late 1990s

The large investment in Internet companies helps create a tools-market boom for Web/Internet/DB connectors.

Early 21st century

Solid growth of DB applications continues.

Data modelling

begins by researching the information requirements of a business.

entity relationship diagram

should completely capture and accurately model the organization’s information needs and support the functions of the business

primary key (PK)

is the unique identifier for each row of data

foreign key (FK)

links data in one table to the data in a second table by referring to the PK column in the second table

Nulls

indicates if a column must contain a value (mandatory)

Unique

indicates if the value contained in a column is unique within the table

Data type

identifies the definition and format of the data stored in each column

Structured Query Language (SQL)

commands are used to build the physical structure of the database.

SQL

also used to populate, access, and manipulate the data within the relational database.

Flat File Database

designed around a single table

Hierarchical Model

data is organized in a tree like structure.

Record

collection for fields.

Network database

comprised of a collection of records connected to one another through links

Link

association between two records.

Object Oriented Model

An entity is modelled as an object

Relational model

data is represented as a collection of tables

Relational database

presents information in tables with rows and columns

Table

A basic storage structure

Field

the one value found at the intersection of a row and column

Conceptual Model

captures the functional and information needs of a business

Logical model

includes all entities and relationships among them. Also called ERM

Physical Model

Extension to a logical data model. Describes how the objects should be implemented in specific database.

Entity

Information that must be tracked, name for things that you can list

Attributes

describe entities and are the specific information that must be known.

Volatile attributes

unstable attributes

Nonvolatile attributes

stable attributes

Mandatory attributes

must have a value

Optional attributes

can be blank

Single or atomic attributes

are attributes that cannot be divided into subparts.

Composite attributes

are attributes that can be divided into smaller subparts that represent basic attributes with independent meanings of their own

Unique Identifier

It is unique across all instances of the entity

ERD

a model that identifies the concepts or entities that exist in a system and the relationships between those entities.

Relationship

is a bidirectional, significant association between two entities or between an entity and itself.

Optional

Use "may be" or "may."

Mandatory

Use "must be" or "must.”

Line

Use "one and only one."

Crow's feet

Use "one or more.”

Optionality

Relationships are either mandatory or optional

Cardinality

measures the quantity of something