/* Database schema for www.goldenkey.edu.vn
  Create 2 table book_orders and customers
  This two table act as the initial tables for the shopping cart
  application in www.goldenkey.edu.vn
*/
create table courses (id int not null auto_increment,
                     course_name varchar(40) not null,
                     startdate date not null,
                     enddate date not null,
                     primary key (id)) type=InnoDB default charset=utf8;

create table customers (id int not null auto_increment,
                       name varchar(40) not null,                        
                       address varchar(40) not null,
                       primary key (id))type=InnoDB default charset=utf8;

create table enrolments (
                      id int not null auto_increment,
                      class_type varchar(50),
                      valid_from date not null,
                      valid_until date not null,
                      customer_id int not null,
                      foreign key (customer_id) references customers(id),
                      primary key (id)) type=InnoDB default charset=utf8;

create table course_customer (course_id int not null,
                             foreign key (course_id) references courses(id),
                             customer_id int not null,
                             foreign key (customer_id) references customers(id)
                            ) type=InnoDB default charset=utf8;

create table book_orders (id int not null auto_increment,
                    items varchar(30) not null,
                    saledate date not null,
                    customer_id int not null,
                    foreign key (customer_id) references customers(id),
                    primary key (id)) type=InnoDB default charset=utf8;


/* Add the values for customers table */
insert into customers (id  , name        , address)
           values    (null, 'Minh Hoang', 'Ha Noi');

insert into customers (id  , name        , address)
           values    (null, 'Ngoc Ha'   , 'Ha Noi');  

insert into customers (id  , name        , address)
           values    (null, 'Minh Lam'  , 'Ha Noi');  

insert into customers (id  , name        , address)
           values    (null, 'Truong Anh', 'Ha Noi');

insert into customers (id  , name      , address)
           values    (null, 'Khanh An', 'Hai Phong');

insert into customers (id  , name         , address)
           values    (null, 'Khanh Huyen', 'Thai Binh');  

insert into customers (id  , name       , address)
           values    (null, 'Mai Trang', 'Sai Gon');    
select * from customers            

/* Add the values for courses table */
insert into courses (id,   course_name     , startdate   , enddate)
           values  (null, 'English for IT', '2005-10-18', '2005-12-29')

insert into courses (id,   course_name     , startdate   , enddate)
           values  (null, 'English for IT', '2005-08-18', '2005-10-29')

insert into courses (id,   course_name, startdate   , enddate)
           values  (null, 'Let\'s go', '2005-10-18', '2005-12-29')

insert into courses (id,   course_name, startdate   , enddate)
           values  (null, 'Let\'s go', '2005-11-18', '2005-12-25')

insert into courses (id,   course_name             , startdate   , enddate)
           values  (null, 'International Business', '2005-11-18', '2005-12-25')

select * from courses

/* Add the values for book_orders table */
insert into book_orders (id  , items     , saledate   , customer_id)
           values      (null,'Let\'s go','2005-10-12', 3          );
insert into book_orders (id  , items     , saledate   , customer_id)
           values      (null,'Let\'s go','2005-10-12', 3          );
insert into book_orders (id  , items       , saledate   , customer_id)
           values      (null,'Let\'s go 2','2005-10-14', 4          );
insert into book_orders (id  , items         , saledate   , customer_id)
           values      (null,'Talk business','2005-10-16', 1          );
insert into book_orders (id  , items                 , saledate   , customer_id)
           values      (null,'International Express','2005-10-16', 4          );
insert into book_orders (id  , items                  , saledate   , customer_id)
           values      (null,'International Business','2005-10-16', 5          );

select * from book_orders
/* Add the values for course_customer table */
insert into course_customer (course_id, customer_id)  
           values          (1        , 2          )
insert into course_customer (course_id, customer_id)  
           values          (2        , 2          )
insert into course_customer (course_id, customer_id)  
           values          (3        , 1          )  
insert into course_customer (course_id, customer_id)  
           values          (4        , 3          )  

select * from course_customer;