Transcript Database Lecture

Database Overview
Created by
Virginia Phillips
What is a Database
• Integrated
– Many tables, reports, views, queries
• Shared
– Can be used by many users simultaneously
• Minimizes Redundancy
– Foreign keys are only repeated fields
• Maximum Integrity
– Allows only valid data
Data
• Entities
– Objects to be modeled -- people, places, things
• Associations
– Relationships between objects
• Validation Rules
– Sex must be M or F; State must be 2 letters OH; Account number must be 7 digits >999999
Approach to Database Design
• Think about data
– What data are needed for output
• Consider data as a resource
– Resources can be managed
– Costs and benefits can be identified
• Use a systems approach
– identify data, normalize data, etc.
Three Database Models
• Hierarchical
– Think DOS Tree
• Network
– Think DOS Tree with
relationships that can
flow both directions
• Relational
– Think tables,
relationships, and keys
Normalized Data
•
•
•
•
1st NF
2nd NF
3rd NF
4th NF
no repeating groups
no partial key dependence
no transitive dependencies
no independent multivalued
dependencies
• 5th NF decompose to 3 or more relations
to avoid JOIN dependencies
• BCNF all determinants are candidate keys
Database Design Phases
• Integrate Views
Identify Transactions
• Validate
• Determine logical access needed
• Build conceptual model
– Validate
– Identify business entities
• Build Enterprise model
• Build usage map
– Access paths, inquiries, relationship ratios
Traditional View of Data
Modeling
Information Resource Planning
System Analysis
Conceptual Data Modeling
Systems Design
Physical Database Design
Coding and Implementation
Database Implementation
Preliminary Steps
• System Analysis
– Complete Data Flow Diagrams
• Design Conceptual Model
• Design Database
– Data Dictionary
– Entities
– Relationships
Data Flow Diagram
Data Flow
Process
Data Store
Terminator or Source
Conceptual Modeling
• Traditional Approach
– Analyze and model data for each program
– Analyze and model data for enterprise before
initiating application projects
• Better Approach
– Analyze and model data for a related group of
programs -- a DATABASE
Business Enterprise
•
•
•
•
•
•
•
Accounts Receivable
Order Entry
Inventory Control
General Ledger
Accounts Payable
Purchasing
Distribution
• Personnel
• Payroll
• Fringe Benefits
– 401K
– Insurance
– Stock Options
Model Data for a Specific
Application
• Order Entry
– What are local data?
– What are shared data?
– What programs are required?
Order Entry Model
• Customer (ID, Name, Add, City, State, City,
Zip, Credit Status, Telephone, Balance,
Contact)
– Ship to (ID, ShipID, Add, City, State, Zip)
• Orders (ID, CustID, Date, Disc, ShipDate,
Terms, ShipCharg, OrdAmt, AmtDue)
• Details (OrdID, ItemID, ORDQty,
UnitPrice, InvAvail)
• Item (ItemID, Desc, UnitPrice)
Minimum Cardinality
• Required Relationship
– For an entity to exist there must be a related
entity in another table
• Optional Relationship
– For an entity to exist there need NOT be a
related entity in another table
Cardinality Example
• Required Relationship
– An order must be
related to a specific
customer
– A class must exist if a
student can be
registered
• Optional Relationship
– A customer may exist
without a current order
in the system
– A student may exist
without specific
courses in the
inventory
Relationships
• One-to-One
– For each record in table 1, there is a maximum
of one record in table 2
• One-to-Many (1:m)
– For each record in table 1, there may be many
related records in table 2
• Many-to-Many
– For each record in table 1, there may be many
related records in table 2 and vice versa
Many-to-Many
• Many orders in an inventory system
• Eliminate a many-to-many relationship
– create a new entity called an intersection
• Ordered Items
• Inventory Items
• Orders
class rosters
courses
students
Entity/Relationship
• Entity Name
–
–
–
–
Primary Key
Candidate Key
Foreign Key
Secondary Key
• Field Type
• Field Length
• Description
Database Design
• Determine Design Requirements for
– Databases
• Tables
– Elements
• Fields -- type, length, description
– Keys -- primary, candidate, secondary, foreign
– Data views -- reports, queries, etc.
– Storage Area Requirements
Database Design
• Start with Flow Diagram
– Build Lexicon and Determine Process
Specifications
• Use Lexicon and Specifications to build Conceptual
Data Model
– Use Lexicon, Specifications, and Conceptual
Data Model to build Logical Access Map
• Build Logical Access Map
– Create Views
Data, Process, & Technology
Portfolios
Process
Languages
Programs
Technology
Hardware
Data Communications
Operating Systems
Data
Dictionary
Access
Data
Modularization
•
•
•
•
Data Flows
Access Order
Access Customer
Access Ordered Item
Access Items
Data Stores
Logical Access Maps
• Arrange entities hierarchically by access
– Different accesses depicted as separate boxes
• Show relationships with solid lines
• Show flows with dashed lines
• I HEAR, I FORGET
– I FORGOT
• I SEE, I REMEMBER
– DID YOU REMEMBER
• I DO, I UNDERSTAND
– DO YOU UNDERSTAND
Action Diagrams
• Develop a module for each entity access
• All actions against a single entity must be
illustrated in single module
• Show sequence of actions (single,
compound, structures, relationships)
– Create, Read, Update, Delete
– Sort, Select, Join, Intersect
– Sequential, Repetitive, Conditional
TIME OUT
• The Mind Can Absorb
• only
• What the Bladder
• Can Endure
Future Database Issues
• Standards -- SQL
• PC Platform
– Client-Server Systems
•
•
•
•
Knowledge based
Object Oriented
Distributed
Data Warehouse
– Data Mining