[Main Page]
[Grades DB]
[Literature]
[Software]
[People]
[Conferences]
[Courses]
[Web-DB]
UNIVERSITY OF PITTSBURGH
School of Information Sciences
INFSCI 2710 - Database Management
(Spring 2000)
I passed the first four exams to become an Oracle8 Certified DBA
(there is one more required):
More Information.
The grades are in the database now:
Check your grade.
Please answer the questionaire which can be reached from your
grades page.
There is now a long (28 pages) solution for the final exam,
which includes a discussion of errors made:
[Postscript]
[PDF].
Please read it.
The discussion of exams often helps to clarify some remaining
issues.
You can fetch your exam from Marian Grant (Room 732).
A student recommended the
Online SQL Tutorial written by Richard Holowczak.
Another student recommended the
Online SQL Tutorial written by James Hoffman.
Pro*C and some other programs
(e.g. the Oracle Enterprise Manager)
are contained on the CD
"Oracle8 Client Version 8.0.4 for Windows 95/NT"
which is available from the Software Licensing Department.
There is a database of students with database background
looking for jobs, managed by the ACM SIGMOD.
You might want to post your data:
http://www.acm.org/sigmod/dbgrads.
A student also recommended
http://www.orafaq.org.
Maybe you should also become member in the
Oracle Technology Network.
Syllabus
- Course Description:
-
Purpose and development of database systems,
data modeling, including entity-relationship model,
relational data model,
normalization,
structured query language (SQL),
transaction management,
object-oriented databases,
and basics of physical database design and query evaluation.
(Prerequesite: INFSCI 2610)
- Class:
-
Wednesdays, 6:00 PM - 8:50 PM,
169 Crawford Hall
(Please check the room again, it is sometimes changed)
- Instructor:
-
Stefan Brass
(sbrass@sis.pitt.edu)
- Address:
-
University of Pittsburgh
School of Information Sciences /
Dept. of Inf. Sci. and Telecommunications (DIST)
135 N. Bellefield Ave.
Pittsburgh, PA 15260, USA
- Office:
-
Room 726, IS Building
Office Hours: Mondays, 5:00-7:00 PM (and by appointment)
- Phone:
-
(412) 624-9404
- GSA:
-
Siripun Sanguansintukul
(siripun@lis.pitt.edu)
Office: B 203, IS Building, 2nd Floor
Office Hours: Wednesdays, 4:00-5:45 PM
- Textbook:
-
Ramez Elmasri /
Shamkant B. Navathe:
Fundamentals of Database Systems, Third Edition
Addison Wesley, 1999,
ISBN 0-8053-1755-4 (Hardcover), 960 pages.
[Amazon.Com Page]
[Price Comparison]
[BIGWORDS.com (B-2BG4FD)]
There will also be extensive class handouts.
But it is very important for your success in this course that you
read a textbook in addition.
Optional/Recommended books
(in addition to the official textbook):
Rajshekhar Sunderraman:
Oracle Programming.
Addison-Wesley,
1999, ISBN 0-201-61258-5, Softcover, 304 pages,
ca. $29.00.
[Page of the Author]
[Amazon.com Page]
[Price Comparison]
- Prerequisites:
-
For my version of INFSCI 2710,
the data structures course INFSCI 2610 is not a strict prerequisite.
However, you need programming knowledge,
which you can e.g. get by taking INFSCI 2610.
Programming will not be the main focus of this course,
but there might be one homework in C with embedded SQL,
and one homework in Oracle's language PL/SQL
(or similar application programming assignments).
For the C program, there will probably be an alternative,
but I expect that you are able to learn PL/SQL
(a Pascal-like language) in this course.
Programming knowledge will also help you understand
the standard database language SQL better,
which is a main topic in this course.
- Grading:
-
There will be homeworks (more or less each week),
a midterm exam, and a final exam.
The homeworks can be solved in small groups.
Some of the homeworks may build on each other to create a kind
of small project.
The exams will be "open book"
(but because of the limited time,
books are only useful if you have read them before the exam).
The homeworks will earn you 35% of the points,
the better of both exams will be weighted 40% and the worse 25%.
If you reach 90% of the total points,
you get an A grade (or A+ if above 99.0%),
85% give you at least an A- grade, 80% a B+ grade,
75% a B grade,
and 67% a B- grade.
I reserve the right to change the rules in ways which improve the grade.
I.e. these formulas give only a lower limit for your grade.
Since some students have asked for a curve,
I also guarantee that at least the best 20% of the participants get
an A grade, the next 15% will get an A- (or better),
and the next 20% will get a B+ (or better).
The statistics of previous courses have been significantly better,
but the students said that they had to work more than in other courses.
Course Schedule (Plan, Subject to Changes)
Wednesdays, 6:00-8:50, in Crawford Hall, Room 169
There will be no class on March 8
due to the Spring Recess.
- January 5: Introduction
(Functions of Database Management Systems)
Functions of DBMS, Schema and Instance, Data Models,
Data Independence, Three Schema Architecture,
Transaction Concept.
- January 12: SQL Basics, Entity-Relationship-Model I
Repetition of Functions of Database Management Systems.
Tools, Users, Applications.
First Introduction to the Relational Model and SQL,
Demonstration of SQL*Plus,
Basic Database Design Concepts,
Three Schema Design Phases,
ER-Model Elements (Entities, Relationships, Attributes).
- January 19: ER-Model I (Basics),
ER-Model II (Constraints)
Repetition of ER-Model Elements.
Style and Quality of ER-Schemas,
Constraints in Database Design,
Keys.
- January 26: ER-Model II (Constraints),
ER-Model III (Advanced Constructs)
Repetition of Keys.
Cardinality Constraints.
Advanced Constructs of the ER-Model (Weak Entities,
Optional Attributes).
- February 2: ER-Model III, Relational Data Model I
Advanced Constructs of the ER-Model (Structured and Multivalued
Attributes, Specialization).
Some General remarks about Database Design.
Relational Data Model.
- February 9: Relational Model I, Relational Model II
Relational Algebra.
- February 16: Relational Model III
Null Values, Keys, Foreign Keys, CHECK constraints.
Translation form the Entity-Relationship-Model
into the Relational Model.
- Februray 23: *** MIDTERM EXAM ***
Exercises could be, e.g., of the following types:
- Create an ER-schema for a given application
(with keys and cardinalities and possibly further constraints
in natural language)
- Formulate Queries in relational algebra
- Simple SQL queries (not more complicated than selection, projection, join)
- Given an ER-schema, what are the mistakes?
- Given two ER-schemas, what are the differences.
- Given some relational algebra queries,
which of them are syntactically not correct?
- Given pairs of relational algebra queries,
which are equivalent?
- ... (?)
- Notes about Midterm Exam
[Postscript]
[PDF]
- March 1: Repetition, Relational Model III
Discussion of Midterm,
Translation form the Entity-Relationship-Model
into the Relational Model.
- March 15: Relational Model III, SQL I
Basics of Relational Database Design (Functional Dependencies, BCNF).
Repetition of Syntax Formalisms
(especially Syntax Graphs),
History of SQL,
SQL Syntax,
Tuple Variables.
- March 22: SQL I, SQL II
Query Formulation, Self-Joins, Common Errors,
Conditions in SQL, Null Values, Aggregations.
- March 29: SQL II, SQL III
Subqueries, UNION, Order By, SQL-92 Joins.
SQL Data Types, CREATE TABLE.
- April 5: SQL III, Security, Views
CREATE TABLE, SQL Update Commands, COMMIT, ROLLBACK.
Security, Access Rights, SQL GRANT command,
Views.
- April 12: Views, Data Dictionaries, Physical DB Design
Views, Data Dictionaries, Basics of Physical Database Design.
- April 19: Concurrency Control,
Application Programming I
Concurrency Control,
Serializability,
Locks,
2 Phase Locking,
Deadlocks,
Protection Levels,
2 Phase Commit.
Embedded SQL (Oracle Pro*C), PL/SQL.
- April 26: Application Programming II, FINAL EXAM
Web Basics, Design Decisions for Query Interfaces,
Three-Tier Architecture,
CGI programming, Security, Database Access for CGI Programs,
Oracle Application Server.
Exercises in the final exam could be, e.g, of the following types:
- Translation of a given ER-schema into the Relational Model.
- Given a table and FDs, is this table in BCNF?
- Advanced SQL queries,
including aggregations, subqueries,
and the need to use two tuple variables on the same relation.
Please make sure that you "speak SQL fluently",
probably 50% or more of the points will be devoted to SQL.
- There may be queries using the data dictionary tables
as an example.
Of course, the needed tables with their columns will be mentioned in the exam.
- SQL Create Table with Constraints.
- A view definition and/or GRANT command.
- Determine whether a view is updatable.
- Determine the critical updates for a given constraint.
- ... (?)
Course Materials
Postscript files can be printed with
Aladdin Ghostscript
or with lpr on our laser printers.
Syllabus:
Transparencies in Postscript Format:
Homework Assignments:
SQL Puzzles:
Examples for Previous Exams:
Examples:
Oracle Documentation (only local)
Oracle Developer/2000 Quick Tour (only local)
Homework Results
Personal Oracle
Information Sources in the Web
Stefan Brass
(sbrass@sis.pitt.edu),
January 28, 2000
Original URL:
http://www2.sis.pitt.edu/~sbrass/db00_2/
[HTML 3.2 Checked]