Unit: Databases - Spring 2024: Organisational Summative Report Assignment

Introduction

This assignment tasks you with demonstrating the knowledge and skills acquired throughout this unit by designing and developing a back-end database application tailored to the needs outlined below.

To complete this assignment effectively, select a suitable organization for research and as the basis for your database application.

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

Step 1: Choose an Industry of Interest

Select an industry that interests you, such as commercial airlines, healthcare, retail, manufacturing, etc.

Step 2: Investigate Data Types and Transactions

Research the data types typically managed and transactions conducted within your chosen industry. For example, an educational institution might handle student records, staff management, attendance, assessments, etc.

Task 1 Analysis And Design

Produce the following documents:

a) Introduction, Outline, and Initial Analysis: Draft a clear non-technical summary aimed at a company’s senior management team, stakeholders, and shareholders. Assume your audience has limited knowledge of databases.

In your summary: i) Introduce the company and outline its business operations. ii) Explain the concept of a database and its intended functions. iii) List and describe the transactions the database will facilitate for daily organizational operations. iv) Enumerate all entities to be incorporated into the database. v) Analyze the pros and cons of implementing a database specific to the chosen organization.

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

i) Create an Entity Relationship Diagram (ERD) and normalize attributes to 3rd Normal Form (3NF) based on identified entities.

ii) Ensure the ERD illustrates at least six (6) data entities derived from the initial transaction analysis in task 1a).

iii) Provide a written statement detailing challenges encountered during ERD creation and 3NF normalization. Explain the resolution process for identified errors.

c) Create a Comprehensive Data Dictionary: Develop a detailed Data Dictionary for the entity relationship model, incorporating entities and attributes identified in task 1b).

Include: i) Attributes for each entity. ii) Identification of primary, foreign, and compound keys. iii) Data types and field lengths (if applicable). iv) Field constraints relevant to the organization.

Task 2 Implementation

a) SQL Table Creation: Write SQL scripts to create normalized tables from task 1b), clearly showing commands used along with keys, data types, and field lengths. Ensure consistency with the tables designed in the previous phase.

b) Data Population: Enter sample data (minimum THREE (3) rows per table) to demonstrate the tables’ capability to store appropriate data.

c) SQL Queries: Develop FIVE (5) queries showcasing your SQL proficiency, incorporating commands like Select, Update, Delete, From, Where, And, Count, Ascending, and Order By.

Include:

• Two (2) queries using multiple commands.
• Two (2) queries joining two or more tables together.

Task 3 – Evaluation

Provide a written assessment and evaluation of your work, addressing:

• How the created database meets the data and information requirements outlined in task 1a).
• The importance of ER modeling in database design and its role in identifying and resolving design and implementation issues.
• Any assumptions made during the project.
• How the database supports the transactions identified in task 1a).