Transcript 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
• We will see later that this has been an area of dramatic
advances
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?)
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
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
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
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
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
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
MySQL Information Schema
• This is a meta database that contains information on schemas,
tables, columns, etc.
• You can query but not update it
Schema of Information DB
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
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
How about a simple query…
• select examplefield2
• from exampletable1
• where (examplefield1 = "Buzz")
Class project
• 60% of final grade
• Each exam – 20%
• Build an application
• Use full text search
• Or XML capabilities
• Or multimedia capabilities