DROP TABLE IF EXISTS artist, record, customer, transaction;

CREATE TABLE artist
(
    id INT AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    PRIMARY KEY(id)
);


CREATE TABLE record
(
    id INT AUTO_INCREMENT,
    title VARCHAR(50),
    artist_id INT,
    genre TINYTEXT,
    year YEAR(4),
    price DECIMAL(10, 2) unsigned,
    PRIMARY KEY (id),
    FOREIGN KEY(artist_id)
        REFERENCES artist(id)

);

CREATE TABLE customer
(
   id INT AUTO_INCREMENT,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   dob DATE,
   email_address VARCHAR(255),
   address_1 VARCHAR(80),
   address_2 VARCHAR(80),
   postcode VARCHAR(10),
   PRIMARY KEY (id)

);



CREATE TABLE transaction

(

   id INT AUTO_INCREMENT,
   customer_id INT,
   delivery_method INT,
   order_date DATETIME,
   PRIMARY KEY (id),
   FOREIGN KEY (customer_id)
        REFERENCES customer (id)
   

);