Transcript COMP 430 Intro. to Database Systems

COMP 430
Intro. to Database Systems
Design Process
Design Process – Why?!?
Is this your reaction?
Going beyond simple one-person assignments
Specifications not handed to you
• Complex
• Vague
• Not necessarily known by one person
An abbreviated guide to Database Specification...
Requirements – Data items & meanings
• Talk to everyone involved to understand what data is needed.
• Eliminate redundant items
• Salesperson: Items sold
• Loading dock worker: Items shipped
Identify data items that can be calculated
If you know
You can calculate
Game teams + scores
Winning team
Student scores
Letter grades
Racer time
Race ranking
Date shipped
Has it shipped?
Who manages whom
Number of subordinates
Purchased item prices + quantities
Purchase subtotal
Purchased item prices + quantities +
location + date
Purchase total, including taxes
• Eliminate redundancy from base tables
• Use views, calculated fields, application code, …
Group/ungroup data items into fields
One field or multiple?
Game score
Height
Name
Address
Days of the week worked
Period worked
Identify representations & types of fields
Consider abstract types like integer, real, text string, currency, date/time.
What type?
Height
Student number
Elapsed time
Day of the week
Avoid data items that need regular updates
Data that needs to be updated
Age
Duration of employment
Batting average
Total commissions
Group fields into entities/tables
Dept code
Birth date
Person
First name
Number
Phone number
Room
Course
Title
Instructor
Last name
Largely common sense, but also tied to table/relationship design…
Identify relationships between tables
Dept code
Birth date
Person
Number
Phone number
One/many to one/many?
Room
Course
Instructor
First name
Last name
Lots more discussion coming soon!
Title
Identify or create keys
Some desirable properties:
• Unique
• Exists/Known
• Immutable
• Simple/compact
Entity/Table
Person
Course
Team player
Campus building
Library book
Primary key?
Name, SSN, thumbprint
Dept code + number
Jersey number
Name, abbreviation
ISBN
Possibly create surrogate/synthetic keys.
Natural vs synthetic keys
Advantages of natural
Advantages of synthetic
Field exists already – no extra data Concise – single short value
Human-readable, -searchable
Immutable (typically)
Data exists
Design-related Implementation
Issues
Field & Table naming conventions
• Many conflicting conventions
• What’s important:
• Consistency
•
•
•
•
Readable but not too long
Table names – singular, not plural; typically nouns, not verbs
Field names – no type info; typically nouns or adjectives (for Boolean)
Foreign keys field names – easy match to primary keys
Synthetic key types
Integer
Standard SQL
Concise
Automatically
unique
Disallows
operations
Good for indexing
Text
Auto-incrementing
integer
Unique ID
Calculated data
Calculated once-and-for-all, or as needed? When?
Store result in database?
• Database management system
• Calculated field or aggregation in query
• Procedure/function
• Application code
Data validation
• User-interface
• Application
• Database management system
• CHECK constraints – including PRIMARY KEY, FOREIGN KEY, NOT NULL
• Lookup/validation tables
(Explore these later.)
Next: The core
issues of table &
relationship design.