Database 2018-02: Difference between revisions

From Innovation
Jump to: navigation, search
No edit summary
 
(39 intermediate revisions by the same user not shown)
Line 38: Line 38:
== Text Book ==
== Text Book ==
=== Main Textbook  ===
=== Main Textbook  ===
* Database Systems : the Complete Book (Paperback) | Garcia-Molina, Ullman 지음 |Pearson Education Asia [http://www.kyobobook.co.kr/product/detailViewEng.laf?ejkGb=ENG&mallGb=ENG&barcode=9780131354289&orderClick=LET&Kc= link] [http://www.kyobobook.co.kr/product/detailViewKor.laf?ejkGb=KOR&mallGb=KOR&barcode=9788989132189&orderClick=LAG&Kc= 1판 번역본]
Not necessarily required to buy the book
* Database Systems : the Complete Book (Paperback) | Garcia-Molina, Ullman 지음 |Pearson Education Asia [[http://www.kyobobook.co.kr/product/detailViewEng.laf?ejkGb=ENG&mallGb=ENG&barcode=9780131354289&orderClick=LET&Kc= link] ][[http://www.kyobobook.co.kr/product/detailViewKor.laf?ejkGb=KOR&mallGb=KOR&barcode=9788989132189&orderClick=LAG&Kc= 1판 번역본] ]
 
=== Supplementary Textbook ===
=== Supplementary Textbook ===
* Database Management Systems 3/E, Ramakrishnan , Gehrke, Johannes 지음 | McGraw-Hill [http://www.kyobobook.co.kr/product/detailViewKor.laf?ejkGb=KOR&mallGb=KOR&barcode=9788982412158&orderClick=LEA&Kc= 번역본 교보문고 바로가기]
* Database Management Systems 3/E, Ramakrishnan , Gehrke, Johannes 지음 | McGraw-Hill [http://www.kyobobook.co.kr/product/detailViewKor.laf?ejkGb=KOR&mallGb=KOR&barcode=9788982412158&orderClick=LEA&Kc= 번역본 교보문고 바로가기]
* 만화로 쉽게 배우는 데이터베이스, Takahashi Mana 지음, 홍희정 옮김, Azuma Shouko 그림, 성안당  
* 만화로 쉽게 배우는 데이터베이스, Takahashi Mana 지음, 홍희정 옮김, Azuma Shouko 그림, 성안당  
* 데이터베이스 시스템 (AN INTRODUCTION TO DATABASE SYSTEMS), C.J. Date, Pearson, 2004
* 데이터베이스 시스템 (AN INTRODUCTION TO DATABASE SYSTEMS), C.J. Date, Pearson, 2004
== Evaluation ==
{| class="wikitable"
! Categories !! Ratio !! Categories !! Ratio
|-
| Attendance || 10 || Quiz || 10
|-
| Assignments || 10 || Midterm || 30
|-
| Project || 10 || Final || 30
|-
| || || Total || 100%
|}
* All exams are closed book and closed notes
* Any request for regrade must be made within one week of the time that the paper is returned, with no exception. You should describe in writing why the score is incorrect or graded unfairly. The written argument must be self-contained and can be evaluated based only on what is written on the paper.
== Discussion ==
Please provide your info on this [https://docs.google.com/document/d/1FQGt1vLTIOXj2WRFcGIhjDCzJOc9aig0nYLEl8DD1xI/edit?usp=sharing link].
All discussions and assignments are to be submitted in [https://piazza.com/class/jkt5x58qttc3u9 Piazza]. Enroll into the class through the following [https://piazza.com/class/jkt5x58qttc3u9 link]
== Lecture Plan ==
{|class="wikitable"
|-
| width="25px" style="background: Turquoise; text-align:center;" | #
| width="50%" style="background: Turquoise; text-align:left;" | Topic & material
| width="5%" style="background: Turquoise; text-align:left;" | Readings
| width="45%" style="background: Turquoise; text-align:left;" | Assignments
|-
| style="text-align:center;" | 1
| style="vertical-align: top;"| '''Course Overview and Database History'''
[ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day01/Day01_Intro.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day01/Day01_Intro.pdf pdf] ]
<br> '''Worksheet:'''
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day01/dataset_1.db DB]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day01/DB-WS01a.ipynb Worksheet 1a]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day01/DB-WS01b.ipynb Worksheet 1b]
<br> If you haven't installed Jupyter, then please consult [[#JupyterInstall | Jupyter Installation Guide]]
| style="vertical-align: top;"|
| style="vertical-align: top;"|
<!-- Topic 2 -->
|-
| style="text-align:center;" | 2
| style="vertical-align: top;"| '''SQL Introduction Part I'''
[ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day02/Day02_SQL_p1.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day02/Day02_SQL_p1.pdf pdf] ]
[ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day02/Day02_worksheet.ipynb Notebook] ][ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day02/Day02_worksheet_eng.ipynb English] ]
<br> '''Worksheet:'''
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day02/dataset_1.db DB]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day02/DB-WS02a.ipynb Worksheet 2a]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day02/DB-WS02b.ipynb Worksheet 2b]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day02/DB-WS02c.ipynb Worksheet 2c]
| style="vertical-align: top;"| Ch. 6
| style="vertical-align: top;"| '''Assignment 1 '''
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Assignments1/HW1.ipynb Assignment 1] [http://open.gnu.ac.kr/lecslides/2018-2-DB/Assignments1/en/HW1.ipynb [English]]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Assignments1/correct_output.txt Correct Output] [http://open.gnu.ac.kr/lecslides/2018-2-DB/Assignments1/en/correct_output.txt [English]]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Assignments1/flights.db.zip DB for the assignment (must be unzipped)] [http://open.gnu.ac.kr/lecslides/2018-2-DB/Assignments1/en/flights.db.zip [English]]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Assignments1/sanity_check.py Sanity checker script (run on the terminal)] [http://open.gnu.ac.kr/lecslides/2018-2-DB/Assignments1/en/sanity_check.py [English]]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Assignments1/submit.py Answer Template] [http://open.gnu.ac.kr/lecslides/2018-2-DB/Assignments1/en/submit.py [English]]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Assignments1/submission_instructions.txt Instructions on submitting the assignment] [http://open.gnu.ac.kr/lecslides/2018-2-DB/Assignments1/en/submission_instructions.txt [English]]
<!-- Topic 3 -->
|-
| style="text-align:center;" | 3
| style="vertical-align: top;"| '''SQL Introduction Part II'''
[ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day03/Day_03_SQL_p2.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day03/Day_03_SQL_p2.pdf pdf] ]
[ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day03/Day03_worksheet.ipynb Notebook] ]
<br> '''Worksheet:'''
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day03/dataset_1.db DB]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day03/DB-WS03a.ipynb Worksheet 3a]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day03/DB-WS02b.ipynb Worksheet 3b]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day03/DB-WS03c.ipynb Worksheet 3c]
| style="vertical-align: top;"| Ch. 6
| style="vertical-align: top;"|
<!-- Topic 4 -->
|-
| style="text-align:center;" | 4
| style="vertical-align: top;"| '''Databas Design with ER Diagrams'''
[ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day04/Day_04_ER.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day04/Day_04_ER.pdf pdf] ]
<br> '''Worksheet:'''
<br> We are going to create an ER diagram in class with pencil and paper
| style="vertical-align: top;"| Ch. 2
| style="vertical-align: top;"|
<!-- Topic 5 -->
|-
| style="text-align:center;" | 5
| style="vertical-align: top;"| '''Database Design Theory Part I'''
[ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day05/Day_05_Design_p1.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day05/Day_05_Design_p1.pdf pdf] ]
<br> '''Worksheet:'''
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day05/DB-WS05a.ipynb Worksheet 5a]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day05/closure_v2.py Closure visualization tool for python v2]
[http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day05/closure_v3.py / python v3]
| style="vertical-align: top;"| Ch. 3.2-3.7
| style="vertical-align: top;"|
<!-- Topic 6 -->
|-
| style="text-align:center;" | 6
| style="vertical-align: top;"| '''Database Design Theory Part II'''
[ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day06/Day_06_Design_p2.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day06/Day_06_Design_p2.pdf pdf] ]
<br> '''Worksheet:'''
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day06/DB-WS06a.ipynb Worksheet 6a]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day06/DB-WS06a.ipynb Worksheet 6b]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day06/closure_v2.py Closure visualization tool for python v2]
[http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day06/closure_v3.py / python v3]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day06/DB-WS-3NF.ipynb Excercises on 3rd Normal Form]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day06/DB-WS-MVDs.ipynb Excercises on Multi-Valued Dependency]
| style="vertical-align: top;"|
| style="vertical-align: top;"| '''Assignment 2'''
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Assignments2/HW2.ipynb Assignment 2]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Assignments2/submit.py Answer Template]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Assignments2/submission_instructions.txt Instructions on submitting the assignment]
<!-- Topic 7 -->
|-
| style="text-align:center;" | 7
| style="vertical-align: top;"| '''Transactions'''
[ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day07/Day_07_TXNs.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day07/Day_07_TXNs.pdf pdf] ]
<br> '''Worksheet:'''
| style="vertical-align: top;"| Ch. 8.6
| style="vertical-align: top;"|
<!-- Topic 8 -->
|-
| style="text-align:center;" | 8
| style="vertical-align: top;"| '''Concurrency and Locks'''
[ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day08/Day_08_Con_lock.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day08/Day_08_Con_lock.pdf pdf] ]
<br> '''Worksheet:'''
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day08/DB-WS08a.ipynb Worksheet 8a]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day08/txn_viewer_v2.py Transaction visulization tool for pyton v2]
[http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day08/txn_viewer_v3.py / python v3]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day08/txnViewer.js Transaction visulation java script]
| style="vertical-align: top;"| Ch. 18.1-18.4
| style="vertical-align: top;"|
<!-- Topic 9 -->
|-
| style="text-align:center;" | 9
| style="vertical-align: top;"| '''Buffer Manager and Introduction to External Merge algorithm'''
[ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day09/Day_09_BM_EM.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day09/Day_09_BM_EM.pdf pdf] ]
<br> '''Worksheet:'''
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day09/DB-WS09a.ipynb Worksheet 9a]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day09/EMS_Demo.ipynb External Merge Sort Workseet]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day09/io_backend_v2.py Buffer manager engine for pyton v2]
[http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day09/io_backend_v3.py / python v3]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day09/display_tools.py Display helper]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day09/compModel.js Modeling java script]
| style="vertical-align: top;"| Ch. 11.4
| style="vertical-align: top;"|
<!-- Topic 10 -->
|-
| style="text-align:center;" | 10
| style="vertical-align: top;"| '''More on External Merge Sort'''
[ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day10/Day_10_EMS.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day10/Day_10_EMS.pdf pdf] ]
<br> '''Worksheet:'''
| style="vertical-align: top;"|
| style="vertical-align: top;"|
<!-- Topic 11 -->
|-
| style="text-align:center;" | 11
| style="vertical-align: top;"| '''Indexing'''
[ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day11/Day_11_Indexes.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day11/Day_11_Indexes.pdf pdf] ]
<br> '''Worksheet:'''
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day11/DB-WS11a.ipynb Worksheet 11a]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day11/complaint.db Database for the worksheet]
| style="vertical-align: top;"| Ch. 13.1-13.3
| style="vertical-align: top;"|
<!-- Topic 12 -->
|-
| style="text-align:center;" | 12
| style="vertical-align: top;"| '''Join part 1'''
[ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day12/Day12_Joins_p1.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day12/Day12_Joins_p1.pdf pdf] ]
<br> '''Worksheet:'''
| style="vertical-align: top;"| Ch. 15
| style="vertical-align: top;"|
<!-- Topic 13 -->
|-
| style="text-align:center;" | 13
| style="vertical-align: top;"| '''Join part 1'''
[ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day13/Day13_Joins_p2.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day13/Day13_Joins_p2.pdf pdf] ]
<br> '''Worksheet:'''
| style="vertical-align: top;"| Ch. 15
| style="vertical-align: top;"|
<!-- Topic 14 -->
|-
| style="text-align:center;" | 14
| style="vertical-align: top;"| '''Relational Algebra'''
[ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day14/Day_14_Algebra.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day14/Day_14_Algebra.pdf pdf] ]
<br> '''Worksheet:'''
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day14/DB-WS014a.ipynb Worksheet 9a]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day14/DB-WS14a_tree.png Picture used for the Worksheet]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day14/relation_algebra_v2.py Relational Algebra for pyton v2]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day14/display_tools.py Display helper]
| style="vertical-align: top;"| Ch. 2 and 16.3
| style="vertical-align: top;"|
<!-- Topic 15 -->
|-
| style="text-align:center;" | 15
| style="vertical-align: top;"| '''Query Optimization'''
[ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day15/Day_15_Optimization.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day15/Day_15_Optimization.pdf pdf] ]
<br> '''Worksheet:'''
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day15/DB-WS015a.ipynb Worksheet 15a]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day15/DB-WS015b.ipynb Worksheet 15b]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day15/complaint.db Database for the worksheet]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day15/relation_algebra_v2.py Relational Algebra for pyton v2]
<br> [http://open.gnu.ac.kr/lecslides/2018-2-DB/Lectures/Day15/display_tools.py Display helper]
| style="vertical-align: top;"|
| style="vertical-align: top;"|
|}
<div id="JupyterInstall"></div>
== Working Environment Setup ==
=== Step 1: Installing Jupiter Notebook===
Gentle reminder that, jupyter notebook best works at Linux
==== Option 1: Installation via Anaconda ====
It is recommended that you install Jupyter Notebook via Anaconda
# Download & install Anaconda for Python [https://www.anaconda.com/download here]
# Run <code>jupyter notebook</code> to confirm that it is properly installed
If Anaconda doesn't have jupyter:
you can try running
<code>conda install jupyter</code>
==== Option 2: Installation via <code>pip</code> ====
You can also install via the python package manager <code>pip</code>
# Make sure you have installed
#* <code>python</code> and <code>pip</code>
# Make sure <code>pip</code> is updated to its latest version
#; <code>pip install --user --upgrade pip</code>
# Install Jupyter Notebook
#; <code>pip install --user --upgrade jupyter</code>
==== Installing other kernels and versions of python ====
When you are using Python3 and want to switch to Python2 in the notebook,
<code>
python2 -m pip install ipykernel
python2 -m ipykernel install —user
</code>
After restarting the Jupyter, you can chose different kernel in Jupyter notebook.
=== Step 2: Installing Other Dependencies ===
==== Git ====
Git is a version control system which we use to store the various course material; you can download from [https://git-scm.com/downloads here]
* you can download the course material from [https://github.com/resourceful/lecture_db here]
* If you want the up-to-date version of the course material, checkout the course repository from the <code>git</code> by typing the following command
** <code> git clone https://github.com/resourceful/lecture_db YourDestination  </code>
==== IPython-SQL ====
<code>python-sql</code> allows you to use SQL queries inside jupyter notebooks; install using pip
* <code>pip install --user --upgrade ipython-sql</code>
==== other modules ====
you will be needing
* markdown
* numpy
* pandas
=== Step 3: Getting Started ===
==== Getting the latest course materials ====
You can always access the latest course materials on the course website. You can download all at once and keep them synced by using git. To get started with this, run:
<code> git clone https://github.com/resourceful/lecture_db YourDestination </code>
Now you should have all the materials in ''Database''; to get the latest version, run the following command in the repository.
<code>git pull</code>
==== To read the notebook ====
In the directory where the relevant course materials are, run:
<code>jupyter notebook</code>
== Course Credit ==
Much of the materials of this lecture is based on CS145 of Stanford.

Latest revision as of 14:14, 20 September 2018

Class Information

Class Info
Class ETA00137 - 데이터베이스
Lecturer Seongjin Lee
Time and Place 407-507 Tuesday 16:00-18:00
407-507 Thursday 16:00-17:00
Office Hour Tuesday: 18:00-19:00
Contacts Office: 407-314
Email: insight at gnu dot ac dot kr


Course Introduction

In Database Management System (DBMS), we study the database which is organized and structured collection of data. DBMS is a software system which acts as intermediary between user or application and the data. It allows to navigate through the data and analyze the data. In this course, we are going to learn the core mechanism that holds the system together.

Goal

In this course, we are going to learn the essence of data base management system. Students are going to learn the following concepts.

  • Relational model
  • Entity-relational model
  • SQL
  • External, conceptual, physical levels of DMBS
  • Data definition language, Data manipulation language, query language
  • Data structures
  • Index
  • Transaction and Concurrency



Text Book

Main Textbook

Not necessarily required to buy the book

  • Database Systems : the Complete Book (Paperback) | Garcia-Molina, Ullman 지음 |Pearson Education Asia [link ][1판 번역본 ]

Supplementary Textbook

  • Database Management Systems 3/E, Ramakrishnan , Gehrke, Johannes 지음 | McGraw-Hill 번역본 교보문고 바로가기
  • 만화로 쉽게 배우는 데이터베이스, Takahashi Mana 지음, 홍희정 옮김, Azuma Shouko 그림, 성안당
  • 데이터베이스 시스템 (AN INTRODUCTION TO DATABASE SYSTEMS), C.J. Date, Pearson, 2004


Evaluation

Categories Ratio Categories Ratio
Attendance 10 Quiz 10
Assignments 10 Midterm 30
Project 10 Final 30
Total 100%
  • All exams are closed book and closed notes
  • Any request for regrade must be made within one week of the time that the paper is returned, with no exception. You should describe in writing why the score is incorrect or graded unfairly. The written argument must be self-contained and can be evaluated based only on what is written on the paper.

Discussion

Please provide your info on this link.

All discussions and assignments are to be submitted in Piazza. Enroll into the class through the following link

Lecture Plan

# Topic & material Readings Assignments
1 Course Overview and Database History

[ pptx ] [ pdf ]
Worksheet:
DB
Worksheet 1a
Worksheet 1b
If you haven't installed Jupyter, then please consult Jupyter Installation Guide

2 SQL Introduction Part I

[ pptx ] [ pdf ] [ Notebook ][ English ]
Worksheet:
DB
Worksheet 2a
Worksheet 2b
Worksheet 2c

Ch. 6 Assignment 1


Assignment 1 [English]
Correct Output [English]
DB for the assignment (must be unzipped) [English]
Sanity checker script (run on the terminal) [English]
Answer Template [English]
Instructions on submitting the assignment [English]

3 SQL Introduction Part II

[ pptx ] [ pdf ] [ Notebook ]
Worksheet:
DB
Worksheet 3a
Worksheet 3b
Worksheet 3c

Ch. 6


4 Databas Design with ER Diagrams

[ pptx ] [ pdf ]
Worksheet:
We are going to create an ER diagram in class with pencil and paper

Ch. 2


5 Database Design Theory Part I

[ pptx ] [ pdf ]
Worksheet:
Worksheet 5a
Closure visualization tool for python v2 / python v3

Ch. 3.2-3.7



6 Database Design Theory Part II

[ pptx ] [ pdf ]
Worksheet:
Worksheet 6a
Worksheet 6b
Closure visualization tool for python v2 / python v3
Excercises on 3rd Normal Form
Excercises on Multi-Valued Dependency

Assignment 2


Assignment 2
Answer Template
Instructions on submitting the assignment


7 Transactions

[ pptx ] [ pdf ]
Worksheet:

Ch. 8.6


8 Concurrency and Locks

[ pptx ] [ pdf ]
Worksheet:
Worksheet 8a
Transaction visulization tool for pyton v2 / python v3
Transaction visulation java script

Ch. 18.1-18.4


9 Buffer Manager and Introduction to External Merge algorithm

[ pptx ] [ pdf ]
Worksheet:
Worksheet 9a
External Merge Sort Workseet
Buffer manager engine for pyton v2 / python v3
Display helper
Modeling java script

Ch. 11.4


10 More on External Merge Sort

[ pptx ] [ pdf ]
Worksheet:


11 Indexing

[ pptx ] [ pdf ]
Worksheet:
Worksheet 11a
Database for the worksheet

Ch. 13.1-13.3


12 Join part 1

[ pptx ] [ pdf ]
Worksheet:

Ch. 15


13 Join part 1

[ pptx ] [ pdf ]
Worksheet:

Ch. 15


14 Relational Algebra

[ pptx ] [ pdf ]
Worksheet:
Worksheet 9a
Picture used for the Worksheet
Relational Algebra for pyton v2
Display helper

Ch. 2 and 16.3


15 Query Optimization

[ pptx ] [ pdf ]
Worksheet:
Worksheet 15a
Worksheet 15b
Database for the worksheet
Relational Algebra for pyton v2
Display helper


Working Environment Setup

Step 1: Installing Jupiter Notebook

Gentle reminder that, jupyter notebook best works at Linux


Option 1: Installation via Anaconda

It is recommended that you install Jupyter Notebook via Anaconda

  1. Download & install Anaconda for Python here
  2. Run jupyter notebook to confirm that it is properly installed

If Anaconda doesn't have jupyter: you can try running

conda install jupyter


Option 2: Installation via pip

You can also install via the python package manager pip

  1. Make sure you have installed
    • python and pip
  2. Make sure pip is updated to its latest version
    pip install --user --upgrade pip
  3. Install Jupyter Notebook
    pip install --user --upgrade jupyter


Installing other kernels and versions of python

When you are using Python3 and want to switch to Python2 in the notebook,

python2 -m pip install ipykernel

python2 -m ipykernel install —user

After restarting the Jupyter, you can chose different kernel in Jupyter notebook.

Step 2: Installing Other Dependencies

Git

Git is a version control system which we use to store the various course material; you can download from here

  • you can download the course material from here
  • If you want the up-to-date version of the course material, checkout the course repository from the git by typing the following command

IPython-SQL

python-sql allows you to use SQL queries inside jupyter notebooks; install using pip

  • pip install --user --upgrade ipython-sql

other modules

you will be needing

  • markdown
  • numpy
  • pandas

Step 3: Getting Started

Getting the latest course materials

You can always access the latest course materials on the course website. You can download all at once and keep them synced by using git. To get started with this, run:

git clone https://github.com/resourceful/lecture_db YourDestination

Now you should have all the materials in Database; to get the latest version, run the following command in the repository.

git pull

To read the notebook

In the directory where the relevant course materials are, run:

jupyter notebook


Course Credit

Much of the materials of this lecture is based on CS145 of Stanford.

Retrieved from "http://open.gnu.ac.kr/mediawiki/index.php?title=Database_2018-02&oldid=599"