introduction to relational database systems l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Introduction to Relational Database Systems PowerPoint Presentation
Download Presentation
Introduction to Relational Database Systems

Loading in 2 Seconds...

play fullscreen
1 / 29

Introduction to Relational Database Systems - PowerPoint PPT Presentation


  • 100 Views
  • Uploaded on

Introduction to Relational Database Systems. Lecture 4. Relational database systems. data objects relations / tables operators applied to tables generate tables. Relations / tables. explicit data values extensionally defined atomic keys integrity design

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Introduction to Relational Database Systems' - tuyen


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
relational database systems
Relational database systems
  • data objects
    • relations / tables
  • operators
    • applied to tables
    • generate tables
relations tables
Relations / tables
  • explicit data values
    • extensionally defined
  • atomic
  • keys
  • integrity
  • design
    • includes how to organise data in tables
atomic values
Atomic values

no access to individual items

integrity
Integrity
  • restrictions on data defined by users
    • on individual tables
      • age > 18; salary < 100k
    • on more than one table
      • if budget < 10M then salary < 50k
  • implicit in the data model
primary key integrity
Primary key integrity

incorrect model

foreign key integrity
Foreign key integrity

incorrect model

?

relational operators
Relational operators
  • characteristics
    • set at a time
    • base and derived tables
    • ‘closed’ with respect to relations / tables
      • nested expressions
  • include
    • RESTRICT
    • PROJECT
    • JOIN
restict
RESTICT

RESTRICT Depts WHERE Budget > 8M

project
PROJECT

PROJECT Depts OVER Dept_id, Budget

slide14
JOIN

JOIN Depts AND Emps OVER Dept_id

nested statements
Nested statements
  • “the members of all departments that have the budget greater than 7M”
  • JOIN (RESTRICT Depts WHERE Budget > 7M) AND Emps OVER Dept_id
relational model
Relational model
  • a data model in which all data is modelled as relations
    • a way of looking at data
  • a prescription for a way of
    • representing data
    • manipulating data
    • representing integrity constraints
relational database systems17
Relational database systems
  • relational DBMS
    • implements the relational model
      • not in its entirety
      • may add new features
  • relational database system
    • a database application developed in the relational model and implemented in a relational DBMS
    • physical details hidden from the user
relational dbms features
Relational DBMS - features
  • views
    • security
  • the optimiser
  • the data catalogue / data dictionary
views in relational systems
Views (in relational systems)
  • named derived table
  • the definition stored in the catalogue
  • evaluated only when used
    • optimisation
  • used as if it were a real table
    • problems with updates
  • views
    • ANSI/SPARC
    • relational
views
Views
  • CREATEVIEW TopEmp AS
  • PROJECT
  • (SELECT Emps WHERE Salary > 33K)
  • OVER E_name, Salary
views usage
Views - usage
  • SELECT TopEmp WHERE Salary <= 40
  • SELECT
  • PROJECT
  • SELECT Emps WHERE Salary > 33
  • OVER E_name, Salary
  • WHERE Salary <= 40
security and views
Security and views
  • how would you use the view mechanism in conjunction with the security system?

DEFINE SECURITY RULE AS ...

the optimiser
The optimiser
  • operators - set level
    • the DBMS decides how to best perform the operations, based on
      • strategies of evaluation
      • information about the DB (in the catalogue)
the best evaluation strategy
The best evaluation strategy
  • PROJECT
  • SELECT Emps WHERE E_id = E2
  • OVER E_name, Salary
the catalogue
The catalogue
  • information about the database
      • schemas
      • mappings
      • integrity rules
      • views definition
      • security rules ...
  • other modules that need it
      • the optimiser
      • the security system ...
the system table tables
The system table “Tables”

PROJECT Tables OVER Coulcount, Rowcount

summary
Summary
  • relational model
      • relations
      • operators
      • integrity
  • relational DBMSs
      • implement the relational model
      • views + security
      • the optimiser
      • the catalogue
  • next lecture : SQL