Database Comparisons: An Overview

1. Database Comparisons: An Overview Instructor: Vicki Weidler Assistant:

2. Overview • Understand what to look for when choosing a database solution • Define basic database terminology • Describe different database models • Explain the advantages and disadvantages of each model • Compare MS Excel, MS Access, FileMaker Pro & MS Visual FoxPro

3. What is a Database? • Collection of data • Stored for a specific purpose • Easily accessed, managed, & updated • Electronic filing system

4. What is a DBMS? • Database Mgmt System • Extra layer of software between the applications programs and the physical access to the data

5. Database Models • “Description” of • Container for data • Methodology for storing and retrieving data • Abstractions (i.e. mathematical algorithms & concepts) • Advancements have increased database efficiency

6. Timeline Late 1950’s Early 1960’s 1960’s • Large corporations • developed their own • transaction • processing systems • Mainframe disk • pack storage • medium of choice • Assembler, • COBOL, Fortran, • PL/1 • Flat file storage & • retrieval • techniques • Paper tape • storage used • briefly • Magnetic tape • replaced it • Mechanical • movement using • tapes & tape • drives • Sequential file • access • File-oriented • Flat file model • Limited govt & • commercial use • Punch cards • Large mainframes

7. Timeline 1960’s • Submit work orders • to programmers to • write a program to • display the info they • needed • Caused backlogs & • long delays before • they could see/use • info

8. Flat File Model

9. Data Hierarchy & Data Storage BITS (0,1/binary digit) BYTES (8 bits/ASCII/EBCDIC) FIELDS (group of related bytes) RECORDS (group of related fields) FILES (group of related records) DATABASES (group of related files)

10. Timeline 1970’s 1964-1965 1966 • Proliferation of • hierarchical model • products • Private within each • organization; no • public access to the • database • Random/direct file • access • IBM released first • commercially • available DBMS • Called IMS (Info • Mgmt System) • Based on • hierarchical model • Hierarchical data • model concept

11. Hierarchical Model Continued

12. Hierarchical Model Continued

13. Hierarchical Model Continued

14. Hierarchical Model Continued

15. Timeline 1967 1957 1964-1965 • GE developed IDS • based on network • model • Honeywell produced • IDS-2 • U.S. Dept. of • Defense founded • CODASYL to • develop computer • programming • languages • Network data model • developed

16. Timeline 1969 1970’s • IDMS commands • were embedded in • programming • languages & used • on mainframe • systems • Mid-range • computers called • minicomputers were • developed • CODASYL Database • Task Group set • standards for • network database • products • Proliferation of • network model • products • Major player was • Cullinet software • which developed • IDMS using the • network model

17. Social Security Info Market Research Human Resources Name, Address, Telephone # Job Function Management Group

18. Timeline 1970’s 1980’s 1980’s • Microrim created • RBASE • ANSI adopted SQL • as a standard • Borland produced • Paradox • Late 1980’s, object- • oriented model was • born • Dr. E.F. Codd, IBM • researcher, • proposed relational • data model theory • IBM developed a • relational model • prototype called • System R • IBM released first • commercial • relational database • called SQL/DS • (Structured Query • Language/Data • System) for its • mainframes • IBM brought PCs to • the market • IBM produced DB2 • (SQL-based) for main- • frames with batch • operating system

19. Timeline 1990’s 1990’s 1990’s • Using LANs, the • corporate database • might still reside on • the mainframe, but • data also could be • downloaded & • analyzed on the • desktop • Past models • primarily stored text • & numeric data • Microsoft bought • Fox Pro Software • Microsoft released • MS Access • SAP R/3 released • for client/server • environment • Power of LANs • became evident & • migration to client/ • server systems • Telecommunications • infrastructure • created for the • Internet & first public • access to WANS • (worldwide network of • networks) • Microcomputers • became more common • in the workplace • Desktop computers • had equivalent power • to mainframe systems • of the 1960’s

20. Relational Terminology

21. Relational Model Continued

22. Timeline 1990’s • Desktop computing • included GUI • (graphical user • interfaces) & • multimedia • capabilities • DBMS had to adapt • new capabilities • Databases now • include text data, • but also objects

23. Object-Oriented Database Model • Some objects may have multimedia characteristics (i.e. drawings, sound bites or video clips) • Example: CAD engineering drawing might include operations to rotate or enlarge • Object-oriented database systems (OODBMS) have been developed as a result of object oriented programming techniques • In an object-oriented database, data encapsulated with the valid operations on that data • Some applications may require an OODBMS • However, b/c of the time and cost involved, many organizations are hesitant to convert their existing transaction systems to object-oriented methodologies

24. Object-Oriented Programming • Object-oriented programming designed specifically for the purpose of creating objects composed of reusable software code • Using OOP, programmers can build programs using chunks or modules consisting of preassembled code and data • Conventional programming is like building a car a piece at a time, screw-by-screw • OOP is more like building a car in modular fashion

25. Hybrid Models • Nested • Object-Relational

26. Advantages of Nested Model • Fast & flexible development • No problems handling complex text-oriented data structures • Low administrative costs • Usually simpler to administer than RDBMS • More efficient • More users on less power & memory

27. Disadvantages of Nested Model • Minor market segment when compared to RDBMS market • In early days, PICK was prone to data corruption • Today, reliability just as good as relational DBMS • Has transaction logging & similar features built in • Data query language, although easier to use than SQL, was “not SQL” and associated tools for data querying could not be used against a nested DBMS • Now most can be queried using SQL

28. Timeline Late 1990’s Late 1990’s • Debate ensued as to • whether hybrid • object-relational • DBMS like Oracle8 • or Informix/Illustra • will prove more • effective at handling • future requirements • than a pure • OODBMS • Object-relational • approach • Illustra (commercial • version of Univ of • California-Berkeley’s • POSTGRES project) • which was • subsequently • bought by Informix

29. Current Status • Because of the need to store large multimedia files, the object-oriented approach to databases seems to be gaining in popularity, but many organizations continue to use relational databases b/c of the power and user-friendly interfaces these systems provide. Both play a role today. • DBMS allow for several users to access data in a database from different perspectives, and also allow for users to have varying access rights in terms of entering, manipulating, and retrieving data. By empowering the end users, organizations can run at a very efficient pace.

30. Evolution of data storage & retrieval techniques & emergence of DBMS • Increasingly powerful hardware has made it possible to handle ever-increasing volumes of data • Data warehousing & consumer profiling have major commercial value • Evolution of telecommunications & computer networks has made data sharing & data collection more prevalent • Security and privacy have become major concerns • Volumes of data that are being collected and availability of data on the Internet • Programming languages like HTML, Java, & Perl are used to develop Web applications

31. Evolution of data storage & retrieval techniques & emergence of DBMS • Firewalls have been developed to protect hardware, software, and databases from unauthorized access or destruction from hackers via the Internet • Each new technology has had an impact on data organization • ColdFusion is an example of an application server that interprets special commands embedded in HTML to interact with a database; gaining popularity among web developers for its ability to manage a website through a database & its ease of coding • Developers beginning to embrace XML to create database-driven websites where the content for each web page is stored in a database

32. Why use a DBMS? • DBMS provides an interface to access data from a variety of different views • Help control data redundancy & data integrity with several tools that help the user manage the data • Commercial DBMS’s have tools to define data structure, security for protecting data, query features, reporting features, utilities for administering, tracking and monitoring data access

33. Why use a DBMS? • DBMS include a data dictionary which contains descriptions of individual data items • This allows record descriptions to be removed from the programs so that when new fields are added, changed, or deleted, only the data dictionary needs to be changed & the application programs might not have to be changed • Also, access methods are determined by database software and do not have to be coded in the applications programs • Major feature of DBMS is the automatic collection & storage of the metadata (data about the data) • Metadata are kept in the data dictionary which includes the correct spelling of each field name, data type for the field, length of the field, descriptions of valid content for the field

34. Why use a DBMS? • Data dictionary is stored as part of the database and is automatically built by using the data definition language (DDL) of the DBMS • All DBMS contain data definition language (DDL) which allows access to the meta-data or descriptions of the data & data manipulation language (DML) which allows access to the actual data • Data dictionary is a table of tables containing a list of all the tables as well as the structure of the tables & special info about the database itself

35. Why use a DBMS? • Data integrity refers to the accuracy or correctness of the data • Data validation rules (rules for determining the accuracy of data) & corresponding error messages can be entered as part of the DDL and recorded in the data dictionary • DBMS automatically checks input against the rules in the dictionary and if necessary, displays an error message for the user • This greatly simplifiesapplications programming

36. Why use a DBMS? • Data Redundancy occurs when the same data are stored in multiple locations • Causes update problems • Updates require that the data be changed in every location • Causes extra work and can cause data inconsistency if it changes in some locations and not others • In order to avoid this, relationships are established between data files to reduce multiple instances of same data • Use relationships when there is a need to link data items that are stored in one data file with records in other files

37. Why use a DBMS? • Another feature in DBMS is multiple user access and user views • Concurrent usage • Automatic record locks that are applied to a record when a user accesses that record to make a change • This prevents another user from accessing that record a the same time • Views can be customized and simple security measures added to that view

38. Summary • Understand what to look for when choosing a database solution • Define basic database terminology • Describe different database models • Explain the advantages and disadvantages of each model • Compare MS Excel, MS Access, FileMaker Pro & MS Visual FoxPro

39. Conclusion • Resources • Questions & Answers • Evaluations • Thank You!!!