ICBT - CSE4005 Database Design Development Report: DS Library System

This report presents a comprehensive database design for the DS Library, a Sri Lankan-based library center. The assignment involves designing a relational database management system to enhance the library's information system. The report includes an analysis of data models, database design approaches, and the creation of an Entity Relationship Diagram (ERD). It also covers the development of relational schemas, database creation using SQL Server, and the implementation of SQL queries for various functionalities. Furthermore, the report provides a test plan, test strategy, and test cases, along with an explanation of verification and validation processes and access rights for different users. The documentation adheres to academic standards, incorporating proper formatting and Harvard referencing.

Assignment Cover Sheet
Qualification Module Number and Title
Higher Diploma in Computing and Software
Engineering CSE4005/Database Design Development
Student Name & No. Assessor
SAHAN WIJEBANDARA
KG/HDCSE/12/25 Mr. Asanka Dinesh
Hand out date Submission Date
17.05.2021 03.06.2021
Assessment type Duration/Length of Weighting of Assessment
Reports 3000 Words Assessment Type 100%
End of the Module
Learner declaration
I SAHAN WIJEBANDARA KG/HDCSE/12/25, certify that the work submitted for this
assignment is my own and research sources are fully acknowledged.
Marks Awarded
First assessor
IV marks
Agreed grade
Signature of the assessor Date
1

FEEDBACK FORM
INTERNATIONAL COLLEGE OF BUSINESS & TECHNOLOGY
Module/Title: Database Design and Development /Design a Database Management System for
DS Library
Student:
Assessor: Mr. Asanka Dinesh
Assignment: Database Design & Development
Strong features of your work:
Areas for improvement:
Marks Awarded:
2

Course Work
Learning outcomes covered
 LO1 Explain the database concepts and components
 LO 2 Develop a logical database design.
 LO 3 Construct a physical database for a given requirement
 LO 4 Test and Manage databases
Scenario and the Task
DS Library is a Sri Lankan based library center which provides different books for students from
Schools, Diplomas, Bachelors and Masters Programs. They have set of librarian who register
students for the library. Once a student registers following details would be considered.
Registration No (Borrower No), Student Name, Address, Contact Details (Home, Mobile and
Email), Address, NIC, Education …etc. Students have to pay initial registration fee and annual
subscription. Same student can borrow for multiple books up to 3 books. Once students are
registering the respective librarian’s employee no should be recorded for further reference.
Usually, books are being categorized based on title. Category details should be managed
accordingly based on assumptions such as Category no, category name, no of books, age limit,
subject title… etc. Each Book has Book no, Name, Author, ISBN, respective category and Price.
There can be multiple copies from same book. Once a book is borrowed, the remaining copies
should be maintained in the database. When a book is borrowed Borrower no, borrowed date,
returned date and relevant book details should be recorded. If a particular student tries to borrow
more than 3 books or trying to borrow a book which is not for his age limit, librarian should be
able to check the eligibility. If a book is not returned back within 7 days late payment is
calculated as LKR 50 per day. Further author details should be recorded such as author id, author
name …etc.
DS Library 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.

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)
 List of Borrowers of a particular Book
 List of pending late payment details
 List of author details with relevant books that he has wrote
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)
9. Provide a well-structured documentation including proper academic style and Referencing
and in-text citation using Harvard Referencing System. (10 Marks)

Assessment Criteria
This submission will be assessed as follows Depth of the task
TASK 1 Compare Hierarchical, Network and
Relational data model and the
importance of adapting a new data
model
TASK 2 Top-down design method, Bottom-
up design method, Centralized
design and De centralized design
TASK 3 ERD with cardinality and attributes
with proper standard symbols
(Proper theories and techniques need
to apply in ERD)
. Use Chen & Martin notations for
ERD
TASK 4 Relational Schema for ERD state the
primary key foreign key concept
correctly
(Proper theories and techniques need
to apply in Relational schema)
TASK 5 Create Database using SQL server
TASK 6
Produce SQL queries for
manipulation functions
TASK 7
Correct Test plan and test cases with
all test documents
TASK 8 Clearlyexplainthe applied
verification and validation process in
the data base and explain the Object permission,

Statement permission
and Implied permission Statements
TASK 9
Well-structured Documentation with
proper formatting styles and Harvard
Referencing System is used for
Referencing and in-text citation
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
Paper A4
Margins 1.5” left, 1” right, top and bottom
Page numbers – bottom, right
Line spacing 1.5
Word Count 3000
Font
Headings 14pt, Bold
Normal 12pt
Font face- Times New Roman
Referencing and in-text citation should be done strictly using Harvard Referencing System.

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

Task-2 contains 10 marks
Criteria
Marks
Out of 10
Fail
0-2
 Not analyze properly the different approaches. Not explain
the different approaches
Pass
3-4 Analyzed the different approaches. Not provided the proper
explanation.
Average / Good
5-7 Analyzed 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

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
Good
 Clear identification of Entities, Attributes and 5-7
Relationships
 Correct use of ERD notations with minor mistakes
Excellent
 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

Task-4 contains 10 marks
Criteria Marks
Out of 10
Fail
In correct Relational Schema
 Not identified the correct Entities Attributes and 0-2
Relationships
Pass
Relational Schema
 Identification of correct Entities 3-5
 Identification of correct Attributes and Relationships
Good
 Average Design
 Clear identification of Entities, Attributes and
Relationships 6-7
 Correct use of Relational Schema notations with
minor mistakes
Excellent
 excellent Design
 Relational Schema with proper standard symbols
 Proper theories and techniques
need to apply in Relational schema
 Relational Schema for ERD state the primary key foreign
key concept correctly 8-10
Task- 5 contains 15 marks

Criteria Marks
Out of 15
Excellent 15-13
 Appropriate use of more sophisticated database features (e.g.,
use of stored procedures / functions / triggers to implement
business rules)
 Database design being proposed to facilitate requirements
 Provided the relevant assumptions
Good 12-9
 Make a good attempt to follow the Relational Database
Model including proper database objects
 Provide More accurate database design
Pass 8-5
 Provide a Basic Database Design with minor mistakes
including enough database objects (simple design)
Fail 4-0
 Provide a poor Database Design with mistakes
 Not work with enough database objects
Task 6 -contains 10 marks
Written SQL Queries (1 mark for each query 2×3 -> 6)
 Provided the correct SQL queries
Appropriate Data with proper records (4 marks)
 Derive test data for the Database
Task-7 contains 10 marks