Chapter 14ObjectivesSlide 3Conventional Files versus the DatabaseFiles versus DatabasePros and Cons of Conventional FilesPros and Cons of DatabasesFieldsFields (continued)RecordsFiles and TablesTypes of conventional files and tablesFile and Table DesignData ArchitectureData Architecture (continued)A Modern Data ArchitectureAdministratorsDatabase ArchitectureTypical DBMS ArchitectureRelational DatabasesFrom Logical Data Model …… To Physical Data Model (Relational Schema)User Interface for a Relational PC DBMSWhat is a Good Data Model?Database Normalization (also see Chapter 8)Conventional File DesignGoals of Database DesignLogical data Model in Third Normal FormDatabase SchemaA Method for Database DesignDatabase IntegrityData Types for Different Database TechnologiesData Types for Different Database Technologies (cont.)Slide 34Physical Database SchemaDatabase Schema with Referential Integrity ConstraintsDatabase Distribution and ReplicationDatabase Distribution and Replication (continued)Database Capacity PlanningSQL DDL CodeMcGraw-Hill/IrwinCopyright © 2007 by The McGraw-Hill Companies, Inc. All rights reserved.Chapter 14Database Design14-2Objectives•Compare and contrast conventional files and modern, relational databases.•Define and give examples of fields, records, files, and databases.•Describe modern data architecture of files, operational databases, data warehouses, personal databases, and work group databases.•Compare roles of systems analyst, database administrator, and data administrator.•Describe architecture of database management system•Describe how a relational database implements entities, attributes, and relationships from a logical data model.•Transform a logical data model into a physical, relational database schema.•Generate SQL to create the database structure in a schema.14-314-4Conventional Files versus the DatabaseFile – a collection of similar records.•Files are unrelated to each other except in the code of an application program.•Data storage is built around the applications that use the files.Database – a collection of interrelated files•Records in one file (or table) are physically related to records in another file (or table).•Applications are built around the integrated database14-5Files versus Database14-6Pros and Cons of Conventional FilesPros•Easy to design because of their single-application focus•Excellent performance due to optimized organization for a single applicationCons•Harder to adapt to sharing across applications•Harder to adapt to new requirements•Need to duplicate attributes in several files.14-7Pros and Cons of DatabasesPros•Data independence from applications increases adaptability and flexibility•Superior scalability•Ability to share data across applications•Less, and controlled redundancy (total non-redundancy is not achievable)Cons•More complex than file technology•Somewhat slower performance•Investment in DBMS and database experts•Need to adhere to design principles to realize benefits•Increased vulnerability due to consolidating in a centralized database14-8FieldsField – the smallest unit of meaningful data to be stored in a database•the physical implementation of a data attribute14-9Fields (continued)Primary key – a field that uniquely identifies a record.Secondary key – a field that identifies a single record or a subset of related records.Foreign key – a field that points to records in a different file.Descriptive field – any nonkey field.14-10RecordsRecord – a collection of fields arranged in a predetermined format.•Fixed-length record structures•Variable-length record structuresBlocking factor – the number of logical records included in a single read or write operation (from the computer’s perspective).14-11Files and TablesFile – the set of all occurrences of a given record structure.Table – the relational database equivalent of a file.14-12Types of conventional files and tables•Master files – Records relatively permanent though values may change•Transaction files – Records describe business events•Document files – Historical data for review without overhead of regenerating document•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 files14-13File and Table Design•Older file design methods required analyst to specify precisely how records should be:•Sequenced (File organization)•Accessed (File access)•Database technology usually predetermines and/or limits this•Trained database administrator may be given some control over organization, storage location, and access methods for performance tuning.14-14Data ArchitectureData architecture – a definition of how:•Files and databases are to be developed and used to store data•The file and/or database technology to be used•The administrative structure set up to manage the data resource14-15Data Architecture (continued)Data is stored in some combination of:•Conventional files•Operational databases – databases that support day-to-day operations and transactions for an information system. Also called transactional databases.•Data warehouses – databases that store data extracted from operational databases.•To support data mining•Personal databases•Work group databases14-16A Modern Data Architecture14-17AdministratorsData administrator – a database specialist responsible for data planning, definition, architecture, and management.Database administrator – a specialist responsible for database technology, database design,construction, security, backup and recovery, and performance tuning.•A database administrator will administer one or more databases14-18Database ArchitectureDatabase architecture – the database technology used to support data architecture•Including the database engine, database utilities, CASE tools, and database development tools.Database management system (DBMS) – special software used to create, access, control, and manage a database. •The core of the DBMS is its database engine.•A data definition language (DDL) is used to physically define tables, fields, and structural relationships.•A data manipulation language (DML) is used to create, read, update, and delete records in database and navigate between records.14-19Typical DBMS Architecture14-20Relational