1 / 19

Wrap-up

Wrap-up. You ’ ve learned the fundamentals for:. 25% off Oracle Certification Exams. OCA Training.

keene
Télécharger la présentation

Wrap-up

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. Wrap-up

  2. You’ve learned the fundamentals for:

  3. 25% off Oracle Certification Exams

  4. OCA Training I may be offering the Admin I DBA training course on Monday from 5:30 – 7:30 during the Fall semester to help you prepare for the OCA exam (1Z0-052). This is normally a > $2000 course but I can offer it for free through the Oracle Academy. Contact me once the semester begins if you’re interested.

  5. Just got this in the mail yesterday

  6. Selected Projects Turnin • If you presented your project on Thursday in class, please email the following to me in a zip file: • saved data model that can be loaded into data modeler • all three DDL files • exported APEX application (it will be an SQL file) that can be loaded into APEXEA. • In addition to the zip file, in the email, please send me the URL link to your project at apexea.oracle.com.

  7. CREO and/or UTBC Team Please send me email if your interested in helping to continue the implementation of the CREO and/or the UTBC applications at CREO and/or UTBC this Fall. This would make a very good cs370 project. CREO just hired Kam McCoy who is expert in the Yardi Property Management System (http://www.yardi.com/products/property-management-software). She would be willing to work with students to turn CREO into a system similar to Yardi. CREO expects to have many more leases to manage in the future because of the new UT Medical School.

  8. This is Event data from a small company in town, it includes JSON data (shown in Red) in each event. ReL as a type of MongoDB ("ID","eventID","eventDate","domain","source","eventType","eventBody","version"), (665037,"CL-l2kQ_5xmI_1397193236453000",1397193236453,"UVC_CSS","CL-l2kQ_5xmI","REGISTRATION_DELETED_EVENT",{"ID":"00000000026015b05347362cf3c90b9e0000047e","guest":False,"platform":"UNKNOWN","productID":"LifeSize Room 220 4.12.1.7","IP":"207.114.244.229","port":1720,"authenticationID":""},1), (665038,"CL-l2kQ_5xmI_1397193246686000",1397193246686,"UVC_CSS","CL-l2kQ_5xmI","AUTHENTICATION_FAILED_EVENT",{"requestInfo":{"pairingToken":"87050315-4b99-4144-8576-7b23f1c6fd67","serialNumber":"KQ6821086D1A8","platform":"Icon_600","version":"1.4.5.376","IP":"207.114.244.5","userAgent":"LifeSize Icon 600/LS_RM3_1.4.5 (376)"},"guest":False,"errorCode":401,"errorReasonCode":"INVALID_CREDENTIALS"},1), (665039,"CL-l2kQ_5xmI_1397193251573000",1397193251573,"UVC_CSS","CL-l2kQ_5xmI","REGISTRATION_DELETED_EVENT",{"ID":"00000000026015b05347362cf3c90b9e0000047f","guest":False,"platform":"UNKNOWN","productID":"LifeSize Room 220 4.12.1.7","IP":"207.114.244.229","port":1720,"authenticationID":""},1), (665040,"CL-l2kQ_5xmI_1397193258153000",1397193258153,"UVC_CSS","CL-l2kQ_5xmI","AUTHENTICATION_FAILED_EVENT",{"requestInfo":{"userLogin":"wwilson","serialNumber":"KQ073800077A5","platform":"Icon_600","version":"1.4.3.15","IP":"207.114.244.5","userAgent":"LifeSize Icon 600/LS_RM3_1.4.3 (15)"},"guest":False,"errorCode":401,"errorReasonCode":"MISSING_PAIRING_CREDENTIALS"},1), (665041,"CL-l2kQ_5xmI_1397193258294000",1397193258294,"UVC_CSS","CL-l2kQ_5xmI","AUTHENTICATION_FAILED_EVENT",{"requestInfo":{"pairingToken":"aa1bfcc0-eb69-47c5-bbb6-4c6e038ef2b9","serialNumber":"KQ68270870C47","platform":"Icon_600","version":"1.4.5.415","IP":"207.114.244.5","userAgent":"LifeSize Icon 600/LS_RM3_1.4.5 (415)"},"guest":False,"errorCode":401,"errorReasonCode":"INVALID_CREDENTIALS"},1), (665042,"CL-l2kQ_5xmI_1397193266692000",1397193266692,"UVC_CSS","CL-l2kQ_5xmI","REGISTRATION_DELETED_EVENT",{"ID":"00000000026015b05347362cf3c90b9e00000480","guest":False,"platform":"UNKNOWN","productID":"LifeSize Room 220 4.12.1.7","IP":"207.114.244.229","port":1720,"authenticationID":""},1), (665043,"CL-l2kQ_5xmI_1397193275396000",1397193275396,"UVC_CSS","CL-l2kQ_5xmI","AUTHENTICATION_FAILED_EVENT",{"requestInfo":{"userLogin":"rharris","serialNumber":"KQ6849087D8A6","platform":"Icon_600","version":"1.4.3.15","IP":"207.114.244.201","userAgent":"LifeSize Icon 600/LS_RM3_1.4.3 (15)"},"guest":False,"errorCode":401,"errorReasonCode":"MISSING_PAIRING_CREDENTIALS"},1), (665044,"CL-l2kQ_Dsny_1397193280391000",1397193280391,"UVC_CSS","CL-l2kQ_Dsny","REGISTRATION_DELETED_EVENT",{"ID":"DL60dec91a71@NBVNARAYANA.corp.logitech.com","userAccountID":"68191e45-29b1-4c9f-b989-af1a06ee4cdf","groupID":"65a0336f-af4b-4a1b-a7d8-03519042a4cb","guest":False,"platform":"WIN","osInfo":"OS v6.1 Windows 7 x64 SP1 suites=0x0100 build 7601","productID":"LifeSize ClearSea Client 9.0.11 (Windows)","IP":"122.166.123.117","port":65275,"authenticationID":"153df7dad38d28c7"},1), (665045,"CL-l2kQ_5xmI_1397193281816000",1397193281816,"UVC_CSS","CL-l2kQ_5xmI","REGISTRATION_DELETED_EVENT",{"ID":"00000000026015b05347362cf3c90b9e00000481","guest":False,"platform":"UNKNOWN","productID":"LifeSize Room 220 4.12.1.7","IP":"207.114.244.229","port":1720,"authenticationID":""},1), (665046,"CL-l2kQ_5xmI_1397193283000000",1397193283000,"UVC_CSS","CL-l2kQ_5xmI","AUTHENTICATION_SUCCESSFUL_EVENT",{"ID":"c4739a4a2ff9b0b0","userAccountID":"4f9b9f78-d1ec-4d4b-b027-3434469a80bb","groupID":"65a0336f-af4b-4a1b-a7d8-03519042a4cb","requestInfo":{"userLogin":"101277","platform":"IOS_IPAD","version":"9.0.9","IP":"98.165.107.220","userAgent":"Dylogic-HttpAction/1.5 (iPhone OS 7.0.6 iPad3,4)"},"guest":False},1),

  9. This Event data can be processed by the following ReL program which loads it into an RDF database. ReL as a type of MongoDB conn = connectTo 'jdbc:oracle:thin:@rising-sun.microlab.cs.utexas.edu:1521:orcl' 'CS347_prof' 'orcl_prof' 'rdf_mode' 'EVENTS'; def flatten_dict(d): # This function flattens nested dictionaries def expand(key, value): if isinstance(value, dict): return [ (key + '_' + k, v) for k, v in flatten_dict(value).items() ] else: return [ (key, value) ] items = [ item for k, v in d.items() for item in expand(k, v) ] return dict(items) f = open("smallTmp_USER_ACCOUNT", "r") cnt = 1 for l in f: if cnt == 1 : H1 = eval(l)[0] # H1 = ('ID', 'eventID', 'eventDate', 'domain', 'source', 'eventType', 'eventBody') else : H = H1 v = "" if isinstance(eval(l)[0][6], dict): d = flatten_dict(eval(l)[0][6]) attrs = [ i[0] for i in d.items()] values = [ i[1] for i in d.items()] print print eval(l)[0][6] print d.items() print attrs print values # H = H1 + tuple(attrs) + tuple(['version']) H = H1 + tuple(attrs) num = 1 for i in values : if num == 1 : v += str(i) else : v += "', '" + str(i) num += 1 else : v = str(eval(l)[0][6]) H = str(H).replace("'", "") s = str(H) + " VALUES ('" + str(eval(l)[0][0]) + "', '" + str(eval(l)[0][1]) + "', '" + str(eval(l)[0][2]) + "', '" + str(eval(l)[0][3]) + "', '" + str(eval(l)[0][4]) + "', '" + str(eval(l)[0][5]) + "', " + 'null' + ", '" + str(eval(l)[0][7]) + "', '" + v + "') ;" print s SQL on conn """INSERT INTO EVENTS"""s cnt+=1 Standard python

  10. The Data Model for this Event data is the shown in the figure. However, this data model was not used to generate a schema for the data in the database, it was just used to help people understand the data. ReL as a type of MongoDB

  11. However, you can do SQL queries on the Event data including the JSON data. ReL as a type of MongoDBwith SQL Queries conn = connectTo 'jdbc:oracle:thin:@rising-sun.microlab.cs.utexas.edu:1521:orcl' 'CS347_prof' 'orcl_prof' 'rdf_mode' 'EVENTS'; t =SQL on conn """select * from EVENTS e where e.eventType = 'AUTHENTICATION_SUCCESSFUL_EVENT’””” import json print json.dumps(t, indent = 2) [ [ "eventDate", "requestInfo_platform", "DBUNIQUEID", "requestInfo_userAgent", "guest", "ID", "groupID", "source", "requestInfo_IP", "eventBody", "version", "requestInfo_serialNumber", "requestInfo_pairingToken", "userAccountID", "eventType", "domain", "eventID", "devicePairingID", "requestInfo_version", "requestInfo_userLogin" ], [ 1397197635584.0, "WIN", 4143, "Dylogic-HttpAction/1.5 (OS v6.1 Windows 7 x64 SP1 suites=0x0100 build 7601)", "false", "cf47fb67cce9cb5c", "65a0336f-af4b-4a1b-a7d8-03519042a4cb", "CL-l2kQ_x6Wb", "93.62.209.82", "NULL", 1, "null", "null", "309771b8-8117-4eb7-8776-bf311ae1ce9e", "AUTHENTICATION_SUCCESSFUL_EVENT", "UVC_CSS", "CL-l2kQ_x6Wb_1397197599533000", "null", "9.0.11", "cmenesatti@lifesize.com" ], . . . [

  12. Step 1 – get the schema information from the data. ReL as a type of MongoDBwith SPARQL Queries In executeRdfSelect, selectStmt is: select distinct obj from table(sem_match( 'select * where { ?sub rdfs:domain ?obj . }' , SEM_MODELS('EVENTS_CS347_PROF'), null, SEM_ALIASES( SEM_ALIAS('', 'http://www.example.org/people.owl#')), null) ) In executeRdfSelect, selectStmt is: select distinct col from table(sem_match( 'select * where { ?col rdfs:domain :EVENTS . ?s1 ?col ?v . }' , SEM_MODELS('EVENTS_CS347_PROF'), null, SEM_ALIASES( SEM_ALIAS('', 'http://www.example.org/people.owl#')), null) ) visitSelect_buildSPARQL Structures necessary to build the SPARQL statement: - plainSelect: SELECT * FROM EVENTS AS e WHERE e.eventType = 'AUTHENTICATION_SUCCESSFUL_EVENT' - RDFTableNames: [EVENTS] - tables: [EVENTS] - tablesAliases: {e=EVENTS} - tables2alias: {EVENTS=e} - tableSymbols: {EVENTS=s1} - columnNames: [e.userID, e.eventDate, e.sentDelayedToLocalSelf, e.requestInfo_platform, e.sentToLocalSelf, e.groupName, e.inviterID, e.DBUNIQUEID, e.requestInfo_userAgent, e.sizeInBytes, e.creationDate, e.serialNumber, e.extension, e.IP, e.displayName, e.sentToRemoteSelf, e.guest, e.osInfo, e.sentDelayedToLocal, e.ID, e.errorReasonCode, e.fromGroupID, e.who, e.groupID, e.source, e.requestInfo_serialNumber, e.email, e.requestInfo_IP, e.requestInfo_pairingToken, e.eventBody, e.port, e.version, e.reason, e.fromUserAccountID, e.sentToRemote, e.loginRefuseRule, e.authenticationID, e.userAccountID, e.devicePairingID, e.sentToLocal, e.inviterUserID, e.productID, e.enabled, e.language, e.usageType, e.eventType, e.domain, e.eventID, e.toUserAccountID, e.invitationToken, e.requestInfo_version, e.errorCode, e.TCAcceptanceDate, e.requestInfo_userLogin, e.adminAccountID, e.platform, e.toGroupID] - columnsAs: {*=*} - joinColumns: [] - filters: [ ?s1 :eventType ?f1 . FILTER(?f1 = "AUTHENTICATION_SUCCESSFUL_EVENT") ] - orderby: [] In executeRdfSelect, selectStmt is: select distinct col from table(sem_match( 'select * where { ?col rdfs:domain :EVENTS . ?s1 ?col ?v . ?s1 :eventType ?f1 . FILTER(?f1 = "AUTHENTICATION_SUCCESSFUL_EVENT") }' , SEM_MODELS('EVENTS_CS347_PROF'), null, SEM_ALIASES( SEM_ALIAS('', 'http://www.example.org/people.owl#')), null) ) - columnsAs: {e.eventDate="eventDate", e.requestInfo_platform="requestInfo_platform", e.DBUNIQUEID="DBUNIQUEID", e.requestInfo_userAgent="requestInfo_userAgent", e.guest="guest", e.ID="ID", e.groupID="groupID", e.source="source", e.requestInfo_IP="requestInfo_IP", e.eventBody="eventBody", e.version="version", e.requestInfo_serialNumber="requestInfo_serialNumber", e.requestInfo_pairingToken="requestInfo_pairingToken", e.userAccountID="userAccountID", e.eventType="eventType", e.domain="domain", e.eventID="eventID", e.devicePairingID="devicePairingID", e.requestInfo_version="requestInfo_version", e.requestInfo_userLogin="requestInfo_userLogin"}

  13. Step 2- generate the SPARQL. ReL as a type of MongoDBwith SPARQL Queries SELECT v1 "eventDate", v2 "requestInfo_platform", v3 "DBUNIQUEID", v4 "requestInfo_userAgent", v5 "guest", v6 "ID", v7 "groupID", v8 "source", v9 "requestInfo_IP", v10 "eventBody", v11 "version", v12 "requestInfo_serialNumber", v13 "requestInfo_pairingToken", v14 "userAccountID", v15 "eventType", v16 "domain", v17 "eventID", v18 "devicePairingID", v19 "requestInfo_version", v20 "requestInfo_userLogin" FROM TABLE(SEM_MATCH('SELECT * WHERE { ?s1 rdf:type :EVENTS . OPTIONAL { ?s1 :eventDate ?v1 } OPTIONAL { ?s1 :requestInfo_platform ?v2 } OPTIONAL { ?s1 :DBUNIQUEID ?v3 } OPTIONAL { ?s1 :requestInfo_userAgent ?v4 } OPTIONAL { ?s1 :guest ?v5 } OPTIONAL { ?s1 :ID ?v6 } OPTIONAL { ?s1 :groupID ?v7 } OPTIONAL { ?s1 :source ?v8 } OPTIONAL { ?s1 :requestInfo_IP ?v9 } OPTIONAL { ?s1 :eventBody ?v10 } OPTIONAL { ?s1 :version ?v11 } OPTIONAL { ?s1 :requestInfo_serialNumber ?v12 } OPTIONAL { ?s1 :requestInfo_pairingToken ?v13 } OPTIONAL { ?s1 :userAccountID ?v14 } OPTIONAL { ?s1 :eventType ?v15 } OPTIONAL { ?s1 :domain ?v16 } OPTIONAL { ?s1 :eventID ?v17 } OPTIONAL { ?s1 :devicePairingID ?v18 } OPTIONAL { ?s1 :requestInfo_version ?v19 } OPTIONAL { ?s1 :requestInfo_userLogin ?v20 } ?s1 :eventType ?f1 . FILTER(?f1 = "AUTHENTICATION_SUCCESSFUL_EVENT") }' , SEM_MODELS('EVENTS_CS347_PROF'), null, SEM_ALIASES( SEM_ALIAS('', 'http://www.example.org/people.owl#')), null) )

  14. The generated SPARQL can also be used to query the data using SQLDeveloper, APEX, Tableau or any other SQL query system. ReL as a type of MongoDBwith SPARQL Queries

  15. ReL Team • Possible Opportunities: • Flesh out ReLas a type of MongoDB • SQL to SPARQL translation (probably publishable) • SIM Query Language to SPARQL translation (probably publishable) • These would make a very good cs370 projects, also.

  16. Final Review • Review the Midterm, Homework and Quizzes • SQL Emphasis on Homework 5 and 6 • B-Tree Indexing • Normalization

  17. Midterm Exam Grade = max(Midterm Grade, Final Exam Grade) Project Grade Homework Grade Grade = (25*(MAX(R3,Z3)/100))+(20*(U3/10))+(15*(W3/10))+(15*(S3/100))+(25*(Z3/100)) Midterm Exam Grade Final Exam Grade Quiz Grade

  18. Final Grade = max(Current Grade, Current Grade + Final Exam) (i.e., the Final is optional, it can’t hurt you, it can only help – Thank you for your effort this semester especially on the projects.) Have a Great Fall!

More Related