SWEN-344: Exam 1 - Databases

You will create a new project in gitlab named as follows:

Project name: ‘swen-344-0n-exam-1’, where ‘n’ is your section number
- e.g. swen-344-01-exam-1 for section 1, swen-344-02-exam-1 for section 2, or swen-344-03-exam-1 for section 3.

  • DO NOT create this inside your DB group. The project must be in the root of your gitlab account!

  • Add your instructor as Reporter:

  • Clone the repo, to your local machine, and add the provided starter code to set up your project.

    • starter code
    • Make sure the files are unzipped into the root folder of your project.
      • When you extract the files from the starter .zip file, make sure you don’t create extra sub-directories!
      • Mac users: Beware of Mac hidden files!

  • Update your db.yml file as needed, with the right password for your local db (in the config folder)

  • Make sure the unittest runs (python -m unittest -v).

  • Update the .gitlab-ci.yml and requirements.txt files (if needed).

  • Commit and push to the repo, and make sure the CI also runs successfully!

  • Read the information below to understand more about the data-set.

  • Finish all the setup/ preliminary work done before working on the actual practicum.

Overview of data-set and project setup:

This is a data-set for tracking University employees and the salary/ hiring information.

Study the tables, and the data set

  • You will be asked to develop queries to answer business questions posed by management.
  • Read the data-set in univ_finance.sql. Read the comments in the .sql file. They will provide information on the meaning of the fields!
    • Not every single column in every table may be utilized for the exam, but you should familiarize yourself with the overall schema.
  • Use python to run unittest and output the answers to the questions to the console.
    • We are NOT looking for assertions, we are looking for the console output!
  • Write your queries in the python code/ file in the src directory e.g. in finance.py or another file you may choose to add.
  • You are responsible for setting up the correct directory structure, which is similar to what you used for the DB project.
    • i.e. a config, src, test directory. db.yml and .gitlab-credentials.yml …
      A .sql file to create and populate the data is provided (univ_finance.sql)

Other files provided:

  • src/swen344_db_utils.py: No need to modify
  • tests/test_finance.py: Add your test(s) here. A setup and sample test function are provided to help you with initial configuration
  • src/finance.py: Skeleton file with one function. Add any extra code here

You don’t need to have unique unit tests for each question in the test_finance.py file.
One unit test that calls functions to get the answers to the questions is sufficient. This will help keeping the output in the correct order.
The DB is reset once in setup. This makes it easier for you to have a clean DB each time you run unittest. Make sure you DO NOT reset the DB between the exam questions you implement.
Keep most of your logic in the src/finance.py file Proper use of SQL is key – the vast majority of the work must be in the SQL statements, not in python logic.

SWEN-344 DB Practicum

  • Make sure you have completed the initial setup instructions for the Practicum.
  • All your code must go in the repo you created.
  • Once your repo is ready, answer the questions below, using python and SQL with the provided data-set. Pay attention to the output formats specified.
  • Push your code to your repo per instructions.

Terminology

  • Faculty have different positions, and have contracts that define when their employment starts and ends.
  • End of employment is either a known date, or null which designates open ended employment i.e. they are still active employees
  • Hire date is when the were initial hired. Contract dates are different from the initial hire date
  • Salary effective date is when the current salary became effective.

NOTE

  • Read and understand the tables and fields. Not all fields may be utilized in the various questions. You need to review/ understand what is needed for which question.

Exam logistics:

  • The exam has two parts

    Part-1: Take-home

    • After completing the setup, implement the initial questions/ tests (see below)
      • Due/ tagged (tag your submission as part-1) before the in-class exam

    Part-2: In-class

    • You will be given instructions on more tests (and APIs) to implement for the remainder of the exam
      • You will receive these additional instructions at class-time

Part-1: Take home test scenarios:

  • Implement the questions/ tests as shown on this page: take home

Part-2: In class exam test scenarios:

  • This link will be provided in class on the day of the practicum