CSC 742: Database Management Systems

Spring 2002

(This will be updated frequently. Check before class.)

Instructional objectives (after mid-term)

Use them as guidelines to prepare for the final exam.

Types of Questions in the Final

Grading for the demo 

There are totally 120 points for the demo, including 40 points for the ease of use of the program and 80 points for functionality & robustness. The ease of use points are subjective, which are decided by the instructor and the TA. The other 80 points are graded as follows. By default you get the 80 points. Each time you program fails, we remove 10 points; for each functionality your program cannot perform, we remove another 10 points. However, we will not remove more than 80 points.

Grading period starts on April 25, 2002, and ends on April 30, 2002. Those that are not graded by April 30 get zero point for demo. The students are responsible for contacting the instructor and the TA to arrange time and location for the demo. The best time for the instructor is during the office hours. 

Course Description

Database concepts. Database design. Data models: entity-relationship and relational. Data manipulation languages including SQL. Data Dictionaries. Query processing. Concurrency. Software development environments using a database system. Expert, object-oriented, multimedia and distributed database systems. Database systems architecture. Use of a commercial database management system.

Prerequisites

Specifically, the following background topics will be assumed: elementary set theory, relations, partial orders, functions, concept of a theorem, propositional logic, and predicate logic. Graduate students in computer science and computer engineering should know these topics well. However, these topics are covered in CSC 222: Applied Discrete Mathematics. You may review Sections 1.1 - 1.6, 3.1, 6.1, 6.2, 6.4 from the following book, which is sometimes used as the CSC 222 textbook:

K. H. Rosen, "Discrete Mathematics and its Applications", McGraw-Hill, 2nd edition, 1991.

Useful Resources

Instructor:

Dr. Peng Ning

 

Office hours:

Tuesdays and Thursdays, 3:00pm - 4:00pm, at 453 EGRC, Centennial Campus

Office phone:

(919) 513-4457

Fax:

(919)515-7925

Email address:

ning@csc.ncsu.edu

Homepage

http://www.csc.ncsu.edu/faculty/ning

Teaching assistant:

Pai Peng

 

Office hours:

 Tuesdays and Thursdays, 10:10-11:10am, Suite 110, room 102 Venture I, Centennial Campus.

Email address:

ppeng@eos.ncsu.edu

Class location:

Room 150 Venture II, Centennial Campus

Day and time:

Tuesdays and Thursdays 4:35pm -5:50pm.

Required text:

Fundamentals of Database Systems, by Ramez Elmasri and Sham Navathe, Addison-Wesley, 3rd edition (ISBN 0-8053-1755-4).

Reference texts:

 Database Management Systems, by Raghu Ramakrishnan and Johannes Gehrke, McGraw Hill, 2nd edition (ISBN 0-07-232206-3).
SQL for Smarties: Advanced SQL Programming by Joe Celko, Morgan Kaufmann, 1995. (ISBN 1-55860-323-9). 
Java Tutorial: JDBC Database Access

Mailing list:

csc742-001@wolfware.ncsu.edu

Web page:

http://courses.ncsu.edu/csc742/lec/001/

Message Board:

http://courses.ncsu.edu/csc742/ 

Assignments:

Eight homework assignments. You must use text editor (e.g. MS Word) to complete your homework. Handwritten submissions are not accepted. You should submit hard copies unless otherwise specified.

All work is to be performed individually unless otherwise specified. For the collaborative problems, you are encouraged to form teams of 1-3 members (of students in this class) to cooperate only on those problems. After discussing the problems, please write up your answers individually. Indicate the names of the other members in your team, if any.

You get no extra credit for working alone where collaboration is permitted.

Project:

 All students are required to complete a course project. Please see read the details. 

Grading:

+/- grades will be assigned. There will be a lot of work - please plan to spend about 11-13 hours for this course (outside of class) each week. The course project (updated, 1/25/02) is an important part of the course.

The final grade will be computed as follows: Assignments 5%, project 30%, midterm 30%, final 30%, Class participation 5%.
Self-Study Responsibilities:   Some of the topics are important but are either quite straightforward or not a main focus of this course. These topics are identified as self-study topics above. Your knowledge of them will be evaluated as appropriate through exams, homeworks, programming assignments, or the project.

You will be responsible for learning how to develop web-based interfaces for your project. You are encouraged to collaborate and share knowledge about interfaces.

Course Policies

Schedule of classes

Date

Topics

Reading Assignment

(Complete before class.)

Homework Assignment

Handout

1/8

 1. Introduction Chapter 1   pdf

1/10

 2. Concepts and architecture 
  • Traditional software systems
  • DBMS
  • Languages and interfaces
  • Conceptual modeling
 Chapter 2 Begin to form project teams pdf

1/15

 3. DB programming basics
  • DBMS: Sybase with JDBC frontend
  • Development environment
  • Application servers
4. Data modeling 
  • Entity-relationship approach
  • Enhanced entity-relationship approach
  HW1 (on topics 1, 2, and 3)

Due by 4:35pm 1/24/02.

Grade Distribution

Solution

pdf1

pdf2

1/17

 4. Data modeling (Cont'd) Chapter 3    

1/22

 No class      

1/24

 4. Data modeling (Cont'd) Chapter 4

Project Team Due

Each team hands in a piece of paper, listing the team members.

  
1/29 5. Relational model
  • Keys
  • Constraints
  • Operations
Chapters 7.1 - 7.3 HW2 (on topics 4 and 5)

Grade Distribution

Solution

Due by 4:35 pm 2/7/02.

1 per page,

2 per page,

6 per page
1/31 6. Database design
  • Design of relational schemas
  • EER to relational mapping
  • Information redundancy and anomalies 
    • Functional dependencies
    • Normalization
      • 1NF
      • 2NF
      • 3NF
      • BCNF
  • Design process (self-study for HW: sections 16.1, 16.2, 16.5)
Chapters 9.1, 9.2, 14.1   1 per page,

2 per page,

6 per page
2/5 6. Database design (Cont'd) Chapter 14 HW3 (on topic 6)

Grade Distribution

Solution

Due by 4:35 pm 2/19/02.

 
2/7 6. Database design (Cont'd)

7. Relational algebra

 Chapters 7.4, 7.5, 7.6   1 per page,

2 per page,

6 per page

 
2/12 Quiz 1 (15 minutes)

7. Relational algebra (Cont'd)

8. Relational calculus

 Chapter 9.3 Project report #1 T07 Supplement (1 per page, 2 per page, 6 per page)

T08 (1 per page, 2 per page, 6 per page)
2/14 8. Relational calculus (Cont'd)
9. System architecture and catalog ( self-study for HW: sections 17.1, 17.2, 17.4, 17.5) 		Chapters 17.1, 17.2, 17.4, 17.5. HW4 (on 7, 8, and 10)

test db1, test db2, test db3.

solution.

Due by 4:35 pm 2/26/02.

 
2/19 8. Relational calculus (Cont'd)

10. SQL 

  • Data definition (self-study for project: section 8.1)
  • Queries
  • Updates (Self-study)
  • Views 
  • Constraints
Chapters 8.1 - 8.7   T10 (1 per page, 2 per page, 6 per page)
2/21 10. SQL (Cont'd)      
2/26 Mid-term review      
2/28 Mid-term exam (in class) All the above. Grade Distribution  
3/5 Review of mid-term exam.
(Note: 3/8/02 is the last day to drop without a grade.) 		     
3/7 11. Security and authorization Chapters 22.1 - 22.3 HW5 (on 10, and 11)

Grade distribution

Solution

Due by 4:35 pm 3/19/02.

T11 (1 per page, 2 per page, 6 per page)
3/12 Spring Break      
3/14 Spring Break      
3/19 12. Transaction concepts
  • ACID properties
  • Schedules
  • Recoverability
  • Serializability
 Chapter 19   T12 (1 per page, 2 per page, 6 per page)
3/21 12. Transaction concepts (Cont'd)   Project report #2 due.  
3/26

13. Transaction programming (self-study)

  • Three tier architectures
  • Component programming
  • JDBC
14. Concurrency control
  • Locking
  • Timestamps
  • Optimistic techniques
  • Granularity
 Chapter 20 HW6 (on 12)

Solution

Grade Distribution

Due by 4:35 pm 4/4/02.

T14 (1 per page, 2 per page, 6 per page)
3/28 No class.      
4/2 14. Concurrency control (Cont'd)      
4/4 14. Concurrency control (Cont'd)  

 

   T14_TO (1 per page, 2 per page, 6 per page)
4/9

15. Recovery

  • Concepts
  • Deferred update
  • Immediate update
  • Shadow paging
  • Backup and recovery
  		Chapter 21 HW7 (on 14 and 15)

solution

Due by 4:35 pm 4/23/02.

T15 (1 per page, 2 per page, 6 per page)
4/11 15. Recovery (Cont'd)      
4/16 17. Advanced topics (or catch up)

Guest Lecture: What can agents do for peer-to-peer systems?

(Dr. Bin Yu)

    GuestLecture
4/18 No class      
4/23 16. Query processing and optimization
  • SQL to relational algebra 
  • Basic algorithms (self-study for HW: section 18.2)
  • Heuristics 
  • Semantic query optimization
 Chapters 18.1 - 18.3, 18.6 HW8 (on 16)

solution

Due by 4:35 pm 4/30/02.

T16 (1 per page, 2 per page, 6 per page)
4/25 Selected project demos   Project report #3 due.  
4/30 Review      
5/14 Final Exam (1:00 pm - 4:00pm)      

Acknowledgement

The course materials are based on those provided by Dr. Munindar Singh.

Peng Ning (ning@csc.ncsu.edu)