Skip to content

Drnoodle/database_project

BranchesTags

Repository files navigation

_________________________________________________________________ README

____________________________________________________ ARCHITECTURE

######## default package

Contains the main of the project used to create a frame and to active the content-pane's controler.

######## data access

Allows to extract informations from csv with the CsvReader's class which inherits from an Iterator<String[String]>. In this way, it's quite easy to iterate over the row of any given csv file. Provide an access to database with DBReader that is able to provide connection to db and implements a factory of prepared-requested as specified in the requirements (All read-only db request are in this class).

######## import_csv

Declare an AbstractImport's class with 2 abstracts functions :

  • getSqlRequest() : give the string used to create a prepared statement for insertion. The parameters aren't specified yet : i.e : return "INSERT INTO data (id,smth) VALUES (?,?)"

  • addbatch(String[] row) : for a given row that can be retrieve from the CsvReader (i.e : Iterator<String[]>) it parametrizes the prepared statement declared above and add batch to the statement.

All class in the package only redefines these two abstract methods. Any AbstractImport class know how to import the data given in the constructor.

######## view

Defines the GUI of the software. provides callbacks when action is executed on the view.

######## controler

instanciate an UI view and add some callbacks to it. The controled view react on event. Callback are implemented as Runnable.

________________________________________________ REQUIRED REQUEST

######## a/

SELECT YEAR(DATE) AS year, COUNT(*) AS total FROM publication GROUP BY(YEAR(DATE));

######## b/

SELECT A.NAME, COUNT() as totalPub FROM author A INNER JOIN publication_author P ON A.ID_AUTHOR = P.ID_AUTHOR GROUP BY(A.ID_AUTHOR) ORDER BY totalPub DESC LIMIT 0,10

######## c/

youngest : SELECT * FROM author WHERE ID_AUTHOR IN ( SELECT PA.ID_AUTHOR FROM publication_author PA INNER JOIN publication P ON P.ID_PUBLICATION = PA.ID_PUBLICATION WHERE YEAR(P.DATE) = 1993 ) ORDER BY YEAR(BIRTHDATE) DESC, MONTH(BIRTHDATE) DESC, DAY(BIRTHDATE) DESC LIMIT 0,1

oldest: SELECT * FROM author WHERE ID_AUTHOR IN ( SELECT PA.ID_AUTHOR FROM publication_author PA INNER JOIN publication P ON P.ID_PUBLICATION = PA.ID_PUBLICATION WHERE YEAR(P.DATE) = 1993 ) ORDER BY YEAR(BIRTHDATE) ASC, MONTH(BIRTHDATE) ASC, DAY(BIRTHDATE) ASC LIMIT 0,1

######## d/

SELECT CEIL(P.PAGES/50)50, COUNT() FROM publication P INNER JOIN title_has_publication TP ON TP.ID_PUBLICATION = P.ID_PUBLICATION INNER JOIN title T ON T.ID_TITLE = TP.ID_PUBLICATION WHERE T.IS_GRAPHICS = "Y" GROUP BY(CEIL((P.PAGES+1)/50))

####### e

SELECT SUM(PRICE)/COUNT(*) FROM publication WHERE CURRENCY='$' GROUP BY(ID_PUBLISHER)

######## f

SELECT A.NAME, COUNT(*) as totalPub FROM title_has_publication TP INNER JOIN publication_author PA ON PA.ID_PUBLICATION = TP.ID_PUBLICATION INNER JOIN author A ON A.ID_AUTHOR = PA.ID_AUTHOR WHERE TP.ID_TITLE IN ( SELECT ID_TITLE FROM tagged_by WHERE ID_TAG = (SELECT ID_TAG FROM tag WHERE NAME='science fiction' ) ) GROUP BY(A.ID_AUTHOR) ORDER BY totalPub DESC LIMIT 0,1

######## g

SELECT T.TITLE, COUNT(*) AS popularity FROM ( SELECT ID_TITLE AS idTitle FROM award_has_title UNION ALL SELECT ID_TITLE_REVIEWED AS idTitle FROM review) ID INNER JOIN title T WHERE T.ID_TITLE = idTitle GROUP BY(idTitle) ORDER BY popularity DESC LIMIT 0,3

______________________________________________ DATABASE RELATIONS

-- MySQL Script generated by MySQL Workbench -- sam 23 avr 2016 15:32:48 CEST -- Model: New Model Version: 1.0 -- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- Schema ldb

-- Schema ldb

CREATE SCHEMA IF NOT EXISTS ldb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ; USE ldb ;

-- Table ldb.language

CREATE TABLE IF NOT EXISTS ldb.language ( ID_LANGUAGE INT NOT NULL AUTO_INCREMENT COMMENT '', NAME VARCHAR(155) NOT NULL COMMENT '', CODE VARCHAR(55) NULL COMMENT '', SCRIPT VARCHAR(55) NULL COMMENT '', PRIMARY KEY (ID_LANGUAGE) COMMENT '') ENGINE = InnoDB;

-- Table ldb.note

CREATE TABLE IF NOT EXISTS ldb.note ( ID_NOTE INT NOT NULL COMMENT '', NOTE VARCHAR(3000) NOT NULL COMMENT '', PRIMARY KEY (ID_NOTE) COMMENT '') ENGINE = InnoDB;

-- Table ldb.title_serie

CREATE TABLE IF NOT EXISTS ldb.title_serie ( ID_SERIE INT NOT NULL AUTO_INCREMENT COMMENT '', TITLE VARCHAR(255) NOT NULL COMMENT '', ID_PARENT INT NULL COMMENT '', ID_NOTE INT NULL COMMENT '', PRIMARY KEY (ID_SERIE) COMMENT '', INDEX fk_title_series_title_series1_idx (ID_PARENT ASC) COMMENT '', INDEX fk_serie_note1_idx (ID_NOTE ASC) COMMENT '', CONSTRAINT fk_title_series_title_series1 FOREIGN KEY (ID_PARENT) REFERENCES ldb.title_serie (ID_SERIE) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_serie_note1 FOREIGN KEY (ID_NOTE) REFERENCES ldb.note (ID_NOTE) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

-- Table ldb.title

CREATE TABLE IF NOT EXISTS ldb.title ( ID_TITLE INT NOT NULL AUTO_INCREMENT COMMENT '', TITLE VARCHAR(160) NOT NULL COMMENT '', STORY_LENGTH CHAR(5) NULL COMMENT '', ID_LANGUAGE INT NULL COMMENT '', ID_NOTE INT NULL COMMENT '', ID_SYNOPSIS INT NULL COMMENT '', TYPE INT NULL COMMENT '', SERIE_NUMBER INT NULL COMMENT '', ID_SERIE INT NULL COMMENT '', IS_GRAPHICS CHAR(1) NULL COMMENT '', ID_ORIGINAL INT NULL COMMENT '', TRANSLATOR_NAME VARCHAR(45) NULL COMMENT '', PRIMARY KEY (ID_TITLE) COMMENT '', INDEX fk_title_languages1_idx (ID_LANGUAGE ASC) COMMENT '', INDEX fk_title_note1_idx (ID_NOTE ASC) COMMENT '', INDEX fk_title_serie1_idx (ID_SERIE ASC) COMMENT '', INDEX fk_title_note2_idx (ID_SYNOPSIS ASC) COMMENT '', INDEX fk_title_title1_idx (ID_ORIGINAL ASC) COMMENT '', CONSTRAINT fk_title_languages1 FOREIGN KEY (ID_LANGUAGE) REFERENCES ldb.language (ID_LANGUAGE) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_title_note1 FOREIGN KEY (ID_NOTE) REFERENCES ldb.note (ID_NOTE) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_title_serie1 FOREIGN KEY (ID_SERIE) REFERENCES ldb.title_serie (ID_SERIE) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_title_note2 FOREIGN KEY (ID_SYNOPSIS) REFERENCES ldb.note (ID_NOTE) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_title_title1 FOREIGN KEY (ID_ORIGINAL) REFERENCES ldb.title (ID_TITLE) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

-- Table ldb.tag

CREATE TABLE IF NOT EXISTS ldb.tag ( ID_TAG INT NOT NULL COMMENT '', NAME VARCHAR(65) NOT NULL COMMENT '', PRIMARY KEY (ID_TAG) COMMENT '') ENGINE = InnoDB;

-- Table ldb.tagged_by

CREATE TABLE IF NOT EXISTS ldb.tagged_by ( ID_TAG INT NOT NULL COMMENT '', ID_TITLE INT NOT NULL COMMENT '', PRIMARY KEY (ID_TAG, ID_TITLE) COMMENT '', INDEX fk_tags_has_title_title1_idx (ID_TITLE ASC) COMMENT '', CONSTRAINT fk_tags_has_title_tags1 FOREIGN KEY (ID_TAG) REFERENCES ldb.tag (ID_TAG) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT fk_tags_has_title_title1 FOREIGN KEY (ID_TITLE) REFERENCES ldb.title (ID_TITLE) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE = InnoDB;

-- Table ldb.review

CREATE TABLE IF NOT EXISTS ldb.review ( ID_TITLE_REVIEW INT NOT NULL COMMENT '', ID_TITLE_REVIEWED INT NOT NULL COMMENT '', PRIMARY KEY (ID_TITLE_REVIEW, ID_TITLE_REVIEWED) COMMENT '', INDEX fk_title_review_title2_idx (ID_TITLE_REVIEWED ASC) COMMENT '', CONSTRAINT fk_title_review_title1 FOREIGN KEY (ID_TITLE_REVIEW) REFERENCES ldb.title (ID_TITLE) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT fk_title_review_title2 FOREIGN KEY (ID_TITLE_REVIEWED) REFERENCES ldb.title (ID_TITLE) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

-- Table ldb.author

CREATE TABLE IF NOT EXISTS ldb.author ( ID_AUTHOR INT NOT NULL AUTO_INCREMENT COMMENT '', NAME VARCHAR(205) NULL COMMENT '', LEGAL_NAME VARCHAR(205) NULL COMMENT '', LAST_NAME VARCHAR(155) NULL COMMENT '', PSEUDO VARCHAR(155) NULL COMMENT '', BIRTHPLACE VARCHAR(155) NULL COMMENT '', BIRTHDATE CHAR(10) NULL COMMENT ' ', DEATHDATE CHAR(10) NULL COMMENT '', EMAIL VARCHAR(255) NULL COMMENT '', IMG_LINK VARCHAR(255) NULL COMMENT '', ID_LANGUAGE INT NULL COMMENT '', ID_NOTE INT NULL COMMENT '', PRIMARY KEY (ID_AUTHOR) COMMENT '', INDEX fk_author_language1_idx (ID_LANGUAGE ASC) COMMENT '', INDEX fk_author_note1_idx (ID_NOTE ASC) COMMENT '', CONSTRAINT fk_author_language1 FOREIGN KEY (ID_LANGUAGE) REFERENCES ldb.language (ID_LANGUAGE) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_author_note1 FOREIGN KEY (ID_NOTE) REFERENCES ldb.note (ID_NOTE) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

-- Table ldb.publication_serie

CREATE TABLE IF NOT EXISTS ldb.publication_serie ( ID_PUBLICATION_SERIE INT NOT NULL AUTO_INCREMENT COMMENT '', NAME VARCHAR(250) NULL COMMENT '', ID_NOTE INT NULL COMMENT '', PRIMARY KEY (ID_PUBLICATION_SERIE) COMMENT '', INDEX fk_publication_serie_note1_idx (ID_NOTE ASC) COMMENT '', CONSTRAINT fk_publication_serie_note1 FOREIGN KEY (ID_NOTE) REFERENCES ldb.note (ID_NOTE) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

-- Table ldb.publisher

CREATE TABLE IF NOT EXISTS ldb.publisher ( ID_PUBLISHER INT NOT NULL COMMENT '', NAME VARCHAR(45) NOT NULL COMMENT '', ID_NOTE INT NULL COMMENT '', PRIMARY KEY (ID_PUBLISHER) COMMENT '', INDEX fk_publisher_note1_idx (ID_NOTE ASC) COMMENT '', CONSTRAINT fk_publisher_note1 FOREIGN KEY (ID_NOTE) REFERENCES ldb.note (ID_NOTE) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

-- Table ldb.publication

CREATE TABLE IF NOT EXISTS ldb.publication ( ID_PUBLICATION INT NOT NULL AUTO_INCREMENT COMMENT '', TITLE VARCHAR(200) NOT NULL COMMENT ' ', DATE CHAR(10) NULL COMMENT '', PAGES INT NULL COMMENT '', ISBN VARCHAR(20) NULL COMMENT '', IMG_LINK VARCHAR(255) NULL COMMENT '', PRICE FLOAT NULL COMMENT '', CURRENCY CHAR(2) NULL COMMENT '', TYPE INT NULL COMMENT '', PACKAGING VARCHAR(30) NULL COMMENT '', SERIE_NUMBER INT(11) NULL COMMENT '', ID_PUBLICATION_SERIE INT NULL COMMENT '', ID_NOTE INT NULL COMMENT '', ID_PUBLISHER INT NULL COMMENT '', PRIMARY KEY (ID_PUBLICATION) COMMENT '', INDEX fk_publication_note1_idx (ID_NOTE ASC) COMMENT '', INDEX fk_publication_publication_serie1_idx (ID_PUBLICATION_SERIE ASC) COMMENT '', INDEX fk_publication_publisher1_idx (ID_PUBLISHER ASC) COMMENT '', CONSTRAINT fk_publication_note1 FOREIGN KEY (ID_NOTE) REFERENCES ldb.note (ID_NOTE) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_publication_publication_serie1 FOREIGN KEY (ID_PUBLICATION_SERIE) REFERENCES ldb.publication_serie (ID_PUBLICATION_SERIE) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_publication_publisher1 FOREIGN KEY (ID_PUBLISHER) REFERENCES ldb.publisher (ID_PUBLISHER) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

-- Table ldb.award_type

CREATE TABLE IF NOT EXISTS ldb.award_type ( ID_AWARD_TYPE INT NOT NULL AUTO_INCREMENT COMMENT '', CODE_NUM CHAR(5) NULL COMMENT '', NAME VARCHAR(500) NULL COMMENT '', BY_NAME VARCHAR(500) NULL COMMENT '', FOR_NAME VARCHAR(500) NULL COMMENT '', SHORT_NAME VARCHAR(100) NULL COMMENT '', POLL CHAR(1) NULL COMMENT '', NON_GENRE CHAR(1) NULL COMMENT '', ID_NOTE INT NULL COMMENT '', PRIMARY KEY (ID_AWARD_TYPE) COMMENT '', INDEX fk_award_type_note1_idx (ID_NOTE ASC) COMMENT '', CONSTRAINT fk_award_type_note1 FOREIGN KEY (ID_NOTE) REFERENCES ldb.note (ID_NOTE) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

-- Table ldb.award

CREATE TABLE IF NOT EXISTS ldb.award ( ID_AWARD INT NOT NULL AUTO_INCREMENT COMMENT '', TITLE VARCHAR(555) NULL COMMENT '', DATE CHAR(10) NULL COMMENT '', ID_NOTE INT NULL COMMENT '', ID_AWARD_TYPE INT NULL COMMENT '', PRIMARY KEY (ID_AWARD) COMMENT '', INDEX fk_award_note1_idx (ID_NOTE ASC) COMMENT '', INDEX fk_award_award_type1_idx (ID_AWARD_TYPE ASC) COMMENT '', CONSTRAINT fk_award_note1 FOREIGN KEY (ID_NOTE) REFERENCES ldb.note (ID_NOTE) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_award_award_type1 FOREIGN KEY (ID_AWARD_TYPE) REFERENCES ldb.award_type (ID_AWARD_TYPE) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

-- Table ldb.award_category

CREATE TABLE IF NOT EXISTS ldb.award_category ( ID_AWARD_TYPE INT NOT NULL COMMENT '', NAME VARCHAR(155) NOT NULL COMMENT '', ORDER_NUM INT NULL COMMENT '', PRIMARY KEY (ID_AWARD_TYPE, NAME) COMMENT '', INDEX fk_award_category_award_type1_idx (ID_AWARD_TYPE ASC) COMMENT '', CONSTRAINT fk_award_category_award_type1 FOREIGN KEY (ID_AWARD_TYPE) REFERENCES ldb.award_type (ID_AWARD_TYPE) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE = InnoDB;

-- Table ldb.webpage

CREATE TABLE IF NOT EXISTS ldb.webpage ( ID_WEBPAGE INT NOT NULL AUTO_INCREMENT COMMENT '', URL VARCHAR(255) NOT NULL COMMENT '', ID_AUTHOR INT NULL COMMENT '', ID_TITLE INT NULL COMMENT '', ID_PUBLISHER INT NULL COMMENT '', ID_AWARD_TYPE INT NULL COMMENT '', PRIMARY KEY (ID_WEBPAGE) COMMENT '', INDEX fk_webpage_author1_idx (ID_AUTHOR ASC) COMMENT '', INDEX fk_webpage_title1_idx (ID_TITLE ASC) COMMENT '', INDEX fk_webpage_publisher1_idx (ID_PUBLISHER ASC) COMMENT '', UNIQUE INDEX URL_UNIQUE (URL ASC) COMMENT '', INDEX fk_webpage_award_type1_idx (ID_AWARD_TYPE ASC) COMMENT '', CONSTRAINT fk_webpage_author1 FOREIGN KEY (ID_AUTHOR) REFERENCES ldb.author (ID_AUTHOR) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_webpage_title1 FOREIGN KEY (ID_TITLE) REFERENCES ldb.title (ID_TITLE) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_webpage_publisher1 FOREIGN KEY (ID_PUBLISHER) REFERENCES ldb.publisher (ID_PUBLISHER) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_webpage_award_type1 FOREIGN KEY (ID_AWARD_TYPE) REFERENCES ldb.award_type (ID_AWARD_TYPE) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

-- Table ldb.publication_author

CREATE TABLE IF NOT EXISTS ldb.publication_author ( ID_PUBLICATION INT NOT NULL COMMENT '', ID_AUTHOR INT NOT NULL COMMENT '', PRIMARY KEY (ID_PUBLICATION, ID_AUTHOR) COMMENT '', INDEX fk_publication_has_author_author1_idx (ID_AUTHOR ASC) COMMENT '', INDEX fk_publication_has_author_publication1_idx (ID_PUBLICATION ASC) COMMENT '', CONSTRAINT fk_publication_has_author_publication1 FOREIGN KEY (ID_PUBLICATION) REFERENCES ldb.publication (ID_PUBLICATION) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT fk_publication_has_author_author1 FOREIGN KEY (ID_AUTHOR) REFERENCES ldb.author (ID_AUTHOR) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE = InnoDB;

-- Table ldb.title_has_publication

CREATE TABLE IF NOT EXISTS ldb.title_has_publication ( ID_TITLE INT NOT NULL COMMENT '', ID_PUBLICATION INT NOT NULL COMMENT '', PRIMARY KEY (ID_TITLE, ID_PUBLICATION) COMMENT '', INDEX fk_title_has_publication_publication1_idx (ID_PUBLICATION ASC) COMMENT '', INDEX fk_title_has_publication_title1_idx (ID_TITLE ASC) COMMENT '', CONSTRAINT fk_title_has_publication_title1 FOREIGN KEY (ID_TITLE) REFERENCES ldb.title (ID_TITLE) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT fk_title_has_publication_publication1 FOREIGN KEY (ID_PUBLICATION) REFERENCES ldb.publication (ID_PUBLICATION) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE = InnoDB;

-- Table ldb.award_has_title

CREATE TABLE IF NOT EXISTS ldb.award_has_title ( ID_AWARD INT NOT NULL COMMENT '', ID_TITLE INT NOT NULL COMMENT '', PRIMARY KEY (ID_AWARD, ID_TITLE) COMMENT '', INDEX fk_award_has_title_title1_idx (ID_TITLE ASC) COMMENT '', INDEX fk_award_has_title_award1_idx (ID_AWARD ASC) COMMENT '', CONSTRAINT fk_award_has_title_award1 FOREIGN KEY (ID_AWARD) REFERENCES ldb.award (ID_AWARD) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT fk_award_has_title_title1 FOREIGN KEY (ID_TITLE) REFERENCES ldb.title (ID_TITLE) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

About

No description, website, or topics provided.

Resources

Readme
Activity

Stars

0 stars

Watchers

1 watching

Forks

0 forks
Report repository

Releases

No releases published

Packages

 
 
 

Contributors

Languages