CREATE TABLE Z_Employee (

p_id INTEGER PRIMARY KEY

);

CREATE TABLE Z_Address (

Address_id INTEGER PRIMARY KEY,
Street VARCHAR(50),
zip INTEGER,
house_number INTEGER

);

CREATE TABLE Z_City (

Zip INTEGER PRIMARY KEY,
City VARCHAR(50)

);

CREATE TABLE Z_Person (

p_id INTEGER PRIMARY KEY,
first_name VARCHAR(50),
surname VARCHAR(50),
sex CHAR,
address_id INTEGER,
category VARCHAR(50)

);
CREATE TABLE Z_Customer (
p_id INTEGER PRIMARY KEY

);

CREATE TABLE Z_Restaurant (
U_id INTEGER PRIMARY KEY,
name VARCHAR(50),
phone VARCHAR(30),
fax VARCHAR(30),
address_id INTEGER

);

CREATE TABLE Z_Invoice (

invoice_no INTEGER PRIMARY KEY,
date1 DATE,
time1 VARCHAR(50),
u_id INTEGER,
p_id_emp INTEGER,
p_id_cust INTEGER

);

CREATE TABLE Z_Article (

article_id INTEGER PRIMARY KEY,
name VARCHAR(50),
price INTEGER,
vat INTEGER

);

CREATE TABLE Z_Invoice_Articles (
invoice_no INTEGER,
article_id INTEGER,
price INTEGER,
vat INTEGER,
PRIMARY KEY (invoice_no, article_id)

);

ALTER TABLE z_address ADD FOREIGN KEY (zip ) REFERENCES z_City (zip );
ALTER TABLE z_person ADD FOREIGN KEY (address_id) REFERENCES z_address (address_id);
ALTER TABLE z_restaurant ADD FOREIGN KEY (address_id) REFERENCES z_address (address_id);
ALTER TABLE z_invoice ADD FOREIGN KEY (u_id) REFERENCES z_restaurant (u_id );
ALTER TABLE z_invoice ADD FOREIGN KEY (p_id_emp) REFERENCES z_person (p_id);
ALTER TABLE z_invoice ADD FOREIGN KEY (p_id_cust) REFERENCES z_person (p_id);
ALTER TABLE z_invoice_articles ADD FOREIGN KEY (invoice_no) REFERENCES z_invoice (invoice_no);
ALTER TABLE z_invoice_articles ADD FOREIGN KEY ( article_id ) REFERENCES z_article ( article_id );
ALTER TABLE z_customer ADD FOREIGN KEY (p_id) REFERENCES z_Person(p_id);
ALTER TABLE z_employee ADD FOREIGN KEY (p_id) REFERENCES z_Person(p_id);

INSERT INTO Z_City VALUES (8151,'Brasov');
INSERT INTO Z_City VALUES (8040,'Bucuresti');
INSERT INTO Z_City VALUES (1020,'Wien');
INSERT INTO Z_Address VALUES (1,'Iuliu Maniu',8151,3);
INSERT INTO Z_Address VALUES (2,'Rosiorilor',8040,10);
INSERT INTO Z_Address VALUES (3,'Taubstummengasse',1020,11);
INSERT INTO Z_Person VALUES (1,'Clark','Kent','M',3,'Employee');
INSERT INTO Z_Person VALUES (2,'Lois','Lane','F',2, 'Customer');
INSERT INTO Z_Person VALUES (3,'Bugs','Bunny','M',2,'Employee');
INSERT INTO Z_Person VALUES (4,'Indiana','Jones','M',2,'Customer');
INSERT INTO Z_Person VALUES (5,'Marie','Antoinette','F',3, 'Employee');
INSERT INTO Z_Person VALUES (6,'Janet','Jackson','F',1,'Customer');
INSERT INTO Z_Customer VALUES (2);
INSERT INTO Z_Customer VALUES (4);
INSERT INTO Z_Customer VALUES (6);
INSERT INTO Z_Employee VALUES (1);
INSERT INTO Z_Employee VALUES (3);
INSERT INTO Z_Employee VALUES (5);
INSERT INTO Z_Restaurant VALUES(123456,'Cafe 23','03458720','031541756',1);
INSERT INTO Z_Restaurant VALUES (234567,'Wan Tan','0542610','03154756',2);
INSERT INTO Z_Restaurant VALUES(345678,'Times','08745220','03451556',3);
INSERT INTO Z_Invoice VALUES(1111,to_date('10.10.2010','dd.mm.yyyy'),'14:00:00',123456,1,2);
INSERT INTO Z_Invoice VALUES(2222,to_date('15.12.2010','dd.mm.yyyy'),'14:00:00',234567,3,4);
INSERT INTO Z_Invoice VALUES(3333,to_date('06.03.2011','dd.mm.yyyy'),'14:00:00',345678,5,6);
INSERT INTO Z_Article VALUES (4321,'Cola',3,0.3);
INSERT INTO Z_Article VALUES (4322,'Fanta',10,2);
INSERT INTO Z_Article VALUES (4323,'Pepsi',2,0.6);
INSERT INTO Z_Invoice_Articles VALUES (1111,4321,3,0.3);
INSERT INTO Z_Invoice_Articles VALUES (2222,4322,10,2);
INSERT INTO Z_Invoice_Articles VALUES (3333,4323,8,0.8);