create database task;
USE task;

CREATE TABLE DEPARTMENT (
    DNAME VARCHAR(50),
    DNUMBER INT PRIMARY KEY,
    MGRSSN INT,
    MGRSTARTDATE DATE
);

CREATE TABLE DEPT_LOCATIONS (
    DNUMBER INT,
    DLOCATION VARCHAR(50)
);

CREATE TABLE PROJECT (
    PNAME VARCHAR(50),
    PNUMBER INT PRIMARY KEY,
    PLOCATION VARCHAR(50),
    DNUM INT
);

CREATE TABLE WORKS_ON (
    ESSN INT,
    PNO INT,
    HOURS FLOAT
);

CREATE TABLE DEPENDENT (
    ESSN INT,
    DEPENDENT_NAME VARCHAR(50),
    SEX CHAR(1),
    BDATE DATE,
    RELATIONSHIP VARCHAR(50)
);

-- DEPARTMENT
INSERT INTO DEPARTMENT VALUES
('HR', 10, 123456, '2010-01-01'),
('IT', 20, 234567, '2015-02-01');

-- DEPT_LOCATIONS
INSERT INTO DEPT_LOCATIONS VALUES
(10, 'Cairo'),
(20, 'Giza');

-- PROJECT
INSERT INTO PROJECT VALUES
('Alpha', 1001, 'Cairo', 10),
('Beta', 1002, 'Giza', 20);

-- WORKS_ON
INSERT INTO WORKS_ON VALUES
(123456, 1001, 40),
(234567, 1002, 35),
(345678, 1001, 30);

-- DEPENDENT
INSERT INTO DEPENDENT VALUES
(123456, 'Laila', 'F', '2010-05-01', 'Daughter'),
(234567, 'Omar', 'M', '2012-07-15', 'Son');


