这是用户在 2025-4-11 17:54 为 https://app.immersivetranslate.com/word/ 保存的双语快照页面,由 沉浸式翻译 提供双语支持。了解如何保存?

BIT103

Introduction to Database Systems
数据库系统简介

Semester 1, 2025
2025 年第一学期

Assignment No
作业编号

: 2/SQL
2/SQL

Release Date
发布日期

: 21 Mar 2025
2025 年 3 月 21 日

Due Date
到期

: 4 Apr 2025 by 4.00pm
2025 年 4 月 4 日下午 4 点前

Value
价值

: 30% of final total
最终总额30 %

Assessment Type
评估类型

: Individual Assignment
个人作业

Course Learning Outcomes

This assignment addresses the following learning objectives for this subject:
本次作业针对该主题的以下学习目标:

CLO2: Use Database tools for data administration and manipulation.
CLO2:使用数据库工具进行数据管理和处理。

CLO3: Create conceptual and logical database models for an information system by
CLO3:通过以下方式为信息系统创建概念和逻辑数据库模型:

working in a group.
以小组形式工作。

Submission Instructions
提交说明

Submission
提交

Submission to LMS by 4:00 pm
下午 4:00提交LMS

No resubmission allowed. It’s a SINGLE submission.
不允许重新提交 这是一次单一提交。

Late Submission
逾期提交

Please fill out the Late Submission Form to be considered for extension.
填写逾期 提交 正在考虑扩展表格

Penalty of 5 marks per working day will be imposed if:
如有下列情况 每天被扣 5

late submission form is not included;
包括延迟提交的表格

reason for extension is not given;
没有说明延期理由

extension is not granted.
不予延期

Cover Sheet
封面

Include the Assignment Cover Sheet
包括作业 覆盖 床单

Academic Integrity
学术诚信

You are expected to adhere to the Academic Integrity Policy. All referencing and citation should use APA Style (7th Edition preferred).
您必须遵守学术诚信政策 。所有参考引用都应使用 APA 格式 最好是7)。

You do not need to submit the similarity report.
无需提交相似报告

Turnitin similarity reports will be generated by the lecturer and penalties imposed for similarity exceeding 15%.

You may be subject to additional penalties according to the Academic Integrity Policy.

Use of AI generative tools

Utilizing AI generative tools should be seen to augment creativity, problem-solving, and productivity while maintaining the integrity of the subject matter and upholding academic and professional standards.

Students must demonstrate their own knowledge, skills, and understanding of the subject matter in their work.

Where an assignment requires ChatGPT to be cited, you must reference all the content from Generative AI tools that you include. Failure to reference externally sourced, non-original work can result in Academic misconduct.

Students are to apply APA Style for any AI content generated that is utilized and integrated into the final work. This acknowledgment is to be added to the footnote of the respective pages.

To cite AI-generated work that you did not edit or revise:

Name of AI Tool. (Year, Month Day you generated the content). Exact text of question or prompt you entered [AI-generated text/image/video, etc.]. Name of Company/Developer if different than name of AI tool. URL.

Example: ChatGPT. (2023, June 3). “Steps of creating a running website using XAMPP?” [AIgenerated text]. OpenAI. https://chat.openai.com.

To cite AI-generated work that you edited or revised:

Name of AI Tool & Your Last Name, First Initial. (Year, Month Day you generated the content). Exact text of question or prompt you entered [AI-generated text/image/video, etc.]. Name of Company/Developer if different than name of AI tool. URL.

Example: ChatGPT & Chong, L.Y. (2023, July 19). How to setup cloud based CI/CD for a website deployment? [AI-generated text]. OpenAI. https://chat.openai.com/c/9bb6771b-209b-4c8c-ac79-6a8a9f39604a

General Format for In-Text Citations

Examples: To make fluffy basmati rice, “rinse the rice twice in cold water” (ChatGPT, 2023)

Cloud-based CI/CD requires the following steps to be applied (ChatGPT & Chong, L.Y., 2023)

Any violation of this policy will result in appropriate academic sanctions, which may include penalizing your grades, failing the course, and other disciplinary actions.
任何违反此政策的行为都将导致适当的学术制裁,其中可能包括扣分、课程不及格和其他纪律处分。

Faculty of Computing and Digital Technology Policy on Generative AI
计算机与数字技术学院生成人工智能政策

ASSIGNMENT – SQL
作业 ——SQL

Assignment Evaluation:
作业评估:

These criteria will be used to evaluate your assignment submission:
这些标准将用于评估您的作业提交:

The assignment is well presented.
任务呈现得很好。

Include a project summary at the beginning of the assignment (maximum 250
在作业开始时附上项目摘要 (最多 250

words).
字)。

The project summary includes the introduction of the project, problem statement, objective, and outcome of the project.
项目摘要包括项目介绍、问题陈述 、目标和项目成果。

All tasks are sufficiently and comprehensively answered.
所有问题都得到了充分和全面的回答。

Conclusion section with relevance to the relational databases
与关系数据库相关的结论部分

Assumptions / business rules identified.
已确定的假设/业务规则。

Submission Guide:
投稿指南:

Only one submission is required.
只需提交一次。

Report with all the required tasks completed in order.
按顺序报告所有必需任务的完成情况。

Submit the Report to this LMS link:
将报告提交至此 LMS 链接:

The submission link will be provided in the HLMS and informed to students via HLMS and MS Teams.
提交链接将在 HLMS 中提供,并通过 HLMS 和 MS Teams 告知学生。

File name format: BIT103_Assign_name_Report
文件名格式: BIT103_Assign_name_Report

Must include student ID and Name in assignment cover page.
作业封面上必须包含学生 ID 号和姓名。

Report Format Requirements:
报告格式要求:

A set of professionally compiled documents with Cover Page, Table of Contents, answer of all tasks, Marking Scheme and List of References.
一套专业编写的文档,包含封面、目录、所有任务的答案、评分方案和参考文献列表。

Format writing: All materials must be produced in a written format using Times New Roman, size 12, 1.5 lines spacing. Paragraph alignment: Justify. Except for SQL queries, PL/SQL statements must be written using Courier New, size 10, 1.0 (single) spacing.
格式书写:所有材料必须以书面格式书写,使用 Times New Roman,12 号,1.5 倍行距。段落对齐:两端对齐。除 SQL 查询外,PL/SQL 语句必须使用 Courier New,10 号,1.0(单倍)行距书写。

Page setup: margin - 2.5cm for top, right, and bottom margin, 3cm for left margin. Paper size: A4 (210 x 297 mm).
页面设置:边距 - 顶部、右侧和底部边距为 2.5 厘米,左侧边距为 3 厘米。纸张尺寸:A4(210 x 297 毫米)。

There should be a caption at the bottom of each picture/graphic/diagram/chart, and a caption above each table. Besides, additional explanations might be required.
每幅图片/图形/图表/图解的底部应有说明,每张表格的上方应有说明。此外,可能还需要额外的说明。

The penalty imposed for submission without a cover page, feedback page or marking scheme page is five marks.
如果提交的论文没有封面、反馈页或评分方案页,则会被扣分 5 分。

All sources must be declared. Failure to state the source of any part of your report will be penalized. According to the University’s regulations. You are to use APA 7 referencing in body text, and to use appropriate heading and numbering system in the body.
必须声明所有来源。未说明报告任何部分的来源将受到处罚。 根据大学的规定。您必须在正文中使用 APA 7 引用, 并在正文中使用适当的标题和编号系统

Please refer to the following CUSTOMER table and answer to the task 1 and 2 below.
请参阅下面的客户表并回答下面的任务 1 和 2

Table: CUSTOMER
表格:客户

CUST_NUM

CUST_NAME

STREET

CITY

STATE

ZIP

BALANCE

608

Johnson's Department Store
约翰逊百货

372 Oxford

Sheldon

FL

33553

2106

842

All Season
所有季节

28 Lakeview
28 湖景

Grove
树林

FL

33321

8221

148

Al's Appliance and Sport
艾尔电器及运动用品

2837 Greenway
2837 绿道

Fillmore
菲尔莫尔

FL

33336

6550

408

The Everything Shop
一切商品商店

1828 Raven
1828 渡鸦

Crystal
水晶

FL

33503

5285.25

462

Bargains Galore
大量特价商品

3829 Central
3829 中央

Grove
树林

FL

33321

3412

524

Kline's
克莱恩

838 Ridgeland
838 里奇兰

Fillmore
菲尔莫尔

FL

33336

12762

687

Lee's Sport and Appliance
李氏体育及用具

282 Evergreen
282 常青树

Altonville
奥尔顿维尔

FL

32543

2851

725

Deerfield's Four Seasons
迪尔菲尔德的四季

282 Columbia
282 哥伦比亚

Sheldon
谢尔顿

FL

33553

248

Based on the above CUSTOMER table, construct a SQL statement to show the CUST_NUM, ZIP, and BALANCE for all customers and sort the output by CUST_NUM in ascending order and then BALANCE by descending order. (CLO2, 6 Marks)
根据上述 CUSTOMER 表, 构建一个 SQL 语句以显示所有客户的 CUST _ NUM ZIPBALANCE ,并按 CUST _ NUM 升序对输出进行排序,然后按 BALANCE 降序对输出进行排序 (CLO 2,6

Based on the above CUSTOMER table, construct the SQL statement to show the CUST_NUM, CUST_NAME, and ZIP for all customers in the city of ‘Grove’. (CLO2, 6 Marks)
根据上述 CUSTOMER 表, 构建 SQL 语句以显示 'Grove' 城市中所有客户的 CUST _ NUM、CUST _ NAME 和 ZIP。 CLO 2,6

Please refer to the following PRODUCT table and answer to the task 3, 4 and 5 below.
请参阅下面的 PRODUCT 表并回答下面的任务 3、4 和 5。

Table: PRODUCT
表: 产品

Based on the above PRODUCT table, what is the output of the following SQL statement? Produce the output in a table format. (CLO2, 6 Marks)
根据上面的 PRODUCT 表,以下 SQL 语句的输出是什么?以表格形式生成输出。 CLO 2,6

SELECT ProductDescription, ProductFinish, ProductStandardPrice
选择产品描述 产品完成 产品标准价格

FROM Product
来自产品

WHERE (ProductDescription LIKE ‘%Desk’
其中( 产品描述 LIKE '%Desk'

OR ProductDescription LIKE ‘%Table’)
OR 产品描述 LIKE '%Table')

AND ProductStandardPrice > 300;
AND 产品标准价格 > 300;

Based on the above PRODUCT table, produce a SQL statement to list all PRODUCTFINISH that has the ‘Ash’ at the end. (CLO2, 6 Marks)
根据上面的 PRODUCT 表,生成一个 SQL 语句来列出所有以“Ash”结尾的 PRODUCTFINISH。 CLO 2,6

Based on the above PRODUCT table, produce a SQL statement to list PRODUCTDESCRIPTION and PRODUCTFINISH that has the ‘Natural Ash’ or ‘Cherry’ as PRODUCTFINISH. You are required to use IN condition. (CLO2, 4 Marks)
根据上述 PRODUCT 表,生成一个 SQL 语句以列出 PRODUCTDESCRIPTION 和 PRODUCTFINISH,其中 PRODUCTFINISH 包含“天然白蜡木”或“樱桃木”。您需要使用 IN 条件。 (CLO 2,4 分)

Refer to the following table and complete task 6 and 7.
参考下表完成任务6和7。

6.Explain the relationships between tables shown above. (Hint, use primary key and foreign key) (CLO2, 2 marks)
6. 解释上面显示的表格之间的关系。(提示,使用主键和外键)(CLO2,2 分)

7.Produce SQL statements for the four (4) tables shown in the Figure above using one of database administration and manipulation tools such as Oracle APEX\MySQL\etc. These SQL statements are created using the CREATE TABLE statements of the SQL. Define Primary key constraint at the table level. All constraints must be logical. Note that the CustomerID in the ORDER table is an optional attribute and all other keys (Primary keys and foreign keys) are mandatory. Keys are underlined. Attribute (variable) length and data type can be decided but must be logical. Attach the snapshots of these SQL queries and the outcome of this task by inserting some records of data into these tables.
7. 使用数据库管理和操作工具(例如 Oracle APEX\MySQL\etc)之一为上图所示的四 (4) 个表生成 SQL 语句 。这些 SQL 语句是使用 SQL 的 CREATE TABLE 语句创建的。 在表级别定义主键约束。 所有约束都必须合乎逻辑。 请注意, ORDER 表中的 CustomerID 是可选属性,所有其他键(主键和键)都是必需的。键带有下划线 。属性(变量)长度和数据类型可以决定 ,但必须合乎逻辑 通过在这些表中插入一些数据记录来附加这些 SQL 查询的快照和此任务的结果

(CLO2, 20 marks)
(CLO2,20 分)

END OF ASSIGNMENT PAPER
作业结束纸