--
--


SELECT pg_catalog.set_config('search_path', '', false);



--
-- Name: hackathon; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.hackathon (
    title text NOT NULL,
    start_date date,
    end_date date,
    description text
);


ALTER TABLE public.hackathon OWNER TO postgres;

--
-- Name: judge; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.judge (
    name text NOT NULL,
    affiliation text,
    "position" text
);


ALTER TABLE public.judge OWNER TO postgres;

--
-- Name: organizer; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.organizer (
    student character(6) NOT NULL,
    hackathon text NOT NULL,
    role text NOT NULL
);


ALTER TABLE public.organizer OWNER TO postgres;

--
-- Name: project; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.project (
    team text NOT NULL,
    hackathon text NOT NULL,
    title text NOT NULL
);


ALTER TABLE public.project OWNER TO postgres;

--
-- Name: rating; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.rating (
    judge text NOT NULL,
    team text NOT NULL,
    hackathon text NOT NULL,
    project text NOT NULL,
    rating double precision
);


ALTER TABLE public.rating OWNER TO postgres;

--
-- Name: sponsorship; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.sponsorship (
    hackathon text NOT NULL,
    sponsor text NOT NULL,
    amount numeric(10,2)
);


ALTER TABLE public.sponsorship OWNER TO postgres;

--
-- Name: student; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.student (
    uin character(6) NOT NULL,
    name text,
    major text,
    year integer
);


ALTER TABLE public.student OWNER TO postgres;

--
-- Name: student_team; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.student_team (
    teamname text NOT NULL,
    hackathon text NOT NULL,
    student character(6) NOT NULL
);


ALTER TABLE public.student_team OWNER TO postgres;

--
-- Name: team; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.team (
    teamname text NOT NULL,
    hackathon text NOT NULL
);


ALTER TABLE public.team OWNER TO postgres;

--
-- Data for Name: hackathon; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.hackathon (title, start_date, end_date, description) FROM stdin;
Sparkhacks 2024	2024-02-08	2024-02-11	UIC-based event
Sparkhacks 2023	2023-02-01	2023-02-02	UIC-based event
Chasehacks 2022	2022-01-01	2022-01-02	Bank-organized
\.


--
-- Data for Name: judge; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.judge (name, affiliation, "position") FROM stdin;
Lazy young prof	UIC	Assistant Professor
Lazy old prof	UIC	Full Professor
No morals industry guy	UIC	Associate Professor
\.


--
-- Data for Name: organizer; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.organizer (student, hackathon, role) FROM stdin;
366666	Sparkhacks 2024	director
234242	Sparkhacks 2024	outreach
366666	Sparkhacks 2023	director
111111	Chasehacks 2022	director
\.


--
-- Data for Name: project; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.project (team, hackathon, title) FROM stdin;
The bosses	Sparkhacks 2024	Boring React Mockup
The bosses	Sparkhacks 2023	Convoluted Haskell Nonsense
The bosses	Chasehacks 2022	Verbose Java Mess
The noobs	Sparkhacks 2024	Too many fancy frameworks
Hack On	Sparkhacks 2024	Static HTML to the Max
Hack On	Sparkhacks 2023	Unnecessary Assembly
\.


--
-- Data for Name: rating; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.rating (judge, team, hackathon, project, rating) FROM stdin;
Lazy young prof	The bosses	Sparkhacks 2024	Boring React Mockup	2
Lazy old prof	The bosses	Sparkhacks 2024	Boring React Mockup	5
No morals industry guy	The bosses	Sparkhacks 2024	Boring React Mockup	3.5
Lazy young prof	The bosses	Sparkhacks 2023	Convoluted Haskell Nonsense	5
Lazy old prof	The bosses	Sparkhacks 2023	Convoluted Haskell Nonsense	1
No morals industry guy	The bosses	Chasehacks 2022	Verbose Java Mess	5
Lazy young prof	The noobs	Sparkhacks 2024	Too many fancy frameworks	2.5
Lazy old prof	The noobs	Sparkhacks 2024	Too many fancy frameworks	4
No morals industry guy	The noobs	Sparkhacks 2024	Too many fancy frameworks	4.5
Lazy young prof	Hack On	Sparkhacks 2024	Static HTML to the Max	1
Lazy old prof	Hack On	Sparkhacks 2024	Static HTML to the Max	3
No morals industry guy	Hack On	Sparkhacks 2024	Static HTML to the Max	1.5
Lazy young prof	Hack On	Sparkhacks 2023	Unnecessary Assembly	1
Lazy old prof	Hack On	Sparkhacks 2023	Unnecessary Assembly	5
\.


--
-- Data for Name: sponsorship; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.sponsorship (hackathon, sponsor, amount) FROM stdin;
Sparkhacks 2024	UIC	3500.00
Sparkhacks 2024	Chase	500.00
Sparkhacks 2024	Oracle	10000.00
Sparkhacks 2023	UIC	4500.00
Chasehacks 2022	Chase	20000.00
Chasehacks 2022	UIC	250.00
\.


--
-- Data for Name: student; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.student (uin, name, major, year) FROM stdin;
111111	Peter	CS	1
112333	Bob	CS	2
443223	Alice	EE	3
234242	Veeta	CS	2
366666	Fauly	CS	6
554344	Rufus	BIO	2
777777	Gertrud	CS	1
\.


--
-- Data for Name: student_team; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.student_team (teamname, hackathon, student) FROM stdin;
The bosses	Sparkhacks 2024	366666
The bosses	Sparkhacks 2024	443223
The bosses	Sparkhacks 2023	366666
The bosses	Sparkhacks 2023	443223
The bosses	Chasehacks 2022	366666
The bosses	Chasehacks 2022	443223
The bosses	Sparkhacks 2024	111111
The noobs	Sparkhacks 2024	777777
The noobs	Sparkhacks 2024	554344
The noobs	Sparkhacks 2024	443223
Hack On	Sparkhacks 2024	234242
Hack On	Sparkhacks 2024	112333
Hack On	Sparkhacks 2023	234242
Hack On	Sparkhacks 2023	112333
\.


--
-- Data for Name: team; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.team (teamname, hackathon) FROM stdin;
The bosses	Sparkhacks 2024
The bosses	Sparkhacks 2023
The bosses	Chasehacks 2022
The noobs	Sparkhacks 2024
Hack On	Sparkhacks 2024
Hack On	Sparkhacks 2023
\.


--
-- Name: hackathon hackathon_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.hackathon
    ADD CONSTRAINT hackathon_pkey PRIMARY KEY (title);


--
-- Name: judge judge_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.judge
    ADD CONSTRAINT judge_pkey PRIMARY KEY (name);


--
-- Name: organizer organizer_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.organizer
    ADD CONSTRAINT organizer_pkey PRIMARY KEY (student, hackathon, role);


--
-- Name: project project_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.project
    ADD CONSTRAINT project_pkey PRIMARY KEY (team, hackathon, title);


--
-- Name: rating rating_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.rating
    ADD CONSTRAINT rating_pkey PRIMARY KEY (judge, team, hackathon, project);


--
-- Name: sponsorship sponsorship_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.sponsorship
    ADD CONSTRAINT sponsorship_pkey PRIMARY KEY (hackathon, sponsor);


--
-- Name: student student_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.student
    ADD CONSTRAINT student_pkey PRIMARY KEY (uin);


--
-- Name: student_team student_team_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.student_team
    ADD CONSTRAINT student_team_pkey PRIMARY KEY (teamname, hackathon, student);


--
-- Name: team team_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.team
    ADD CONSTRAINT team_pkey PRIMARY KEY (teamname, hackathon);


--
-- Name: organizer organizer_hackathon_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.organizer
    ADD CONSTRAINT organizer_hackathon_fkey FOREIGN KEY (hackathon) REFERENCES public.hackathon(title) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: organizer organizer_student_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.organizer
    ADD CONSTRAINT organizer_student_fkey FOREIGN KEY (student) REFERENCES public.student(uin) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: project project_hackathon_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.project
    ADD CONSTRAINT project_hackathon_fkey FOREIGN KEY (hackathon) REFERENCES public.hackathon(title) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: project project_team_hackathon_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.project
    ADD CONSTRAINT project_team_hackathon_fkey FOREIGN KEY (team, hackathon) REFERENCES public.team(teamname, hackathon) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: rating rating_judge_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.rating
    ADD CONSTRAINT rating_judge_fkey FOREIGN KEY (judge) REFERENCES public.judge(name) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: rating rating_team_hackathon_project_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.rating
    ADD CONSTRAINT rating_team_hackathon_project_fkey FOREIGN KEY (team, hackathon, project) REFERENCES public.project(team, hackathon, title) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: sponsorship sponsorship_hackathon_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.sponsorship
    ADD CONSTRAINT sponsorship_hackathon_fkey FOREIGN KEY (hackathon) REFERENCES public.hackathon(title) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: student_team student_team_student_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.student_team
    ADD CONSTRAINT student_team_student_fkey FOREIGN KEY (student) REFERENCES public.student(uin) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: student_team student_team_teamname_hackathon_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.student_team
    ADD CONSTRAINT student_team_teamname_hackathon_fkey FOREIGN KEY (teamname, hackathon) REFERENCES public.team(teamname, hackathon) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: team team_hackathon_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.team
    ADD CONSTRAINT team_hackathon_fkey FOREIGN KEY (hackathon) REFERENCES public.hackathon(title) ON UPDATE CASCADE ON DELETE CASCADE;


-- ********************************************************************************
-- insert prices
-- ********************************************************************************
-- INSERT INTO price
-- VALUES
-- ('Chasewin', 'Chase', 350.0, 'Sparkhacks 2024','The bosses'),
-- ('Cloudwin', 'Oracle', 550.0, 'Sparkhacks 2024','The noobs'),
-- ('Good win', 'UIC', 750.0, 'Sparkhacks 2024','The noobs'),
-- ('Good win', 'UIC', 150.0, 'Sparkhacks 2023','The bosses'),
-- ('Chasewin', 'Chase', 250.0, 'Sparkhacks 2023','Hack On'),
-- ('Lota Money', 'Chase', 2000.0, 'Chasehacks 2022','The bosses');

--
--
