USE master
GO

IF EXISTS(SELECT * FROM master.dbo.sysdatabases WHERE name = 'HelloWorld')
BEGIN
    DROP DATABASE [HelloWorld]
END
GO

CREATE DATABASE [HelloWorld]
GO

USE [HelloWorld]
GO

/******************************************************
  Tables  Begin
******************************************************/
/* Create new table "STAR".                                                                          */
/* "STAR" : A self-luminous celestial body consisting of a mass of gas held together by              */
/*          its own gravity in which the energy generated by nuclear reactions in the interior is    */
/*          balanced by the outflow of energy to the surface, and the inward-directed gravitational  */
/*          forces are balanced by the outward-directed gas and radiation pressures                  */
/* 	"STAR_ID" : The primary key                                                                      */
/* 	"NAME" : Name identifies Star                                                                    */  
create table "STAR" ( 
	"STAR_ID" int IDENTITY(1,1) not null,
	"NAME" varchar(20) not null)  

go

alter table "STAR"
	add constraint "STAR_PK" primary key ("STAR_ID")   


go

/* Create new table "PLANETARY_SATELLITE".                                     */
/* "PLANETARY_SATELLITE" : A natural satellite revolving around a planet.      */
/* 	"PLANETARY_SATELLITE_ID" : A natural satellite revolving around a planet.  */
/* 	"PLANET_ID" : Foreign key to the PLANET table                              */
/* 	"NAME" : Name identifies Planetary Satellite                               */  
create table "PLANETARY_SATELLITE" ( 
	"PLANETARY_SATELLITE_ID" int IDENTITY(1,1) not null,
	"PLANET_ID" int not null,
	"NAME" varchar(20) not null)  

go

alter table "PLANETARY_SATELLITE"
	add constraint "PLANETARY_SATELLITE_PK" primary key ("PLANETARY_SATELLITE_ID")   

go

/* Create new table "PLANET".                                                                 */
/* "PLANET" : A nonluminous celestial body larger than an asteroid or comet, illuminated by   */
/*            light from a star, such as the sun, around which it revolves.                   */
/* 	"PLANET_ID" : The primary key                                                             */ 
/* 	"STAR_ID" : Foreign key to the STAR table                                                 */
/* 	"NAME" : Name identifies Planet                                                           */
/* 	"ROTATIONAL_PERIOD" : The amount of time required for the planet to perform one           */
/*                            complete rotation about its own axis relative to the earth.     */
/*                            If the planet rotates on its axis in a manner opposite to that  */
/*                            of Earth, the rotation is called retrograde and the number is   */
/*                            given with a - sign in front of it.                             */  
create table "PLANET" ( 
	"PLANET_ID" int IDENTITY(1,1) not null,
	"STAR_ID" int not null,
	"NAME" varchar(20) not null,
	"ROTATIONAL_PERIOD" decimal(10,6) not null)  

go

alter table "PLANET"
	add constraint "PLANET_PK" primary key ("PLANET_ID")   

go


/* Add the remaining keys, constraints and indexes for the table "STAR".                      */
alter table "STAR" add constraint "STAR_UC1" unique (
	"NAME")  

go

/* Add the remaining keys, constraints and indexes for the table "PLANETARY_SATELLITE".       */
alter table "PLANETARY_SATELLITE" add constraint "PLANETARY_SATELLITE_UC1" unique (
	"NAME")  

go

/* Add the remaining keys, constraints and indexes for the table "PLANET".                    */
alter table "PLANET" add constraint "PLANET_UC1" unique (
	"NAME")  

go

/* Add foreign key constraints to table "PLANETARY_SATELLITE".                                */
alter table "PLANETARY_SATELLITE"
	add constraint "PLANET_PLANETARY_SATELLITE_FK1" foreign key (
		"PLANET_ID")
	 references "PLANET" (
		"PLANET_ID") on update no action on delete no action  

go

/* Add foreign key constraints to table "PLANET".                                             */
alter table "PLANET"
	add constraint "STAR_PLANET_FK1" foreign key (
		"STAR_ID")
	 references "STAR" (
		"STAR_ID") on update no action on delete no action  

go

/******************************************************
  Load the STAR table
******************************************************/
INSERT INTO STAR([NAME]) VALUES ('Sun')
go

/******************************************************
  Load the PLANET table
******************************************************/
INSERT INTO PLANET([STAR_ID], [NAME], [ROTATIONAL_PERIOD]) VALUES (1, 'Earth', 0.99727)
go