CS-315 – Assignment III
(10 points)
After installing MySQL (using the steps posted on D2L under Install_MySQL.pdf document), use MySQL Workbench (search for it from the start menu of Windows OS or application menu of Mac OS).
This is the relational DBMS that you will use for this assignment.
Create a new database (named MyUniversity) then use it for the tasks listed in the next steps, using the
following commands:
Command Describtion
SHOW DATABASES; List all the available/created databases
CREATE DATABASE databasename; Create a new database named databasename
DROP DATABASE databasename; Delete a database named databasename
USE databasename; Use the database (databasename) to execute more queries
The creation (using the CREATE command) should be done once, and the USE command should be triggered every time you re-open MySQL workbench. You cannot perform any query on your database before the USE command.
Task Description: consider the following relations for a university:
Student (sid: integer, sname: string, GPA: real, dateOfBirth: date)
Department (did: integer, dname: string, capacity: integer)
Course (cid: integer, cname: string, credit: integer)
Enroll (studentID: integer, courseID: integer, departmentID: integer, enrollmentDate: date)
studentID, courseID, and departmentID in Enroll are foreign keys referencing the primary keys of the
student, course, and department relations, respectively.
Task1 (2 points): Using SQL, create these relations under the MyUniversity database you created. Place
the constraints (e.g., default values) you find appropriate.
Task2 (4 points): Using SQL, insert (random but meaningful) data records in the above tables.
• Create at least 3 different courses, including OS, DB, Marketing, and Networks courses.
• Create 2 departments, Computer Science department and Business department.
• Add at least 8 students (divided on the two departments – 4 for each department), and at least two
different enrollments for each student (each student is registered for two different courses). The
enrollments should be between January2020 and December2020.
Task3 (4 points – 1 point/query): Using SQL, develop and execute the following queries:
1- For each student with GPA between 2.5 (included) and 3.5 (included), display the student’s name
and id.
2- Display unique enrollments (by student’s name and Course’s name) of June2020 for students from
the Computer Science department.
3- Display students, by name and date of birth, sorted by GPA in descending order.
4- Display the students, by name and GPA, enrolled in the OS course sorted by GPA in descending
order then students’ names in Ascending order.
Submission:
1. For task 1, take clear screenshots to the queries you developed to create the tables.
2. For task 2, after inserting the records, take a clear screenshot for the content of each table – as follows:
SELECT *
FROM theTableName;
No need to take screenshot to the developed “insert” queries.
3. For task 3, for each query, take a clear screenshot for both the query you developed as well as the
output.
4. This is an individual assignment — Cheating/plagiarism will be checked and will receive zero.
5. Submit ONE PDF file directly to the folder titled Assignment 3 under the D2L Assignments tab (other
formats will not be accepted). Don’t submit .zip, .ppt, .pptx, .doc, .docx file.
6. The assignment is due 10:00pm – October 28th. You can submit your assignment, within 24 hours after
this due date, to be graded out of 50% of the assignment’s grade. After this grace period your late
submission will not be accepted.