Using the diagram write SQL code that will accomplish the following. Please labe

Question

Using the diagram write SQL code that will accomplish the following.
Please label steps 1-5.
1.Create all of the tables for this model, making sure to create relationships between them
2.Add a column to two of the tables, any column that you want on any two tables that you want
3.Add a check constraint to one of the columns that you created in step 2
4.Drop one of the columns that you created in step 2
5.Insert one record into each of the tables that you created in step 1

STAR GALAXY PK GALAXYID PK FK GALAXY ID GALAXY NAME POSITION CLASSIFICATION MOTION DIAMETER DISTANCE AGE BRIGHTNESS AGE LIFE FORM PK LIFE FORM ID PLANET CELLULAR CLASS PLANET LIFE FORM PLANET ID PK PLANET ID P STAR ID FK TO LIFE FORM ID PLANET NAME AVG SIZE POPULATION ROTATION TEMPERATURE MASS DIAMETER AGE

Explanation / Answer

1.Create Tables:

CREATE TABLE GALAXY(

GALAXY_ID INT NOT NULL,

GALAXY_NAME VARCHAR(25) NOT NULL,

CLASSIFICATIONS VARCHAR(50),

DIAMETER VARCHAR(30),

AGE INT,

PRIMARY KEY (GALAXY_ID)

);

CREATE TABLE STAR(

STAR_ID INT NOT NULL,

GALAXY_ID INT NOT NULL,

POSITION VARCHAR(20),

MOTION VARCHAR(20),

DISTANCE VARCHAR(20),

BRIGHTNESS VARCHAR(20),

AGE INT,

PRIMARY KEY (STAR_ID),

FOREIGN KEY (GALAXY_ID) REFERENCES GALAXY(GALAXY_ID)

);

CREATE TABLE PLANET(

PLANET_ID INT NOT NULL,

STAR_ID INT NOT NULL,

PLANET_NAME VARCHAR(30),

REVOLUTION VARCHAR(30),

ROTATION VARCHAR(20),

TEMPERATURE VARCHAR(20),

MASS VARCHAR(20),

DIAMETER INT,

AGE INT,

PRIMARY KEY (PLANET_ID),

FOREIGN KEY (STAR_ID), REFRENCES STAR(STAR_ID)

);

CREATE TABLE LIFE_FORM(

LIFE_FORM_ID INT NOT NULL,

CELLULAR_CLASS INT,

MOBILITY VARCHAR(20),

IQ VARCHAR(30),

AVG_SIZE VARCHAR(20),

POPULATION VARCHAR(30),

PRIMARY KEY (LIFE_FORM_ID)

);

CREATE TABLE PLANET_LIFE_FORM(

PLANET_ID INT NOT NULL,

LIFE_FORM_ID INT NOT NULL,

FOREIGN KEY (PLANET_ID) REFERENCES PLANET(PLANET_ID),

FOREIGN KEY (LIFE_FORM_ID) REFERENCES LIFE-FORM(LIFE_FORM_ID)

);

2. Add a column to 2 of the tables:

Adding Radius column to STAR table

         ALTER TABLE STAR ADD RADIUS NUMERIC;

Adding Galaxy_type to GALAXY table

     ALTER TABLE GALAXY ADD GALAXY_TYPE VARCHAR(40);

3. Add check constraint

    Adding check constraint on RADIUS column of STAR table

           ALTER TABLE STAR ADD CONSTRAINT CHK_RADIUS CHECK (RADIUS<=8);

4. DROP ONE OF THE COLUMN

    Dropping GALAXY_TYPE column of GALAXY table

      ALTER TABLE GALAXY DROP COLUMN GALAXY_TYPE;

5. INSERT ONE RECORD IN EACH TABLE

Note: inserted with junk values

INSERT INTO GALAXY VALUES(1, 'MILKY WAY', 'SPIRAL','100000LIGHTYEARS',13);

INSERT INTO STAR VALUES(10, 1,'north','circle','433lightyears','4.6timesbrighter',7,1);

INSERT INTO PLANET VALUES(20,10,earth,'as','as','23','234',20,67);

INSERT INTO LIFE_FORM VALUES(30,5,'qw','34f','jhg','qe8');

INSERT INTO PLANET_LIFE_FORM VALUES(20,30);

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.