COMP 430 Intro to Database Systems Design Process

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 Primary key? Name, SSN, thumbprint Dept code + number Jersey number Campus building Library book Name, abbreviation ISBN Possibly create surrogate/synthetic keys.

Natural vs synthetic keys Advantages of natural Field exists already – no extra data Advantages of synthetic 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.