Name Student Number
Assignment Title Assignment -1
Course Tutor/Lecturer Dr. Obay | Name | | Student Number |
| :--- | :--- | :--- |
| Assignment Title | Assignment -1 | |
| Course Tutor/Lecturer | Dr. Obay | |
Declaration 声明
I/we* certify that this assignment is entirely my/our* own work, except where I/we* have given fully documented references to the work of others, and that the material contained in this assignment has not previously been submitted for assessment in any other formal course of study. 本人/我们* 保证本作业完全是本人/我们* 自己的作品，除非本人/我们* 已对他人的作品提供完整记录的参考资料，并且本作业中包含的材料之前未提交任何其他正式学习课程进行评估。
Delete where applicable (depends on whether individual or group assignment). 在适用的情况下删除（取决于是个人分配还是小组分配）。
Demonstrate the concept of SQL for data engineering. 演示 SQL 的数据工程概念。
Learning outcomes 学习成果
1.
1.
This is an individual assignment carrying 15% of the
total marks available for the module.
assessed by this
This is an individual assignment carrying 15% of the
total marks available for the module.
assessed by this| This is an individual assignment carrying 15% of the |
| :--- |
| total marks available for the module. |
| assessed by this |
2. The output of this assignment is in terms of a report
1.This is an individual assignment carrying 15% of the,total marks available for the module.,assessed by this
2. The output of this assignment is in terms of a report
written within the range of between 1500 to 1800
words.| 1.This is an individual assignment carrying 15% of the <br> total marks available for the module. <br> assessed by this |
| :--- |
| 2. The output of this assignment is in terms of a report |
| written within the range of between 1500 to 1800 |
| words. |
Instructions 指示
您需要提交电子版报告（通过 myTIMeS）。请确保封面上写有姓名和 ID。 O 标志和禁止参加期末考试可能会对未提交任何作业的人实施。 温馨提示：
You are required to submit your report in softcopy
(through myTIMeS). Kindly ensure name and ID is
written on the cover sheet.
O mark and a barring from sitting final examination
may be implemented for those who does not submit
any assignments.
GENTLE REMINDER:
You are required to submit your report in softcopy
(through myTIMeS). Kindly ensure name and ID is
written on the cover sheet.
O mark and a barring from sitting final examination
may be implemented for those who does not submit
any assignments.
GENTLE REMINDER:| You are required to submit your report in softcopy |
| :--- |
| (through myTIMeS). Kindly ensure name and ID is |
| written on the cover sheet. |
| O mark and a barring from sitting final examination |
| may be implemented for those who does not submit |
| any assignments. |
| GENTLE REMINDER: |
- Plagiarism is a serious offence and plagiarized work - 剽窃是严重的违法行为和剽窃作品
will result in an F grade. 将导致 F 级。
- Similarity should not be more than 20% - 相似度不应超过 20%
- Failure to submit the report by the deadline shall - 未能在截止日期前提交报告，应
be penalized with zero marks. 被罚 0 分。
Subject Code ITS70204
Subject Name Big Data Engineering
Subject Leader Dr. Obay
Weightage Individual Assignment
Assignment 05^("th ") MAR 2025
Handout date 12^("th ") MAR 2025
Submission date Demonstrate the concept of SQL for data engineering.
Learning outcomes "1.This is an individual assignment carrying 15% of the,total marks available for the module.,assessed by this
2. The output of this assignment is in terms of a report
written within the range of between 1500 to 1800
words."
Instructions "You are required to submit your report in softcopy
(through myTIMeS). Kindly ensure name and ID is
written on the cover sheet.
O mark and a barring from sitting final examination
may be implemented for those who does not submit
any assignments.
GENTLE REMINDER:"
- Plagiarism is a serious offence and plagiarized work
will result in an F grade.
- Similarity should not be more than 20%
- Failure to submit the report by the deadline shall
be penalized with zero marks. | Subject Code | ITS70204 |
| :--- | :--- |
| Subject Name | Big Data Engineering |
| Subject Leader | Dr. Obay |
| Weightage | Individual Assignment |
| Assignment | $05^{\text {th }}$ MAR 2025 |
| Handout date | $12^{\text {th }}$ MAR 2025 |
| Submission date | Demonstrate the concept of SQL for data engineering. |
| Learning outcomes | 1.This is an individual assignment carrying 15% of the <br> total marks available for the module. <br> assessed by this <br> 2. The output of this assignment is in terms of a report <br> written within the range of between 1500 to 1800 <br> words. |
| Instructions | You are required to submit your report in softcopy <br> (through myTIMeS). Kindly ensure name and ID is <br> written on the cover sheet. <br> O mark and a barring from sitting final examination <br> may be implemented for those who does not submit <br> any assignments. <br> GENTLE REMINDER: |
| - Plagiarism is a serious offence and plagiarized work | |
| will result in an F grade. | |
| - Similarity should not be more than 20% | |
| - Failure to submit the report by the deadline shall | |
| be penalized with zero marks. | |
Assignment one involves honing your SQL skills through practical exercises. You’ll gain hands-on experience in working with string patterns, sorting result sets, and grouping data. Additionally, you’ll discover how to execute SQL scripts to create multiple tables simultaneously and import data into these tables from .csv files. 作业一涉及通过实践练习磨练您的 SQL 技能。您将获得使用字符串模式、对结果集进行排序和对数据进行分组的实践经验。此外，您还将了解如何执行 SQL 脚本以同时创建多个表，并将数据从 .csv 文件导入到这些表中。
HR Database 人力资源数据库
This HR database schema consists of 5 tables called as follows: 此 HR 数据库架构由 5 个表组成，如下所示：
EMPLOYEES 员工
JOB_HISTORY
JOBS 工作
DEPARTMENTS 部门
LOCATIONS. 地点。
Note: All mentioned database are attached in Mytimes, please download them to start working on this assignment. 注意：所有提到的数据库都附在 Mytimes 中，请下载它们以开始处理此作业。
To complete this assignment you need to use MySQL workbench or any related software. You are required to perform the following tasks in the Part One and Part Two to fulfil the requirements in this question. You must provide a screenshot and full explanation for every step taken to complete the following questions. 要完成此作业，您需要使用 MySQL Workbench 或任何相关软件。您需要执行第一部分和第二部分中的以下任务才能满足本问题中的要求。您必须为完成以下问题所采取的每个步骤提供屏幕截图和完整说明。
Part One (4 Marks) 第一部分 （4 分）
Task 1: Due to the structures of the above mentioned tables will be some issues to upload the data, what can be done to resolve those issues? Explain in detail. (2 Marks) 任务 1：由于上述表格的结构会有一些问题上传数据，可以做些什么来解决这些问题？详细解释。（2 标记）
Task 2: Propose a new structure for each of those mentioned five tables to avoid any issue of uploading data in the future, and explain why? (2 Marks) 任务 2：为上述五个表中的每一个提出一个新结构，以避免将来上传数据的任何问题，并解释为什么？（2 标记）
Part Two (11 Marks/One Mark each) 第二部分 （11 分/各 1 分）
Task 1: Retrieve all employees whose address is in Elgin,IL and Gary IL 任务 1：检索地址位于 Elgin，IL 和 Gary IL 的所有员工
Task 2: Retrieve all employees who were born during the 1980’s. 任务 2：检索 1980 年代出生的所有员工。
Task 3: Retrieve all employees in department 7 whose salary is between 60000 and 80000. 任务 3：检索部门 7 中薪水在 60000 到 80000 之间的所有员工。