Log in User page Discussion History Go to the site toolbox

User:Chotchki/S2006/DatabaseDA

From BluWiki

Pictures Watch the dates to determine what class they are from.

Contents

Thursday 12 January 2006

Showed some notable technology predictions ;)

He talked about the presentation of data and how it influences decisions

Basic Concepts and Definitions

Database - an organized collection logically related data

Data - facts that can be recorded and stored using computer media

  • traditionally this included text and numbers
  • modern usage includes objects such as sounds, images, and video clips

Information

  • Data that has been processed in such a way that it can increase knowledge

Metadata

  • data that describes data

DBMS - general purpose software system that manages databases

Traditional File Processing

Bashes the file processing stuff like the author in Chapter 1

He makes a transition from file to relational via a excel to access transition of a business

Cots and Risks of Databases

  • new specialized personnel
  • new costs
  • conversion costs
  • easier backup and recovery
  • organizational conflict

The Range of DB Applications

  • Personal
  • Workgroup
  • Departmental
  • Enterprise
  • Internet

Database System Environment

a DBAdmin needs to have a wide skill set

User Interaction with DBMS

Evolution of Database Systems

Tuesday 17 January 2006

Information Systems Architecture

Key Components:

  • Data - eg. date models
  • Processes
  • Networks
  • People
  • etc

Information Engineering

an approach to develop an information systems

  • data oriented
  • top-down planning
    • trying to do a full view of the situation instead of piecemeal
  • aligns information technology with organization's business objectives and strategies

he used the example of a bank funds transfer to make his point

Database Design Process

  1. Conceptual Database Design
  2. Logical Database Design
  3. Physical Database Design

Uses the SDLC (see the book comparison)

Conceptual Database Modeling

  • Determine user requirements
  • Determine business rules
  • Build conceptual data model
    • outcome is an Entity-Relationship Diagram, a communication tool
    • This is walked through with the client

describes conceptual struture.... not the final implementation!

Logical Database Design

  • select logical database model
  • map entity relationship diagram
  • create data structures

Physical Database Design

  • select DBMS
  • select storage devices
  • etc

Three-Schema Architecture

  • external - UI with Input/Output
  • conceptual
  • internal

Human-Resources Needed in DB

  • Data administrators
  • DBA
  • Database analysts
  • etc

Business Rules

Things that constrain or define the business

A good business rule is:

  • declarative
  • precise
  • atomic
  • consistent
  • etc

Modeling reality

  • a database should mirror the real world if it is to influence or help the real world
  • data modeling is a design technique for capturing reality

Data modeling Components

  • Conceptual (ch3&4)
    • ERDs
      • Entities
      • attributes
      • relationships
  • Logical (ch5)
  • Physical (ch6)

Entity

something of interest in the environment with multiple instances also called an entity type

represented by a rectangle

  • Do not show system input, output, or users

Attribute

  • discrete data element
  • describes an entity
  • meaningful
  • value may be required or optional

Types

  • simple
  • composite
  • single valued
  • multivalued
  • derived
  • identifier

Thursday 19 January 2006

Identifiers

  • unique instances
  • can be one or more attributes
  • candidate key
  • create one of none exists
  • take note of weak entities
  • underline indentifiers in a diagram

guidelines

  • must be unique
  • should not change over time
  • guaranteed to have a valid, non-null value
  • use as few attributes for an identifier as possible

Attributes

shown as ellipses on ERDs

  • multivalued attributes are shown as double ellipses
  • composite attributes are shown broken into their components
  • derived attributes are shown with a dashed line
    • they are calculated not stored
    • optionally shown on the ERD

he talked about whether composite or simple attributes would be more appropiate

relationships

a relationhip is an association between one or more entities

  • degree - number of entities involved
    • unary - entity to itself
    • binary - two to each other
    • ternary - three to each other simultaneously
  • cardinality - number of instances of one entity related to another
    • think the crows feet stuff
      • 1 to many
      • many to 1
      • 1 to 1
      • many to many
  • optionality or minimum cardinality
    • optional
    • manditory

Tuesday 24 January 2006

Associative Entities may be created from M:N(many to many) relationships

A many to many relationship is the only relationship that can have attributes.

My group is group 1, Michael Cardona, Frank Davis (fedavis@helios.acomp.usf.edu), Chris Hotchkiss, Andrew Smith, Matt Wagner.

Thursday 26 January 2006

We did board problems, here are the results.

Thursday 2 February 2006

Each Group Member will prepare individually:

  1. A list of business functions that information model should include.
  2. Identify potential reports to generate.
  3. Draw a draft ERD of the three pages of the business narative.

Thursday 7 February 2006

supertype/subtype

  • generalization method development
  • specialiaztion method development

subtypes must have unique atrributes

completeness/disjointness

total specialization

  • must be a member of a stated subtype
  • double line

partial specialization

  • does not have to be a member of a stated subtype
  • single line

disjoint

  • cannot have membership in more than one subtype
  • shown as a 'd'
  • the subtype discriminator attribute is indicated next to the 'single' or 'double' line
    • the actual values for the discriminator are put by the line that determines them

overlap

  • can have membership in more than one subtype
  • shown as a 'o'
  • the subtype discriminator composite attribute is indicated next to the 'single' or 'double' line
    • the actual condition for the discriminator are put by the line that determines them
    • it is convention to use a question mark when indicating a value

Relational Model

Created by E.F. Codd

RDBMSes are the dominate database method today

  • Data Structure
    • table has unique name
      • rows
      • columns
  • Data Manipulation
    • SQL
  • Data Integrity
    • business rules

homework

Thursday 09 February 2006

homework

Convert the ERDs on the sample handouts into relational schemas

  • Group 1 - Problem #2
    • Ch5. Problem 1(a,b,c)
  • Group 2 - Problem #3
    • Ch5. Problem 2(a,b)
  • Group 3 - Problem #1,4
    • Ch5. Problem 5(a,b)
  • Professor
    • Ch5. Problem 3(all), 6(all)

Relational Keys in Tables

Primary Key

  • Analogous to the indentifier

Composite Key

  • A primary key made up of more than one field

Foreign Key

  • field in one table that servers as the primary key of another table

Integrity Constraints

Domain Constraint

  • constrains allowable values for a field

entity integrity

  • prohibits null in primary key

action assertion

  • constrains operations
  • ie permissions

referentical integrity

  • constrains a foreign key value to match a primary key value in a related table
    • primary key must exist before the foreign key is made

Relational Schema

NO DASHED UNDERLINES

see page 203

well-structured tables

normalization

Anomalies

Insertion Anomalies

  • attempt to store a value but cannot because another value unknown

Deletion Anomailies

  • when you remove data it takes other valuable data with it

Modification Anomailies

  • changes to multiple records of a table are needs to change a single value

Tuesday 14 February 2006

Gave back the first assignment

Will be going over the homework on Thursday

will get the second individual assignment thursday

E-R Diagrams to tables

  • mapping weak entities
    • use a primary foreign key

binary relationships

    • 1 to m
      • gives a foreign key to the m
    • m to m
      • makes a associative table, typically with a composite primary key it is NOT ALWYAYS the case
    • 1 to 1
      • primary key on the mandatory side becomes a foreign key on the optional side (if optionalities are asymmetric)
      • base the foriegn key location off the typical size differential between tables
      • it is allowed to change the name
  • usage of foreign keys
  • associative entities always become separate tables

unary relationship

    • 1 to m
      • create a separate table and treat it as a binary relationship
      • the foreign key must be named differently
    • m to m
      • typically a double composite foreign key

ternary(and n-way) relationships

  • treat it as a m to m binary relationship

subertype/subtype relationships

Thursday 16 February 2006

Normalization

  • Ill structured tables
  • insertion problems
  • deletion problems
  • modification problems

Steps to normalization

  • remove multivalued attributes
  • remove partial dependancies
  • etc.

functional dependency

  • draw arrows based off what depends on what

First Normal Form

No multivalued attributes

Second Normal Form

table is in 1NF and every non-key attribute is fully functionally dependant on the whole primary key

Third normal form

table is in 2nf and each attribute is not also dependant on another attribute

issues

transitive dependancies are hard to identify

use caution when merging multiple tables

  • synonyms
  • homonyms
  • transitive dependencies

Table Origins

  1. entity
  2. multivalued
  3. m to m relationship

Thursday 23 February 2006

Designing Fields

  • select from availible types
  • try to minimize storage space
  • represent all possible values
  • improve data ingetrity
  • support all data manipulations

Physical Records

records, pages, blocking factor

Optimization Decisions

  • Denormalization
  • Partitioning

Tuesday 28 February 2006

SQL table query uses the dot notation ie 'SELECT employee.phone'

Make sure the team evaluation form is filled out by Tuesday. Fill out it out completely!

File Organization

methods of organizing the database to meet your goals

  • sequential
  • indexed sequential
  • hashed
    • highest speed searches
    • primary key is the location
    • should only require one read
    • space inefficent

RAID

  • mirroring
  • striping

Test

Get here on time

No access questions

Chapters 1-6, 1&2 less so

multichoice true/false fill in the blank

several short essay

diagramming problems

must be legible

Tuesday 07 March 2006

Structured Query Language

  • DDL command set
    • Ex. Create, Alter, Drop, Rename
  • DML command set
    • Ex. Select, Insert, Update, Delete
  • DCL command set
    • Ex. Grant, Revoke

Constraint

Check

Each SQL statement ends in a semicolon

SELECT

SELECT [DISTINCT] column_list
FROM table_list
[WHERE conditional expression]
[GROUP BY column_list]
[HAVING conditional expression]
[ORDER BY column_list]

All data is in uppercase for the oracle sample database.

Thursday 09 March 2006

SELECT statement execution order

oracle has a definate date format required

WHERE clause

when using the AS clause always refer to the origional column name not the origional

BETWEEN ... AND clause is a range operator that is inclusive

IN clause is a list operator

LIKE is a wildcard selector

Logical Operator

AND

OR

NOT

Arithmetic Operators

+

-

/

Aggregate Functions

COUNT

MIN

MAX

SUM

AVG

ORDER BY

Row sorting

DESC

Tuesday 21 March 2006

Group By -categorization

if using aggregate(sum, avg, etc) and non-aggregate selects in the same statement, you MUST use a group by

having does not exclude where

  • processing multiple tables via joins

beware the Cartesian PRoducts

Join Types

  • Equi-join - duplicate primary and foreign keys will be shown
  • Natural join (inner)
  • Outer join -
  • self join

Thursday 23 March 2006

Self-Joins

used in unary relationships

Self join using a temp table, note the use of a <space> <alias> to rename a table SELECT emp.ename AS EMPLOYEE, mgr.ename AS MANAGER FROM emp, emp mgr WHERE emp.mgr = mgr.empno

Subqueries

used to fill in unknowns in a query 

SELECT ename FROM emp WHERE sal > (SELECT sal FROM emp WHERE empno = 7698) ;

SQL*PLUS

Lasts the whole login time

COLUMN command

  • FORMAT
  • HEADING
  • CLEAR 
COLUMN name FORMAT A18 TRUNCATED
COLUMN creditlimit FORMAT $99,999
SELECT name, creditlimit
FROM customer
WHERE creditlimit >= 8000;

Tuesday 04 April 2006

Client/Server Architectures

File Server

Fat client with NAS

Database Server

RPC calls

Three Tier

thin client

Middle Ware

  • ODBC
  • OLE-DB
  • JDBC

Access Usability Hierarchy

  • API
  • VBA
  • Macros
  • Functions/Expression
  • Objects
Retrieved from "http://www.bluwiki.com/go/User:Chotchki/S2006/DatabaseDA"

Site Toolbox:

Personal tools
TOOLBOX
LANGUAGES
GNU Free Documentation License 1.2
 This page was last modified on 14 July 2007, at 23:16.
Disclaimers - About BluWiki