Unit:
Database Design and Development
Assignment title:
Developing a Database
20 credit version
Spring – Winter 2022
Important notes
-
Please refer to the Assignment Presentation Requirements for advice on how to set out your assignment. These can be found on the NCC Education website. Hover over ‘About Us’ on the main menu and then navigate to ‘Policies and Procedures’ then scroll to the ‘Student Support’ area.
-
You must read the NCC Education document Academic Misconduct Policy and ensure that you acknowledge all the sources that you use in your work. These documents are available on the NCC Education website. Hover over
‘About Us’ on the main menu and then navigate to ‘Policies and Procedures’ then scroll to the ‘Student Support’ area.
-
You must complete the Statement and Confirmation of Own Work. The form is available on the NCC Education website. Hover over ‘About Us’ on the main menu and then navigate to ‘Policies and Procedures’ then scroll to the ‘Student Support’ area.
-
Please make a note of the recommended word count. You could lose marks if you write 10% more or less than this.
-
You must submit a paper copy and digital copy (on disk or similarly acceptable medium). Media containing viruses, or media that cannot be run directly, will result in a fail grade being awarded for this assessment.
-
All electronic media will be checked for plagiarism.
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 it.
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, 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 online, through personal contacts with someone who works in the industry or simply by using a particular business’s services. Materials that you gather might include:
-
Invoices
-
Receipts
-
Order Forms
-
Customer Records
-
Delivery Notes
-
Complaints Forms
-
Booking Forms
-
Descriptions of daily tasks
-
Interviews with staff members
-
Company reports
Page 2 of 10
Database Design and Development © NCC Education Limited 2022
Task 1 – Description of Business (5 marks)
Give a written overview of your organisation.
You should include an outline of the context in which it operates. 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 (10) 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.
Task 2 – ER and Data Dictionary (15 marks)
Produce an entity relationship diagram (ERD) 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.
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.
Task 3 – Normalisation (15 marks)
Show how you have used normalisation in developing your data model. You should:
-
Explain the purpose of normalisation, and clearly justify why each of your entities is in 3NF.
-
Explain how you have used normalisation to check tables are well-structured.
-
Explain how normalisation solves the problem of update anomalies by using examples from your design
Task 4 – Assessment of Design (10 marks)
Give an evaluation of how the work you have done so far has met the data requirements and possible transactions of the organisation. You should show how you have implemented the logical and physical design processes in the work you have completed. You should discuss how you have:
-
Mapped logical database design to physical database design.
Page 3 of 10
Database Design and Development © NCC Education Limited 2022
-
Identified any derived data and how you will represent it.
-
Denormalised tables where appropriate.
Task 5 – Scripts to create table structures (10 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.
Task 6 – Data population (5 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.
Task 7 – Enhancement using SQL (10 marks)
Outline a potential change that could be implemented after the original database implementation. This could be an enhancement or the result of some change in requirements. You should provide a rationale for the change explaining how it will benefit the organisation. You should then demonstrate the use of SQL to implement the change using features such as the ALTER TABLE command.
Task 8 – SQL reports (10 marks)
Use SQL to produce TEN (10) 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 it is 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.
Page 4 of 10
Database Design and Development © NCC Education Limited 2022
To achieve higher marks, queries should be of a reasonable level of complexity. This will involve the joining of TWO (2) or more tables, the use of renaming columns to ensure a user friendly result set, using sub-queries and the inclusion of descriptive columns from the tables.
Task 9 – Future development of a data warehouse (10 marks)
Discuss the factors that might lead your organisation to build a data warehouse. You should demonstrate your understanding of the common uses of a data warehouse and how it would operate in the context of your chosen organisation.
Task 10 – (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.
Page 5 of 10
Database Design and Development © NCC Education Limited 2022
Appendix – Example of information collated on scenario
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.
Attribute 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.
Page 6 of 10
Database Design and Development © NCC Education Limited 2022
Page 7 of 10
Database Design and Development © NCC Education Limited 2022
Document 2. Modules on Courses
Page 8 of 10
Database Design and Development © NCC Education Limited 2022
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.
Page 9 of 10
Database Design and Development © NCC Education Limited 2022
Please use the following checklist to ensure that your work is ready for submission.
Have you read the NCC Education document Academic Misconduct Policy 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?
❑
❑
❑
❑
Page 10 of 10
Database Design and Development © NCC Education Limited 2022
