Question

subject DBA

use mysql workbench and select my guitar shop database as default schema

Problem8

Write a script that implements the following design in a database named my_web_db:

    users                                                       downloads                                                                 Products

*user_id INT                                         * download_id INT                                                   * product_id INT                     

*email_address VARCHAR(100)          * user_id INT                                                         * product_name VARCHAR(45)

*first_name VARCHAR(45)                  *download_date DATETIME

*last_name VARCHAR(45)                  * filename VARCHAR(50)

                                                               *product_id INT

Details

• In the downloads table, the user_id and product_id columns are the foreign keys.
• Include a statement to drop the database if it already exists.
• Include statements to create and select the database.
• Include any indexes that you think are necessary.
• Specify the utf8 character set for all tables.
• Specify the InnoDB storage engine for all tables.

Solution fill in the blanks :-

______DATABASE ____________ ;

CREATE DATABASE my_web_db ___________ ;

USE my_web_db;

_________ TABLE _______

    user_id       INT          PRIMARY _______ AUTO_INCREMENT,

    email_address VARCHAR(100) UNIQUE,

    first_name    VARCHAR(45) NOT NULL,

    last_name     VARCHAR(45) NOT NULL

) ___________ ;

__________ products (

    product_id   INT         ________ AUTO_INCREMENT,

    product_name VARCHAR(45) UNIQUE

__________

____________

    download_id   INT         PRIMARY KEY,

    ____________       INT         NOT NULL,

    download_date DATETIME    NOT NULL,

    filename      __________ NOT NULL,

    product_id    INT         NOT NULL,

    ___________ fk_downloads_users

_____________ KEY (user_id )

        REFERENCES users (user_id),

_______________

_______________ product_id)

        REFERENCES products _______________

) ENGINE = _________ ;

Answer 1

DROP DATABASE IF EXISTS my_web_db;/* statement to drop the database if it already exists.*/

CREATE DATABASE my_web_db; /* blank here should be empty as the syntax for creating the database is already given correct*/

USE my_web_db; /* selects the database my_web_db*/

CREATE TABLE IF NOT EXISTS users (

user_id    INT            PRIMARY KEY       AUTO_INCREMENT,
email_address    VARCHAR(100) UNIQUE,
first_name Varchar(45) not null,
last_name    VARCHAR(45) not null
) ENGINE = innoDB; /*Specify the InnoDB storage engine for all tables.*/

CREATE TABLE IF NOT EXISTS products(

product_id    INT PRIMARY KEY   AUTO_INCREMENT,
product_name    VARCHAR(45) UNIQUE
) ENGINE = innoDB; /*Specify the InnoDB storage engine for all tables.*/

CREATE TABLE IF NOT EXISTS downloads (

download_id   INT         PRIMARY KEY,

user_id INT NOT NULL,

download_date DATETIME    NOT NULL,

filename VARCHAR(45) NOT NULL,

product_id    INT   NOT NULL,

CONSTRAINT users_fk_downloads FOREIGN KEY (user_id)/*User_id is a foreign key*/

REFERENCES users (user_id),

CONSTRAINT products_fk_downloads

FOREIGN KEY (product_id) /*product_id is a foreign key/

REFERENCES products (product_id)

) ENGINE = innoDB; /*Specify the InnoDB storage engine for all tables.*/

ALTER SCHEMA `my_web_db` DEFAULT CHARACTER SET utf8 ;/*utf8 character set for all tables.*/