1 / 14

More on Views (3.6—3.7)

More on Views (3.6—3.7). Author: Dr. Adam Anthony For text: Database Management Systems (Ramakrishnan and Gherke ). Understanding Queries. SELECT * FROM Students S WHERE S.age =18. What is the difference?. View Basics. Views, once defined, may be treated just like a table

anka
Download Presentation

More on Views (3.6—3.7)

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. More on Views (3.6—3.7) Author: Dr. Adam Anthony For text: Database Management Systems (Ramakrishnan and Gherke)

  2. Understanding Queries SELECT * FROM Students S WHERES.age=18 What is the difference?

  3. View Basics • Views, once defined, may be treated just like a table • No explicit values • Data comes from other tables • Defined by queries • Indirect consequence: Changing the queried table(s) changes the view! CREATE VIEW YoungActiveStudents (name, grade) AS SELECT S.name, E.grade FROM Students S, Enrolled E WHERE S.sid = E.sid and S.age<21

  4. Why Views? • Hiding Complexity • Simplifying the programming interface • Security • (Possibly) Convenient updates, insertions, deletions • Domain-restricted insertions

  5. Hiding Complexity CREATE VIEW red_green_sailors (sailor_id) AS SELECTS.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid ANDB.color=‘red’ AND S.sid IN (SELECT S2.sid FROM Sailors S2, Boats B2, Reserves R2 WHERE S2.sid=R2.sid AND R2.bid=B2.bid AND B2.color=‘green’) SELECT * FROM red_green_sailors • When is this useful? • Frequently used queries • Simplifying even BIGGER queries • Secret Queries

  6. Views and Application Programming • Conceptual Schema: the actual database tables and how they interact • External Schema: What an application programmer expects to see from the DB • Naïve external schema: provide application programmer with details of conceptual schema • Disallows changing the schema at a later date! • Better choice: implement external schema as views, and maintain them as the conceptual schema changes

  7. Views and Security • Most DBMS’s provide some means of access control • Given a username, privileges to data can be restricted • General approach: restrict tables, allow views Student_Accounts: For helpdesk employees Student table—only available to administrators

  8. Updatable views • A view is updatable if*: • The query that builds it only selects columns, and does not create any calculated columns (not always true) • The query uses data from a table (usually true) • The update/insert/delete only affects a single base table *Rules taken from MSDN online documentation: http://msdn.microsoft.com/en-us/library/ms187956%28SQL.105%29.aspx

  9. Updating View Rows • View is created, and shown below • View uses two base tables • The update must only SET fields from a single base table • The WHERE portion of the update may condition on other fields CREATE VIEW StudentGrades(name, cid,grade) AS SELECT S.name, E.cid, E.grade FROM Students S, Enrolled E WHERE S.sid = E.sid UPDATEStudentGrades • SET grade = “I” • WHERE SG.name = “Jones”

  10. Inserting and Deleting View Rows • Deleting: view may only be defined over one table • Insertion: Will work, sort of: • Still may only affect one of the base tables • Insertion will result in null for view fields from other tables • Any base table fields not in view will be set to NULL • If primary key of base not in view, this will fail!

  11. Restricting Domains With Views • Views depend on underlying data, but are loaded dynamically • When inserting: • If inserted values don’t match query, they won’t be visible in the view! • WITH CHECK OPTION: • Reject any insertions that won’t be visible in the view • Can be used for limited domain restriction on fields CREATE VIEW ProbationaryStudents (name, grade) AS SELECT S.name, E.grade FROM Students S, Enrolled E WHERE S.sid = E.sid and E.grade<2.0 WITH CHECK OPTION

  12. Other Odds and Ends

  13. More on Defaults • With foreign keys, we have the SET DEFAULT option: FOREIGN KEY sid REFERENCES students ON DELETE SET DEFAULT • What is the default value? • We set it on table creation: CREATE TABLE students( sid INTEGER DEFAULT 111223333, …

  14. Employees name Translating Aggregates ssn lot create table projects(…) create table departments (…) create table employees (… ) CREATE TABLE SPONSORS( since : datetime, pid : char(10), did: char(10), primary key (pid,did) foreign key pid references projects foreign key did references departments ); CREATE TABLE MONITORS( until : datetime, pid: char(10), did: char(10), ssn: chare(11), primary key (pid,did,ssn), foreign key (pid,did) references sponsors foreigh key ssn references employee); Monitors until since started_on dname pid pbudget did budget Sponsors Departments Projects

More Related