1 / 69

Introduction

Introduction. The chapter will address the following questions: What are the similarities and differences between conventional files and modern, relational databases? What are of fields, records, files, and databases? What are some examples of each?

nalani
Download Presentation

Introduction

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. Introduction • The chapter will address the following questions: • What are the similarities and differences between conventional files and modern, relational databases? • What are of fields, records, files, and databases? What are some examples of each? • What is a modern data architecture that includes files, operational databases, data warehouses, personal databases, and work group databases? • What are the similarities and differences between the roles of systems analyst, data administrator, and database administrators as they relate to databases? • What is the architecture of a database management system? Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  2. Introduction • The chapter will address the following questions: • How does a relational database implement entities, attributes, and relationships from a logical data model? • How do you normalize a logical data model to remove impurities that can make a database unstable, inflexible, and non-scaleable? • How do you transform a logical data model into a physical, relational database schema? • How do you generate SQL code to create the database structures in a schema? Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  3. Conventional Files Versus the Database • Introduction • All information systems create, read, update and delete data. This data is stored in files and databases. • Files are collections of similar records. • Databases are collections of interrelated files. • The key word is interrelated. • The records in each file must allow for relationships (think of them as ‘pointers’) to the records in other files. • In the file environment, data storage is built around the applications that will use the files. • In the database environment, applications will be built around the integrated database. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  4. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  5. Conventional Files Versus the Database • The Pros and Cons of Conventional Files • Pros: • Conventional files are relatively easy to design and implement because they are normally based on a single application or information system. • Historically, another advantage of conventional files has been processing speed. • Cons: • Duplication of data items in multiple files is normally cited as the principal disadvantage of file-based systems. • A significant disadvantage of files is their inflexibility and non-scaleability. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  6. Conventional Files Versus the Database • The Pros and Cons of Conventional Files • As legacy file-based systems and applications become candidates for reengineering, the trend is overwhelmingly in favor of replacing file-based systems and applications with database systems and applications. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  7. Conventional Files Versus the Database • The Pros and Cons of Database • Pros: • The principal advantage of a database is the ability to share the same data across multiple applications and systems. • Database technology offers the advantage of storing data in flexible formats. • Databases allow the use of the data in ways not originally specified by the end-users - data independence. • The database scope can even be extended without impacting existing programs that use it. • New fields and record types can be added to the database without affecting current programs. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  8. Conventional Files Versus the Database • The Pros and Cons of Database • Cons: • Database technology is more complex than file technology. • Special software, called a database management system (DBMS), is required. • A DBMS is still somewhat slower than file technology. • Database technology requires a significant investment. • The cost of developing databases is higher because analysts and programmers must learn how to use the DBMS. • In order to achieve the benefits of database technology, analysts and database specialists must adhere to rigorous design principles. • Another potential problem with the database approach is the increased vulnerability inherent in the use of shared data. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  9. Conventional Files Versus the Database • Database Design in Perspective • To fully exploit the advantages of database technology, a database must be carefully designed. • The end product is called a database schema, a technical blueprint of the database. • Database design translates the data models that were developed for the system users during the definition phase, into data structures supported by the chosen database technology. • Subsequent to database design, system builders will construct those data structures using the language and tools of the chosen database technology. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  10. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  11. Database Concepts for the Systems Analyst • Fields • Fields are common to both files and databases. • A field is the implementation of a data attribute. • Fields are the smallest unit of meaningful data to be stored in a file or database. • There are four types of fields that can be stored: primary keys, secondary keys, foreign keys, and descriptive fields. • Primary keys are fields whose values identify one and only one record in a file. • Secondary keys are alternate identifiers for an database. • A single file in a database may only have one primary key, but it may have several secondary keys. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  12. Database Concepts for the Systems Analyst • Fields • There are four types of fields that can be stored: primary keys, secondary keys, foreign keys, and descriptive fields. (continued) • Foreign keys are pointers to the records of a different file in a database. • Foreign keys are how the database ‘links’ the records of one type to those of another type. • Descriptive fields are any other fields that store business data. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  13. Database Concepts for the Systems Analyst • Records • Fields are organized into records. • Like fields, records are common to both files and databases. • A record is a collection of fields arranged in a predefined format. • During systems design, records will be classified as either fixed-length or variable-length records. • Most database systems impose a fixed-length record structure, meaning that each record instance has the same fields, same number of fields, and same logical size. • Variable-length record structures allow different records in the same file to have different lengths. • Database systems typically disallow (or, at least, discourage) variable length records. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  14. Database Concepts for the Systems Analyst • Records • When a computer program ‘reads’ a record from a database, it actually retrieves a group or block of records at a time. • This approach minimizes the number of actual disk accesses. • A blocking factor is the number of logical records included in a single read or write operation (from the computer’s perspective). A block is sometimes called a physical record. • Today, the blocking factor is usually determined and optimized by the chosen database technology, but a qualified database expert may be allowed to fine tune that blocking factor for performance. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  15. Database Concepts for the Systems Analyst • Files and Tables • Similar records are organized into groups called files. • A file is the set of all occurrences of a given record structure. • In database systems, a file corresponds to a set of similar records; usually called a table. • A table is the relational database equivalent of a file. • Some of the types of files and tables include: • Master files or tables contain records that are relatively permanent. • Once a record has been added to a master file, it remains in the system indefinitely. • The values of fields for the record will change over its lifetime, but the individual records are retained indefinitely. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  16. Database Concepts for the Systems Analyst • Files and Tables • Some of the types of files and tables include: (continued) • Transaction files or tables contain records that describe business events. • The data describing these events normally has a limited useful lifetime. • In information systems, transaction records are frequently retained on-line for some period of time. • Subsequent to their useful lifetime, they are archived off-line. • Document files and tables contain stored copies of historical data for easy retrieval and review without the overhead of re-generating the document. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  17. Database Concepts for the Systems Analyst • Files and Tables • Some of the types of files and tables include: (continued) • Archival files and tables contain master and transaction file records that have been deleted from on-line storage. • Records are rarely deleted; they are merely moved from on-line storage to off-line storage. • Archival requirements are dictated by government regulation and the need for subsequent audit or analysis. • Table look-up files contain relatively static data that can be shared by applications to maintain consistency and improve performance. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  18. Database Concepts for the Systems Analyst • Files and Tables • Some of the types of files and tables include: (continued) • Audit files are special records of updates to other files, especially master and transaction files. • They are used in conjunction with archive files to recover ``lost’’ data. • Audit trails are typically built into better database technologies. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  19. Database Concepts for the Systems Analyst • Databases • Databases provide for the technical implementation of entities and relationships. • The history of information systems has led to one inescapable conclusion: • Data is a resource that must be controlled and managed! • Out of necessity, database technology was created so an organization could maintain and use its data as an integrated whole instead of as separate data files. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  20. Database Concepts for the Systems Analyst • Databases • Data Architecture: • A business’ data architecture is comprised of the files and databases that store all of the organization’s data, the file and database technology used to store the data, and the organization structure set up to manage the data resource. • Operational databases have been developed to support day-to-day operations and business transaction processing for major information systems. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  21. Database Concepts for the Systems Analyst • Databases • Data Architecture: • Many information systems shops hesitate to give end-users access to operational databases, because the volume of unscheduled reports and queries could overload the computers and hamper business operations. • To remedy that problem, data warehouses were developed. computers. • Data warehouses store data that is extracted from the production databases and conventional files. • Fourth-generation programming languages, query tools, and decision support tools are then used to generate reports and analyses off these data warehouses. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  22. Database Concepts for the Systems Analyst • Databases • Data Architecture: • Personal computer and local network database technology has rapidly matured to allow end-users to develop personal and departmental databases. • These databases may contain unique data, or they may import data from conventional files, operational databases, and/or data warehouses. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  23. Database Concepts for the Systems Analyst • Databases • Data Architecture: • To manage the enterprise-wide data resource, a staff of database specialists may be organized around the following administrators: • A data administrator is responsible for the data planning, definition, architecture, and management. • One or more database administrators are responsible for the database technology, database design and construction, security, backup and recovery, and performance tuning. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  24. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  25. Database Concepts for the Systems Analyst • Databases • Database Architecture: • Database architecture refers to the database technology including the database engine, database management utilities, database CASE tools for analysis and design, and database application development tools. • The control center of a database architecture is its database management system. • A database management system (DBMS) is specialized computer software available from computer vendors that is used to create, access, control, and manage the database. The core of the DBMS is often called its database engine. The engine responds to specific commands to create database structures, and then to create, read, update, and delete records in the database. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  26. Database Concepts for the Systems Analyst • Databases • Database Architecture: • A systems analyst, or database analyst, designs the structure of the data in terms of record types, fields contained in those record types, and relationships that exist between record types. • These structures are defined to the database management system using its data definition language. • Data definition language (or DDL) is used by the DBMS to physically establish those record types, fields, and structural relationships. Additionally, the DDL defines views of the database. Views restrict the portion of a database that may be used or accessed by different users and programs. DDLs record the definitions in a permanent data repository. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  27. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  28. Database Concepts for the Systems Analyst • Databases • Database Architecture: • Some data dictionaries include formal, elaborate software that helps database specialists track metadata – the data about the data –such as record and field definitions, synonyms, data relationships, validation rules, help messages, and so forth. • The database management system also provides a data manipulation language to access and use the database in applications. • A data manipulation language (or DML) is used to create, read, update, and delete records in the database, and to navigate between different records and types of records. The DBMS and DML hide the details concerning how records are organized and allocated to the disk. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  29. Database Concepts for the Systems Analyst • Databases • Database Architecture: • Many DBMSs don’t require the use of a DDL to construct the database, or a DML to access the database. • They provide their own tools and commands to perform those tasks. This is especially true of PC-based DBMSs. • Many DBMSs also include proprietary report writing and inquiry tools to allow users to access and format data without directly using the DML. • Some DBMSs include a transaction processing monitor (or TP monitor) that manages on-line accesses to the database, and ensures that transactions that impact multiple tables are fully processed as a single unit. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  30. Database Concepts for the Systems Analyst • Databases • Relational Database Management Systems: • There are several types of database management systems and they can be classified according to the way they structure records. • Early database management systems organized records in hierarchies or networks implemented with indexes and linked lists. • Relational databases implement data in a series of tables that are ‘related’ to one another via foreign keys. • Files are seen as simple two-dimensional tables, also known as relations. • The rows are records. • The columns correspond to fields. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  31. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  32. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  33. Database Concepts for the Systems Analyst • Databases • Relational Database Management Systems: • Both the DDL and DML of most relational databases is called SQL (which stands for Structured Query Language). • SQL supports not only queries, but complete database creation and maintenance. • A fundamental characteristic of relational SQL is that commands return ‘a set’ of records, not necessarily just a single record (as in non-relational database and file technology). Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  34. Database Concepts for the Systems Analyst • Databases • Relational Database Management Systems: • High-end relational databases also extend the SQL language to support triggers and stored procedures. • Triggers are programs embedded within a table that are automatically invoked by a updates to another table. • Stored procedures are programs embedded within a table that can be called from an application program. • Both triggers and stored procedures are reusable because they are stored with the tables themselves. • This eliminates the need for application programmers to create the equivalent logic within each application that use the tables. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  35. Data Analysis for Database Design • What is a Good Data Model? • A good data model is simple. • As a general rule, the data attributes that describe an entity should describe only that entity. • A good data model is essentially non-redundant. • This means that each data attribute, other than foreign keys, describes at most one entity. • A good data model should be flexible and adaptable to future needs. • We should make the data models as application-independent as possible to encourage database structures that can be extended or modified without impact to current programs. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  36. Data Analysis for Database Design • Data Analysis • The technique used to improve a data model in preparation for database design is called data analysis. • Data analysis is a process that prepares a data model for implementation as a simple, non-redundant, flexible, and adaptable database. The specific technique is called normalization. • Normalization is a technique that organizes data attributes such that they are grouped together to form stable, flexible, and adaptive entities. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  37. Data Analysis for Database Design • Data Analysis • Normalization is a three-step technique that places the data model into first normal form, second normal form, and third normal form. • An entity is in first normal form (1NF) if there are no attributes that can have more than one value for a single instance of the entity. • An entity is in second normal form (2NF) if it is already in 1NF, and if the values of all non-primary key attributes are dependent on the full primary key – not just part of it. • An entity is in third normal form (3NF) if it is already in 2NF, and if the values of its non-primary key attributes are not dependent on any other non-primary key attributes. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  38. Data Analysis for Database Design • Normalization Example • First Normal Form: • The first step in data analysis is to place each entity into 1NF. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  39. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  40. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  41. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  42. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  43. Data Analysis for Database Design • Normalization Example • Second Normal Form: • The next step of data analysis is to place the entities into 2NF. • It is assumed that you have already placed all entities into 1NF. • 2NF looks for an anomaly called a partial dependency, meaning an attribute(s) whose value is determined by only part of the primary key. • Entities that have a single attribute primary key are already in 2NF. • Only those entities that have a concatenated key need to be checked. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  44. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  45. Data Analysis for Database Design • Normalization Example • Third Normal Form: • Entities are assumed to be in 2NF before beginning 3NF analysis. • Third normal form analysis looks for two types of problems, derived data and transitive dependencies. • In both cases, the fundamental error is that non key attributes are dependent on other non key attributes. • Derived attributes are those whose values can either be calculated from other attributes, or derived through logic from the values of other attributes. • A transitive dependency exists when a non-key attribute is dependent on another non-key attribute (other than by derivation). • Transitive analysis is only performed on those entities that do not have a concatenated key. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  46. Data Analysis for Database Design • Normalization Example • Third Normal Form: • Third normal form analysis looks for two types of problems, derived data and transitive dependencies. (continued) • A transitive dependency exists when a non-key attribute is dependent on another non-key attribute (other than by derivation). • This error usually indicates that an undiscovered entity is still embedded within the problem entity. • Transitive analysis is only performed on those entities that do not have a concatenated key. • “An entity is said to be in third normal form if every non-primary key attribute is dependent on the primary key, the whole primary key, and nothing but the primary key.” Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  47. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  48. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  49. Data Analysis for Database Design • Normalization Example • Simplification by Inspection: • When several analysts work on a common application, it is not unusual to create problems that won’t be taken care of by normalization. • These problems are best solved through simplification by inspection, a process wherein a data entity in 3NF is further simplified by such efforts as addressing subtle data redundancy. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

  50. Data Analysis for Database Design • Normalization Example • CASE Support for Normalization: • Most CASE tools can only normalize to first normal form. • They accomplish this in one of two ways. • They look for many-to-many relationships and resolve those relationships into associative entities. • They look for attributes specifically described as having multiple values for a single entity instance. • It is exceedingly difficult for a CASE tool to identify second and third normal form errors. • That would require the CASE tool to have the intelligence to recognize partial and transitive dependencies. Prepared by Kevin C. Dittman for Systems Analysis & Design Methods 4ed by J. L. Whitten & L. D. Bentley

More Related