5915 Database Design – COLT2, 2020
ASSIGNMENT 1
Due date: Friday week 7 at 11.59 pm
This assignment has 100 marks which constitutes 15% of the total marks for this unit.这项作业有100分，占该单元总分的15％。
You need to submit your design documents (Part 1) as well as the answer to Normalisation question (Part 2) on a separate document for this assignment. The design documents include a conceptual Enhanced Entity Relationship Diagram together with a Data Dictionary as described in the assignment specification below. Please contact your teacher if you have any queries about this assignment.
您需要在单独的文档中提交设计文档（第1部分）以及对规范化问题的答案（第2部分），以进行此分配。设计文档包括概念性增强实体关系图以及数据字典，如以下分配规范中所述。如果您对此作业有任何疑问，请与您的老师联系。
General Information一般信息
This is an individual assignment and each student is responsible for both the submission and the outcome.这是一项个人作业，每个学生都要负责提交和成绩。
Instructions:说明：
• Late submission, unless authorised by the lecturer, WILL NOT be marked.逾期提交，除非得到讲师的授权，否则不会被标记。
• There are no restrictions on the use of word processors or similar tools for the production of submissions for this assignment.2.使用字处理器或类似工具来制作此作业的提交没有任何限制。
• Plagiarism will attract severe penalties in accordance with the guidelines as university of Canberra polices.3.按照堪培拉大学的政策，P窃将受到严厉的惩罚。
• An electronic copy of this assignment should be submitted via the Canvas site using the assignment drop box by the due date and time.4.应在截止日期和时间之前通过“作业”下拉框通过Canvas网站提交此作业的电子副本。
• You need to use Visio(or any other modelling software of your choice) to create your EER model and then save as a .jpg file to insert in your assignment..您需要使用Visio（或您选择的任何其他建模软件）创建EER模型，然后另存为.jpg文件以插入作业中。
Be sure to maintain regular back-ups for any models or material prepared with the aid of software. Loss of files will not be accepted as an excuse for non-completion of this assignment.6.对于使用软件准备的任何型号或材料，请确保定期备份。文件丢失将不作为未完成此作业的借口。
翻译此页面 在Google翻译
•
Page 1 of 4
PART 1 – Modelling (55 marks)第1部分-建模（55分）
The purpose of this assignment is to provide you with experience in analysing and designing a solution for a swim school. This assignment will help you to understand the nature and purpose of data analysis and conceptual design.这项任务的目的是为您提供分析和设计游泳学校解决方案的经验。这项作业将帮助您了解数据分析和概念设计的性质和目的。
Read the description of the library and its business operations then answer the questions. Problem Description阅读图书馆的描述及其业务运作，然后回答问题。问题描述
SwimSafe is a Canberra based college, which administers lessons at various swimming pools within Canberra and Queanbeyan. You have taken over the organisation of SwimSafe and you have inherited a computerised file-system that organises the information to run SwimSafe’s operations and administration. The file system consists of documents that are lists of facts that allow you to gather and organise basic data about class enrolment, payment and student progress.SwimSafe是一所位于堪培拉的学院，负责管理堪培拉和昆比恩（Queanbeyan）内各个游泳池的课程。您已经接管了SwimSafe的组织，并且继承了一个计算机化的文件系统，该文件系统对信息进行组织以运行SwimSafe的操作和管理。文件系统由文档组成，这些文档是事实列表，可让您收集和组织有关班级注册，付款和学生进度的基本数据。
You want to use the existing data to create a database system, which would track class enrolment, payments for the lessons and the progress of students. You would like to display financial reports to show the profitability of offering swimming lessons at particular swimming pools. As the SwimSafe grows you might desire from your database to provide more operations and functionalities, which could be achieved by modifying your database.您想使用现有数据来创建一个数据库系统，该系统将跟踪班级注册，课程付款和学生进度。您想显示财务报告，以显示在特定游泳池提供游泳课程的收益。随着SwimSafe的增长，您可能希望从数据库中提供更多的操作和功能，这可以通过修改数据库来实现。
Requirements要求
SwimSafe runs swimming classes during the three semesters in a year. As an example, the second semester of year 2019 is identified as 19b and is named as the winter semester. Classes are classified into seven separate levels from 1 to 7, and classified as Guppy, Minnow, Fish, Flying Fish, Shark, Dolphin and Lifeguard. Different classes may run on different days, and one class’s timetable may involve several days per week. SwimSafe runs classes on all days except Sundays. Each class is run for an hour. Start time for the class is helpful in identifying a class on a particular day. Each pool has two sections and an instructor is assigned to each class in that section. Senior instructors are identified by a Senior badge, the details of their badges must be stored in the database (the badge number and date). The classes have a limit of 12, 15 and 20 students. However, not all the classes are always full. The information on pools includes the code of the pool building, the name of the pool and the address of the pool. For example, the pool in the City is assigned CITY as the code and ‘Canberra Pool’ as the name
SwimSafe一年三个学期开设游泳课程。例如，2019年第二学期被标识为19b，并被命名为冬季学期。类别分为1到7个七个独立级别，分别为孔雀鱼，py鱼，鱼，飞鱼，鲨鱼，海豚和救生员。不同的课程可能在不同的日期举行，并且一个课程的时间表可能每周涉及几天。 SwimSafe每天（星期日除外）全天运行课程。每个课程运行一个小时。上课的开始时间有助于识别特定日期的课程。每个池都有两个部分，该部分中的每个班级都分配了一名教师。高级教练由高级徽章标识，他们的徽章详细信息必须存储在数据库中（徽章编号和日期）。每班最多可容纳12、15和20名学生。但是，并非所有课程都总是满的。有关池的信息包括池构建代码，池名称和池地址。例如，将城市中的游泳池分配为CITY作为代码，将“堪培拉游泳池”分配为名称
Information on students includes students’ identification, first name, second name, middle name initial, date of birth, street, postcode, phone number, amount charged, amount paid.
有关学生的信息包括学生的身份，名字，名字，中间名的缩写，出生日期，街道，邮政编码，电话号码，收费金额，已付款金额。
A payment can only be made in full and a receipt number is recorded in the database if the amount is paid in cash. Payment can also be made by cheque or credit card.如果以现金付款，则只能全额付款，并且收据编号会记录在数据库中。也可以用支票或信用卡付款。
The details of payments would include payment number, payment type, date of payment, first name, second name and the initial of the middle name of the person making payment(usually the student’s next-of-kin), their phone number, amount paid, cheque number and the students information
付款详细信息包括付款号码，付款类型，付款日期，名字，名字和付款人的中间名的缩写（通常是学生的近亲），他们的电话号码，已付款的金额，核对号码和学生信息
Page 2 of 4
Requirements要求
• Identify the main entities of the SwimSafe and provide the description of all entities. a）确定SwimSafe的主要实体，并提供所有实体的描述。
(6 Marks)
• Provide description of relationships and the multiplicity between all the entities described in (a)
（b）提供（a）中描述的所有实体之间的关系和多重性描述
(10 Marks)
• Provide the description of all attributes for all entities from part(a)
c）提供（a）部分中所有实体的所有属性的描述
(6 Marks)
• Determine candidate and primary key attributes for each strong entity.
d）确定每个强实体的候选人和主键属性。
(5 Marks)
• Using your answers (a) to (e) attempt to represent the data requirements of the
Library as a Conceptual data model (ERD and Data Dictionary). State any
assumptions necessary to support your design. (28 Marks)
（e）使用您的答案（a）至（e）尝试将图书馆的数据需求表示为概念数据模型（ERD和数据字典）。说明支持您的设计所需的任何假设。 （28分）
**For parts (a), (b) and (c) you need to use the suitable data dictionary, for more information please refer to Week 5 Lecture 01B notes to see the structure of the required data dictionary for each part.
**对于（a），（b）和（c）部分，您需要使用适当的数据字典，有关更多信息，请参阅第5周第01B讲义，以了解每个部分所需的数据字典的结构。
Page 3 of 4
PART 2 – Normalisation (45 Marks)第2部分-标准化（45分）
The following relation Lists the students’ results in a relational database.以下关系在关系数据库中列出学生的成绩。
StudnetResultsStudnet结果
StudentNo
SemeterNo
Year
SemesterName
UnitNo
UnitNmae
FinalGrade
1112
1
2019
COLT1
5915
Database Design
Fail
1112
2
2019
COLT2
4483
Software Technology 1
Pass
1112
2
2019
COLT2
5915
Database Design
Credit
1113
1
2019
COLT1
6348
Information Systems in
High
Organisations
Distinction
1113
2
2019
COLT2
9487
Academic English
Distinction
1113
3
2020
COLT3
4478
Introduction to Information
Credit
Technology
1114
1
2019
COLT1
4483
Software Technology 1
Fail
1114
1
2020
COLT1
4483
Software Technology 1
Pass
Requirements要求
• The above table is subject to update anomalies. Using the data in the table, provide examples of insertion, modification and deletion anomalies.
a）上表可能会更新异常。使用表中的数据，提供插入，修改和删除异常的示例。
(15 marks)
(b) Identify the candidate keys of the above relation and select one of them as the primary
key.确定上述关系的候选键，然后选择其中之一作为主键。 (7 marks)
• Identify the functional dependencies on the alternate keys as well as the primary key.
（c）确定对备用键和主键的功能依赖性
(5 marks)
• Using the functional dependencies identified in part (c), normalise the above relation to 3NF showing any Foreign Keys. (Include data in the normalised tables).
d）使用（c）部分中确定的功能相关性，将上述关系归一化为显示任何外键的3NF。 （将数据包括在规范化表中）。
(18 marks)
SUBMISSIONS 提交
Submit your design documents (Conceptual ERD, Data Dictionary) for Part1 and your answers for Part 2(in one compressed file) via Canvas by the due date as specified above together with a cover page containing your name and student number.
在上述指定的截止日期之前，通过Canvas提交有关第1部分的设计文档（概念性ERD，数据字典）和第2部分的答案（在一个压缩文件中）以及包含您的姓名和学生编号的封面。
Page 4 of 4