Note: This is an individual assignment. While it is expected that students will discuss their ideas with one another, students need to be aware of their responsibilities in ensuring that they do not deliberately or inadvertently plagiarize the work of others.
Assignment 2 – Database Implementation and Query Formulation
Assessment weight: 20%
This assignment has been designed to give students experience using Structured Query Language (SQL) and other database management systems (DBMS) facilities to create/alter a relational database and to query the DBMS. This assignment addresses the following learning objectives for this subject:
• Develop and implement a database model using the E-R model and facilities provided by a DBMS
• Formulate queries using a database query language
This assignment consists of two main tasks:
1. Creating the database (by following three subtasks):
o Create a relational database for a given conceptual model (ERD) using MySQL
Workbench o Create a physical database model on MySQL Workbench by applying the forward engineer process
o Import raw data from the external file to a table using the MySQL Workbench facility or using SQL queries
2. Writing SQL queries for given problems.
Further details about each task are presented in the following pages.
• A MySQL Workbench file containing the ERD you created (.mwb)
• A database dump file built and exported on MySQL Workbench (.sql)
• A WORD or text file containing all SQL query codes and result tables (.doc, .docx, or .txt)
Task 1: Creating the database [42 marks]
1. Use MySQL Workbench to create an ERD to present a relational database model supplied as below. This is a sample ERD for a simplified University Enrolment Database.
• A faculty means an academic member who usually runs (teaches) courses (subjects).
• A course means a subject run by University
• A course is offered by the schedule set by University
• A student make a registration for each term by enrolling to a number of courses offered.
• An attribute in bold type means it is set as ‘Not Null’.
PKs should be correctly specified. All necessary attributes should be specified by setting appropriate data types and appropriate field lengths. [Save the completed model as a uniEnrolDB.mwb file]
The final ERD you create should correctly correspond with the ERD provided above in terms of structure, though the way to present components (PK or FK) in the ERD created using MySQL Workbench is not exactly same as what is presented in the ERD provided above
2. Create a physical database model using the forward engineering process on MySQL Workbench.
3. Insert the data (provided for this assignment) into the database. You may choose to use either the SQL INSERT syntax or the import facility provided by MySQL Workbench. (Caution: The name or order of attributes in the raw data files provided may not exactly match with those shown in the ERD. You will have to be careful to check it when you import data from Excel files to each table of your database)
4. Finally, dump the database into one integrated file on MySQL Workbench. [Save the file as uniEnrolDB.sql file]
Task 2: Creating queries [40 marks]
Using the database you constructed in Task 1, create the following SQL queries in MySQL Workbench.
For each question, the correct result expected is provided to help you get an idea of what the table head and data format of your query result should look like.
Each question shows the correct result expected by the correct query. Please note that this result will be generated as it is, only if your database is developed correctly as specified in this assignment. If your database is not built up fully or incorrectly, the result may be different even if your SQL code is correct and ideal.
When your marker does marking on your SQL submission, the marker will test your code in their own correct database and also will assess your SQL codes by checking if the code has correct logic and syntax.
[Paste your SQL query into a Word document to submit. The final Word file should contain all SQL queries you made for this task]
- Queries should be written so that they would work with all reasonable sets of test data, not just that which has been supplied as a sample data.
- Marks may be deducted if your SQL is excessively (or unnecessarily) complicated.
- Full marks will be awarded where the solution provided is correct in all respects.
- Partial marks may be allocated where students are deemed to have provided a significant effort toward a correct result, but the solution contains some error.
- No marks are awarded where either no solution is provided, or the solution provided is deemed to be mostly incorrect.
1. Retrieve the name, city, and grade point average (GPA) of students with a high GPA (greater than or equal to 3.2). Sort the output by the student’s last name. Save this query as Q-1.
StdFirstName StdLastName StdCity StdGPA
CRISTOPHER COLAN SEATTLE 4.00
MARIAH DODGE SEATTLE 3.60
TESS DODGE REDMOND 3.30
JOE ESTRADA SEATTLE 3.20
CANDY KENDALL TACOMA 3.50
WILLIAM PILGRIM BOTHELL 3.80
2. List the name, city, and increased salary (increase the salary by 20 percent) of faculty hired after 1996. Save this query as Q-2.
Note: HireDate must display the faculty’s hire date in the form shown.
FirstName LastName City InflatedSalary HireDate
NICKI MACON BELLEVUE 78000 4/11/1997
CRISTOPHER COLAN SEATTLE 48000 3/1/1999
JULIA MILLS SEATTLE 90000 3/15/2000
3. List the offering number and course number of Year 2006 offerings which had no instructor (faculty) assigned. Save this query as Q-3.
4. List the offer number, course number, offer term, offer year and faculty Social Security number (SSN) for offerings scheduled in fall 2005 or spring 2006. Save this query as Q-4.
(Hint: Use lpad fundtion to fill ‘0’ at the start of FacSSN value if necessary) Resource: https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_lpad
OfferNo CourseNo OffTerm OffYear FacSSN
1234 IS320 FALL 2005 098765432
3333 IS320 SPRING 2006 098765432
4321 IS320 FALL 2005 098765432
5679 IS480 SPRING 2006 876543210
7777 FIN480 SPRING 2006 765432109
9876 IS460 SPRING 2006 654321098
5. List the offering number, course number, and days of offerings containing the words “finance” or “database” in the course description (regardless of uppercase or lowercase letters) and taught in winter 2006. Save this query as Q-5.
OfferNo CourseNo OffDays
5555 FIN300 MW
5678 IS480 MW
6. List the offer number, course number, and full name of the instructor (faculty) of all FINANCE courses (the course number’s prefix is ‘FIN’) offered in winter 2006 taught by professor. Note: professor’s rank is “PROF” in the database. Save this query as Q-6.
OfferNo CourseNo Instructor Name
5555 FIN300 NICKI MACON
7. Summarize the number of offerings by course. Sort the output by courseNo in descending order. Save this query as Q-7.
8. Summarize the average GPA of upper-division (junior or senior) students by major. Only list the majors with average GPA greater than 3.3. Save this query as Q-8.
9. Summarize the minimum and maximum GPA of students by major and class. Save this query as Q-9.
Major Class MinGPA MaxGPA
IS FR 3.0 3.0
FIN JR 2.5 2.7
ACCT JR 3.5 3.5
IS SR 2.2 4.0
FIN SR 3.2 3.2
IS JR 3.6 3.6
ACCT SO 3.3 3.3
IS SO 3.8 3.8
10. Summarize the number of offerings run in 2006 by offering location. Save this query as Q-10.
11. List a faculty Leonard Fibon’s teaching schedule in spring 2006. List the offering number, course number, course description, days, location, and time. Save this query as Q-11.
OfferNo CourseNo CrsDesc OffDays OffLocation OffTime
9876 IS460 SYSTEMS ANALYSIS TTH BLM307 1:30 PM
12. List a student Candy Kendall’s course schedule in 2005. List the offering number, course number, days, location, time, and instructor’s last name. Save this query as Q-12.
OfferNo CourseNo OffDays OffLocation OffTime FacLastName
1234 IS320 MW BLM302 10:30 AM VINCE
13. List faculty members who have a lower salary than their supervisor. List the faculty member’s name, the faculty member’s salary and the supervisor’s name, the supervisor’s salary. Save this query as Q-13.
Subr.FacFirstName Subr.FacLastName Subr.FacSalary Supr.FacFirstName Supr.FacLastName Supr.FacSalary
LEONARD VINCE 35000 LEONARD FIBON 70000
LEONARD FIBON 70000 VICTORIA EMMANUEL 120000
CRISTOPHER COLAN 40000 LEONARD FIBON 70000
14. List the names of faculty members and the course number for which the faculty member teaches the same course number as his or her supervisor in the same year. Save this query as Q-14.
Faculty Name Year CourseNo
LEONARD VINCE 2006 IS320
LEONARD FIBON 2006 IS320
15. List the course number, the offering number, and the average GPA of students enrolled. Only include courses offered in spring term in which the average GPA of enrolled students is greater than 3.0. Save this query as Q-15.
CourseNo OfferNo AvgGPA
IS480 5679 3.07
IS460 9876 3.37
16. For offerings beginning with IS in the associated course number, retrieve the offer number, the course number, the faculty number, and the faculty name. Include an offering in the result even if the faculty is not assigned. Save this query as Q-16.
(Hint: Use lpad function to fill ‘0’ at the start of FacSSN value if necessary)
OfferNo CourseNo faculty.FacSSN FacFirstName FacLastName 1111 IS320 2222 IS460 1234 IS320 098765432 LEONARD VINCE 3333 IS320 098765432 LEONARD VINCE 4321 IS320 098765432 LEONARD VINCE 4444 IS320 543210987 VICTORIA EMMANUEL 8888 IS320 654321098 LEONARD FIBON 9876 IS460 654321098 LEONARD FIBON 5679 IS480 876543210 CRISTOPHER COLAN 5678 IS480 987654321 JULIA MILLS
17. Retrieve the Social Security Number (SSN), name (first and last), rank, and salary of faculty who are also students. Save this query as Q-17.
SSN FirstName LastName Rank Salary
876543210 CRISTOPHER COLAN ASST 40000
18. List the Social Security Number (SSN), name and city of faculty who only teach in winter term 2006. Save this query as Q-18.
FacSSN Name FacCity
543210987 VICTORIA EMMANUEL BOTHELL
987654321 JULIA MILLS SEATTLE
19. List the course number, the course description, the number of offerings, and the average enrollment across offerings. Save this query as Q-19.
CourseNo CrsDesc NumOfferings AvgEnroll
IS320 FUNDAMENTALS OF BUSINESS PROGRAMMING 3 4
IS460 SYSTEMS ANALYSIS 1 6
IS480 FUNDAMENTALS OF DATABASE MANAGEMENT 2 5
20. List the name and rank of faculty who teach at least one offering of all of the 2006 information systems (IS) courses and his/her supervisor’s name. Save this query as Q-
FacFirstName FacLastName FacRank Supervisor
LEONARD VINCE ASST LEONARD FIBON
JULIA MILLS ASSC NICKI MACON
CRISTOPHER COLAN ASST LEONARD FIBON
LEONARD FIBON ASSC VICTORIA EMMANUEL
Assignment #2 Database implementation and query formulation: Marking criteria
Requirement Criteria Marks
Creating the database
Create/Save ERD correctly as required in Workbench (.mwb file)
All tables required are correctly created/presented (in ERD), with:
• correct attributes (1 marks for each table)
• correct PKs (1 mark for each table)
• Relationships are constructed correctly (1 mark for each relationship) ___/18
Develop physical database having all tables required are correctly created, with:
• correct attributes (1 mark for each table)
• correct PKs (1 mark for each table)
• data added correctly (1 mark for each table)
• correct relationships made between tables (1 mark for each relationship)
Creating queries Queries produce the correct results with correct logic ___/40
(2 marks for each query)
Total Marks ___/82