1. Fall 2013, Databases, Exam 2 Questions for the second exam…

2. 1. Cassandra (or other noSQLdb) • Describe an application that would be an excellent match for developing in one of the noSQL systems discussed in class. • *** You must use one of the noSQL databases listed in problem 7. *** • What business, organization, country, etc. owns the app? • What primary sort of data that they have to manipulate on an ongoing basis? – describe this carefully • What does the data represent in the real world? • Some of your data should consist of text documents – but not all of it. • Why would Cassandra be the best thing for them? • List the reasons • Why not a relational DBMS? • Why not another of the no-SQL database systems?

3. Cassandra, continued • Draw out the column families (in the data model of Cassandra) for the application • Make them specific and detailed • Make sure your specification isn’t trivial • Write 3 queries for your Cassandra application using CQL • One that creates data with an Insert • One that performs an SQL like summary task • One that creates the column families for your application

4. 2. An XML Language • Create an XML language for the application described in question 1 and other related applications • Your goal is to create a shared language for anyone publishing data from companies/organizations/etc. that manage data of this sort. • Make sure your language is not trivial • For your specific company/org/etc., use your language to create a data description for the data you defined structurally in question 1. • Keep in mind that the XML language is generic and your data description for the specific app in question 1

5. 3. Relational dbs and olap • Pick an application domain and create a relational schema for it. Make sure you have at least four tables • Specify keys and FK’s • Specify all relevant FD’s and MVD’s – but you do not have to normalize your schema • Write 2 queries for your schema • One does an insert • One does a general set oriented query with at least one join in it. • Your app must be the same as the one in question 1. • Your schema must use only terms from your XML language

6. oltp and olap, continued • Create a data warehousing schema to go with your relational schema • Add to your relational schema if you need to do so in order to make your warehousing schema non-trivial • Assume that the basic data being studied over time is money • Assume that time is one of the dimensions, and break your time data down by year/month/day • Create three transactions that can be run in order to look for trends • Carefully describe the data that is taken from the transaction database and how it is aggregated into the dimension tables of the warehouse • Write a couple of sentences describing the reason for your warehouse and queries – what aggregate data is being studied and why?

7. 4. Assertions and Inferences • For the application you used in question 1 created in question 1, write a set of assertions • Create at least 10 triples • Make sure that all of the “middles” of your assertions are from the xml language you specified • Make sure you reference among other items, some of the documents in your document base • Remember that your inference base concerns actual data items, not “schema” items. • Create some inferences based on your assertions

8. 5. Distributed DBs • Create a heterogeneous distributed database • Use your relational schema from problem 3 as one of them • Specify two other schemas that concern the same general application domain, but do NOT use your XML language • For your other 2 schemas specify their keys, FK’s, FDs and MVDs and deliberately make them conflict with the original schema and with each other • Build a set of primitives that you might use to translate these conflicting schemas into a single schema – you will of course give the user of your primitives the power to pick one dependency or PK or FK over another, and to resolve conflicting terms.

9. 6. Ambient Intelligence • Describe an instance of ambient intelligence that would be useful to some sizeable group of people • Make sure that it is end-user friendly and available to people in public places • What would this system do? • Who would want to use it? • Would it require special hardware? • Where would this system be located? • Make sure that it serves hands-on users, but is also web-connected • How would the connectivity to the web enhance your systems value to people?

10. Ambient Intelligence, continued • Pick a database system (among the ones we have talked about in class) • Why is this the best database to use? Be specific • What would be the worst choice to store the data? • It’s okay to suggest that more than one database system should be used, but you have to defend both and explain how the two databases will remain correct with respect to each other

11. 7. Database Systems Comparison • Create a chart that includes • Neo4J • Mongo • MySQL • Cassandra • Hbase • In your chart, the vertical axis will have the databases on them and the horizontal axis will have (at least 4) properties that specifically focus on the way these database systems differ in their capabilities (not the kinds of apps they are meant for, but in their actual functionalities)