INFSCI 1022
Database Management Systems

(Spring 2008, CRN 19275)

04/30/08

I believe this is a final announcement:

• The grades for the course are posted.
• If you have any questions and/or any feedback, please, contact me via email.
• Have a great summer!

04/21/08

• The location for all demos is IS 708.

04/16/08

• Solutions and comments for the Homework 4 are posted here.

04/15/08

• Handouts for Lectures 25 and 26 are posted.
• Reminder: Project Reports are due this Thursday, 4/17/08.

04/09/08

• See some project-related F.A.Q. here.

04/08/08

• Lecture notes and materials for the Lecture 23 are posted.
• Reminder: the Homework 4 is due today.
• Reminder: schedule your project demo (see open time slots here).
• Reminder: any group is welcome to discuss their assumptions and ER Model for the project with me, as well as ask questions.
• Reminder: use Knowledge Tree to earn extra points and get ready for the final exam.

04/02/08

• Solutions and comments for the Homework 3 are posted here.

04/02/08

• Lecture notes and materials for the Lecture 22 are posted.

04/02/08. On future plans:

• On Thursday, 4/3/08, we'll finish Transactions (Chapter 15, the same handouts) and spend some time on techniques relevant to your projects (mainly ER Models and how to convert them into database tables).
• Homework 4 is due before the class on Tuesday, 4/8/08.
• Project reports are due before the class on Thursday, 4/17/08. Please review the guidelines for the project report. One report per group is required.
• The final exam is on Monday, 4/21/08, from 4 pm to 5:50 pm, in IS 405.
• Each group needs to schedule a 30-min. project demo with me. Remember that the entire group must be present at the demo: if the student is not present at the demo, this student receives zero for the project, resulting in a poor grade. The main demo date is Monday, 4/28/08. The time slots are listed at the bottom of the project page and will be distributed on a first come first serve basis (talk to me or send an email). If you need to have a demo earlier than that, discuss possible dates with your group and we'll see how it could be arranged.

03/29/08

• Homework 4 is posted here. Questions and requests for office hours are welcome.

03/27/08.

• Lecture notes and materials for the Lecture 21 are posted.

03/26/08.

1. Lecture notes for the Lecture 20 are posted.
2. The students, who made a wrong assumption that to create the tables and to insert data for the Homework 3 they don't need to test their statements in sqlplus, are given the second chance. A new versions of the statements (tested in sqlplus!) for the problems 2 and 3 of the Homework 3 can be submitted no later than before the class on Tuesday, April 1st. No credit will be given for a late submission.

03/23/08.

• Lecture notes and materials for the Lecture 18 and the Lecture 19 are posted.

03/17/08.

• Office Hours. Tomorrow, 3/18/08, 9:30-10:45, IS 708, walk-ins welcome.

03/06/08.

• Lecture notes for the Lecture 17 are posted.

03/04/08.

• Lecture notes and materials for the Lecture 16 are posted. For the practice session you can see two files: one with problems (handouts), and another one with problems and their solutions. It is a good idea to try to solve the problems on your own, then to compare them with solutions.

03/04/08.

• Homework 3 is posted here. It is due in two weeks, on Tuesday after the Spring Break, 3/18/08. I understand that some of you won't be able to work on your assignment during the Spring Break. I'll honor requests for extension received by 23:59 on Sunday, 3/9/08. I'll send you a confirmation that your request was received and how far the extension goes.

03/03/08.

• I haven't received any emails from students for over a week, and before that a few emails came to my yahoo account with delays. If you have tried to contact me via email, expected a response, but it never came, please repeat you message addressing it to valeriab36@gmail.com. It seems like a good idea to send future emails to the both addresses, just in case.

02/29/08.

• Solutions and comments for the Homework 2 are posted here.

02/28/08.

• Lecture notes and materials for the Lecture 14 and the Lecture 15 are posted.

To make the main course page shorter, all old annoucements have been moved here.

• Data abstraction; external, conceptual, and internal schemata; data independence; data definition and data manipulation languages.
• Relational data models, relational query languages, relational algebra, SQL.
• Database design, entity-relationship model, functional dependencies; introduction to normalization theory.
• Query processing, database catalog system, algebraic manipulation; optimization of selections and joins.
• Storage strategies and indices.
• Introduction to transaction management, concurrency control and recovery.

Chapter 1. Introduction.

Chapter 2. Relational Model. Last Semester Slides

Chapter 3. SQL. Last Semester Slides, Part 1 and Part 2

Chapter 6. Entity-Relationship Model. Last Semester Slides

Chapter 7. Relational Database Design. Last Semester Slides

Chapter 11. Storage and File Structure. Last Semester Slides

Chapter 12. Indexing and Hashing. Last Semester Slides

Chapter 15. Transactions. Last Semester Slides

Chapter 16. Concurrency Control. Last Semester Slides

Chapter 17. Recovery System. Last Semester Slides

Knowledge Tree - Link - is a set of interactive tools developed to help you learn Relational Algebra and SQL. Use the login/password sent to you via email by Knowledge Tree administrator. If you haven't received the email from the Knowledge Tree administrator, contact your instructor.

Knowledge Tree tutorial is posted here (.pdf)

Your grade for the course will be combined from the grades for the homeworks (30%), the project (30%) and the final exam (40%).

The score is translated into letter grades as following:

• Less than 50% is F.
• Greater than or equal to 50% and less than 62.5% is D range.
• Greater than or equal to 62.5% and less than 75% is C range.
• Greater than or equal to 75% and less than 87.5% is B range
• Greater than or equal to 87.5% is A range.
• The lower and upper parts of each range correspond to - and + modifiers.

Homework is always due before the class. If you can't come to the class, you can put the completed homework to the instructor's mailbox at IS 701 as early as you like, but before the class.

Homework 1 is posted here.

Below are the questions I've received by email and my answers to them.

1. Q. On Problem 4, the last part says, "List all students who have taken both 1022 and 1017 courses". By "students", do you mean you want the student_id's or last names and first names?
   A. The student_id's will be sufficient.
2. Q. Could you tell us whether the solution for Problem 4 number 4 should use a natural join?
   A. Actually, Problem 4 number 4 could be solved with a natural join or without a natural join. I think that a solution without a natural join is more natural for this problem, but you may solve it either way.

Solutions and comments for the Homework 1 are posted here. Please, don't be alarmed if your solution is different from the one suggested there: if your answer solves the given problem, it gets full points, unless some additional requirements were stated in the original problem. The corrected and graded homework papers will be distributed in class on 2/5/08.

Homework 2 is posted here.

To create test data for this homework:

1. Log in your unix account.
2. Run the command that copies a file (hw2data.sql) from another user account (../ug39/) to your account (.):
   cp ../ug39/hw2data.sql .
   (Notice that there is a space between the file name and the dot. The dot means "copy it here, to my account")
3. Log in your sqlplus account.
4. Run the copied script:
   @hw2data
5. You should get a feedback that tables are created and rows are inserted.

As an alternative, you can use the same script from here.

Below are the questions I've received by email and my answers to them.

1. Q. On Problem 4, do we need to join four tables?
   A. No, there is no need to join four tables to solve this problem. However, it seems that I should clarify that no item appears in any catalog more than once.
2. Q.Where clause
   where catalog_name="BackToSchool"
   causes and error. Why?
   A. Oracle expects us to use single quotes around character strings:
   where catalog_name='BackToSchool'

Solutions and comments for the Homework 2 are posted here. The corrected and graded homework papers were distributed in class on 2/28/08.

Homework 3 is posted here.

Below are the questions I've received by email and my answers to them.

1. Q.When you say an accident had "driver #3 on car #3", does that mean driver #3 was driving car #3?
   A. Yes.

Solutions and comments for the Homework 3 are posted here.

Homework 4 is posted here.

Solutions and comments for the Homework 4 are posted here. The corrected and graded homework papers were distributed in class on 4/15/08.

Detailed project description is posted here.

The first steps to the successful completion of the project are

• To read the information and prepare questions we can discuss at the next lecture;
• To find two more students you want to work with;
• To choose what application you want to implement;
• To mail a list of three students and a choice of an application to the instructor by the "Project Group Lists Due" date, 2/07/08.

To make the main course page shorter, Lecture Notes for the Lectures 1 through 4 (Introduction and Relational Algebra) have been moved here.

Unix Lab, 01/22/08.

The presentation given today is here.

Additional slides on spool, ftp and printing are here.

Lecture 5, 01/24/08. Chapter 3. SQL. Part 1.

After this lecture, you should be able to:

1. Understand the basic CREATE TABLE statement, point out the table name, the list of column names and data types for each column. Write a basic CREATE TABLE statement for your own table, following the given example.

2. List three major types of data (character data, numeric data and date/time data) and explain Oracle data types for the character and the numeric data.

3. Understand the basic INSERT INTO ... VALUES (...) statement that provides a value for each column. Write a similar INSERT statement for your own table and data, following the given example.

4. Write a statement for table deletion.

5. Explain that results of a SQL query is a table that is shown on the screen (or used in some other way), but not preserved in the database, unlike the tables created by CREATE TABLE statement and populated by INSERT INTO statements.

6. Know an order of clauses in SQL query (SELECT, then FROM, then WHERE, then ORDER BY - for now).

7. Understand and master a variety of SQL queries for a single table:

• Pick one column from a table.
• Pick more than one column from a table.
• Pick columns from a table in different order.
• Pick columns under different names.
• Give an instruction to remove duplicates, to keep duplicates. Know which one is a default.
• SELECT * FROM table query.
• Arithmetic operations in SELECT clause. +, -, *, /. Use numbers, use multiple columns in one expression. Show examples.
• ORDER BY clause to order rows. By one column, by more than one column. Show examples. Explain the meaning of DESC and ASC. Specify which one is a default.
• WHERE clause to select some of the rows. Use >, <, =, >=, <=, <> (not equal), AND, OR, NOT, BETWEEN ... AND ..., NOT BETWEEN ... AND ....

We are at the point when the more you practice with various queries, the better you know the material. You can try out the queries from the handouts, invent your own, and/or practice with Knowledge Tree. Knowledge Tree not only shows some queries with explanations, it also provides a tool where you have a task, write SQL statement to solve it and, the best of all, your answer gets verified. The students who used Knowledge Tree in the past, reported that it helped them significantly in mastering necessary skills.

The theory, examples and explanations could be found in the Chapter 3 of the Course Book. If you feel that you need an additional help, contact the instructor.

Lecture 6, 01/29/08. Chapter 3. SQL. Part 2.

After this lecture, you should be able to:

1. Explain and demonstrate how aggregate functions work on an entire table. Show examples for SUM, MIN, MAX, AVG and COUNT. Explain, why we put star (*) in COUNT(*). Explain how the word "distinct" inside the parentheses affects the result.

2. Explain and demonstrate how aggregate functions work with GROUP BY clause. Show the process step-by-step.

3. Add WHERE clause and ORDER BY clause. Explain in what order WHERE clause, GROUP by, aggregate function(s), and ORDER BY will be executed.

4. Have a clear idea how many rows the particular query on particular table will produce.

5. Have a clear idea what could and what couldn't be listed as a column in SELECT clause, when aggregate functions and/or GROUP BY present.

6. Understand HAVING clause. Explain and demonstrate when it will be executed in a statement with other clauses.

7. Apply all of above to solve tasks. We will practice this in class, but to master the skills you need to practice on your own. You can try to recreate statements from the handouts, invent your own, do exercises from the book, work in pairs and groups to check each other's work, and, of course, practice with Knowledge Tree. You can bring interesting tasks and/or questions to the class, though it would be helpful if you send them to the instructor before the class.

The theory, examples and explanations could be found in the Chapter 3 of the Course Book. If you feel that you need an additional help, contact the instructor.

Lecture 7, 01/31/08. Chapter 3. SQL. Part 3.

After this lecture, you should be able to:

1. Apply a general approach to write SQL statements with aggregate functions, GROUP BY and HAVING clauses. Most of the examples we solved in class are here.

2. Understand when the condition(s) from the task goes into WHERE clause (when we can check it for each row before aggregation), and when - into HAVING clause (when we have to do aggregation first, and then check the condition(s)).

3. Create data for the first versions of tables for your project or for your experiments. We learned:

• To use unix comand cat to show the content of the file.
• To run sql script (a file with sql statements) in sqlplus, using @ filename.
• To use describe command in sqlplus to see table structure.
• To drop a table before it's re-creation.
• How to write an insert statement that includes some (not all) columns of a table.
• How to write an update statement for the entire table (without WHERE clause) and for some rows (with WHERE clause).

4. Have a general idea about NULLs ("unknown" data) - this term was introduced, but not yet properly covered.

A listing of the demo is posted here.

There was an additional demo of KnowledgeTree system, specifically QuizGuide.

The theory, examples and explanations could be found in the Chapter 3 of the Course Book. If you feel that you need an additional help, contact the instructor.

Lecture 8, 02/05/08. Chapter 3. SQL. Part 4.

The three set operations (union, intersect and except/minus), nested subqueries and joins were covered. Then a problem of unknown data and NULL values was addressed. The lecture followed the handouts closely.

The theory, examples and explanations could be found in the Chapter 3 of the Course Book. If you feel that you need an additional help, contact the instructor.

Lecture 9, 02/07/08. Chapter 6. Entity-Relashionship Model. Theory.

After this lecture, you should be able to:

1. Understand the place of an Entity-Relashionship Model in the database design process.

2. Understand what it means and know how to express (draw) in your model:

• Entity sets
• Entity attributes
• Relationships
• Primary keys
• Simple and composite attributes
• Single-valued and multivalued attributes
• Derived attributes
• Descriptive attributes (attributes on relationships)
• Roles
• Mapping cardinalities (one-to-one, one-to-many, many-to-one, many-to-many)
• Partial and total participation
• Strong and weak entity sets
• Descriminator of a weak entity set
• Specialization/Generalization: condition- or user-defined, disjoint or overlapping, total or partial

3. Have some ideas how to translate Entity-Relashionship Model into database design. The lecture handouts/slides are posted here.

The theory, examples and explanations could be also found in the Chapter 6 of the Course Book. If you feel that you need an additional help, contact the instructor.

Lecture 10, 02/12/08. Chapter 3. SQL. Part 5.

After this lecture, you should be able to:

1. Understand the structure of the CREATE TABLE statement with column definitions and constraint definitions.

2. Understand and use:

• NOT NULL constraint, both unnamed and named,
• DEFAULT feature,
• CHECK constraint,
• UNIQUE constraint and why we need it at all,
• PRIMARY KEYconstraint

3. Understand and use ALTER TABLE statement to add columns and constraints.

4. Understand and use DELETE statements.

5. Perform multiple column inserts with nested subquery.

The lecture handouts are posted here.

We also devoted some time to practice solving sql problems that could be found here.

The theory, examples and explanations could be also found in the Chapter 3 of the Course Book. If you feel that you need an additional help, contact the instructor.

Lecture 11, 02/14/08. Chapter 3. SQL. Part 6.

The lecture handouts are posted here. They list statements, that were explained and demonstrated, and problems that were solved, explained and/or demonstrated at this lecture.

I urge everyone at this point to work with KnowledgeTree to practice your skills and assess your knowledge.

The theory, examples and explanations could be also found in the Chapter 3 of the Course Book. If you feel that you need an additional help, contact the instructor.

Lecture 12, 02/19/08. Chapter 6. Entity-Relashionship Model. Practice.

We took a set of requirements for a pharmacy application (this one), built a rough draft of an Entity-Relationship model for this application, discussed choices to make and questions to ask, then defined (created) tables for some of the entities, some of the relationships, and for the multivalued attributes.

After this lecture you should be able to prepare an E-R model and create a set of tables for your project.

The theory, examples and explanations could be also found in the Chapter 6 of the Course Book. If you feel that you need an additional help, contact the instructor.

Lecture 13, 02/21/08. Chapter 3. SQL. Part 7.

The lecture handouts are posted here.

After this lecture you should be able to:

1. Understand primary key and foreign key constraints and their role in referential integrity.

2. Create and delete these constraints.

3. Update and delete rows without integrity violations.

4. Work with strings:

• Use LIKE predicate with pattern matching characters;
• Use concatenation.

5. Use line editor in sqlplus:

• Command to make a particular line current;
• Command to find/replace a part of the line

The theory, examples and explanations could be found in the Chapters 3 and 4 of the Course Book. If you feel that you need an additional help, contact the instructor.

Lecture 14, 02/26/08. Chapter 7. Relational Database Design. Part 1

The lecture handouts are posted here.

After this lecture you should be able to:

1. Explain what problems we are trying to solve by a good relational design.

2. List the main goals of a good relational design.

3. Define a First Normal Form, provide examples.

4. Explain a lossless join decomposition and how to check for it.

5. Describe functional dependencies.

6. Define a closure of a set of functional dependencies, list Armstrong's axioms, give examples.

The theory, examples and explanations could be found in the Chapter 7 of the Course Book. If you feel that you need an additional help, contact the instructor.

Lecture 15, 02/28/08. Chapter 7. Relational Database Design. Part 2

The lecture handouts are posted here.

After this lecture you should be able to:

1. Explain a process of normalization using functional dependencies.

2. Distinguish between dependency preserving decomposition and decomposition which doesn't preserve dependencies. Explain drawbacks.

3. Define a Boyce-Codd Normal Form.

4. Explain how to check a relation for BCNF.

5. Understand decomposition to the point when all relations are in BCNF.

We haven't finished the Part 2, we'll continue at the next lecture.

The theory, examples and explanations could be found in the Chapter 7 of the Course Book. If you feel that you need an additional help, contact the instructor.

Lecture 16, 03/04/08. Chapter 7. Relational Database Design. Part 2+Practice

We have finished the Relational Database Design. Part 2 (the same handouts as the previous lecture, they are posted here). Then, we worked through the list of problems to apply what we learned in Chapter 7. The problems are here, the same problems with solutions and some explanations are here.

After this lecture you should be able to:

1. Define a Third Normal Form (3NF).

2. Explain how to check a relation for 3NF.

3. Explain why we may want to stop decomposition BEFORE all relations are in BCNF, and what are the advantages and the problems of 3NF.

4. Solve the problems similar to the practice problems.

The theory, examples and explanations could be found in the Chapter 7 of the Course Book. If you feel that you need an additional help, contact the instructor.

Lecture 17, 03/06/08. Chapter 11. Storage and File Structure.

Lecture handouts are posted here).

After this lecture you should be able to:

1. List and explain storage media classification parameters: speed, cost, reliability and volatility.

2. List, characterize and compare current storage media options.

3. Explain which storage options we use to store databases.

4. Show and explain magnetic disk functionality, using proper terminology. Explain access time, and what it consists of.

5. Explain functions of a disk controller.

6. Explain why the data needs to be moved to and from disk and where it is moved.

7. Define block. Define buffer.

8. List functions of a buffer manager.

9. List and explain replacement strategies buffer manager might use.

The theory, examples and explanations could be found in the Chapter 11 of the Course Book. If you feel that you need an additional help, contact the instructor.

Lecture 18, 03/18/08. Chapter 3. Part 8. More SQL: views, derived relations, some, all, exists - and more practice.

To learn more SQL, we were solving the following problems: without solutions, with solutions).

After this lecture and additional practice on similar problems you should be able to:

1. Use words some, all and exists when needed.

2. Solve complex problems with subqueries in where clause, having clause, as well as in from clause (derived relation).

3. Create and use views.

The theory, examples and explanations could be found in the Chapter 3 of the Course Book. If you feel that you need an additional help, contact the instructor.

Lecture 19, 03/20/08. Chapter 12. Indexing and Hashing. Hands-on introduction

After this lecture you should be able to:

1. Understand why and when indices (indexes) are useful.

2. Distinguish dense index from sparse index.

3. Distinguish primary (clustered, clustering) index from secondary index.

4. Create index manually (without SQL); search table with index.

5. Explain and use multi-level index.

The theory, examples and explanations could be found in the Chapter 3 of the Course Book. If you feel that you need an additional help, contact the instructor.

Lecture 20, 03/25/08.

1. We finished the theme of the Lecture 18 (see handouts and solutions posted for that lecture).

2. We discussed an ER Model for Homework 3.

3. We reviewed what we know about indexes (indices) so far.

Lecture 21, 03/27/08. Chapter 12. Indexing and Hashing.

Lecture handouts about B-trees are posted here.

Slides for the entire chapter are posted here

After this lecture you should be able to:

1. Explain how hashing technique works, give examples.

2. Define B-tree indexes. Draw B-tree for different n. Note: we drew B-tree up to 7 for n=3, and B-tree up to 9 for n=6.

The theory, examples and explanations could be found in the Chapter 12 of the Course Book. If you feel that you need an additional help, contact the instructor.

Lecture 22, 04/01/08. Chapter 15. Transactions. Part 1.

Lecture handouts are posted here (both Part 1 and Part 2).

After this lecture you should be able to:

1. Define transaction. List and explain ACID properties.

2. Define transaction schedule. Define serial schedule.

3. Explain the reasons for concurrent execution. Explain possible problems.

4. Define conflict serializable schedule. Give example.

5. For a given schedule show that it is / isn't conflict serializable.

The theory, examples and explanations could be found in the Chapter 15 of the Course Book. If you feel that you need an additional help, contact the instructor.

Lecture 23, 04/03/08. Chapter 15. Transactions. Part 2.

Lecture handouts are posted here (the same as Lecture 22 handouts).

After this lecture you should be able to:

1. Define recoverable schedule. Give example.

2. For a given schedule show that it is / isn't recoverable.

3. Explain cascading rollback. Give example.

4. Define cascadeless schedule. Explain one way to achieve it.

5. Compare concurrency control with serializability tests.

6. Give an example of a situation when weak level of consistency is acceptable.

7. Explain what commit and rollback statements do in SQL.

The theory, examples and explanations could be found in the Chapter 15 of the Course Book. If you feel that you need an additional help, contact the instructor.

Lecture 24, 04/08/08. Chapter 16. Concurrency Control. Hand-on part.

This lecture was devoted to hand-on exercises with transactions and an introduction of some terms and problems. The theory will be presented at the next lecture.

Lecture 25, 04/10/08. Chapter 16. Concurrency Control. Theory.

Lecture handouts are posted here.

Lecture 26, 04/15/08. Chapter 17. Recovery. Theory.

Lecture handouts (for this and the beginning of the next lecture) are posted here.

Lecture 27, 04/17/08. Chapter 17. Recovery. Theory. Chapter 15-17. Practice

We will go over a few last slides of Chapter 17 (see previous lecture) and then work on the problems posted here with solutions.