USE company;
DROP TABLE IF EXISTS EMPLOYEE;
CREATE TABLE EMPLOYEE
(
FNAME VARCHAR(15) NOT NULL,
MINIT CHAR,
LNAME VARCHAR(15) NOT NULL,
SSN CHAR(9) NOT NULL,
BDATE DATE,
ADDRESS VARCHAR(30),
SEX CHAR,
SALARY DECIMAL(10,2),
SUPERSSN CHAR(9),
DNO INT NOT NULL DEFAULT 1,
PRIMARY KEY (SSN),
INDEX (SUPERSSN),
INDEX (DNO)
)TYPE = INNODB;
DESCRIBE EMPLOYEE;
DROP TABLE IF EXISTS DEPARTMENT;
CREATE TABLE DEPARTMENT
(
DNAME VARCHAR(15) NOT NULL,
DNUMBER INT NOT NULL,
MGRSSN CHAR(9) NOT NULL DEFAULT '888665555',
MGRSTARTDATE DATE,
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
INDEX (MGRSSN)
)TYPE = INNODB;
ALTER TABLE EMPLOYEE
FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN)
ON DELETE SET NULL
ON UPDATE CASCADE;
ALTER TABLE EMPLOYEE
FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER)
ON DELETE SET DEFAULT
ON UPDATE CASCADE;
ALTER TABLE DEPARTMENT
FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN)
ON DELETE SET DEFAULT
ON UPDATE CASCADE;
Can someone please help me and try to compile this with some MySQL
and please help me to populate it and make use of
the foreign keys,
the references and
the alter table statements.
Thank you !
Yours Sincerely
Morten Gulbrandsen