1 / 18

Understanding Cross Product in SQL and Prolog: A Student's Query

This document explores a student's question regarding the meaning of the cross product operation between a set of entities (mrAstor, mrsAstor, mrBlake, mrsBlake, etc.) in SQL and Prolog. We break down the concept of 'married' relations and demonstrate examples using the provided employee and department databases in Prolog. The exercise clarifies the relationship structure and helps learners understand relational database concepts through practical examples, making the query relevant for both SQL and Prolog users.

rodney
Télécharger la présentation

Understanding Cross Product in SQL and Prolog: A Student's Query

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. Programming Languages Prolog Part 3 SQL & Prolog

  2. Good question from a student: what does the following mean? married (mrAstor, mrsAstor). Cross Product of mrAstor, mrsAstor, mrBlake, mrsBlake, mrCrane, mrsCrane, mrDavis, mrsDavis (mrAstor, mrAstor) (mrAstor, mrsAstor) (mrsAstor, mrAstor) (mrsAstor, MrsAstor) (mrAstor, mrBlake) (mrAstor, mrsBlake) (mrsAstor, mrBlake) (mrsAstor, MrsBlake) (mrAstor, mrCrane) (mrAstor, mrsCrane) (mrsAstor, mrCrane) (mrsAstor, MrsCrane) (mrAstor, mrDavis) (mrAstor, mrsDavis) (mrsAstor, mrDavis) (mrsAstor, MrsDavis) (mrBlake, mrAstor) (mrBlake, mrsAstor) (mrsBlake, mrAstor) (mrsBlake, MrsAstor) (mrBlake, mrBlake) (mrBlake, mrsBlake) (mrsBlake, mrBlake) (mrsBlake, MrsBlake) (mrBlake, mrCrane) (mrBlake, mrsCrane) (mrsBlake, mrCrane) (mrsBlake, MrsCrane) (mrBlake, mrDavis) (mrBlake, mrsDavis) (mrsBlake, mrDavis) (mrsBlake, MrsDavis) (mrCrane, mrAstor) (mrCrane, mrsAstor) (mrsCrane, mrAstor) (mrsCrane, MrsAstor) (mrCrane, mrBlake) (mrCrane, mrsBlake) (mrsCrane, mrBlake) (mrsCrane, MrsBlake) (mrCrane, mrCrane) (mrCrane, mrsCrane) (mrsCrane, mrCrane) (mrsCrane, MrsCrane) (mrCrane, mrDavis) (mrCrane, mrsDavis) (mrsCrane, mrDavis) (mrsCrane, MrsDavis) (mrDavis, mrAstor) (mrDavis, mrsAstor) (mrsDavis, mrAstor) (mrsDavis, MrsAstor) (mrDavis, mrBlake) (mrDavis, mrsBlake) (mrsDavis, mrBlake) (mrsDavis, MrsBlake) (mrDavis, mrCrane) (mrDavis, mrsCrane) (mrsDavis, mrCrane) (mrsDavis, MrsCrane) (mrDavis, mrDavis) (mrDavis, mrsDavis) (mrsDavis, mrDavis) (mrsDavis, MrsDavis)

  3. Good question from last class. What does the following mean? married (mrAstor, mrsAstor). married subset (Relation) of Cross Product of mrAstor, mrsAstor, mrBlake, mrsBlake, mrCrane, mrsCrane, mrDavis, mrsDavis (mrAstor, mrAstor) (mrAstor, mrsAstor) (mrsAstor, mrAstor) (mrsAstor, MrsAstor) (mrAstor, mrBlake) (mrAstor, mrsBlake) (mrsAstor, mrBlake) (mrsAstor, MrsBlake) (mrAstor, mrCrane) (mrAstor, mrsCrane) (mrsAstor, mrCrane) (mrsAstor, MrsCrane) (mrAstor, mrDavis) (mrAstor, mrsDavis) (mrsAstor, mrDavis) (mrsAstor, MrsDavis) (mrBlake, mrAstor) (mrBlake, mrsAstor) (mrsBlake, mrAstor) (mrsBlake, MrsAstor) (mrBlake, mrBlake) (mrBlake, mrsBlake) (mrsBlake, mrBlake) (mrsBlake, MrsBlake) (mrBlake, mrCrane) (mrBlake, mrsCrane) (mrsBlake, mrCrane) (mrsBlake, MrsCrane) (mrBlake, mrDavis) (mrBlake, mrsDavis) (mrsBlake, mrDavis) (mrsBlake, MrsDavis) (mrCrane, mrAstor) (mrCrane, mrsAstor) (mrsCrane, mrAstor) (mrsCrane, MrsAstor) (mrCrane, mrBlake) (mrCrane, mrsBlake) (mrsCrane, mrBlake) (mrsCrane, MrsBlake) (mrCrane, mrCrane) (mrCrane, mrsCrane) (mrsCrane, mrCrane) (mrsCrane, MrsCrane) (mrCrane, mrDavis) (mrCrane, mrsDavis) (mrsCrane, mrDavis) (mrsCrane, MrsDavis) (mrDavis, mrAstor) (mrDavis, mrsAstor) (mrsDavis, mrAstor) (mrsDavis, MrsAstor) (mrDavis, mrBlake) (mrDavis, mrsBlake) (mrsDavis, mrBlake) (mrsDavis, MrsBlake) (mrDavis, mrCrane) (mrDavis, mrsCrane) (mrsDavis, mrCrane) (mrsDavis, MrsCrane) (mrDavis, mrDavis) (mrDavis, mrsDavis) (mrsDavis, mrDavis) (mrsDavis, MrsDavis)

  4. Standard Oracle emp / dept Database

  5. Oracle Sqldeveper http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

  6. Oracle Sqldeveper

  7. Oracle Sqldeveper

  8. Oracle Sqldeveper

  9. Oracle Sqldeveper Cut and Paste this into here and press

  10. Oracle Sqldeveper

  11. Oracle Sqldeveper This database is for this book. But, the book is old and assumes you’re using sqlplus not sqldeveloper. So, ignore all of the formatting examples in the book and just do the sql.

  12. emp / dept Database in Prolog emp(7839, king, president, 0, 17-nov-81, 5000, 0, 10). emp(7698, blake, manager, 7839, 01-may-81, 2850, 0, 30). emp(7782, clark, manager, 7839, 09-jun-81, 2450, 0, 10). emp(7566, jones, manager, 7839, 02-apr-81, 2975, 0, 20). emp(7788, scott, analyst, 7566, 09-dec-82, 3000, 0, 20). emp(7902, ford, analyst, 7566, 03-dec-81, 3000, 0, 20). emp(7369, smith, clerk, 7902, 17-dec-80, 800, 0, 20). emp(7499, allen, salesman, 7698, 20-feb-81, 1600, 300, 30). emp(7521, ward, salesman, 7698, 22-feb-81, 1250, 500, 30). emp(7654, martin, salesman, 7698, 28-sep-81, 1250, 1400, 30). emp(7844, turner, salesman, 7698, 08-sep-81, 1500, 0, 30). emp(7876, adams, clerk, 7788, 12-jan-83, 1100, 0, 20). emp(7900, james, clerk, 7698, 03-dec-81, 950, 0, 30). emp(7934, miller, clerk, 7782, 23-jan-82, 1300, 0, 10). dept(10, accounting, new_york). dept(20, research, dallas). dept(30, sales, chicago). dept(40, operations, boston). select 'emp(' || empno || ', ' || lower(ename) || ', ' || lower(job) || ', ' || nvl(mgr, 0) || ', ' || lower(hiredate) || ', ' || sal || ', ' || nvl(comm, 0) || ', ' || deptno || ').' from emp select 'dept(' || deptno || ', ' || lower(dname) || ', ' || lower(loc) || ').' from dept

  13. emp / dept Database in Prolog | ?- emp(Empno, Ename, Job, _, _, Sal, _, Deptno). Deptno = 10 Empno = 7839 Ename = king Job = president Sal = 5000 ? ; Deptno = 30 Empno = 7698 Ename = blake Job = manager Sal = 2850 ? . . . Haskell> [(empno, ename, job, sal, deptno) | (empno, ename, job, _, _, sal, deptno) <-emp]

  14. emp / dept Database in Prolog | ?- emp(Empno, Ename, Job, _, _, Sal, _, 10). Empno = 7839 Ename = king Job = president Sal = 5000 ? ; Empno = 7782 Ename = clark Job = manager Sal = 2450 ? ; Empno = 7934 Ename = miller Job = clerk Sal = 1300 yes | ?- Haskell> [(empno, ename, job, sal, deptno) | (empno, ename, job, _, _, sal, deptno) <- emp, deptno == 10]

  15. emp / dept Database in Prolog | ?- emp(Empno, Ename, Job, _, _, Sal, _, D), dept(D, Dname, _). D = 10 Dname = accounting Empno = 7839 Ename = king Job = president Sal = 5000 ? ; D = 30 Dname = sales Empno = 7698 Ename = blake Job = manager Sal = 2850 ? ; D = 10 Dname = accounting Empno = 7782 Ename = clark Job = manager Sal = 2450 ? Main> [(empno, ename, job, sal, dname) | (empno, ename, job, _, _, sal, edeptno) <- emp, (deptno, dname, loc) <- dept, edeptno == deptno ]

  16. emp / dept Database in Prolog as Binary Relations (Triple Store) ename(7839, king). ename(7698, blake). ename(7782, clark). ename(7566, jones). ename(7788, scott). ename(7902, ford). ename(7369, smith). ename(7499, allen). ename(7521, ward). ename(7654, martin). ename(7844, turner). ename(7876, adams). ename(7900, james). ename(7934, miller). job(7839, president). job(7698, manager). job(7782, manager). job(7566, manager). job(7788, analyst). job(7902, analyst). job(7369, clerk). job(7499, salesman). job(7521, salesman). job(7654, salesman). job(7844, salesman). job(7876, clerk). job(7900, clerk). job(7934, clerk). mgr(7839, 0). mgr(7698, 7839). mgr(7782, 7839). mgr(7566, 7839). mgr(7788, 7566). mgr(7902, 7566). mgr(7369, 7902). mgr(7499, 7698). mgr(7521, 7698). mgr(7654, 7698). mgr(7844, 7698). mgr(7876, 7788). mgr(7900, 7698). mgr(7934, 7782). hiredate(7839, 17-nov-81). hiredate(7698, 01-may-81). hiredate(7782, 09-jun-81). hiredate(7566, 02-apr-81). hiredate(7788, 09-dec-82). hiredate(7902, 03-dec-81). hiredate(7369, 17-dec-80). hiredate(7499, 20-feb-81). hiredate(7521, 22-feb-81). hiredate(7654, 28-sep-81). hiredate(7844, 08-sep-81). hiredate(7876, 12-jan-83). hiredate(7900, 03-dec-81). hiredate(7934, 23-jan-82). salary(7839, 5000). salary(7698, 2850). salary(7782, 2450). salary(7566, 2975). salary(7788, 3000). salary(7902, 3000). salary(7369, 800). salary(7499, 1600). salary(7521, 1250). salary(7654, 1250). salary(7844, 1500). salary(7876, 1100). salary(7900, 950). salary(7934, 1300). deptno(7839, 10). deptno(7698, 30). deptno(7782, 10). deptno(7566, 20). deptno(7788, 20). deptno(7902, 20). deptno(7369, 20). deptno(7499, 30). deptno(7521, 30). deptno(7654, 30). deptno(7844, 30). deptno(7876, 20). deptno(7900, 30). deptno(7934, 10).

  17. emp / dept Database in Prolog as Binary Relations (Triple Store) | ?- ename(E, Ename), job(E, Job), salary(E, Salary), deptno(E, Deptno). Deptno = 10 E = 7839 Ename = king Job = president Salary = 5000 ? ; Deptno = 30 E = 7698 Ename = blake Job = manager Salary = 2850 ? ; Deptno = 10 E = 7782 Ename = clark Job = manager Salary = 2450 ? Haskell> [(empno, ename, job, sal, deptno) |(x0, empno) <- empno, (x1, ename) <- ename, (x2, job) <- job, (x3, sal) <- sal, (x4, deptno) <- deptno, x0 == x1 && x1 == x2 && x2 == x3 && x3 == x4]

  18. These are all the same relation name = [ (7839, "KING"), (7698, "BLAKE"), (7782, "CLARK"), (7566, "JONES"), (7788, "SCOTT"), (7902, "FORD"), (7369, "SMITH"), (7499, "ALLEN"), (7521, "WARD"), (7654, "MARTIN"), (7844, "TURNER"), (7876, "ADAMS"), (7900, "JAMES"), (7934, "MILLER") ] name(7839, KING). name(7698, BLAKE). name(7782, CLARK). name(7566, JONES). name(7788, SCOTT). name(7902, FORD). name(7369, SMITH). name(7499, ALLEN). name(7521, WARD). name(7654, MARTIN). name(7844, TURNER). name(7876, ADAMS). name(7900, JAMES). name(7934, MILLER). (7839, name, KING) (7698, name, BLAKE) (7782, name, CLARK) (7566, name, JONES) (7788, name, SCOTT) (7902, name, FORD) (7369, name, SMITH) (7499, name, ALLEN) (7521, name, WARD) (7654, name, MARTIN) (7844, name, TURNER) (7876, name, ADAMS) (7900, name, JAMES) (7934, name, MILLER) Also called a triple store. Also a form of a graph. Haskell Prolog RDF * Also called Predicates ( Predicate Logic – the science of correct thinking. ) * see

More Related