Transcript Introduction to Database Development (1)

Introduction to
Database
Development (1)
IS 240 – Database Management
Lecture #3 – 2004-01-22
Prof. M. E. Kabay, PhD, CISSP
Norwich University
[email protected]
Topics: Kroenke Chapter 2
 Database
 DBMS
 Creating a DB
 Application Components
 DB Development
 Homework
2
Copyright © 2004 M. E. Kabay. All rights reserved.
Database: Types of Data
 User Data: information we care about
 Metadata: information about the data
 Indexes: information for fast access to data
 Application Metadata: information for
showing and using data
3
Copyright © 2004 M. E. Kabay. All rights reserved.
Types: User Data
4
Your student information:
 Student_name
 Student_ID
 Student_home_address
 Student_residence_address
 Student_residence_address_term
 Student_grad_year
 Student_major
 Student_major_year
 Course_number
 Course_term
 Course_title
 Course_instructor
 Course_credits
 Course_grade . . . .
Copyright © 2004 M. E. Kabay. All rights reserved.
Types: User Data
How to group data effectively to represent course
information?
1. One big record: Student_id, student_home_address,
student_major, course_term, . . . .
2. A record for student-specific info +
a record for residence info +
a record for course-specific info +
linkages (relationships)
 Student_name, Student_ID, Student_home_address. .
..
 Student_ID, Student_residence_address,
Student_residence_address_term
 Student_ID, Course_number, Course_term,
Course_grade. . . .
 Course_number, Course_term, Course_title,
Course_instructor, Course_credits
 .....
5
Copyright © 2004 M. E. Kabay. All rights reserved.
User Data
 Examples from instructor’s own simple
databases
TODO – keeps track of things to do
IYIR – keeps abstracts of security events
6
Copyright © 2004 M. E. Kabay. All rights reserved.
(User data: TODO)
7
Copyright © 2004 M. E. Kabay. All rights reserved.
(User data: IYIR)
8
Copyright © 2004 M. E. Kabay. All rights reserved.
User Data Relationships
 First impressions of data requirements are usually too
simple for realistic applications
 Think about what can change – may need to create
records to keep track of history; e.g., ____?
 Think about relationships among data
 Relationships among data may be
 1:1 (one-to-one); e.g., _____?
 1:n (one-to-many) ; e.g., _____?
 n:n (many-to-many) ; e.g., _____?
 Bad designs lead to duplication, trouble in updates
 Normalization (to be studied later) provides
systematic method for sorting out data into records
with minimal trouble
9
Copyright © 2004 M. E. Kabay. All rights reserved.
Types
 Metadata
Information about the data and their
relationships
 Application Metadata
Forms, reports
Varies among products
10
Copyright © 2004 M. E. Kabay. All rights reserved.
(Metadata: TODO)
11
Copyright © 2004 M. E. Kabay. All rights reserved.
(Metadata: TODO)
12
Copyright © 2004 M. E. Kabay. All rights reserved.
(Metadata: IYIR)
13
Copyright © 2004 M. E. Kabay. All rights reserved.
(Metadata: IYIR)
14
Copyright © 2004 M. E. Kabay. All rights reserved.
(Metadata: IYIR)
15
Copyright © 2004 M. E. Kabay. All rights reserved.
(Metadata: IYIR)
16
Copyright © 2004 M. E. Kabay. All rights reserved.
(Metadata: IYIR)
17
Copyright © 2004 M. E. Kabay. All rights reserved.
(Forms)
18
Copyright © 2004 M. E. Kabay. All rights reserved.
(Forms)
19
Copyright © 2004 M. E. Kabay. All rights reserved.
(Forms)
20
Copyright © 2004 M. E. Kabay. All rights reserved.
(Reports)
21
Copyright © 2004 M. E. Kabay. All rights reserved.
(Reports)
22
Copyright © 2004 M. E. Kabay. All rights reserved.
(Reports)
23
Copyright © 2004 M. E. Kabay. All rights reserved.
Types: Indexes
 Large databases can contain millions of
records
 Without index, need to search all records
Can take seconds, minutes or hours
All depends on speed of I/O:
24
Copyright © 2004 M. E. Kabay. All rights reserved.
DBMS
 Design Tools
 Run-Time
 DBMS Engine
25
Copyright © 2004 M. E. Kabay. All rights reserved.
DBMS: Design Tools
26
Copyright © 2004 M. E. Kabay. All rights reserved.
DBMS: Run-Time
 Infrastructure that “runs” the database
Handles designer/user requests for
database changes, data input/output
Handles all the details of opening,
accessing, modifying information
 Reads structure metadata to interpret
linkages
 Reads form metadata to represent
information readably
 Produces and prints reports automatically
27
Copyright © 2004 M. E. Kabay. All rights reserved.
DBMS: DBMS Engine
 Turns instructions from DBMS run-time
components into instructions to operating
system
Translates dataset names into file names
Handles file I/O
Handles transactions
Manages locking of resources
 Communicates with other OS subsystems
Input devices – keyboards, mouse
Display units – screens
Spooler – sends print output to printer
28
Copyright © 2004 M. E. Kabay. All rights reserved.
Creating a DB
 Schema
 Tables
 Relationships
29
Copyright © 2004 M. E. Kabay. All rights reserved.
Creating a DB: Schema
 Define all aspects of the needed data
 Specify names of
Fields (columns)
Records (table rows)
 Decide on domains for every field
Physical format (length, types)
Allowable values
Empty(null) allowed?
Unique?
 Define relations (links) among tables
30
Copyright © 2004 M. E. Kabay. All rights reserved.
Creating a DB: Tables
 Turn specifications into real tables
 DBMS automatically handles details
31
Copyright © 2004 M. E. Kabay. All rights reserved.
Creating a DB: Relationships
 Specify how to link tables
 Requires common fields
 Specify type of relationship
1:1
1:n
m:n
32
Copyright © 2004 M. E. Kabay. All rights reserved.
Application Components
Will study details later
 Forms
 Queries
 Reports
 Menus
 Programs
33
Copyright © 2004 M. E. Kabay. All rights reserved.
Forms
 Show data in different ways for different
functions
Lookup
Data entry
Data management (modification)
 Many different forms can exist in a single DB
 Can define fields that have different
characteristics
Read-only
Specific input-values only
 Generate specific error messages or
confirmations
34
Copyright © 2004 M. E. Kabay. All rights reserved.
Queries
 Ask questions about data
Planned – stored queries
Ad hoc – defined on the spot
 Various methods
SQL – Structured Query Language
Sometimes pronounced “sequel”
Often pronounced “S – Q – L”
Visual methods – drag and drop
Query-by-example (QBE) – specify
requirements, then store query
Query-by-form – designer supplies form,
user inputs specific data
35
Copyright © 2004 M. E. Kabay. All rights reserved.
Reports
 Formatted output for printers
 Typical banded structure
Report header
Page header
Detail
Page footer
Report footer
 Many different report structures can be
named and stored for a single DB
36
Copyright © 2004 M. E. Kabay. All rights reserved.
Menus
 Windows / Mac visual interface has made
drop-down menus familiar
 Can create application-specific menus
 Provide shortcut-keys if desired
 Menus can change from stage to stage of
user’s progress through application program
37
Copyright © 2004 M. E. Kabay. All rights reserved.
Programs
 DBMS usually include facilities for special
controls (programs)
Can define actions for anything that can be
specified in syntax of programming
language for DBMS
Often highly portable across platforms as
long as same DBMS used
 Can also write standalone programs that
access database files in various languages
Need application program interface (API)
May not be easily portable from platform to
platform
38
Copyright © 2004 M. E. Kabay. All rights reserved.
DB Development
 Prototypes
 Top-down design/development
 Bottom-up design/development
 Data modeling
39
Copyright © 2004 M. E. Kabay. All rights reserved.
Development: Prototypes
 SDLC (System Development Life Cycle)
Analysis
Specifications
Design
Coding
Testing
Documentation
Implementation
 Sometimes takes too long
Code is out of date by time it’s
implemented
40
Copyright © 2004 M. E. Kabay. All rights reserved.
Development: Prototypes
 Rapid Application Development (RAD)
 Joint Application Development (JAD)
Show user mockup of application
Helps to improve communications among
users, analysts, programmers, trainers
 Modern DBMS ideal for prototyping
Easy forms & reports for I/O
Can include constraints easily and write
good error messages quickly
 Write one to throw away: learn from mistakes
41
Copyright © 2004 M. E. Kabay. All rights reserved.
Development: Top-down
Design/Development
 Thorough analysis of requirements
Plan carefully, looking at strategic issues
first
Develop data models of ever-more detail
 Advantages
Reduce wasted effort, rewriting of code
Expandable to new requirements because
of systematic understanding
 Disadvantages
Slow to reach production code
May be inflexible in rapidly-changing area
42
Copyright © 2004 M. E. Kabay. All rights reserved.
Development: Bottom-up
Design/Development
 Start with specific project
Often small-scale
Consistent with RAD/JAD
Build up from existing project by adding
others as required
 Advantages
Can be rapid to meet needs
Consistent with Pareto Principle: meet 80% of
needs with first 20% of development
 Disadvantages
Can lead to false starts, reworking, difficult
integration of different systems
43
Copyright © 2004 M. E. Kabay. All rights reserved.
Development: Data Modeling
 Complex, sophisticated process
 Observe what people do as well as what they
say about their information needs
Users do not know how to represent data
in data structures
Have to see what they need to construct
models
But what they do now may not be the best
for them – limited by current tools
 Good communications skills essential
44
Copyright © 2004 M. E. Kabay. All rights reserved.
Homework
 Reread Chapter Two of Kroenke using ReadRecite-Review
 For Thu the 29th of January 2004
Answer questions 2.1 through 2.21 in
writing
 For Tue the 27th
Prepare for lab work on by running MSACCESS on a lab computer (or your own)
Finish Ferrett et al Projects #1 & #2
Look ahead to Ferrett Projects #3 & #4
45
Copyright © 2004 M. E. Kabay. All rights reserved.
DISCUSSION
46
Copyright © 2004 M. E. Kabay. All rights reserved.