Database 1 What is a database Give examples
Database 1 What is a database ? Give examples of databases
Database 2 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
Database Management System 3 What is a DBMS ? Give examples of DBMS
Database Management System 4 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 DB 2 (IBM), SQL Server (MS), Oracle, Sybase My. SQL, Postgres, …
Market Shares 5 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%.
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
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.
Top 10 Enterprise Database Systems of 2016 The latest release of DB 2, DB 2 11. 1, runs on Linux, UNIX, Windows, the IBM i. Series and mainframes.
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.
Top 10 Enterprise Database Systems of 2016 Postgre. SQL, 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.
Top 10 Enterprise Database Systems of 2016 Maria. DB Enterprise is a fully open source database system, with all code released under GPL, LGPL or BSD.
Top 10 Enterprise Database Systems of 2016 My. SQL 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, My. SQL has since become part of the Oracle empire in 2009 following Sun's acquisition by Oracle.
Top 10 Enterprise Database Systems of 2016 Late 1970 s, 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 (Io. T). Teradata released version 15. 10 of its RDBMS in early 2015.
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).
Top 10 Enterprise Database Systems of 2016 Ingres is the parent open source project of Postgre. SQL and other database systems, and it is still around to brag about it.
Top 10 Enterprise Database Systems of 2016 Amazon's Simple. DB (Simple Database Service) offers enterprises a simple, flexible and inexpensive alternative to traditional database systems.
An Example 17 The Internet Movie Database http: //www. imdb. com Entities: Actors (800 k), Movies (400 k), Directors, … Relationships: who played where, who directed what, … Want to store and process locally; what functions do we need ?
What the Database Systems Does 18 1. 2. 3. 4. 5. 6. 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)
Possible Organizations 19 Files Spreadsheets DBMS
1. Create/store Large Datasets 20 Files Yes, but… Spreadsheets DBMS Not really… Yes
2. Search/Query/Update 21 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
2. Search/Query/Update 22 Files Simple queries Spreadsheets Multi-table queries (maybe) DBMS All Updates: generally OK
3. Change the Structure 23 Address to each Actor Files Very hard Spreadsheets Yes DBMS Yes
4. Concurrent Access 24 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 ?
4. Concurrent Access 25 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
5. Recover from crashes 26 Transfer $100 from account #4662 to #7199: X = Read(Account, #4662); X. amount = X. amount - 100; Write(Account, #4662, X); CRASH ! Y = Read(Account, #7199); Y. amount = Y. amount + 100; Write(Account, #7199, Y); What is the problem ?
Enters a DMBS 27 “Two tier system” or “client-server” connection (ODBC, JDBC) Data files Database server (someone else’s C program) Applications
DBMS = Collection of Tables 28 Directors: Movie_Directors: id f. Name l. Name id mid 15901 Francis Ford Coppola 15901 130128 . . . Movies: mid Title Year 130128 The Godfather 1972 . . . Still implemented as files, but behind the scenes can be quite complex “data independence”
1. Create/store Large Datasets 29 Use SQL to create and populate tables: CREATE TABLE Actors ( Name CHAR(30) Date. Of. Birth 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
2. Searching/Querying/Updating 30 Find all movies by ‘Coppola’ SELECT title FROM Movies, Directors, Movie_Directors WHERE 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 What happens behind the scene ? We will discuss the query optimizer in class.
3. Changing the Structure 31 Address to each Actor ALTER TABLE Actor ADD address CHAR(50) DEFAULT ‘unknown’ Lots of cleverness goes on behind the scenes
3&4 Concurrency & Recovery: Transactions 32 A transaction = sequence of statements that either all succeed, or all fail E. g. Transfer $100 BEGIN TRANSACTION; UPDATE Accounts SET amount = amount - 100 WHERE number = 4662 UPDATE Accounts SET amount = amount + 100 WHERE number = 7199 COMMIT
Transactions 33 Transactions have the ACID properties: A = atomicity C = consistency I = isolation D = durability
4. Concurrent Access 34 Serializable execution of transactions The I (=isolation) in ACID three techniques- protection Locks Timestamps Validation
5. Recovery from crashes 35 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
- Slides: 35