56 lines
1.8 KiB
SQL
56 lines
1.8 KiB
SQL
CREATE DATABASE IF NOT EXISTS bsldb
|
|
CHARACTER SET `utf8`
|
|
COLLATE utf8_general_ci;
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- use database and create user
|
|
--
|
|
|
|
use bsldb;
|
|
grant all on bsldb.* to
|
|
'WT1CS1usr'@'localhost'
|
|
identified by 'ibz4life-WT1CS1';
|
|
flush privileges;
|
|
SHOW GRANTS FOR 'WT1CS1usr';
|
|
|
|
--
|
|
-- Table structure
|
|
--
|
|
|
|
CREATE TABLE IF NOT EXISTS `bsldb`.`benutzer` (
|
|
`benutzer_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`benutzer_name` varchar(30) NOT NULL,
|
|
`benutzer_pass` varchar(30) NOT NULL,
|
|
`anmelde_datum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE (`benutzer_name`),
|
|
PRIMARY KEY (`benutzer_id`)
|
|
) ;
|
|
|
|
CREATE TABLE IF NOT EXISTS `bsldb`.`nachfrager` (
|
|
`erfassungs_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`benutzer_id` int(11) NOT NULL,
|
|
`nachfr_typ` varchar(35) NOT NULL,
|
|
`nachfr_quali` tinyint(2) NOT NULL,
|
|
`nachfr_menge` int(11) NOT NULL,
|
|
`nachfr_lieferdatum` date,
|
|
`nachfr_datum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`nachfr_beschr` varchar(400),
|
|
CONSTRAINT `pk_nachfrager_erfassungs_id`
|
|
PRIMARY KEY (erfassungs_id),
|
|
CONSTRAINT `fk_nachfrager_benutzer_id`
|
|
FOREIGN KEY (benutzer_id) REFERENCES `benutzer` (benutzer_id)
|
|
) ;
|
|
|
|
CREATE TABLE IF NOT EXISTS `bsldb`.`anbieter` (
|
|
`angebot_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`benutzer_id` int(11) NOT NULL,
|
|
`erfassungs_id` int(11) NOT NULL,
|
|
`angebot_preis` DECIMAL(7,2) NOT NULL,
|
|
`angebot_datum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`angebot_file` varchar(63) NOT NULL,
|
|
PRIMARY KEY (`angebot_id`),
|
|
FOREIGN KEY (`benutzer_id`) REFERENCES `benutzer` (benutzer_id),
|
|
FOREIGN KEY (`erfassungs_id`) REFERENCES `nachfrager` (erfassungs_id)
|
|
) ;
|