Database Design and Developement

The process of creating a thorough data model for a database is known as database design. This data model provides all of the logical and physical design options, as well as physical storage parameters, that are required to create a design.

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

Criteria Marks
Out of 10
Excellent
 Detailed Test Plan
 Provided the Test Strategy and Proper test cases and test data 10 - 8
Good 7-5
 Provided Correct Test Plan and Test Cases with
less test data
Pass Level 4-2
 Provided Only test cases with minor
mistakes with less test data
Fail
 Not provided proper test cases and not provided proper test data 2-0
Task-8 contains 10 marks
Criteria Marks
Out of 10
Excellent
 Provided and well explained the applied verification and validation process
with the relevant examples,
 Detailed Explanation of Access rights with Suitable Examples for Object
permission, Statement permission and Implied permission Statements
10-8
Good
 Provided the explanation of verification and validation
process with the relevant examples
 Provided the explanation of Access rights with Suitable Examples
7-5
Pass
4-3
 Provided the process of Verification and Validation Process only.
 Provided the Access Rights only for the Database without the description
and examples
Fail
 Not provide the process of Verification and Validation
properly
 Not provide examples to show the Access Rights only for the Database 2-0

Acknowledgement
Task- 9 contains 10 marks
Criteria Marks
Out of 10
Excellent
 Professional standard of documentation
 with screen shots & good explanation
 Harvard Referencing System is used for
Referencing and in-text citation.
10-8
Good
 High standard of documentation with screen
shots & average explanations
`7-5
Pass
 Acceptable standard of documentation with
poor explanations 4-3
Fail
 Not followed a proper academic style,
 Not provided the referencing /provided in
 correct citation. 2-0

First of all, I would like to express my appreciation to all those who provided me the to complete
this report. A special gratitude I give to me lecture Database Design Development, Asanka Dinesh
whose contribution in suggestion and encouragement helped me to coordinate my assignment.
I would like to pay my gratitude to the website that I have referred to in completing this
assignment. As well as I give my deepest appreciation for my family members for encouraging
and helping me in several aspects when completing this work.
And I thank all of my colleagues who helped me on this task.
Finally, I would like to thank all the ICBT staff for supporting me to complete the task in this
report.
Abbreviation
Abbreviation Meaning
DAD Database and Design
DBMS Data Base Management System
ER Entity Relationship

UML Unified Modelling Language
PK Primary Key
FK Foreign Key
ID Identity
1:1 One to One
1:M One to Many
M:M Many to Many
Executive Summery
World is fast developing with technologies. Therefore, our all system should be developed with
technologies. Why people are attracted to this field, it is the most interesting field in the world. If
we want to produce an efficient and accuracy, good service, we need to information technology.
This scenario explains their old manual system are most problem, therefore this DC Library
Management Database System need to computerized system.
I have identified the advantage of using a standard Database Management System development
life cycle model for developing the Database solution what is proposed. In the task I have
implemented the suitable data model of the database solution.

Last task is done by considering the requirements and specification of the database management
system in library with benefits and future requirements and recommendation.
Table of Content
Contents
Task 1............................................................................................................................................19
Data Models..............................................................................................................................19
Two types of data modelling technique are,..........................................................................19
Types of Data Models............................................................................................................19
Task 2............................................................................................................................................24
Database Design.......................................................................................................................24
What is Database Design?.....................................................................................................24
Database Development Lifecycle..........................................................................................24

Database Design Technique..................................................................................................26
How to Design Database: Steps of Designing Database.......................................................26
Task 3............................................................................................................................................29
Task 4............................................................................................................................................30
Relationship..............................................................................................................................30
One to One.............................................................................................................................30
Task 7............................................................................................................................................32
Test Case...................................................................................................................................32
Reference......................................................................................................................................33
Task 1
Data Models
Data models define how the logical structure of a database is modeled. Data models are
fundamental entities to introduce abstraction in a DBMS. Data models define how data is
connected to each other and how they are processed and stored inside the system.
Two types of data modelling technique are,
 Entity Relational ship Model (ER)
 Unified Modelling Language (UML)
Types of Data Models
 Hierarchical Model
 Network Model
 Entity-Relationship Model
 Relational Model

Hierarchical Model
To which all the other data is link. The hierarchy starts from the Root data, and expands like a
tree, adding child nodes to the parent nodes.
Data is organized into tree-like structure with one one-to-many relationship between two different
types of data. For example, one department can have many courses, many professors and of
course many students.
Hierarchical 1
Features of Hierarchical model
 One to Many Relationship - Where the one-to-many relationship is between the data
types. Also, there can be only one path from to any node.
 Parent-Child Relationship - Each child node has a parent node but a parent node can
have more than one child node. Multiple parents are not allowed.
 Deletion Problem - If a parent node is deleted then the child node is automatically
deleted.
 Pointers – Pointers are used to link the parent node with child node and are used to
navigate between the store data.
Advantage Disadvantage
It is very simple and fast to traverse through a
like structure
Complex Relationship are not support.
The integrity of data is maintained. As it does not support more than one parent of
the child node so if we have some complex
relationship where a child node to have two

parent nodes then that can’t be represented
using this model.
If a parent node is deleted then the child node
is automatically deleted.
Hierarchical 2
Network Model
It was the most popular model before the relational model. This model is the same as the
hierarchical model, the only difference is that a record can have more than one parent. It replaces
the hierarchical tree with a graph.

Network 1
Features of a Network Model
 Ability to merge more relationship- As there are more relationships so data is more
related. This model has the ability to manage one-to-one relationships as well as many-to-
many relationships.
 Many Paths: As there are more relationships so there can be more than one path to the
same record. This makes data access fast and simple.
 Circular linked list: The operations on the network model are done with the help of the
circular linked list. The current position is maintained with the help of a program and this
position navigates through the records according to the relationship.
Advantage Disadvantage
The data can be accessed faster as
compare to the hierarchical model.
As more and more relationship need to be
handled the system might get complex.
As there is a parent-child relationship so
data interiority is present. Any change in
parent record is reflected in the child
record.
Any change like updating, deletion,
insertion is very complex.

2
Entity-Relationship Model
In this model, we represent the real-world problem in the pictorial form to make it easy for the
stakeholders to understand. It is also very easy for the developers to understand the system by just
looking at the ER diagram.
ER diagram has following three components:
 Entities: Entity is a real-world thing. It can be person, place, or even a concept.
 Attributes: An entity contains a real-world property called attribute. This is the
characteristic of that attributes.
 Relationship: Relationship tell how two attributes are related.
ER 1
Features of ER Model
 Graphical Representation for Better Understanding- It is very easy and simple to
understand so it can be used by the developers to communicate with the stakeholders.
 ER Diagram- ER diagram is used as a visual tool for a representing a model.
 Database Design- The model helps the database designer to build the database and is
widely used in database design.
Advantage Disadvantage
Simple No industry standard for notation
Effective Communication Tool Hidden information

Easy conversion to any model
ER 2
Relational Model
In this model, the data is maintained in the form of a two-dimensional table. All the information is
stored in the form of row and columns. The basic structure of a relational model is tables. So, the
tables are also called relations in the relational model.
Features of Relational Model
 Tuples- Each row in the table is called tuple. A row contains all the information about any
instance of the object.
 Attribute or Field- Attributes are the property which defines the table or relation. The
values of the attribute should be from the same domain.
Advantage Disadvantage
Simple Hardware Overheads
Scalable Bad design
Structural Independence
Relational 1
Task 2
Database Design

Database Design 1
What is Database Design?
Database design is defined as a collection of steps that help with designing, creating,
implementing, and maintaining a business’s data management system. The main purpose of
designing a database is to produce physical and logical models of designs for the purposed
database system.
Database Development Lifecycle
There are various stages in database development. However, it is not necessary to follow each of
the steps sequentially. The lifecycle can be broadly divided into three steps: requirement analysis,
database designing, and implementation.
Requirement Analysis
Requirement analysis requires two steps,
 Planning: In this stage, the plan of the entire Database Development Life Cycle is
decided. It is also requiring and analysis of the organization’s information system strategy.
 Defining the system: This stage explains and lays out the proposed database system’s
scope.

Database Designing
The actual database designing takes into account two key models.
 Logical Model: It is concerned with using the given requirements to create a database
model. The complete design is laid out on paper at this stage, without considering any
specific DBMS requirement or physically implementing it.
 Physical Model: This stage comes after the logical model and therefore involves
physically implementing the logical mode. It takes the DBMS and other physical
implementation factors into consideration.
Implementation
The implementation stage of the database development life cycle is concerned with,
 Data conversation and data loading: It comprises data importation and data conversation
coming from the old system into the new database.
 Testing: This stage identifies errors in the new system and make sure if all the database
requirement specification are met.
Database Design Technique
The two most common techniques used to design a database include.
 Normalization: Tables are organized in such a way that it decreases data redundancy and
dependency. Larger tables are divided into smaller tables and are linked together using
relationship.
 Entity Relationship (ER) modeling: It’s a graphical database design approach that
model’s entities, their attributes, and defines relationships among these entities to signify
real-life objects. An entity is any real-world item that’s different or unique from the
surroundings.

How to Design Database: Steps of Designing Database
Database designing generally starts with identifying the purpose of your database. The relevant
data is then collected and organized into tables. Next, you specify the primary keys and analyze
relationships between different tables for an efficient data design. After refining the tables, the last
step is to apply normalization rules for table standardization.
Let’s look at these steps of database design in detail:
 Define the objective of your Database:
The first step is to determine the purpose of your database.
 Locate and consolidate the necessary data:
The next step is to collect all kinds of information that you might want to store in
the database. Begin with the current information. Mull over the question you want
your database to answer, and it’ll help you decide which data needs to be recorded.
 Distribute the data into tables:
Once you’ve amassed all the necessary data items. The next step is to divide them
into the main entities or subject area
 Change data item into columns:
Data is segregated into tables, such that every data item becomes a field and is
shown as a column. For instance, a customer table might include fields like name,
address, e-mail address, and city.
 Identify primary keys:
The next step to improve your database design is to select a primary key for every
table. This primary key is a column or a set of columns that’s used to distinctively
pinpoint each row. For instance, in your customer table, the primary key could be
costumer ID. This will allow you to uniquely identify each row based on the
customer ID.
 Determine how tables are related:
After dividing data into tables, information needs to be brought together in a
meaningful manner. So, explore each table and determine how the data in one table
is linked with the data in another table. If need, you can add fields or form new
tables to simplify the relationship based on the types of information.

In this step, you’ll create one-to-one, one-to-many, and/or many-to-many
relationships between different table entries.
 Enchase your database design:
Now that you have all the required tables, fields, and relationships, the next step is
to refine your database design by creating and populating your tables with mockup
information.
 Implement the normalization rules:
The last step is to implement the normalization rules for your database design. It is
a systematic approach that removes redundancy and unwanted characteristics, such
as Insertion, Update, and Deletion irregularities.

Task 3

ER Diagram 1
Task 4
Relationship
One to One

1 1
1 1
1 1
1 1
One to Many
1 *
Librarian Category DetailBorrow
Librarian Student
RegistrationBorrow
Librarian Student
RegistrationBooks Borrow
Librarian
Books
PaymentCheck
Librarian
Author LibrarianBooksHas

1 *
* 1
Task 7
Test Case
Test Case ID DC_01 Test Case
Description
Test the library
management system
Created By Sahan Wijebandara Reviewed By Books
QA Tester’s Log Review comment
from book borrow
Version 1.0
Student
Registration
Category Details Select

Testers Name Date Tested
Test Case
Testcase 1
Reference
Naeem, T. (2021, April 30). Database Design - Overview, Importance, and Techniques. Astera.
https://www.astera.com/type/blog/all-you-need-to-know-about-database-design/
Steps in Database design and implementation. (2018, October 10). SQL.
https://economagically.blogspot.com/2018/10/steps-in-database-design-and.html

J. (2020, December 12). 5 Best Books to learn SQL and Database design in 2021. Medium.
https://medium.com/javarevisited/5-best-books-to-learn-sql-and-database-design-for-
programmers-and-developers-1e7839df2f3e
Relational Database Design. (2019). DBMS.
https://www3.ntu.edu.sg/home/ehchua/programming/sql/Relational_Database_Design.htm
l