數(shù)據(jù)庫原理實(shí)驗(yàn)報(bào)告

天少云華學(xué)院實(shí)驗(yàn)報(bào)告課程：數(shù)據(jù)庫原理學(xué)生姓名 天少 專業(yè)班級(jí) 計(jì)算機(jī)卓越15-1 學(xué) 號(hào) XXXXXXXXXXXX 指導(dǎo)教師 云華 實(shí)驗(yàn)日期 2017年 11月 3 日 一、實(shí)驗(yàn)內(nèi)容及要求我校田徑運(yùn)動(dòng)會(huì)中設(shè)置了各類比賽，每一個(gè)比賽類別有類別編號(hào)、類別名稱和主管等屬性，每個(gè)比賽類別包含很多比賽項(xiàng)目；每個(gè)比賽項(xiàng)目有項(xiàng)目編號(hào)、項(xiàng)目名稱、比賽時(shí)間和級(jí)別等屬性；各個(gè)院系的代表團(tuán)有團(tuán)編號(hào)、團(tuán)名稱、領(lǐng)隊(duì)等屬性，每一代表團(tuán)有多名運(yùn)動(dòng)員組成，運(yùn)動(dòng)員有編號(hào)，姓名，年齡，性別等屬性；每一名運(yùn)動(dòng)員可以參加多個(gè)比賽項(xiàng)目，每一比賽項(xiàng)目也有多名運(yùn)動(dòng)員參加，運(yùn)動(dòng)員參加比賽有成績(jī)屬性，成績(jī)限定在07分。任務(wù)要求：1）根據(jù)描述完成E-R圖的設(shè)計(jì)；2）根據(jù)自己設(shè)計(jì)的E-R圖完成數(shù)據(jù)表的創(chuàng)建（包含主外鍵）；3）創(chuàng)建視圖，顯示每個(gè)代表團(tuán)的運(yùn)動(dòng)員數(shù)量及對(duì)應(yīng)的團(tuán)隊(duì)名稱；4）創(chuàng)建視圖，顯示每個(gè)運(yùn)動(dòng)員的姓名及參賽項(xiàng)目數(shù)；5）查詢得分最高的運(yùn)動(dòng)員姓名；6）創(chuàng)建觸發(fā)器，針對(duì)“參加關(guān)系表“，當(dāng)發(fā)生有更新（UPDATE）數(shù)據(jù)的操作時(shí)，操作完成后，顯示更新前和更新后的數(shù)據(jù)。（參考觸發(fā)器ppt）7）分析每個(gè)關(guān)系表上的函數(shù)依賴情況，根據(jù)函數(shù)依賴情況初步判斷每個(gè)關(guān)系表滿足的范式級(jí)別。（文字描述）二、實(shí)驗(yàn)結(jié)果及代碼關(guān)系模式：比賽類別（類別編號(hào)，名稱，主管）比賽項(xiàng)目（比賽項(xiàng)目編號(hào)，類別編號(hào)，名稱，等級(jí)，時(shí)間）運(yùn)動(dòng)員（運(yùn)動(dòng)員編號(hào)，代表團(tuán)編號(hào)，年齡，姓名，性別）參加（比賽項(xiàng)目編號(hào)，運(yùn)動(dòng)員編號(hào)，成績(jī)）代表團(tuán)（代表團(tuán)編號(hào)，名稱，領(lǐng)隊(duì)）E-R圖編號(hào)名稱主管比賽類別包括代表團(tuán)性別姓名年齡編號(hào)運(yùn)動(dòng)員成績(jī)參加名稱等級(jí)時(shí)間編號(hào)比賽項(xiàng)目 1 n m n構(gòu)成 成成ehng n編號(hào) 1名稱領(lǐng)隊(duì)代碼：CREATE DATABASE YUNDONGHUIUSE YUNDONGHUIGOCREATE TABLE Type -運(yùn)動(dòng)類型(t_id VARCHAR(4) PRIMARY KEY,t_name VARCHAR(20),t_fuze VARCHAR(20)CREATE TABLE Project(t_id VARCHAR(4) FOREIGN KEY REFERENCES Type(t_id),p_id VARCHAR(4) PRIMARY KEY,p_name VARCHAR(20),p_class VARCHAR(20),p_date VARCHAR(30)CREATE TABLE Daibiao(d_id VARCHAR(4) PRIMARY KEY,d_name VARCHAR(20),d_people VARCHAR(20) )CREATE TABLE Runner(d_id VARCHAR(4) FOREIGN KEY REFERENCES Daibiao(d_id), r_id VARCHAR(4) PRIMARY KEY, r_name VARCHAR(20), r_age int, r_sex VARCHAR(20) CHECK(r_sex IN(男,女)CREATE TABLE Paticipate(p_id VARCHAR(4) FOREIGN KEY REFERENCES Project(p_id),r_id VARCHAR(4) FOREIGN KEY REFERENCES Runner(r_id),p_grade intPRIMARY KEY(p_id,r_id)INSERT INTO TypeVALUES (101,田徑,劉備);INSERT INTO TypeVALUES(102,體操,關(guān)羽);INSERT INTO TypeVALUES(103,球類,張飛);INSERT INTO ProjectVALUES(101,201,長(zhǎng)跑,一等,2017-01-01);INSERT INTO ProjectVALUES(101,202,短跑,一等,2017-01-02);INSERT INTO ProjectVALUES(102,203,健美操,二等,2017-01-03);INSERT INTO ProjectVALUES(103,204,乒乓球,三等,2017-01-04);INSERT INTO ProjectVALUES(103,205,籃球,一等,2017-01-05);INSERT INTO DaibiaoVALUES(401,小學(xué)領(lǐng)導(dǎo)團(tuán),小學(xué));INSERT INTO DaibiaoVALUES(402,中學(xué)領(lǐng)導(dǎo)團(tuán),中學(xué));INSERT INTO DaibiaoVALUES(403,初中領(lǐng)導(dǎo)團(tuán),初中);INSERT INTO DaibiaoVALUES(404,高中領(lǐng)導(dǎo)團(tuán),高中);INSERT INTO DaibiaoVALUES(405,大學(xué)領(lǐng)導(dǎo)團(tuán),大學(xué));INSERT INTO RunnerVALUES(401,301,張三,18,女);INSERT INTO RunnerVALUES(402,302,李四,19,女);INSERT INTO RunnerVALUES(403,303,王二,20,男);INSERT INTO RunnerVALUES(404,304,孫五,21,男);INSERT INTO RunnerVALUES(405,305,吳六,25,女);INSERT INTO PaticipateVALUES(201,301,80);INSERT INTO PaticipateVALUES(202,302,85);INSERT INTO PaticipateVALUES(203,303,90);INSERT INTO PaticipateVALUES(204,304,91);INSERT INTO PaticipateVALUES(205,305,95);-創(chuàng)建視圖，顯示每個(gè)代表團(tuán)的運(yùn)動(dòng)員數(shù)量及對(duì)應(yīng)的團(tuán)隊(duì)名稱CREATE VIEW 團(tuán)隊(duì)名稱以及人數(shù)ASSELECT d_name,COUNT(r_id) AS numFROM Daibiao,RunnerWHERE Daibiao.d_id=Runner.d_idGROUP BY d_name-創(chuàng)建視圖，顯示每個(gè)運(yùn)動(dòng)員的姓名及參賽項(xiàng)目數(shù)CREATE VIEW 運(yùn)動(dòng)員姓名以及團(tuán)隊(duì)名稱ASSELECT r_name AS 姓名,COUNT(p_id) AS 項(xiàng)目數(shù)FROM Runner a,Paticipate cWHERE a.r_id=c.r_id GROUP BY r_name-查詢得分最高的運(yùn)動(dòng)員姓名SELECT r_name AS 得分最高的運(yùn)動(dòng)員FROM RunnerWHERE r_id=(SELECT r_idFROM PaticipateWHERE p_grade=(SELECT MAX(p_grade) FROM Paticipate)-創(chuàng)建觸發(fā)器，針對(duì)“參加關(guān)系表“，當(dāng)發(fā)生有更新（UPDATE）數(shù)據(jù)的操作時(shí)，操作完成后，顯示更新前和更新后的數(shù)據(jù)。 CREATE TRIGGER a_update ON Paticipate AFTER UPDATE A