relational database systems 1 - tu braunschweig · •sql syntax –use the syntax as introduced in...
TRANSCRIPT
![Page 1: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/1.jpg)
Wolf-Tilo Balke
Jan-Christoph Kalo
Institut für Informationssysteme
Technische Universität Braunschweig
www.ifis.cs.tu-bs.de
Relational
Database Systems 1
![Page 2: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/2.jpg)
• First off
– We will post up-to-date information on our website
• Language
– exam of tasks will be in German
– … but you may answer either in English, German, or Denglisch
• Content
– all content from the lecture or exercises may come up in the exams
• except content that was only in detours and not in an exercise
– This of course includes also lectures 10-14…
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 2
Exam Facts
![Page 3: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/3.jpg)
• SQL Syntax
– Use the syntax as introduced in the lecture and
exercises
• e.g. You are not allowed to use the Postgres Inheritance feature
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 3
Exam Facts
![Page 4: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/4.jpg)
• Cheat Sheets
– you may bring two hand-writtentwo-sided DIN A4 pages with notes• No photocopies, print-outs, etc.
• Date
– the exam will be written on March 9,2018, from 08:30 until 10:00/10:30
• Duration
– 90 min or 120 min depending on your exam regulations
• Location
– ZI 24.1, ZI 24.2, ZI 24.4
– Detailed allocations will be posted on our website
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 4
Exam Facts
![Page 5: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/5.jpg)
14.1 Towards NoSQL & NewSQL
14.2 Server Hardware at Google
14.3 Example: CouchDB
14.4 Outlook: Next Semester
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 5
Towards NoSQL & NewSQL
![Page 6: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/6.jpg)
• NoSQL and special databases have been popularized by different communities and a driven by different design motivations
• Base motivations– Extreme Requirements
• Extremely high availability, extremely high performance, guaranteed low latency, etc.– e.g. global web platforms
– Alternative data models • Less complex data model suffices
• (More complex) non-relational data model necessary– e.g. multi-media or scientific data
– Alternative database implementation techniques• Try to maintain most database features but lessen the drawbacks
– e.g. “traditional” database applications, e.g. VoltDB
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 6
14.1 Towards NoSQL & NewSQL
![Page 7: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/7.jpg)
• Traditional databases are usually all-purpose systems
– e.g. DB2, Oracle, MySQL, …
– Theoretically, general purpose DB provide all features to develop any data driven application
– Powerful query languages
• SQL, can be used to update and query data; even very complex analytical queries possible
– Expressive data model
• Most data modeling needs can be servedby the relational model
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 7
14.1 Towards NoSQL & NewSQL
![Page 8: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/8.jpg)
– Full transaction support
• Transactions are guaranteed to be “safe”
– i.e. ACID transaction properties
– System durability and security
• Database servers are resilient to failures
– Log files are continuously written
» Transactions running during a failure can recovered
– Most databases have support for constant backup
» Even severe failures can be recovered from backups
– Most databases support “hot-standby”
» 2nd database system running simultaneously which can take over in case of severe failure of the primary system
• Most databases offer basic access control
– i.e. authentication and authorization
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 8
14.1 Towards NoSQL & NewSQL
![Page 9: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/9.jpg)
• In short, databases could be used as storage
solutions in all kinds of applications
• Higher scalability can be achieved with
distributed databases, having all features
known from classical all-purpose databases
– In order to be distributed, additional mechanisms are
needed
• partitioning, fragmentation, allocation, distributed
transactions, distributed query processor,….
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 9
14.1 Towards NoSQL & NewSQL
![Page 10: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/10.jpg)
• However, classical all-purpose databases may lead to problems in extreme conditions– Problems when being faced with massively high query
loads• i.e. millions of transactions per second
• Load to high for a single machine or even a traditional distrusted database– Limited scaling
– Problems with fully global applications• Transactions originate from all over the globe
• Latency matters!– Data should be geographically close to users
• Claims:– Amazon: increasing the latency by 10% will decrease the sales by 1%
– Google: increasing the latency by 500ms will decrease traffic by 20%
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 10
14.1 Towards NoSQL & NewSQL
![Page 11: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/11.jpg)
– Problems with extremely high availability constraints
• Traditionally, databases can be recovered using logs or
backups
• Hot-Standbys may help during repair time
• But for some applications, this is not enough:
Extreme Availability (Amazon)
– “… must be available even if disks are failing, network routes are
flapping, and several data centers are destroyed by massive
tornados”
– Additional availability and durability
concepts needed!
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 11
14.1 Towards NoSQL & NewSQL
![Page 12: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/12.jpg)
• Problems with emerging applications requiring
new data models
– Traditional databases rely on the relational model which
is not optimal for many new applications
• e.g. scientific data management like genome databases, geo-
information databases, etc.
• e.g. for handling data streams and massive volumes of sensor data
• e.g. for handling knowledge networks and reasoning
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 12
14.1 Towards NoSQL & NewSQL
![Page 13: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/13.jpg)
• In extreme cases, specialized database-like
systems may be beneficial
– Specialize on certain query types
– Focus on a certain characteristic
• i.e. availability, scalability, expressiveness, etc…
– Allow weaknesses and limited features for other
characteristics
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 13
14.1 Towards NoSQL & NewSQL
![Page 14: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/14.jpg)
• In the recent years, discussing “NoSQL”
databases has become very popular
– Careful: big misnomer!
• Does not necessarily mean that no SQL is used
– There are SQL-supporting NoSQL systems…
• NoSQL often refers to “non-standard” architectures for
database or database-like systems
– i.e. system not implemented as shown in RDB2
– Sometimes, the label NewSQL is also used
• Not formally defined, more used as a “hype” word
– Popular base dogma: Keep It Stupid Simple!
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 14
14.1 Towards NoSQL & NewSQL
![Page 15: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/15.jpg)
• The NoSQL movement popularized the development of special purpose databases
– In contrast to general purpose systems like e.g. Postgres
• NoSQL usually means one or more of the following
– Being massively scalable• Usually, the goal is unlimited linear scalability
– Being massively distributed
– Being extremely available
– Showing extremely high OLTP performance• Usually, not suited for OLAP queries
– Not being “all-purpose”• Application-specific storage solutions showing some database
characteristics
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 15
14.1 Towards NoSQL & NewSQL
![Page 16: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/16.jpg)
– Not using the relational model• Usually, much simpler data models are used
• Some, much more complex data models are used (XML, Logic-based, objects, etc.)
– Not using strict ACID transactions• No transactions at all or weaker transaction models
– Not using SQL• But using simpler query paradigms
– Especially, not supporting “typical” query interfaces• i.e. JDBC
• Offering direct access from application to storage system
– System is cloud-based, i.e. not installed on a local server• System managed by a 3rd party
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 16
14.1 Towards NoSQL & NewSQL
![Page 17: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/17.jpg)
• In short:
– Many NoSQL & NewSQL
focus on
building specialized
high-performance data
storage systems!
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 17
14.1 Towards NoSQL & NewSQL
![Page 18: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/18.jpg)
• NoSQL and special databases have been popularized by different communities and a driven by different design motivations– Extreme Requirements
• Extremely high availability, extremely high performance, guaranteed low latency, etc.– e.g. global web platforms
– Alternative data models • Less complex data model suffices
– See https://highlyscalable.wordpress.com/2012/03/01/nosql-data-modeling-techniques/
• (More complex) non-relational data model necessary– e.g. multi-media or scientific data
– Alternative database implementation techniques• Try to maintain most database features but lessen the drawbacks
– e.g. “traditional” database applications, e.g. VoltDB
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 18
14.1 Towards NoSQL & NewSQL
![Page 19: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/19.jpg)
Classification System
Key-Value CacheCoherence, eXtreme Scale, GigaSpaces, Hazelcast, Infinispan, JBossCache, Memcached, Repcached, Terracotta, Velocity
Key-Value Store Flare, Keyspace, RAMCloud, SchemaFree
Key-Value Store - Eventually consistent
DovetailDB, Dynamo, Dynomite, MotionDb, Voldemort, SubRecord
Key-Value Store - Ordered Actord, Lightcloud, Luxio, MemcacheDB, NMDB, Scalaris, TokyoTyrant
Tuple Store Apache River, Coord, GigaSpaces
Object Database DB4O, Perst, Shoal, ZopeDB,
Document Store Clusterpoint, CouchDB, MarkLogic, MongoDB, Riak, XML-databases
Wide Columnar Store BigTable, Cassandra, HBase, Hypertable, KAI, KDI, OpenNeptune, Qbase
Array Databases SciDB, PostGIS, Oracle GeoRaster, Rasdaman
Stream Databases StreamSQL, STREAM, AURORA
Analytical Column Stores Vertica, SybaseIQ
High Throughput OLTP VoltDB, Hana
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 19
14.1 Towards NoSQL & NewSQL
![Page 20: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/20.jpg)
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 20
14.1 Towards NoSQL & NewSQL
![Page 21: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/21.jpg)
• Apache CouchDB
– Couch==cluster of unreliable commodity hardware
– Aimed at serving webpages and web apps
– Core Features
• Distributed Architecture with high degree of replication
– Can run on hundreds of nodes if required
– Focus on availability of data!
– Replicas are NOT always consistent, but eventually consistent
» Some nodes can even be offline!
» CouchDB can fall into partitions, this will be fixed by the
system
» Replicas will be synced bi-directionally when opportune
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 21
14.3 Example: CouchDB
![Page 22: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/22.jpg)
• No support for transactions– ... but at least supports some consistency for replicas : eventual
consistency
» See CAP theorem if you are interested in this…
» In short: in system with replicas, you can have availability, consistency, and partition tolerance
• Cap theorem: pick only two
• Uses a Document Data model– Stores and retrieves documents given by JSON files
• Has a strong emphasize on open Web APIs– No client APIs necessary
– No drivers necessary
– All documents have unique URI, exposed via HTTP REST calls
• Strong support for views– Views are defined via JavaScript
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 22
14.3 Example: CouchDB
![Page 23: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/23.jpg)
• Data Model:
– JSON Documents
• Initially a format designed to serialize Javascript objects
• Primary use: data exchange in a Web environment
– E.g., AJAX applications
• Extended use: data serialization and storage
• Could be seen as lightweight XML
– pretty easy to integrate to any programming language, with minimal
parsing effort
• However: No query language, no schema
• Basic idea: Structured key-value pairs
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 23
14.3 Example:
CouchDB
![Page 24: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/24.jpg)
• Example: Simple Movie DB
• Simple data items are
key-value pairs supporting
typical Web data types
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 24
14.3 Example:
CouchDB
• “title” : “Terminator 2“
• “year” : 1991
![Page 25: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/25.jpg)
• An object is a key value pair which has a set of
unordered keyvalue pairs as value
– Sub-item keys must be unique
– Objects can be used as values of a key-value pair
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 25
14.3 Example:
CouchDB
“director”: {
“first_name” : “James“,
“last_name” : “Cameron”
}
“terminator2”: {
“title” : “Terminator 2“,
“year” : 1991,
“director” : {
“first_name” : “James“,
“last_name” : “Cameron” }}
![Page 26: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/26.jpg)
• Also, arrays can be used
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 26
Example: CouchDB
“terminator2”:
{ “title” : “Terminator 2“,
“year” : 1991,
“director” : {
“first_name” : “James“,
“last_name” : “Cameron” }
“actors”: [
{“first_name” : “Arnold”, “last_name” : ”Schwarzenegger” },
{“first_name” : “Linda”, “last_name” : ”Hamilton” },
{“first_name” : “Edward”, “last_name” : ”Furlong” },
]
}
![Page 27: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/27.jpg)
• Documents are complex and autonomous
pieces of information
– Each document has a unique URI
– Can be retrieved, stored, modified, and deleted
• REST Calls: GET, PUT, POST, DELETE
– There are no references between documents
– Also, documents can be versioned, replicated,
synchronized, and restructured
• Each document is identified by an id and a revision number
• Each update created a new revision
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 27
Example: CouchDB
![Page 28: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/28.jpg)
• Quick introduction
– You can use CURL for quick interaction
• Programming language & environment for interactive web
applications
• Provides native support for most web standards like HTML,
REST, or JSON
– Assume we installed CouchDB locally
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 28
Example: CouchDB
![Page 29: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/29.jpg)
• Futon Admin Interface: http://127.0.0.1:5984/_utils/
• Already created movies DB
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 29
Example: CouchDB
![Page 30: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/30.jpg)
• Add some data:
– Each document needs an ID, think of one!
– Or just use files:
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 30
Example: CouchDB
curl -X PUT -d ‘{ “title” : “Terminator 2“, “year” : 1991,“director” : { “first_name” : “James“, “last_name” : “Cameron” },
“actors”: [
{“first_name” : “Arnold”, “last_name” : ”Schwarzenegger” },
{“first_name” : “Linda”, “last_name” : ”Hamilton” },
{“first_name” : “Edward”, “last_name” : ”Furlong” },
]
}‘ http://127.0.0.1:5984/movies/Terminator2
curl -X PUT -d @Terminator2.json
http://127.0.0.1:5984/movies/Terminator2
![Page 31: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/31.jpg)
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 31
Example: CouchDB
![Page 32: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/32.jpg)
• This all looks quite easy and nice…
• Let’s query for something by using…no SQL???
– CouchDB only supports views, no queries!
– Views are defined using JavaScript MapReduce functions
• Map functions are run on each document and emit a new temporary document part of the view
– Again: A document has a key, and some value…
– View is ordered by key
• Views can then be queried by a reduce function
– Reduce functions summarize emitted map result grouped by key
• The MapReduce paradigm allows for an easy distribution of queries in a multi-node environment!
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 32
Example: CouchDB
![Page 33: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/33.jpg)
• Example: Return an ordered list of all movies from 1991 or older
– i.e., SELECT title FROM movies WHERE year<=1991• …but we don’t have SQL…
– CouchDB:
• Create a new view with years as keys and titles as values
• Select from this view all pairs with keys<=1991
– Views are collected in design documents
• Each design document can have multiple views
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 33
Example: CouchDB
function(doc) {
if (doc.title && doc.year) {
emit(doc.year, doc.title);
}
}
Map:
![Page 34: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/34.jpg)
• Example: Return an ordered list of all movies
from 1991 or older
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 34
Example: CouchDB
If there is a title and a year, create a newdocument with key=‘year’ and value=‘title’
No reduce necessary right now
View key-value pairs
We call this view “year-title”
![Page 35: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/35.jpg)
• Query via REST HTTP
– http://127.0.0.1:5984/movies/_design/rdb1_14/_view/year-title?endkey=1991
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 35
Example: CouchDB
DB name Design Document Name
View name All keys up to 1991
![Page 36: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/36.jpg)
• Example: Create list of years and the number of
movies released in that year
• (skip years without movies released, and consider
only years 1991 and older)
– e.g. SELECT year, count(*) FROM movies WHEREyear<=1991 GROUP BY year
• In CouchDB, we can use the same map as for the
previous query
– However, we need a reducer
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 36
Example: CouchDB
![Page 37: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/37.jpg)
• Reducers are run on all mapped data
– Mapped values are grouped by key, and a reducer is
called for each key with a set of all respective values
– Reducers can also be run on their own output
• Called a re-reduce, which can be done multiple times
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 37
Example: CouchDB
function(keys, values, rereduce) {
return values.length;
}
Reduce:
![Page 38: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/38.jpg)
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 38
Example: CouchDB
“terminator2”: { "title": "Terminator 2 - Judgement Day", "year": 1991, “genre”:”Action”}
“robinHood”:{ "title": "Robin Hood - Prince of Thieves", "year": 1991, “genre”:[”Action”, “Romance”]}
“conan”:{ "title": "Conan the Barbarian", "year": 1982, “genre”:”Action”}
function(doc) {
if (doc.title && doc.year) {
emit(doc.year, doc.title);
}
}
Map:
{1991: "Terminator 2 - Judgement Day"}
{1991: "Robin Hood - Prince of Thieves"}
{1982: "Conan the Barbarian"}
![Page 39: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/39.jpg)
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 39
Example: CouchDB
{1991: "Terminator 2 - Judgement Day"}
{1991: "Robin Hood - Prince of Thieves"}
{1982: "Conan the Barbarian"}
function(keys, values, rereduce) {
return values.length;
}
Reduce:
{1982: 1}
{1991: 2}
![Page 40: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/40.jpg)
• Query via REST HTTP
– http://127.0.0.1:5984/movies/_design/rdb1_14/_view/s
ums?endkey=1991&group_level=1
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 40
Example: CouchDB
Run reducer on level 0 and level 1
![Page 41: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/41.jpg)
• So, how about transactions?
– Not supported per se!
– But there are “easy” workarounds – just keep track of transaction consistency manually
– Example: inventory management• You are selling hammers, and screwdrivers, and don’t want to sell more
than you have on stock
• What happens if we sell a hammer?
• In JDBC/SQL, this would be simple…
– Have constraint that inventory number can never be negative
– Start JDBC transaction in your application
– Load current inventory number for hammers
– If there are still hammers, reduce inventory by one
– Commit transaction – if this works out, tell customer that everything is fine
» If not, somebody else snatched the last hammer quicker
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 41
Example: CouchDB
![Page 42: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/42.jpg)
• “Solution” A: Work with revision numbers• Have an inventory document
• Load document with hammer inventory number, store revision
• Sell hammer
• Update hammer inventory document with new number if only if document has still the same revision– If not, retrieve the new document and try to update that one…
– If you find out that there are no hammers anymore, reimburse customer and apologize
– This process catches many potential consistency problems, but gives NO guarantees at all!• This is horrible in a high concurrency environment!
• You could have purchases which get pushed back all the time…
• You could still sell more hammer than you have…
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 42
Example: CouchDB
inventory : {
_rev : “471c37eb3116179b9f269427372a86db”
“hammers” : 15;
“screwdrivers” :9;
}
![Page 43: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/43.jpg)
• “Solution” B: Build fake “locks” for each item• For each hammer and screwdriver, have an own inventory document
• If you want to know how many hammers you have, create a view and count all hammer documents
• If you sell a hammer, randomly load one hammer file and try to delete it
• If this works, all might be well…
– This process has still problems…• e.g., inventory documents are replicated – how do you deal with that?
– Visit our lectures RDB2 and DDM to learn how to program something that will really work…
– … in which case you just build a distributed database transaction manager yourself!! Congrats, wheel re-invented!
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 43
Example: CouchDB
Hammer_1 : {_rev : “471c37eb3116179b9f269427372a86db”}
Hammer_2 : {_rev : “5ff77937ea707d35cc907b466f726cc8”}
Hammer_3 : {_rev : “3dd521c277ab448b91ce2e8bb57bbb4f”}
Screwdriver_1 : {_rev : “a1a70294da183c8b0fb525ec285971c9”}
Screwdriver_2 : {_rev : “09bdb275b75fea85369c86f7ba5f3467”}
![Page 44: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/44.jpg)
• Closing words…
– Yes, NoSQL is cool and can do cool things!
• Usually, its easy, fast, and scalable!
– No, NoSQL does NOT universally invalidate
Relational Databases
– New Challenge for YOU:
• Choose the right tool for the right task!
• What does your application really require?
• What will it require in the future?
• Which technologies fulfill these requirements best?
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 44
NoSQL
![Page 45: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/45.jpg)
• Lectures in SoSe 2018
– Deductive Databases and
Knowledge-based Systems
– Distributed Databases
– Software Entwicklungs Praktikum
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 45
14 Next Semester
![Page 46: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/46.jpg)
• Knowledge-Based Systems
and Deductive Databases
– Master’s course, 5 Credits, in English
• Main challenge
– How can we encode and use knowledge
in a database system
• Touches multiple areas of
artificial intelligence
46
KBS
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig
![Page 47: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/47.jpg)
• The increase of knowledge grows exponentially
– e.g. in terms of new publications
• Huge amounts of data have to be sifted and analyzed to gain intelligence from data
– Need knowledge-based technology for this task!
– Analysts have interesting perspectives,i.e. exciting and well-paid work
– Find out about the things you always wanted to know…
47
KBS - Why should you go there?
data extracted from the Medline database
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig
![Page 48: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/48.jpg)
• But you can also go into the direction of big
bucks…
– Write an automated reasoning system and
license call-center technology
– Ronald A. Katz, founder of Ronald A.
Katz Technology Licensing, LP)
• Licensing the technology earned
more than one billion USD
• Customers include AT&T, Bank of
America, Citibank, Delta Air Lines,
Hewlett Packard, IBM, Microsoft…
48
KBS - Why should you go there?
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig
![Page 49: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/49.jpg)
• What are knowledge-based systems?
– What can you do with them?
• Topics covered
– We will show you how the vision of knowledge based systems was born and what became of it
– Formal Logics and Deduction
• …learn about different kinds of formal logic
• …learn of how to interpret logical expressions
• …learn how to efficiently evaluate logical expressions in a database setting
• …how to design a KBS using different flavors of formal logic
49
KBS - What will you learn?
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig
![Page 50: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/50.jpg)
– Expert Systems
• How can human knowledge and
experience be encoded
• Can databases replace experts?
– The Semantic Web
• We will show you how all KBS ideas have been reborn
within the semantic web
• We will show you how the semantic web works and what it
tries to achieve
50
KBS - What will you learn?
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig
![Page 51: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/51.jpg)
– Question Answering
• We will show how knowledge-based methods can be
unleashed to be even smarter than very smart quitz
champions
• DeepQA project
51
KBS - What will you learn?
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig
![Page 52: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/52.jpg)
• Up to now we have only considered scenarios
where a central DBMS is responsible for
– Keeping the data consistent and persistent
– Optimizing query plans and minimizing disk accesses
– Guaranteeing the ACID properties of interaction
– Controlling the data security and privacy
• Especially, all meta-information about the
processes is concentrated at the DBMS
Datenbanksysteme 2 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 52
14.2 Distributed Databases
![Page 53: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/53.jpg)
• But organizations usually have a variety of
different systems to keep data records
– Customer database, human resources, product
catalogs, financial records
• How can a complete view of the distributed
data be provided, keeping all the advantages of a
single central database system?
– Integrate data, but avoid
single point of failure
Datenbanksysteme 2 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 53
14.2 Distributed Databases
![Page 54: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/54.jpg)
• A distributed database is a database
– Under the control of a central database management
system
– Operations run on multiple computers located in the
same physical location, or may be dispersed over a
network of interconnected computers
– Storage devices are not all
attached to a common CPU
– Collections of data can be distributed
across multiple physical locations
Datenbanksysteme 2 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 54
14.2 Distributed Databases
![Page 55: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/55.jpg)
• Database fragmentation
– Horizontal (row distribution)
– Vertical (column and/or
table distribution)
• Distributing databases (top-down)
– How to split and allocate data to individual sites?
• Integrating databases (bottom-up)
– Combine existing databases
– How to deal with heterogeneity & autonomy?
Datenbanksysteme 2 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 55
14.2 Distributed Databases
![Page 56: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/56.jpg)
• Major advantages
– Reflects organizational structure
• Database fragments are located in the departments they relate to
– Local autonomy
• A department can control its own data and enforce policies
– Improved availability
• A fault in one database system will only affect one fragment,
instead of the entire database
– Improved performance
• The database fragments enable parallelized operations, allowing
load on the databases to be balanced among servers
Datenbanksysteme 2 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 56
14.2 Distributed Databases
![Page 57: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/57.jpg)
• But not only managed data access within some structured organization is necessary, but also data access via the Internet
– Distributed information sources
– Deep Web / Hidden Web
– E-Commerce, comparison shopping
• Data transfer over the Web makes up for a large amount of bandwidth
– About 90% of network resources (including file sharing applications)
Datenbanksysteme 2 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 57
14.2 Distributed Databases
![Page 58: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/58.jpg)
• Avoiding a single point of failure is mostly
beneficial for organizations
– The company’s central database is down, the complete
company is out of business?
– On the other hand multiple points of access may pose
severe security issues…
• For unstructured data the
peer-to-peer (P2P) paradigm
is often used for content access
Datenbanksysteme 2 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 58
14.2 Distributed Databases
![Page 59: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/59.jpg)
• P2P systems are overlay architectures, with
the following characteristics
– Two logically separate networks
– Mostly IP based signaling
– Decentralized and self organizing
– Employ distributed shared resources
Datenbanksysteme 2 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 59
14.2 Distributed Databases
![Page 60: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/60.jpg)
• First application: file sharing
– Classical application of P2P systems
• Large distributed database of files (music , videos, etc.) for free download
– First large scale occurrence of digital copyright infringement
• But also legal distribution of software/updates
– Basic idea of distributing softwareupdates or patches in a P2P fashion
– Technology used
• Today mostly BitTorrent (Block-based File Swarming)
• Microsoft’s Avalanche (File Swarming with Network Coding)
Datenbanksysteme 2 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 60
14.2 Distributed Databases
![Page 61: Relational Database Systems 1 - TU Braunschweig · •SQL Syntax –Use the syntax as introduced in the lecture and exercises •e.g. You are not allowed to use the Postgres Inheritance](https://reader035.vdocuments.us/reader035/viewer/2022062923/5f0a06557e708231d429a624/html5/thumbnails/61.jpg)
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 61
14 That‘s all folks…