Assignment Cover Sheet

Qualification Module Number and Title

HD in Software Engineering/HD in Network
Technology and Cybersecurity CSE4005 - Database Design Development
Student Name & No. Assessor

Hand out date Submission Date

Assessment type Duration/Length of Weighting of Assessment

Reports 3000 Words Assessment Type 100%
End of the Module

Learner declaration

I 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
“Silver Coin” Bank
Student:
Assessor:
Assignment:

Feedback:

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

“Silver Coin” is a well-known financial institution. They run two main banks locally and globally.
Each bank has a name, code and address.
Bank has several branches. Each branch is identified by branch ID, branch name, contact number,
and address.
There are several regional managers to manage branches. Each regional manager has an employee
ID, name, address, email and phone number. Two or more managers manage several branches.
Branches facilitate financial support to the customers mainly by handling accounts and offering
loans.
Customers need to provide their valid name, NIC, phone number and address in order to register
with a branch. One customer can have one or more accounts in the same branch as well as in
different branches.
Accounts are identified by account no, and account type. Any customer can check their available
account balance by providing their unique credentials. Two or more customers can share the same
account such as joint accounts.
Customer also can avail loans. Loans are identified by loan ID, and loan type. There is a specific
amount and interest rate for each loan. One customer can avail several loans from one branch but
not from different branches.
“Silver Coin” is currently planning to implement a database management system to achieve a
better service.
Students can add any functionality which will enhance the system and make the proposed
solution more comprehensive.

3
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) (10 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. Normalize Schema up to 3NF. (Clear Show the Steps) (LO2) (15 Marks)

6. Create the database using SQL server. (LO3) (15 Marks)

• NOTE: Make sure to enter at least 15 records for each table.

7. Write SQL queries for below requirements. (LO3)

Practical Implementation and Demonstration (10 Marks)
• List of account details hold by a branch
• List of loans availed by a specific customer
• List of accounts hold by a specific customer

8. Provide the test plan, test strategy and proper test cases. (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)

4
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 Normalize Schema up to 3rd Normal Form

TASK 6 Create Database using SQL server

TASK 7 Produce SQL queries for manipulation functions

TASK 8 Correct Test plan and test cases with all test documents

TASK 9 Well-structured Documentation with proper formatting

styles and Harvard Referencing System is used for
Referencing and in-text citation

5
 Marking Scheme

Task-1 contains 10 marks

Marks
Criteria
Out of 15

Excellent
• Well explained about the data model. Critically compared different

• 5 data models. Well explained why the older data models are being 10-8
• replaced by new data models

Good
• Explained the data model. Compared minimum 5 data models. 7-5
• Provided proper reasons to replace the older data models
Pass
• Explained the data model. Compared minimum 3 data models. Not 5-3
• provided enough/Proper reasons to replace the older data models

Fail
• Not explained the data model. Not compared enough data models
• Not given proper reasons for the replacement. 2-0

6
Task-2 contains 10 marks

Marks

Criteria
Out of 10

Excellent
• Design Well Analyzed and briefly explained the different
10-8
approaches to database design with proper examples and
figures also.
Good
• Analyzed the different approaches using enough features.
7-5
• Provided the proper explanation with examples

Pass
• Analyzed the different approaches. Not provided the proper 4-2
explanation.
Fail
• Not properly analyzed the different approaches.
1-0
• Not explain the different approaches

7
Task-3 contains 10 marks

Diagram should be evaluated according to the following criteria.

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

Good

•Clear identification of Entities, Attributes and Relationships 7-5

• Correct use of ERD notations with minor mistakes

Pass
• Develop a logical database design.
• Entity Relationship Diagram
4-3
• Identification of correct Entities
• Identification of correct Attributes and Relationships

Poor
• Poor logical database design.
• Include incorrect Entities
• Include incorrect Attributes and Relationships 2-0

8
 • Not used proper /standard symbols

Task-4 contains 10 marks

Marks
Criteria
Out of 10
Excellent
• Excellent Design
• Relational Schema with proper standard symbols
• Proper theories and techniques need to apply in Relational
schema 10-8

• Relational Schema for ERD state the primary key foreign key
concept correctly

Good
• Average Design
• Clear identification of Entities, Attributes and
Relationships 6-7 7-6
• Correct use of Relational Schema notations with
minor mistakes

Pass
• Relational Schema
•Identification of correct Entities
• Identification of correct Attributes and Relationships 5-3

Fail
• In correct Relational Schema
• Not identified the correct Entities Attributes and 2-0
Relationships

9
Task- 5 contains 15 marks

Criteria Marks
Out of 15

Excellent
• Appropriate use of more sophisticated Normalization from up to
15-12
3NF
• Normalization design being proposed to facilitate requirements
• Provided the relevant assumptions

Good 11-8

• Make a good attempt to follow 2NF

• Provide More accurate normalization form
Pass
• Provide 1NF Design with minor 7-4

Mistakes including enough database design objects (simple

design)

Fail
3-0
• Provide a poor Normalization with mistakes
• Not relevant to database design objects

10
Task- 6 contains 15 marks

Criteria Marks
Out of 15
Excellent
• Appropriate use of more sophisticated database features (e.g.
use of stored procedures / functions / triggers to implement
15-13
business rules)
• Database design being proposed to facilitate requirements
• Provided the relevant assumptions

Good
• Make a good attempt to follow the Relational Database Model 12-9

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

11
 Task 7 -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-8 contains 10 marks

Criteria Marks
Out of 10

Excellent
• Detailed Test Plan 10 - 8
• Provided the Test Strategy and Proper test cases and test data

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 2-0
• Not provided proper test cases and Not provided proper test data

12
Task- 9 contains 10 marks

Criteria Marks
Out of 10

Excellent 10-8
• Professional standard of documentation
• with screen shots & good explanation
• Harvard Referencing System is used for Referencing and in-
text citation.

Good `7-5
• High standard of documentation with screen shots & average
explanations

4-3
Pass
• Acceptable standard of documentation with poor explanations

Fail
• Not followed a proper academic style, 2-0
• Not provided the referencing /provided in
• correct citation.

Final Grading criteria:

Marks Final Grade

>=70 Distinction
69-55 Merit
54-40 Pass
<40 Fail

13
 Marks obtained by
This submission will be Total marks
the student for the
assessed as follows Allocated
answer provided
TASK 1 10
TASK 2 10
TASK 3 10
TASK 4 10
TASK 5 15
TASK 6 15
TASK 7 10
TASK 8 10
TASK 9 10
TOTAL 100

Guidelines for the report format

Paper - A4
Margins - 1.5” left, 1” right, 1” top and 1” bottom
Page numbers -bottom, right
Line spacing - 1.5
Word count - 3000
Font face - Times New Roman
Headings - 14pt, Bold
Normal - 12pt

Referencing and in-text citation should be done strictly using Harvard Referencing System.

14