CECS-323: Database Fundamentals

This site contains information about the activities carried out in the database fundamentals class, CECS-323, during Spring-2008. Here you'll find the most recent activities in each of the different categories and you'll also find announcements. You cand read the class syllabus (PDF) to get more administrative details about the class for Spring-2008.

Resources

  1. Download and install the trial version of MS Visio 2007.
  2. You may use MS Visio using the CECS Terminal Server, it is running on port 3976 at IP address 134.139.249.89. You can use the Remote Desktop Connection to connect to the server, this is an application available for download from Microsoft. This Microsoft page should give you the information needed to install and use it.
  3. Description of database projects assigned in previous semesters.
  4. The Apache Derby Reference Manual. You can also download it as a PDF or as a single HTML file. You may also want to view a complete list of all the documentation manuals.
  5. To install Eclipse + Derby on your home computer follow these instructions.
  6. The ToyBank Database.
  7. Handout of relational database query operations in SQL and in Relational Algebra (PDF). The handout covers all the operations we will study except for outer joins, see the handout below for more information on outer joins.
  8. Handout on determining the result set of different join operations.
  9. Additional notes on Outer Joins.
  10. Notes on B+-tree indexes.
  11. Using MySQL in the CECS Department.

Readings and Lectures

Following is a brief description of the concepts introduced during each of the fifteen weeks of the course.

  1. Brief historical background on Relational Model and ER Model. Data vs. Information and the knowledge hierarchy. UML class notation to model an entity set. Week 1 Lecture notes (PDF) -- all lecture notes are password protected
  2. The relational model: scheme, relation, tuple, keys. Query operations: selection and projection in relational algebra (RA) and SQL. Relevant sections on Jewett's site: rows and tables, and basics queries in SQL and RA. Dr. Monge's Lecture notes on basics of the relational model and queries (PDF). Relationships: modeling them with UML associations, relationship constraints, modelling relationships in the relational model, Integrity Constraints: primary key, uniqueness, and foreign key constraints of relations. A relational database with tuples related via the foreign key and primary key values. Relevant sections on Jewett's site: associations, and DDL and DML. Dr. Monge's lecture notes on modeling relationships
  3. Continued discussion of relationships as represented in UML and in the relational model. SQL and Relational Algebra. Retrieving information from multiple relations using the join operation. Also, discussion of queries involving all operands: selection, projection, and join operations (in RA and SQL). Relevant sections on Jewett's site: Joins, Many-to-many, and Keys.
  4. More on 1-to-1, 1-to-many, and many-to-many relationships. Introduction of surrogate PK and substitute PK attributes. Retrieving information from two or more relations using joins in RA and SQL. Implementing relationship attributes by UML association classes. Many-to-many, Multiple Joins. You may also want to download a Derby database script to create the Students/Accounts/Majors relational database and also the sample SQL queries on this database
  5. Self joins, aggregate functions (e.g. SUM, COUNT, AVG, etc.), many-to-many with history (e.g.: library loan). Relevant sections on Jewett's site: Many-to-many 2 and SQL aggregate functions. You may download the lecture notes showing: design of model for project and timecards, and also SQL Group By and Aggregate Function -- These notes include questions from previous quizzes!! See also Chapter 6 of the SQL: Visual Quickstart Guide
  6. Self-relationships (married-to and manages relationships), aggregate functions and group-by-having queries. Lecture Notes (text): These are notes from the lecture on modeling self-relationships and on self-joins.
  7. Repeated Attributes and Multivalued Attributes. Query set operations: set union, difference, and intersection. Outer Joins: here is a web page I created explaining outer joins and also a PDF file showing the result of several join operations on two simple tables. See also Chapter 7 (section 8) of the SQL: Visual Quickstart Guide
  8. Continue Outer Joins. Midterm on Thursday of this week.
  9. Subqueries. See Jewett's site on subqueries and also my class notes on subqueries (PDF). See also Chapter 8 of the SQL: Visual Quickstart Guide. Sample subqueries from lecture.
  10. Subqueries continued. Transactions Demo. Domains and Enumerated Domains. Subclasses and Aggregation
  11. Subkeys and Normalization: Redundancy, update anomalies, Funtional Dependencies. Lecture notes on Normalization.
  12. Normal Forms: BCNF, 3NF.
  13. Complete discussion of BCNF and 3NF normal forms. Indexes: Notes on B+-tree indexes.
  14. Indexes. Project presentations.
  15. [Week of May 13]Indexes. Database Views. Transactions

Activities

Date Activity Notes
Tue May 13 Quiz #3: Normalization. Taken in lab.
Tue April 22 Quiz #2: SQL subqueries and modeling using specialization, aggregation, enumerated domains. Prepare by trying the practice problems on subqueries. Taken in lab! It's a written quiz, you will not use the computers.
March 27 Database Project for the rest of the semester. Students will be assigned to be groups. Students should send e-mail by Wednesday 5pm with feedback on the student partners they've had for the assignment. This feedback will be used to determine group memberships. Each student is required to bring to lab printed copies of two ideas for a database project. You will discuss these ideas and determine the group project.

Database Resources