Dealership/databasecode.sql at master · mitraKa/Dealership · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
CREATE TABLE seller(
  id int(6) NOT NULL PRIMARY KEY,
  dealer_name varchar(20) NOT NULL,
  auction varchar(3) NOT NULL,
  location varchar(20) NOT NULL
    );

CREATE TABLE car_warehouse(
  sn int(9) NOT NULL PRIMARY KEY,
  make varchar(10) NOT NULL,
  model varchar(20) NOT NULL,
  year INT(4) not null,
  color varchar(10) NOT NULL,
  miles int(6) NOT NULL,
  cond varchar(10) NOT NULL,
  book_price numeric(12,2) NOT NULL,
  purchase_date numeric(12,2) NOT NULL,
  id int(6),
  FOREIGN key (id) REFERENCES seller(id)


);

CREATE TABLE repair_info(
 repair_id int(6) NOT NULL PRIMARY KEY AUTO_INCREMENT,
 problem varchar(40) NOT NULL,
 est_cost numeric(10,2),
 act_cost numeric(10,2),
 sn int(9) NOT NULL,
 FOREIGN KEY (sn) REFERENCES car_warehouse(sn)

);

CREATE TABLE customer(
    cust_id int(6) NOT null PRIMARY KEY,
    first_name varchar(20) not null,
    last_name varchar(20) not null,
    phone int(12) not null,
    address varchar(120) not null,
    city varchar(20) not null,
    state varchar(10)  not null,
    zip int(6) not null
 );

 CREATE TABLE employment_history(
   employment_id int(8) NOT NULL PRIMARY KEY AUTO_INCREMENT,
   employer_name varchar(20)  NOT NULL,
   title varchar(20)  NOT NULL,
   supervisor varchar(20)  NOT NULL,
   phone int(10)  NOT NULL,
   address varchar(20) NOT NULL,
   cust_id int(6) NOT NULL,
   start_date int(8),
   FOREIGN KEY (cust_id) REFERENCES customer(cust_id)
 );

 Create TABLE payments(
    pmt_id int(10) not null primary key AUTO_INCREMENT,
    cust_id int(6) NOT null,
    pmt_date int(8)  NOT NULL,
    paid_date int(8)  NOT NULL,
    amount numeric(12,2)  NOT NULL,
    bank_account int(10)  NOT NULL,
    FOREIGN KEY (cust_id) REFERENCES customer(cust_id)

 );

 CREATE TABLE employee_info(
     emp_id int(9) not null PRIMARY KEY AUTO_INCREMENT,
     first_name varchar(12) not null,
     last_name varchar(12) not null,
     phone int(110)
     );

  ALTER TABLE employee AUTO_INCREMENT = 100000001

  CREATE TABLE waranty(
         waranty_id int(5) not null PRIMARY KEY,
         items_covered varchar(120),
         deductible numeric(12,2)
 );

 CREATE TABLE car_sale(
  sn int(9) not null,
  cust_id int(6) not null,
  emp_id int(9) not null,
  sale_date int(8) not null,
  total_due numeric(12,2) not null,
  down_payment numeric(12 , 2) not null,
  finance_amount numeric(12 ,2) not null,
  commition numeric(12 ,2),
  PRIMARY KEY (sn , cust_id , emp_id),
  FOREIGN KEY (sn) REFERENCES car_warehouse(sn),
  FOREIGN KEY (cust_id) REFERENCES customer(cust_id),
  FOREIGN KEY (emp_id) REFERENCES employee(emp_id)
);

INSERT INTO payments(pmt_date ,paid_date,amount,bank_account,cust_id)
                 VALUES ( "20170306","20170307" ,"500" ,"123456789","100000" );
CREATE TABLE waranty_sale(
  wSaleId int(9)  NOT NULL primary KEY AUTO_INCREMENT,
  sn int(9) NOT NULL,
  emp_id int(9) NOT NULL,
  waranty_id int(5) NOT NULL,
  cust_id int(6)  NOT NULL,
  saleDate int(8)  NOT NULL,
  co_signer varchar(20) ,
  monthly_cost numeric(12,2) ,
  start_date int(8) NOT NULL,
  length int(6) NOT NULL,
  total_cost numeric(12,2) ,
  FOREIGN KEY (sn) REFERENCES car_warehouse(sn),
  FOREIGN KEY (emp_id) REFERENCES employee_info(emp_id),
  FOREIGN KEY (waranty_id) REFERENCES waranty(waranty_id),
  FOREIGN KEY (cust_id) REFERENCES customer(cust_id)


);