Database

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.