Relational Model

Size: px

Start display at page:

Download "Relational Model"

혜인 저
5 years ago
Views:

1 Relational Model

2 Entity

3 실체

4 Department

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

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

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

8 Key

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

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

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

12 Relation

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

14 Department include Student

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

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

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

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

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

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

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

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

23 Department include Student 1 1..n

24 Department include Student n

25 Department include Student Department n Student Department Student

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

27 Department include Student n

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

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

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

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

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

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

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

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

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

37 ERD

38 Entity Relation Diagram

39 dep id dep_name foundation homepage std_name std_number Department include Student

40 schema

41 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)

42 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)

key, unique dep_name varchar(20), unique, not null std_name

43 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)

44 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)

45 mysql

46 my sql

47 my structured query language

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

49 $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: a Type 'help;' or '\h' for help. Type '\c' to clear the buffer mysql>

50 >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: a Type 'help;' or '\h' for help. Type '\c' to clear the buffer mysql>

51 mysql> show databases; mysql> show databases; Database information_schema mysql rows in set (0.00 sec)

52 mysql> show databases; mysql> show databases; Database information_schema mysql rows in set (0.00 sec)

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

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

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

56 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)

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

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

59 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) -> );

60 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), -> CHECK (std_id >= AND std_id< ) -> );

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

62 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( , 정진근 );

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

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

65 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 > ;

66 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 = ;

67 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)

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

69 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( ) -> );

70 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(dep_id, std_id) -> );

71 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(dep_id, std_id), -> FOREIGN KEY(dep_id) REFERENCES Department, -> FOREIGN KEY(std_id) REFERENCES Student -> );

72 Library DB

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

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

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

85 ssbn: -1

86 ssbn book_name publisher img_url book_id resist_date SSBN of Book

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

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

89 방명록 DB

90 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})

91 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})

92 Object-relational mapping

93 mysql> SELECT FROM Student WHERE std_id = ;

94 학번 이름 정진근 박신후 김문범 정종혁

95 # 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.

96 # courses/models.py from django.db import models class Professor(models.Model): name = models.charfield(max_length=10) = models. field() 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()

97 # courses/models.py from django.db import models class Professor(models.Model): name = models.charfield(max_length=10) = models. field() 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() professor = models.foreignkey(professor)

98 # courses/models.py from django.db import models class Professor(models.Model): name = models.charfield(max_length=10) = models. field() 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() professors = models.manytomanyfield(professor)

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

100 $ python manage.py validate

101 $ python manage.py validate 0 errors found

102 $ python manage.py sqlall courses

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

104 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;

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",

105 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;

106 $ python manage.py syncdb

107 $ 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

108 $ python manage.py shell Python (r264:75706, Mar , 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.

109 >>> from courses.models import Professor >>> p1 = Professor(name='Kim Doguk', ='logue311@gmail.com', website=' >>> p1.save() >>> p2 = Professor(name='Oh Youngtaek', ='dongx3@gmail.com', website=' >>> p2.save() >>> professor_list = Professor.objects.all() >>> professor_list [<Professor: Professor object>, <Professor: Professor object>]

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

>>> p1.email = 'logue@sparcs.org' >>> p1.

111 >>> p1. = 'logue@sparcs.org' >>> p1.save() >>> p2. = '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.

112 >>> Professor.objects.all() >>> Professor.objects.filter(job_date=" ") >>> Professor.objects.filter( ) >>> Professor.objects.get(name contain="doguk") >>> Professor.objects.order_by("name") >>> Professor.objects.order_by("-job_date") >>> Professor.objects.filter( ).order_by("-job_date") >>> Professor.objects.order_by("name")[0] >>> Professor.objects.order_by("name")[0:2]

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

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

115 Admin Sites

116 django.contrib

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

autodiscover() urlpatterns = patterns('', ('^hello/$',

118 # 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)), )

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

120 $ python manage.py runserver

121

122

123

124

125

126

127

128 Next steps

129

130

131

132

10.ppt

10.ppt : SQL. SQL Plus. JDBC. SQL >> SQL create table : CREATE TABLE ( ( ), ( ),.. ) SQL >> SQL create table : id username dept birth email id username dept birth email CREATE TABLE member ( id NUMBER NOT NULL