📄 troda.sql

📁 于毕业设计与论文以及做课题用-MSK Simulink simulation program for the design and graduation thesis topic, and makin
💻 SQL
📖 第 1 页 / 共 2 页
字号:
12 下一页
%% ============================================================
%%   Database name:  MASTER_MODEL                              
%%   DBMS name:      Sybase SQL Anywhere                       
%%   Created on:     99-7-29  9:00                             
%% ============================================================

if exists(select 1 from sys.systable where table_name='BOOK_AUTHOR' and table_type='BASE') then
   drop table BOOK_AUTHOR
end if;

if exists(select 1 from sys.systable where table_name='SALES_TRANSACT' and table_type='BASE') then
   drop table SALES_TRANSACT
end if;

if exists(select 1 from sys.systable where table_name='BOOKS' and table_type='BASE') then
   drop table BOOKS
end if;

if exists(select 1 from sys.systable where table_name='CUSTOMERS' and table_type='BASE') then
   drop table CUSTOMERS
end if;

if exists(select 1 from sys.systable where table_name='AUTHORS' and table_type='BASE') then
   drop table AUTHORS
end if;

if exists(select 1 from sys.systable where table_name='PUBLISHERS' and table_type='BASE') then
   drop table PUBLISHERS
end if;

%% ============================================================
%%   Table: PUBLISHERS                                         
%% ============================================================
create table PUBLISHERS
(
    PUBLISHERS_ID      char(10)              not null,
    PUBLISHERS_FNAME   varchar(30)           not null,
    primary key (PUBLISHERS_ID)
);

%% ============================================================
%%   Table: AUTHORS                                            
%% ============================================================
create table AUTHORS
(
    AUTHOR_ID          integer               not null
        default AUTOINCREMENT,
    AUTHOR_NAME        varchar(10)           not null,
    primary key (AUTHOR_ID)
);

%% ============================================================
%%   Table: CUSTOMERS                                          
%% ============================================================
create table CUSTOMERS
(
    CUST_ID            char(10)              not null,
    CUST_NAME          varchar(30)           not null,
    CUST_ADDRESS       varchar(30)                   ,
    CUST_CITY          varchar(20)                   ,
    CUST_ZIP           char(6)                       ,
    CUST_PHONE         varchar(18)                   ,
    primary key (CUST_ID)
);

%% ============================================================
%%   Table: BOOKS                                              
%% ============================================================
create table BOOKS
(
    ISBN               char(13)              not null,
    PUBLISHERS_ID      char(10)              not null,
    BOOK_TITLE         varchar(30)           not null,
    BOOK_CATEGORY      varchar(20)           not null,
    BOOK_SUMMARY       long varchar                  ,
    BOOK_PRICE         decimal(6,2)          not null,
    BOOK_REBATE        decimal(3,2)          not null,
    BOOK_STOCK_DATE    date                  not null,
    BOOK_STOCK_AMOUNT  integer               not null,
    PUBLISH_DATE       date                          ,
    BOOK_COVER         long binary                   ,
    primary key (ISBN)
);

%% ============================================================
%%   Table: SALES_TRANSACT                                     
%% ============================================================
create table SALES_TRANSACT
(
    TRANSACT_ID        integer               not null
        default AUTOINCREMENT,
    SALES_DATE         date                  not null,
    ISBN               char(13)              not null,
    SALES_AMOUNT       integer               not null,
    CUST_ID            char(10)              not null,
    primary key (TRANSACT_ID)
);

%% ============================================================
%%   Table: BOOK_AUTHOR                                        
%% ============================================================
create table BOOK_AUTHOR
(
    ISBN               char(13)              not null,
    AUTHOR_ID          integer               not null,
    primary key (ISBN, AUTHOR_ID)
);

alter table BOOKS
    add foreign key FK_BOOKS_BOOK_PUBL_PUBLISHE (PUBLISHERS_ID)
       references PUBLISHERS (PUBLISHERS_ID) on update restrict on delete restrict;

alter table SALES_TRANSACT
    add foreign key FK_SALES_TR_CUSTRES_T_CUSTOMER (CUST_ID)
       references CUSTOMERS (CUST_ID) on update restrict on delete restrict;

alter table SALES_TRANSACT
    add foreign key FK_SALES_TR_BOOK_SALE_BOOKS (ISBN)
       references BOOKS (ISBN) on update restrict on delete restrict;

alter table BOOK_AUTHOR
    add foreign key FK_BOOK_AUT_BOOK_AUTH_BOOKS (ISBN)
       references BOOKS (ISBN) on update restrict on delete restrict;

alter table BOOK_AUTHOR
    add foreign key FK_BOOK_AUT_BOOK_AUTH_AUTHORS (AUTHOR_ID)
       references AUTHORS (AUTHOR_ID) on update restrict on delete restrict;

%% ============================================================
%%   为表publishers录入数据                                        
%% ============================================================
INSERT INTO publishers VALUES (
	'302',
	'清华大学出版社');
INSERT INTO publishers VALUES (
	'111',
	'机械工业出版社');
INSERT INTO publishers VALUES (
	'301',
	'北京大学出版社');
INSERT INTO publishers VALUES (
	'951',
	'上海科学技术文献出版社');
INSERT INTO publishers VALUES (
	'505',
	'电子工业出版社');
INSERT INTO publishers VALUES (
	'906',
	'内蒙古大学出版社');

%% ============================================================
%%   为表authors录入数据                                       
%% ============================================================
INSERT INTO authors VALUES (
	1,
	'（美）Harley Hahn');
INSERT INTO authors VALUES (
	3,
	'王朝阳');
INSERT INTO authors VALUES (
	4,
	'（美）JimO''Donnell Eric Ladd');
INSERT INTO authors VALUES (
	5,
	'吕睿烜');
INSERT INTO authors VALUES (
	6,
	'（美）Peter Kent');
INSERT INTO authors VALUES (
	7,
	'（美）Phil James');
INSERT INTO authors VALUES (
	8,
	'王佑中');
INSERT INTO authors VALUES (
	9,
	'梁春梅');
INSERT INTO authors VALUES (
	10,
	'创德工作室');
INSERT INTO authors VALUES (
	11,
	'侯俊耀');
INSERT INTO authors VALUES (
	12,
	'何 渝');
INSERT INTO authors VALUES (
	13,
	'（美）Novell公司');
INSERT INTO authors VALUES (
	14,
	'程 鹏');
INSERT INTO authors VALUES (
	15,
	'金 海');
INSERT INTO authors VALUES (
	16,
	'刘新宇');
INSERT INTO authors VALUES (
	17,
	'曹立明');
INSERT INTO authors VALUES (
	18,
	'赖才金');
INSERT INTO authors VALUES (
	19,
	'（美）科默（Comer,D.E.）');
INSERT INTO authors VALUES (
	20,
	'斐胡福');
INSERT INTO authors VALUES (
	21,
	'周世雄');
INSERT INTO authors VALUES (
	22,
	'（美）Barrle Sosinsky');
INSERT INTO authors VALUES (
	23,
	'Microsoft公司');
INSERT INTO authors VALUES (
	30,
	'王新梅');
INSERT INTO authors VALUES (
	31,
	'李勇');
INSERT INTO authors VALUES (
	32,
	'康博创作室');
INSERT INTO authors VALUES (
	33,
	'冯志强');
INSERT INTO authors VALUES (
	29,
	'温为民');

%% ============================================================
%%   为表customers录入数据                                       
%% ============================================================
INSERT INTO customers VALUES (
	'PUBLIC00',
	'普通用户',
	'无',
	'无',
	'000000',
	'0000');
INSERT INTO customers VALUES (
	'NM000001',
	'内蒙古农业发展银行',
	'呼和浩特乌兰察布路18号',
	'呼和浩特',
	'010010',
	'(0471)4939664');
INSERT INTO customers VALUES (
	'SD000001',
	'山东大学',
	'山大学路17号',
	'济南市',
	'010010',
	'(0531)8940242');
INSERT INTO customers VALUES (
	'LN000001',
	'沈阳市财政局信息处',
	'沈阳市和平区北京北街103号',
	'沈阳市',
	'110000',
	'(024)22833660');
INSERT INTO customers VALUES (
	'LN000002',
	'大连科海电子',
	'大连市滨洲路79号',
	'大连市',
	'116000',
	'(0411)7951440');
INSERT INTO customers VALUES (
	'BJ000001',
	'北京华兴电子书店',
	'北京五一东路238号',
	'北京市',
	'100000',
	'(010)62641574');
INSERT INTO customers VALUES (
	'BJ000002',
	'水利电力科学院',
	'建国门外大街121号',
	'北京市',
	'100000',
	'(010)85454124');
INSERT INTO customers VALUES (
	'BJ000003',
	'北京市兆维集团',
	'北京市朝阳区九仙桥路2号',
	'北京市',
	'100000',
	'(010)65413547');
INSERT INTO customers VALUES (
	'BJ000004',
	'中国共青团中央',
	'北京市长安街48号',
	'北京市',
	'100000',
	'(010)68451241');
INSERT INTO customers VALUES (
	'TJ000001',
	'天津市新华书店',
	'天津市南开区东南角23号',
	'天津市',
	'300000',
	'(022)24133774');

%% ============================================================
%%   为表books录入数据                                       
%% ============================================================
INSERT INTO books VALUES (
	'7-302-00860-4',
	'302',
	'全球Internet网址薄（1998版）',
	'计算机',
	'本书包括全球最热门、最有应用价值的Intrenet站点，是每一位在Internet这广袤世界中探索求知的人们所必备的工具书和案头卷。
上网必备，行动指南。',
	56.00,
	0.70,
	'1998-10-12',
	10,
	'1998-05-03',
	null);
INSERT INTO books VALUES (
	'7-302-01034-X',
	'302',
	'中文FrontPage98网页设计',
	'计算机',
	'本书介绍FrontPage98的有关内容，
作者以由浅入深、由低级到高级的顺序为读者展示了利用
中文FrontPage98进行网页设计的全过程。全书包括大量简单实用的例子，
稍加屐就可应用于读者自己的页面。',
	73.00,
	0.65,
	'1999-04-14',
	7,
	'1999-03-10',
	null);
INSERT INTO books VALUES (
	'7-302-02621-1',
	'302',
	'中文Internet Explorer 4开发',
	'计算机',
	'本书是一本全面介绍Internet Explorer 4的著作。它从Internet Explorer 4的新特性入手，非常详细由浅入深地讲解这个软件。全书共分５个部分：入门知识、深入学习、相关程序、Web广播和Web管理员。本书适合各种
层次的使用者学习，图文并茂，细致透彻。',
12 下一页
💿 文件大小 383 K
👤 上传用户 KuFly
📂 所属分类 matlab例程
🏷️ 相关标签

#simulation #graduation #and #Simulink
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -