Relational databases

Document Sample
Relational databases Powered By Docstoc
					                   Relational Databases
• Codd's relational model 1970
• First implementations in 1980's
• Very popular, e.g., Access, Paradox, Oracle,
  SQL Server (Microsoft / Sybase), DB2 (IBM),
  Ingres, Informix, MySQL
• Other modern database types:
     – object databases
     – object-relational databases
Database Systems / Eero Kettunen                 1
Lahti Polytechnic, Faculty of Business Studies
                             Table (Relation)
                           EMPLOYEE
                           empid    name         room#
                           10392    John            10
                           30281    Mary            13
                           33029    Bill            12
                           13829    Jill            13

• Other order of rows doesn't make any difference!
• Notation: EMPLOYEE(empid, name, room#)

Database Systems / Eero Kettunen                         2
Lahti Polytechnic, Faculty of Business Studies
                                                   Key
• Identifies a row                    • primary key
• Key integrity constraint: no two • secondary key
  rows with same key values
• Key can consist of one or
  more columns!              EMPLOYEE
                                                   empid   name   room#
                                                   10392   John      10
                                                   30281   Mary      13
                                                   33029   Bill      12
                                                   13829   Jill      13
  Database Systems / Eero Kettunen                                        3
  Lahti Polytechnic, Faculty of Business Studies
                                  Foreign Key
EMPLOYEE                                         ROOM
empid    name                       room#        room#   capacity
10392    John                          10        10          1
30281    Mary                          13        11          2
33029    Bill                          12        12          4
13829    Jill                          13        13          5
referencing table                       referenced table
"child table"                           "parent table"
Terminology with respect to this foreign key setting!
• Foreign key references to another table's
  (primary) key.
Database Systems / Eero Kettunen                                    4
Lahti Polytechnic, Faculty of Business Studies
          Referential integrity constraint
• Foreign key values must be found in the
  referenced table (exception: NULL values can be allowed).
     EMPLOYEE                                               ROOM
     empid    name                       room#              room#         capacity
     10392    John                          10              10                1
     30281    Mary                          13              11                2
     33029    Bill                          12              12                4
     13829    Jill                          15              13                5
                                        Violation of referential integrity constraint!

Database Systems / Eero Kettunen                                                     5
Lahti Polytechnic, Faculty of Business Studies
                DBMS
     (Database Management System)
• Software "hosting" the data:
   – user command interpretation and carrying out
     the resulting operations, e.g., creation of
     database objects and querying the data
   – data access control
   – multiple user control
   – data backups
   – etc.
Database Systems / Eero Kettunen                    6
Lahti Polytechnic, Faculty of Business Studies
   SQL (Structured Query Language)

• Standards:
      SQL, SQL-92 (SQL2), SQL-99 (SQL3), SQL 2003 (SQL4)

• SQL-99 includes object features.

• No product fully implements the standard.


Database Systems / Eero Kettunen                     7
Lahti Polytechnic, Faculty of Business Studies
                                   Use of SQL
• interactive (user writes SQL statements)
• embedded (programmer writes SQL statements
  to the program code)
• dynamic (programmer writes code which
  generates SQL statements)


Database Systems / Eero Kettunen                 8
Lahti Polytechnic, Faculty of Business Studies
       DDL (Data Definition Language)
• For creation of new databases and specifying
  logical structure of data.
• SQL statement to create table structure:
      CREATE TABLE EMPLOYEE (
      empid varchar(5) primary key,
      name varchar(20),
      room# varhar(2) );
• CREATE, DROP, ALTER
Database Systems / Eero Kettunen                 9
Lahti Polytechnic, Faculty of Business Studies
  DML (Data Manipulation Language)
• For querying and modifying the data.
• SQL statement to insert a row to a table:
      INSERT INTO EMPLOYEE
      VALUES ('10392','John','10');
• SQL statement to query room 10 members:
      SELECT name FROM EMPLOYEE
      WHERE room# = '10';
• SELECT, INSERT, UPDATE, DELETE
Database Systems / Eero Kettunen                 10
Lahti Polytechnic, Faculty of Business Studies
                              Access Control
• Database users can be given different
  permissions to access data, e.g.,
      GRANT SELECT ON EMPLOYEE TO USER1;
• Concurrent transaction control, e.g., locking data
  from other users until transaction is committed or
  cancelled.
      COMMIT;
      ROLLBACK;
Database Systems / Eero Kettunen                   11
Lahti Polytechnic, Faculty of Business Studies
                                Syntax Issues
• SQL commands (keywords and database object
  names) are not case-sensitive.
      select NAME FRom Employee;
• Row changes, tabs and additional spaces can
  be used where a single space is allowed.
• Data 'John' is different from 'john'. How the string
  comparison operators works depends on the
  system:
      select room from employee where name = 'john';
Database Systems / Eero Kettunen                       12
Lahti Polytechnic, Faculty of Business Studies
                Client/Server Architecture
CLIENTS                                            SERVER
                                 SQL statement
   >>select name
      select name
   >>from employee
      from employee
   John                               result set           DBMS
    John
                                      / message
    USER
    USER
                                                       database
                                                       operations
                      SERVER

                                                      DB          DB
                                 DB


Database Systems / Eero Kettunen                                       13
Lahti Polytechnic, Faculty of Business Studies

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:11
posted:4/2/2012
language:
pages:13