內蒙古大學 2017-2018《資料庫原理與應用A》實驗指導書 作業一
（本文中藍色字型為個人理解，非標準答案，僅供參考）
作業一 作業二 作業三 作業四 作業五
=======================================================================
作業一 資料庫模式設計及建立
一、實驗內容及說明
1.實驗目的：
理解和掌握資料庫 DDL 語言，能夠熟練地使用 SQL DDL 語句建立、修改和刪除資料庫、模型和基本表，對錶中資料進行更新操作。
2.實驗內容和要求：
理解和掌握 SQL DDL 語句和更新操作語句的語法，特別是各種引數的具體含義和使用方法；使用 SQL 語句建立、修改和刪除資料庫、模式和基本表。
3.實驗重點和難點：
實驗重點：建立資料庫、基本表。
實驗難點：建立基本表時，為不同的列選擇合適的資料型別，正確建立表級和列級完整性約束，如列值是否允許為空、主碼和外碼等。注意：資料完整性約束，可以在建立基本表時定義，也可以先建立表然後定義完整性約束；由於完整性約束的限制，被引用的表要先建立。
二、實驗步驟
（一）熟悉上機環境和 sqlplus 中的各種操作命令
（二）資料庫模式建立
1、建立學生關係模式
2、建立課程關係模式
3、建立選課關係模式
4、建立教師關係模式
5、建立系別關系模式
（三）SQL 對資料的 DDL 操作
1. 熟悉上機環境和 sqlplus 中的各種操作命令。
2. 用 SQL 語言對上述 1,2,3,4,5 五個表進行建立（注意，建表順序不一定是1,2,3,4,5）。
要建立這5張表，必須首先分析出建表的先後順序。
由於外碼的存在，如果不按順序建表，就會違反參照完整性，在SQL中的錯誤就是"未找到父項關鍵字"。
————————————————————————————————————————————————
1、對 學生表(Student) 分析
表中最後一行屬性 所在系(sdno) 是外碼，含義是這名學生的所在系。
那麼哪張表還有學生的所在系資訊呢？
縱觀5張表，只有 系別表(Dept)的 系編號(dno) 名稱與 所在系(sdno)接近
而且資料型別都是char，長度都是10，
系編號(dno)是 系別表(Dept)的主碼， 所在系(sdno)是 學生表(Student)的外碼。
所以外碼—— 所在系(sdno)的被參照表是 系別表(Dept)，參照表是 學生表(Student)
結論1：應該先建立系別表(Dept)，後建立學生表(Student)，含義是先建立系，才能招收學生
————————————————————————————————————————————————
2、對 課程表(Course) 分析
表中第三行屬性 教師(ctno) 是外碼，含義是這門課程的授課教師。
那麼哪張表還有課程的授課教師資訊呢？
縱觀5張表，只有 教師表(Dept)的 教師號(tno) 名稱與 教師(ctno)接近，
而且資料型別都是char，長度都是10，
教師號(tno)是 教師表(Teacher)的 主碼， 教師(ctno)是 課程表(Course)的外碼。
所以外碼—— 教師(ctno)的被參照表是 教師表(Teacher)，參照表是 課程表(Course)
結論2：應該先建立教師表(Teacher)，後建立課程表(Course)，含義是先有教師，才能授課
—————————————————————————————————————————————
3、對 選課表(SC) 分析
因為表中有兩個外碼，所以先分析學號(sno)，後分析課程號(cno)
I：先分析學號(sno)
表中第一行屬性 學號(sno) 是外碼，含義是選了課的這名學生的學號。
那麼哪張表還有學生的學號資訊呢？
縱觀5張表，只有 學生表(Student)的 學號(sno) 名稱與 學號(sno)相同，
而且資料型別都是char，長度都是6，
學號(sno)是 學生表(Student)的 主碼， 學號(sno)是 選課表(SC)的外碼。
所以外碼—— 學號(sno)的被參照表是 學生表(Student)，參照表是 選課表(SC)
結論3-1：應該先建立學生表(Student)，後建立選課表(SC)，含義是先有學生，才能選課
-----------------------------------------------------------------------------------------------------------------
Ⅱ：後分析課程號(cno)
表中第二行屬性 課程號(cno) 是外碼，含義是這名學生選了哪門課。
那麼哪張表還有課程資訊呢？
縱觀5張表，只有 課程表(Course)的 課程號(sno) 名稱與 課程號(sno)相同，
而且資料型別都是char，長度都是4，
課程號(sno)是 課程表(Course)的 主碼， 課程號(sno)是 選課表(SC)的外碼。
所以外碼—— 課程號(sno)的被參照表是 課程表(Course)，參照表是 選課表(SC)
結論3-2：應該先建立課程表(Course)，後建立選課表(SC)，含義是先有課程，才能選課
綜合結論3-1 與 結論3-2
結論3-1：應該先建立學生表(Student)，後建立選課表(SC)，含義是先有學生，才能選課
結論3-2：應該先建立課程表(Course)，後建立選課表(SC)，含義是先有課程，才能選課
得出結論3
結論3：應該先建立課程表(Course)和學生表(Student)，後建立選課表(SC)，含義是先有課程和學生，才能選課
———————————————————————————————————————————————
4、對 教師表(Teacher) 分析
表中最後一行屬性 所在系(sdno) 是外碼，含義是這名教師的所在系。
那麼哪張表還有教師的所在系資訊呢？
縱觀5張表，只有 系別表(Dept)的 系編號(dno) 名稱與 所在系(sdno)接近
而且資料型別都是char，長度都是10，
系編號(dno)是 系別表(Dept)的主碼， 所在系(sdno)是 教師表(Teacher)的外碼。
所以外碼—— 所在系(sdno)的被參照表是 系別表(Dept)，參照表是 教師表(Student)
結論4：應該先建立系別表(Dept)，後建立教師表(Student)，含義是先建立系，才能招聘教師
—————————————————————————————————————————————————
5、對 系別表(Dept) 分析
因為系別表(Dept)沒有外碼，所以不會違反參照完整性。
結論5：應該最先建立系別表(Dept)，以便被其他表參照
—————————————————————————————————————————————
綜合結論1到結論5：
結論1：應該先建立系別表(Dept)，後建立學生表(Student)，含義是先建立系，才能招收學生
結論2：應該先建立教師表(Teacher)，後建立課程表(Course)，含義是先有教師，才能授課
結論3：應該先建立課程表(Course)和學生表(Student)，後建立選課表(SC)，含義是先有課程和學生，才能選課
結論4：應該先建立系別表(Dept)，後建立教師表(Student)，含義是先建立系，才能招聘教師
結論5：應該最先建立系別表(Dept)，以便被其他表參照
得出建表順序：
①系別表(Dept)->②教師表(Teacher)->③課程表(Course)->④學生表(Student)->⑤選課表(SC)
SQL語言建表： （Oracle 10g測試通過）
create table dept /*建立系別表*/
(
dno char(10),
dname char(15) constraint dept_C_dname not null, /*not null只能是列級約束，非空屬性dname的列級完整性約束名是dept_C_dname*/
constraint dept_P_dno primary key(dno) /*最後一行沒有逗號，主碼dno的表級實體完整性約束名是dept_P_dno*/
);
create table teacher /*建立教師表*/
(
tno char(10),
tname char(8) constraint teacher_C_tname not null, /*not null只能是列級約束，非空屬性tname的列級完整性約束名是teacher_C_tname*/
tsex char(2),
tage smallint,
prof char(10),
tdno char(10),
constraint teacher_P_tno primary key(tno), /*主碼dno的表級實體完整性約束名是teacher_P_tno*/
constraint teacher_R_tdno foreign key(tdno) references dept(dno) /*最後一行沒有逗號，外碼tdno的表級參照完整性約束名是teacher_R_tdno*/
); tno char(10),
tname char(8) constraint teacher_C_tname not null, /*not null只能是列級約束，非空屬性tname的列級完整性約束名是teacher_C_tname*/
tsex char(2),
tage smallint,
prof char(10),
tdno char(10),
constraint teacher_P_tno primary key(tno), /*主碼dno的表級實體完整性約束名是teacher_P_tno*/
constraint teacher_R_tdno foreign key(tdno) references dept(dno) /*最後一行沒有逗號，外碼tdno的表級參照完整性約束名是teacher_R_tdno*/
);
create table course /*建立課程表*/
(
cno char(4),
cname char(20),
ctno char(10),
ccredit smallint,
constraint course_P_cno primary key(cno), /*主碼cno的表級實體完整性約束名是course_P_cno*/
constraint course_R_ctno foreign key(ctno) references teacher(tno) /*最後一行沒有逗號，外碼ctno的表級參照完整性約束名是course_R_ctno*/
); cno char(4),
cname char(20),
ctno char(10),
ccredit smallint,
constraint course_P_cno primary key(cno), /*主碼cno的表級實體完整性約束名是course_P_cno*/
constraint course_R_ctno foreign key(ctno) references teacher(tno) /*最後一行沒有逗號，外碼ctno的表級參照完整性約束名是course_R_ctno*/
);
create table student /*建立學生表*/
(
sno char(6),
sname char(8),
ssex char(2),
sage smallint,
sdno char(10),
constraint student_P_sno primary key(sno), /*主碼sno的表級實體完整性約束名是student_P_sno*/
constraint student_R_sdno foreign key(sdno) references dept(dno) /*最後一行沒有逗號，外碼sdno的表級參照完整性約束名是student_R_sdno*/
); sno char(6),
sname char(8),
ssex char(2),
sage smallint,
sdno char(10),
constraint student_P_sno primary key(sno), /*主碼sno的表級實體完整性約束名是student_P_sno*/
constraint student_R_sdno foreign key(sdno) references dept(dno) /*最後一行沒有逗號，外碼sdno的表級參照完整性約束名是student_R_sdno*/
);
create table sc /*建立選課表*/
(
sno char(6),
cno char(4),
grade smallint,
constraint sc_P_sno_cno primary key(sno,cno), /*主碼(sno,cno)的表級實體完整性約束名是sc_P_sno_cno*/
constraint sc_R_sno foreign key(sno) references student(sno), /*外碼sno的表級參照完整性約束名是sc_R_sno*/
constraint sc_R_cno foreign key(cno) references course(cno) /*最後一行沒有逗號，外碼cno的表級參照完整性約束名是sc_R_cno*/
); sno char(6),
cno char(4),
grade smallint,
constraint sc_P_sno_cno primary key(sno,cno), /*主碼(sno,cno)的表級實體完整性約束名是sc_P_sno_cno*/
constraint sc_R_sno foreign key(sno) references student(sno), /*外碼sno的表級參照完整性約束名是sc_R_sno*/
constraint sc_R_cno foreign key(cno) references course(cno) /*最後一行沒有逗號，外碼cno的表級參照完整性約束名是sc_R_cno*/
);
3. 用“desc <表名>;” 命令檢查所構建表的表結構是否正確。
desc dept;
desc teacher;
desc course;
desc student;
desc sc;
4. 用“select * from user_tables;” 命令檢查當前使用者所建表情況是否正確。
select * from user_tables;
5. 用“select * from user_constraints;”命令檢查所構建表的約束是否正確。
熟悉各約束定義，R: foreign key，P: primary key， C: Not Null 或 Check，U: Unique
select * from user_constraints;
6. 給 Student 表增加一個地址(address，長度為 10 的字串)屬性。
alter table student add( address char(10) );
執行後，需要用desc，檢視錶結構是否增加上address屬性
desc student;
7. 將 Student 表地址(address)資料型別改為長度為 13 的字串。
alter table student modify( address char(13) );
執行後，需要用desc，檢視錶結構是否將address屬性的資料型別改為char(13)
desc student;
8. 給 Course 表增加一個開課學期（term 資料型別 smallint）屬性。
alter table course add( term smallint );
執行後，需要用desc，檢視錶結構是否增加上term屬性
desc course;
（四）SQL 對資料的更新操作
1. 在 Dept 表中輸入以下5條合法記錄。
insert into dept values('001','cs');
insert into dept values('002','is');
insert into dept values('003','ma');
insert into dept values('004','ea');
insert into dept values('005','sw');
2. 在 Teacher 表中輸入以下7條合法記錄。
insert into teacher values('70001','李勇','男',46,'教授','001');
insert into teacher values('74005','任白','男',42,'副教授','003');
insert into teacher values('80003','李明','女',36,'講師','003');
insert into teacher values('72004','王華平','女',50,'教授','005');
insert into teacher values('74036','陳剛','男',42,'教授','001');
insert into teacher values('79058','張雪','女',36,'副教授','004');
insert into teacher values('74025','蘇紅','女',36,'副教授','002');
3. 在 course 表中輸入以下6條合法記錄。
因為course表增加了term屬性，所以插入值時最後一個屬性term賦空值null
insert into course values('1001','資料庫','70001',4,null);
insert into course values('2002','數學','80003',2,null);
insert into course values('1004','作業系統','74036',3,null);
insert into course values('1005','資料結構','70001',4,null);
insert into course values('3001','英語','79058',3,null);
insert into course values('5012','生物資訊學','72004',5,null);
4. 在 student 表中輸入以下8條合法記錄。
因為student表增加了address屬性，所以插入值時最後一個屬性address賦空值null
insert into student values('209001','李勇','男',20,'001',null);
insert into student values('209002','劉晨','女',19,'002',null);
insert into student values('209003','王敏','女',18,'004',null);
insert into student values('209004','張立','男',19,'003',null);
insert into student values('209005','趙強','男',21,'002',null);
insert into student values('209006','陳偉','男',19,'001',null);
insert into student values('209007','王珊','女',21,'005',null);
insert into student values('209008','張華','男',19,'001',null);
5. 在 SC 表中輸入以下10 條合法記錄。(注意，有些課程可有多人選，有些課程可能沒人選)
insert into sc values('209001','1001',92);
insert into sc values('209001','2002',85);
insert into sc values('209001','1005',88);
insert into sc values('209002','2002',90);
insert into sc values('209002','1005',80);
insert into sc values('209003','2002',67);
insert into sc values('209003','3001',56);
insert into sc values('209004','1001',87);
insert into sc values('209004','1005',69);
insert into sc values('209005','1001',82);
insert into sc values('209006','3001',87);
insert into sc values('209007','1004',65);
insert into sc values('209008','1001',84);
6. 用“SELECT * FROM <表名>;”命令檢查表中資料的正確性。
select * from dept;
select * from teacher;
select * from course;
select * from student;
select * from sc;
7. 給 student 表建立索引 index_name，按 sname 升序。
create index index_name on student(sname);
用select * from user_indexes查詢建立的索引
8. 建立檢視 view1，列出學生選課情況，要求有 sname,cname,grade 三個欄位。
create view view1 as
select sname,cname,grade
from student,course,sc
where student.sno=sc.sno
and course.cno=sc.cno;
用select * from user_views;查詢建立的檢視
用select * from view1;查詢檢視view1
9. 建立檢視 view2，列出學生基本資訊，要求有 sno,ssex,sage,birthday 四個欄位。
create view view2 as
select sno,ssex,sage,to_char(sysdate,'yyyy')-sage birthday /*也可以直接用年份-sage求出生年份，如2018-sage birthday*/
from student;
用select * from user_views;查詢建立的檢視
用select * from view2;查詢檢視view2
10. 建立“cs”系學生成績檢視 JSGV（sno,cno,grade）。
create view JSGV as
select sc.sno,cno,grade
from student,sc,dept
where student.sno=sc.sno
and student.sdno=dept.dno
and dname='cs';
用select * from user_views;查詢建立的檢視
用select * from JSGV;查詢檢視JSGV
（五）簡單的檢查和除錯語句
1. 用“select * from user_indexes;”檢查當前使用者建立索引情況是否正確。
select * from user_indexes;
2. 用“select * from user_views;” 檢查當前使用者建立檢視情況是否正確。
select * from user_views;
3. 用“select * from user_tables;” 檢查當前使用者建立表情況是否正確。
select * from user_tables;
4. 用“select * from user_constraints;” 檢查當前使用者建立表情況是否正確。
select * from user_constraints;
5. 用“desc”檢查當前表的表結構。
desc dept;
desc teacher;
desc course;
desc student;
desc sc;
6. 用“show user”顯示當前使用者。
show user;
=======================================================================
作業一 作業二 作業三 作業四 作業五
相關文章
- 資料庫原理與應用----實驗1:Oracle基本操作資料庫Oracle
- 圖資料庫 NebulaGraph 的 Java 資料解析實踐與指導資料庫Java
- 資料庫原理實驗指導（三）使用SQL語言進行簡單查詢【轉載csdn】資料庫SQL
- 從Forrester白皮書看國內重點行業圖資料庫的應用REST行業資料庫
- 作業系統實驗6之訊號量的實現與應用作業系統
- 資料標籤與指標在金融行業的應用指標行業
- 現代作業系統-原理與實現【讀書筆記】作業系統筆記
- 《大學生就業指導》第一課：就業決策 筆記就業筆記
- 實驗吧 —— web完整滲透測試實驗指導書（圖片版）Web
- ASP與資料庫應用資料庫
- 資料庫課程作業筆記 - 驗收資料庫筆記
- 大資料技術原理與應用大資料
- 同一個server內將資料從A資料庫導到B資料庫Server資料庫
- MYSQL學習與實驗（一）——資料庫定義與操作MySql資料庫
- Mysql資料庫應用(一)MySql資料庫
- 《大資料：技術與應用實踐指南》圖書資訊大資料
- 【python基礎語法實踐應用】合肥工業大學python實驗一題解Python
- 大資料技術原理與應用——大資料概述大資料
- openGauss資料庫日誌管理指導資料庫
- C語言程式設計實驗指導書 王明衍pdfC語言程式設計
- 資料庫應用優化（一）資料庫優化
- 作業系統之“實驗一”作業系統
- .NET 應用架構指導應用架構
- 向雲再出發：如資料般飛馳的內蒙古
- 資料驅動運營成功案例——內蒙古國大藥房
- Oracle資料庫學習應用：經驗分享Oracle資料庫
- 麒麟作業系統下管理國內外主流資料庫作業系統資料庫
- 物件導向與資料庫物件資料庫
- Hulu大資料架構與應用經驗大資料架構
- UT 資料庫日常維護指導手冊資料庫
- VB與資料庫應用一例----生成EXCEL表 (轉)資料庫Excel
- 資料庫應用開發一、vs資料庫
- 50張家暢實驗一作業
- 資料結構 — 並查集的原理與應用資料結構並查集
- Java 2實用教程（第三版）實驗指導與習題解答and實驗模版程式碼及答案 （三）Java
- 大學生創業指導——A Student’s Guide to Startups創業GUIIDE
- 資料庫系統原理（思維導圖）資料庫
- 對《windows作業系統原理》一書的評論 (轉)Windows作業系統