Question

Subject (DBA)

use MYSQL workbench select my guitar shop database as default schema

Question text

Problem10

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

Details

• 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

______ DATABASE IF EXISTS ______ ;

CREATE DATABASE household_chores CHARSET _________ ;

________ ;

______   _______ (

         person_id       INT          PRIMARY KEY ______ ,

         first_name    VARCHAR(50) NOT NULL,

         last_name     VARCHAR(50) NOT NULL

) ENGINE = InnoDB;

CREATE TABLE chores (

         chore_id   INT         PRIMARY KEY _______

         name VARCHAR(100) UNIQUE

) _______

CREATE TABLE tasks (

         task_id   INT         PRIMARY KEY AUTO_INCREMENT,

         chore_id INT,

         name   _______ UNIQUE,

         CONSTRAINT fk_tasks_chores

                 _________

                 REFERENCES _______

) _______ = InnoDB;

CREATE TABLE assignments (

         _______    INT         PRIMARY KEY AUTO_INCREMENT,

         person_id   ______ ,

         task_id   INT,

         _______ fk_assignments_people

                 _______

                 REFERENCES _______ ,

         CONSTRAINT fk_assignments_tasks

                 ______

                 _______ tasks (task_id)

_______

Answer 1

The answers for blanks as follows :

DROP DATABASE IF EXISTS household_chores;
CREATE DATABASE household_chores CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE TABLE people (
    person_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name  VARCHAR(50) NOT NULL,
)ENGINE = innoDB;

CREATE TABLE chores(
    chore_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) UNIQUE
)ENGINE = innoDB;

CREATE TABLE tasks(
    task_id INT PRIMARY KEY AUTO_INCREMENT,
    chore_id INT,
    name VARCHAR(100) UNIQUE,
    CONSTRAINT fk_tasks_chores 
    FOREIGN KEY(chore_id)
    REFERENCES chores(chore_id)
)ENGINE = innoDB;

CREATE TABLE assignments(
    chore_id INT PRIMARY KEY AUTO_INCREMENT,
    person_id INT,
    task_id INT,
    CONSTRAINT fk_assignment_people
    FOREIGN KEY(person_id)
    REFERENCES people(person_id),
    CONSTRAINT fk_assignment_tasks
    FOREIGN KEY(task_id)
    REFERENCES tasks(task_id)
)ENGINE = innoDB;