数据库系统概念数据库.sql,oracle导入

数据库系统概念书中数据,oracle导入

 

前言

最近在学习数据库系统概念时老师让我们装一个小的oracle数据库,老师想让我们直接装一个虚拟机,然后用toad来远程连接本地的数据库,大小有20多G,嫌弃太大了,一个这么小的数据库用20多g,然后觉得toad不好用,所有就打算自己来windows建一个数据库,中间遇到一些问题,所有记录一下

关于oracle的下载就不说了,然后数据库链接工具我选择了navicat for oracle mysql我也是用这个类型的工具,觉得还行,反应也快

 

数据库链接

然后就是数据库的链接

先是oracle的导入

先是书中数据库文件下载,官方有给出地址

sql文件地址

https://www.db-book.com/db6/lab-dir/sample_tables-dir/index.html

刚开始导入的时候疯狂说表不存在,后来才发现oracle 和mysql的结构还是有区别的,具体可以百度,然后sql文件里面的语句明显是有问题的,然后弄了很久,终于弄好了

先是,把所有的delect语句删了

然后直接在navicat中创建需要的表如下

参考了这位的mysql的表创建导入https://blog.csdn.net/a610786189/article/details/84832714?utm_medium=distribute.pc_relevant_download.none-task-blog-baidujs-1.nonecase&depth_1-utm_source=distribute.pc_relevant_download.none-task-blog-baidujs-1.nonecase

create table classroom
(building  varchar(15),
room_number varchar(7),
capacity  numeric(4,0),
primary key (building, room_number)
);
​
​
CREATE TABLE department 
(dept_name  VARCHAR(20), 
 building VARCHAR(15),
 budget NUMBER(12,2), 
 primary key(dept_name)
);
​
​
CREATE TABLE department 
(dept_name  VARCHAR(20), 
 building VARCHAR(15),
 budget NUMBER(12,2), 
 primary key(dept_name)
);
​
CREATE TABLE course
(course_id  VARCHAR(7), 
 title VARCHAR(50),
 dept_name VARCHAR(20),
 credits NUMBER(2,0), 
 primary key(course_id),
 foreign key(dept_name) references department
);
​
​
CREATE TABLE instructor 
(ID  VARCHAR(5), 
 name VARCHAR(20), 
 dept_name VARCHAR(20),
 salary NUMBER(8,2), 
 primary key(ID),
 foreign key(dept_name) references department
);
​
CREATE TABLE section (
course_id  VARCHAR(8),
sec_id varchar(8),
semester VARCHAR(6),
year NUMBER(4,0),
building VARCHAR(15),
room_number VARCHAR(7),
time_slot_id VARCHAR(4), 
primary key(course_id,sec_id,semester,year ),
foreign key(course_id) references course );
​
CREATE TABLE teaches (
ID  VARCHAR(5),
course_id  VARCHAR(8),
sec_id varchar(8),
semester VARCHAR(6),
year NUMBER(4,0),
primary key(ID,course_id,sec_id,semester,year ),
foreign key(course_id,sec_id,semester,year) references section,
foreign key(ID) references instructor
 
);
​
​
create table student
(ID varchar(5), 
 name varchar(20) not null, 
 dept_name varchar(20), 
 tot_cred numeric(3,0) check (tot_cred >= 0),
 primary key (ID),
 foreign key (dept_name) references department(dept_name)
 on delete set null
);
​
​
create table takes
(ID varchar(5), 
 course_id varchar(8),
 sec_id varchar(8), 
 semester varchar(6),
 year NUMBER(4,0),
 grade varchar(2),
 primary key (ID, course_id, sec_id, semester, year),
 foreign key (course_id,sec_id, semester, year) 
 references section (course_id,sec_id, semester, year) on delete cascade,
 foreign key (ID) references student (ID) on delete cascade
);
​
​
​
create table advisor
(s_ID varchar(5),
 i_ID varchar(5),
 primary key (s_ID),
 foreign key (i_ID) references instructor (ID) on delete set null,
 foreign key (s_ID) references student (ID)
 on delete cascade
);
​
​
create table time_slot
(time_slot_id varchar(4),
 day varchar(1),
 start_hr NUMBER(2) check (start_hr >= 0 and start_hr < 24),
 start_min NUMBER(2) check (start_min >= 0 and start_min < 60),
 end_hr NUMBER(2) check (end_hr >= 0 and end_hr < 24),
 end_min NUMBER(2) check (end_min >= 0 and end_min < 60),
 primary key (time_slot_id, day, start_hr, start_min)
);
​
​
create table prereq
(course_id varchar(8), 
 prereq_id varchar(8),
 primary key (course_id, prereq_id),
 foreign key (course_id) references course(course_id) on delete cascade,
 foreign key (prereq_id) references course(course_id)
);
​
​

 

 

最后直接导入删掉delect的sql文件就可以了

 

docker创建oracle数据库

还有同系大佬用docker 的,我试了一下,不是很便捷

而且也不是很喜欢单纯 Online SQL interpreter( https://www.db-book.com/db7/university-lab-dir/sqljs.html)

主要是刚开始导入sql文件的时候还报错了,后面才反应过来什么原因,然而那时候我已经把docker删了,装windows版本的了,所以就没在意了,具体可参考:

https://www.asc8384.top/2021/Database-System-Concepts.html