Database Management Systems: File Structures

File and Table Structures

• Fixed-length record structures and variable-length record structures
• Blocking factor: the number of logical records included in a single read or write operation

Types of Files and Tables

• Master files: records that are relatively permanent, but values may change
• Transaction files: records that describe business events
• Document files: historical data for review without the overhead of regenerating documents
• Archival files: master and transaction records that have been deleted
• Table lookup files: relatively static data that can be shared to maintain consistency
• Audit files: special records of updates to other files

Data Architecture

• Definition of how files and databases are developed and used to store data
• Specification of file and/or database technology to be used
• Administrative structure set up to manage the data resource

Data Storage

• Data is stored in a combination of:
  • Conventional files
  • Operational databases (support day-to-day operations and transactions)
  • Data warehouses (store data extracted from operational databases for data mining)
  • Personal databases
  • Work group databases

Data Administration

• Data administrator: responsible for data planning, definition, architecture, and management
• Database administrator: responsible for database technology, design, construction, security, backup, and recovery, and performance tuning

Database Architecture

• Database technology used to support data architecture
• Includes database engine, utilities, CASE tools, and development tools
• Database management system (DBMS): special software used to create, access, control, and manage a database
• Core of DBMS: database engine
• Data definition language (DDL): part of the engine used to physically define tables, fields, and structural relationships
• Data manipulation language (DML): part of the engine used to create, read, update, and delete records in the database, and navigate between different records

Relational Databases

• Implement stored data in a series of two-dimensional tables related to each other via foreign keys
• Physical data model: schema
• DDL and DML for a relational database: SQL (Structured Query Language)
• Triggers: programs embedded within a database that are automatically invoked by updates
• Stored procedures: programs embedded within a database that can be called from an application program

Database Design

• Goals:
  • Efficient storage, update, and retrieval of data
  • Reliable data with high integrity
  • Adaptable and scalable to new and unforeseen requirements and applications
• Method for database design:
  1. Review logical data model
  2. Create a table for each entity
  3. Create fields for each attribute
  4. Create an index for each primary and secondary key
  5. Create an index for each subsetting criterion
  6. Designate foreign keys for relationships
  7. Define data types, sizes, null settings, domains, and defaults for each attribute
  8. Create or combine tables to implement supertype/subtype structures
  9. Evaluate and specify referential integrity constraints

Database Integrity

• Key integrity: every table should have a primary key
• Domain integrity: controls must be designed to ensure that no field takes on an inappropriate value
• Referential integrity: assurance that a foreign key value in one table has a matching primary key value in the related table
• Referential integrity constraints:
  • No restriction
  • Delete: cascade
  • Delete: restrict
  • Delete: set null

Database Distribution and Replication

• Data distribution analysis: determines which business locations need access to which logical data entities and attributes
• Centralization: entire database on a single server in one physical location
• Horizontal distribution (partitioning): tables or rows assigned to different database servers/locations