B7IS109 Library Management System Assignment: SQL Server Case Study for Normalised 3NF Database Design

Assignment Objectives

1. To develop/analyse a set of requirements for a database system.
2. To develop an Entity Relationship Diagram (ERD) and a normalised schema.
3. To implement the database system in SQL Server

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
   • 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)

Instructions for Submission

You are required to:

• Create a folder with yourNameDBCA.
• Include the report in the folder.
• Develop the scripts for your SQL server database and include them in the folder.
• Compress the folder and upload it on Moodle Page B7IS109 using the specified link.

Note: Your SQL server database must be available in your SQL Server Account until you receive your results for this module.

Assessment Criteria

General Assessment Submission Requirements for Students:

1. Online assignments must be submitted no later than the stated deadline.
2. All relevant provisions of the Assessment Regulations must be complied with.
3. Extensions to assignment submission deadlines will not be granted, other than in exceptional circumstances. To apply for an extension please go to https://servicedesk.dbs.ie/HEAT/ and log a ticket. Please note lecturers DO NOT grant these extensions.
4. Students are required to retain a copy of each assignment submitted, and the submission receipt.
5. Assignments that exceed the word count will be penalised.
6. Students are required to refer to the assessment regulations in their Student Guides and on the Student Website.
7. Dublin Business School penalises students who engage in academic impropriety (i.e. plagiarism, collusion and/or copying). Please refer to the attached referencing guidelines for information on correct referencing.
8. Late submissions will be penalised by 2 marks per day.

What is referencing and why is it necessary?

Please follow this link to the Harvard Style Referencing Guide – all referencing is required in this format.

http://issuu.com/dbslibrary/docs/harvard-referencing-guide/1?mode=a_p The School of Arts generally use APA Referencing, information is available under DBS library guides on www.library.dbs.ie.

cta_qestion_3