Assignment Brief

BTEC Level 4-5 HNC/HND Diploma (QCF)

To be filled by the Learner

Name of the Learner :

Edexcel No : Centre No : Batch:

Date of Submission :

Unit Assessment Information

Qualification : HND in Computing and Systems Development

Unit Code & Title : Unit 33 - Data Analysis and Design

Assessment Title & No’s : Database solution for Naomi Villa (No 1 of 1)

Learning outcomes and grading opportunities:

LO 01:Understand data models and database technologies
Learning Outcomes LO1.1 LO1.2 LO1.3
LO 02: Be able to design and implement relational database systems
Learning Outcomes LO2.1 LO2.2 LO2.3
LO 03: Be able to use manipulation and querying tools
Learning Outcomes LO3.1 LO3.2 LO3.3
LO 04: Be able to test and document relational database systems.
Learning Outcomes LO4.1 LO4.2 LO4.3 LO4.4 LO4.5

Merit and Distinction Descriptor

M1 M2 M3 D1 D2 D3

Assessor : Mr. Nisala B Internal Examiner (IE) : Miss. Shahana

Nadarajah

Date Reviewed : Date of IE :

Ronica Coorey DAD Assignment no 33 Page 7 of 99

 Statement of Originality and Student Declaration

I hereby, declare that I know what plagiarism entails, namely to use another’s work and
to present it as my own without attributing the sources in the correct way. I further
understand what it means to copy another’s work.

1. I know that plagiarism is a punishable offence because it constitutes theft.

2. I understand the plagiarism and copying policy of the Edexcel UK.
3. I know what the consequences will be if I plagiaries or copy another’s work in
any of the assignments for this program.
4. I declare therefore that all work presented by me for every aspects of my program,
will be my own, and where I have made use of another’s work, I will attribute the
source in the correct way.
5. I acknowledge that the attachment of this document signed or not, constitutes a
binding agreement between myself and Edexcel UK.
6. I understand that my assignment will not be considered as submitted if this
document is not attached to the attached.

Student’s Signature: ……………………… Date:

Ronica Coorey DAD Assignment no 33 Page 7 of 99

 Case study
Naomi Beach Villa

Naomi Villa is a five star hotel situated in the southern border of Sri Lanka, awarded as
the best of its category by the president, several times.
Naomi Villa has 5 categories of rooms namely Standard, Superior, Deluxe, Suits and
Penthouses. All five categories are offered in either of two types “Garden View” or
“Ocean View”. Standard, Superior and Deluxe categories can be ordered in any of three
bed sizes, known as “King” best suited for couples, “Queen” suited for Singles, or
“Twins” which is two separate single beds for travel partners. Suits have “King” and
“Twin Set” size beds where Penthouses can only be ordered with “King” size beds.
Room bed configuration and the view are native to the room and cannot be changed as
desired. The five different room categories are priced at a variation, but within a
category, the price remains the same regardless of the bed type and size or view.
Naomi Villa has several types of Clients, collectively known as “Guests”. A Guest can be
either “Direct” who booked the hotel directly, “repeat” who has visited another hotel
before or “Agent” who booked through third party travel agents. Different client types are
offered rooms at different prices.
Customers willing to make a stay in the hotel must first make an inquiry, inquiries are
recorded for further follow up and promotional purposes. After the inquiry, customer can
make an advance payment, which makes the inquiry a confirmed booking. While making
a booking, the customer has to specify the preferred room type, view, bed size, stay
period, etc.
Clients are also free to select one of any of the three meal plans during reservation. The
meal plans include “BB” where breakfast is included in the room bill, “HB” where
breakfast and Dinner is included and “FB” where all three meals are included.
Upon arrival, the Guest card is filled, and filed for the duration of stay, and archived after
the departure of the client. If the client visits the hotel again, the information in the
archived guest card is used to create a reservation for the new stay. Guest card collects
the Customer Name(s), Birthday(s), ID Number(s), Address(s) and Phone Number(s).
After the registration, the customer is escorted into his/her room.
Naomi villa has a selection of restaurants, bars, sports, tour and healthcare treatment
facilities which the guests can enjoy during his/her stay. Each facility requires a prior
booking, for example, a customer can book a romantic dinner for two, at the special roof
top restaurant named “temptations” and request for jasmine scented candles. Charges for
such utilization are credited to the customer’s “room account” which the Guest can pay
during his “Check-out” either by cash or credit card.
After the check-out, guest records including his personal details, preference and
information related to his stay are archived along with the Guest card, which can be
called upon to verify a re-visiting customer.

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Task 1

1.1. Prepare a power point presentation to critically compare and contrast

different data models and provide suitable justifications for why relational
model best fits the scenario (LO.1.1)
1.2. Discuss the benefits and limitations of different database technologies
such as distributed database, data warehouse, etc. (LO.1.2) (M2.1)(M 2.3)
1.3. Analyze different approaches to database design(LO 1.3)

Task 2

2.1. Draw an ER diagram for the above scenario. Make sure to indicate
primary keys, cardinality constraints, weak entities (if any), and participation
constraints.
2.2. List any assumptions and extra constraints which cannot be captured by
the ER diagram.
2.3. For each entity set and relationship, write a short description in plain
English of what it represents or models.
2.4. Translate the ER diagram in Relational Schemas. (LO 2.1)
2.5. Translate the ER diagram into relational database tables (give the SQL
DDL statements). (LO 2.2)
2.6. Provide evidence of the use of a suitable IDE to create a simple interface
to insert, update and delete data in the database (LO 2.3)

Task 3

3.1. Explain and provide evidence on the benefits gained when making use of
DML (LO 3.1) (M 1.2)
3.2. After creating the tables, execute the following SQL queries on your
database.
For each of the problems show:
• Your SQL query
• The result you obtained (LO 3.2)

Ronica Coorey DAD Assignment no 33 Page 7 of 99

 a. Display the number of “Superior” rooms booked, facing “Garden” with
“Twin Beds”.
b. Filter the customers who have made prior booking for special facilities.
c. Delete the records of customers who have booked ‘Deluxe’ rooms.
d. Write an update query to update a particular record in the database

3.3. How meaningful data has been extracted through the use of query tools.
Explain with examples. (LO 3.3)
3.4. Provide suitable test cases and a test plan to test the database.(LO 4.1)(LO
4.2)
3.5. Demonstrate how MS SQL effectively supporting to the designer when
they implement the database through the documentation. (User screen shots)
(LO 4.3)
3.6. Explain how verification and validation are addressed in your database
using stored procedures.(LO 4.4)
3.7. Discuss the security policies relevant to any database management system.
(LO4.5)

Ronica Coorey DAD Assignment no 33 Page 7 of 99

 Observation Sheet

Activit Activity Learning Date Feedback

y Outcome (Pass/ Redo)
No (LO)
1 Insert data to the tables LO4.1

2 Data manipulations (Update/Delete) LO4.1

4 Implement user privileges LO4.5

5 Execute triggers and stored procedures D3.5

LO 4.4

6 Test the database with a test plan LO4.2

Comments:

Assessor Name :

Assessor Signature:

Possible
Outcomes/Criteria for PASS Page Feedback
evidence

Ronica Coorey DAD Assignment no 33 Page 7 of 99

LO1- Understand data models and database technologies
1.1 critically compare different data models Task 1.1
and schemas
1.2 critically discuss the benefits and Task 1.2
limitations of different database technologies
1.3 analyze different approaches to database Task 1.3
design
LO2 - Be able to design and implement relational database systems
2.1 design a relational database system to Task 2.1
meet a given requirement – 2.4

2.2 build a relational database system based Task 2.5

on a prepared design
2.3 apply a range of database tools and Task 2.6
techniques to enhance the user interface
LO3 -Be able to use manipulation and querying tools
3.1 explain the benefits of using Task 3.1
manipulation and query tools in a relational
database system
3.2 implement a query language into the Task 3.2
relational database system
3.3 critically evaluate how meaningful data Task 3.3
has been
extracted through the use of query tools

Ronica Coorey DAD Assignment no 33 Page 7 of 99

LO4 - Be able to test and document relational database systems
4.1 critically review and test a relational Task
3.4
database system
4.2 create documentation to support the Task
3.4
implementation and testing of a relational
database system
4.3 create user documentation for a developed Task
relational database system 3.5
4.4 explain how verification and validation Task
has been addressed 3.6

4.5 explain how control mechanisms have Task

3.7
been used.

Grade Descriptor for MERIT Possible evidence Feedback

M1 Identify and apply strategies
to find appropriate solutions
Use join queries to
M1.2 complex problems with more than bring summarized
one variable have been
Explored records from two
different tables.
M2 Select / design appropriate
methods / techniques Proper use of Harvard
M2.1relevant theories and techniques referencing.
have been applied
M2.3 a range of sources of
information has been used

M3 Present and communicate Documentation is

appropriate findings well structured
adhering to the
M3.3 A range of methods of formatting guidelines
presentation have been used and with non-overlapping
technical language has been facts.
accurately used Data provided are
accurate, reliable and
consistent

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Grade Descriptor for DISTINCTION Possible evidence Feedback
D1 Use critical reflection to Report: shown in the
evaluate own work and self-reflection
justify valid conclusions section

D1.3 Self-criticism of approach has Good conclusion

taken place with suggestions for
further improvement

D1.4 Realistic improvements have

been proposed against defined
characteristics for subject

D2 Take responsibility for Gantt chart must be

managing and organising provided at the
activities appendix section and
submit the work on
D2.3 Activities have been managed time.

D3 Demonstrate convergent / Evidences on Use of

lateral / creative thinking triggers, views and
stored procedure.
D3.5 Innovation and creative
thoughts have been applied Whether the queries
are correct, the
number of tables
they reference, and
the running time.

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Strengths: Weaknesses:

Future Improvements & Assessor Comment:

Assessor: Signature:
Date: ____/____/______

Internal Verifier’s Comments:

Internal Verifier: Signature:

Date: ____/____/______

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Table of Contents
Acknowledgment.........................................................................................................14

1.1 Data Models...........................................................................................................16

1.2 Types of Database Technologies –........................................................................22

1.2.2 Benefits and Limitations of Data Mining............................................................25

1.3 Analyze different approaches to database design..................................................26

1.3.1 Different Approaches..........................................................................................26

1.3.2.1 Differences between Top down and Bottom Up..............................................27

1.3.3 Database Planning...............................................................................................28

1.3.4 Systems Definition..............................................................................................28

1.3.5 Requirements Collection and Analysis...............................................................28

1.3.6 Database Design..................................................................................................29

1.3.7 Database Management System Selection............................................................29

1.3.10 Implementation.................................................................................................30

1.3.11 Data Conversion and Loading...........................................................................30

1.3.12 Testing...............................................................................................................30

1.3.13 Operational Maintenance..................................................................................30

2.1 ERD Diagram of Case study..................................................................................31

2.2 Domain Integrity constraints..................................................................................32

2.3 Short Description of what it represents..................................................................33

Facilities details............................................................................................................35

2.4 Translate the ER diagram in Relational Schemas..................................................37

2.5 Translate the ER diagram into relational database tables......................................38

2.6 Interfaces................................................................................................................40

3.1 Explain and provide evidence on the benefits gained when making use of DML.51

Ronica Coorey DAD Assignment no 33 Page 7 of 99

3.1.1 Benefits of using Query tools..............................................................................52

3.2 Display the number of “Superior” rooms booked, facing “Garden”.....................53

3.2.1 Filter the customers who have made prior booking for special facilities...........54

3.2.2 Delete the records of customers who have booked ‘Deluxe’ rooms...................55

3.2.3 Write an update query to update a particular record in the database..................56

3.3 Explanation of query tools.....................................................................................57

Examples for Query Tools...........................................................................................58

3.4 Provide suitable test cases and a test plan to test the database...............................60

Non-functional testing..................................................................................................63

3.4.1 Test Plan for Naomi Beach Villa Database.........................................................64

Test Case 1..................................................................................................................65

3.4.1 Actual Test Case.................................................................................................65

3.4.2 Test Case 3..........................................................................................................66

3.5 Sql Server Demonstration......................................................................................67

3.6 Verification............................................................................................................74

3.6.1 Stored Procedure – In Output Variable...............................................................75

3.7 Triggers..................................................................................................................79

Gantt chart....................................................................................................................83

Self Criticism...............................................................................................................84

Summary......................................................................................................................85

Indexes.........................................................................................................................86

Glossary........................................................................................................................87

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Table of Figure

Figure 1 Data Models...................................................................................................................16

Figure 2 Type of Data Models......................................................................................................16
Figure 3 Flat file Models..............................................................................................................17
Figure 4 Hierarchical Model.........................................................................................................17
Figure 5 Network Model...............................................................................................................18
Figure 6 Network contd................................................................................................................18
Figure 7 Relational Model............................................................................................................19
Figure 8 Relational Model contd..................................................................................................19
Figure 9 Object Oriented Model...................................................................................................20
Figure 10 Object Oriented Model contd.......................................................................................20
Figure 11 why relational model is popular....................................................................................21
Figure 12 Ease of data access in relational model.........................................................................21
Figure 13 Database Technologies.................................................................................................22
Figure 14 Benefits of Distributed Database..................................................................................22
Figure 15 Limitations of Database................................................................................................23
Figure 16 Advantages of Data Warehouse....................................................................................23
Figure 17 Advantages of Data Warehouse contd..........................................................................24
Figure 18 Disadvantages of Data Warehouse...............................................................................24
Figure 19 Advantages of Data Mining..........................................................................................25
Figure 20 Disadvantages of Data Mining.....................................................................................25
Figure 21 Database Design...........................................................................................................26
Figure 22 ERD Diagram...............................................................................................................31
Figure 23 Relational Schemas......................................................................................................37
Figure 24 Log In...........................................................................................................................40
Figure 25 Main Form of Naomi Beach Villa................................................................................40
Figure 26 Guest Interface.............................................................................................................41
Figure 27 Member Interface.........................................................................................................43
Figure 28 Booking Interface.........................................................................................................45
Figure 29 Facilities Interface........................................................................................................47
Figure 30 Room Interface.............................................................................................................49
Figure 31 Superior Sql Query.......................................................................................................53

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Figure 32 Prior Booking Sql Query..............................................................................................54
Figure 33 Deletion of Deluxe rooms in Sql Query........................................................................55
Figure 34 Update of Guest in Sql Query.......................................................................................56
Figure 35 Guest/Member..............................................................................................................58
Figure 36 Facilities/Guest.............................................................................................................58
Figure 37 Room, Guest , Booking................................................................................................59
Figure 38 Member, Facilities.......................................................................................................59
Figure 39 Connect to SQL Server.................................................................................................67
Figure 40 Management Studio Express Work Area......................................................................68
Figure 41 New Database Form.....................................................................................................69
Figure 42 Creating tables..............................................................................................................70
Figure 43 Script Wizard................................................................................................................71
Figure 44 Procedure - Query........................................................................................................72
Figure 45 in Output Variable........................................................................................................73
Figure 46 Create Procedure..........................................................................................................73
Figure 47 Stored Procedure Create a table....................................................................................74
Figure 48 After Update Trigger....................................................................................................77
Figure 49 After Delete Trigger.....................................................................................................78
Figure 50 After Insert Trigger.......................................................................................................78
Figure 51 create view....................................................................................................................79
Figure 52 Update view..................................................................................................................79
Figure 53 Drop view.....................................................................................................................80
Figure 54 Gantt chart....................................................................................................................82

List of Tables

Table 1 Guest details....................................................................................................34

Table 2 Member details................................................................................................35
Table 3 Facilities details...............................................................................................36
Table 4 Booking details...............................................................................................36
Table 5 Room details...................................................................................................37
Table 6 Test Case 1......................................................................................................62
Table 7 Test Case 2......................................................................................................63

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Table 8 Test Case 3......................................................................................................64
Acknowledgment

It is of greatest and extreme gratitude and with the help and guideline of one person, our
wonderful lecturer Miss Malsha. Therefore the completion of this assignment gives us
much pleasure. Our sincere gratitude to Miss Malsha and to the whole administration of
Esoft for giving us a good guideline in helping us in our assignments.

You have guided us in every possible way and we are grateful. From a certain point of
view, we can always ask anything from you and we are certain that you will certainly
help us in every possible way.

Her valuable guidance and advice has inspired us greatly to work in this project. Thus,
her willingness to motivate us has contributed tremendously to the completion of this
assignment.

It is certainly a great privilege to be learning under your guidance, no other words to

describe you, such a wonderful, kind hearted and loving lecturer.

To end all I have to say or mention is about a famous quote about Education –
“Education is the most powerful weapon which you can use to change the world.

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Task 1

Prepare a power point presentation to critically compare and contrast different data
models and provide suitable justifications for why relational model best fits the
scenario

1.1 Data Models

Figure 1 Data Models

Ronica Coorey DAD Assignment no 33 Page 7 of 99

 Fig
ure 2 Type of Data Models

Figure 3 Flat file Models

Ronica Coorey DAD Assignment no 33 Page 7 of 99

 Fig
ure 4 Hierarchical Model

Figure 5 Network Model

Ronica Coorey DAD Assignment no 33 Page 7 of 99

 Fig
ure 6 Network contd

Figure 7 Relational Model

Ronica Coorey DAD Assignment no 33 Page 7 of 99

 Fig
ure 8 Relational Model contd

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Figure 9 Object Oriented Model

Fig
ure 10 Object Oriented Model contd

Figure 11 why relational model is popular

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Figure 12 Ease of data access in relational model

Discuss the benefits and limitations of different database technologies such as

distributed database, data warehouse, etc.

Ronica Coorey DAD Assignment no 33 Page 7 of 99

1.2 Types of Database Technologies – Benefits and Limitations of Distributed
Database

Figure 13 Database Technologies

Figure 14 Benefits of Distributed Database

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Figure 15 Limitations of Database

Fig
ure 16 Advantages of Data Warehouse

Ronica Coorey DAD Assignment no 33 Page 7 of 99

1.2.1Benefits and Limitations of Data Warehouse

Figure 17 Advantages of Data Warehouse contd

Fig
ure 18 Disadvantages of Data Warehouse

Ronica Coorey DAD Assignment no 33 Page 7 of 99

1.2.2 Benefits and Limitations of Data Mining

Figure 19 Advantages of Data Mining

Figure 20 Disadvantages of Data Mining

Ronica Coorey DAD Assignment no 33 Page 7 of 99

1.3 Analyze different approaches to database design
Database Design

Database design is the process of producing a detailed data model of a database. This data
model contains all the needed logical and physical design choices and physical storage
parameters that are needed to generate a design in a data definition language, which can
be used to create a database.

Source (www.tutorialpoint.com)

1.3.1 Different Approaches

A database is usually a fundamental component of the information system, especially in
business oriented systems. Thus database design is part of system development.

Diagram of Database Design

Figure 21 Database Design

Source (www.amk.fi)

Ronica Coorey DAD Assignment no 33 Page 7 of 99

1.3.2 Approaches to Database Design
Top down approach

The top-down approach, also known as step-wise design, essentially breaks a system or
model down into component sub-systems, each of which may further broken down
further. However, no first-level system are defined – you won’t describe a for loop, or
define the attributes of an object in the top-down approach. Systems end up as a series of
‘black boxes’; components that have specific inputs and outputs, but no definite internal
structure.

Bottom Up

Bottom-up design consists of defining and coding the very basic, definite parts of the
system to be designed, then linking these parts together to form the whole. It also
identifies the data elements (items) and then groups them together in data sets. In other
words, it first defines attributes, and then groups them to form entities. Bottom up begins
with the specific details and moves up to the general. It identifies the data elements and
then groups them together in data sets.

1.3.2.1 Differences between Top down and Bottom Up

Top Down

 It starts from abstract to finally achieving a solid design

 This approach is most commonly employed

 It starts with the top level module and progresses downward to the lowest level
module

 It is easy to visualize and provides sense of completeness

Bottom Up

 It’s just the reverse as it begins with the concrete design to get the abstract entity

 It proceeds with the design of lowest level module

Ronica Coorey DAD Assignment no 33 Page 7 of 99

  It has the advantages of solid business logic and the ability to write good unit test
and to ease which changes can be managed and modified

1.3.3 Database Planning

The database planning includes the activities that allow the stages of the database system
development lifecycle to be realized as efficiently and effectively as possible. This phase
must be integrated with the overall Information System strategy of the organization.

The very first step in database planning is to define the mission statement and objectives
for the database system. It is the major aim, the purpose and the supported tasks of the
database system which are known as the resources of the database system

1.3.4 Systems Definition

In the systems definition phase, the scope and boundaries of the database application are
described. This description includes

 Links with the other information systems of the organization

 What the planned system is going to do now and in the future
 Who the users are now and in the future

The major user views are also described. I.e. what is required of a database system from
the perspectives of particular job roles or enterprise application areas?

1.3.5 Requirements Collection and Analysis

During the requirements collection and analysis phase, the collection and analysis of the
information about the part of the enterprise to be served by the database are completed.
The results may include e.g.:

 The description of the data that is used or generated

 The details how the data is to be used or generated
 Any additional requirements needed for the new database

Source (www.amk.fi)

Ronica Coorey DAD Assignment no 33 Page 7 of 99

1.3.6 Database Design
The database design phase is divided into three steps:

Conceptual database design

Logical database design

Physical database design

In the conceptual design phase, the model of the data to be used is to be independent of
all physical considerations which are to be constructed. The model is based on the
requirements specification

In the logical database design phase, the model of the data to be used is based on a
specific data model, but independent of a particular database management system is
constructed. This is based on the target data model for the database e.g. relational data
model.
In the physical database design phase, the description of the implementation of the
database on secondary storage is created. The base relations, indexes, integrity
constraints, security, etc. are defined using the SQL language.

1.3.7 Database Management System Selection

This is an optional phase. When there is a need for a new database management system
(DBMS), this phase is done. DBMS means a database system like Access, SQL Server,
MYSQL, and Oracle

In this phase the criteria for the new DBMS are defined. Then several products are
evaluated according to the criteria. Finally the recommendation for the selection is
decided.

1.3.8 Application Design

In the application design phase, the design of the user interface and the application
programs that use and process the database are defined and designed. It includes database
concerns such as interfacing SQL with traditional programming languages

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Source (www.amk.fi, Craig s.mullins)

1.3.9 Prototyping

The purpose of a prototype is to allow the users to use the prototype to identify the
features of the system using a computer.

There are horizontal and vertical prototypes. A horizontal prototype has many features
(e.g. user interfaces) but they are not in a working condition. A vertical prototype has
very few features but they are in perfect working condition.

1.3.10 Implementation
During the implementation phase, the physical realizations of the database and
application designs are to be done. This is the programming phase of the systems
development.

1.3.11 Data Conversion and Loading

This phase is needed when a new database is replacing an old system. During this phase
the existing data will be transferred into the new database.

1.3.12 Testing
Before the new system is going to live, it should be thoroughly tested. The goal of testing
is to find errors! The goal is not to prove the software is working well.

1.3.13 Operational Maintenance

The operational maintenance is the process of monitoring and maintaining the database
system.

Monitoring means that the performance of the system is observed. If the performance of
the system falls below an acceptable level, tuning or reorganization of the database may
be required

Source (www.amk.fi)

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Task 2 Draw an ER diagram for the above scenario. Make sure to indicate primary
keys, cardinality constraints, weak entities (if any) and participation constraints.

2.1 ERD Diagram of Case study

Figure 22 ERD Diagram

Ronica Coorey DAD Assignment no 33 Page 7 of 99

List any assumptions and extra constraints which cannot be captured by the ER
diagram.

2.2 Domain Integrity constraints

 NOT NULL
User is restricted by entering null value to column by giving not null in the attribute.

 UNIQUE
User is prohibited to insert duplicate values.

 DEFAULT
A default value can be specified to an attribute

 CHECK
Use of check clause is to ensure that attribute values satisfy specified condition.

Entity Integrity constraints

The entity integrity constraint states that primary keys can’t be null. There must be a
proper value in the primary key field. This is because the primary key value is used to
identify individual rows in a table. If there were null values for primary keys, it would
mean that we could not identify those rows. On the other hand, there can be null values
other than primary key fields. Null value means that one doesn’t know the value for that
field. Null value is different from zero value or space. The entity integrity constraints
assure that a specific row in a table can be identified.

Referential Integrity constraints

This is a relational database concept in which multiple tables share a relationship based
on the data stored in the tables, and that relationship must remain consistent. It is
violated when the relation to which a foreign key refers no longer exists.

Ronica Coorey DAD Assignment no 33 Page 7 of 99

  Update with referential integrity
 Insert with referential integrity
 Delete with referential integrity

For each entity set and relationship write a short description in plain English of
what it represents or models.

2.3 Short Description of what it represents

GUEST DETAILS

NAME DATA TYPE RELATIONSHIP DESCRIPTION

GUEST ID INTEGER PRIMARY KEY ID OF THE

GUEST

FNAME VARCHAR(20) NOT NULL FIRST NAME OF

THE GUEST

LAST NAME VARCHAR(20) NOT NULL LAST NAME OF

THE GUEST

NIC VARCHAR(20) NOT NULL IDENTITY OF

THE GUEST

DOB VARCHAR(20) NOT NULL BIRTH DATE OF

THE GUEST

TEL NO VARCHAR(20) NOT NULL CONTACT NO OF

THE GUEST

TYPE VARCHAR(20) NOT NULL TYPE OF GUEST

Table 1 Guest details

Source (created by user)

Ronica Coorey DAD Assignment no 33 Page 7 of 99

MEMBER DETAILS

NAME DATA TYPE RELATIONSHIP DESCRIPTION

MEMBER ID INTEGER PRIMARY KEY ID OF THE

MEMBER

NAME VARCHAR(20) NOT NULL NAME OF THE

MEMBER

DOB VARCHAR(20) NOT NULL DOB OF THE

MEMBER

NIC VARCHAR(20) NOT NULL IDENTITY OF

THE MEMBER

TEL NO VARCHAR(20) NOT NULL CONTACT NO OF

THE MEMBER

GUEST ID INTEGER FOREIGN KEY ID OF THE

GUEST

Table 2 Member details

Source (created by user)

Ronica Coorey DAD Assignment no 33 Page 7 of 99

FACILITIES DETAILS

NAME DATA TYPE RELATIONSHIP DESCRIPTION

GUEST ID INTEGER PRIMARY KEY ID OF THE

GUEST

FACILITY TYPE VARCHAR(20) NOT NULL WHAT KIND OF

FACILITY

DATE VARCHAR(20) NOT NULL DATE OF THE

CERTAIN
FACILITY

PAYMENT VARCHAR(20) NOT NULL PAYMENT FOR

FACILITIES
Table 3 Facilities details

BOOKING DETAILS

NAME DATA TYPES RELATIONSHIP DESCRIPTION

ROOM ID INTEGER PRIMARY KEY ID OF THE

ROOM

ADVANCE VARCHAR(20) NOT NULL PAYMENT IN

PAYMENT ADVANCE

MEAL PLAN VARCHAR(20) NOT NULL ALL TYPES OF

MEALS

DATE VARCHAR(20) NOT NULL DATE OF

BOOKING

DEPARTURE VARCHAR(20) NOT NULL DATE OF GUEST

DATE DEPARTURE

DURATION VARCHAR(20) NOT NULL DURATION OF

GUEST STAY
Table 4 Booking details

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Source (created by user)

Ronica Coorey DAD Assignment no 33 Page 7 of 99

ROOM DETAILS

NAME DATA TYPES RELATIONSHIP DESCRIPTION

ROOM ID INTEGER PRIMARY KEY ID OF THE ROOM

TYPE VARCHAR(20) NOT NULL TYPE OF ROOM

BED TYPE VARCHAR(20) NOT NULL TYPE OF BED

VIEW VARCHAR(20) NOT NULL TYPE OF VIEW

PRICE VARCHAR(20) NOT NULL PRICE OF THE

ROOM

Table 5 Room details

Source (created by user)

Ronica Coorey DAD Assignment no 33 Page 7 of 99

2.4 Translate the ER diagram in Relational Schemas.

Figure 23 Relational Schemas

Ronica Coorey DAD Assignment no 33 Page 7 of 99

2.5 Translate the ER diagram into relational database tables (give the SQL DDL
statements).

create table Guest(

GID integer,
fname varchar(10),
lname varchar(10),
DOB varchar(10),
NIC varchar (15),
TELno varchar (15),
GuestType varchar(10),
primary key (GID)

)
create table Booking(
BID integer,
Bdate varchar(10),
Duration varchar(10),
Mplans varchar(10),
Fpayment varchar(10),
Rpayment varchar(10),
Mpayment varchar (10),
Apayment varchar(10),
GID integer,
primary key (BID),
foreign key (GID) references Guest(GID)
)

create table Room(

RID integer,
Btype varchar(10),
Price varchar(10),
Rtype varchar(10),
Views varchar(10),

Ronica Coorey DAD Assignment no 33 Page 7 of 99

BID integer,
primary key (RID),
foreign key (BID) references Booking (BID)
)

create table Member(

MID integer,
fname varchar(10),
lname varchar(10),
DOB varchar (10),
NIC varchar (15),
GuestType varchar(10),
primary key (MID),
GID integer,
foreign key (GID) references Guest(GID)
)

create table Facilities(

FID integer,
Ftype varchar(10),
Duration varchar(10),
payments varchar (10),
GID integer,
primary key (FID),
foreign key (GID) references Guest(GID)

Ronica Coorey DAD Assignment no 33 Page 7 of 99

2.6 Interfaces

NAOMI BEACH VILLA

Figure 24 Log In

Figure 25 Main Form of Naomi Beach Villa

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Guest Interface

Figure 26 Guest Interface

Syntax for Insert

String sql = "insert into Guest (GID, fname, lname, DOB, NIC, TELno, GuestType)
values ('" + txtGuestID.Text + "','" + txtFname.Text + "','" + txtLname.Text + "','" +
txtDob.Text + "','" + txtNic.Text + "','" + txtTelno.Text + "','" + txtGuesttype.Text + "')";
SqlConnection con = new SqlConnection(c);
con.Open ();
SqlCommand cmd = new SqlCommand (sql, con);
cmd.ExecuteNonQuery ();
MessageBox.Show ("value is inserted successfully!!!");
con.Close ();

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Syntax for Delete

String sql = "delete from Guest where GID='" + txtGuestID.Text + "')";

SqlConnection con = new SqlConnection(c);
SqlCommand cmd = new SqlCommand (sql, con);
Try {
con.Open ();
cmd.ExecuteNonQuery ();
MessageBox.Show ("value is deleted successfully!!!") ;}
Catch (Exception ex) {
MessageBox.Show (ex. Message .ToString (),"ERROR");
}
Finally
{
con.Close ();
}

Syntax for Update

String sql = "update Guest set Fname ='" + txtFname.Text + "','" + txtLname.Text + "','"
+ txtDob.Text + "','" + txtNic.Text + "','" + txtTelno.Text + "','" + txtGuesttype.Text + "',
Where GID ='" + txtGuestID + "')";
SqlConnection con = new SqlConnection(c);
SqlCommand cmd = new SqlCommand (sql, con);
con.Open ();
cmd.ExecuteNonQuery ();
MessageBox.Show ("value is updated successfully!!!");
con.Close ();

Member Interface

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Figure 27 Member Interface

Syntax for Insert

String sql = "insert into Member (MID, fname, lname, DOB, NIC, GuestType) values ('"
+ txtMemberID.Text + "','" + txtFname.Text + "','" + txtLname.Text + "','" + txtDob.Text
+ "','" + txtNic.Text + "','" + txtGuesttype.Text + "')";
SqlConnection con = new SqlConnection(c);
con.Open ();
SqlCommand cmd = new SqlCommand (sql, con);
cmd.ExecuteNonQuery ();
MessageBox.Show ("value is inserted successfully!!!");
con.Close ();

Syntax for Update

Ronica Coorey DAD Assignment no 33 Page 7 of 99

string sql = "update Member set Fname ='" + txtFname.Text + "','" + txtLname.Text +
"','" + txtDob.Text + "','" + txtNic.Text + "','" + txtGuesttype.Text + "'";
SqlConnection con = new SqlConnection(c);
SqlCommand cmd = new SqlCommand (sql, con);
con.Open ();
cmd.ExecuteNonQuery ();
MessageBox.Show ("value is updated successfully!!!");
con.Close ();

Syntax for Delete

String sql = "delete from Member where MID='" + txtMemberID.Text + "')";

SqlConnection con = new SqlConnection(c);
SqlCommand cmd = new SqlCommand (sql, con);
Try {
con.Open ();
cmd.ExecuteNonQuery ();
MessageBox.Show ("value is deleted successfully!!!");
}
Catch (Exception ex)
{
MessageBox.Show (ex. Message .ToString (),"ERROR");
}
finally
{
con.Close ();
}

Booking Interface

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Figure 28 Booking Interface

Syntax for Insert

String sql = "insert into

BOOKING(BID,Bdate,Duration,Mplans,Fpayment,Rpayment,Mpayment,Apayment,GID
) values('" + txtBookingID.Text + "','" + txtBdate.Text + "','" + txtDuration.Text + "','" +
txtMplans.Text + "','" + txtFpayment.Text + "','" + txtRpayment.Text + "','" +
txtMpayment.Text + "', '"+txtApayment.Text+"','"+txtGID.Text+"')";
SqlConnection con = new SqlConnection(c);
con.Open ();
SqlCommand cmd = new SqlCommand (sql, con);
cmd.ExecuteNonQuery ();
MessageBox.Show ("value is inserted successfully!!!");
con.Close ();

Syntax for Update

Ronica Coorey DAD Assignment no 33 Page 7 of 99

string sql = "update
BOOKING(BID,Bdate,Duration,Mplans,Fpayment,Rpayment,Mpayment,Apayment,GID
) values('" + txtBookingID.Text + "','" + txtBdate.Text + "','" + txtDuration.Text + "','" +
txtMplans.Text + "','" + txtFpayment.Text + "','" + txtRpayment.Text + "','" +
txtMpayment.Text + "', '" + txtApayment.Text + "','" + txtGID.Text + "')";
SqlConnection con = new SqlConnection(c);
SqlCommand cmd = new SqlCommand (sql, con);
con.Open ();
cmd.ExecuteNonQuery ();
MessageBox.Show ("value is updated successfully!!!");
con.Close ();

Syntax for Delete

string sql = "delete from Guest where GID='" + txtGID.Text + "'";

SqlConnection con = new SqlConnection(c);
SqlCommand cmd = new SqlCommand (sql, con);
Try
{
con.Open ();
cmd.ExecuteNonQuery ();
MessageBox.Show ("value is deleted successfully!!!");
}
Catch (Exception ex)
{
MessageBox.Show (ex.Message.ToString (), "ERROR");
}
finally
{
con.Close ();

Facilities Interface

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Figure 29 Facilities Interface

Syntax for Insert

String sql = "insert into FACILITIES (FID, Ftype, Duration, Payment, GID) values ('" +
txtFID.Text + "','" + txtFtype.Text + "','" + txtDuration.Text + "','" +txtPayment.Text +
"','" + txtGID.Text + "'";
SqlConnection con = new SqlConnection(c);
con.Open ();
SqlCommand cmd = new SqlCommand (sql, con);
cmd.ExecuteNonQuery ();
MessageBox.Show ("value is inserted successfully!!!");
con.Close ()

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Syntax for Delete

string sql = "delete from FACILITIES where FID='" + txtFID.Text + "'";

SqlConnection con = new SqlConnection(c);
SqlCommand cmd = new SqlCommand (sql, con);
Try
{
con.Open ();
cmd.ExecuteNonQuery ();
MessageBox.Show ("value is deleted successfully!!!");
}
Catch (Exception ex)
{
MessageBox.Show (ex.Message.ToString (), "ERROR");
}
Finally
{
con.Close ();
}
}

Syntax for Update

string sql = "Update FACILITIES set Ftype ='" + txtFtype.Text + "','" + txtDuration.Text
+ "','" + txtPayment.Text + "','" + txtGID.Text + "' where FID='" + txtFID.Text + "'";
SqlConnection con = new SqlConnection(c);
SqlCommand cmd = new SqlCommand (sql, con);
con.Open ();
cmd.ExecuteNonQuery ();
MessageBox.Show ("value is updated successfully!!!");
con.Close ();
Room Interface

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Figure 30 Room Interface

Syntax for Insert

String sql = "insert into Room (RID, Btype, Price, Rtype, Views, BID) values ('" +
txtRID.Text + "','" + txtBtype.Text + "','" + txtPrice.Text + "','" + txtRtype.Text + "','" +
txtViews.Text + "','" + txtBID.Text + "')";
SqlConnection con = new SqlConnection(c);
con.Open ();
SqlCommand cmd = new SqlCommand (sql, con);
cmd.ExecuteNonQuery ();
MessageBox.Show ("value is inserted successfully!!!");
con.Close ();

Syntax for Delete

Ronica Coorey DAD Assignment no 33 Page 7 of 99

string sql = "delete from Room where RID='" + txtRID.Text + "'";
SqlConnection con = new SqlConnection(c);
SqlCommand cmd = new SqlCommand (sql, con);
Try
{
con.Open ();
cmd.ExecuteNonQuery ();
MessageBox.Show ("value is deleted successfully!!!");
}
Catch (Exception ex)
{
MessageBox.Show (ex.Message.ToString (), "ERROR");
}
Finally
{
con.Close ();
}
}
Syntax for Update

string sql = "update Room type='" + txtBtype.Text + "','" + txtPrice.Text + "','" +

txtRtype.Text + "','" + txtViews.Text + "','" + txtBID.Text +"','"+ txtRID.Text + "')";
SqlConnection con = new SqlConnection(c);
SqlCommand cmd = new SqlCommand (sql, con);
con.Open ();
cmd.ExecuteNonQuery ();
MessageBox.Show ("value is updated successfully!!!");
con.Close ();

TASK 3

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Provide evidence of the use of a suitable IDE to create a simple interface to insert,
update and delete data in the database

3.1 Explain and provide evidence on the benefits gained when making use of DML
In a relational database system, it is considered as a database which appears to be nothing
more than a collection of tables, and it is based on three major aspects known as
structure, manipulation and integrity of data, whereas data manipulation is provided by a
set of algebraic or calculus operators, Data manipulation is known as a family of
computer languages which includes commands in permitting users to manipulate data in a
database. This kind of manipulation involves in inserting data into database tables, thus
retrieving existing data, deleting data from existing tables and modifying existing data.
Therefore DML is mostly incorporated in SQL databases. DML enhances efficient user
interaction with the system. Therefore the functional capability of DML is organized in
manipulation commands like SELECT, UPDATE, INSERT into and DELETE from as
mentioned below:

 SELECT: This command is used to retrieve rows from a table. The syntax is
SELECT [column name(s)] from [table name] where [conditions]. SELECT is the
most widely used DML command in SQL.
 UPDATE: This command modifies data of one or more records. An update command
syntax is UPDATE [table name] SET [column name = value] where [condition].

 INSERT: This command adds one or more records to a database table. The insert
command syntax is INSERT INTO [table name] [column(s)] VALUES [value(s)].

 DELETE: This command removes one or more records from a table according to
specified conditions. Delete command syntax is DELETE FROM [table name] where
[condition].
Source (www.techopedia.com, maria.db)

Ronica Coorey DAD Assignment no 33 Page 7 of 99

3.1.1 Benefits of using Query tools
The benefits that is mentioned about Query tools firstly is that different types of query
tools are been used nowadays. Some are known as SQL Server, MsAccess, Oracle,
MySQL etc. From this we realize that queries allows the users to extract relevant
information from a database, thus from the use of a query it helps to pre define the
categories of information that will be sought.

Queries are the primary mechanism for retrieving information from a database and
consist of questions presented to the database in a predefined format. Many database
management systems use the Structured Query Language (SQL) standard query format.
A database query is used to retrieve data from the database in a readable format
according to the user's request. There are some few advantages and there are as follows

 Storage space
While you are developing a database that is entirely based on data specifications.
There are no unnecessary bytes or characters stored in the SQL database. This saves
storage space.

 Efficient data retrieval

Queries can be used to retrieve large amounts of data from a database efficiently and
speedily.

 Database security
Security gets better because you can allow particular data to be stored in the SQL
database.

 Speed
The database is efficient and uses the correct character length and data types, query
speed will improve.

Source (www.tutorial point.com)

Ronica Coorey DAD Assignment no 33 Page 7 of 99

After creating the tables, execute the following SQL queries on your database.

For each of the problems show:

 Your SQL query

 The result you obtained

3.2 Display the number of “Superior” rooms booked, facing “Garden” with “Twin
Beds”.

Figure 31 Superior Sql Query

select count(RID)
from Room
where Views='Garden' and Btype='TwinBeds'
select * from Room

Ronica Coorey DAD Assignment no 33 Page 7 of 99

3.2.1 Filter the customers who have made prior booking for special facilities.

Figure 32 Prior Booking Sql Query

select *
from Facilities
where Ftype='prior'

Ronica Coorey DAD Assignment no 33 Page 7 of 99

3.2.2 Delete the records of customers who have booked ‘Deluxe’ rooms.

Figure 33 Deletion of Deluxe rooms in Sql Query

Delete
from Room
where Rtype='Deluxe'

Ronica Coorey DAD Assignment no 33 Page 7 of 99

3.2.3 Write an update query to update a particular record in the database.

Figure 34 Update of Guest in Sql Query

Update Guest set DOB='16/08/1997' where GID='13

Ronica Coorey DAD Assignment no 33 Page 7 of 99

How meaningful data has been extracted through the use of query tools. Explain
with examples.

3.3 Explanation of query tools

Although query tools generate operate by extracting data from a traditional database
engine such as SQL server. It refers to the fact that the data is stored and referenced
according to several dimensions.

They have changed, by making storage, retrieval and accuracy of data that is stored more
efficiently effective and reliable. It enables us to execute arbitrary SQL commands.

The approach would be to select features that meet some criteria or that are located in a
particular place. We can build SQL queries to select particular features or rows from the
source data.

For example, the ‘Select’ tool allows us to use a SQL query to make a new feature class
of features that are selected from an existing class. As we specify about query tools, they
help analyze the data in a database in which they provide query building editing and
summarizes functionalities

Examples

The most basic SELECT statement has two parts. They are what columns you want to
return and what table those columns come from.

 To retrieve all of the information about the entire Guest in the Add_New_Guest
table, we can use the asterisk (*) mark as a shortcut for all of the columns.

SELECT * FROM Add_New_Guest

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Examples for Query Tools

Figure 35 Guest/Member

Figure 36 Facilities/Guest

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Figure 37 Room, Guest , Booking

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Figure 38 Member, Facilities

3.4 Provide suitable test cases and a test plan to test the database

The 3 types of Database Testing are

 Structural Testing
 Functional Testing
 Non-functional Testing

Structural database testing

The structural data testing involves the validation of all those elements inside the data
repository that are used primarily for storage of data and which are not allowed to be
directly manipulated by the end users. The validation of the database servers is also a
very important consideration in these types of testing. The successful completion of this
phase by the testers involves mastery in SQL queries. It is also known as glass box
testing or white box testing, where it is an approach in which the tests are derived from

Ronica Coorey DAD Assignment no 33 Page 7 of 99

the knowledge of the software’s structure or internal implementation. The validation of
the database servers is also a very important consideration in these types of testing.

 Schema testing
 Database table, column testing
 Keys and indexes testing
 Stored procedures testing
 Trigger testing

Functional Testing

The Functional database testing as specified by the requirement specification needs to

ensure most of those transactions and operations as performed by the end users are
consistent with the requirement specifications. This particular process is the validation of
the field mappings from the end user viewpoint. In this particular scenario the tester
would perform an operation at the data base level and then would navigate to the relevant
user interface item to observe and validate whether the proper field validations have been
carried out or not.

The vice versa condition whereby first an operation is carried out by the tester at the user
interface and then the same is validated from the back end is also considered to be a valid
option.

Checking data integrity and consistency

 Whether the data is logically well organized

 Whether the data stored in the tables is correct and as per the business requirements.
 Whether there are any unnecessary data present in the application under test.
 Whether the data has been stored as per as the requirement with respect to data which
has been updated from the user interface.
 Whether the TRIM operations performed on the data before inserting data into the
database under test.

Ronica Coorey DAD Assignment no 33 Page 7 of 99

 Whether the transactions have been performed according to the business requirement
specifications and whether the results are correct or not.
 Whether the data has been properly committed if the transaction has been
successfully executed as per the business requirements.
 Whether the data has been roll backed successfully if the transaction has not been
executed successfully by the end user.
 Whether the data has been roll backed at all in the condition that the transaction has
not been executed successfully and multiple heterogeneous databases have been
involved in the transaction in question.
 Whether all the transactions have been executed by using the required design
procedures as specified by the system business requirements.

Login and user security

The validations of the login and user security credentials need to take into consideration
the following things.

 Whether the application prevents the user to proceed further in the application in case
of a
 Invalid username but valid password
 Valid username but invalid password.
 Invalid username and invalid password.
 Valid username and a valid password.

Ronica Coorey DAD Assignment no 33 Page 7 of 99

 Whether the user is allowed to perform only those specific operations which are
specified by the business requirements.
 Whether the data secured from unauthorized access
 Whether there are different user roles created with different permissions
 Whether all the users have required levels of access on the specified Database as
required by the business specifications.
 Check that sensitive data like passwords, credit card numbers are encrypted and not
stored as plain text in database. It is a good practice to ensure all accounts should
have passwords that are complex and not easily guessed.

Non-functional testing

Nonfunctional testing in the context of database testing can be categorized into

various categories as required by the business requirements. These can be load
testing, stress testing, security testing, usability testing, and compatibility testing and
so on. The load testing as well as stress testing which can be grouped under the gamut
of performance testing serves two specific purposes when it comes to the role of
nonfunctional testing.

Risk quantification-

Quantification of risk actually helps the stakeholders to ascertain the various system
response time requirements under required levels of load. This is the original intent of
any quality assurance task. We need to note that load testing does not mitigate risk
directly, but through the processes of risk identification and of risk quantification,
presents corrective opportunities and an impetus for remediation that will mitigate
risk.

Minimum system equipment requirement

Ronica Coorey DAD Assignment no 33 Page 7 of 99

 The understanding which we observe through formal testing, the minimum system
configuration that will allow the system to meet the formal stated performance
expectations of stakeholders.

3.4.1 Test Plan for Naomi Beach Villa Database

Test Case: #1 Test Case Name : Guest Details

System: Naomi Villa Management System Sub System :

Design By : Ronica Coorey Design Date : 2015/12/12

Description: Member Details form Adding Member, Update Member and Delete
Member

Step Action User System Response Pass/

Expected Fail
Result

Ronica Coorey DAD Assignment no 33 Page 7 of 99

1 Adding Display Pass
Records Message

2 Update Display Pass

Records Message

3 Display
Message
Delete
Records

Table 6 Test Case 1

3.4.1 Actual Test Case

Test Case: 2 Test Case Name : DDL
Statements
System: Naomi Villa Database System Sub System :

Design By : Ronica Coorey Design Date : 2016/01/14

Description: Execute database tables and get following results

Ronica Coorey DAD Assignment no 33 Page 7 of 99

1 Record Display DDL
Successfully Message State-
ments

2 Syntax Display
Errors Message

Object Display
3 Errors Error
Message

4 Data Type Display

Errors Error
Message

Table 7 Test Case 2

3.4.2 Test Case 3

Test Case: 3 Test Case Name : DML Statements

System: Naomi Villa Database System Sub System :

Design By : Ronica Coorey Design Date : 2016/06/04

Description: Every DML Query’s execute and get these messages.

Step Action User System Response Pass
Expected /
Result Fail

Ronica Coorey DAD Assignment no 33 Page 7 of 99

 1 Insert Record Display Pass
Successfully Message

1 Display Display Pass

Record Data
Successfully

2 Syntax Display Pass

Errors Massage

3 Object Errors Display Pass

Error
Massage

3 Column Display Pass

Errors Error
Massage

4 Table Display Pass

Definition Error
don’t Match Message

Table 8 Test Case 3

Demonstrate how MS SQL effectively supporting to the designer when they

implement the database through the documentation. (User screen shots)

3.5 Sql Server Demonstration

 Gaining access to SQL Server Management studio Express

When you open MS Management Studio Express, a connection window with SQL Server
will be opened. In this window, enter the details for connection with SQL EXPRESS
existent in your PC. Click in the Server name box and choose the browser for more
option to search for the other server. You will have to use the Windows Authentication
mode to get access to the program.

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Figure 39 Connect to SQL Server

 Management Studio Work Area

After connected, the work area of Management Studio is then open. This window
presented the Menu Bar, Toolbar, Object Explorer and Active Files.

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Figure 40 Management Studio Express Work Area

Through the Object Explorer section we can navigate among all of the Servers, Databases
and their items are contained (tables, views, Diagrams etc.).

 Creating new database

To create a new database, right click on the Databases folder and click the New

Database option.

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Figure 41 New Database Form

The window for the new database is open. Then insert the database name, choose the
owner and configure the initial size of the log and data files. In the Options page, you can
set some of the new database’s advanced settings such as: Compatibility level, Recovery
model and containment type. After creating all the actions, click the OK button to create
the database.

Creating tables

Ronica Coorey DAD Assignment no 33 Page 7 of 99

To create a new table, expand the Database we use, Right clicks over Tables and choose
the Table option. Then the new table’s data structure opens. It contained of three parts:

 The Table Designer toolbar

 The spread sheet for creation of the columns

The column properties box

Figure 42 Creating tables

To insert a field, you must write the name of the column, choose the data type and check
if it allow null values. After the new field selected, you will be able to set all its
properties in the Column Properties box. Set the primary key and save the table by
pressing CTRL+S. After the Management Studio Express asks you to enter the name of
the table .Once it is saved, the table begins to appear in the list of the Tables folder.

Exporting the database

Ronica Coorey DAD Assignment no 33 Page 7 of 99

After creating the database, there might be the essential to export it to a SQL Server, or to
generate a backup for later use. Following are the steps to exporting the database to a
Script (*.sql) archive.

 Click with the right hand button over database.

 Go to Task > Generate Scripts. The Management Studio will show a wizard for the
script generation.
 Click next, select the “Script for entire database and all database objects” on the
Choose Objects window and click the Next button.
 Specify how script should be saved, click Finish

Figure 43 Script Wizard

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Explain how verification and validation are addressed in your database using stored
procedures

3.6 Verification
Verification in database is the procedure in checked properly so that data is entered
correctly into the database without any errors. When data gets move from one table to
other table or transcribe data in one format to other, there may be generate some errors
while entering data.

Figure 44 Procedure - Query

Ronica Coorey DAD Assignment no 33 Page 7 of 99

3.6.1 Stored Procedure – In Output Variable

Figure 45 in Output Variable

Figure 46 Create Procedure

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Figure 47 Stored Procedure Create a table

Validation

Validation is the procedure where we check the sensibility of data. There are a variety of
data validation methods and they are dependent on the data item. In the application
various fields and data are validated by providing the stored procedures, that only valid
data can be entered into database

There are many situations in which we need to do validation in our stored procedure. We
can do validation before update, delete and insert functions in stored procedures. Data
validation is the setting validation in a stored procedure on update, delete and insert
processes.

Ronica Coorey DAD Assignment no 33 Page 7 of 99

 The CREATE PROCEDURE statement is used to create a stored procedure.
 You can pass one or more parameters in a stored procedure from the calling program
of the stored procedure.
 The WITH RECOMPILE statement forces the stored procedure to be recompiled
every time it is used in an application. It reduces system performance so avoid it.
 The WITH ENCRYPTION statement in the stored procedure prevents users from
viewing the code of the stored procedure.
 To declare a parameter in the stored procedure we use the @ sign before the name of
the parameter; then it's a data type.
 The Input parameter is used to accept values from the calling program.
 If a stored procedure returns a value to the calling program then we need to declare a
variable in the calling program.
 The DECLARE clause is used to declare a variable in the calling program.
 To check validation we use IF ELSE statement exists function.
 We use RAISE ERROR to explicitly generate an error in our stored procedure.

Source (Verma, 2012)

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Discuss the security policies relevant to any database management system

3.7 Triggers
A SQL trigger is a set of SQL statements stored in the database catalogue. A SQL trigger
is executed or fired whenever an event associated with a table occurs e.g., insert, update
or delete. A SQL trigger is a special type of stored procedure. It is special because it is
not called directly like a stored procedure. The main difference between a trigger and a
stored procedure is that a trigger is called automatically when a data modification event is
made against a table whereas a stored procedure must be called explicitly.

Triggers can be written for the following purposes:

 Implementing referential integrity

 Auditing data
 Synchronous replication of tables
 Imposing security authorizations
 avoiding invalid transactions

Source (www.mysqltutorial.org)

Types of Triggers

• There are three action query types that you use in SQL which are INSERT, UPDATE
and DELETE.

• So, there are three types of triggers and hybrids that come from mixing and matching
the events and timings that fire them.

• Basically, triggers are classified into two main types:

• After Triggers (For Triggers)

• Instead Of Triggers

• These triggers run after an insert, update or delete on a table. They are not supported
for views. 

Ronica Coorey DAD Assignment no 33 Page 7 of 99

 AFTER TRIGGERS can be classified further into three types as: AFTER INSERT
Trigger. AFTER DELETE Trigger. AFTER UPDATE Trigger.

Examples for Triggers

Figure 48 After Update Trigger

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Figure 49 After Delete Trigger

Figure 50 After Insert Trigger

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Views - Create

Figure 51 create view

Figure 52 Update view

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Figure 53 Drop view

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Gantt chart

Figure 54 Gantt chart

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Self Criticism
This assignment was of great source of knowledge to me. From this assignment, I came
to know about various data models, database technologies. In this assignment I covered
many ways of the database.

I got to realize what a database is. I achieved many of the objectives which are mainly
data insertion, updating and deletion in the database and I learnt how to secure database
with the stored procedures and triggers. It’s certainly a new thing to us learning about
Databases.

The following system that we were given was Naomi Beach Villa Management System.
It gave me a wide knowledge about the relations designs on the database with entities
attributes and how to create a successful relation between each entity, and to give each a
valid unique primary key, plus also to make a difference between entity and attributes.

I'm very satisfied with this project as I achieved, and I gave my uttermost effort in
completing this following assignment.

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Summary
As we mention about this specific assignment known as DAD or in other words Database
Analysis and Design, it foretells about certain new things that we have never known
about

We would be confused when everyone talks about it, mentioning about Databases and all
those stuff. But what is a Database. As we mention about Databases it is known as an
organized collection of data. It is the collection of schemas, tables, queries, reports, views
and other objects. Besides that, although we mentioned about Databases behind all that
it’s barely a design known as Database design.

Database design is the process of producing a detailed data model of a database. This data

model contains all the needed logical and physical design choices and physical storage
parameters needed to generate a design in a data definition language, which can then be
used to create a database. A fully attributed data model contains detailed attributes for
each entity.

It can be used to describe many different parts of the design of an overall database

system. Principally, and most correctly, it can be thought of as the logical design of the
base data structures used to store the data. However, the term database design could also
be used to apply to the overall process of designing, not just the base data structures, but
also the forms and queries used as part of the overall database application within
the database management system

Conclusion

Understanding different model of database and GUI applications; the knowledge behind
these models are effectively applied and database has been implemented as per the
requirements and regulations, SQL Server, SQL queries have been executed and the
results have been practical and reported. Also a GUI application related with the SQL
server is designed for the user to insert, update, delete and search data from the database.
In this way, all the given tasks have been successfully completed and are submitted with
proper documentation.

Ronica Coorey DAD Assignment no 33 Page 7 of 99

References

• w3school. 1999. www.w3school.com. [ONLINE] Available at: http://www.

w3school.com. [Accessed 06 April 16].

• Microsoft Corporation. 2016. www.technet.com. [ONLINE] Available

at: http://www.technet.com. [Accessed 06 April 16].

• Answers.com. 2016. www.Answers.com. [ONLINE] Available

at: http://www.Answers.com. [Accessed 06 April 16].

• Techopedia. 2016. www.Techopedia.com. [ONLINE] Available

at: http://www.Techopedia.com. [Accessed 06 April 16]

• Guru99. 2016. www.Guru99.com. [ONLINE] Available

at: http://www.Guru99.com. [Accessed 06 April 16].

• Tutorialspoint. 2016. www.Tutorialspoint.com. [ONLINE] Available

at: http://www.Tutorialspoint.com. [Accessed 06 April 16].

• Mullins, S, Craig. 2009. www.Mullins,S,Craig.com. [ONLINE] Available at:

http://www.CraigSMullins.com. [Accessed 06 April 16].

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Indexes

B M
BID, 57 MS, 74

D N
DAD, 92 NIC, 45
DBMS, 32
DDL, 45 O
DML, 73
OK, 94
DOB, 40, 65

P
E
PC, 74
ER, 45
ERD, 35
R
F RID, 57

FID, 55
S
G SET, 58
SQL, 92
GID, 65
GUI, 92
T
I TRIM, 68

IDE, 47

Ronica Coorey DAD Assignment no 33 Page 7 of 99

Glossary

BID – Booking ID

DAD – Database Analysis and Design

DBMS – Database Management System

DOB – Date of Birth

DML – Data Manipulation Language

DDL – Data Definition Language

ERD – Entity Relationship Diagram

FID – Facility ID

GID – Guest ID

GUI – Graphical User Interface

IDE – Integrated Development Environment

NIC – National Identity Card

RID – Room ID

SQL – Structured Query Language

Ronica Coorey DAD Assignment no 33 Page 7 of 99