myProject

CREATE DATABASE diary;

USE diary;

DROP TABLE IF EXISTS memoryAll;

DROP TABLE IF EXISTS memoryMoment;

DROP TABLE IF EXISTS memoryPlace;

DROP TABLE IF EXISTS memoryDescription;

CREATE TABLE memoryAll
(
memoryID INT AUTO_INCREMENT UNIQUE NOT NULL,
memoryName VARCHAR(255) NOT NULL,
PRIMARY KEY(memoryID)
);

CREATE TABLE memoryMoment
(
memoryMomentID INT AUTO_INCREMENT UNIQUE NOT NULL,
memoryMomentDate DATE NOT NULL,
memoryMomentTime TIME NOT NULL,
memoryID INT,
PRIMARY KEY(memoryMomentID),
FOREIGN KEY(memoryID) REFERENCES memoryAll(memoryID)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);

CREATE TABLE memoryPlace
(
memoryPlaceID INT AUTO_INCREMENT UNIQUE NOT NULL,
memoryPlaceName VARCHAR(255) NOT NULL,
memoryID INT,
PRIMARY KEY(memoryPlaceID),
FOREIGN KEY(memoryID) REFERENCES memoryAll(memoryID)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

CREATE TABLE memoryDescription
(
memoryDescriptionID INT AUTO_INCREMENT UNIQUE NOT NULL,
memoryDescriptionFull VARCHAR(255) NOT NULL,
memoryID INT,
PRIMARY KEY(memoryDescriptionID),
FOREIGN KEY(memoryID) REFERENCES memoryAll(memoryID)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);

INSERT INTO memoryAll(memoryID,memoryName)
VALUES(NULL,'new pet');
INSERT INTO memoryAll(memoryID,memoryName)
VALUES(NULL,'gift received');
INSERT INTO memoryAll(memoryID,memoryName)
VALUES(NULL,'new mobile');

INSERT INTO memoryMoment(memoryMomentID,memoryMomentDate,memoryMomentTime,memoryID)
VALUES(NULL,'2016-12-19','12:30:14',1);
INSERT INTO memoryMoment(memoryMomentID,memoryMomentDate,memoryMomentTime,memoryID)
VALUES(NULL,'2017-1-1','2:30:14',2);
INSERT INTO memoryMoment(memoryMomentID,memoryMomentDate,memoryMomentTime,memoryID)
VALUES(NULL,'2016-12-31','1:30:14',3);

INSERT INTO memoryPlace(memoryPlaceID,memoryPlaceName,memoryID)
VALUES(NULL,'DHAKA',1);
INSERT INTO memoryPlace(memoryPlaceID,memoryPlaceName,memoryID)
VALUES(NULL,'RANGPUR',2);
INSERT INTO memoryPlace(memoryPlaceID,memoryPlaceName,memoryID)
VALUES(NULL,'BARISHAL',3);

INSERT INTO memoryDescription(memoryDescriptionID,memoryDescriptionFull,memoryID)
VALUES(NULL,'I have bought a new white cat',1);
INSERT INTO memoryDescription(memoryDescriptionID,memoryDescriptionFull,memoryID)
VALUES(NULL,'one of my best friends have sent me a black whiteboard marker',2);
INSERT INTO memoryDescription(memoryDescriptionID,memoryDescriptionFull,memoryID)
VALUES(NULL,'I have bought a new samsung galaxy note IV',3);