The Principle of Database System (CS 022)

 

Instructor :

Fang Li 

Room 3-533, SEIEE Building

Office time: Tuesday 13.30~14.30

 

Teaching Assistant:

Hao Xu

Lecture Time and Venue

Time: Tuesday: 8.00AM ~ 9.40 AM (from the 1st week to 8th week)

Thursday: 10.00 AM ~ 11.40 AM (from the 1st week to 16th week)

Place: 东上院107Building: Dong Shang Yuan   Room:107     

 

TextBook: a first course in database systems (third edition) written by Jeffrey D.Ullman, Jennifer Widom, from Stanford University

The English reprint edition (英文影印版) can be bought through China-Pub  or from other book stores.

Reference books:

Database System Concepts  by Abraham Silberschatz, et al (机械工业出版社)  (Sixth edition) from Yale University

 

Introduction:  The lecture will introduce some basic concepts, components and features of database systems. It is an introduction to the design and use of database systems. Many models and query languages will also discussed. Entity-relationship data model, relational data model, semi-structure model and object-oriented data models will be learned in the lecture. Query languages include relational algebra, SQL (Structured Query Language) and Datalog. The aim of this lecture is to know the overview of database systems, to learn how to design many database applications and use SQL in different environments.

 

Course Topics and Schedule

Week

Topics

Slides

Readings & Homework

1

Introduction to the course and database system

Intro.pdf, chapter 1

Chapter2-relational model

Textbook 1.2

2

Introduction to the relational model

Relational algebra

Chapter2-algebra

Chapter3-functionalDependecy

Exercise 2.3.1 Exercise 2.4.1 a,f,h

Exercise 2.5.1 b  or

Chapter 2 Homework

3

Normal forms

Multivalued dependency & reasoning. 

Chapter3-NormForm

Chapter3-MultivaluedDependcy

Exercise 3.2.1 Exercise 3.5.2

Exercise 3.6.3 a,c   or

Chapter 3 homework

4

ER Model

UML/ODL

Chapter4-ERModel

Chapter4-UML/ODL

Classroom exercises:

Exercise 4.2.1  Exercise 4.4.1

Exercise 4.4.2

Textbook 4.6.1 and 4.6.2

 

5

A logic language: datalog

SQL: introduction and query

Chapter5-LogicQueryLanguage

 

Chapter6-BasicQueryLanguage

Exercise 5.3.1 a,f,h 

Exercise 5.4.1 g  or

Chapter 5 homework

6

Subqueries & aggregations

JoinOperation & Modification

Chapter6-SubqueryAggregations

Chapter6-Join&Modification

Classroom Exercises for Chapter 6:

SQLite and DBdata

Exercises Solutions

Chapter 6 homework

or

Exercise 6.2.2 e)

Exercise 6.3.1 c)

Exercise 6.4.6 i)

7

Transactions in SQL

Many kinds of Constraints

Triggers

Chapter6-transaction

Chapter7-constraints 

Chapter7-triggers

Exercise 6.6.4

Textbook 7.5

Classroom Exercise: DBdata1

8

Views and Indexes

EmbededSQL & Stored Modules

Chapter 8-views and Indexes

Chapter9-Embeded&storedModule

Python&SQLite by Bao,Weiming

Textbook 8.2

Homework: to find a way to connect a DBMS from a programming language.

9

Database Connection Libraries

Chapter 9-JDBC&PHP

Textbook 9.4

10

Authorization

Chapter10-authorization

Classroom exercises

11

Recursion in SQL

Chapter10-recursiveSQL

Textbook 10.2

12

Introduction to Object-relational model

Chapter10-ObjectRelationalModel

 

Textbook 10.6,10.7

 

13

Online Analytic processing

Chapter10-OnlineAnalysis

Classroom Exercises: mydbdata

Exercise Solutions: OLAPqueries

14

XML model

(DTD, XML schema)

Chapter 11-XMLModel,

Classroom exercises

Textbook 11.4

Online Testing

15

Introduction to Xpath,Xquery,XSLT

Chapter 12-QueryXML

 

Textbook 12.1

bookstoreSchema

BarsDTD

16

Review & QA

 

 


Homework upload:

ftp://public.sjtu.edu.cn  to the directory of each chapter.

Notice: StudentID is the file name.

 

Grading Scheme

1.     Homework & attendance (20~30%)  

2.     Final Examination (80~70%)

Q&AJune 19 (time: 9AM to 11AM place: my office 3-533)