/==============================================================/ /* Table: department /…
- User
/==============================================================/ /* Table: department / /==============================================================*/create or replace table department ( dep_id char(10) not null, pro_id char(10) null, name char(10) null, manager char(10) null, project char(10) null, constraint PK_DEPARTMENT primary key clustered (dep_id) );
/==============================================================/ /* Table: employee / /==============================================================*/ create or replace table employee ( emp_id char(10) not null, name char(10) null, Identi_id char(10) not null, role_id char(10) null, pro_id char(10) null, sala_id char(10) null, dep_id char(10) null, gender char(10) null, age char(10) null, phone char(10) null, department char(10) null, position char(10) null, project char(10) null, constraint PK_EMPLOYEE primary key clustered (emp_id, Identi_id) );
/==============================================================/ /* Index: emp_gets_sal_FK / /==============================================================*/ create index emp_gets_sal_FK on employee ( sala_id ASC );
/==============================================================/ /* Table: project / /==============================================================*/ create or replace table project ( pro_id char(10) not null, name char(10) not null, manager char(10) null, department char(10) null, starttime char(10) null, endtime char(10) null, constraint PK_PROJECT primary key clustered (pro_id) );
/==============================================================/ /* Table: role / /==============================================================*/ create or replace table role ( role_id char(10) not null, dep_id char(10) null, name char(10) not null, department char(10) null, constraint PK_ROLE primary key clustered (role_id) );
/==============================================================/ /* Table: salary / /==============================================================*/ create or replace table salary ( sala_id char(10) not null, emp_id char(10) null, Identi_id char(10) null, emp char(10) null, base char(10) null, bonus char(10) null, deduction char(10) null, constraint PK_SALARY primary key clustered (sala_id) );
Please fix this sql code, find the right data type rather than default "char" according to the attribute name and table name and set right contriains.. This is a employee manager database.
- Assistant
/==============================================================/ /* Table: department / /==============================================================*/ CREATE TABLE department ( dep_id INT NOT NULL, pro_id INT NULL, name VARCHAR(50) NULL, manager INT NULL, project INT NULL, CONSTRAINT PK_DEPARTMENT PRIMARY KEY (dep_id) );
/==============================================================/ /* Table: employee / /==============================================================*/ CREATE TABLE employee ( emp_id INT NOT NULL, name VARCHAR(50) NULL, Identi_id INT NOT NULL, role_id INT NULL, pro_id INT NULL, sala_id INT NULL, dep_id INT NULL, gender VARCHAR(10) NULL, age INT NULL, phone VARCHAR(20) NULL, department INT NULL, position VARCHAR(50) NULL, project INT NULL, CONSTRAINT PK_EMPLOYEE PRIMARY KEY (emp_id, Identi_id) );
/==============================================================/ /* Index: emp_gets_sal_FK / /==============================================================*/ CREATE INDEX emp_gets_sal_FK ON employee (sala_id ASC);
/==============================================================/ /* Table: project / /==============================================================*/ CREATE TABLE project ( pro_id INT NOT NULL, name VARCHAR(50) NOT NULL, manager INT NULL, department INT NULL, starttime DATE NULL, endtime DATE NULL, CONSTRAINT PK_PROJECT PRIMARY KEY (pro_id) );
/==============================================================/ /* Table: role / /==============================================================*/ CREATE TABLE role ( role_id INT NOT NULL, dep_id INT NULL, name VARCHAR(50) NOT NULL, department INT NULL, CONSTRAINT PK_ROLE PRIMARY KEY (role_id) );
/==============================================================/ /* Table: salary / /==============================================================*/ CREATE TABLE salary ( sala_id INT NOT NULL, emp_id INT NULL, Identi_id INT NULL, emp INT NULL, base DECIMAL(10,2) NULL, bonus DECIMAL(10,2) NULL, deduction DECIMAL(10,2) NULL, CONSTRAINT PK_SALARY PRIMARY KEY (sala_id) );
/==============================================================/ /* Foreign Key Constraints / /==============================================================*/ ALTER TABLE department ADD CONSTRAINT fk_department_project FOREIGN KEY (pro_id) REFERENCES project(pro_id);
ALTER TABLE department ADD CONSTRAINT fk_department_manager FOREIGN KEY (manager) REFERENCES employee(emp_id);
ALTER TABLE department ADD CONSTRAINT fk_department_dep_id FOREIGN KEY (department) REFERENCES department(dep_id);
ALTER TABLE employee ADD CONSTRAINT fk_employee_role FOREIGN KEY (role_id) REFERENCES role(role_id);
ALTER TABLE employee ADD CONSTRAINT fk_employee_project FOREIGN KEY (pro_id) REFERENCES project(pro_id);
ALTER TABLE employee ADD CONSTRAINT fk_employee_salary FOREIGN KEY (sala_id) REFERENCES salary(sala_id);
ALTER TABLE employee ADD CONSTRAINT fk_employee_department FOREIGN KEY (department) REFERENCES department(dep_id);
ALTER TABLE salary ADD CONSTRAINT fk_salary_employee FOREIGN KEY (emp_id, Identi_id) REFERENCES employee(emp_id, Identi_id);
ALTER TABLE role ADD CONSTRAINT fk_role_department FOREIGN KEY (dep_id) REFERENCES department(dep_id);