Unit:
Database Design and Development
Assignment
20 Credit Version
Spring 2020 – Winter 2020
單元：
數據庫設計與開發
分配
20學分版本
• 2020年春季– 2020年冬季
Introduction
This assignment requires you to demonstrate knowledge and skills you have acquired throughout this module by producing a database and producing a report that addresses the tasks given below. In order to complete the assignment, you will need to choose an appropriate organisation to research and base your database project on.
Choosing an appropriate organisation
Step 1: select an industry in which you are interested. For example, commercial airlines, commercial banking, retail, beauty, IT, publishing, hospitality, leisure and tourism, automotive, or construction etc.
Step 2: select one or more organisations in that industry to research. You should investigate the sort of data they hold and the types of transactions they carry out. For example the education college shown in the appendix holds data about the courses, staff, modules, laboratories and equipment. Their transactions might include allocating modules to courses, staff to modules, and assigning laboratories to courses and allocating equipment to laboratories.
Do not chose a college for your organisation as this has been used as an example.
You should reference the businesses or organisations that you investigate.
Step 3. Gathering materials
For your chosen type of organisation gather materials relating to their data and transactions. You could do this by looking on-line, through personal contacts with someone who works in the industry or simply by using a particular business’s services. Materials that you might gather include:
• Invoices
• Receipts
• Order Forms
• Customer Records
• Delivery Notes
• Complaints Forms
• Booking Forms
• Descriptions of daily tasks
• Interviews with staff members
• Company reports
介紹
這項作業要求您通過生成數據庫並生成可解決以下任務的報告來證明您在本模塊中學習的知識和技能。為了完成任務，您將需要選擇合適的組織來研究數據庫項目並以此為基礎。
選擇合適的組織
步驟1：選擇您感興趣的行業。例如，商業航空公司，商業銀行，零售，美容，IT，出版，酒店，休閒和旅遊，汽車或建築等。
步驟2：選擇該行業中的一個或多個組織進行研究。您應該調查他們持有的數據種類以及他們執行的交易類型。例如，附錄中顯示的教育學院保存有關課程，人員，模塊，實驗室和設備的數據。他們的事務可能包括將模塊分配給課程，將人員分配給模塊，將實驗室分配給課程以及將設備分配給實驗室。
不要為您的組織選擇一所大學，因為這已作為示例。
您應該參考您調查的企業或組織。
步驟3.收集材料
對於您選擇的組織類型，請收集與其數據和交易相關的材料。您可以通過在線查找，與業內人士的私人聯繫或僅通過使用特定企業的服務來進行查找。您可能收集的材料包括：
•發票
•收據
•訂購表格
•客戶記錄
•交貨單
•投訴表
•預訂表格
•日常任務說明
•採訪工作人員
•公司報告
Task 1 – Description of Business (10 marks)
Give a written overview of your organisation.
You should include an outline of the context in which they operate. For example a college would operate in an environment in competition with other colleges.
You should outline their day-to-day operations, their data requirements and the types of transactions they carry out. You might include scans and/or diagrams of any documents you have gathered, which should be suitably anonymised so as not to show any personal data that might be included. You should explain the purpose of these documents in the context of the organisation.
You should outline the scope of the database project that you will undertake. This should be suitably ambitious enough to include at least ten entities in the data model. You should make clear what will be included within the scope of the project and what will not be included. This discussion of scope should include both data and operational issues.
任務1 –業務描述（10分）
提供您組織的書面概述。
您應該概述它們運行的上下文。 例如，一所大學將在與其他大學競爭的環境中運作。
您應該概述他們的日常操作，他們的數據要求以及他們執行的交易類型。 您可能會包括對您收集到的所有文檔的掃描和/或圖表，這些文檔應適當地匿名化，以免顯示任何個人信息。
Task 2 – ER and Data Dictionary (20 marks)
Produce an entity relationship diagram for your organisation and an accompanying data dictionary. Your entity relationship diagram should include at least TEN (10) entities. You should use the UML format.
Briefly explain the purpose of normalisation, and clearly justify why each of your entities is in 3NF.
Using an example from your design, explain how normalisation solves the problem of update anomalies
Define the Integrity constraints on the tables in your design. You should clearly include Domain constraints, Business rules (Table constraints) and Propagation constraints in your data dictionary.
任務2 – ER和數據字典（20分）
為您的組織生成一個實體關係圖和一個隨附的數據字典。 您的實體關係圖應至少包括TEN（10）個實體。 您應該使用UML格式。
簡要說明歸一化的目的，並明確說明每個實體為何都位於3NF中。
使用設計中的示例，說明規範化如何解決更新異常的問題
在設計中的表上定義完整性約束。 您應該在數據字典中明確包括域約束，業務規則（表約束）和傳播約束。
Task 3 – Scripts to create table structures (15 marks)
Show the SQL scripts that you have used to create your database in a suitable database environment. You should include scripts for tables, columns, primary and foreign keys and any other database objects that you use. You should include screen shots of the scripts running within the database environment. For high marks, your scripts should implement integrity constraints you identified in Task 2.
You should provide an explanation of how you developed the scripts, order of running, and any issues encountered with them.
任務3 –創建表結構的腳本（15個標記）
顯示在合適的數據庫環境中用於創建數據庫的SQL腳本。 您應該包括用於表，列，主鍵和外鍵以及您使用的任何其他數據庫對象的腳本。 您應該包括在數據庫環境中運行的腳本的屏幕截圖。 對於高分，腳本應實現在任務2中確定的完整性約束。
您應提供有關腳本開發方式，運行順序以及遇到的任何問題的說明。
Task 4 – Data population (10 marks)
You should create suitable data for your database. Show the SQL insert scripts that you have used to populate your database with this data. You should include screen shots of the scripts running within the database environment.
You should provide an explanation of how you developed the scripts, order of data population, and any issues encountered with them. 任務4 –數據填充（10分）
您應該為數據庫創建合適的數據。 顯示用於使用此數據填充數據庫的SQL插入腳本。 您應該包括在數據庫環境中運行的腳本的屏幕截圖。
您應該提供有關如何開發腳本，數據填充順序以及它們遇到的任何問題的說明。
Task 5 – Enhancements (15 marks)
Discuss how you would design and implement THREE (3) enhancements to your current systems. These could include changes in scope, data requirements, functionality, or user interface design.
You should provide a rationale for each of your enhancements that demonstrates how they would be useful for your system and chosen organisation.
任務5 –增強（15分）
討論如何設計和實現當前系統的三（3）個增強功能。 這些可能包括範圍，數據要求，功能或用戶界面設計的更改。
您應該為每個增強功能提供理由，以證明它們將如何對您的系統和所選組織有用。
Task 6 – SQL reports (10 marks)
Use SQL to produce FIVE (5) queries that will be useful for your organisation. For each query you should supply the following:
• Rationale for query – what is its purpose and what is it trying to retrieve
• SQL script running in database environment
• Result set of query shown in database environment
Query scripts and results should be shown as running in the database environment. They should be well-formatted and easy to read.
To achieve higher marks queries should be of a reasonable level of complexity. This will involve the joining of two or more tables, the use of renaming columns to ensure a user friendly result set, and the inclusion of descriptive columns from the tables.
任務6 – SQL報告（10分）
使用SQL產生五（5）個查詢，這將對您的組織有用。 對於每個查詢，您應提供以下內容：
•查詢的原理–它的目的是什麼，它試圖檢索什麼
•在數據庫環境中運行的SQL腳本
•數據庫環境中顯示的查詢結果集
查詢腳本和結果應顯示為在數據庫環境中運行。 它們應該格式正確並且易於閱讀。
為了獲得更高的分數，查詢應該具有合理的複雜度。 這將涉及兩個或多個表的聯接，使用重命名列以確保用戶友好的結果集以及包含表中的描述性列。
Task 7 – Distributed Database Option (10 marks)
In the future your organisation could expand by merging with other similar companies.
Describe the factors that might make them consider implementing a distributed database.
任務7 –分佈式數據庫選項（10分）
將來，您的組織可以通過與其他類似公司合併來擴展。
描述可能使他們考慮實現分佈式數據庫的因素。
Task 8 – Reflective Practice (10 marks)
Using the Rolfe, G., Freshwater, D. and Jasper, M. (2001) model, critically review the learning that you have undertaken in order to complete this assignment.
Based upon your learning, your reflection should include a description; an analysis and; an action plan in order to bring about improvements in the future.
任務8 –反思性練習（10分）
使用Rolfe，G.，Freshwater，D.和Jasper，M.（2001）模型，批判性地回顧您為完成這項任務而進行的學習。
根據您的學習，您的反思應包括描述； 分析和； 一項行動計劃，以期在未來實現改進。
Appendix – Worked example
DO NOT use this example or a variation of it for your own assignment.
The example below shows a written scenario that could be derived from investigating an organisation. There are also some documents of the sort that it would be possible to collect examples of from your own organisation. From this material it is possible to identify the entities that would constitute the database. Note that there is some overlap of entities between the documents.
Attributes names would be those shown in the documents. It is acceptable to introduce new attributes to take account of the data needs of the organisation. It is also worth noting that data in its current form is not necessarily normalised and it would be up to the developer to ensure that it is when the new database model is being developed.
Scenario
Lowry College is Further Education College based in Kent, United Kingdom. They specialise in science education.
They want a database to help them manage teaching and resources for their various courses. This database will not be concerned, at least initially, with the allocation of students to courses.
Each course will have a number of staff allocated to it. A member of staff might be allocated to more than one course. Staff are defined by type: Teacher, Technical Support, Administration, Other.
Courses are made up of modules. A module might be part of more than one course. Modules are defined by type ‘Core’, ’Elective’ or ‘Optional’. Some modules such as ‘Biochemistry of Life’ are taught on different courses.
A course might have one or more laboratories associated with it. A laboratory is administered by one particular course.
A module will be taught in a particular laboratory. A laboratory might host many different modules. Laboratories have equipment in them. A piece of equipment might be allocated to more than one laboratory. Equipment is defined by type. Examples of data are shown in the tables below.
From document one it is possible to derive the following tables using a top-down ER approach:
Course
Staff
Staff Type
Course Staff
From document two the following tables can be derived:
Course
Module
Course Module
Staff
Module Staff
From Document 3 the following tables can be derived:
Course
Laboratory
Course Laboratory
Equipment
Laboratory Equipment
Equipment Type
A completed ER diagram for this scenario could look like that shown below:
Suggested Data Dictionary
Attribute Name / Key
Data Type
Range/ Length
Constraints
Course
Course Code (PK)
Char / Varchar
10
PK
Course Name
Char / Varchar
30
Course Module
Module Code (PK) (FK)
Char / Varchar
10
PK / FK
Course Code (PK) (FK)
Char/Varchar
10
PK / FK
Module
Module Code (PK)
Char/Varchar
10
PK
Module Type Code (FK)
Char/Varchar
10
FK
Module Name
Char / Varchar
30
Module Type
Module Type Code(PK)
Char/Varchar
10
PK
Module Type
Description
Char/Varchar
30
Module Staff
Module Code (PK) (FK)
Char / Varchar
10
PK / FK
Staff Code (PK) (FK)
Char / Varchar
10
PK / FK
Course Staff
Course Code (PK)
Char / Varchar
10
PK / FK
Staff Code (PK) (FK)
Char / Varchar
10
PK / FK
Staff
Staff Code (PK)
Char / Varchar
10
PK
Staff Name
Char/Varchar
30
Staff Type
Staff Type Code(PK)
Char/Varchar
10
PK
Staff Type Description
30
Laboratory
Laboratory Code (PK)
Char/Varchar
10
PK
Laboratory Name
Char/Varchar
30
Laboratory Equipment
Laboratory Code (PK) (FK)
Char/Varchar
10
PK
Equipment Code (PK) (FK)
Char/Varchar
10
PK
Equipment
Equipment Code (PK)
Char/Varchar
10
PK
Equipment Name
Char/Varchar
30
Equipment Type Code (FK)
Char/Varchar
10
FK
Equipment Type
Equipment Type Code (PK)
Char/Varchar
10
PK/FK
Equipment Type Name
Char/Varchar
30
Submission requirements
• Your submission should be in the form of a single word-processed document that includes any necessary diagrams.
• The word count for the document is 2000 words (excluding text in any diagrams). You should explain any assumptions you have made.
• A digital version must be submitted on a CD, USB flash drive or other similarly acceptable medium, along with a copy of the developed database.
422/5000
提交要求
•您的提交應採用包含任何必要圖表的單字處理文檔的形式。
•文檔的字數為2000個字（任何圖中的文字除外）。 您應該解釋您所做的任何假設。
•數字版本必須與CD，USB閃存驅動器或其他類似可接受的介質一起提交，並提供開發數據庫的副本。
Candidate checklist
Please use the following checklist to ensure that your work is ready for submission.
Have you read the NCC Education documents ‘What is Academic
Misconduct? Guidance for Candidates’ and ‘Avoiding Plagiarism and Collusion: Guidance for Candidates’ and ensured that you have acknowledged all the sources that you have used in your work?
❑
Have you completed the ‘Statement and Confirmation of Own Work’ form and attached it to your assignment? You must do this.
❑
Have you ensured that your work has not gone over or under the recommended word count by more than 10%?
❑
Have you ensured that your work does not contain viruses and can be run directly?
❑