Question

Using MySQL Workbench to write and run the following steps, and take a screenshot for me!

Create a species table and a specimen table.

In the species table, include attributes that reflect the name of the species, a general description of the species, and any other attributes you feel relevant.

In the specimens table, include an ID for the specimen, a reference to the species which it is, the date which it was observed, and any relevant details like height, weight, location, or whatever else you think appropriate.

Select appropriate data types and constraints.

Save the SQL commands for creating the tables as text to include in your lab report. Also, run these commands to create the tables in your database.

Additionally, include an ALTER TABLE ADD and an ALTER TABLE MODIFY command. Make sure these are reasonable for your database and run them as well.

Answer 1

I am using 'student' Database. Replace it with the name of database you like.

1) creating table species.

COMMAND: USE student;
CREATE TABLE species
(
   species_name VARCHAR(30) NOT NULL,
   description VARCHAR(100),
    species_abbreviations VARCHAR(10),
    PRIMARY KEY(species_name)
);

2) Checking table created or not by showing the table.

COMMAND: SELECT * FROM species;

3) Creating table specimen.

COMMAND:

USE student;
CREATE TABLE specimens
(
   specimen_id INT NOT NULL,
    species_nam VARCHAR(30),
    height INT,
    weight INT,
    location VARCHAR(30),
  
    PRIMARY KEY (specimen_id),
  
    FOREIGN KEY (species_nam)
    REFERENCES species(species_name)
);

4) Checking table specimen created or not by showing the table.

COMMAND : SELECT * FROM specimens;

5)ALTER TABLE ADD: For this we are adding a column to species table named 'identifiaction_code'.

COMMAND :

ALTER TABLE species
ADD identification_code INT;

6) Checking whether the column is added or not by showing the table.

COMMAND : SELECT * FROM species;

7) ALTER TABLE MODIFY : For this command we are changing the datatype of 'identification_code' in table species from Int to varchar.

COMMAND :

ALTER TABLE species
MODIFY COLUMN identification_code VARCHAR(10);

8) To check successful modification took place or not we describe the table.

COMMAND : DESCRIBE species;

Ask any doubts if you have in comment section below.

Please rate the answer