Relational Model

Entity

실체

Department

학과코드 창립년도 홈페이지 학과코드 창립년도 홈페이지 학과코드 창립년도 홈페이지 학과코드 창립년도 홈페이지

학과코드 bis 창립년도 2001 홈페이지 bioeng. 학과코드 bs 창립년도 1972 홈페이지 bio. 학과코드 cs 창립년도 1972 홈페이지 cs. 학과코드 mas 창립년도 1972 홈페이지 mathsci.

학과이름 학과코드 창립년도 홈페이지 바이오및뇌공학과 bis 2001 bioeng. 생명과학과 bs 1972 bs. 전산학과 cs 1972 cs. 수리과학과 mas 1972 mathsci.

Key

학과이름 학과코드 창립년도 홈페이지 바이오및뇌공학과 bis 2001 bioeng. 생명과학과 bs 1972 bs. 전산학과 cs 1972 cs. 수리과학과 mas 1972 mathsci.

Candidate Key 학과이름 학과코드 창립년도 홈페이지 바이오및뇌공학과 bis 2001 bioeng. 생명과학과 bs 1972 bs. 전산학과 cs 1972 cs. 수리과학과 mas 1972 mathsci.

primary Key 학과이름 학과코드 창립년도 홈페이지 바이오및뇌공학과 bis 2001 bioeng. 생명과학과 bs 1972 bs. 전산학과 cs 1972 cs. 수리과학과 mas 1972 mathsci.

Relation

Department Student 학과이름학과코드창립년도홈페이지 학번 이름 바이오및뇌공학과 bis 2001 bioeng. 생명과학과 bs 1972 bs. 전산학과 cs 1972 cs. 수리과학과 mas 1972 mathsci. 20070764 정진근 20070315 박신후 20070109 김문범 20110125 정종혁

Department include Student

Department include Student bis 0764 정진근 cs 0315 박신후 0109 김문범

Department include Student Department 1 1..n Student Department Student

Department Student 학과이름학과코드창립년도홈페이지 학번 이름 바이오및뇌공학과 bis 2001 bioeng. 생명과학과 bs 1972 bs. 전산학과 cs 1972 cs. 20070764 정진근 20070315 박신후 20070109 김문범 수리과학과 mas 1972 mathsci.

Department Student 학과이름 학과코드 창립년도 홈페이지 바이오및뇌공학과 bis 2001 bioeng. 생명과학과 bs 1972 bs. 전산학과 cs 1972 cs. 학번 이름 학과 20070764 정진근 20070315 박신후 20070109 김문범 수리과학과 mas 1972 mathsci.

Department Student 학과이름 학과코드 창립년도 홈페이지 바이오및뇌공학과 bis 2001 bioeng. 생명과학과 bs 1972 bs. 전산학과 cs 1972 cs. 학번 이름 학과 20070764 정진근 bis 20070315 박신후 cs 20070109 김문범 cs 수리과학과 mas 1972 mathsci.

Department include Student bis 0764 정진근 cs 0315 박신후 0109 김문범 0125 정종혁

Department Student 학과이름 학과코드 창립년도 홈페이지 바이오및뇌공학과 bis 2001 bioeng. 생명과학과 bs 1972 bs. 전산학과 cs 1972 cs. 수리과학과 mas 1972 mathsci. 학번 이름 학과 20070764 정진근 bis 20070315 박신후 cs 20070109 김문범 cs 20110125 정종혁

Department include Student 1 1..n

Department include Student 0..1 1..n

Department include Student Department 0..1 1..n Student Department Student

Department include Student bis 0764 정진근 cs 0315 박신후 0109 김문범 0125 정종혁

Department include Student 0..1 1..n

Department include Student 0..n 1..n

Department include Student Department 0..n 1..n Student Department Student

Student 학번 이름 학과 복수전공 20070764 정진근 bis cs 20070315 박신후 cs null 20070109 김문범 cs null 20110125 정종혁 null null

Student 학번이름학과복수전공 복수전공 2 복수전공 3 복수 20070764 정진근 bis cs bs mas 20070315 박신후 cs null null null 20070109 김문범 cs null null null 20110125 정종혁 null null null null

Department Department- Student Student 학과이름학과코드창립년도홈페이지 학번 이름 바이오및뇌공학과 bis 2001 bioeng. 생명과학과 bs 1972 bs. 전산학과 cs 1972 cs. 수리과학과 mas 1972 mathsci. 20070764 정진근 20070315 박신후 20070109 김문범 20110125 정종혁

Department Department- Student Student 학과이름학과코드창립년도홈페이지 학과코드 학번 학번 이름 바이오및뇌공학과 bis 2001 bioeng. 생명과학과 bs 1972 bs. 전산학과 cs 1972 cs. 수리과학과 mas 1972 mathsci. bis 20070764 cs 20070315 cs 20070109 20070764 정진근 20070315 박신후 20070109 김문범 20110125 정종혁

Department Department- Student Student 학과이름학과코드창립년도홈페이지 학과코드 학번 학번 이름 바이오및뇌공학과 bis 2001 bioeng. 생명과학과 bs 1972 bs. 전산학과 cs 1972 cs. 수리과학과 mas 1972 mathsci. bis 20070764 cs 20070315 cs 20070109 cs 20070764 mas 20070764 20070764 정진근 20070315 박신후 20070109 김문범 20110125 정종혁

ERD

Entity Relation Diagram

dep id dep_name foundation homepage std_name std_number Department include Student

schema

Department include Student dep_id std_id char(3), not null integer, not null dep_id char(3), primary key, unique std_id integer, primary key, unique dep_name varchar(20), unique, not null std_name varchar(20), unique, not null foundation integer, not null hompage varchar(20)

Department include Student dep_id std_id char(3), not null integer, not null dep_id char(3), primary key, unique std_id integer, primary key, unique dep_name varchar(20), unique, not null std_name varchar(20), not null foundation integer, not null hompage varchar(20)

mysql

my sql

my structured query language

SQL Structured query language Standard language for interacting with a DBMS Data definition Manipulation

$mysql [ h host] [ u user] [ p[password]] [dbname] $mysql -u zzongaly -p Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 to server version: 3.23.34a Type 'help;' or '\h' for help. Type '\c' to clear the buffer mysql>

>mysql [ h host] [ u user] [ p[password]] [dbname] >mysql -u zzongaly ppassword Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 to server version: 3.23.34a Type 'help;' or '\h' for help. Type '\c' to clear the buffer mysql>

mysql> show databases; mysql> show databases; +--------------------+ Database +--------------------+ information_schema mysql +--------------------+ 2 rows in set (0.00 sec)

mysql> create database [dbname]; mysql> create database zzongaly -> ; Query OK, 1 row affected (0.04 sec)

mysql> use [dbname]; mysql> use zzongaly Database changed

mysql> show tables; mysql> show tables; Empty set (0.00 sec)

Department include Student dep_id std_id char(3), not null integer, not null dep_id char(3), primary key, unique std_id integer, primary key, unique dep_name varchar(20), unique, not null std_name varchar(20), not null foundation integer, not null hompage varchar(20)

Student std_id std_name integer, primary key, unique varchar(20), not null

mysql> CREATE TABLE tablename( fieldname fieldtype [NOT NULL], [fieldname fieldtype [NOT NULL],] UNIQUE(fieldname[, fieldname]), PRIMARY KEY(fieldname[, fieldname]) );

std_id std_name integer, primary key, unique varchar(20), not null mysql> CREATE TABLE tablename( fieldname fieldtype [NOT NULL], [fieldname fieldtype [NOT NULL],] UNIQUE(fieldname[, fieldname]), PRIMARY KEY(fieldname[, fieldname]) ); mysql> CREATE TABLE Student( -> std_id INTEGER, -> std_name VARCHAR(20) NOT NULL, -> PRIMARY KEY(std_id) -> );

std_id std_name integer, primary key, unique varchar(20), not null mysql> INSERT INTO tablename VALUES(value [,value]); mysql> INSERT INTO Student VALUES(20070764, 정진근 );

std_id std_name integer, primary key, unique varchar(20), not null mysql> INSERT INTO tablename (fieldname [, fieldname]) VALUES (value [,value]); mysql> INSERT INTO Student(std_id, std_name) -> VALUES(20070764, 정진근 );

std_id std_name integer, primary key, unique varchar(20), not null mysql> SELECT * FROM tablename; mysql> SELECT * FROM Student;

std_id std_name integer, primary key, unique varchar(20), not null mysql> SELECT fieldname FROM tablename; mysql> SELECT std_id FROM Student;

std_id std_name integer, primary key, unique varchar(20), not null mysql> SELECT * FROM tablename WHERE condition; mysql> SELECT * FROM Student WHERE std_id > 20110000;

std_id std_name integer, primary key, unique varchar(20), not null mysql> DELETE FROM tablename WHERE condition; mysql> SELECT FROM Student WHERE std_id = 20110125;

Department include Student dep_id std_id char(3), not null integer, not null dep_id char(3), primary key, unique std_id integer, primary key, unique dep_name varchar(20), unique, not null std_name varchar(20), not null foundation integer, not null hompage varchar(20)

include dep_id std_id char(3), not null integer, not null

dep_id std_id char(3), not null integer, not null mysql> CREATE TABLE tablename( fieldname fieldtype [NOT NULL], [fieldname fieldtype [NOT NULL],] UNIQUE(fieldname[, fieldname]), PRIMARY KEY(fieldname[, fieldname]) ); mysql> CREATE TABLE Dep_Stu( -> dep_id char(3) NOT NULL, -> std_id INTEGER NOT NULL, -> PRIMARY KEY( ) -> );

Library DB

isbn integer primary key book_name varchar(50) NOT NULL publisher varchar(50) img_url varchar(50)

C (21) + algorithm(24) = 10010(2)

C (2 1 ) + algorithm(2 4 ) = 10010 (2) = 12 (16)

ssbn: -1

ssbn book_name publisher img_url book_id resist_date SSBN of Book

ssbn book_name publisher img_url book_id resist_date SSBN of Book User user_id score

ssbn book_name publisher img_url book_id resist_date SSBN of Book rend rend_date return_date User user_id score

방명록 DB

from django.shortcuts import render_to_response import MySQLdb def dep_list(request): db = MySQLdb.connect(user= bbashong', db='dbname', passwd= password, host='localhost') cursor = db.cursor() cursor.execute('select * FROM Department') deps = [row[0] for row in cursor.fetchall()] db.close() return render_to_response( dep_list.html', { deps': deps})

from django.shortcuts import render_to_response from courses.models import Book def dep_list(request): deps = Department.objects.order_by( dep_id') return render_to_response( dep_list.html, { deps': deps})

Object-relational mapping

mysql> SELECT FROM Student WHERE std_id = 20110125;

학번 이름 20070764 정진근 20070315 박신후 20070109 김문범 20110125 정종혁

# settings.py DATABASES = { 'default': { 'ENGINE': 'django.db.backends.sqlite3', 'NAME': 'mydatabase' } }

# courses/models.py from django.db import models class Professor(models.Model): name = models.charfield(max_length=10) email = models.emailfield() website = models.urlfield() job_date = models.datefield() class Course(models.Model): code = models.charfield(max_length=30) department = models.charfield(max_length=20) semester = models.integerfield() year = models.integerfield()

# settings.py INSTALLED_APPS = ( 'django.contrib.auth', 'django.contrib.contenttypes', 'django.contrib.sessions', 'django.contrib.sites', 'django.contrib.messages', 'courserating.courses', )

$ python manage.py validate

$ python manage.py validate 0 errors found

$ python manage.py sqlall courses

$ python manage.py sqlall courses BEGIN; CREATE TABLE "courses_professor" ( "id" integer NOT NULL PRIMARY KEY, "name" varchar(10) NOT NULL, "email" varchar(75) NOT NULL, "website" varchar(200) NOT NULL, "job_date" date NOT NULL ) ;

CREATE TABLE "courses_course_professors" ( "id" integer NOT NULL PRIMARY KEY, "course_id" integer NOT NULL, "professor_id" integer NOT NULL REFERENCES "courses_professor" ("id"), UNIQUE ("course_id", "professor_id") ) ; CREATE TABLE "courses_course" ( "id" integer NOT NULL PRIMARY KEY, "code" varchar(30) NOT NULL, "department" varchar(20) NOT NULL, "semester" integer NOT NULL, "year" integer NOT NULL ) ; COMMIT;

$ python manage.py syncdb

$ python manage.py syncdb Creating table auth_permission Creating table courses_professor Creating table courses_course_professors Creating table courses_course You just installed Django's auth system, which means you don't have any superusers defined. Would you like to create one now? (yes/no): no Installing index for auth.permission model Installing index for courses.course_professors model No fixtures found.

$ python manage.py shell Python 2.6.4 (r264:75706, Mar 31 2010, 23:32:27) [GCC 4.3.4] on linux2 Type "help", "copyright", "credits" or "license" for more information. (InteractiveConsole) >>>

>>> from courses.models import Professor >>> p1 = Professor(name='Kim Doguk', email='logue311@gmail.com', website='http://twitter.com/logue311',job_date='2010-06-05') >>> p1.save() >>> p2 = Professor(name='Oh Youngtaek', email='dongx3@gmail.com', website='http://twitter.com/dongx3',job_date='2010-06-05') >>> p2.save() >>> professor_list = Professor.objects.all() >>> professor_list [<Professor: Professor object>, <Professor: Professor object>]

>>> p1.id 1 >>> p2.id 2

>>> p1.email = 'logue@sparcs.org' >>> p1.save() >>> p2.email = 'dongdongdong@sparcs.org' >>> p2.save()

>>> Professor.objects.all() >>> Professor.objects.filter(job_date="2010-06-05") >>> Professor.objects.filter( email contains="@sparcs.org ) >>> Professor.objects.get(name contain="doguk") >>> Professor.objects.order_by("name") >>> Professor.objects.order_by("-job_date") >>> Professor.objects.filter( email contains="@sparcs.org ).order_by("-job_date") >>> Professor.objects.order_by("name")[0] >>> Professor.objects.order_by("name")[0:2]

>>> p = Professor.objects.get(name="Kim Doguk") >>> p.name = "Kim Wheel" >>> p.save() >>> Professor.objects.all().update( job_date="2010-02-23") >>> p = Professor.objects.get(name contains="oh") >>> p.delete() >>> Professor.objects.filter( name contains="park").delete() >>> Professor.objects.all().delete()

>>> profs = Professor.objects.all() >>> print profs [<Professor: Kim Doguk>, <Professor: Oh Youngtaek>] >>> print profs.query SELECT "courses_professor"."id", "courses_professor"."name", "courses_professor"."email", "courses_professor"."website", "courses_professor"."job_date" FROM "courses_professor"

Admin Sites

django.contrib

# settings.py INSTALLED_APPS = ( 'django.contrib.auth', 'django.contrib.contenttypes', 'django.contrib.sessions', 'django.contrib.sites', 'django.contrib.messages', 'courserating.courses', 'django.contrib.admin', )

# urls.py from django.conf.urls.defaults import * from django.contrib import admin from views import * admin.autodiscover() urlpatterns = patterns('', ('^hello/$', hello), ('^current/$', current), ('^admin/', include(admin.site.urls)), )

# courses/admin.py from django.contrib import admin from courses.models import Professor, Course admin.site.register(professor) admin.site.register(course)

$ python manage.py runserver

Next steps

http://djangobook.com http://djangoproject.com http://djangosnippets.org