1 / 46

COMP 5305 Advanced Databases

COMP 5305 Advanced Databases. COMP 5305: Advanced Database Systems. Course contents Database review Relational Model review Relational Algebra Tuple Relational Calculus Domain Relational Calculus Datalog. Course Contents:. Nested Relational Data Model Algebra and Calculus Languages

cstokes
Download Presentation

COMP 5305 Advanced Databases

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. COMP 5305Advanced Databases

  2. COMP 5305: Advanced Database Systems • Course contents • Database review • Relational Model review • Relational Algebra • Tuple Relational Calculus • Domain Relational Calculus • Datalog

  3. Course Contents: • Nested Relational Data Model Algebra and Calculus Languages • Object-Oriented Data Model (ODMG 3.0) ODL, OQL Languages • Object-Relational Databases (Oracle 11g) Extended SQL Language • XML Databases XQuery Language • INM Databases • NoSQL Databases

  4. Database Review • Data: • A value that represents known facts with an implicit meaning. • Database: • A collection of related data stored on a computer. • Mini-world: • Some part of the real world about which data is stored in a database. For example, student, course and grade at a university. • Database Management System (DBMS): • Software to facilitate the creation and maintenance of a computerized database. • Database System: • The DBMS software together with the database itself. Sometimes, the applications are also included.

  5. Simplified database system environment

  6. Typical DBMS Functionality • Construct or Load the initial database contents on a secondary storage medium • Provide Data Definition Language (DDL) to Define a particular database in terms of its data types, structures, and constraints • Create, Alter, Drop • Provide Data Manipulation Language (DML) to Manipulate the database: • Insert, delete and update its content • Provide Query Language (QL) to Retrieve the database: • Query, generate reports • Accessing the database through Web applications

  7. Typical DBMS Functionality (continue) • Processing and Sharing by a set of concurrent users and application programs – yet, keeping all data valid and consistent • Protection or Security measures to prevent unauthorized access • Presentation and Visualization of data • Maintaining the database and associated programs over the lifetime of the database application

  8. History of Data Models • Hierarchical Model • Network Model • Relational Model • Object-oriented Data Models • Object Role Models • Object-Relational Models

  9. Hierarchical Model • Introduced in mid 1960s and dominated during 1970s. • Initially implemented in a joint effort by IBM and North American Rockwell around 1965. • Resulted in the IMS family of systems.

  10. Hierarchical Model • The data is organized into a tree-like structure. • Representing information using parent/child relationships: • each parent can have many children but • each child only has one parent. • 1:N • All attributes of a specific record are listed under an entity type.

  11. Hierarchical Model • Advantages: • Simple to construct and operate • Corresponds to a number of natural hierarchically organized domains, e.g., organization (“org”) chart • Language is simple: • Uses constructs like GET, GET UNIQUE, GET NEXT, GET NEXT WITHIN PARENT, etc. • Disadvantages: • Navigational and procedural nature of processing • Database is visualized as a linear arrangement of records • Little scope for "query optimization“ • Cannot represent many to many relationships naturally

  12. Database Software Pioneer • Graduated in 1948 with a bachelor's degree in Mechanical Engineering • Graduated In 1950 with a master's degree in Mechanical Engineering • Working 10 years at Dow Chemical in Midland, Michigan • Joined in 1960 General Electric • Developed the Integrated Data Store (IDS) in 1962 to automate the business processes of the General Electric Low Voltage Switch Gear Department in Philadelphia • Received ACM’s Turing Award in 1973 without a Ph.D Charles Bachman born Dec 11, 1924

  13. The Integrated Data Store (IDS) • At the start of the 1960s computer science was beginning to emerge as an academic field, but its early stars focused on programming language design, theory of computation, numerical analysis, and operating system design. • Like modern DBMS, IDS explicitly stored and manipulated metadata about the records and their relationships, rather than expecting each application program to understand and respect the format of every data file it worked with. • It could enforce relationships between different record types, and would protect database integrity. Database designers would specify indexes and other details of record organization to boost performance based on expected usage patterns.

  14. Network Model • Introduced in 1960s by Charles Bachman (who wins 1973 Turing award) • Implemented by Honeywell in 1964-65 (IDS System). • Adopted heavily due to the support by CODASYL (Conference on Data Systems Languages) • Later implemented in a large variety of systems • IDMS (Cullinet - now Computer Associates), • DMS 1100 (Unisys), • IMAGE (H.P. (Hewlett-Packard)), • VAX -DBMS (Digital Equipment Corp., next COMPAQ, now H.P.).

  15. Network Model • The data is organized into a graph (lattice) structure. • each parent can have many children • each child can also have many parents. • N:M • All attributes of a specific record • are listed under an entity type.

  16. Network Model • Advantages: • Network Model is able to model complex N:M relationships and represents semantics of add/delete on the relationships. • Can handle most situations for modeling using record types and relationship types. • Language is navigational; uses constructs like FIND, FIND member, FIND owner, FIND NEXT within set, GET, etc. • Programmers can do optimal navigation through the database.

  17. Network Model • Disadvantages: • Navigational and procedural nature of processing • Database contains a complex array of pointers that thread through a set of records. • Little scope for automated “query optimization”

  18. Relational Databases • born on the Isle of Portland in England • studied mathematics and chemistry at Exeter College, Oxford • Worked for IBM as a mathematical programmer in 1948 • moved to Ottawa in 1953 for 10 years • returned to US and received his doctorate in computer science from the University of Michigan in Ann Arbor in 1965 • then,worked at IBM's San Jose Research Laboratory E.F. Codd Aug.23,1923 Apr.18,2003

  19. Relational Databases • In the 1960s and 1970s he worked out his theories of data arrangement, issuing his paper "A Relational Model of Data for Large Shared Data Banks" in 1970, after an internal IBM paper one year earlier. • IBM refused to implement the relational model in order to preserve revenue from IMS/DB. • Codd then showed IBM customers the potential of the implementation of its model, and they in turn pressured IBM. • Continued to develop and extend his relational model E.F. Codd Aug.23,1923 Apr.18,2003

  20. Relational Databases • IBM included in its Future Systems project a System R subproject in 1974, but put in charge of it developers who were not thoroughly familiar with Codd's ideas, and isolated the team from Codd. • they did not use Codd's own Alpha language but created a non-relational one, SEQUEL(SQL), which has since become the standard relational database language. • System R with SQL started in 1974 and finished in 1977 as a prototype. Commercial products SQL/DS(1983) and DB2 (1983) for its mainframe computers • Codd Received ACM’s Turing Award in 1981 E.F. Codd Aug.23,1923 Apr.18,2003

  21. Relational Databases • As the relational model started to become fashionable in the early 1980s, Codd fought a sometimes bitter campaign to prevent the term being misused by database vendors who had merely added a relational veneer to older technology. As part of this campaign, he published his 12 rules to define what constituted a relational database. • This made his position in IBM increasingly difficult, so he left to form his own consulting company with Chris Date and others E.F. Codd Aug.23,1923 Apr.18,2003

  22. Relational Databases: Oracle • born in the Lower East Side of Manhattan, New York City to a unwed Jewish mother, father was an Italian American US force pilot • After he contracted pneumonia at the age of nine months, his mother gave him to her aunt and uncle for adoption. He did not meet his biological mother again until he was 48 • raised in a Reform Jewish home • was a bright but inattentive student, left the University of Illinois at Urbana-Champaign after his second year • attended the University of Chicago for one term, where he first encountered computer design Larry Ellison Born Aug 17, 1944 The third wealthiest American citizen

  23. Relational Databases: Oracle • at 20 years of age, moved to Northern California permanently and worked for Ampex Corporation during 1970s • one of his project was a database for CIA, called Oracle. • In 1977, inspired by E.F. Codd’s paper on relational database system, and founded consultancy Software Development Laboratories (SDL) with his friends, former coworkers Bob Miner and Ed Oates Larry Ellison Born Aug 17, 1944 The third wealthiest American citizen

  24. Relational Databases: Oracle • It implemented a relational database system called Oracle on Unix operating systems • In 1978, Oracle Version 1 finished but not released. • In 1979, changed to Relational Software, Inc. and released its Oracle 2, run on PDP-11. • In 1982, changed to Oracle Systems Corp. • In 1990, big loss and layoff, almost bankrupted. • In 1995, changed to Oracle Corporation • In 2014, Forbes listed him as the third-wealthiest man in America and as the fifth-wealthiest person in the world, with a fortune of $52 billion. Larry Ellison Born Aug 17, 1944 The third wealthiest American citizen

  25. Informix • Founded Relatoinal Databsae Systems (RDS) in 1980 by Roger Sippl and Laura King • Released their Relational database Product Informix (INFORMation on unIX) in 1981. • It grewed gradually and then changed to Informix Software • In 1995, purchased Illustra, focused on object-relational databases. It released the first object-relational databases Informix Universal Server in 1996, making it the first big three DB company (Oracle, Sybase, Informix) to offer built-in object relation support. • In late 1996, product releases began to fall behind schedule, with 10 key people joined Oracle in early 1997, it struggled • In April 2001 IBM bought from Informix the database technology.

  26. Relational Databases: Sybase • Founded in 1984 by Mark Hoffman, Bob Epstein, Jane Doughty and Tom Haggin in Epstein’s home in Berkeley, California • In late 1986, Sybase shipped its first test programs, and in May 1987 formally released the SYBASE system, the first high-performance RDBMS for online applications. • Rather than having a vast central bank of data stored in a large mainframe computer, the SYBASE was the first to provide a client/server computer architecture. The server is called Sybase SQL Server

  27. Relational Databases: Sybase • Until 1993, Sybase was the fastest growing database company and the database industry's darling vendor, various new versions were released • Then it acquired Powersoft, the leading maker of development tools for client-server computing, which resulted in a loss of focus on its core database technology • In 1993, Sybase sold the rights to its database software running under the Windows operating system to Microsoft Corporation, which now markets it under the name "SQL Server. • It does other business instead thereafter

  28. Microsoft SQL Server • In 1989, Microsoft started to sell Sybase system and call it SQL Server 1.0 for IBM OS/2 system • In 1993, Microsoft released its operating system Windows NT, and it bought SQL Server code specific for Windows NT from Sybase and called it SQL Server 4.21 • Gradually, it modified the system with its own code. In 2005, it completely rewrote SQL Server code and released its SQL Server 2005

  29. Relational Database Wars • IBM dominated the mainframe relational database market with its SQL/DS(1981) and DB2 (1983) database products, it delayed entering into mini and microcomputer. • Oracle, Sybase, Informix dominated mini and microcomputers • Oracle almost went bankrupt in 1990 • Sybase was far ahead of Oracle and expanded rapidly, resulted in a loss of focus on DB and sold its DB software to Microsoft in 1993, which now markets it under SQL Server • Informix overtook Sybase between 1994-1997 and competed with Oracle, but its CEO landed in Jail in 1997 and Informix relational DB division was taken by IBM in 2001 • Since then, Oracle enjoyed years of industry dominance

  30. Relational DB History

  31. 2011 Market Share

  32. Market Shares in 2011 • Oracle DB on Mini&Micro Computers 48.8% • IBM (DB2 database) on Mainframe 20.2% • Microsoft (SQL Server) on PC 17.0% • SAP/Sybase 4.6% 24 billions US dollar sales in 2011

  33. MySQL • Initially released in 23 May 1995 by the Swedish company MySQL • The world second most widely used RDBMS • It was acquired by Sun Microsystems in 2008 for $1 billion, which was in turn acquired by Oracle Corporation in 2010. • The world's most popular open source database. With over 65,000 downloads per day • Popular choice of database for use in web applications (Linux, Apache, MySQL, Perl/PHP/Python)

  34. Database Installations and Deployment Plans - 2008

  35. Microsoft Access • RBASE was the first relational database program for the PC, created by Wayne Erickson in 1981. He and his brother then incorporated the company, MicroRim, Inc. to sell the database, MicroRIM (Relational Information Management) • Privately funded and ultimately venture backed, the MicroRim database products achieved significant market share in the mid-1980s • Microsoft's first attempt to sell a relational database product was during the mid 1980s, when Microsoft obtained license to sell Rbase • In the late 1980s, Microsoft developed its own relational database Omega and eventually demonstrated in 1992 to several journalists and then abandoned.

  36. Microsoft Access • In order to develop a relational database on Windows, it started another project Cirrus. • In November, 1992, it released Access Version 1.0, the software worked well with relatively small databases but testing showed that some circumstances caused data corruption, when file size over 10MB. • With Office 95, Microsoft Access 7.0 became part of the Microsoft Office Professional Suite, joining Microsoft Excel, Word, and PowerPoint. Since then, Microsoft has released new versions of Microsoft Access with each release of Microsoft Office. This includes Access 97 (version 8.0), Access 2000 (version 9.0), Access 2002 (version 10.0), Access 2003 (version 11.5), Access 2007 (version 12.0), and Access 2010 (version 14.0).

  37. SQL Versions American National Standard Institute (ANSI) • SQL or SQL-86 • SQL2 or SQL-89 • SQL2 or SQL-92 • SQL3 or SQL-99 • SQL4 or SQL:2003 • SQL5 or SQL:2006 • SQL6 or SQL:2008 • SQL7 or SQL:2011

  38. Relational Model • all data is represented in terms of tuples, grouped into relations

  39. Object-oriented Models • Introduced in late 1980s and early 1990s to deal with complex data processing in CAD and other applications. • Information is represented in the form of objects, classes and inheritance as in object-oriented programming but objects are persistent

  40. Object-oriented Models • Many systems based on different languages • C++ (e.g., in OBJECTSTORE or VERSANT) • Smalltalk (e.g., in GEMSTONE). • O2, • ORION (at MCC - then ITASCA) • IRIS (at H.P.- used in Open OODB). • Object Database Standard: • ODMG 1.0 1993 • ODMG 2.0 1997 • ODMG 3.0 2001

  41. Object-Relational Models • Most Recent Trend. Started with Informix Universal Server. • Extend relational model with an object-oriented model: objects, classes and inheritance are directly supported in database schemas and in the query language. • In addition, just as with proper relational systems, it supports extension of the data model with custom data-types and methods.

  42. Object-Relational Models • Systems • Oracle-8i and later version • DB2 • SQL Server

  43. Database Management System High-level Query Q • Answer • Translates Q into • best execution plan • for current conditions, • runs plan • DBMS • Data

  44. Example: At a Company Query 1: Is there an employee named “Nemo”? Query 2: What is “Nemo’s” salary? • Query 3: How many departments are there in the company? Query 4: What is the name of “Nemo’s” department? Query 5: How many employees are there in the “Accounts” department? • Employee • Department

  45. Join (Cars.OwnerID = Owners.ID) • Filter (Make = Honda and • Model = Accord) • Filter (Age <= 23) Example: Store that Sells Cars • Owners of • Honda Accords • who are <= • 23 years old • Cars • Owners

  46. DBMS is multi-user • ExampleGet account balance from database;If balance > amount of withdrawal then balance = balance - amount of withdrawal; dispense cash; store new balance into database; • Homer at ATM1 withdraws $100 • Marge at ATM2 withdraws $50 • Initial balance = $400, final balance = ? • Should be $250 no matter who goes first

More Related