lab4.sql

lab4.sql

create table `member` (
       `number` int,
       `name` varchar(256) not null,
       `address` varchar(512) not null,
       primary key (`number`)
);

create table `wine` (
       `name` varchar(256),
       `appellation` date,
       `vintage` numeric,
       `alcohol_deg` int not null,
       `vineyard` varchar(512) not null,
       `certificate` varchar(256),
       `country` varchar(256) not null,
       `count` int not null,
       primary key (`name`, `appellation`, `vintage`)
);

create table `bottle` (
       `wine_name` varchar(256),
       `wine_appellation` varchar(256),
       `wine_vintage` numeric,
       `number` numeric not null,
       `in_cellar` boolean not null,
       primary key (`number`, `wine_name`, `wine_appellation`, `wine_vintage`, `number`),
       foreign key (`wine_name`, `wine_appellation`, `wine_vintage`)
       references `wine` (`name`, `appellation`, `vintage`)
);

create table `taste` (
       `member_number` int not null,
       `wine_name` varchar(256) not null,
       `wine_appellation` varchar(256) not null,
       `wine_vintage` numeric not null,
       `bottle_number` numeric not null,
       `rating` varchar(45) not null,
       `date` date not null,
       primary key (`member_id`, `wine_name`, `wine_appellation`, `wine_vintage`, `bottle_number``),
       foreign key (`member_number`) references `member` (`number`)
       foreign key (`bottle_number`, `wine_name`, `wine_appellation`, `wine_vintage`)
       references `bottle` (`number`, `wine_name`, `wine_appellation`, `wine_vintage`)
);