--
--


--
-- Name: FotoAgentur; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON DATABASE FotoAgentur IS 'delete me asap';


--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';





--
-- Name: Erfahrungsstufen; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE Erfahrungsstufen (
    ID integer NOT NULL,
    Bezeichnung character varying
);


ALTER TABLE public.Erfahrungsstufen OWNER TO postgres;

--
-- Name: Erfahrungsstufen_ID_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE Erfahrungsstufen_ID_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.Erfahrungsstufen_ID_seq OWNER TO postgres;

--
-- Name: Erfahrungsstufen_ID_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE Erfahrungsstufen_ID_seq OWNED BY Erfahrungsstufen.ID;


--
-- Name: Fotoabzuege; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE Fotoabzuege (
    Foto integer NOT NULL,
    Zeitstempel timestamp with time zone NOT NULL,
    Bildgroesse point
);


ALTER TABLE public.Fotoabzuege OWNER TO postgres;

--
-- Name: COLUMN Fotoabzuege.Bildgroesse; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN Fotoabzuege.Bildgroesse IS 'Laenge und Breite als Vektor';


--
-- Name: Fotographen; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE Fotographen (
    MitarbeiterID integer NOT NULL
);


ALTER TABLE public.Fotographen OWNER TO postgres;

--
-- Name: Fotos; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE Fotos (
    ID integer NOT NULL,
    GPSKoordinaten point,
    Aufnahmezeitpunkt timestamp with time zone,
    Fotograph integer NOT NULL,
    Kamera integer NOT NULL
);


ALTER TABLE public.Fotos OWNER TO postgres;

--
-- Name: Fotos_ID_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE Fotos_ID_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.Fotos_ID_seq OWNER TO postgres;

--
-- Name: Fotos_ID_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE Fotos_ID_seq OWNED BY Fotos.ID;


--
-- Name: Kameras; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE Kameras (
    ID integer NOT NULL,
    Marke character varying NOT NULL,
    Modell character varying NOT NULL
);


ALTER TABLE public.Kameras OWNER TO postgres;

--
-- Name: Kamera_ID_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE Kamera_ID_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.Kamera_ID_seq OWNER TO postgres;

--
-- Name: Kamera_ID_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE Kamera_ID_seq OWNED BY Kameras.ID;


--
-- Name: Kunden; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE Kunden (
    PersonID integer NOT NULL,
    Branche character varying
);


ALTER TABLE public.Kunden OWNER TO postgres;

--
-- Name: Managed; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE Managed (
    SeniorID integer NOT NULL,
    FotographID integer NOT NULL,
    Von date NOT NULL,
    Bis date
);


ALTER TABLE public.Managed OWNER TO postgres;

--
-- Name: Mitarbeiter; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE Mitarbeiter (
    PersonID integer NOT NULL,
    Gehalt numeric NOT NULL,
    Erfahrung integer NOT NULL
);


ALTER TABLE public.Mitarbeiter OWNER TO postgres;

--
-- Name: Mitarbeiter_Erfahrung_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE Mitarbeiter_Erfahrung_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.Mitarbeiter_Erfahrung_seq OWNER TO postgres;

--
-- Name: Mitarbeiter_Erfahrung_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE Mitarbeiter_Erfahrung_seq OWNED BY Mitarbeiter.Erfahrung;


--
-- Name: Mitarbeiter_PersonID_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE Mitarbeiter_PersonID_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.Mitarbeiter_PersonID_seq OWNER TO postgres;

--
-- Name: Mitarbeiter_PersonID_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE Mitarbeiter_PersonID_seq OWNED BY Mitarbeiter.PersonID;


--
-- Name: Personen; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE Personen (
    ID integer NOT NULL,
    Name character varying,
    Vorname character varying,
    GeburtsDatum date
);


ALTER TABLE public.Personen OWNER TO postgres;

--
-- Name: Person_ID_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE Person_ID_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.Person_ID_seq OWNER TO postgres;

--
-- Name: Person_ID_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE Person_ID_seq OWNED BY Personen.ID;


--
-- Name: Seniors; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE Seniors (
    MitarbeiterID integer NOT NULL,
    AnzahlGraueHaare integer,
    Bonus bigint
);


ALTER TABLE public.Seniors OWNER TO postgres;

--
-- Name: Verkaeufer; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE Verkaeufer (
    MitarbeiterID integer NOT NULL,
    Spezialgebiet character varying
);


ALTER TABLE public.Verkaeufer OWNER TO postgres;

--
-- Name: Verkaufen; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE Verkaufen (
    Preis numeric,
    Kunde integer NOT NULL,
    Verkaeufer integer NOT NULL,
    Foto integer NOT NULL
);


ALTER TABLE public.Verkaufen OWNER TO postgres;

--
-- Name: ID; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY Erfahrungsstufen ALTER COLUMN ID SET DEFAULT nextval('Erfahrungsstufen_ID_seq'::regclass);


--
-- Name: ID; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY Fotos ALTER COLUMN ID SET DEFAULT nextval('Fotos_ID_seq'::regclass);


--
-- Name: ID; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY Kameras ALTER COLUMN ID SET DEFAULT nextval('Kamera_ID_seq'::regclass);


--
-- Name: ID; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY Personen ALTER COLUMN ID SET DEFAULT nextval('Person_ID_seq'::regclass);


--
-- Name: Erfahrungsstufen_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY Erfahrungsstufen
    ADD CONSTRAINT Erfahrungsstufen_pkey PRIMARY KEY (ID);


--
-- Name: Fotoabzuege_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY Fotoabzuege
    ADD CONSTRAINT Fotoabzuege_pkey PRIMARY KEY (Foto, Zeitstempel);


--
-- Name: Fotograph_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY Fotographen
    ADD CONSTRAINT Fotograph_pkey PRIMARY KEY (MitarbeiterID);


--
-- Name: Fotos_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY Fotos
    ADD CONSTRAINT Fotos_pkey PRIMARY KEY (ID);


--
-- Name: Kamera_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY Kameras
    ADD CONSTRAINT Kamera_pkey PRIMARY KEY (ID);


--
-- Name: Kunde_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY Kunden
    ADD CONSTRAINT Kunde_pkey PRIMARY KEY (PersonID);


--
-- Name: Managed_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY Managed
    ADD CONSTRAINT Managed_pkey PRIMARY KEY (SeniorID, FotographID, Von);


--
-- Name: Mitarbeiter_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY Mitarbeiter
    ADD CONSTRAINT Mitarbeiter_pkey PRIMARY KEY (PersonID);


--
-- Name: Person_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY Personen
    ADD CONSTRAINT Person_pkey PRIMARY KEY (ID);


--
-- Name: Senior_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY Seniors
    ADD CONSTRAINT Senior_pkey PRIMARY KEY (MitarbeiterID);


--
-- Name: Verkaeufer_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY Verkaeufer
    ADD CONSTRAINT Verkaeufer_pkey PRIMARY KEY (MitarbeiterID);


--
-- Name: Verkaufen_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY Verkaufen
    ADD CONSTRAINT Verkaufen_pkey PRIMARY KEY (Kunde, Foto);


--
-- Name: fki_MitarbeiterErfahrung; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX fki_MitarbeiterErfahrung ON Mitarbeiter USING btree (Erfahrung);


--
-- Name: Fotoabzuege_Foto_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY Fotoabzuege
    ADD CONSTRAINT Fotoabzuege_Foto_fkey FOREIGN KEY (Foto) REFERENCES Fotos(ID);


--
-- Name: Fotograph_MitarbeiterID_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY Fotographen
    ADD CONSTRAINT Fotograph_MitarbeiterID_fkey FOREIGN KEY (MitarbeiterID) REFERENCES Mitarbeiter(PersonID);


--
-- Name: Fotos_Fotograph_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY Fotos
    ADD CONSTRAINT Fotos_Fotograph_fkey FOREIGN KEY (Fotograph) REFERENCES Fotographen(MitarbeiterID);


--
-- Name: Fotos_Kamera_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY Fotos
    ADD CONSTRAINT Fotos_Kamera_fkey FOREIGN KEY (Kamera) REFERENCES Kameras(ID);


--
-- Name: Kunde_PersonID_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY Kunden
    ADD CONSTRAINT Kunde_PersonID_fkey FOREIGN KEY (PersonID) REFERENCES Personen(ID);


--
-- Name: Managed_FotographID_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY Managed
    ADD CONSTRAINT Managed_FotographID_fkey FOREIGN KEY (FotographID) REFERENCES Fotographen(MitarbeiterID);


--
-- Name: Managed_SeniorID_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY Managed
    ADD CONSTRAINT Managed_SeniorID_fkey FOREIGN KEY (SeniorID) REFERENCES Seniors(MitarbeiterID);


--
-- Name: MitarbeiterErfahrung; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY Mitarbeiter
    ADD CONSTRAINT MitarbeiterErfahrung FOREIGN KEY (Erfahrung) REFERENCES Erfahrungsstufen(ID);


--
-- Name: Mitarbeiter_PersonID_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY Mitarbeiter
    ADD CONSTRAINT Mitarbeiter_PersonID_fkey FOREIGN KEY (PersonID) REFERENCES Personen(ID);


--
-- Name: Senior_MitarbeiterID_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY Seniors
    ADD CONSTRAINT Senior_MitarbeiterID_fkey FOREIGN KEY (MitarbeiterID) REFERENCES Mitarbeiter(PersonID);


--
-- Name: Verkaeufe_Foto_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY Verkaufen
    ADD CONSTRAINT Verkaeufe_Foto_fkey FOREIGN KEY (Foto) REFERENCES Fotos(ID);


--
-- Name: Verkaeufe_Kunde_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY Verkaufen
    ADD CONSTRAINT Verkaeufe_Kunde_fkey FOREIGN KEY (Kunde) REFERENCES Kunden(PersonID);


--
-- Name: Verkaeufe_Verkaeufer_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY Verkaufen
    ADD CONSTRAINT Verkaeufe_Verkaeufer_fkey FOREIGN KEY (Verkaeufer) REFERENCES Verkaeufer(MitarbeiterID);


--
-- Name: Verkaeufer_MitarbeiterID_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY Verkaeufer
    ADD CONSTRAINT Verkaeufer_MitarbeiterID_fkey FOREIGN KEY (MitarbeiterID) REFERENCES Mitarbeiter(PersonID);


--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
--

