1 / 16

Results of the survey and relational dbs

Results of the survey and relational dbs. Fall 2011. The results. More weight on project More hands-on stuff But the underlying formalisms and technological concepts are important Programming in SQL Post-relational technology. An early extension: UDTs. Enumerated sets

rollo
Download Presentation

Results of the survey and relational dbs

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. Results of the survey and relational dbs Fall 2011

  2. The results • More weight on project • More hands-on stuff • But the underlying formalisms and technological concepts are important • Programming in SQL • Post-relational technology

  3. An early extension: UDTs • Enumerated sets • We will see later that this has been an area of dramatic advances

  4. Foreign Keys • To minimize “meaningless” joins • Root cause of problems: value semantics • Enhancing the semantics of a schema • “Advice” from the designer • Warning if you are not using a PK (or CK) and FK pair • Question: if you delete the tuple with the PK, what happens to the tuple with the FK? (set null? Leave it?) • Question: if you change the value of the PK, what happens to the tuple with the FK? (change it?)

  5. Triggers: generic integrity constraints • After (SQL1) If (SQL2 boolean) Then (SQL3) • Or After, When, implied • The After tells the system when to check (minimizes cost) • The When or If sets the trigger • The Then or (implied) dictates the corrective action • This sort of thing can be used to “undo” transactions - later

  6. Views • Run an SQL program with no updates • Give the result a name • Now you have a “view table” • View tables can sometimes be updated

  7. Access control • Grant (rights) On (schema elements) To (class of users) With (grant option) • Does not mesh well with organizations with pre-existing security levels – in these cases, we use “mandatory” controls where you cannot update data below your level

  8. Real DBMSs • MySQL • free and old and minimal • extremely popular • PostgreSQL – free and made from Postgres, named inspired by MySQL • more modern • with great UDT capabilities and some XML capabilities • growing in popularity • (Postgres was named after Ingres, a first generation RDBMS.) • Microsoft SQL Server 2008 • commercial and constantly being updated • with full text search, extensive XML capabilities • Oracle • commercial and constantly being updated • with great multimedia, XML, and data warehousing (OLAP) capabilities

  9. RDBS GUIs • General purpose • Navicat • DBVis • SQL Razor • Targeted and vendor supplied • MySQL workbench • pgAdmin (postgres) • SQL Server Management Studio • Oracle SQL Developer • Targeted 3rd party supplied • Maestro for MySQL, PostgreSQL, Oracle, SQL Server

  10. GUI capabilities • Connecting to DBMSs • Creating DBs • Updating and querying DBs • Administering DBs • Building server code for websites (such as PHP that can read and write the tables) • But they are not meant as production interfaces

  11. MySQL Information Schema • This is a meta database that contains information on schemas, tables, columns, etc. • You can query but not update it

  12. Schema of Information DB

  13. The “MySQL” database • Used by administrator • Created when MySQL is installed • A primary purpose is to control user access rights • user – can the user connect to the database server? • db – what user can access what database? • tables_priv and columns_priv – user priviledges

  14. Performance schema • Maintains data used for performance tuning • Everything is an “event” • Execution of an SQL schema • A wait on the OS • Does not have to be activated • You can query it to find what is causing a wait

  15. How about a simple query… • select examplefield2 • from exampletable1 • where (examplefield1 = "Buzz")

  16. Class project • 60% of final grade • Each exam – 20% • Build an application • Use full text search • Or XML capabilities • Or multimedia capabilities

More Related