In-class Examples List

CIS 315
Database Design and Implementation
Fall 2010

Last modified: Thursday, December 9, 2010

Important Notes:

• This is merely a selection of files and examples, most of which were mentioned or used in the class (lectures and labs). It is not a comprehensive list, nor are all lectures and labs included. Formatting is generally minimal.

• Week 15, Lecture, December 6, 2010 - A few words and in-class discussion on Databases, Society, and Ethics (and discussing the final project milestone)
  • Week 15 Lecture projected notes - issues and questions brought up by class members
  • different version of Week 15 lecture material, in tagged PDF
  
  
• Week 14, Lab, December 1, 2010 - Intro to outer-joins, intro to PL/SQL triggers
  • 315lab14-projected.sql - example SQL script projected in class
  • lab14-results.txt - the results files spooled when 315lab14-projected.sql is executed
  • separate versions of first trigger, inventory_update: trigger1.sql, inventory_update.sql
  • separate versions of second trigger, add_prof_ct: trigger2.sql, add_prof_ct.sql
  • separate versions of third trigger, clear_advisor: trigger3.sql, clear_advisor.sql
  • separate versions of fourth trigger, limit_advisees: trigger4.sql, limit_advisees.sql
  
  
• Week 14, Lecture, November 29, 2010 - A few words on OODBMS's and ORDBMS's
  • Week 14 Lecture projected notes
  • slightly different version of Week 14 lecture material, in tagged PDF
  
  
• Week 13, Lab, November 17, 2010 - intro to simple reports, part 2 (including column, break, compute, and numerous useful SQL date, time, and string functions)
  • 315lab13-projected.sql - example SQL script projected in class
  • lab13-results.txt - one of the results files spooled when 315lab13-projected.sql is executed
  • lab13-results-part2.txt - another of the results files spooled when 315lab13-projected.sql is executed
  • slightly different version of Week 13 lab material, in tagged PDF
  • cleanup.sql - SQL script to restore various SQL*Plus settings that a script creating an ASCII report might muck with (as suggested by T. Koyuncu)
  • try-flat.sql - pulls out the flat-file example out of 315lab13-projected.sql into its own little SQL script
  • flat-empl-data.txt - example flat file of comma-separated data generated by the another part of 315lab13-projected.sql (and/or try-flat.sql)
  • set-up-ex-tbls.sql
  
  
• Week 13, Lecture, November 15, 2010 - Intro to Transactions and concurrency Management, part 2
  • Week 13 Lecture projected notes
  • slightly different version of Week 13 lecture material, in tagged PDF
  
  
• Week 12, Lab, November 10, 2010 - talked about SQL*Loader, introduced views (including how views and the grant command can help enhance database security), and just started the discussion of building better ASCII reports with the help of SQL*Plus commands
  • 315lab12-projected.sql - example SQL script projected in class
  • lab12-results.txt - results file spooled when 315lab12-projected.sql is executed
  • slightly different version of Week 12 lab material, in tagged PDF (but SQL*Loader discussion is in the slightly differnt version of the Week 10 lab material)
  • Files for SQL*Loader examples: (to be hopefully actually *executed* in class eventually...!)
    • create_empltemp.sql
    • empltemp_data1.ctl
    • empltemp_data2.ctl
    • empltemp_data2.dat
    • painter.ctl - example control file showing using a sequence in conjunction with SQL*Loader
    • painter.dat - example data file to go with above control file
  
  
• Week 12, Lecture, November 8, 2010 - Intro to Transactions and concurrency Management, part 1
  • Week 12 Lecture projected notes
  • slightly different version of Week 12 lecture material, in tagged PDF
  
  
• Week 10, Lab, October 27, 2010 - the set-theoretic operations: UNION, INTERSECT, MINUS; the "full" select statement; intro to SQL UPDATE and DELETE; intro to sequences; just started talking about SQL*Loader; and more...
  • set-up-ex-tbls.sql
  • 315lab10-projected.sql - example SQL script projected in class
  • lab10-results.txt - results file spooled when 315lab10-projected.sql is executed
  • slightly different version of Week 10 lab material, in tagged PDF
  • explanation of the "full" select syntax
  • Files for SQL*Loader examples: (to be hopefully gone through in a later lab...)
    • create_empltemp.sql
    • empltemp_data1.ctl
    • empltemp_data2.ctl
    • empltemp_data2.dat
    • painter.ctl - example control file showing using a sequence in conjunction with SQL*Loader
    • painter.dat - example data file to go with above control file
  
  
• Week 10, Lecture, October 25, 2010 - Basics of Database Design - Part 2
  • Week 10 Lecture projected notes
  • slightly different version of Week 10 lecture material, in tagged PDF
  • Some of the little ER models for the in-lecture examples (all of these should also be in the slightly-different posting above, as well)
    • Accounts model example
    • Univ-person model example
    • Univ-sponsor model example
    • Flight model example
    • Apartment model example
  
  
• Week 9, Lab, October 20, 2010 - more SQL select statement clauses: order by, group by, and having
  • set-up-ex-tbls.sql
  • 315lab09-projected.sql - example SQL script projected in class
  • lab09-results.txt - results file spooled when 315lab09-projected.sql is executed
  • slightly different version of Week 9 lab material, in tagged PDF
  
  
• Week 9, Lecture, October 18, 2010 - Basics of Database Design, part 1
  • Week 9 Lecture projected notes
  • slightly different version of Week 9 lecture material, in tagged PDF
  • the Art Gallery example ER diagram (in tagged PDF)
  
  
• Week 8, Lecture, October 11, 2010 - intro to Normalization
  • Week 8 Lecture projected notes
  • Lecture example relation #1, riddled with opportunities for modification anomalies;
    Alternate pair of relations with fewer opportunities for modification anomalies (but not yet in 2NF or 3NF)
  • slightly different version of Week 8 lecture material, in tagged PDF
  
  
• Week 6, Lab, September 29, 2010 - nested selects/subselects, including use of exists and correlated subqueries; also concatenating columns and projecting literals
  • set-up-ex-tbls.sql
  • 315lab06-projected.sql - example SQL script projected in class
  • lab06-results.txt - results file spooled when 315lab06-projected.sql is executed
  • slightly different version of Week 6 lab material, in tagged PDF
  
  
• Week 6, Lecture, September 27, 2010 - Entity-Relationship Model (E-R Model), continued
  • Week 6 Lecture projected notes
  • Slightly-different version of Week 6 lecture material, in tagged PDF, which includes some example ER diagrams (including for the Union variant of supertypes/subtypes)
  
  
• Week 5, Lab, September 22, 2010 - SQL select continued: more options for the where-clause condition, column aliases, table aliases, computed columns, aggregate functions; also "interactive" queries using & and how that can relate to SQL injection
  • 315lab05-projected.sql - example SQL script projected in class
  • lab5-results.txt - results file spooled when 315lab05-projected.sql is executed
  • slightly different version of Week 5 lab material, in tagged PDF (does NOT include & and SQL injection material)
  • set-up-ex-tbls.sql
  
  
• Week 5, Lecture, September 20, 2010 - Introduction to the Entity-Relationship Model (E-R Model)
  • Week 5 Lecture projected notes
  • slightly different version of Week 5 lecture material, in tagged PDF
  
  
• Week 4, Lab, September 15, 2010 - Writing relational operations using the SQL select statement
  • set-up-ex-tbls.sql
  • 315lab04-projected.sql - example SQL script projected in class
  • 315lab04-results.txt - results file spooled when 315lab04-projected.sql is executed
  • slightly different version of Week 4 lab material, in tagged PDF
  
  
• Week 4, Lecture, September 13, 2010 Intro to the Relational Model and Relational Operations
  • Week 4 Lecture projected notes
  • Relational operation examples projected in class (in tagged PDF)
  • slightly different version of Week 4 lecture material, in tagged PDF (and also including the relational operation examples displayed in lecture)
  
  
• Week 3, Lab, September 8, 2010 - More database fundamentals; some discussion of how SQL Data Control Language (DCL) statements such as grant and revoke can be used as one step in helping improve data security
  • 315lab03-projected.txt - notes projected during lab
  • grant-test-postable.sql - version of grant example SQL script that does not include the actual class members' usernames...!
  • revoke-test-postable.sql - version of revoke example SQL script that does not include the actual class members' usernames...!
  • Reading Packet - slightly different version of Week 3 lab material, in tagged PDF; note that it does not include any discussion of grant or revoke
  
  
• Week 2, Lab, September 1, 2010 - Intro to SQL create table, drop table, insert, and the simplest version of select, along with a choice beginning selection of SQL*Plus commands to allow us to write and run SQL scripts
  • 315lab02-projected.txt - notes projected during lab
  • 315lab02.sql - example SQL script projected in class
  • 315lab02-out.txt - results file spooled when SQL script 315lab02.sql is executed
  • Reading Packet - slightly different version of Week 2 lab material, in tagged PDF
  
  
• Week 2, Lecture, August 28, 2010 - introduction to database processing and development
  • Week 2 Lecture projected notes
  • Reading Packet - slightly different version of Week 2 lecture material, in tagged PDF

Back to Course Home Page