Ufcekg 20 2 data schemas applications
Download
1 / 22

UFCEKG-20-2 Data, Schemas & Applications - PowerPoint PPT Presentation


  • 98 Views
  • Uploaded on

UFCEKG-20-2 Data, Schemas & Applications. Lecture 8 Database Theory & Practice (2) : The Relational Data Model. Origins of the Relational Model. The relational model was developed by EF Codd in the early 1970s.

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 ' UFCEKG-20-2 Data, Schemas & Applications' - levia


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
Ufcekg 20 2 data schemas applications

UFCEKG-20-2 Data, Schemas & Applications

Lecture 8Database Theory & Practice (2) : The Relational Data Model


Origins of the relational model
Origins of the Relational Model

  • The relational model was developed by EF Codd in the early 1970s.

  • Commercial systems based on the relational model appeared in the late 1970s.

  • At present there are several hundred relational DBMSs and most computer vendors support 'relational' software.

  • Examples of well-known products include Oracle, DB2, Sybase, MySQL, MS.SQL Server and MS Access.

    Informally, a relational system is a system in which:

    1. The data is perceived by the user as tables (and nothing but tables).

    2. The operators available to the user for (e.g.) retrieval are operators that derive “new” tables from "old" ones. For example, there is one operator, restrict, which extract a subset of the rows of a given table, and another, project, which extracts a subset of columns - and a row subset and a column subset of a table can both be regarded in turn as tables in their own right.


Components and terminology 1
Components and terminology (1)

The model uses terminology taken from mathematics, particularly set theory and predicate logic.

Basic terminology used in relational theory includes:

  • relation - this corresponds to a table or flat file with columns and rows

  • tuple - a row of a relation

  • attribute - a named column of a relation

  • domain - the set of allowable values for one or more attributes

  • degree of a relation - the number of attributes it contains

  • cardinality of relation - the number of tuples it contains.



Properties of relations
Properties of relations

  • There is only one data structure in the relational data model - the relation.

  • Every relation and every attribute within a relation must have a distinct name.

  • Attribute (column) values of a relation are atomic (i.e. single valued).

  • All values in an attribute (column) are taken from same domain.

  • The ordering of columns in a relation is not significant.

  • Duplicate tuples (rows) are not allowed (e.g. each row in a relation must be distinct).

  • The ordering of tuples (rows) and attributes (columns) is not significant.


Relational algebra relational calculus
Relational algebra & relational calculus

  • Relational algebra (ra) and relational calculus (rc) are both formal (mathematically based) languages defined by EF Codd.

  • ra & rcare logically equivalent languages. ra is “procedural” and rc is “declarative” in nature.

  • ra and rc are the formal grounding of the relational database model and illustrate the basic operations required by any data manipulation language such as SQL.

  • Relational algebra is an offshoot of first-order logic, is a set of relationsclosed under operators. Operators operate on one or more relations to yield a relation.

  • The “closure” property relates to the fact that from any given relational operation another relation is output - this is often referred to as the “relations in – relations out” property.


Relational algebra operators 1
Relational algebra operators (1)

  • Each relational operator takes one or more relations as its input and produces a new relation as output (closure). Codd originally defined eight operators, in two classes:



Relational algebra operators 3 dept emp salgrade example 1
Relational algebra operators (3)dept – emp – salgrade example (1)

Department : dept (depno, dname, location)

Employee : emp (empno, ename, mgr, sal, deptno)

Salary Grade : salgrade (grade, losal, hisal)


Relational algebra operators 4 dept emp salgrade example 2
Relational algebra operators (4)dept – emp – salgrade example (2)

  • dept table


Relational algebra operators 5 dept emp salgrade example 3
Relational algebra operators (5)dept – emp – salgrade example (3)

  • emp table


Relational algebra operators 6 dept emp salgrade example 4
Relational algebra operators (6)dept – emp – salgrade example (4)

  • salgrade table


Relational algebra operators 7 dept emp salgrade example 5
Relational algebra operators (7)dept – emp – salgrade example (5)

  • Restrict => subset of the Rows in a Table

    RESTRICT EMP WHERE sal > 2000


Relational algebra operators 8 dept emp salgrade example 6
Relational algebra operators (8)dept – emp – salgrade example (6)

  • Project => subset the Columns in a Table

    PROJECT EMP [EMPNO, SAL,DEPTNO]


Relational algebra operators 9 dept emp salgrade example 7
Relational algebra operators (9)dept – emp – salgrade example (7)

  • Restrict-Project

    RESTRICT EMP WHERE SAL >2000

    PROJECT EMP[EMPNO, SAL, DEPTNO]

call this EMPX

Could you reverse these operations - always? ( project then restrict?)


Relational algebra operators 10 dept emp salgrade example 8
Relational algebra operators (10)dept – emp – salgrade example (8)

  • Product =>combine each row of one table with each row of the other

    PRODUCT DEPT with EMPX


Relational algebra operators 11 dept emp salgrade example 9
Relational algebra operators (11)dept – emp – salgrade example (9)

continued from last page :


Relational algebra operators 12 dept emp salgrade example 10
Relational algebra operators (12)dept – emp – salgrade example (10)

  • Product (Cartesian product)

DEPT has 4 records

EMPX has 6 records

so DEPT x EMPX has 24 records

but not very useful


Relational algebra operators 13 dept emp salgrade example 11
Relational algebra operators (13)dept – emp – salgrade example (11)

  • Equi-Join =>

    product restricted to rows which have matching common domain


Relational algebra operators 14 dept emp salgrade example 12
Relational algebra operators (14)dept – emp – salgrade example (12)

  • Natural Join =>

    equi-join projected with the duplicate column removed


Basic sql
Basic SQL

  • SELECT * FROM EMP WHERE SAL > 2000;

  • SELECT ENAME,SAL,DEPTNO FROM EMP;

  • SELECT ENAME,SAL,DEPTNO FROM EMP WHERE SAL > 2000;

  • SELECT * FROM EMP, DEPT WHERE SAL > 2000;

  • SELECT * FROM EMP,DEPT WHERE SAL > 2000 AND EMP.DEPTNO = DEPT.DEPTNO;

  • SELECT EMPNO, SAL, DEPTNO, DNAME FROM EMP,DEPT WHERE SAL > 2000 AND EMP.DEPTNO = DEPT.DEPTNO;


Bibliography readings
Bibliography / Readings

Bibliography

  • An Introduction to Database Systems (8th ed.), C J Date, Addison Wesley 2004

  • Database Management Systems, P Ward & G Defoulas, Thomson 2006

    Readings

  • Introduction to SQL, McGraw-Hill/Osbourne (handout)


ad