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),

   PRIMARY KEY (id)

);



CREATE TABLE transaction

(

   id INT AUTO_INCREMENT,

   customer_id INT,

   record_id INT,

   PRIMARY KEY (id),

   FOREIGN KEY (customer_id)

        REFERENCES customer (id),

   FOREIGN KEY (record_id)

        REFERENCES record (id)

);