Using Word Or A Suitable Drawing Package

Published Date: 02 Nov 2017

Task 1

Analysis

Using Word or a suitable drawing package, produce an Entity Relational Diagram for the Case Study.

Consider at this stage the kinds of Reports and Queries that East London Vets may require.

Justify your choice and ensure that the database will contain the information needed to produce the reporting requirements.

P2.1, 2.2,

M1.1, M1.2, M2.1, M2.2 ( Reports and Queries should meet the Business Requirements. Justify your choices)

Entity Relational Diagram

Introduction

An entity-relationship diagram is a data modelling technique that creates a graphical representation of the entities, and the relationships between entities, within an information system.

The three major mechanism of an ERD are:

The entity is a person, object, place or event for which data is collected. For example, if you consider the information system for a business, entities would include not only customers, but the customer's address, and orders as well. The entity is represented by a rectangle and labelled with a singular noun.

The relationship is the interaction between the entities. In the example above, the customer places an order, so the word "places" defines the relationship between that instance of a customer and the order or orders that they place. A relationship may be represented by a diamond shape, or more simply, by the line connecting the entities. In either case, verbs are used to label the relationships.

The cardinality defines the relationship between the entities in terms of numbers. An entity may be optional: for example, a sales rep could have no customers or could have one or many customers; or mandatory: for example, there must be at least one product listed in an order. The three main chief relationships are:

One-to-one, expressed as 1:1;

One-to-many, expressed as 1:M; and

Many-to-many, expressed as M:N.

The steps involved in creating an ERD are:

Identify the entities.

Determine all significant interactions.

Analyze the nature of the interactions.

A number of tools, such as Ms Word, E draw, Visible Analyst and Data Architect, can be used to generate ERDs. I am going to use the Ms Word to generate the ERD, but before generating the ERD I want to draw the Level 0 FD which will show the starting process of the ERD.

Level 0: This is a level 1 DFD for East London Vets.

This is the ERD for East London Vets.

Reports and Queries

Pets or pet’s owner Reports and queries are clearly required by management of each surgery for an appointment or daily check up of each pets and some reports are required for billing process. Some queries like ending balance for each owner are used to help to generate reports. Others queries are not explicitly required, but I think it's necessary to have those like query of refund for each owner, Surgery would like to know how much refund for each Owner of Pet. Queries and reports for certain dates of appointments and treatments etc.

Reports          

The following table summarizes the reports in the East London Vets system. Those reports are minimal to meet management need. More report will be added and implemented to meet administrative need.

 

Report Name

Description

Relations Accessed

Bill

In East London Vets at the end of the billing period, billing system need to generate bill then mail to owner regardless the due. The billing period would be the end of the month or the beginning depending on East London Vets policy. In my implementation, I will use the end of month. But it should be easy to switch to the beginning of the month. Decision will be made by management. It will list all the charges order by date, all the payment made after the last bill, and all the credit which maybe returns at pro shops. Finally the bill will show the total due, or the credits available to redeem towards next bill.

· payment

· credit

· Pet Owner

. ending

. balance

Report of daily treatments

Reports of daily treatment for any appointment in given date. Report will list all pets treatment charges made on that day including cash and aggregate the total. 

· Department

· invoice

Report of total pets of each member

Lists total pet’s name of each Owner. Also show owner’s name.

· List Of Pets

· List Of Owners

 

Report of treated pets.

List Pet id and Pet name. Report will be ordered by owner id. This report will be used by management for analysis or Appointments.

· Pet’s ID

· Owner’s ID

 

Report of untreated pets.

List of untreated Pet id and Pet name. Report will be ordered by owner id. This report will be used by management for analysis or Appointments.

·Untreated Pet’s

ID.

· Owner’s ID

 

Report of most active time

List the total amount of each day and their amount.  

· Invoice

 

 

Justification of choosing Report And Queries

For me and I think for everyone it is very significant to know the importance of reports and queries in any kind of business. Good reports provide information about the concluding bits and pieces regarding business. Here i am justifying that these reports and queries are important in many ways because by producing this it is an easy way to tackle the information and handle the problem in a proper direction and which can be explored at ease. It may be more important for those financiers working in East London Vets and other staff while dealing with booking and appointment process for vets and owners. With the help of these reports I can have detailed information regarding ups and downs of East London Vets. It may help to produce a record of the previous years. It may be helpful in finding out the high and low grades in a business in previous many years. These reports will help to keep the detailed information regarding financial record of business. This would be required by East London Vets all the time. It is good to make copies of these reports to send it to different branches (Ilford, Manor Park and barking. From these reports it will be easy to know the previous picture of the East London Vets and they can also know that how much records are putting and how much they needs to put in.

Before I attempt to realize the great importance of these reports, I try to visualize a reasonably accurate concept of what these reports really are. These are a very useful tool, an instrument of managerial control. To put it simply, these reports can be explained as a social kind of information, on which East London Vets in ability may take a decision. The management engages the services of well qualified and experienced persons to write reports on the problem. They write these reports by conducting surveys, investigating the matter thoroughly and submitting their findings along with their recommendations or suggestions. The management always likes to rely on them and accept their recommendations and suggestions if they are found workable as well as profitable. Thus these reports will play the great role in the planning and organization of the East London Vets. But it is not the planning and organization of this business alone which come under the review of a report; it is the whole of business which benefits by it. When the industry is to be expanded, scores of reports have to be prepared and gone through before the board of directors can arrive at a safe decision. Often, even the routine direction of administrative and manufacturing units; require the assistance of reports whenever serious problems crop up.

Same like reports which I build for East London Vets Queries will also allow the user to extract relevant information from a database. These queries which I build for East London Vets will scan the whole table they can pre-define the categories of information which will be required. These Queries will also allow the user to join multiple tables. For example, if I have a table called "Owners" and a table called "invoices", I can use a query to join the two tables. When I run a query that joins these two tables, I can get results showing the name of the customer (from the Owner table) and all associated invoices (from the invoices table). Queries are often used as the basis for reports in SQL.

Queries don't do the same things as any other part of a database, and so asking for their 'advantages' isn't really valid. They are the only way to extract some of a table's records while ignoring those which don't match the criteria set by the query. Some queries which I generate like for ending balance for each member which help to used to generate reports. Others queries are not explicitly required, but I think it's necessary to have those. Like query of refund for each Owner, East London Vets would like to know how much refund for each member. If any owner of pets gets a lot of refund every month, then special attention should be given and staffs who give those refunds also should be monitored because a fraud might be involved.

Task 2

Design One

Design the tables to be used in the database. A minimum of six tables must be chosen.

Describe the choice of suitable Primary Keys, Foreign Keys, Indexes, Attributes and Data Types and justify your choice.

Explain why the data structures are in third normal form.

Justify your database design in terms of meeting user requirements for daily use and for the need to provide reporting requirements.

A Data Dictionary could be provided.

P2.1, 2.2

M1.1, M1.2, M2.1, M2.2 (Accurate tables that can be used to produce the correct Reports)

Here I am going to create some tables which is shown below.

In this column I enter all the required data about the Surgery.Surgery Table

Here I Set the primary Key for Surgery ID.

Here I save the table with a name ‘Surgery Table’.

Here I Set the primary Key for Vet ID.Vet Table

Here I Set the primary Key for Pet ID.Pet Table

t Emergencies

Here I choose the name for my table and press ok

Owner Table

Here I Set the primary Key for Owner ID.

Here I Set the primary Key for Appointment ID.Appointment Table

In this column I enter all the required data about the Transaction.Transaction Table

Here i set the Primary Key which will be unique

After the Primary key the table will look like below.

Primary Keys, Attributes and Foreign Keys

 

All Primary keys Of Tables

 

All Attributes

 

All Foreign Key

 

 

 

1

Surgery ID

 

 

a

Surgery Name

a

Surgery ID

 

 

b

Address

 

 

c

Postcode

 

 

d

Phone Number

 

 

 

 

 

 

 

 

 

2

Vet ID

 

 

a

First Name

b

Vet ID

 

 

b

Last Name

 

 

c

Professional In

 

 

d

Timing

 

 

e

Surgery Location

 

 

 

 

 

 

 

 

3

Pet ID

 

 

a

Pet Name

c

Booking ID

 

 

b

Pet Owner

 

 

c

Pet Type

 

 

d

Pet Emergency

 

e

Pet Fee

 

 

 

4

Transaction ID

 

a

Date

d

Transaction ID

 

b

Pet Name

 

c

Owner Name

 

d

Vet Name

 

f

Surgery Location

 

 

g

Total Fee

 

 

5

Appointment ID

 

a

Date

d

Appointment ID

 

b

Owner ID

 

c

Owner Name

 

d

Pet ID

 

f

Pet Name

 

 

g

Appointment With

 

 

6

Owner ID

 

a

Owner First Name

d

Owner ID

 

b

Owner Last Name

 

c

[Phone/Mobile]

 

d

[Owner's Pet Name]

 

f

Address

 

 

g

Postcode

 

 

7

Head Office ID

 

a

Appointment ID

d

Head Office ID

 

b

Owner ID

 

c

Pet ID

 

d

Surgery ID

 

f

Transaction ID

 

 

g

Vet ID

 

 

Third Normal Form And ERD

Here I Add all the tables to make the Relation in the entire Data Base. The Head Office contains all the Foreign Keys which is shown below.

This table contain all foreign keys.

Here I choose the name for this Relationship between Appointment Table and Head Office Table.

This window shows the properties of this relationship for Head Office Table.

The first Relationship is shown below.

First Relationship between ‘Head Office’ and ‘Appointment table’

Final ERD

: Data Dictionary :

Surgery Table

Column Name

Type

Size

Key

Input Mask

Other Validation

Surgery ID

Int

Long Integer

Surgery Name

Varchar(50)

50

Like "Ma?????"

Postcode

varchar

50

>LL00 0LL

Phone Number

Int

(9999) 00090009

Vet Table

Field Name

Data Type

Size

Key

Input Mask

Other Validation

Vet ID

Int

Long Integer

First Name

Varchar

50

Like "Fa?????"

Last Name

Varchar

50

>LL00\ 0LL;;_

Professional In

Text

25

Timing

Date & Time

14

>LL00 0LL

Surgery Location

Text

Pet Table

Field Name

Data Type

Size

Key

Display Control

Other Validation

Pet ID

Integer

Long Integer

Like "Mul?????"

Pet Name

Text

From List

Pet Owner

Varchar(50)

50

Pet Type

Text

Pet Emergency

Varchar(50)

50

Pet Fee

Varchar(50)

50

Owner Table

Field Name

Data Type

Size

Key

Input Mask

Other Validation

Owner ID

Integer

Long Integer

Owner First Name

Text

12

Like "Ta?????"

Owner Last Name

Text

12

Phone/Mobile

Number

Owner’s Pet Name

Text

Address

Varchar(50)

50

Postcode

Varchar(50)

50

Appointment Table

Field Name

Data Type

Size

Key

New Values

Other Validation

Appointment ID

Integer

Long Integer

Increment

Appointment Date

Date and Time

Like "dd-dd-dd ????"

Owner Name

Text

Owner ID

Integer

Pet Name

Integer

Pet ID

Integer

Appointment with (Dr)

Text

Transaction Table

Field Name

Data Type

Size

Key

IME

Other Validation

Transaction Number

Integer

Long Integer

Date

Date and Time

Like "dd-dd-dd ?????"

Pet Name

Number

12

Owner Name

Text

50

No Control

Vet Name

Number

Long Integer

Surgery Location

Text

50

No Control

Total Fee

Text

50

No Control

Task 3

Test Design

Discuss the different kinds of Testing Strategies for the Database Application

e.g

White Box

Black Box

Unit Testing

`

Explain and justify the testing approach to be used for the application

P4.3

White Box

White box testing is performed based on the knowledge of how the system is implemented. White box testing includes analyzing data flow, control flow, information flow, coding practices, and exception and error handling within the system, to test the intended and unintended software behaviour. White box testing can be performed to validate whether code implementation follows intended design, to validate implemented security functionality, and to uncover exploitable vulnerabilities. White box testing requires access to the source code. Though white box testing can be performed any time in the life cycle after the code is developed, it is a good practice to perform white box testing during the unit testing phase.

White box testing requires knowing what makes software secure or insecure, how to think like an attacker, and how to use different testing tools and techniques. The first step in white box testing is to comprehend and analyze available design documentation, source code, and other relevant development artefacts, so knowing what makes software secure is a fundamental requirement. Second, to create tests that exploit software, a tester must think like an attacker. Third, to perform testing effectively, testers need to know the different tools and techniques available for white box testing. The three requirements do not work in isolation, but together.

The general outline of the white box testing process is as follows:

Perform risk analysis to guide the whole testing process. In Microsoft’s SDL process, this is referred to as threat modelling.

Develop a test strategy that defines what testing activities are needed to accomplish testing goals.

Develop a detailed test plan that organizes the subsequent testing process.

Prepare the test environment for test execution.

Execute test cases and communicate results.

Prepare a report.

Inputs

Some of the artefacts relevant to white box testing include source code, a risk analysis report, security specification/requirements documentation, design documentation, and quality assurance related documentation.

Source code is the most important artefact needed to perform white box testing. Without access to the code, white box testing cannot be performed, since it is based on testing software knowing how the system is implemented.

Architectural and design risk analysis should be the guiding force behind all white box testing related activities, including test planning, test case creation, test data selection, test technique selection, and test exit criteria selection. If a risk analysis was not completed for the system, this should be the first activity performed as part of white box testing. The following section discusses risk analysis.

Design documentation is essential to improve program understanding and to develop effective test cases that validate design decisions and assumptions. If design documentation is unavailable or otherwise insufficient, the test team will at a minimum require direct access to the design team for extensive question and answer sessions. Either way, the test team will need to build a detailed understanding of how the software should behave.

Security specifications or requirements are a must, to understand and validate the security functionality of the software under test. Similarly, if the security requirements and specifications are lacking, the test team will need to obtain this information from various stakeholders, including the design team as well as the software’s business owner.

Security testers should have access to quality assurance documentation to understand the quality of the software with respect to its intended functionality. Quality assurance documentation should include a test strategy, test plans, and defect reports. Load and performance tests are important in understanding the constraints placed on the system and the behaviour of the system under stress.

Any artefact relevant to program understanding should be available to white box testers.

Black Box

Black box testing is based on the software’s specifications or requirements, without reference to its internal workings. Gray box testing combines white box techniques with black box input testing. This method of testing explores paths that are directly accessible from user inputs or external interfaces to the software. In a typical case, white box analysis is used to find vulnerable areas, and black box testing is then used to develop working attacks against these areas. The use of gray box techniques combines both white box and black box testing methods in a powerful way.

Unit Testing

The test plan should manifest the test strategy. The main purpose of having a test plan is to organize the subsequent testing process. It includes test areas covered, test technique implementation, test case and data selection, test results validation, test cycles, and entry and exit criteria based on coverage metrics. In general, the test plan should incorporate both a high-level outline of which areas are to be tested and what methodologies are to be used and a general description of test cases, including prerequisites, setup, execution, and a description of what to look for in the test results. The high-level outline is useful for administration, planning, and reporting, while the more detailed descriptions are meant to make the test process go smoothly. While not all testers like using test plans, they provide a number of benefits:

Test plans provide a written record of what is to be done.

Test plans allow project stakeholders to sign off on the intended testing effort. This helps ensure that the stakeholders agree with and will continue to support the test effort.

Test plans provide a way to measure progress. This allows testers to determine whether they are on schedule, and also provides a concise way to report progress to the stakeholders.

Due to time and budget constraints, it is often impossible to test all components of a software system. A test plan allows the analyst to succinctly record what the testing priorities are.

Test plans provide excellent documentation for testing subsequent releases—they can be used to develop regression test suites and/or provide guidance to develop new tests.

A test manager (or similar role) is responsible for developing and managing a test plan. The development managers are also part of test plan development, since the schedules in the test plan are closely tied to that of the development schedules.

On the next page I show the testing process

Testing

Number

Of

Test

Test Data

Action

Error Message

Pass/Fail

Comment

1

Splash Screen

Run for 4 second

No

Pass

Wait 4 second

2

Password

Capital letter

No

pass

4 capital letters

3

Switch Board

First window

No

pass

Main Interface

Surgery Table

Number

Of

Test

Test Data

Action

Error Message

Pass/Fail

Comment

4

Surgery ID

Primary Key

Auto Num

Pass

Unique ID Number

5

Num Of Vets/Surgery

Integer Between 1-200

Long integer

pass

Capacity 20 Vets/Surgery

6

Telephone

Run

pass

Number Only

7

Address

pass

Integers and Alphabet

Vet Table

Number

Of

Test

Test Data

Action

Error Message

Pass/Fail

Comment

8

Vet ID

Primary Key

Auto Num

Pass

Unique ID Number

9

Num Of Pet/Vet

Integer Between 1-20

Short integer

pass

Serve 12 Pets

10

Checkups Availability

Yes/No

pass

View list

Pet Table

Number

Of

Test

Test Data

Execute

Error Message

Pass/Fail

Comment

12

Pet ID

Primary Key

Auto Num

Pass

Unique Court ID Num

13

Num Of Checkups

Integer Between 1-15

Short integer

pass

3 Times/week

14

Charges

Currency

Standard

pass

By Card or Cash

Owner Table

Number

Of

Test

Test Data

Execute

Error Message

Pass/Fail

Comment

15

Owner ID

Primary Key

Auto Num

Pass

Unique Court ID Num

16

Num Of Pets

Integer Between 1-25

Short integer

pass

3 Times/Week

17

Charges

Currency

Standard

pass

By Card or Cash

Appointment Table

Number

Of

Test

Test Data

Execute

Error Message

Pass/Fail

Comment

18

Appointment ID

Unique Key

Auto Num

Pass

Unique Purchase Num

19

Date

Date and Time

Integer

pass

Infinite Num

20

Appointment with

Dr’s List

Standard

pass

Transaction Table

Number

Of

Test

Test Data

Execute

Error Message

Pass/Fail

Comment

24

Transaction Num

Primary Key

Auto Num

Pass

Unique Transaction Num

-

Vet ID

//

//

Pass

Unique Vet Num

-

Pet ID

//

//

Pass

Unique Pet Num

-

Owner ID

//

//

Pass

Unique Owner Num

-

Surgery ID

//

//

Pass

Unique Surgery Num

-

Charges

Currency

Pass

By Card or Cash

-

Appointment ID

//

//

Pass

Unique Appointmant Num

-

Date Of Transaction

Date

Long Date

Pass

Current Date

----------------------------------------------------------------------------------------------------------------------------------------

Task 4

Table Creation

Using the SQL Server Management Studio/Visual Studio software, create the following

Tables

Primary and Foreign Keys

Indexes

Relationships

Discuss the need for Data and Referential Integrity in the design and creation of tables. Discuss how errors could be reduced at the input, processing and output stage.

Show how the implementation reduces the above errors

Use Screen Dumps to provide evidence for the above creation of the database objects.

P2.3, P3.2, P4.2, P4.3

It is very easy to create the following in SQL Server Management Studio/Visual Studio software.

Tables

Primary and Foreign Keys

Indexes

Relationships

Opening SQL server management studio/ Visual Studio

First I click on start in the corner of the desktop and from programme select the Microsoft Visual Studio 2005 which is shown below.

Creating a table

In the server window I press the right button on the table which is in the left of the Microsoft Visual Studio and press the ‘Add New Table’ which is shown below in the diagram.

This will give me the option to create a new table.

This is the first column of the table where I am going to enter the unique ID.

After entering the other data the table looks like below.

Setting the Primary Key

By right clicking on the Appointment ID in the column give the option to set the ‘Primary Key’ which is shown below.

After choosing the ‘Primary Key’ the data base look like below.

Setting the Data Type

Setting of a data type defends on the type of data. Here I choose different data which is shown below. In the cell of ‘Data Type’ column I click on the right button of the mouse and then choose different data type.

Here i select the Data Type ‘int’ for ‘Appointment ID’ which will show integers only.

Setting the Relationship

Setting of a ‘Relationship’ between different tables makes the connection between them. Here I choose different Tables which are shown below. In the cell of ‘Column Name’ I click on the right button of the mouse and then select the relationship option which is shown below.

Here i select the ‘Relationships’ option which will make different relation between different table.

These are the tables which I Add for the ‘Relationship’.

Here I saved the relationship between the following tables.

One by One Relation Of Tables

The ‘Head Office’ contains all the foreign keys where the ‘Appointment Table’ contain the primary key which is linked below with the help of ‘Relationship’.

Here i create the first ‘Relationships’ between the two tables.

In this diagram all the tables are linked with the ‘Head Office’ table.

Task 5

Query Creation

Using the SQL Server Management Studio/Visual Studio software

Populate the tables so that Query Testing can take place.

Use the Query Designer to create the queries needed for the Reports.

Use the Query Designer to create queries for Database Maintenance. e.g. Insertion, Deletion and Update of Table Rows.

Include examples of Stored Procedures

Run the queries to test their design and provide Screen Dumps of the Queries and the resulting Record Sets.

The testing would include evidence of implementation of the Testing Design stage. P3.1, P3.2, P3.3, P4.2, P4.3

M1.1, M1.2, M2.1, M2.2 ( Accurate application of the software and use of Stored Procedures. The majority of the queries should work accurately)

D3.3, D3.4 (Complete set of accurate Reports. Formulae used for Totals and Averages)

instructions