Database 2020-02 - Revision history

James: /* Lecture Plan */

2020-10-27T08:38:54Z

Lecture Plan

← Older revision		Revision as of 17:38, 27 October 2020
Line 201:		Line 201:
	<br> [http://open.gnu.ac.kr/lecslides/2020-2-DB/Assignments2/submit.py Answer Template]		<br> [http://open.gnu.ac.kr/lecslides/2020-2-DB/Assignments2/submit.py Answer Template]
	<br> [http://open.gnu.ac.kr/lecslides/2020-2-DB/Assignments2/submission_instructions.txt Instructions on submitting the assignment]		<br> [http://open.gnu.ac.kr/lecslides/2020-2-DB/Assignments2/submission_instructions.txt Instructions on submitting the assignment]
	<br><br> [https://docs.google.com/forms/d/e/1FAIpQLSeQ-5cmY8Ymlz4VkFngOQQywX_EWk_2op2wiKixXZEfFJKZkw/viewform?usp=sf_link quiz] <br><br><br><font color="red"> '''Exam on Oct. ~~27th~~, up to Day 08 at 16:~~30 Online~~ '''</font>		<br><br> [https://docs.google.com/forms/d/e/1FAIpQLSeQ-5cmY8Ymlz4VkFngOQQywX_EWk_2op2wiKixXZEfFJKZkw/viewform?usp=sf_link quiz] <br><br><br><font color="red"> '''Exam on Oct. 30th, up to Day 06 at 16:00 in 407-501 '''</font>

	<!-- Topic 7 -->		<!-- Topic 7 -->

James: /* Lecture Plan */

2020-10-27T08:37:45Z

Lecture Plan

← Older revision		Revision as of 17:37, 27 October 2020
Line 201:		Line 201:
	<br> [http://open.gnu.ac.kr/lecslides/2020-2-DB/Assignments2/submit.py Answer Template]		<br> [http://open.gnu.ac.kr/lecslides/2020-2-DB/Assignments2/submit.py Answer Template]
	<br> [http://open.gnu.ac.kr/lecslides/2020-2-DB/Assignments2/submission_instructions.txt Instructions on submitting the assignment]		<br> [http://open.gnu.ac.kr/lecslides/2020-2-DB/Assignments2/submission_instructions.txt Instructions on submitting the assignment]
	<br><br> [https://docs.google.com/forms/d/e/1FAIpQLSeQ-5cmY8Ymlz4VkFngOQQywX_EWk_2op2wiKixXZEfFJKZkw/viewform?usp=sf_link quiz]		<br><br> [https://docs.google.com/forms/d/e/1FAIpQLSeQ-5cmY8Ymlz4VkFngOQQywX_EWk_2op2wiKixXZEfFJKZkw/viewform?usp=sf_link quiz] <br><br><br><font color="red"> '''Exam on Oct. 27th, up to Day 08 at 16:30 Online '''</font>

	<!-- Topic 7 -->		<!-- Topic 7 -->
Line 234:		Line 234:
	<br> [https://www.youtube.com/watch?v=olj00HmP71U&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=34 Strict Two-phase locking (9:31)]		<br> [https://www.youtube.com/watch?v=olj00HmP71U&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=34 Strict Two-phase locking (9:31)]
	\| style="vertical-align: top;"\| Ch. 18.1-18.4		\| style="vertical-align: top;"\| Ch. 18.1-18.4
	\| style="vertical-align: top;"\| <br> [https://docs.google.com/forms/d/e/1FAIpQLSf_esRp4usgvJiNizGHEpk8W_JAZzfCvp3tD5flxpxo-aES5A/viewform?usp=sf_link quiz] ~~<br><br><br><font color="red"> '''Exam on Oct. 27th, up to Day 08 at 16:30 Online '''</font>~~		\| style="vertical-align: top;"\| <br> [https://docs.google.com/forms/d/e/1FAIpQLSf_esRp4usgvJiNizGHEpk8W_JAZzfCvp3tD5flxpxo-aES5A/viewform?usp=sf_link quiz]

James: /* Lecture Plan */

2020-10-13T05:22:26Z

Lecture Plan

← Older revision		Revision as of 14:22, 13 October 2020
Line 188:		Line 188:
	<br> '''Worksheet:'''		<br> '''Worksheet:'''
	<br> [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day06/DB-WS06a.ipynb Worksheet 6a]		<br> [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day06/DB-WS06a.ipynb Worksheet 6a]
	<br> [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day06/DB-~~WS06a~~.ipynb Worksheet 6b]		<br> [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day06/DB-WS06b.ipynb Worksheet 6b]
	<br> [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day06/closure_v2.py Closure visualization tool for python v2]		<br> [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day06/closure_v2.py Closure visualization tool for python v2]
	[http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day06/closure_v3.py / python v3]		[http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day06/closure_v3.py / python v3]

James: /* Lecture Plan */

2020-08-10T01:32:43Z

Lecture Plan

← Older revision		Revision as of 10:32, 10 August 2020
Line 116:		Line 116:
	<br> [http://open.gnu.ac.kr/lecslides/2020-2-DB/Assignments1/submission_instructions.txt Instructions on submitting the assignment] [http://open.gnu.ac.kr/lecslides/2020-2-DB/Assignments1/en/submission_instructions.txt [English]]		<br> [http://open.gnu.ac.kr/lecslides/2020-2-DB/Assignments1/submission_instructions.txt Instructions on submitting the assignment] [http://open.gnu.ac.kr/lecslides/2020-2-DB/Assignments1/en/submission_instructions.txt [English]]
	<br>		<br>
	<br>~~Due 20191008~~		<br>
	<br>		<br>
	<br>[https://docs.google.com/forms/d/e/1FAIpQLSc77xOpv5Pjtgn9rJau3iXshMjMRbjRk_LLeDtLkRG0oi1Yhw/viewform?usp=sf_link quiz]		<br>[https://docs.google.com/forms/d/e/1FAIpQLSc77xOpv5Pjtgn9rJau3iXshMjMRbjRk_LLeDtLkRG0oi1Yhw/viewform?usp=sf_link quiz]
Line 335:		Line 335:


	<div id="JupyterInstall"></div>		<div id="JupyterInstall"></div>

	== Working Environment Setup ==		== Working Environment Setup ==

James at 01:26, 10 August 2020

2020-08-10T01:26:14Z

← Older revision		Revision as of 10:26, 10 August 2020
Line 60:		Line 60:

	* All exams are closed book and closed notes		* 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.		* 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.
	* Due to COVID-19, Evaluation metrics are may change in favor of the student's effort and progress made by individual students.





	== Lecture Plan ==		== Lecture Plan ==

Line 230:		Line 234:
	<br> [https://www.youtube.com/watch?v=olj00HmP71U&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=34 Strict Two-phase locking (9:31)]		<br> [https://www.youtube.com/watch?v=olj00HmP71U&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=34 Strict Two-phase locking (9:31)]
	\| style="vertical-align: top;"\| Ch. 18.1-18.4		\| style="vertical-align: top;"\| Ch. 18.1-18.4
	\| style="vertical-align: top;"\| <br> [https://docs.google.com/forms/d/e/1FAIpQLSf_esRp4usgvJiNizGHEpk8W_JAZzfCvp3tD5flxpxo-aES5A/viewform?usp=sf_link quiz] <br><br><br><font color="red"> '''Exam on ~~29th~~, up to Day 08 at 16:30 ~~in room 417-202~~ '''</font>		\| style="vertical-align: top;"\| <br> [https://docs.google.com/forms/d/e/1FAIpQLSf_esRp4usgvJiNizGHEpk8W_JAZzfCvp3tD5flxpxo-aES5A/viewform?usp=sf_link quiz] <br><br><br><font color="red"> '''Exam on Oct. 27th, up to Day 08 at 16:30 Online '''</font>


Line 309:		Line 313:
	<br> [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day14/display_tools.py Display helper]		<br> [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day14/display_tools.py Display helper]
	\| style="vertical-align: top;"\| Ch. 2 and 16.3		\| style="vertical-align: top;"\| Ch. 2 and 16.3
	\| style="vertical-align: top;"\| <br><br><br><font color="red"> '''Exam on Dec ~~10th 16:00 in room 417-202~~ '''</font>		\| style="vertical-align: top;"\| <br><br><br><font color="red"> '''Exam on Dec 8th OFFLINE'''</font>

James: /* Evaluation */

2020-08-10T01:21:37Z

Evaluation

← Older revision		Revision as of 10:21, 10 August 2020
Line 60:		Line 60:

	* All exams are closed book and closed notes		* 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.		* 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.
			* Due to COVID-19, Evaluation metrics are may change in favor of the student's effort and progress made by individual students.





	== Lecture Plan ==		== Lecture Plan ==

James at 01:19, 10 August 2020

2020-08-10T01:19:57Z

← Older revision		Revision as of 10:19, 10 August 2020
Line 1:		Line 1:
	~~== Class Information==~~		== Class Information==
	~~{\|class="wikitable"~~		{\|class="wikitable"
	!		!
	~~!Class Info~~		!Class Info
	\|-		\|-
	~~\| '''Class''' \|\| ETA00009 - 데이터베이스~~		\| '''Class''' \|\| ETA00009 - 데이터베이스
	\|-		\|-
	~~\| '''Lecturer''' \|\| Seongjin Lee~~		\| '''Lecturer''' \|\| Seongjin Lee
	\|-		\|-
	~~\| '''Time and Place''' \|\| 407-202 Tuesday 16:00-18:00, Friday 16:00-18:00~~		\| '''Time and Place''' \|\| 407-202 Tuesday 16:00-18:00, Friday 16:00-18:00
	\|-		\|-
	~~\| '''Office Hour''' \|\| Tuesday: 14:00-16:00~~		\| '''Office Hour''' \|\| Tuesday: 14:00-16:00
	\|-		\|-
	~~\| '''Contacts''' \|\| Office: 407-314~~		\| '''Contacts''' \|\| Office: 407-314
	\|-		\|-
	~~\| \|\| Email: insight at gnu dot ac dot kr~~		\| \|\| Email: insight at gnu dot ac dot kr
	\|}		\|}

	~~==Course Introduction ==~~		==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.		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 ==~~		== Goal ==
	~~In this course, we are going to learn the essence of data base management system. Students are going to learn the following concepts.~~		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		* Relational model
	* Entity-relational model		* Entity-relational model
	* SQL		* SQL
	* External, conceptual, physical levels of DMBS		* External, conceptual, physical levels of DMBS
	* Data definition language, Data manipulation language, query language		* Data definition language, Data manipulation language, query language
	* Data structures		* Data structures
	* Index		* Index
	* Transaction and Concurrency		* Transaction and Concurrency




	~~== Text Book ==~~		== Text Book ==
	~~=== Main Textbook ===~~		=== Main Textbook ===
	~~Not necessarily required to buy the book~~		Not necessarily required to buy the book
	* Database Management Systems 3/E, Ramakrishnan , Gehrke, Johannes 지음 \| McGraw-Hill [http://www.kyobobook.co.kr/product/detailViewKor.laf		* Database Management Systems 3/E, Ramakrishnan , Gehrke, Johannes 지음 \| McGraw-Hill [http://www.kyobobook.co.kr/product/detailViewKor.laf
	Much of the materials of this lecture is based on CS145 of Stanford.		Much of the materials of this lecture is based on CS145 of Stanford.

James: Created page with "== Class Information== {|class="wikitable" ! !Class Info |- | '''Class''' || ETA00009 - 데이터베이스 |- | '''Lecturer''' || Seongjin Lee..."

2020-08-10T01:19:01Z

Created page with "== Class Information== {|class="wikitable" ! !Class Info |- | '''Class''' || ETA00009 - 데이터베이스 |- | '''Lecturer''' || Seongjin Lee..."

New page

== Class Information==
{|class="wikitable"
!
!Class Info
|-
| '''Class''' || ETA00009 - 데이터베이스
|-
| '''Lecturer''' || Seongjin Lee
|-
| '''Time and Place''' || 407-202 Tuesday 16:00-18:00, Friday 16:00-18:00
|-
| '''Office Hour''' || Tuesday: 14:00-16: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 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= 번역본 교보문고 바로가기]

=== Supplementary 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판 번역본] ]
* 만화로 쉽게 배우는 데이터베이스, Takahashi Mana 지음, 홍희정 옮김, Azuma Shouko 그림, 성안당
* 데이터베이스 시스템 (AN INTRODUCTION TO DATABASE SYSTEMS), C.J. Date, Pearson, 2004

== Evaluation ==
{| class="wikitable"
! Categories !! Ratio !!
|-
| Attendance || 10 ||
|-
| Assignments || 10 ||
|-
| Project || 10 ||
|-
| ||
|}

* 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.

== 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/2020-2-DB/Lectures/Day01/Day01_Intro.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day01/Day01_Intro.pdf pdf] ]
 '''Worksheet:'''
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day01/dataset_1.db DB]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day01/DB-WS01a.ipynb Worksheet 1a]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day01/DB-WS01b.ipynb Worksheet 1b]
 If you haven't installed Jupyter, then please consult [[#JupyterInstall | Jupyter Installation Guide]]
 '''Video:'''
 [https://www.youtube.com/watch?v=Xx1PyWyWkeo&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=2 Overview Part 1 (16:14)]
 [https://www.youtube.com/watch?v=_GypJDuTCMA&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=3 Overview Part 2 (14:48)]
| style="vertical-align: top;"|
| style="vertical-align: top;"|


|-
| style="text-align:center;" | 2
| style="vertical-align: top;"| '''SQL Introduction Part I'''
[ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day02/Day02_SQL_p1.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day02/Day02_SQL_p1.pdf pdf] ]
[ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day02/Day02_worksheet.ipynb Notebook] ][ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day02/Day02_worksheet_eng.ipynb English] ]
 '''Worksheet:'''
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day02/dataset_1.db DB]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day02/DB-WS02a.ipynb Worksheet 2a]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day02/DB-WS02b.ipynb Worksheet 2b]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day02/DB-WS02c.ipynb Worksheet 2c]
 '''Video:'''
 [https://www.youtube.com/watch?v=egNBweUhpEI&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=4 Basic SQL 1 Part 1 Schema (18:35)]
 [https://www.youtube.com/watch?v=isorfcTBxVY&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=5 Basic SQL 1 Part 2 Single Table Query (10:20)]
 [https://www.youtube.com/watch?v=TpOO2YiIoZg&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=6 Basic SQL 1 Part 3 Multi table query (9:48)]
 [https://www.youtube.com/watch?v=itroznpVhM4&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=7 Basic SQL 1 Part 4 Join Ex (9:25)]
 [https://www.youtube.com/watch?v=EznSrTJlqOM&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=8 Basic SQL 1 Part 5 Join Semantic (10:05)]
| style="vertical-align: top;"| Ch. 6
| style="vertical-align: top;"| '''Assignment 1 '''
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Assignments1/HW1.ipynb Assignment 1] [http://open.gnu.ac.kr/lecslides/2020-2-DB/Assignments1/en/HW1.ipynb [English]]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Assignments1/correct_output.txt Correct Output] [http://open.gnu.ac.kr/lecslides/2020-2-DB/Assignments1/en/correct_output.txt [English]]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Assignments1/flights.db.zip DB for the assignment (must be unzipped)] [http://open.gnu.ac.kr/lecslides/2020-2-DB/Assignments1/en/flights.db.zip [English]]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Assignments1/sanity_check.py Sanity checker script (run on the terminal)] [http://open.gnu.ac.kr/lecslides/2020-2-DB/Assignments1/en/sanity_check.py [English]]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Assignments1/submit.py Answer Template] [http://open.gnu.ac.kr/lecslides/2020-2-DB/Assignments1/en/submit.py [English]]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Assignments1/submission_instructions.txt Instructions on submitting the assignment] [http://open.gnu.ac.kr/lecslides/2020-2-DB/Assignments1/en/submission_instructions.txt [English]]
 
 Due 20191008
 
 [https://docs.google.com/forms/d/e/1FAIpQLSc77xOpv5Pjtgn9rJau3iXshMjMRbjRk_LLeDtLkRG0oi1Yhw/viewform?usp=sf_link quiz]


|-
| style="text-align:center;" | 3
| style="vertical-align: top;"| '''SQL Introduction Part II'''
[ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day03/Day_03_SQL_p2.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day03/Day_03_SQL_p2.pdf pdf] ]
[ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day03/Day03_worksheet.ipynb Notebook] ]
 '''Worksheet:'''
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day03/dataset_1.db DB]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day03/DB-WS03a.ipynb Worksheet 3a]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day03/DB-WS03b.ipynb Worksheet 3b]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day03/DB-WS03c.ipynb Worksheet 3c]
 '''Video:'''
 [https://www.youtube.com/watch?v=fBS3J9gIyqs&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=9 Basic SQL 2 01 (multiset operations) 8:09]
 [https://www.youtube.com/watch?v=IDIsWemryn4&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=10 Basic SQL 2 02 (nested query) 8:50]
 [https://www.youtube.com/watch?v=NnIrnl3RwP4&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=11 Basic SQL 2 03 (nested query 2) 7:12]
 [https://www.youtube.com/watch?v=Sn2eC9Jtkoc&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=16 Basic SQL 2 04 (aggregation) 14:10]
 [https://www.youtube.com/watch?v=fjJDOX3eTj4&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=17 Basic SQL 2 05 (join) 11:46]
| style="vertical-align: top;"| Ch. 6
| style="vertical-align: top;"| [https://docs.google.com/forms/d/e/1FAIpQLSf-eEsyro27h4yWYmfMVuC-RoWudJ9c1RWIebCbACpF46BpIg/viewform?usp=sf_link quiz]


|-
| style="text-align:center;" | 4
| style="vertical-align: top;"| '''Databas Design with ER Diagrams'''
[ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day04/Day_04_ER.pptx pptx] ][ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day04/Day_04_ER.pdf pdf] ]
 '''Worksheet:'''
 We are going to create an ER diagram in class with pencil and paper
 '''Video:'''
 [https://www.youtube.com/watch?v=K5MctgUSZ7A&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=12 ER Model Part 1 Entity Set (9:37)]
 [https://www.youtube.com/watch?v=sgrWRbxlexo&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=14 ER Model Part 2 Relationship (10:22)]
 [https://www.youtube.com/watch?v=3PS2iQgNfTw&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=13 ER Model Part 3 Multiplicity (11:48)]
 [https://www.youtube.com/watch?v=QL27ml4cn60&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=15 ER Model Part 4 Design Consideration (6:08)]
 [https://www.youtube.com/watch?v=Hc7pwv0Fncw&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=18 ER Model Part 5 Subclass ISA (8:31)]
 [https://www.youtube.com/watch?v=VGo8QcMw11o&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=19 ER Model Part 6 Constraints and Weak entity set (8:58)]
| style="vertical-align: top;"| Ch. 2
| style="vertical-align: top;"| [[https://docs.google.com/forms/d/e/1FAIpQLSdJ-DTRM1RYhJhY5MuZbSfsl7bTH781bf70JiQ0T43d83MYTg/viewform?usp=sf_link quiz]] [[http://open.gnu.ac.kr/lecslides/2019-2-DB/Activity/ER_diagram.pdf worksheet]]


|-
| style="text-align:center;" | 5
| style="vertical-align: top;"| '''Database Design Theory Part I'''
[ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day05/Day_05_Design_p1.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day05/Day_05_Design_p1.pdf pdf] ]
 '''Worksheet:'''
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day05/DB-WS05a.ipynb Worksheet 5a]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day05/closure_v2.py Closure visualization tool for python v2]
[http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day05/closure_v3.py / python v3]
 '''Video:'''
 [https://www.youtube.com/watch?v=5nKKbcTpioI&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=23 Normal forms (10:39)]
 [https://www.youtube.com/watch?v=-kyE0HYQih0&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=20 Functional Dependencies (10:18)]
 [https://www.youtube.com/watch?v=pNseyqwugbo&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=21 Finding FDs (14:35)]
 [https://www.youtube.com/watch?v=nkVmt1NOfcU&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=22 Closure Part 1 (7:48)]
 [https://www.youtube.com/watch?v=r_FFYD29fdI&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=24 Closure Part 2 (7:43)]
| style="vertical-align: top;"| Ch. 3.2-3.7
| style="vertical-align: top;"| [[https://docs.google.com/forms/d/e/1FAIpQLSf1zr-cBgxv9f19lBntJpyCgd87R5IRMsQGdPjAagvqkehlyg/viewform?usp=sf_link quiz]] [[http://open.gnu.ac.kr/lecslides/2019-2-DB/Activity/FDandClosure.pdf worksheet]]


|-
| style="text-align:center;" | 6
| style="vertical-align: top;"| '''Database Design Theory Part II'''
[ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day06/Day_06_Design_p2.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day06/Day_06_Design_p2.pdf pdf] ]
 '''Worksheet:'''
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day06/DB-WS06a.ipynb Worksheet 6a]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day06/DB-WS06a.ipynb Worksheet 6b]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day06/closure_v2.py Closure visualization tool for python v2]
[http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day06/closure_v3.py / python v3]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day06/DB-WS-3NF.ipynb Excercises on 3rd Normal Form]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day06/DB-WS-MVDs.ipynb Excercises on Multi-Valued Dependency]
 '''Video:'''
 [https://www.youtube.com/watch?v=uoRKjrGoLc8&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=25 BCNF (11:59)]
 [https://www.youtube.com/watch?v=W5UyIzld6R4&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=26 Decomposition (12:36)]
| style="vertical-align: top;"|
| style="vertical-align: top;"| '''Assignment 2'''
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Assignments2/HW2.ipynb Assignment 2]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Assignments2/submit.py Answer Template]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Assignments2/submission_instructions.txt Instructions on submitting the assignment]
 [https://docs.google.com/forms/d/e/1FAIpQLSeQ-5cmY8Ymlz4VkFngOQQywX_EWk_2op2wiKixXZEfFJKZkw/viewform?usp=sf_link quiz]


|-
| style="text-align:center;" | 7
| style="vertical-align: top;"| '''Transactions'''
[ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day07/Day_07_TXNs.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day07/Day_07_TXNs.pdf pdf] ]
 '''Worksheet:'''
 '''Video:'''
 [https://www.youtube.com/watch?v=Xqb6cCANVIo&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=30 Transaction Introduction (6:18)]
 [https://www.youtube.com/watch?v=RT5aCN_KeIc&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=27 Motivation of Transaction (9:20)]
 [https://www.youtube.com/watch?v=4ooLrwpO8Yg&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=28 Properties of Transaction (6:47)]
 [https://www.youtube.com/watch?v=nOhpNG2gR0M&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=29 Atomicity and durability via logging (12:34)]
| style="vertical-align: top;"| Ch. 8.6
| style="vertical-align: top;"| [https://docs.google.com/forms/d/e/1FAIpQLSdI4tB4k0iRJdbgIk5iFRtqhwPrNtRdy7bksxp63ziVjvVXRw/viewform?usp=sf_link quiz]


|-
| style="text-align:center;" | 8
| style="vertical-align: top;"| '''Concurrency and Locks'''
[ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day08/Day_08_Con_lock.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day08/Day_08_Con_lock.pdf pdf] ]
 '''Worksheet:'''
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day08/DB-WS08a.ipynb Worksheet 8a]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day08/txn_viewer_v2.py Transaction visulization tool for pyton v2]
[http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day08/txn_viewer_v3.py / python v3]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day08/txnViewer.js Transaction visulation java script]
 '''Video:'''
 [https://www.youtube.com/watch?v=ffIXWmZxY8I&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=31 Interleaving and Scheduling (10:15)]
 [https://www.youtube.com/watch?v=NqGA_KfsGqI&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=32 Conflicts (6:07)]
 [https://www.youtube.com/watch?v=F-9hfCO1u8g&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=33 Conflict Serializability (11:16)]
 [https://www.youtube.com/watch?v=olj00HmP71U&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=34 Strict Two-phase locking (9:31)]
| style="vertical-align: top;"| Ch. 18.1-18.4
| style="vertical-align: top;"| [https://docs.google.com/forms/d/e/1FAIpQLSf_esRp4usgvJiNizGHEpk8W_JAZzfCvp3tD5flxpxo-aES5A/viewform?usp=sf_link quiz] '''Exam on 29th, up to Day 08 at 16:30 in room 417-202 '''


|-
| style="text-align:center;" | 9
| style="vertical-align: top;"| '''Buffer Manager and Introduction to External Merge algorithm'''
[ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day09/Day_09_BM_EM.pptx pptx] ]
 '''Worksheet:'''
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day09/DB-WS09a.ipynb Worksheet 9a]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day09/EMS_Demo.ipynb External Merge Sort Workseet]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day09/io_backend_v2.py Buffer manager engine for pyton v2]
[http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day09/io_backend_v3.py / python v3]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day09/display_tools.py Display helper]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day09/compModel.js Modeling java script]
 '''Video:'''
 [https://www.youtube.com/watch?v=C1Wq15UxzQ4&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=35 Disk, Buffer, and File system (8:55)]
 [https://www.youtube.com/watch?v=hJVlt8sLMC4&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=36 External Mege (8:00)]
| style="vertical-align: top;"| Ch. 11.4
| style="vertical-align: top;"|


|-
| style="text-align:center;" | 10
| style="vertical-align: top;"| '''More on External Merge Sort'''
[ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day10/Day_10_EMS.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day10/Day_10_EMS.pdf pdf] ]
 '''Worksheet:'''
 '''Video:'''
 [https://www.youtube.com/watch?v=z4S-puXQJPk&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=37 External Merge Sort (5:40)]
 [https://www.youtube.com/watch?v=Gt5JHc0OsJs&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=38 External merge sort on larger files (3:00)]
 [https://www.youtube.com/watch?v=48scgv9CXpE&list=PLWm33cHVRby7Av1hirp_TUWAnmWLvw8Nr&index=39 Optimizations for sorting (6:28)]
| style="vertical-align: top;"|
| style="vertical-align: top;"|


|-
| style="text-align:center;" | 11
| style="vertical-align: top;"| '''Indexing'''
[ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day11/Day_11_Indexes.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day11/Day_11_Indexes.pdf pdf] ]
 '''Worksheet:'''
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day11/DB-WS11a.ipynb Worksheet 11a]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day11/complaint.db Database for the worksheet]
| style="vertical-align: top;"| Ch. 13.1-13.3
| style="vertical-align: top;"|


|-
| style="text-align:center;" | 12
| style="vertical-align: top;"| '''Join part 1'''
[ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day12/Day12_Joins_p1.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day12/Day12_Joins_p1.pdf pdf] ]
 '''Worksheet:'''
| style="vertical-align: top;"| Ch. 15
| style="vertical-align: top;"|


|-
| style="text-align:center;" | 13
| style="vertical-align: top;"| '''Join part 2'''
[ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day13/Day13_Joins_p2.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day13/Day13_Joins_p2.pdf pdf] ]
 '''Worksheet:'''
| style="vertical-align: top;"| Ch. 15
| style="vertical-align: top;"|


|-
| style="text-align:center;" | 14
| style="vertical-align: top;"| '''Relational Algebra'''
[ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day14/Day_14_Algebra.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day14/Day_14_Algebra.pdf pdf] ]
 '''Worksheet:'''
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day14/DB-WS014a.ipynb Worksheet 9a]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day14/DB-WS14a_tree.png Picture used for the Worksheet]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day14/relation_algebra_v2.py Relational Algebra for pyton v2]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day14/display_tools.py Display helper]
| style="vertical-align: top;"| Ch. 2 and 16.3
| style="vertical-align: top;"| '''Exam on Dec 10th 16:00 in room 417-202 '''


|-
| style="text-align:center;" | 15
| style="vertical-align: top;"| '''Query Optimization'''
[ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day15/Day_15_Optimization.pptx pptx] ] [ [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day15/Day_15_Optimization.pdf pdf] ]
 '''Worksheet:'''
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day15/DB-WS015a.ipynb Worksheet 15a]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day15/DB-WS015b.ipynb Worksheet 15b]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day15/complaint.db Database for the worksheet]
 [http://open.gnu.ac.kr/lecslides/2020-2-DB/Lectures/Day15/relation_algebra_v2.py Relational Algebra for pyton v2]
 [http://open.gnu.ac.kr/lecslides/2020-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.