CS122aSlides11

CS122aSlides11 - ICS122A / EECS116 Introduction to Data...

Info iconThis preview shows pages 1–9. Sign up to view the full content.

View Full Document Right Arrow Icon
1 ICS122A / EECS116 Introduction to Data Management Spring 2009 Prof. Mike Carey Bren School of ICS UC Irvine Slides based on previous CS122a lecture notes as well as  material borrowed from U-Wisconsin, Stanford, & Berkeley
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
CS122A/EECS116 Notes 11 2 Announcements HWs and projects Project Part 2 is due this evening by midnight Please include your most current E-R schema as well (!) HW #4 is available (and due next Tuesday evening) Next week: Indexes Guest lecturer: Alex Behm (your TA) I will be out of town M/Tu/W but back on Th/F Today: Views & Security Now that you’ve mastered the basics of database schema design, the relational data model, queries, and SQL, we’re going to spend the rest of the quarter on more advanced features (outside & inside), basic database programming, database design theory, and XML. ..
Background image of page 2
ICS122A/EECS116 Notes 11 3 SQL Views & Security Views Top layer of the three-schema architecture View definitions and query execution Views and updates View updates using triggers Security SQL access control concepts Views (and stored procedures) for access control
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
ICS122A/EECS116 Notes 11 4 Three-Layer Schema Architecture Three levels of schemas (from Lecture #1) Physical Schema Logical Schema View1 View 2 View n Physical Level (Physical files and indexes) Conceptual Level (Central relational schema) External Level (Today’s topic…!)
Background image of page 4
ICS122A/EECS116 Notes 11 5 Views eName Dno Sal Jack 111 50K Alice 111 90K Lisa 222 80K Tom 333 70K Mary 333 60K dno dname Mgr 111 Sells Alice 222 Toys Lisa 333 Electronics Mary eName Dno Sal Jack 111 50K Alice 111 90K Lisa 222 80K Tom 333 70K Mary 333 60K Views are “virtual” relations (not physically stored ) eName Dno Sal Jack 111 50K Alice 111 90K Lisa 222 80K Tom 333 70K Mary 333 60K Views Uses can include: Simplify common/complex queries by predefining useful “tables” Tailor DB schema to meet the needs of different applications Provide logical data independence Serve as a unit of access control for security purposes Base Tables
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
ICS122A/EECS116 Notes 11 6 A Simple Example Ex 1: Employees in the Toy department CREATE VIEW   toyEmpInfo  AS SELECT  ename, sal, Emp.dno, mgr FROM   Emp, Dept WHERE   Emp.dno = Dept.dno  AND  dname = ‘Toy’; Dept(dno, dname, mgr) dno dname mgr 111 Toy Alice 222 Sales Lisa 333 Electronics Mary Emp (ename, dno, sal) ename dno sal Jack 111 50K Alice 111 90K Lisa 222 80K Tom 333 70K Mary 333 60K
Background image of page 6
ICS122A/EECS116 Notes 11 7 Another Simple Example Ex 2: Some attributes can be dropped (e.g., dno) and others renamed (e.g., ename) CREATE VIEW  toyEmp  AS SELECT  ename  AS  name, sal, mgr FROM   Emp, Dept WHERE   Emp.dno = Dept.dno  AND  dname = ‘Toy’; Dept(dno, dname, mgr) dno dname mgr 111 Toy Alice 222 Sales Lisa 333 Electronics Mary Emp (ename, dno, sal) ename dno sal Jack 111 50K Alice 111 90K Lisa 222 80K Tom 333 70K Mary 333 60K toyEmp (name, sal, mgr) name sal mgr Jack 50K Alice Alice 90K Alice
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
ICS122A/EECS116 Notes 11 8 A Few More Examples Ex 3: A department summary for managers to use CREATE VIEW
Background image of page 8
Image of page 9
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 06/18/2009 for the course CS 122a taught by Professor Carey during the Spring '09 term at UC Irvine.

Page1 / 23

CS122aSlides11 - ICS122A / EECS116 Introduction to Data...

This preview shows document pages 1 - 9. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online