User:Chotchki/S2006/DatabaseDA
From BluWiki
Pictures Watch the dates to determine what class they are from.
[edit] Thursday 12 January 2006
Showed some notable technology predictions ;)
He talked about the presentation of data and how it influences decisions
[edit] 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
[edit] 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
[edit] Cots and Risks of Databases
- new specialized personnel
- new costs
- conversion costs
- easier backup and recovery
- organizational conflict
[edit] The Range of DB Applications
- Personal
- Workgroup
- Departmental
- Enterprise
- Internet
[edit] Database System Environment
a DBAdmin needs to have a wide skill set
[edit] User Interaction with DBMS
[edit] Evolution of Database Systems
[edit] Tuesday 17 January 2006
[edit] Information Systems Architecture
Key Components:
- Data - eg. date models
- Processes
- Networks
- People
- etc
[edit] 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
[edit] Database Design Process
- Conceptual Database Design
- Logical Database Design
- Physical Database Design
Uses the SDLC (see the book comparison)
[edit] 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!
[edit] Logical Database Design
- select logical database model
- map entity relationship diagram
- create data structures
[edit] Physical Database Design
- select DBMS
- select storage devices
- etc
[edit] Three-Schema Architecture
- external - UI with Input/Output
- conceptual
- internal
[edit] Human-Resources Needed in DB
- Data administrators
- DBA
- Database analysts
- etc
[edit] Business Rules
Things that constrain or define the business
A good business rule is:
- declarative
- precise
- atomic
- consistent
- etc
[edit] 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
[edit] Data modeling Components
- Conceptual (ch3&4)
- ERDs
- Entities
- attributes
- relationships
- ERDs
- Logical (ch5)
- Physical (ch6)
[edit] 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
[edit] Attribute
- discrete data element
- describes an entity
- meaningful
- value may be required or optional
Types
- simple
- composite
- single valued
- multivalued
- derived
- identifier
[edit] Thursday 19 January 2006
[edit] 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
[edit] 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
[edit] 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
- think the crows feet stuff
- optionality or minimum cardinality
- optional
- manditory
[edit] 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.
[edit] Thursday 26 January 2006
We did board problems, here are the results.
[edit] Thursday 2 February 2006
Each Group Member will prepare individually:
- A list of business functions that information model should include.
- Identify potential reports to generate.
- Draw a draft ERD of the three pages of the business narative.
[edit] Thursday 7 February 2006
[edit] supertype/subtype
- generalization method development
- specialiaztion method development
subtypes must have unique atrributes
[edit] 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
[edit] Relational Model
Created by E.F. Codd
RDBMSes are the dominate database method today
- Data Structure
- table has unique name
- rows
- columns
- table has unique name
- Data Manipulation
- SQL
- Data Integrity
- business rules
[edit] homework
[edit] Thursday 09 February 2006
[edit] 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)
[edit] 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
[edit] 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
[edit] Relational Schema
NO DASHED UNDERLINES
see page 203
well-structured tables
normalization
[edit] 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
[edit] Tuesday 14 February 2006
Gave back the first assignment
Will be going over the homework on Thursday
will get the second individual assignment thursday
[edit] 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
- 1 to m
- 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
- 1 to m
ternary(and n-way) relationships
- treat it as a m to m binary relationship
subertype/subtype relationships
[edit] Thursday 16 February 2006
Normalization
- Ill structured tables
- insertion problems
- deletion problems
- modification problems
[edit] Steps to normalization
- remove multivalued attributes
- remove partial dependancies
- etc.
functional dependency
- draw arrows based off what depends on what
[edit] First Normal Form
No multivalued attributes
[edit] Second Normal Form
table is in 1NF and every non-key attribute is fully functionally dependant on the whole primary key
[edit] Third normal form
table is in 2nf and each attribute is not also dependant on another attribute
[edit] issues
transitive dependancies are hard to identify
use caution when merging multiple tables
- synonyms
- homonyms
- transitive dependencies
[edit] Table Origins
- entity
- multivalued
- m to m relationship
[edit] 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
[edit] Physical Records
records, pages, blocking factor
[edit] Optimization Decisions
- Denormalization
- Partitioning
[edit] 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!
[edit] 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
[edit] 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
[edit] 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
[edit] 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.
[edit] Thursday 09 March 2006
SELECT statement execution order
oracle has a definate date format required
[edit] 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
[edit] Logical Operator
AND
OR
NOT
[edit] Arithmetic Operators
+
-
/
[edit] Aggregate Functions
COUNT
MIN
MAX
SUM
AVG
[edit] ORDER BY
Row sorting
DESC
[edit] 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
[edit] Join Types
- Equi-join - duplicate primary and foreign keys will be shown
- Natural join (inner)
- Outer join -
- self join
[edit] Thursday 23 March 2006
[edit] Self-Joins
used in unary relationships
Self join using a temp table, note the use of a
