Scribd Logo
Upload

Welcome to Scribd!

  • 40 views

    SQL 02 MySQL Design Handout

    Uploaded by

    Alexandre Cabrel
    The document describes the creation of MySQL database tables to store music data. It creates tables for Artists, Albums, Genres, and Tracks with foreign key relationships. It then inserts sample data and writes queries to join the tables and return music data like track titles, artist names, and genres. It also shows how to create a second database for an online learning system with tables for Accounts, Courses, and Membership roles.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    SQL 02 MySQL Design Handout

    Uploaded by

    Alexandre Cabrel
    40 views3 pages
    The document describes the creation of MySQL database tables to store music data. It creates tables for Artists, Albums, Genres, and Tracks with foreign key relationships. It then inserts sample data and writes queries to join the tables and return music data like track titles, artist names, and genres. It also shows how to create a second database for an online learning system with tables for Accounts, Courses, and Membership roles.

    Original Description:

    SQL O2 Emulation

    Original Title

    SQL-02-MySQL-Design-Handout

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document describes the creation of MySQL database tables to store music data. It creates tables for Artists, Albums, Genres, and Tracks with foreign key relationships. It then inserts sample data and writes queries to join the tables and return music data like track titles, artist names, and genres. It also shows how to create a second database for an online learning system with tables for Accounts, Courses, and Membership roles.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    40 views3 pages

    SQL 02 MySQL Design Handout

    Uploaded by

    Alexandre Cabrel
    The document describes the creation of MySQL database tables to store music data. It creates tables for Artists, Albums, Genres, and Tracks with foreign key relationships. It then inserts sample data and writes queries to join the tables and return music data like track titles, artist names, and genres. It also shows how to create a second database for an online learning system with tables for Accounts, Courses, and Membership roles.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 3

    17/11/2021 11:33 https://www.wa4e.com/lectures/SQL-02-MySQL-Design-Handout.

    txt

    CREATE DATABASE Music DEFAULT CHARACTER SET utf8;

    USE Music; (Command line only)

    CREATE TABLE Artist (

    artist_id INTEGER NOT NULL AUTO_INCREMENT,

    name VARCHAR(255),

    PRIMARY KEY(artist_id)

    ) ENGINE = InnoDB;

    CREATE TABLE Album (

    album_id INTEGER NOT NULL AUTO_INCREMENT,

    title VARCHAR(255),

    artist_id INTEGER,

    PRIMARY KEY(album_id),

    INDEX USING BTREE (title),

    CONSTRAINT FOREIGN KEY (artist_id)

    REFERENCES Artist (artist_id)

    ON DELETE CASCADE ON UPDATE CASCADE

    ) ENGINE = InnoDB;

    CREATE TABLE Genre (

    genre_id INTEGER NOT NULL AUTO_INCREMENT,

    name VARCHAR(255),

    PRIMARY KEY(genre_id)

    ) ENGINE = InnoDB;

    CREATE TABLE Track (

    track_id INTEGER NOT NULL AUTO_INCREMENT,

    title VARCHAR(255),

    len INTEGER,

    rating INTEGER,

    count INTEGER,

    album_id INTEGER,

    genre_id INTEGER,

    PRIMARY KEY(track_id),

    INDEX USING BTREE (title),

    CONSTRAINT FOREIGN KEY (album_id) REFERENCES Album (album_id)

    ON DELETE CASCADE ON UPDATE CASCADE,

    CONSTRAINT FOREIGN KEY (genre_id) REFERENCES Genre (genre_id)

    ON DELETE CASCADE ON UPDATE CASCADE

    ) ENGINE = InnoDB;

    INSERT INTO Artist (name) VALUES ('Led Zepplin');

    INSERT INTO Artist (name) VALUES ('AC/DC');

    INSERT INTO Genre (name) VALUES ('Rock');

    INSERT INTO Genre (name) VALUES ('Metal');

    INSERT INTO Album (title, artist_id) VALUES ('Who Made Who', 2);

    INSERT INTO Album (title, artist_id) VALUES ('IV', 1);

    INSERT INTO Track (title, rating, len, count, album_id, genre_id)

    VALUES ('Black Dog', 5, 297, 0, 2, 1);

    INSERT INTO Track (title, rating, len, count, album_id, genre_id)

    VALUES ('Stairway', 5, 482, 0, 2, 1);

    INSERT INTO Track (title, rating, len, count, album_id, genre_id)

    VALUES ('About to Rock', 5, 313, 0, 1, 2);

    INSERT INTO Track (title, rating, len, count, album_id, genre_id)

    https://www.wa4e.com/lectures/SQL-02-MySQL-Design-Handout.txt 1/3
    17/11/2021 11:33 https://www.wa4e.com/lectures/SQL-02-MySQL-Design-Handout.txt
    VALUES ('Who Made Who', 5, 207, 0, 1, 2);

    SELECT Album.title, Artist.name FROM Album JOIN Artist ON

    Album.artist_id = Artist.artist_id

    SELECT Album.title, Album.artist_id, Artist.artist_id, Artist.name

    FROM Album JOIN Artist ON Album.artist_id = Artist.artist_id

    SELECT Track.title, Track.genre_id, Genre.genre_id, Genre.name

    FROM Track JOIN Genre

    SELECT Track.title, Genre.name FROM Track JOIN Genre ON

    Track.genre_id = Genre.genre_id;

    SELECT Track.title, Artist.name, Album.title, Genre.name

    FROM Track JOIN Genre JOIN Album JOIN Artist

    ON Track.genre_id = Genre.genre_id AND Track.album_id =

    Album.album_id AND Album.artist_id = Artist.artist_id

    DELETE FROM Genre WHERE name = 'Metal'

    DROP TABLE Track; DROP TABLE Album; DROP TABLE Genre; DROP TABLE Artist;

    Fresh Database...

    CREATE DATABASE Learning DEFAULT CHARACTER SET utf8 ;

    USE Learning; (Command line only)

    CREATE TABLE Account (

    account_id INTEGER NOT NULL AUTO_INCREMENT,

    email VARCHAR(128) UNIQUE,

    name VARCHAR(128),

    PRIMARY KEY(account_id)

    ) ENGINE=InnoDB CHARACTER SET=utf8;

    CREATE TABLE Course (

    course_id INTEGER NOT NULL AUTO_INCREMENT,

    title VARCHAR(128) UNIQUE,

    PRIMARY KEY(course_id)

    ) ENGINE=InnoDB CHARACTER SET=utf8;

    CREATE TABLE Member (

    account_id INTEGER,

    course_id INTEGER,

    role INTEGER,

    CONSTRAINT FOREIGN KEY (account_id) REFERENCES Account (account_id)

    ON DELETE CASCADE ON UPDATE CASCADE,

    CONSTRAINT FOREIGN KEY (course_id) REFERENCES Course (course_id)

    ON DELETE CASCADE ON UPDATE CASCADE,

    PRIMARY KEY (account_id, course_id)

    ) ENGINE=InnoDB CHARACTER SET=utf8;

    INSERT INTO Account (name, email) VALUES ('Jane', '[email protected]');

    INSERT INTO Account (name, email) VALUES ('Ed', '[email protected]');

    INSERT INTO Account (name, email) VALUES ('Sue', '[email protected]');

    INSERT INTO Course (title) VALUES ('Python');

    INSERT INTO Course (title) VALUES ('SQL');

    INSERT INTO Course (title) VALUES ('PHP');

    INSERT INTO Member (account_id, course_id, role) VALUES (1, 1, 1);

    INSERT INTO Member (account_id, course_id, role) VALUES (2, 1, 0);

    https://www.wa4e.com/lectures/SQL-02-MySQL-Design-Handout.txt 2/3
    17/11/2021 11:33 https://www.wa4e.com/lectures/SQL-02-MySQL-Design-Handout.txt

    INSERT INTO Member (account_id, course_id, role) VALUES (3, 1, 0);

    INSERT INTO Member (account_id, course_id, role) VALUES (1, 2, 0);

    INSERT INTO Member (account_id, course_id, role) VALUES (2, 2, 1);

    INSERT INTO Member (account_id, course_id, role) VALUES (2, 3, 1);

    INSERT INTO Member (account_id, course_id, role) VALUES (3, 3, 0);

    SELECT Account.name, Member.role, Course.title

    FROM Account JOIN Member JOIN Course

    ON Member.account_id = Account.account_id

    AND Member.course_id = Course.course_id

    ORDER BY Course.title, Member.role DESC, Account.name

    https://www.wa4e.com/lectures/SQL-02-MySQL-Design-Handout.txt 3/3

    You might also like