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
<to be filled by the student>
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
“TRAVEL CLASSICS” travels
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
“Travel Classics” (TC) is a newly establish company in tourism industry. Company is planning
to launch a website to facilitate travelers to find necessary information regarding travel lodging
facilities and places of interest.
Company is planning to have two public user groups as travelers and hotels. Hotels have to
register
with site and one logging will be given for the hotel. Travel Classics is providing a web page for
each
hotel and through hotel logging hotel users can add information about the hotel. New website is
planning to provide search function to search hotel based on country, city and street. Hotel might
have many additional facilities such as swimming pool, gymnasium etc.. Travel Classics has
defined set of common facilities and every hotel has to use these define facilities. If a hotel wants
to add new facility which is not available in the given list they have to make a request and TB
will add to the system.
Every hotel has many accommodation options and should specify how many rooms are available
under each category. Same option might be available on many hotels. Under each category there
can be many related information such as description, conditions and also price of a room will
differ
from room to room and hotel to hotel.
Travelers can register by providing necessary information. Travelers have to add their
information such as name, addresses, email, country …etc. and for each traveler unique
reference id is given. Further they can add reports of their travels to a specific country or a
location. Once they register in the website, they can make reservations for destinations. Travelers
3
could add report on their travelling experiences to the website. This report might contain many
information on particular destinations and there should be small description and a title. Other
could view these posts.
Travel Classics website is also providing function to make reservation for a hotel room for a
particular duration. One traveler could reserve multiple rooms as well and should add no of
guests. Information regarding each reservation should be kept in the system. For each
reservation, unique reservation id is generated. Only the registered travelers can make
reservation for a particular hotel and they have to pay 10% at the reservation. All transactions
should be log with unique transaction ID, date and time.
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) (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 upto 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.
4
7. Write SQL queries for below requirements. ( LO3)
Practical Implementation and Demonstration (10 Marks)
List of Travelers of a particular location
Sum of transactions received with in a given duration of a particular hotel
List of hotels and its room availability
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)
5
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 upto 3rd Normal Form
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
TASK 9 Well-structured Documentation with proper formatting
styles and Harvard Referencing System is used for
6
Referencing and in-text citation
7
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
Task-2 contains 10 marks
Marks
Criteria
Out of 10
8
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
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
9
Good
7-5
Clear identification of Entities, Attributes and Relationships
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
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
10
Good
Average Design
Clear identification of Entities, Attributes and
Relationships 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
Task- 5 contains 15 marks
Criteria Marks
Out of 15
Excellent
Appropriate use of more sophisticated Normalization from upto
3NF 15-12
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
11
Mistakes including enough database design objects (simple
design)
Fail
3-0
Provide a poor Normalization with mistakes
Not relevant to database design objects
12
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
13
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
14
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
15
This submission will be
assessed as follows Total marks Marks obtained by
Allocated the student for the
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, 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.
16
