ISYS 464, Section 3, Fall, 2015 Professor Paul Beckman Agenda – Hand back exam #1 and go over – Physical Database Design and Performance » textbook chapter 5 – Questions Next week – Introduction to SQL/Advanced SQL » textbook chapters 6 & 7
Exam #1 Hand back – and go over Statistics: – mean: – std. dev.:
Next Topic Physical database design – textbook chapter 5
But First, Database Articles . . . Oracle Exalytics: Engineered System for Extreme Analytics SAP HANA
Objectives Definition of terms Describe the physical database design process Choose storage formats for attributes Select appropriate file organizations Describe three types of file organization Describe indexes and their appropriate use Translate a database model into efficient structures Know when to use de-normalization
Systems Development Life Cycle (see also Figure 1-10) Planning Analysis Physical Design Implementation Maintenance Logical Design Physical Design Purpose: develop technology and organizational specifications Deliverable: program/data structures, technology purchases, organization redesigns Database activity: physical database design (define database to DBMS, physical data organization, database processing programs)
Physical Database Design Purpose: – translate the logical description of data into ____________________ for the storing and retrieving data Goal: – create a design for storing data that will provide: » adequate performance AND – insure » database integrity » security » recoverability technical specifications
Physical Design Process Normalized relations Volume estimates Attribute definitions Response time expectations Data security needs Backup/recovery needs Integrity expectations DBMS technology used Inputs Attribute data types Physical record descriptions (doesn’t always match logical design) File organizations Indexes and database architectures Query optimization Leads to Decisions
Fig. 5-1: Composite Usage Map (1) (Pine Valley Furniture Company)
Fig. 5-1: Composite Usage Map (2) (Pine Valley Furniture Company) Data volumes (records/table)
Fig. 5-1: Composite Usage Map (3) (Pine Valley Furniture Company) Access frequencies (per hour)
Fig. 5-1: Composite Usage Map (4) (Pine Valley Furniture Company) Usage analysis: 14,000 purchased parts accessed per hour 8000 quotations accessed from these 14,000 purchased part accesses 7000 suppliers accessed from these 8000 quotation accesses
Fig. 5-1: Composite Usage Map (5) (Pine Valley Furniture Company) Usage analysis: 7500 suppliers accessed per hour 4000 quotations accessed from these 7500 supplier accesses 4000 purchased parts accessed from these 4000 quotation accesses
Designing Fields Field : smallest unit of usable data in the db Field design – Choose: data type – Choose: coding, compression, encryption – Control: data integrity “Fixed” vs. “variable” length records – how could a record length be variable?
You've reached the end of your free preview.
Want to read all 64 pages?
- Summer '19
- Business, Computer data storage, data integrity