Data Analysis And Design Computer Science Essay

Published:

A model is any representation of something else. It may be physical e.g. a toy car representing a real car or it can be conceptual, only represented on paper to bring a good description of the operation of the real object. "A data model is a way finding tool for both business and IT professionals, which uses a set of symbols and text to precisely explain a subset of real information to improve communication within the organization and thereby lead to a more flexible and stable application environment." (Singh 2009) .In software engineering a data model is an abstract representation of facts and how those facts can be accessed. By examining a data model a certain structure comes out, depending on the type of data being represented and retrieved; all this leads to structured data. Data modeling structure is used in design of database models and information systems. We have different types of data model structures in relation to their contribution in data base development; these are relational model, hierarchical model and the network model (Kimbal 2005).

Data model structures

Lady using a tablet
Lady using a tablet

Professional

Essay Writers

Lady Using Tablet

Get your grade
or your money back

using our Essay Writing Service!

Essay Writing Service

In a hierarchical model data is stored in different levels in the form of a hierarchy. For example a company consists of departments and these departments consist of employees. If this was to be represented using a tree structure, the department will be based at the root of the tree and the employees will be like branches, hence to get to employees one will' be forced to traverse the whole branches. The hierarchical model only implements a 1 to one model unlike the relational model that can implement a zero one to one, a zero one to many, a one to many and a many to many relationship between the entities. The network model came into being to address the shortcomings of the hierarchical model, and this concentrated mainly to relationship representation. This is because unlike with the hierarchical model, with the network model you could be able to show how data entities are related to each other. The irony is that unlike the network model which is not in existence presently the hierarchical model is still in use in legacy applications. While in hierarchical models records are represented as one parent having many children in a network model we could have many parents having many child records. In a relational model unlike the other models data is represented in form of collection of tables, with each table fully describing an entity. In this model we have the columns or field representing the properties of an entity and the rows has got specific values of an instance or occurrence of the entity (Singh 2009).

Figure 1, figure 2 and figure 3 below shows examples of the hierarchical model, network model and relational model respectively

Figure1. (Pratt 1985)

Figure 2 (Pratt 1985)

Figure 3 (Pratt 1985)

P2

Database development focusing on data mining and data warehousing

Due to lots of data being stored in databases, it is sometimes difficult for a large company with huge databases to be able to extract meaningful information from the database. This can really be a challenge in a dynamic world that needs varied information to help in decision making in a timely manner. This tedious task of extracting relevant information form a large collection of database led to data warehousing and data mining. Data mining is achieved through specialized tools with ability to predict behavior and trends that might happen in future, aiding companies make knowledge based decisions. Data mining process is driven by well developed algorithms, ability to collect massive data and computers with powerful processors. Without data mining the process of obtaining predictive processed data from a database can be a nightmare. For successful application of data mining techniques we require a data warehouse. A data warehouse is central data store extracted from data which is operational. Data present in a warehouse exhibits subject orientation, historic nature and non volatility, contrary to an online processing. Sound data base modeling leads to well built data warehouse in which data mining can be carried effectively. This implies most companies which rely on well processed data to perform their critical functions, must possess data architectures which are well integrated. This must emanate from excellent data modeling, leading to effective data warehousing with the objective of applying the necessary tools for data mining process (Connolly 2005).

P3

Approaches to database design

Lady using a tablet
Lady using a tablet

Comprehensive

Writing Services

Lady Using Tablet

Plagiarism-free
Always on Time

Marked to Standard

Order Now

There are no specific universally agreed approaches to database design. Database designs being a modeling technique, different designers have taken their on approach to develop functioning databases. Some designers employ the integrated approach. This has a series of phases. The input and output of this phase are explained and any problems that may arise being pointed out. These phases are: requirement analysis, View modeling, and View integration, View restructuring and finally schema analysis and mapping Schema any analysis and mapping.

Another approach is staged database design. This being a top down approach it begins by outlining the general requirement of an organization. As this task continues the organization problems are checked in detail. Steps involved in this approach are: analysis of current user environment being keen on its output. This is flowed by logical data odl being developed. At this stage the designer looks into issues like data volumes, transactions volumes, etc. the next stage is choosing a DBMS. It is during this stage that the designer makes a choice of database management system. The next stage is the mapping of the logical model to the DBMS. After this step a physical model is developed, then follows the evaluating of the physical model followed by performance tuning and then finally implementing the physical model. The third approach is semantic modeling. We also have the top down approach and the design approach. In the design approach implementing the distances involves the 2nd normal form and then 3rd normal form (Nagabhushana 2006).

P4

The Sport_Doping_Therapy Database

This database consists of six tables Patient_tbl, Doctor_tbl, Booking_tbl, Room_tbl, Session_tbl and Treatment_tbl. Sport_Doping_Therapy is a relational database develop using SQL server 2005. The database meets user requirements by ensuring there is no double booking is possible for guest rooms. This is achieved by use of constraints in the booking table. Through analysis in the session's table continuous treatment is checked to ensure it does not exceed four days. The tables are related appropriately as it will be seen in the following diagrams.

Figure 4 below shows the database diagram

Figure 4

P5 & P6

Relationships in Sport_Doping_Therapy Database

The patient table is related to the booking table. When a patient arrives at the sport doping clinic, what he does first is to book for a room. The patient is diagnosed and then assigned a particular treatment which is a offered by a specific doctor. This brings out the relationship between the treatment table and the doctor table. For the patient to be treated a session has to be created, this session occurs in a specific room done by a specified doctor hence the relationship between the session table, the booking table and the doctor's table. Figure 5 below shows the database relationship diagram.

Figure 5

P7

Insert Query

INSERT INTO Treatment_tbl VALUES('T0003','Knee ligaments')

Insert tratment values into treatment table

   Treatment_ID

Description

T0001  

Muscle anti-stressing

T0002     

Tendonitis

T0003     

Knee ligaments

T0004     

Ankle flexing

T0005     

Elbow restoration

T0006     

Hip strengthening

Update Query

UPDATE [Sport_Doping_Therapy].[dbo].[Room_tbl]

SET [RoomType] = 'TREATMENT ROOM'

WHERE RoomName = 'HERO'

Room_ID     

RoomName

Room_Type

R0001     

VICTORY

TREATMENT ROOM

R0002     

HERO

TREATMENT ROOM

R0003     

WINNER

TREATMENT ROOM

R0004     

EMIRATES

GUEST ROOM

R0005     

NUCAMP

GUEST ROOM

R0006     

OLD TRAFORD

GUEST ROOM

R0007     

HAT TRICK

ARCADE GAMES ROOM

Select Sort Query

SELECT *

FROM [Sport_Doping_Therapy].[dbo].[Patient_tbl]ORDER BY Age ASC

Query Results

P0001 HILLARY CLINTON ATHELETE 123456 09876 3256 CHICAGO 18

P0003 MONICA LEWINSKY SPRINTER 234155 23412 2354 TORONTO 22

P0004 JESSICA OKAYA FOOTBALLER 241521 23212 2321 NEW YORK 25

P0002 JERRY ARIEL KICK BOXER 012345 32421 3242 ORLANDO 25

Join Query

SELECT Doctor_tbl.FirstName, Doctor_tbl.LastName, Doctor_tbl.Specialty, Treatment_tbl.Description

FROM Doctor_tbl JOIN Treatment_tbl

ON Doctor_tbl.Treatment_ID = Treatment_tbl.Treatment_ID

Query Results

Lady using a tablet
Lady using a tablet

This Essay is

a Student's Work

Lady Using Tablet

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

Examples of our work

ALEX JOHNSON SURGEON Tendonitis

WILFRED TRUMP PHYSICIAN Ankle flexing

JAMES KIMOTE SURGEON Muscle anti-stressing

JACOB WILLIS PHYSCIAN Knee ligaments

NANCY CLARA SURGEON Elbow restoration

Group By Query

SELECT Doctor_tbl.FirstName, Doctor_tbl.LastName, Doctor_tbl.Specialty, Treatment_tbl.Description

FROM Doctor_tbl JOIN Treatment_tbl

ON Doctor_tbl.Treatment_ID = Treatment_tbl.Treatment_ID

GROUP BY Doctor_tbl.Specialty, Doctor_tbl.FirstName, Doctor_tbl.LastName, Treatment_tbl.Description

Query Results

JACOB WILLIS PHYSCIAN Knee ligaments

WILFRED TRUMP PHYSICIAN Ankle flexing

ALEX JOHNSON SURGEON Tendonitis

JAMES KIMOTE SURGEON Muscle anti-stressing

NANCY CLARA SURGEON Elbow restoration

P8

Front end Screens/Forms for Data entry.

The following visual tools for data entry have been developed using Borland Delphi 2007 to enhance database design by aiding in data entry.

Patient Form

Figure 7 below shows patient form with data from the database.

Figure 7

Doctor Form

Figure 8 below shows doctor form

Figure 8

P9 & P11

Transact SQl

This is the procedural language for Microsoft SQL Server 2005. It is simply the Microsoft version of Oracle PL/SQL.

The following stored procedure rejects any attempts of booking more than four sessions per treatment room per day. This is done by analyzing the values of the table Session_tbl.

The following stored procedure rejects any attempts of entering bookings for treatment for the next day for all those patients who have four for consecutive days prior to the one being booked.

The following stored procedure reports any consecutive bookings for the same patient on agiven day.

The following stored procedure provides a list of treatment room utilization over a given period of time, providing details of patients treated and specialists providing the treatments.

The following stored procedure produces a detailed bill for room and treatment costs per patient. This is identified as date, room, specialist and treatment received plus the charges for the treatment.

P10

Entity Relation Diagram

Figure 6 below displays the relationship between the entities comprising Sport_Doping_Therapy database

Figure 6

Table Structures

Patient_tbl

Field Name

Data type

Primary key

Foreign key

Required

Pat_ID

Yes

-

Yes

FirstName

-

-

Yes

LastName

-

-

Yes

Profession

-

-

Yes

PassPort_No

-

-

Yes

Phone_No

-

-

Yes

Address

-

-

Yes

Age

-

-

Yes

Booking_tbl

Field Name

Data type

Primary key

Foreign key

Required

Booking_ID

Yes

-

Yes

Patient_ID

-

Yes

Yes

Room_ID

-

Yes

Yes

Pat_ID

-

Yes

Yes

Room_tbl

Field Name

Data type

Primary key

Foreign key

Required

Room_ID

Yes

-

Yes

RoomName

-

-

Yes

RoomType

-

-

Yes

Session_tbl

Field Name

Data type

Primary key

Foreign key

Required

Session_ID

Yes

-

Yes

Booking_ID

-

Yes

Yes

Doctor_ID

-

Yes

Yes

Start_Time

-

-

Yes

End_Time

-

-

Yes

Max_Duration

-

-

Yes

Doctor_tbl

Field Name

Data type

Primary key

Foreign key

Required

Doctor_ID

Yes

-

Yes

FirstName

-

-

Yes

LastName

-

-

Yes

Specialty

-

-

Yes

Treatment_ID

-

Yes

Yes

Treatment_tbl

Field Name

Data type

Primary key

Foreign key

Required

Treatment_ID

Yes

-

Yes

Description

-

-

Yes

P12

Database Testing Techniques

This refers to techniques used to find errors in a database. These techniques may be formal or informal or a combination of both. Over time database testing has been challenging and intimidating and hence it is an area that cannot be wished away by any designer. Database testing plays a core role in enhancing the stability and quality of an application. Some of database testing techniques are:

Smoke testing

Unit testing

Database structure confirmation

Database connectivity (simple test)

Data persistence testing

Database security and corruption testing

Performance testing

Boundary testing

To test Sport_Doping_Therapy database I carried out the following tests:

Data Loading Test

I loaded data from source table to the target table in consideration of business logic. The columns data types, constraints were checked during this test.

Relational Testing

During this test I carried both black box test and clear box test

Black box test

Testing of persistent data values

Testing of retrieval data values

Test of stored procedures

Clear box test

Testing of data consistency

Testing of constraints

Testing of views

Testing of triggers

Testing of data quality

Performance Testing

Testing of read/write/delete access time

Testing query return values access time

Test index performance

Structural Testing

Testing of all SQL query syntaxes on tables

Testing of design data format e.g. row and columns data arrangement etc.

Conclusion

The design and implementation of Sport_Doping_Therapy database was a major success and was completed within the appropriate time. Despite the fact of gathering the appropriate software and researching on relevant technology this exercise was completed well. The database was implemented using a relational model and normalized to the 3rd Normal form. A good relationship between the entities was developed fulfilling the business objectives of the project. The use of structured query language and PLSQL aided in the testing of the database to ensure it meets all the integrity tests. Therefore it can be seen clearly through proper planning and using the appropriate design and testing techniques database designers will be able to deliver high quality databases, which is core to any application development.