Super database¶

Try me¶

## Problem definition You are building a database for a ‘fandom’ website. The objective is to store in a database the information about superheroes in different comics across different comics. You want to store basic details about the following entities:

Comic editorial: name, and head-quarters (e.g. Marvel, New York),
Authors: Full name, role (either writer or artist),
Comic: Name, issue, publication date (e.g. Avengers, 1, 1963-09-20),
Super-Hero: Real name, fictional name (e.g. Peter Parker, Spiderman)
Super-hero squad: Name (e.g. Avengers)
Super powers: Name, description (e.g. super-strength, ability to kick ass bad time)

You also want to implement in your model, meaningful relationships to connect the different entities, for instance, which editorial publishes each comic, which are the authors of the comic, in which issues does every super hero appear, or what super-powers do they have.

Draw the model of your database design to discuss implementation details with your team mates. Include in the diagram field names and types and relationships.

Database Design¶

The following diagrams shows a possible solution for the database:

super model

The trick is to use junction tables to define all the many-to-many relationships in the model:

Authorship: Junction table to implement the (many-to-many) relationship between Authors and Comics (A comic can have more than one author and an author more than one comic).
Affiliation: Junction table to implement the (many-to-many) relationship between Super-heroes and Squads (A super-hero can join more than one squad and there is probably no squad with more than one super-hero).
Appearance: Junction table to implement the (many-to-many) relationship between Super-heroes and Comics (A super-hero can appear in more than one comic and several heroes can appear in the same comic issue, otherwise they are no fun).
Super-Empowerment: Junction table to implement the (many-to-many) relationship between Super-heroes and Super Powers (A super-hero can have more than one super-power, and several heroes may have the same super-power)

These junction tables allow us to define the many-to-many relationships, but we can also add new fields to these records to store and analyse the properties of these relationships.

Try it yourself¶

The following SQL script creates a database and data model with this solution for you to try:

--
--
drop database if exists superheroes;
create database superheroes;
use superheroes;
--
CREATE TABLE Editorial(
    editorial_id INT PRIMARY KEY,
    editorial_name VARCHAR(20),
    head_quarters VARCHAR(30)
);

CREATE TABLE Author(
    author_id INT PRIMARY KEY,
    full_name VARCHAR(30),
    author_role ENUM('writer', 'practice')
);

CREATE TABLE Comic (
    comic_id INT PRIMARY KEY,
    issue_no INT,
    publication_time DATETIME,
    editorial_fk INT,
    CONSTRAINT comic_editorial_fk FOREIGN KEY (editorial_fk) REFERENCES Editorial(editorial_id)
);

CREATE TABLE Authorship (
    comic_fk INT,
    author_fk INT,
    CONSTRAINT authorship_comic_fk FOREIGN KEY (comic_fk) REFERENCES Comic(comic_id),
    CONSTRAINT authorship_author_fk FOREIGN KEY (author_fk) REFERENCES Author(author_id),
    PRIMARY KEY (comic_fk, author_fk)
);

CREATE TABLE Superheroe (
    superhero_id INT PRIMARY KEY,
    real_name VARCHAR(30),
    fictional_name VARCHAR(30)
);

CREATE TABLE Appearance (
    super_hero_fk INT,
    comic_fk INT,
    CONSTRAINT affiliation_super_hero_fk FOREIGN KEY (super_hero_fk) REFERENCES Superheroe(superhero_id),
    CONSTRAINT affiliation_comic_fk FOREIGN KEY (comic_fk) REFERENCES Comic (comic_id),
    PRIMARY KEY (super_hero_fk, comic_fk)
);

CREATE TABLE Squad (
    squad_id INT PRIMARY KEY,
    squad_name VARCHAR(30)
);

CREATE TABLE Affiliation (
    super_hero_fk INT,
    squad_fk INT,
    CONSTRAINT affiliation_super_hero_fk FOREIGN KEY (super_hero_fk) REFERENCES Superheroe(superhero_id),
    CONSTRAINT affiliation_squad_fk FOREIGN KEY (squad_fk) REFERENCES Squad (squad_id),
    PRIMARY KEY (super_hero_fk, squad_fk)
);

CREATE TABLE SuperPower (
    super_power_id INT PRIMARY KEY,
    super_power_name VARCHAR(30),
    super_power_description VARCHAR(30)
);

CREATE TABLE SuperEmpowerMent (
    super_hero_fk INT,
    super_power_fk INT,
    CONSTRAINT empowerment_super_hero_fk FOREIGN KEY (super_hero_fk) REFERENCES Superheroe(superhero_id),
    CONSTRAINT empowerment_power_fk FOREIGN KEY (super_power_fk) REFERENCES SuperPower (super_power_id),
    PRIMARY KEY (super_hero_fk, super_power_fk)
)