christdave/leaderboard

Leader Board

Outline

Outline	Value
Course	CSI 2532
Date	Winter 2021
Professor	Andrew Forward, [email protected]
TA	Kalonji Kalala, [email protected]
TA	Lintian Wang, [email protected]
Team	Andrew Forward 1484511 Ayana Forward 9021000

Deliverables

Deliverable 1 (5%) Hello World

Mark	Description	Comment
2.0	GitHub repository setup	GitHub Repo
2.5	ER model	See below
2.5	Relational model / SQL schema	Both image and SQL below
1.0	SQL examples to INSERT, UPDATE, SELECT and DELETE data	See examples below
1.0	README.md contains all required information	See this page
1.0	Git usage (commit messages, all students involved)	See commit details in GitHub
/ 10

Application Description

The leaderboard database models an athlete, including details such as their name, date of birth, and identified gender.

ER Model

The ER diagram was created with Lucidchart.

Relational Model

The Relational Model (diagram) was also created with Lucidchart.

SQL Schema

This was tested using Online SQL Interpreter available with the textbook.

CREATE TABLE athletes (
  id int,
  identifier varchar(50),
  created timestamp,
  modified timestamp,
  name varchar(50),
  dob date,
  identified_gender varchar(6),
  PRIMARY KEY (id)
);

Example SQL Queries

After running the above schema, you can test the queries below in the Online SQL Interpreter Refresh the browser if you want to start over.

INSERT INTO athletes (id, name, identified_gender, dob)
VALUES
(1, 'Andrew', 'm', '1986-12-01'),
(2, 'Ayana', 'F', '1998-06-11'),
(3, 'Hayden', 'm', '1996-07-24'),
(4, 'August', 'm', '1999-09-09');

Let's find all 'F' athletes.

SELECT *
FROM athletes
WHERE identified_gender = 'F';

Let's update all 'm's to 'M's.

UPDATE athletes
SET identified_gender = 'M'
WHERE identified_gender = 'm';

And now all 'M' athletes.

SELECT *
FROM athletes
WHERE identified_gender = 'M';

Let's delete all athletes.

DELETE FROM athletes;

And now the table is empty.

SELECT count(*)
FROM athletes;