G1_2_DDL_Scripts.txt

CREATE TABLE Country (
	Country_ID char(3) PRIMARY KEY, 
	Country_Name varchar(100) NOT NULL, 
	Continent varchar(100) NOT NULL
);
CREATE TABLE Population( 
	Country_ID char(3),
	Year integer,
	Total_Population bigint NOT NULL,
	Male_Population bigint NOT NULL,
	Female_Population bigint NOT NULL,
	Life_Expectancy decimal (4,2) NOT NULL,
	Infant_Mortality_Rate decimal(4,2) CHECK (Infant_Mortality_Rate<100) NOT NULL, 
	Primary Key (Country_ID, Year),
	Foreign Key (Country_ID) references Country(Country_ID)
);

CREATE TABLE Awareness_Events(
	Country_ID char(3),
	Year integer,
	Topic varchar(100),
	Donation_Collected bigint NOT NULL,
	Primary Key(Country_ID, Year, Topic),
	Foreign Key(Country_ID) references Country(Country_ID)
);

CREATE TABLE Ngo_Collaboration( Country_ID char(3),
	Year integer,
	Topic varchar(100),
	NGO_Collaborated varchar(100),
	Primary Key(Country_ID, Year, Topic, NGO_Collaborated),
	Foreign Key(Country_ID, Year, Topic) references Awareness_Events(Country_ID, Year, Topic) 
	
);

CREATE TABLE Environmental_Factors( 
	Country_ID char(3),
	Year integer,
	CO2_Emmission_Intensities decimal(6,2) NOT NULL, 
	CFCs_Consumption decimal(6,2) NOT NULL, 
	SOx_emissions decimal(6,2) NOT NULL,
	NOx_emissions decimal(6,2) NOT NULL, 
	Waste_Generation_Intensity decimal(6,2) NOT NULL, 
	Intensity_of_Water_Usage decimal(6,2) NOT NULL,
	Primary Key(Country_ID, Year),
	Foreign Key(Country_ID) references Country(Country_ID) 
);

CREATE TABLE Treaty( 
	Treaty_ID integer,
	Treaty_Name varchar(100) NOT NULL,
	Year integer,
	Duration_of_Treaty integer NOT NULL, 
	Location_Signed varchar(100) NOT NULL, 
	Primary Key (Treaty_ID)
);
CREATE TABLE Treaty_Signed( 
	Treaty_ID integer,
	Country_ID char(3),
	Primary Key(Treaty_ID, Country_ID),
	Foreign Key(Treaty_ID) references Treaty(Treaty_ID), 
	Foreign Key(Country_ID) references Country(Country_ID)
);

CREATE TABLE Diseases (
Disease_ID integer primary key,
Disease_type varchar (100) not null, /* chronic, communicable, ec.*/ 
Disease_name varchar (100) not null unique,
Pathogen varchar(100) /* bacteria, virus or other*/
);

Create Table Curing_method(
	/* since curing type is a multivalued key*/ 
	Disease_ID integer references Diseases(Disease_ID),
	Cure_Type varchar (100), /* either vaccine, medication, antibiotics, drugging, etc.*/ 
	Primary Key(Disease_ID, Cure_Type)
);
CREATE TABLE Funds(
	Funds_ID integer Primary Key, 
	Funds_Recieved integer, 
	Usage varchar(100) NOT NULL
);

Create Table Disease_Affected (
Country_code char (3) references Country(Country_ID), 
	Disease_ID integer references Diseases(Disease_ID), 
	Year integer not null,
	Affected_people integer,
	Deaths integer,
	Disease_Adversity varchar (100),
	Funds_ID integer references Funds(Funds_ID),
	primary key (Country_code, Disease_ID,Year)
);

Create table Disaster (
	Disaster_ID integer primary key, 
	Disaster_Group varchar (100) not null, 
	Disaster_Sub_Group varchar (100) not null, 
	Disaster_Type varchar (100) not null, 
	Disaster_Sub_Type varchar (100) not null, 
 	Scale_Type varchar(100)
);

Create table Disaster_Affected(
	Country_code char (3) references Country(Country_ID), 
	Disaster_ID integer references Disaster(Disaster_ID), 
	Focus_Region varchar (100),
	Affected_people integer,
	Deaths integer,
	Date date not null,
	Scale_value decimal (2,1),
	Funds_ID integer references Funds(Funds_ID),
	primary key (Country_code, Disaster_ID,Date)
);

Create Table Research(
	Institute_ID char (8) primary key,
	Country_ID char (3) references Country(Country_ID), 
	Institute_Name varchar (100) not null
);

create table Researcher_Information(
	Institute_ID char (8) references Research(Institute_ID), 
	Researcher_ID char (3),
	Name varchar (100),
	Age integer,
	primary key (Institute_ID, Researcher_ID)
);

Create table Research_subject_information(
	Institute_ID char (8) references Research(Institute_ID), 
	Research_subject varchar (100) references Diseases(Disease_name), 
	primary key(Institute_ID,Research_subject)
);

CREATE TABLE Hospital(
	Hospital_ID char (5),
	Hospital_Name varchar(100) not null,
	Area_Spread decimal(4,1), --Sq.km
	Country_ID char (3) references Country(Country_ID), 
	Primary key(Hospital_ID)
);

Create Table Patients(
	Patient_ID char(6),
	Name varchar (100) not null,
	Age integer not null,
	Gender char (1) not null,
	Blood_Group varchar (10) not null,
	Reason_for_check varchar (100) not null, Admitted_days integer,
	Operation varchar (100),
	Hospital_ID char (5) references Hospital(Hospital_ID), 
	Primary Key(Patient_ID, Hospital_ID)
);

Create table Staff(
	Staff_ID char (8),
	Name varchar (100) not null,
	Age integer not null,
	Gender char (1) not null,
	Staff_Role varchar (100) not null,
	Qualification char (5) ,
	Hospital_ID char (5) references Hospital (Hospital_ID), 
	Primary Key(Staff_ID, Hospital_ID)
);

CREATE TABLE Production(
	Production_ID integer Primary Key, 
	Company_Name varchar(100) NOT NULL
);

CREATE TABLE Production_Location(
	Country_ID char(3),
	Production_ID integer,
	Primary Key(Country_ID, Production_ID),
	Foreign Key(Country_ID) references Country(Country_ID),
	Foreign Key(Production_ID) references Production(Production_ID)
);

CREATE TABLE Medicine(
	Medicine_ID integer,
	Medicine_Name varchar(100),
	Disease_ID integer NOT NULL,
	Composition varchar(100) NOT NULL,
	Primary Key(Medicine_ID),
	Foreign Key(Disease_ID) references diseases(Disease_ID)
);

CREATE TABLE Medicine_Production( 
	Production_ID integer,
	Medicine_ID integer,
	Country_ID char(3), 
	Units_Produced integer NOT NULL, 
	MRP decimal (5,3) NOT NULL,
	Primary Key(Production_ID, Medicine_ID, Country_ID),
	Foreign Key(Production_ID,Country_ID) references Production_Location(Production_ID,Country_ID), 
	Foreign Key(Medicine_ID) references Medicine(Medicine_ID)
);

CREATE TABLE Country_Currency( Country_ID char(3),
	Currency varchar(100),
	Primary Key(Country_ID,Currency),
	Foreign Key(Country_ID) references Country(Country_ID)
);