create database acmeproducts;
use acmeproducts;

create table customers (
	id int(11) not null auto_increment,
	first_name varchar(100) not null,
	last_name varchar(100) not null,
	email_address varchar(100) not null,
	data_of_birth datetime default null,
	created_at timestamp not null default current_timestamp,
	primary key(id),
	unique key email_address (email_address)
) engine=InnoDB;

create table products (
	id int(11) not null auto_increment,
	name varchar(100) not null,
	unit_price double not null,
	created_at timestamp not null default current_timestamp,
	primary key(id),
	unique key name (name)
) engine=InnoDB;

create table sales (
	id int(11) not null auto_increment,
	product_id int(11) not null,
	customer_id int(11) not null,
	units_sold int(11) not null,
	time_of_sales timestamp not null default current_timestamp,
	primary key (id),
	key sales_ibfk_1 (product_id),
	key sales_ibfk_2 (customer_id)
)engine=InnoDB;

alter table sales
	add constraint sales_ibfk_1 foreign key (product_id) references products (id),
	add constraint sales_ibfk_2 foreign key (customer_id) references customers (id);