Transcript presentation source - CLAIR

January 14, 2000
Database Application Design
Handout #2
(C) 2000, The
University of Michigan
1
Course information
• Instructor: Dragomir R. Radev
([email protected])
• Office: 305A, WH, Phone: 615-5225
• Office hours: TBD and Friday 1-2
• Course page:
http://www.si.umich.edu/~radev/654w00
• Meeting time:
Fridays, 2:30 - 5:30 PM, 311 WH
(C) 2000, The
University of Michigan
2
Introduction to database
development
(cont’d)
(C) 2000, The
University of Michigan
3
Metadata
• System tables
• Overhead data (indexes)
• Application metadata
(C) 2000, The
University of Michigan
4
Components of a DBMS
• Design tools subsystem: design and creation of
databases and applications; creations of tables,
forms, queries, and reports; interfaces to
programming languages
• Run-time subsystem: e.g., for linking forms to
backend
• DBMS engine: translates requests into commands;
transaction management; backup and recovery;
locking
(C) 2000, The
University of Michigan
5
Creating the database
• Database schemas
• Tables:
– CAPTAIN (CaptainName, Phone, Street, City,
State, Zip)
– ITEM (Quantity, Description, DateOut, DateIn)
– Uniqueness?
(C) 2000, The
University of Michigan
6
Creating the database
• Relationships:
– one captain to many items (1:N)
– need to add CAPTAIN_ID to ITEM
• Domains
– sets of values that a column may have
(C) 2000, The
University of Michigan
7
Creating the database
• Business rules (constraints)
– In order to check out any equipment, a captain
must have a local phone
– No captain may have more than 7 balls checked
out at any time
– Captain must return all equipment within five
days after the end of each semester
– No captain may check out any equipment if he
or she has any overdue
(C) 2000, The
University of Michigan
8
Enforcing business rules
• Depends on system
• Stored procedures
(C) 2000, The
University of Michigan
9
Creating tables
•
•
•
•
•
•
•
Relationships
Foreign keys (CAPTAIN_ID)
Forms
Queries
Reports
Menus
Application programs
(C) 2000, The
University of Michigan
10
DB654 database
• Students
• Projects
(C) 2000, The
University of Michigan
11
Database development process
• Top-down development:
–
–
–
–
–
–
general to specific
start with the strategic goals of the organization
means by which they will be accomplished
information requirements
necessary systems
BUILD ABSTRACT DATA MODEL
(C) 2000, The
University of Michigan
12
Database development process
• Bottom-up development:
– Start from needed reports, forms, queries
– analyze existing systems, input & output
– BU design: produces systems more quickly
than TD design
– Other tradeoffs
(C) 2000, The
University of Michigan
13
Data modeling
• Inference process: from users’ statements to
the structure and relationships of items to be
stored in the database
• Reports and forms are shadows on the wall
• Inferencing is more an art than a science
• Multi-user systems: how to resolve
differences; who decides
(C) 2000, The
University of Michigan
14
The entity-relationship
model
(C) 2000, The
University of Michigan
15
E-R model
• Introduced by Peter Chen in 1976 (“The EntityRelationship Model - Towards a Unified View of
Data”) in ACM TODS, January 1976
• Key elements:
–
–
–
–
entities: something that the user wants to track
entity classes
attributes (or properties); e.g, employee name
identifiers (name, but not salary): unique or not;
composite
– relationships: by degree
(C) 2000, The
University of Michigan
16
Binary relationships
•
•
•
•
1:1
1:N
N:M
HAS-A relationships: e.g., a CLUB has
STUDENTS
STUDENT
N:M
CLUB
STUDENT-CLUB
(C) 2000, The
University of Michigan
17
Entity-relationship diagrams
• Maximum cardinality
• Minimum cardinality
DORMITORY O
1:N
STUDENT
DORM-OCCUPANT
• Recursive relationships: examples?
(C) 2000, The
University of Michigan
18
****
(C) 2000, The
University of Michigan
19