Database Design Documentation and Implementation Report - ICBT HND

This report details the design and implementation of a relational database for New Life Academy Pvt Ltd, an educational institute. The assignment covers the analysis of data models, comparing different approaches to database design, and creating an Entity Relationship Diagram (ERD). It includes the development of relational schemas and the creation of a database using SQL Server, with at least 15 records per table. The report also presents SQL queries to retrieve specific data, such as student lists, batch details, and program information. Furthermore, it provides a test plan, test strategy, and test cases for the database, along with an explanation of verification, validation, and access rights for different user types. The report adheres to Harvard referencing and follows specific formatting guidelines, including word count, margins, and font specifications.

Assignment Cover Sheet
Qualification Module Number and Title
HND in Computing / Network Engineering SEC4205/Database Design Development
Student Name & No. Assessor
Nishanthi . K
CL/HND/22/17 Chatura.N.Warnasuriya
Hand out date Submission Date
Assessment type Duration/Length of Weighting of Assessment
Report 3000 words Assessment Type 40%
TBC
Learner declaration
I certify that the work submitted for this assignment is my own and research sources are fully
acknowledged.
1

Marks Awarded
First assessor
IV marks
Agreed grade
Signature of the assessor Date
2

FEEDBACK
FORM
INTERNATIONAL COLLEGE OF BUSINESS &
TECHNOLOGY
Module: Database Design Development
Student:
Assessor: Chatura.N.Warnasuriya
Assignment: Database Design Documentation and Implementation
Strong features of your work:
Areas for improvement:
3

Learning outcomes covered
 LO1 Understand the database concepts and components
 LO 2 Develop a logical database design.
 LO 3 Implement a physical database for a given requirement
 LO 4 Test and Manage databases
Scenario and the Task
New Life Academy pvt LTD is a Sri Lankan based education institute which conducts different
education programs such as Higher National Diplomas, Bachelors and Masters Programs. They
have set of student councilors who register students for the programs. Once a student registers
following details would be considered. Registration No, Student Name, Contact Details (Home,
Mobile and Email), Address, NIC, Education and Professional Qualifications. Same student can
register for multiple programs and a new registration no would be issued based on registering
batch and program. Once students are registering the respective student counselors’’ employee
no should be recorded for further reference. Usually, programs are conducted in both full time
and part time and should be finished with in a given time period and payment amount. Each
Program have modules and modules would include Module no, Name, Credit Value, No of
Sessions and module leader Emp No. Module leader is a lecturer who is working at the institute
as a full time employee and allocated for the batches as Batch advisors. The batches are created
based on student registrations. Each Student is allocated to a batch. Batch would have a Batch
No, Relevant Program, Starting Date, End Date and No of Students. In the institute, the
employee data should be recorded appropriately. And Each employee is assigned to different
departments such as Academics, Marketing, Accounts …etc.
New Life Academy pvt LTD is willing to enhance their information system and to have a
properly designed Database Management System. Assume that you have been assigned as the
Database Designer of the Development Company to prepare a fully descriptive Database Design
documentation to the implementation of the Information System.
4

Students can add any functionality which will enhance the system and make the proposed
solution more comprehensive.
Use Harvard referencing to properly acknowledge all the external sources you use.
Tasks:
Design the database with the constraint that the available technology is relational.
1. Explain what is data model, critically compare different data models and explain why
older data models are being replaced by new data models.(LO1) (15 Marks)
2. Analyze and briefly explain the different approaches to database design. (LO1) (10 Marks)
3. Draw an entity relationship diagram for given scenario with proper standards. Identify
important keys and represent different types of attributes and relationships. (LO2) (10
Marks)
4. Draw Relational Schemas. Effectively map conceptual data models with relational database
schema according to the mapping algorithm. All the steps should be clearly mentioned.
(LO2) (10 Marks)
5. Create the database using SQL server. (LO3) Practical submission and Demonstration (15
Marks)
NOTE: Make sure to enter at least 15 records for each table.
6. Write sql queries for below requirements. ( LO3)
Practical submission and Demonstration (10 Marks)
5

List of Student names of a particular Batch
List of Batch details and relevant student councilor information
List of Program details with relevant modules that they relate to
7. Provide the Test plan, test strategy and proper test cases. (LO4) (10 Marks)
8. Explain how verification and validation has addressed in your data base and the access rights
to various types of users. (LO4) (10 Marks)
6

Assessment Criteria
This submission will be
assessed as follows Total marks Marks obtained by
Allocated the student for the
answer provided
TASK 1 15
TASK 2 10
TASK 3 10
TASK 4 10
TASK 5 15
TASK 6 10
TASK 7 10
TASK 8 10
TASK 9 10
TOTAL 100
Guidelines for the report format
o Paper A4
o Margins 1.5” left, 1” right, top and bottom
o Page numbers – bottom, right
o Line spacing 1.5
o Word Count 3000
7

o Font
o Headings 14pt, Bold
o Normal 12pt
o Font face- Times New Roman
 Referencing and in-text citation should be done strictly using Harvard Referencing
System.
 All citation should be given as per "Harvard Referencing Style" for Text, Tables
and Figures
 Late submission will be penalized
 Please refer to the ICBT HND student handbook for further details
 Improper report structure will lead to maximum of 15% reduction in the
total marks.
Note: Any kind of copying and plagiarized text will lead to automatic Re-submission
8

Marking Scheme
Task-1 contains 15 marks
Criteria
Marks
Out of 15
Fail
Not explained the data model. Not compared enough data models
0-4
.Not given proper reasons for the replacement.
Pass
Explained the data model. Compared minimum 3 data models. Not
5-8
provided enough/Proper reasons to replace the older data models
Good
Explained the data model. Compared minimum 5 data models. 9-12
Provided proper reasons to replace the older data models
Excellent
Well explained about the data model. Critically compared different
13-15
5 data models. Well explained why the older data models are being
replaced by new data models
9

Task-2 contains 10 marks
Criteria
Marks
Out of 10
Fail
0-2
Not analyse properly the different approaches. Not explain the
different approaches
3-4
Pass
Analyzed the different approaches. Not provided the proper
explanation.
Average / Good
5-7Analyzed the different approaches using enough features. Provided
the proper explanation with examples
Excellent
Design Well Analyzed and briefly
explained the different approaches to
database design with proper examples and
figures also.
8-10
10

Task-3 contains 10 marks
Diagram should be evaluated according to the following criteria.
Criteria
Marks
Out of 10
Fail
Poor logical database design.
 Include incorrect Entities
 Include incorrect Attributes and Relationships 0-2
 Not used proper /standard symbols
Pass
Develop a logical database design.
Entity Relationship Diagram
3-4
 Identification of correct Entities
 Identification of correct Attributes and Relationships
Average Design/ Good
 Clear identification of Entities, Attributes and
5-7
Relationships
 Correct use of ERD notations with minor mistakes
11

Excellent Design
 Highly detailed diagram
 Backed by relevant assumptions
 ERD with cardinality and attributes with proper
standard symbols
8-10
 Proper theories and techniques need to apply in
ERD
 Use Chen & Martin notations for ERD
12