1 / 35

Database

Database. What is a database ? Give examples of databases. Database. What is a database ? A collection of files storing related data Give examples of databases Accounts database; payroll database; KMUTNB’s students database; Amazon’s products database; airline reservation database.

hstaley
Download Presentation

Database

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. Database What is a database ? Give examples of databases

  2. Database What is a database ? • A collection of files storing related data Give examples of databases • Accounts database; payroll database; KMUTNB’s students database; Amazon’s products database; airline reservation database

  3. Database Management System What is a DBMS ? Give examples of DBMS

  4. Database Management System What is a DBMS ? • A big C program written by someone else that allows us to manage efficiently a large database and allows it to persist over long periods of time Give examples of DBMS • DB2 (IBM), SQL Server (MS), Oracle, Sybase • MySQL, Postgres, …

  5. Market Shares Over the past 5 years www.computerworld.com • Oracle has dropped 1.5 points since 2011 to 41.6%, • And IBM 5.6 points to 16.5%, surrendering second place to Microsoft, who gained 0.8 to 19.4%.

  6. Top 10 Enterprise Database Systems of 2016 • Oracle began its journey in 1979 as the first commercially available relational database management system (RDBMS). By Forrest Stroud Posted December 7, 2016

  7. Top 10 Enterprise Database Systems of 2016 • SQL Server's ease of use, availability and tight Windows operating system integration makes it an easy choice for firms that choose Microsoft products for their enterprises.

  8. Top 10 Enterprise Database Systems of 2016 • The latest release of DB2, DB2 11.1, runs on Linux, UNIX, Windows, the IBM iSeries and mainframes.

  9. Top 10 Enterprise Database Systems of 2016 • Sybase is still a major force in the enterprise market after 25 years of success and improvements to its Adaptive Server Enterprise product.

  10. Top 10 Enterprise Database Systems of 2016 • PostgreSQL, or simply Postgres, is an open-source object-relational database management system (ORDBMS) that hides in such interesting places as online gaming applications, data center automation suites and domain registries.

  11. Top 10 Enterprise Database Systems of 2016 • MariaDB Enterprise is a fully open source database system, with all code released under GPL, LGPL or BSD.

  12. Top 10 Enterprise Database Systems of 2016 • MySQL began as a niche database system for developers but grew into a major contender in the enterprise database market. Sold to Sun Microsystems in 2008, MySQL has since become part of the Oracle empire in 2009 following Sun's acquisition by Oracle.

  13. Top 10 Enterprise Database Systems of 2016 • Late 1970s, Teradata laid the groundwork for the first data warehouse . Teradata created the first terabyte database for Wal-Mart in 1992. • As a Very Large Database (VLDB) system, Teradata's capabilities have made it a great fit for handling emerging enterprise trends like Big Data analytics, business intelligence (BI) and the Internet of Things (IoT). Teradata released version 15.10 of its RDBMS in early 2015.

  14. Top 10 Enterprise Database Systems of 2016 •  IBM offers a range of Informix database options, starting with entry-level Workgroup and Express Editions and scaling up to an Enterprise Edition, an Enterprise Hypervisor Edition and finally Advanced Workgroup and Enterprise Editions with the Informix Warehouse Accelerator (IWA).

  15. Top 10 Enterprise Database Systems of 2016 • Ingres is the parent open source project of PostgreSQL and other database systems, and it is still around to brag about it.

  16. Top 10 Enterprise Database Systems of 2016 •  Amazon's SimpleDB (Simple Database Service) offers enterprises a simple, flexible and inexpensive alternative to traditional database systems.

  17. An Example The Internet Movie Databasehttp://www.imdb.com • Entities: Actors (800k), Movies (400k), Directors, … • Relationships:who played where, who directed what, … Want to store and process locally; what functions do we need ?

  18. What the Database Systems Does • Create/store large datasets • Search/query/update • Change the structure • Concurrent access to many user • Recover from crashes • Security (not here, but in other apps)

  19. Possible Organizations • Files • Spreadsheets • DBMS

  20. 1. Create/store Large Datasets • Files • Spreadsheets • DBMS Yes, but… Not really… Yes

  21. 2. Search/Query/Update • Simple query: • In what year was ‘Rain man’ produced ? • Multi-table query: • Find all movies by ‘Coppola’ • Complex query: • For each actor, count her/his movies • Updating • Insert a new movie; add an actor to a movie; etc

  22. 2. Search/Query/Update • Files • Spreadsheets • DBMS Simple queries Multi-table queries(maybe) All Updates: generally OK

  23. 3. Change the Structure Add Address to each Actor • Files • Spreadsheets • DBMS Very hard Yes Yes

  24. 4. Concurrent Access Multiple users access/update the data concurrently • What can go wrong ? • How do we protect against that in OS ? • This is insufficient in databases; why ?

  25. 4. Concurrent Access Multiple users access/update the data concurrently • What can go wrong ? • Lost update; resulting in inconsistent data • How do we protect against that in OS ? • Locks • This is insufficient in databases; why ? • A logical action consists of multiple updates

  26. 5. Recover from crashes • Transfer $100 from account #4662 to #7199: • X = Read(Account, #4662); • X.amount = X.amount - 100;Write(Account, #4662, X); • Y = Read(Account, #7199);Y.amount = Y.amount + 100;Write(Account, #7199, Y); CRASH ! What is the problem ?

  27. connection (ODBC, JDBC) Enters a DMBS “Two tier system” or “client-server” Database server(someone else’sC program) Applications Data files

  28. Still implemented as files,but behind the scenes can be quite complex DBMS = Collection of Tables Directors: Movie_Directors: Movies: “data independence”

  29. 1. Create/store Large Datasets Use SQL to create and populate tables: CREATE TABLE Actors ( Name CHAR(30) DateOfBirth CHAR(20) ) . . . INSERT INTO Actors VALUES(‘Tom Hanks’, . . .) Size and physical organization is handled by DBMS We focus on modeling the database Will study data modeling in this course

  30. 2. Searching/Querying/Updating • Find all movies by ‘Coppola’ • What happens behind the scene ? SELECT titleFROM Movies, Directors, Movie_DirectorsWHERE Directors.lname = ‘Coppola’ and Movies.mid = Movie_Directors.mid and Movie_Directors.id = Directors.id We will study SQL in gory details in this course We will discuss the query optimizer in class.

  31. 3. Changing the Structure Add Address to each Actor ALTER TABLE Actor ADD address CHAR(50) DEFAULT ‘unknown’ Lots of cleverness goes on behind the scenes

  32. 3&4 Concurrency & Recovery:Transactions • A transaction = sequence of statements that either all succeed, or all fail • E.g. Transfer $100 BEGIN TRANSACTION; UPDATE AccountsSET amount = amount - 100WHERE number = 4662 UPDATE AccountsSET amount = amount + 100WHERE number = 7199 COMMIT

  33. Transactions • Transactions have the ACID properties: A = atomicity C = consistency I = isolation D = durability

  34. 4. Concurrent Access • Serializable execution of transactions • The I (=isolation) in ACID three techniques- protection Locks Timestamps Validation

  35. 5. Recovery from crashes • Every transaction either executes completely, or doesn’t execute at all • The A (=atomicity) in ACID three types of log files Undo log file Redo log file Undo/Redo log file

More Related