Outline | Value |
---|---|
Course | CSI 2532 |
Date | Winter 2020 |
Professor | Andrew Forward |
TA 1 | Kyle Quintal |
TA 2 | Lintian Wang |
Team | Andrew Forward 1484511 Ayana 9021000 |
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 |
The leaderboard database models an athlete, including details such as their name, date of birth, and identified gender.
The ER diagram was created with Lucidchart.
The Relational Model (diagram) was also created with Lucidchart.
This was testing 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)
);
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', '1975-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;