Notes

CREATE TABLE Employee(
emp_id int PRIMARY KEY,
emp_name varchar(25) NOT NULL,
emp_dob date NOT NULL
--emp_age(SELECT datediff(day, emp_dob , GETdate()) / 365.5643 ) DECIMAL,
--Age as (SELECT datediff(day, DateBought, GETdate()) / 365.2425 )
--emp_age as (CURRENT_DATE()-emp_dob)
);

CREATE TABLE emp_phone(
emp_id int,
phone varchar(10),
PRIMARY KEY(emp_id, phone),
FOREIGN KEY(emp_id)
REFERENCES Employee(emp_id)
);

CREATE TABLE Fuel(
fuel_id int PRIMARY KEY,
fuel_name varchar(10),
fuel_density varchar(10),
fuel_rate varchar(50)
);

CREATE TABLE petrolpump(
p_licno varchar(16) PRIMARY KEY,
p_name varchar(25) NOT NULL,
p_loc varchar(255) NOT NULL,
emp_id int,
fuel_id int,
FOREIGN KEY(emp_id)
REFERENCES Employee(emp_id),
FOREIGN KEY(fuel_id)
REFERENCES Fuel(fuel_id)
);

CREATE TABLE EmpRoles(
emp_id int,
emp_role varchar(20),
emp_role_id int,
PRIMARY KEY(emp_id, emp_role),
FOREIGN KEY(emp_id)
REFERENCES Employee(emp_id)
);


CREATE TABLE EmpLogin(
emp_id int,
emp_username varchar(15),
emp_password varchar(20),
PRIMARY KEY(emp_username),
FOREIGN KEY(emp_id)
REFERENCES Employee(emp_id)
);



CREATE TABLE FuelSales(
fuel_id int,
sales_amt varchar(255),
sales_date date,
FOREIGN KEY(fuel_id)
REFERENCES Fuel(fuel_id)
);

CREATE TABLE EmphasLogin(
emp_id int,
emp_username varchar(15),
FOREIGN KEY(emp_id)
REFERENCES Employee(emp_id),
FOREIGN KEY(emp_username)
REFERENCES EmpLogin(emp_username)
);

CREATE TABLE PumphasEmp(
emp_id int,
p_licno varchar(16),
FOREIGN KEY(emp_id)
REFERENCES Employee(emp_id),
FOREIGN KEY(p_licno)
REFERENCES petrolpump(p_licno)
);