1 / 15

SYS364

Learn about the basics of database design, including definitions, initial ERD creation, data normalization, and final ERD creation. Explore database management, models, and file access and organization.

ashc
Download Presentation

SYS364

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SYS364 Database Design Continued

  2. Database Design • Definitions • Initial ERD’s • Normalization of data • Final ERD’s • Database Management • Database Models • File Access and Organization

  3. Database Management • File processing environments where each user/department has its own copy of files (or data in spreadsheets) have three problems • Data redundancy • Inconsistent data • Managers require enterprise-wide information • A database is a normalized, single repository optimized for transactions. • A data warehouse is optimized for read only data inquiry and may not be normalized. Updated by the transaction database at intervals.

  4. Elements of Database Management Systems • DBMS is a software system used to create, access and control a database • Has five main components • Data Definition Language • Data Manipulation Language • Query language • Data dictionary • Utility programs

  5. Data Definition Language • Used to describe the structure of the database • Complete database description (fields, records and relationships) is the schema or collection • Subschema is a view of the database as seen by a program or a user

  6. Data Manipulation Language • Provides necessary commands for database operations • Select • Insert • Update • Delete

  7. Query Language • A non-procedural language used to access a database • Non-procedural languages allows you to specify a task without specifying how the task will be done • QBE (Query By Example) • SQL (Structured Query Language)

  8. Data Dictionary • Serves as a central repository for information about the database • Schemas and subschemas are stored in the data dictionary

  9. Utility Programs • Most DBMS include the necessary support for database security, backup and recovery, audit trails, and data integrity • Most DMBS also provide utility programs for creating a database, changing the structure of the database, gathering and reporting patterns of database usage, and detecting and reporting database structure irregularities

  10. Characteristics of Database Management • Programs independent of Data storage • Scalability – accommodates growth • Support for client/server • Central repository:Economy of scale – one big serverData exists once and is shared (not copied)DB Admin. Enforces standards, balances specific requirements vs. overall performance • Controls DB Replication when needed • Security

  11. Database Models • Hierarchical & Network • old, e.g. FoxPro, dBase • Programs must know relationships • Object-orientedgood for BLOBs and non-transactional data • Relational • the current standard, SQL interface • ad hoc (to this) interactive processing • Embedded in programs • ODBC, JDBC

  12. File Access and Organization • Logical vs. Physical Records • Types of files • Control files, e.g. next numbers for Customer code, SKU, Invoice No. • Master file, e.g. Customer, Inventory • Transaction Files, e.g. Invoices (updated, volatile) • Audit/Journal Files, who did what and when • History Files, e.g. old Invoices (read-only, non-volatile) • Work Files, temporary use

  13. File Access • Sequential Access Method (all records in sequence, usually by primary key) • Random Access Method (any one record) • Various programming techniques must be used in non-DBMS systems • DBMS is all by key, Sequentially or Randomly through program interfaces and/or imbedded SQL

  14. File Organization • File (non-DBMS) systems: • Sequential Organization (sorted records) • Direct Organization (relative record number) • Indexed Organization (simple key but no relations) • DBMS • Tables in 3NF with Primary Keys, Views of data across tables by relationships

  15. Storage Media • HDD – cheap, fast • DASD –good, faster • RAID1 – mirrored redundancy: fast, good • RAID5 – striped redundancy: cheaper, fast • Floppy vs. diskette • Tape, CD-ROM, removable media ensure capacity for unattended backup • Hierarchical file systems

More Related