Unit: Databases- Spring 2024: Organisational Summative Report Assignment, UCD, Ireland

Introduction

This assignment requires you to demonstrate the knowledge and skills you have acquired throughout this unit by designing and creating a back-end database application that addresses the tasks given below.

In order to complete this assignment, you should choose an appropriate organisation on which to conduct research and base your database application.

This assignment is split into THREE (3) tasks and accounts for 100% of the overall coursework mark for this unit.

Step 1: Select an industry in which you have an interest.

For example, commercial airlines, commercial banking, retail, beauty, digital, manufacturing, healthcare, fitness, publishing, marketing, hospitality, leisure and tourism, automotive, production, construction etc.

Step 2: From the industry selected you should investigate the different types of data that they hold and the types of transactions they carry out.

For example, an educational establishment will hold data about:
– Students
– Attendance
– Staff
– Rooms
– Units
– Assessments
– Results
– Staff wages/salary
– Equipment

Their transactions might include:
– Adding new students
– Adding new staff
– Deleting staff who leave
– Adding student attendance information
– Adding students to units
– Allocating staff to teach units.
– Allocating rooms to units
– Allocating different assessments to units and recording the results for each
assessment the student undertakes
– Increase or decrease the salary of a member of staff.
– Buy new equipment.
– Delete equipment that has been used or damaged.

Task 1 Analysis and Design

Produce the following documents:
a) Introduction, outline, and initial analysis: Write a clear non-technical summary for a company’s senior management team, their stakeholders, and shareholders. Assume that your summary will be read by those who have very limited knowledge or digital skills in databases.

In your summary use clear, concise language and fully explain any technical terms that are used so that the readers can understand its contents.

The summary should:
i) Introduce the company and describe the nature of its business.
ii) Explain what a database is and what typical functions the proposed database
will perform.
iii) Clearly list and describe the transactions the database will carry out to help the daily functions of the organisation.
iv) List all entities to be included in the database.
v) Identify and discuss the advantages and disadvantages of implementing a
database specifically for the organisation you have chosen.

b) Using the list of entities and functions identified in task 1a), complete the following:

i) Draw an Entity Relationship Diagram (ERD) and list and normalise to 3
rd normal form (3NF) all the attributes that will be required in the database

ii) The Entity Relationship Diagram (ERD) should show at least SIX (6) data entities from the initial analysis from the identification of the transactions that were carried out in task 1a).

iii) Provide a written statement of any problems that you encountered during the process of creating the ERD and production of the 3 rd Normal Form (3NF) and explain the process you undertook to resolve any errors you found.

c) Create a full Data Dictionary for the entity relationship model using the entities and attributes identified in task 1b). The data dictionary should include the following:

i) All attributes used for each entity.
ii) Identify all primary, foreign, and compound keys that you have created.
iii) Show the data type and field length (if applicable) for each attribute used.
iv) Include any field constraints that have been identified from your chosen organisation

Task 2 Implementation

a) Create all of the normalised tables from task 1b) in SQL. Write your SQL scripts clearly showing the commands you have used with the keys, data types and field lengths. Show the finished tables and ensure they are consistent with the tables you created earlier in the design phase.

b) Enter a range of sample data in all tables (minimum THREE (3) rows per table) to show that your tables are capable of holding appropriate data.

c) Write FIVE (5) queries that demonstrate your knowledge and understanding of SQL. SQL statements to be used should include all of the following commands:

Select, Update, Delete, From, Where, And, Count, Ascending and Order By.
Ensure TWO (2) of your queries use multiple commands.
TWO (2) of the queries should join TWO (2) or more tables together.

Task 3 – Evaluation

Provide a written assessment and evaluation of the work you have undertaken.
Your discussion should include:
• How the database you have created meets the data and information requirements of your chosen organisation as identified in task 1a).
• Why the use of ER modelling is an essential part of the design of a database
solution, and how you used it to identify and rectify any design and implementation issues you encountered.
• Any assumptions that you have made.
• How the database supports the transactions that you identified in task 1a).