company

-- 1. Create DEPARTMENT (basic, no FK yet)

CREATE TABLE DEPARTMENT (

    Dname VARCHAR(50) NOT NULL,

    Dnumber INT PRIMARY KEY,

    Mgr_ssn CHAR(9),

    Mgr_start_date DATE

);


-- 2. Create EMPLOYEE

CREATE TABLE EMPLOYEE (

    Fname VARCHAR(20),

    Minit CHAR(1),

    Lname VARCHAR(20),

    Ssn CHAR(9) PRIMARY KEY,

    Bdate DATE,

    Address VARCHAR(100),

    Sex CHAR(1),

    Salary DECIMAL(10,2),

    Super_ssn CHAR(9),

    Dno INT,

    FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn),

    FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber)

);


-- 3. Alter DEPARTMENT (add manager FK → EMPLOYEE)

ALTER TABLE DEPARTMENT

ADD CONSTRAINT fk_mgr FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn);


-- 4. Create DEPT_LOCATIONS

CREATE TABLE DEPT_LOCATIONS (

    Dnumber INT,

    Dlocation VARCHAR(50),

    PRIMARY KEY (Dnumber, Dlocation),

    FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber)

);


-- 5. Create PROJECT

CREATE TABLE PROJECT (

    Pname VARCHAR(50),

    Pnumber INT PRIMARY KEY,

    Plocation VARCHAR(50),

    Dnum INT,

    FOREIGN KEY (Dnum) REFERENCES DEPARTMENT(Dnumber)

);


-- 6. Create WORKS_ON

CREATE TABLE WORKS_ON (

    Essn CHAR(9),

    Pno INT,

    Hours DECIMAL(5,2),

    PRIMARY KEY (Essn, Pno),

    FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn),

    FOREIGN KEY (Pno) REFERENCES PROJECT(Pnumber)

);


-- 7. Create DEPENDENT

CREATE TABLE DEPENDENT (

    Essn CHAR(9),

    Dependent_name VARCHAR(20),

    Sex CHAR(1),

    Bdate DATE,

    Relationship VARCHAR(20),

    PRIMARY KEY (Essn, Dependent_name),

    FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn)

);




Comments

Post a Comment

Popular posts from this blog

college

Image
CREATE TABLE Department (     dept_id INT PRIMARY KEY,     name VARCHAR(50) ); CREATE TABLE Students (     reg_no INT PRIMARY KEY,     name VARCHAR(50),     address VARCHAR(100),     bcourse VARCHAR(50),     dept_id INT,     FOREIGN KEY (dept_id) REFERENCES Department(dept_id) ); CREATE TABLE Teachers (     teacher_id INT PRIMARY KEY,     name VARCHAR(50),     email VARCHAR(100),     address VARCHAR(100),     dept_id INT,     FOREIGN KEY (dept_id) REFERENCES Department(dept_id) ); CREATE TABLE Courses (     course_id INT PRIMARY KEY,     name VARCHAR(50),     credits INT,     dept_id INT,     teacher_id INT,     FOREIGN KEY (dept_id) REFERENCES Department(dept_id),     FOREIGN KEY (teacher_id) REFERENCES Teachers(teacher_id) ); CREATE TABLE Library (     library_id INT P...
Read more

company database

 -- 1. Create the 'department' table CREATE TABLE department (     Dname VARCHAR(20),     Dnum INT PRIMARY KEY,     Mgr_ssn INT,     MgrStdate DATE ); -- 2. Create the 'employee' table CREATE TABLE employee (     Fname VARCHAR(20),     Mname CHAR(1),     Lname VARCHAR(20),     Ssn INT PRIMARY KEY,     Bdate DATE,     address VARCHAR(100),     Gender CHAR(1),     salary INT,     Super_ssn INT,     Dno INT,     FOREIGN KEY (Dno) REFERENCES department(Dnum) ); -- 3. Insert data into 'department' table INSERT INTO department VALUES ('Research', 5, 333445555, '1988-05-22'); INSERT INTO department VALUES ('Administration', 4, 987654321, '1995-01-01'); INSERT INTO department VALUES ('Headquarters', 1, 888665555, '1981-06-19'); -- 4. Insert data into 'employee' table INSERT INTO employee VALUES ('John', 'B', 'Smith', 123456789, '1965-01-09', ...
Read more