SQL Fundamentals

CMPG321 Project 2020 – (Groups of 2 to 4 students) [135]
All parts of the project should be completed by the members in your group and submitted on
eFundi by 01 December 2020 as a report with practical SQL code and output examples.
Each group must consist of at least 2 students and not more than 4 students. You are welcome
to use the chat on the CMPG321 eFundi site to get in touch with other students to join or form
a group for the project.
Marks will also be given for the structure, completeness and format of the report document.
For the document, please remember include basic, but important aspects, such as a neatly
formatted front page, index, a bibliography for the sources referenced, etc.
Part 1 – SQL Fundamentals 2 – Lessons 1,2,3 [60]
1.1) SQL Fundamentals 2 – Lesson 1 (Users) [20]
Write a summary report (2-4 pages) on the users of a database. Also show examples of the
SQL instructions used to manage users, privileges and roles.
1.2) SQL Fundamentals 2 – Lesson 2 (Changes to tables) [20]
Write a summary report (2-4 pages) to describe the below changes that can be made to
tables with examples of possible scenarios, the SQL instructions used and the results
thereof.
• Add-, alter-, and remove columns
• Change the name of columns
• Add-, and remove constraints
1.3) SQL Fundamentals 2 – Lesson 3 (Objects and data dictionary views) [20]
Write a summary report (2-4 pages) on the use of data dictionary views in your database.
Also show examples of the SQL instructions used to create and use them.
Part 2 – DS Chapter 13 & SQL Fundamentals 2 – Lessons 4,5 [59]
Running case – Practical SQL examples
Consider the relational diagram of a purchasing system from an operational database below
and answer the following questions.
DEPARTMENT ORDER VENDOR
ORDER_LINE ITEM
PK DEPT_CODE
DEPT_EMAIL
PK ORDER_NUM
FK DEPT_CODE
Places
PK VEND_CODE
VEND_CONTACT
Receives
ORDER_DATE
DEPT_NAME
DEPT_CONTACT
ORDER_AMOUNT
FK VEND_CODE
VEND_EMAIL
VEND_NAME
PK FK ORDER_NUM
FK ITEM_CODE
LINE_PRICE
LINE_QUANT
PK ORDER_LINE_NUM
PK ITEM_CODE
ITEM_NAME
ITEM_QOH
FK VEND_CODE
ITEM_PRICE
Contains
Is placed in
Supplies
DS Chapter 13 (Business Intelligence and Data Warehouses)
2.1) Star Schema [15]
You must create a database for a decision support system (DSS). During interviews with users
the average amount ($) of orders (ORDER_AMOUNT) and the number of orders per year
(year of ORDER_DATE), department (DEPARTMENT) and vendor (VENDOR) were identified
as information requirements. Draw a detailed star schema for these information requirements,
use the correct naming convention and indicate all primary- and foreign keys.
SQL Fundamentals 2 – Lesson 4,5 (Large Datasets, and Time Zones)
2.2) Initial Loading [10]
Write a transaction with SQL statements to populate the fact table of your star schema with
ALL data from the purchasing system operational database. Assume the fact table
ORDER_FACT is already created.
2.3) Daily Updates [22]
After the data was initially loaded from the operational database into ORDER_FACT, the detail
of new orders should be updated daily from the operational database into the fact table,
ORDER_FACT. Write a SQL statement to do that.
2.4) Conditional Insert [12]
This question is unrelated to the running case in the previous questions of Part 2 and can be
answered in isolation. Write a SQL statement to do the following:
Retrieve employee ID (EMP_ID), hire date (EMP_HIRE_DATE), salary (EMP_SALARY) and
manager ID (EMP_MANAGER_ID) of all employees on a table named EMPLOYEE, with
employee ID less than 300. For every row retrieved, do the following:
• If the salary is more than R20,000 the employee ID (EMP_ID) and salary (EMP_SALARY)
must be added to the SPECIAL_SAL table.
• If the salary is less or equal to R20,000, the following must be done:
o Add the employee ID (EMP_ID), hire date (EMP_HIRE_DATE) and salary
(EMP_SALARY) to the SAL_HISTORY table.
o Add the employee ID (EMP_ID), manager ID (EMP_MANAGER_ID) and salary
(EMP_SALARY) to the MGR_HISTORY table.
Part 3 – SQL Fundamentals 2 – Lesson 5 (Managing data in different Time Zones) [16]
3.1) Time and Date functions and format [6]
Write a SQL statement to display the surname (LAST_NAME), hire date (HIRE_DATE) and
a date that is 100 days and 10 hours after the hire date (HIRE_DATE) of all employees with
manager ID (MANAGER_ID) equal to 100 on the EMPLOYEE table. Display this data
formatted as indicated in the figure below.
3.2) Time and Date Intervals [10]
Discuss and provide examples of the difference between CURRENT_DATE,
CURRENT_TIMESTAMP, and LOCALTIMESTAMP. Then write and provide an SQL
statement to display the current system date and time in separate fields (as shown below) for
year, month, day, hour, minute and second. Also provide the output of your SQL statement.