table.sql

这是我老师布置一个学习任务：保险管理系统

DROP DATABASE Assurance;
CREATE DATABASE Assurance;
USE Assurance;
CREATE TABLE  Customer           
(
	custom_id  VARCHAR(16) PRIMARY KEY,        
	custom_password  VARCHAR(16),               
	custom_property VARCHAR(16),                
	custom_name VARCHAR(30),              
	custom_address VARCHAR(80),            
	custom_remark VARCHAR(10),            
	leader VARCHAR(8),                     
        telephone VARCHAR(16),                  
        mobile_phone VARCHAR(11) ,              
        fax VARCHAR(16)    ,                    
        photo VARCHAR(40) ,                     
	register_date VARCHAR(10),            
	user_id VARCHAR(16) NOT NULL,               
        communicating_company VARCHAR(8)  ,                                               
);

CREATE TABLE Person_info         
(
	person_id VARCHAR(16) PRIMARY KEY,    
	name VARCHAR(8),                       
        target_type VARCHAR(1),                 
 	sex VARCHAR(2),                        
	identify VARCHAR(18),                 
	phone VARCHAR(16),                    
	mobile VARCHAR(11),                    
	zipcode VARCHAR(6),                    
	address VARCHAR(80),                   
	photo VARCHAR(40),                     
);

CREATE TABLE Target_car         
(
  target_id VARCHAR(16) PRIMARY KEY,          
  customer_id VARCHAR(16) ,                     
  person_id VARCHAR(16),                        
  car_no VARCHAR(20),                          
  factory_no VARCHAR(30),                      
  type VARCHAR(10),                            
  colour VARCHAR(10),                          
  motor_no VARCHAR(20),                        
  vin_no VARCHAR(18),                          
  frame_no VARCHAR(20),                        
  register_date VARCHAR(10),                  
  use_type VARCHAR(10),                         
                              
  cap_passanger VARCHAR(16),                        
  capability VARCHAR(16),                           
  pass_side_1 VARCHAR(40),                    
  pass_side_2 VARCHAR(40),                     
  outer_look VARCHAR(40),                     
  driver_side_1 VARCHAR(40),                  
  driver_side_2 VARCHAR(40), 
);                  

CREATE TABLE Target_ship         
(
  target_id VARCHAR(16) PRIMARY KEY,          
  customer_id VARCHAR(16) ,                    
  person_id VARCHAR(16),                      
  fleetno VARCHAR(10),                        
  shipcname VARCHAR(80),                       
  shipename VARCHAR(80),                       
  oldshipname VARCHAR(80),                     
  shipowner VARCHAR(100),                     
  makeyearmonth VARCHAR(10),                             
  country VARCHAR(20),                         
  stephull VARCHAR(20),                        
  makefactory VARCHAR(24),                     
  shiptype VARCHAR(10),                                   
  shipusage VARCHAR(10),                                
  shipport VARCHAR(20),                        
  sailscope VARCHAR(80),                       
  toncount VARCHAR(16),                       
  loadton VARCHAR(16),                        
  shiplength VARCHAR(16),                    
  shipwidth VARCHAR(16),                     
  shipdepth VARCHAR(16),                      
  seatcount VARCHAR(16),                           
  shipoutlooking VARCHAR(40),                 
  sailcertificate VARCHAR(40),                 
  certfication VARCHAR(40),                   
  shipvalue VARCHAR(16),                    
  currency VARCHAR(16),                            
);

CREATE TABLE  Target_prop          
(
  target_id VARCHAR(16) PRIMARY KEY,            
  customer_id VARCHAR(16) ,                    
  person_id VARCHAR(16)  ,                      
  fixedassets VARCHAR(16),                    
  workshophouse VARCHAR(16),                  
  building VARCHAR(16),                       
  currencyassets VARCHAR(16),                 
  raw_material VARCHAR(16),                   
  stock_commodity VARCHAR(16),               
  machine VARCHAR(16),                        
  computer VARCHAR(16),                       
  processing VARCHAR(16),                     
  replace_storage VARCHAR(16),                
  specialitem VARCHAR(16),                    
 );


CREATE TABLE Target_respon        
(
  target_id VARCHAR(16) PRIMARY KEY,            
  customer_id VARCHAR(16),                       
  person_id VARCHAR(16),                         
  total_rep VARCHAR(16),                      
  percase_rep VARCHAR(16),                    
  perdeath1 VARCHAR(16),                      
  person1 CHAR(5),                              
  perdeath2 VARCHAR(16),                      
  person2 CHAR(5),                              
  perservicefee1 VARCHAR(16),                 
  personfee1 CHAR(5),                           
  perservicefee2 VARCHAR(16),                 
  personfee2 CHAR(5),                           
  perservicelose VARCHAR(16),                 
  lose_of_rate VARCHAR(16),                    
);

CREATE TABLE Target_else          
(
  target_id VARCHAR(16) PRIMARY KEY,           
  customer_id VARCHAR(16)  ,                     
  person_id VARCHAR(16) ,                       
  insurename VARCHAR(20),                       
  description VARCHAR(100),                     
  etcitem1 VARCHAR(40),                       
  etcitem2 VARCHAR(40),                       
 );

CREATE TABLE Reqrying
(
  target_id VARCHAR(16) primary key,
  policy_no VARCHAR(20),
  sumamount VARCHAR(14),
  sumpremium VARCHAR(14),
  pol_year VARCHAR(4),
);

CREATE TABLE admin
(
  admin_no VARCHAR(16),
  admin_name VARCHAR(20),
  admin_password VARCHAR(20),
 
);