B7IS109 Database Design and Cloud Technologies Assessment Brief 2026

B7IS109 Assessment Brief

Assessment Task (100 Marks) – Weighting (70%)

You are tasked with developing and designing a new database system for the below list or a company/business of your choosing. The implementation of your system must be carried out using MS SQL Server.

1. Library Management System

A database to manage books, borrowers, lending history, due dates, and fines.

2. Online Ordering System for a Restaurant

A system for customers to order food online, track orders, and manage payments.

3. Student Enrolment System

A database to manage student records, course registrations, and grades.

4. Event Management System

A system to handle event registrations, attendee information, and ticket sales.

5. Inventory Management System

A database to track products, stock levels, suppliers, and purchase orders.

6. Hotel Booking System

A system for managing room reservations, guest information, and billing.

7. Online Learning Platform

A database to manage courses, instructors, student enrolments, and progress tracking.

8. Medical Appointment System

A system to manage patient appointments, doctor schedules, and medical records.

9. E-commerce Product Catalogue

A database to store products, customers, orders, and payment details for an online store.

10. Movie Rental System

A system to manage movie rentals, customers, and overdue fees.

Requirements

Report (35 Marks)

The report must include the following:

1. Background for the development of the chosen database system. (Why the Database is needed) Scope includes requirements for the database system to be developed. (What should it be able to do)

(5 marks)

2. An Entity Relationship Diagram (ERD) of the system must include a minimum of 4 entities displaying all the relationships with cardinalities and all the constraints. The model (relational schema) of your database system must fulfil the requirements of 3NF. Use Draw.io or LucidChart to develop the ERD.

(15 marks)

3. Evaluation of a suitable cloud solution for your Database.

(7 marks)

4. Outline 2 security threats posed to a database and how they can be mitigated

(6 marks)

5. Appropriately structured report showing precise information. (Cover page, Table of Contents, Headings etc)

(2 marks)

(Total: 35 marks)

Completed Database System (using MSSQL Server) (65 Marks)

Consider the ERD created and;

1. Create the relevant tables with appropriate relationships in MS SQL Server using the CREATE TABLE statement.

(6 Marks)

2. Ensure appropriate Data types are used.

(5 Marks)

3. Populate the tables with a minimum of 5 records using INSERT INTO SQL Statements.

(5 Marks)

4. Create the following parametric stored procedures :

• Inserting new values for ONE table
• Deleting values from ONE table
• Search BETWEEN two dates
• Use of the “LIKE” operator
• A parametric Stored Procedure that would support the business

(25 marks)

5. Create 4 Views that would be beneficial for the business. Views should consist of: ● At least 1 using a JOIN.

• At least 1 listing all items from any table in order of some attribute o At least 1 using the “DISTINCT” operator
• At least 1 using “Aliased” column names

(20 Marks)

6. Scripts for your SQL server database for structure and data.

(4 marks)

(Total: 65 marks)