rumulus.sql

《Java案例开发》源代码( 考勤系统的完整实现！） 包括数据库 网络通讯＆uml

CREATE TABLE SCOTT.Test
(
	s_ident	NUMBER NOT NULL,
	ident	VARCHAR2(32) UNIQUE NOT NULL,
	timing	NUMBER DEFAULT 0 NOT NULL,
	title	VARCHAR2(255),
	CONSTRAINT PK_Test PRIMARY KEY (s_ident )
);

CREATE TABLE SCOTT.QuestionList
(
	t_s_ident	NUMBER NOT NULL,
	q_s_ident	NUMBER NOT NULL
);

CREATE TABLE SCOTT.Question
(
	s_ident	NUMBER NOT NULL,
	ident	VARCHAR2(32) UNIQUE NOT NULL,
	title	VARCHAR2(255),
	timing	NUMBER DEFAULT 0 NOT NULL,
	CONSTRAINT PK_Question PRIMARY KEY (s_ident )
);

CREATE TABLE SCOTT.ObjectList
(
	q_s_ident	NUMBER NOT NULL,
	o_s_ident	NUMBER NOT NULL
);

CREATE TABLE SCOTT.Objective
(
	s_ident	NUMBER NOT NULL,
	ident	VARCHAR2(32) UNIQUE NOT NULL,
	can_view	NUMBER DEFAULT 0 NOT NULL,
	con_s_ident	NUMBER NOT NULL,
	CONSTRAINT PK_Objective PRIMARY KEY (s_ident )
);

CREATE TABLE SCOTT.FeedbackList
(
	q_s_ident	NUMBER NOT NULL,
	f_s_ident	NUMBER NOT NULL
);

CREATE TABLE SCOTT.Feedback
(
	s_ident	NUMBER NOT NULL,
	ident	VARCHAR2(32) UNIQUE NOT NULL,
	can_view	NUMBER DEFAULT 0 NOT NULL,
	title	VARCHAR2(255),
	con_s_ident	NUMBER NOT NULL,
	CONSTRAINT PK_Feedback PRIMARY KEY (s_ident )
);

CREATE TABLE SCOTT.ItemList
(
	q_s_ident	NUMBER NOT NULL,
	type NUMBER DEFAULT 0 NOT NULL,
	i_s_ident	NUMBER NOT NULL
);

CREATE TABLE SCOTT.Fib
(
	s_ident	NUMBER NOT NULL,
	ident	VARCHAR2(32) UNIQUE NOT NULL,
	maxchars	NUMBER DEFAULT 255 NOT NULL,
	con_s_ident	NUMBER NOT NULL,
	correctanswer	VARCHAR2(255) NOT NULL,
	CONSTRAINT PK_Fib PRIMARY KEY (s_ident )
);

CREATE TABLE SCOTT.Choice
(
	s_ident	NUMBER NOT NULL,
	ident	VARCHAR2(32) UNIQUE NOT NULL,
	rcardinality	NUMBER DEFAULT 1 NOT NULL,
	con_s_ident	NUMBER NOT NULL,
	shuffle	NUMBER DEFAULT 0 NOT NULL,
	CONSTRAINT PK_Choice PRIMARY KEY (s_ident )
);

CREATE TABLE SCOTT.ChoiceItemList
(
	c_s_ident	NUMBER NOT NULL,
	ci_s_ident	NUMBER NOT NULL
);

CREATE TABLE SCOTT.ChoiceItem
(
	s_ident	NUMBER NOT NULL,
	ident	VARCHAR2(32) UNIQUE NOT NULL,
	label	VARCHAR2(255),
	con_s_ident	NUMBER NOT NULL,
	iscorrect	NUMBER DEFAULT 0 NOT NULL,
	CONSTRAINT PK_ChoiceItem PRIMARY KEY (s_ident )
);

CREATE TABLE SCOTT.Content
(
	s_ident	NUMBER NOT NULL,
	ident	VARCHAR2(32) UNIQUE NOT NULL,
	label	VARCHAR2(255),
	CONSTRAINT PK_Content PRIMARY KEY (s_ident )
);

CREATE TABLE SCOTT.ContentList
(
	con_s_ident	NUMBER NOT NULL,
	con_i_s_ident	NUMBER NOT NULL,
	con_type	NUMBER DEFAULT 0 NOT NULL
);

CREATE TABLE SCOTT.ImageContent
(
	s_ident	NUMBER NOT NULL,
	ident	VARCHAR2(32) UNIQUE NOT NULL,
	label	VARCHAR2(255),
	uri	VARCHAR2(255),
	Width	NUMBER DEFAULT 0 NOT NULL,
	height	NUMBER DEFAULT 0 NOT NULL,
	CONSTRAINT PK_ImageContent PRIMARY KEY (s_ident )
);

CREATE TABLE SCOTT.TextContent
(
	s_ident	NUMBER NOT NULL,
	ident	VARCHAR2(32) UNIQUE NOT NULL,
	text	VARCHAR2(255),
	CONSTRAINT PK_TextContent PRIMARY KEY (s_ident )
);

CREATE TABLE SCOTT.Can_View
(
	s_ident	NUMBER NOT NULL,
	can_view	VARCHAR2(255),
	CONSTRAINT PK_Can_View PRIMARY KEY (s_ident )
);

CREATE TABLE SCOTT.TestRes
(
	s_ident	NUMBER NOT NULL,
	t_s_ident	NUMBER NOT NULL,
	score	NUMBER DEFAULT 0 NOT NULL,
	username	VARCHAR2(255),
	CONSTRAINT PK_TestRes PRIMARY KEY (s_ident )
);

CREATE SEQUENCE S_CHOICE INCREMENT BY 1 START WITH 1 MINVALUE 0 NOCYCLE NOCACHE NOORDER;

CREATE SEQUENCE S_CHOICEITEM INCREMENT BY 1 START WITH 1 MINVALUE 0 NOCYCLE NOCACHE NOORDER;

CREATE SEQUENCE S_CONTENT INCREMENT BY 1 START WITH 1 MINVALUE 0 NOCYCLE NOCACHE NOORDER;

CREATE SEQUENCE S_C_IMG INCREMENT BY 1 START WITH 1 MINVALUE 0 NOCYCLE NOCACHE NOORDER;

CREATE SEQUENCE S_C_TEXT INCREMENT BY 1 START WITH 1 MINVALUE 0 NOCYCLE NOCACHE NOORDER;

CREATE SEQUENCE S_FEEDBACK INCREMENT BY 1 START WITH 1 MINVALUE 0 NOCYCLE NOCACHE NOORDER;

CREATE SEQUENCE S_FIB INCREMENT BY 1 START WITH 1 MINVALUE 0 NOCYCLE NOCACHE NOORDER;

CREATE SEQUENCE S_OBJECTIVE INCREMENT BY 1 START WITH 1 MINVALUE 0 NOCYCLE NOCACHE NOORDER;

CREATE SEQUENCE S_QUESTION INCREMENT BY 1 START WITH 1 MINVALUE 0 NOCYCLE NOCACHE NOORDER;

CREATE SEQUENCE S_TEST INCREMENT BY 1 START WITH 16 MINVALUE 0 NOCYCLE NOCACHE NOORDER;

CREATE SEQUENCE S_VIEW INCREMENT BY 1 START WITH 1 MINVALUE 0 NOCYCLE NOCACHE NOORDER;

CREATE SEQUENCE S_TESTRES INCREMENT BY 1 START WITH 1 MINVALUE 0 NOCYCLE NOCACHE NOORDER;

CREATE OR REPLACE TRIGGER S_CHOICE_TRI 
 BEFORE INSERT OR UPDATE ON CHOICE
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
   tmpVar := 0;

   Select S_CHOICE.NextVal into tmpVar from dual;
   :NEW.S_IDENT := tmpVar;

   EXCEPTION
     WHEN OTHERS THEN
       RAISE;
END S_CHOICE_TRI;

CREATE OR REPLACE TRIGGER S_CHOICEITEM_TRI 
 BEFORE INSERT OR UPDATE ON CHOICEITEM
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
   tmpVar := 0;

   Select S_CHOICEITEM.NextVal into tmpVar from dual;
   :NEW.S_IDENT := tmpVar;

   EXCEPTION
     WHEN OTHERS THEN
       RAISE;
END S_CHOICEITEM_TRI;

CREATE OR REPLACE TRIGGER S_CONTENT_TRI 
 BEFORE INSERT OR UPDATE ON CONTENT
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
   tmpVar := 0;

   Select S_CONTENT.NextVal into tmpVar from dual;
   :NEW.S_IDENT := tmpVar;

   EXCEPTION
     WHEN OTHERS THEN
       RAISE;
END S_CONTENT_TRI;

CREATE OR REPLACE TRIGGER S_C_IMG_TRI 
 BEFORE INSERT OR UPDATE ON IMAGECONTENT
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
   tmpVar := 0;

   Select S_C_IMG.NextVal into tmpVar from dual;
   :NEW.S_IDENT := tmpVar;

   EXCEPTION
     WHEN OTHERS THEN
       RAISE;
END S_C_IMG_TRI;

CREATE OR REPLACE TRIGGER S_C_TEXT 
 BEFORE INSERT OR UPDATE ON TEXTCONTENT
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
   tmpVar := 0;

   Select S_C_TEXT.NextVal into tmpVar from dual;
   :NEW.S_IDENT := tmpVar;

   EXCEPTION
     WHEN OTHERS THEN
       RAISE;
END S_C_TEXT;

CREATE OR REPLACE TRIGGER S_FEEDBACK_TRI 
 BEFORE INSERT OR UPDATE ON FEEDBACK
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
   tmpVar := 0;

   Select S_FEEDBACK.NextVal into tmpVar from dual;
   :NEW.S_IDENT := tmpVar;

   EXCEPTION
     WHEN OTHERS THEN
       RAISE;
END S_FEEDBACK_TRI;

CREATE OR REPLACE TRIGGER S_FIB_TRI 
 BEFORE INSERT OR UPDATE ON FIB
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
   tmpVar := 0;

   Select S_FIB.NextVal into tmpVar from dual;
   :NEW.S_IDENT := tmpVar;

   EXCEPTION
     WHEN OTHERS THEN
       RAISE;
END S_FIB_TRI;

CREATE OR REPLACE TRIGGER S_OBJECTIVE_TRI 
 BEFORE INSERT OR UPDATE ON OBJECTIVE
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
   tmpVar := 0;

   Select S_OBJECTIVE.NextVal into tmpVar from dual;
   :NEW.S_IDENT := tmpVar;

   EXCEPTION
     WHEN OTHERS THEN
       RAISE;
END S_OBJECTIVE_TRI;

CREATE OR REPLACE TRIGGER S_QUESTION_TRI 
 BEFORE INSERT OR UPDATE ON QUESTION
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
   tmpVar := 0;

   Select S_QUESTION.NextVal into tmpVar from dual;
   :NEW.S_IDENT := tmpVar;

   EXCEPTION
     WHEN OTHERS THEN
       RAISE;
END S_QUESTION_TRI;

CREATE OR REPLACE TRIGGER S_TEST_TRI 
 BEFORE INSERT OR UPDATE ON TEST
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
   tmpVar := 0;

   Select S_TEST.NextVal into tmpVar from dual;
   :NEW.S_IDENT := tmpVar;

   EXCEPTION
     WHEN OTHERS THEN
       RAISE;
END S_TEST_TRI;

CREATE OR REPLACE TRIGGER S_TESTRES_TRI 
 BEFORE INSERT OR UPDATE ON TESTRES
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
   tmpVar := 0;

   Select S_TESTRES.NextVal into tmpVar from dual;
   :NEW.S_IDENT := tmpVar;

   EXCEPTION
     WHEN OTHERS THEN
       RAISE;
END S_TEST_TRI;