CREATE TABLE customer ( customerid INTEGER NOT NULL, personid INTEGER NOT NULL, address VARCHAR2(60) NOT NULL, phone VARCHAR2(30) NOT NULL ); ALTER TABLE customer ADD CONSTRAINT customer_pk PRIMARY KEY ( customerid ); CREATE TABLE customerflatrate ( flatrateid INTEGER NOT NULL, customerid INTEGER NOT NULL, startdate DATE NOT NULL, enddate DATE ); ALTER TABLE customerflatrate ADD CONSTRAINT customerflatrate_pk PRIMARY KEY ( flatrateid, customerid ); CREATE TABLE extraservice ( serviceid INTEGER NOT NULL, customerid INTEGER NOT NULL, startdate DATE NOT NULL, amount FLOAT ); ALTER TABLE extraservice ADD CONSTRAINT extraservice_pk PRIMARY KEY ( startdate ); CREATE TABLE flatrate ( flatrateid INTEGER NOT NULL, name VARCHAR2(20) NOT NULL, price FLOAT NOT NULL ); ALTER TABLE flatrate ADD CONSTRAINT flatrate_pk PRIMARY KEY ( flatrateid ); CREATE TABLE flatrateservice ( flatrateid INTEGER NOT NULL, serviceid INTEGER NOT NULL, amount FLOAT ); ALTER TABLE flatrateservice ADD CONSTRAINT flatrateservice_pk PRIMARY KEY ( flatrateid, serviceid ); CREATE TABLE person ( personid INTEGER NOT NULL, email VARCHAR2(30) NOT NULL, password VARCHAR2(30) NOT NULL, firstname VARCHAR2(20) NOT NULL, lastname VARCHAR2(20) NOT NULL, role CHAR(1) NOT NULL ); ALTER TABLE person ADD CONSTRAINT user_pk PRIMARY KEY ( personid ); CREATE TABLE service ( serviceid INTEGER NOT NULL, name VARCHAR2(15) NOT NULL, price FLOAT NOT NULL, units VARCHAR2(5) NOT NULL ); ALTER TABLE service ADD CONSTRAINT service_pk PRIMARY KEY ( serviceid ); CREATE TABLE usagelog ( serviceid INTEGER NOT NULL, flatrateid INTEGER NOT NULL, customerid INTEGER NOT NULL, amount FLOAT NOT NULL, when DATE NOT NULL ); ALTER TABLE usagelog ADD CONSTRAINT currentstate_pk PRIMARY KEY ( flatrateid, customerid, serviceid ); -- ERROR: FK name length exceeds maximum allowed length(30) ALTER TABLE usagelog ADD CONSTRAINT currentstate_customerflatrate_fk FOREIGN KEY ( flatrateid, customerid ) REFERENCES customerflatrate ( flatrateid, customerid ); ALTER TABLE usagelog ADD CONSTRAINT currentstate_service_fk FOREIGN KEY ( serviceid ) REFERENCES service ( serviceid ); ALTER TABLE customer ADD CONSTRAINT customer_user_fk FOREIGN KEY ( personid ) REFERENCES person ( personid ); ALTER TABLE customerflatrate ADD CONSTRAINT customerflatrate_customer_fk FOREIGN KEY ( customerid ) REFERENCES customer ( customerid ); ALTER TABLE customerflatrate ADD CONSTRAINT customerflatrate_flatrate_fk FOREIGN KEY ( flatrateid ) REFERENCES flatrate ( flatrateid ); ALTER TABLE extraservice ADD CONSTRAINT extraservice_customer_fk FOREIGN KEY ( customerid ) REFERENCES customer ( customerid ); ALTER TABLE extraservice ADD CONSTRAINT extraservice_service_fk FOREIGN KEY ( serviceid ) REFERENCES service ( serviceid ); ALTER TABLE flatrateservice ADD CONSTRAINT flatrateservice_flatrate_fk FOREIGN KEY ( flatrateid ) REFERENCES flatrate ( flatrateid ); ALTER TABLE flatrateservice ADD CONSTRAINT flatrateservice_service_fk FOREIGN KEY ( serviceid ) REFERENCES service ( serviceid );