Database Design and Developement

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