Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*Datenbank erstellen (löschen wenn vorhanden)*/
- drop database if exists Skiverleih;
- create database Skiverleih;
- use Skiverleih;
- /*Tabellen erstellen (löschen wenn vorhanden)*/
- drop table if exists PrivatKunde, FirmenKunde, Kunde, RabattGruppe, KundenGruppe, FirmKu_zu_KuGru, SkiTyp, Qualitaet, Zeit, Ski, Ausleihe;
- create table PrivatKunde (id_PrivatKunde int not null auto_increment primary key, BLZ int, KontoNr int);
- create table FirmenKunde (id_FirmenKunde int not null auto_increment primary key, Ansprechpartner varchar(32));
- create table Kunde (id_Kunde int not null auto_increment primary key, PrivatKundeFK int,
- FirmenKundeFK int, Name varchar(32), Vorname varchar(32), PLZ varchar(32), Ort varchar(32),
- foreign key (PrivatKundeFK) references PrivatKunde(id_PrivatKunde), foreign key (FirmenKundeFK)
- references FirmenKunde(id_FirmenKunde));
- create table RabattGruppe (id_RabattGruppe int not null auto_increment primary key, Rabatt varchar(32));
- create table KundenGruppe (id_KundenGruppe int not null auto_increment primary key, Name varchar(32));
- create table FirmKu_zu_KuGru (FirmenKundeFK int not null auto_increment primary key, KundenGruppeFK int not null,
- foreign key (FirmenKundeFK) references FirmenKunde(id_FirmenKunde),
- foreign key (KundenGruppeFK) references KundenGruppe(id_KundenGruppe));
- create table SkiTyp (id_SkiTyp int not null auto_increment primary key, Typ varchar(32));
- create table Qualitaet (id_Qualitaet int not null auto_increment primary key, Qualitaet varchar(32));
- create table Zeit (id_Zeit int not null auto_increment primary key, StartTag date, EndTag date, AnzTage int);
- create table Ski (id_Ski int not null auto_increment primary key, SkiTypFK int, ZeitFK int,
- QualitaetFK int, Hersteller varchar(32), Bezeichnung varchar(32), AnzExemplare int,
- foreign key (SkiTypFK) references SkiTyp(id_SkiTyp), foreign key (ZeitFK) references Zeit(id_Zeit),
- foreign key (QualitaetFK) references Qualitaet(id_Qualitaet));
- create table Ausleihe (KundeFK int not null, SkiFK int not null, foreign key (KundeFK) references Kunde(id_Kunde),
- foreign key (SkiFK) references FirmenKunde(id_FirmenKunde));
- /*Daten einfügen*/
- insert into SkiTyp (Typ) values ('Slalomski');
- insert into SkiTyp (Typ) values ('Abfahrtsski');
- insert into SkiTyp (Typ) values ('Langlaufski');
- insert into Qualitaet (Qualitaet) values ('Basis');
- insert into Qualitaet (Qualitaet) values ('Mid-Range');
- insert into Qualitaet (Qualitaet) values ('Top-Level');
- insert into KundenGruppe (name) values ('GrossKunde');
- insert into KundenGruppe (name) values ('NeuKunde');
- insert into KundenGruppe (name) values ('Kunde');
- insert into RabattGruppe (Rabatt) values ('Skonto');
- insert into RabattGruppe (Rabatt) values ('MengeRabatt');
- insert into Zeit (StartTag, EndTag) values ("2015-11-12", "2015-12-12");
- insert into Zeit (StartTag, EndTag) values ("2015-02-11", "2015-03-12");
- insert into Zeit (StartTag, EndTag) values ("2015-01-12", "2016-01-01");
- insert into FirmenKunde (Ansprechpartner) values ('Herr Meier');
- insert into FirmenKunde (Ansprechpartner) values ('Herr Mumentaler');
- insert into PrivatKunde (BLZ, KontoNR) values (01538642, 0914);
- insert into PrivatKunde (BLZ, KontoNR) values (03569814, 8091);
- insert into Kunde (PrivatKundeFK, FirmenKundeFK, Name, Vorname, PLZ, Ort) values (1,2,'Ackermann', 'Alexa', '3172', 'Niederwangen');
- insert into Kunde (PrivatKundeFK, FirmenKundeFK, Name, Vorname, PLZ, Ort) values (1,2,'Kaufmann', 'Oliver', '4852', 'Rohtrist');
- insert into Kunde (PrivatKundeFK, FirmenKundeFK, Name, Vorname, PLZ, Ort) values (2,1,'Neaf', 'Michael', '3645', 'Gwatt');
- insert into Kunde (PrivatKundeFK, FirmenKundeFK, Name, Vorname, PLZ, Ort) values (2,1,'Schättin', 'Remo', '3600', 'Thun');
- insert into Ski (SkiTypFK, ZeitFK, QualitaetFK, Hersteller, Bezeichnung, AnzExemplare) values (1,2,1,'Fischer', 'Ranger JR', 5);
- insert into Ski (SkiTypFK, ZeitFK, QualitaetFK, Hersteller, Bezeichnung, AnzExemplare) values (1,3,3,'Head', 'World Cup Rebels', 2);
- insert into Ski (SkiTypFK, ZeitFK, QualitaetFK, Hersteller, Bezeichnung, AnzExemplare) values (3,3,2,'stoekli', 'Laser SG', 3);
- insert into Ski (SkiTypFK, ZeitFK, QualitaetFK, Hersteller, Bezeichnung, AnzExemplare) values (2,1,2,'Fischer', 'RC4 Superior Pro', 6);
- /*Benutzer erstellen (löschen wenn vorhanden)*/
- drop user if exists 'PK'@'localhost', 'AA'@'localhost', 'M_1'@'%', 'M_2'@'%', 'M_3'@'localhost', 'M_4'@'%', 'M_5'@'%';
- create user 'PK'@'localhost' identified by 'r00tpw';
- create user 'AA'@'localhost' identified by 'r00tpw';
- create user 'M_1'@'%' identified by '123';
- create user 'M_2'@'%' identified by '123';
- create user 'M_3'@'localhost' identified by '123';
- create user 'M_4'@'%' identified by '123';
- create user 'M_5'@'%' identified by '123';
- /*Rollen erstellen (löschen wenn vorhanden)*/
- drop role if exists DBAdmin, Lagerist, HR, Verkauf;
- create role DBAdmin;
- create role Lagerist;
- create role HR;
- create role Verkauf;
- /*Rechte an Rollen vergeben*/
- grant all privilegs on Skiverlei.* to DBAdmin;
- grant select, insert, update on Skiverleih.Ski to Lagerist;
- grant select, insert, update on Skiverleih.SkiTyp to Lagerist;
- grant select, insert, update on Skiverleih.Qualitaet to Lagerist;
- grant select on Skiverleih.zeit to Lagerist;
- grant select, insert, update on Skiverleih.PrivatKunde to HR;
- grant select, insert, update on Skiverleih.FirmenKunde to HR;
- grant select, insert, update on Skiverleih.KundenGruppe to HR;
- grant select, insert, update on Skiverleih.RabattGruppe to HR;
- grant select on Skiverleih.Ski to HR;
- grant select on Skiverleih.SkiTyp to HR;
- grant select on Skiverleih.Qualitaet to HR;
- grant select on Skiverleih.Kunde to HR;
- grant select on Skiverleih.RabattGruppe to HR;
- grant select, insert on Skiverleih.Zeit to HR;
- /*Benutzer den Rollen hinzufügen*/
- grant DBAdmin to PK@localhost;
- grant DBAdmin to 'AA'@'localhost';
- grant Lagerist to 'M_1'@'%';
- grant Lagerist to 'M_2'@'%';
- grant HR to 'M_3'@'localhost';
- grant Verkauf to 'M_4'@'%';
- grant Verkauf to 'M_5'@'%';
- /*Views (löschen wenn vorhanden)*/
- drop view if exists view_Kunde;
- drop view if exists view_Ski;
- create view view_Kunde as select * from Kunde;
- create view view_Ski as select * from Ski;
- select * from view_Kunde;
- select * from view_Ski;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement