仓库管理数据库系统设计

第1关：数据库表设计 - 核心表创建

#ÇëÔÚ´ËÌí¼ÓʵÏÖ´úÂë
########## Begin ##########
#ÔÚwarehouse_db¿âÖд´½¨warehouse±í
use warehouse_db;
create table warehouse(
    warehouseId int(11) primary key,
    area int(11) not null,
    phone int(11) not null
);

#ÔÚwarehouse_db¿âÖд´½¨component±í
create table component(
    componentId int(11) primary key,
    componentName varchar(20) not null,
    standard varchar(255) not null,
    price double(10,2) not null,
    `describe` varchar(255) not null
);


#ÔÚwarehouse_db¿âÖд´½¨supplier±í
create table supplier(
    supplyId int(11) primary key,
    name varchar(20) not null,
    address varchar(255) not null,
    phone int(11) not null,
    account bigint(18) not null
);


########## End ##########

第2关：数据库表设计 - 项目职员表

#请在此添加实现代码
########## Begin ##########
#在warehouse_db库中创建project表
use warehouse_db;
create table project(
 projectId int(11) primary key,
 projectBudget double(10,0) not null,
 commenceDate datetime not null
);


#在warehouse_db库中创建employee表
create table employee(
    employeeId int(11) primary key,
    name varchar(20) not null,
    age int(3) not null,
    designation varchar(20) not null,
    warehouseId int(11) not null,
    leaders varchar(20) not null,
    constraint FK_employee_warehouseId foreign key(warehouseId) references warehouse(warehouseId)
);


########## End ##########

第3关：数据库表设计 - 关联表

#请在此添加实现代码
########## Begin ##########
#在warehouse_db库中创建supply表
use warehouse_db;
create table supply(
    supplyId int(11) primary key,
    projectId int(11) not null,
    componentId int(11) not null,
    supplyCount int(11) not null,
    constraint FK_supply_supplyId foreign key(supplyId) references supplier(supplyId),
    constraint FK_supply_projectId foreign key(projectId) references project(projectId),
    constraint FK_supply_componentId foreign key(componentId) references component(componentId)
);


#在warehouse_db库中创建repertory表
create table repertory(
    warehouseId int(11) primary key,
    componentId int(11) not null,
    repertoryCount int(11) not null,
    constraint FK_repertory_warehouseId foreign key(warehouseId) references warehouse(warehouseId),
    constraint FK_repertory_component_componentId foreign key(componentId) references component(componentId)
);



########## End ##########