Fact-finding is a formal process by which information is acquired via various methods in order tobetter articulate the requirements, and intricacies of a potential database system. In order forthe subsequent steps of database design to be completed properly, it is imperative that the fact-finding step be carried out thoroughly so as to ensure complete understanding by thedeveloper.The following techniques were used in the fact-finding process of this project.



Interviewing

–

A number of casinos and a few end users were interviewed to provideunderstanding of the database system. The structure of the interviews was unstructuredat first, allowing the interviewee toprovide direction to the interview. Later, questionswere formed in order to fill in any apparent gaps left in understanding. It is worth notingthat interviewing providedsupplemental

information that the other techniques couldnot provide.



Research–

A majority of this project’s end-user understanding was acquired viaresearch as there is a multitude of information available. Casinos both physical, and on-line provided guides to their system, while blogs and other end-user documentation wasavailable and surprisingly detailed on their perspective of the betting system.Researchalso provided some clarity to answers given in the interview process by givingfundamental information that some interviewees assumed in their responses.



Questionnaires–

A structured series of open-ended questions were used to givestructure ofthe entity types and attributes. Like the research, this provided a broadunderstanding that was later clarified through the interviewing process.

Page |4

1.2

Introduction to Enterprise/Organization

Gambling has been around just about as long as any form of currency was passing throughhands. It is by no stretch of the imagination to assume that sports gambling has been around aslong as their respective sports. Football was created around 1900 C.E. and the NFL was foundedin 1920. Money line betting is the original, most basic form of sport betting: you bet which teamwill win. The problem arose that a vast majority would bet for one team(the team expected towin;

orfavorite) which did not provide the balance that thebookmakers (people who took bets)

desired. Thus the invention of the point spread came about. The point spread assigns a handicapto the favorite team so that they must win by a certain amount of points. After observing thatmore typesof betting resulted in more gamblers, another form of betting was created, calledtotals. For totals betting, gamblers bet on the summated amount of points in a game, withoutregard to which team wins or loses.

Recently, more forms of betting opportunities

have arose,such as parlays and teasers.

1.3

Structure of the Enterprise

The basic structure of sports betting consists of three main parts: The oddsmaker, sportsbook,and gambler. The oddsmaker controls the given odds on a game. In a money line bet, theyprovide an opinion on who they think will win the game. However in point spread gambling,they provided the handicap by which the favorite team must win by. These handicaps areformulated through complex and exhaustive algorithms, which are not shared withthe public.The sportsbook is any entity that takes bets from gamblers. They get their odds and pointspreads from the oddsmakers and offer these to the gamblers. Gamblers place their bets, andreceive any winnings from the sportsbook. Typically, a casinoacts as both a sportsbook andoddsmaker. The gambler is perhaps the most obvious of the three: the one who places bets.

It is a fact that the sportsbook has an inherent advantage over gamblers and the bets they place.It is called the 11/10 vigorish, which means for every 11 units that a gambler bets, they have apotential to gain 10 units (a winning 11 unit bet will result in a 21 unit return). This means that agambler has to win 52.38 percent of their bets just to break even.

Page |5

1.4

ItemizedDescription of Major Objects

A gambler is the person that drives this enterprise. The gambler will have basic informationstored, a username, name, and password as well as contact information such as an address. Agambler has a relationship with a bet inthat the gamblerplaces

the bet.

The bet is a simple object with the attribute of amount. However it will have relationships withthe game entity and book (or sportsbook) entity. The bet willon

a game which isheld bya book.However more information may be required to further describe the game to the end user.Attributes such asscore, weather, and

game type are needed. Any more details about the teamsthat are required will likely result in a team entity, which will be why we create one now.

A team willplay in

a game. The play

relationship

will have the boolean attribute “at home”. Theteam will have a team name, location, record, and current streak as attributes. Let us now goback and examine the relationship

between the bet and the book.

A bet isheld by a book. The book provides odds for the game,

so they willl have theodds onrelationship

with the game. The odds onwill also describe the spread of the game. The book willhave the attribute of bank amount, as potential winnings can never exceed the amount in thebook’s bank. The book will also have basic attributes such as name and address.

1.5

Data Views and Operations for User Groups

There are two user groups: the gamblers and the bookies. The gamblers will need to be able tolog in and place bets with the book on a game. They will also need to be able to view the statusof the bet after they have placed it in order to know if they have won, and how much they havewon. The bookies will need to be able to set the odds on a game, and manage game data. Thebookies will need to be able to view both betting summaries for a game as well as detailedgambler-level betting information.

Page |6

2.

Conceptual Database Design

2.1

Entity Set Description

User



This entity describes anybody whouses the system to place

or manage bets. The intentof this database it to manage bets, so minimal contact as well as secure logininformation

is stored.



Candidate keys: userID,

userName



Primary key: userID



Strong/Weak Entity: Strong



Fields to be indexed: userID,userName

Name

userID

userName

fullName

address

emailAddress

password

Description

An auto-incremented value.

A user-chosenidentifier

User’s fullN慭e

User’s慤T牥獳

User’s email

User’sp慳獷arT

Domain/Type

32 bitUnsignedInteger

String

String

String

String

String

Value Range

0 … 2^32

䅮X⁃桡慲牡a

䅮X⁃桡慲牡a

䅮X⁃桡慲牡a

䅮X⁳ 物rgwith ‘@’ and‘.’

䅮X⁃桡慲牡a

DefaultValue

None

None

None

None

None

None

Nullable?

No

No

No

No

No

No

Unique?

Yes

Yes

No

Yes

Yes

No

Single orMultipleValue

Single

Single

Single

Single

Single

Single

Simple orComposite

Simple

Simple

Composite

Composite

Simple

Simple

Page |7

Bet



This entity describesa bet placed by a gambler on a game with a book. It detailsamount, time, date, and winning information.



Candidate keys:betID



Primary key:betID



Strong/Weak Entity: Weak



Fields to be indexed:BetID, Date

Name

betID

amount

time

date

type

win

Description

An auto-incrementedvalue.

A positivenumber

Time ofbet

Date ofbet

Bet type

Did thebet win?

Domain/Type

32 bitUnsignedInteger

32 bitUnsignedDouble

Time

Date

String

Boolean

Value Range

0 … 2^32

0.00 …10H000⸰0

䍵牲敮琠瑩We⁯nlX

䍵牲敮琠M慴a⁯nlX

MoneX⁬楮eⰠ獰V敡搬⁯r瑯瑡汳

0⁯爠r

Default Value

None

None

Currenttime

CurrentDate

None

null

Nullable?

No

No

No

No

No

Yes

Unique?

Yes

No

No

No

No

No

Single orMultipleValue

Single

Multiple

Multiple

Multiple

Multiple

Multiple

Simple orComposite

Simple

Simple

Composite

Composite

Simple

Simple

Page |8

Game



This entity describes amatch between two teams.



Candidate keys:gameID



Primary key:gameID



Strong/Weak Entity:Weak



Fields to be indexed:gameID, Date

Name

gameID

homeScore

awayScore

time

date

gameType

weather

Description

An auto-incrementedvalue.

A positivenumber

A positive

number

Time ofbet

Date ofbet

Whattype ofgame isthis?

Weatherforcast forthe game

Domain/Type

32 bitUnsignedInteger

32 bitUnsignedInteger

32 bitUnsignedInteger

Time

Date

String

String

Value Range

0 … 2^32

0 … 999

0 … 999

䍵牲敮琠瑩We⁯nlX

䍵牲敮琠M慴a⁯nlX

P牥-獥慳VnH牥杵污l獥慳VnHp污祯晦Ⱐ獵V敲-bo睬

䅮X⁣U慲a慲牡a

Default Value

None

null

null

Currenttime

CurrentDate

None

null

Nullable?

No

Yes

Yes

No

No

No

Yes

Unique?

Yes

No

No

No

No

No

No

Single orMultipleValue

Single

Multiple

Multiple

Multiple

Multiple

Multiple

Multiple

Simple orComposite

Simple

Simple

Simple

Composite

Composite

Simple

Composite

Page |9

Team



Thisentity provides information on the teams that are bet upon.



Candidate keys:teamID, teamName



Primary key:teamID



Strong/Weak Entity:Strong



Fields to be indexed:teamID

Name

teamID

teamName

city

State

record

streak

Description

An auto-incrementedvalue.

The team’sn慭e

周攠team’s捩瑹

周攠team’s獴慴e

周攠team’s牥捯牤

䄠Aumb敲eo映捯n獥Vu瑩W攠睩湳⁯r汯獳敳

Domain/Type

32 bitUnsignedInteger

String

String

String

String

32 bit

int

Value Range

0 … 2^32

䅮X⁣U慲a慲牡a

䅮X⁣U慲a慲牡a

䅮X⁣U慲a慲牡a

0-0-0⁴oㄶ-ㄶ-ㄶ

-16 … 16

Default Value

None

None

None

None

0-0-0

0

Nullable?

No

No

No

No

No

No

Unique?

Yes

Yes

No

No

No

No

Single orMultipleValue

Single

Single

Multiple

Multiple

Multiple

Multiple

Simple orComposite

Simple

Simple

Simple

Simple

Composite

Simple

Book

Page |10



This entity describes an entity that accepts bets, setsodds, and payswinnings



Candidate keys:bookID,

bookName



Primary key:bookID



Strong/Weak Entity: Strong



Fields to be indexed:bookID

Name

bookID

bookName

address

bank

Description

An auto-incrementedvalue.

The book’sn慭攠⡣慳楮o o爠o瑨敲睩獥w

User’s慤T牥獳

周攠book’s

慶慩a慢汥l慳獥aV

Domain/Type

32 bitUnsignedInteger

String

String

32 bit

UnsignedInt

Value Range

0 … 2^32

䅮X⁣U慲a慲牡a

䅮X⁃桡慲牡a

0 … 2^32

Default Value

None

None

None

None

Nullable?

No

No

No

No

Unique?

Yes

Yes

Yes

No

Single orMultipleValue

Single

Single

Single

Multiple

Simple orComposite

Simple

Simple

Composite

Simple

2.2

Relationship Set Description

Gambles:

This

ternary

relationship

is between the gambler, the

bet, and the game. Every bet musthave one gambler and one

game. Whenever a gambler creates a bet on a game, this relationship

is created. This links the userID with the betID

and the gameID.

-

Mapping cardinality: M..M..M

-

Descriptive field: teamID

-

Participation constraint: mandatory for Bet, optional for gambler

and game

Page |11

Held_By:

This relationship

is between the bet and the book. It describes the entity that managesthe bet, and pays winnings to the gambler. The linking attributes are betID and bookID.

-

Mapping cardinality: M..1

-

Descriptive field: none

-

Participation constraint: mandatory for Bet, optional for book

Plays_In:

This relationship

is between the game and a team. It describes the game a team will playin. It also describes if the team is playing at home and if they win. The linking attributesaregameID and teamID.

-

Mapping cardinality: 2..M

-

Descriptive field: atHome, win

-

Participation constraint: mandatory for game, optional for team.

Odds_On:

This relationship

is between the book and the game entity. It describes which team isfavorited in agame by the teamID, and by what point spread. This information is used whenplacing bets. The linking attributes are bookID and gameID.

-

Mapping cardinality: M..M

-

Descriptive field: favTeamID, pointSpread

-

Participation constraint: optional for book and forgame

Works_For:

This relationship is between the bookie and the book entity. It describes which book abookie works for and can access. This information is used when managing the system. Thelinking attributes are bookID and userID.

-

Mapping cardinality: M..1

-

Descriptive field: position

-

Participation constraint: optional for book mandatory for bookie

Page |12

2.3

Related Entity Set

Gambler



This entity is a subclass specialization of the User entity, and serves the purpose ofstoring a gambler’s payment information.

This entity is a subclass specialization of the User entity, andserves the purpose ofstoring a bookie’s affiliation information. This information will be used to filter bettingreports and management access.

The Relationship Data Model first caught the attention of the programming industry due to itssimplicity and mathematical foundation in a paper written by Ted Codd

of IBM in 1970. In theearly 1980s, the model was applied forcommercial

use as the SQL/DS system. Since then themodel has been widely used in major systems such as SQL Server and Oracle. This model uses acollection of relations to compose the database.

Each relation contains

instances (tuples)

thatare described by the data which they hold. This data is organized into attributes that is furtherspecified by the domain.The result is a logically-simplified theoretical representation of adatabasewhich is therefore easier to convert into an actual database.

1.2

Comparison

The Entity-Relationship model is a highly conceptual model that makes it ideal for the initialdesign of a database.This high-level conceptualization is most useful when representedvisuallythrough an ER diagram. This is perhaps one of the few mediums through which a databasedesigner and a non-technical person of reference

(or users)

can clearly communicate theirunderstandings of the database concept.It then follows that this would be an ideal startingmodel, as any input on the design from non-technical persons from here forward will typicallybe difficult to integrate.

The Relational model is the next step in transforming the conceptualization into an actualdatabase. Each entity and relationship from the previous model becomes a relation. Eachrelation contains attributes that describe the relation. For each instance of the relation there is atuple; each tuple

contains values for the attributes which collectively describe the instance.

While it may be harder to visualize like the E-R Model, the structure and detail of the Relationalmodel is a more explicit iteration of the database concept.

This structure is also closer to thestructure of the implemented database.

Page |16

1.3

Conversion from E-R Model to Relational Model.

The conversion from an E-R Model is not as much a necessary process as it is a natural process.To skip this conversion, one could argue that a lot of time could be saved. The same could besaid if we also skippedthe E-R Model. However, through experience we have learned that this isa very bad idea. We first take the ideas behind a database, and make it a high-level E-R Model.Then we take the high-level E-R Model and convert it to a lower-level Relational Model.Finally,we convert the Relational Model to the actual database. These careful iterations allow us toproperly think through the organization and structure of our database so that the final productis flawless in its implementation.

The conversion of strong entity types is relatively simple; each strong entity becomes a relation.This relation contains all the simple attributes of the entity. A composite attribute is broken intoits simple components.One primary key is chosen, while making note of any other keys ascandidates for indexing.

Weak entity types are similarly handled. Each weak entity becomes a relation with its attributesmade up of the simple attributes of the entity. However, the weak entity also includes theprimary key of the owner entity

type as a foreign key. This, along with the partial key (if any) ofthe weak entity compose the primary key.

Binary 1:1 relationship types

have three approaches that can be used to convert to the relatioalmodel depending on the situation. In real-worldsituations these relationship types are not verylikely to occur. The three approaches are:



Foreign key approach: add the primary key of the other relation to the one that has totalparticipation as a foreign key. Thisavoids having a large number of nullvalues.



Merged relation approach: merge the two entity types and the relation into one relationthat includes the attributes of all its constituents. This is only acceptable when bothentities have full participation.

as attributes. This is ideal of low participationrelationships as it saves us from having a large number of null values in one of therelations.

The foreign keyand relationship relation approach are used for binary 1:N relationships as well.The foreign key approach asks us to add the primary key of the 1-side as a foreign key of the N-side.The relationship relation works the same as before, for each relationship instance we havea tuple containing the primary keys of the two entities. Which approach to use depends on theparticipation of the N-side entity as well as the size of memory each approach uses per tuple. Bymultiplying the number of records by the memory size, we should be able to determine whichapproach is appropriate.

Binary M:N relationship types must also use the relationship relationdue to the cardinality constraints.

Page |17

Multivalued attributes are handled by creating a new relation for the attribute, and assigningeach part of the multi-valued attribute as its own attribute to the relation. The relation can thenbe referenced by a foreign key attribute by any relation that wishes to use it. For N-aryrelationship types, we create a relationship relation that contains all participating relations’primary keys as attributes along with any simple attributes of the relationship type.

For specialization and generalization, we also have multiple options for conversion. Theseoptions are:



Createa relation for the superclass, and a relation for each subclass. Each subclasswould have its attributes union with the superclass. Also, the primary key of thesubclass would be the same as the superclass. This option is acceptable for anyspecialization.



Create a relation for every subclass that has its own attributes as well as the superclass’attributes and primary key. This only works when every superclass entity belongs to atleast one of the subclasses.



Create a single relation that contains all the subclass and

superclass attributes, thesuperclass’ primary key, and a type attribute to specify which subclass to which a tuplebelongs. This option could have many null values if there are numerous subclassattributes and only works if they are disjoint.



Create a single relation as per the previous option; however, create Boolean typeattributes for each subclass type. This option is appropriate for a specialization wheresubclasses overlap as well as disjoint.

When converting a category, you must add a surrogate key if the defining superclasses do notshare a common key. The surrogate key becomes the primary key of the category’s relation, anda foreign key to the superclasses. If the superclasses share a primary key, then we merely usethis as the primary key of the new relation. Now that we have covered various instances in theconversion process, we need to consider constraints.

Page |18

1.4

Constraints

Constrains are limitations we enforce upon a database to ensure that order persists in ouroperations and that nounexpected value occurs

within the data.Entity constraints maintain that notwo tuples are duplicated. This is usually achieved by including a unique primary key to each tuple ina relation; a primary key can be unique but must not be null. Having a unique identifier provides uswith a means to select and compare specific tuples within a relation. Similar is the constraint that areference to a tuple must refer to a tuple and not null. This referential constraint can be takenfurther as a foreign key. A foreign key must have the same domain as the primary key of which itrefers. A foreign key must also exist as a primary key in the reference relation, or be a null value.

Check constrains and business rules allow us to customize a database to the specific application.Values must not exist outside the domain of the business. These constraints keep the data relevantand concise.

Page |19

2.

E-R Database to Relational Database Conversion

Users

Attributes

userID

Domain: unsigned integer: 0 to 2^32-1.Cannot be NULL.

userName

Domain: string. Must be between 6 and 12 alpha-numeric characters long. Cannot be NULL.

fullName

Domain: string. Must be between 4 and 50 characters long. Composite attribute in the format of:lastName, firstName. A comma and space

separate the constituent attributes.

Cannot be NULL.

Address

Domain: string. Must be less than or equal to 75 characters. Composite attribute in the format of:Street 1, Street2, City, State, Zip.Composite is comma delimited.

Cannot be NULL.

emailAddress

Domain: string. Must be a valid email address containing one ‘@’ character and at least one ‘.’character.

Cannot be NULL.

Password

Domain: string. Must be between 6 and 12 characters long. Cannot be NULL.

Constraints

Primary key: userID, must be unique and not NULL.

Business Rule: none of the attributes can be null for payment purposes.

Every userName, address,and emailAddress must be unique.

Every user must be a gambler or bookie.

Candidate Keys

userID, userName

Page |20

Gambler

Attributes

userID

Domain: unsigned integer: 0 to 2^32-1. Cannot be NULL.

routingNo

Domain: string. Must be 9 numeric characters long. Cannot be NULL.

bankAcctNo

Domain: string.Must be less than 20 numeric characters long. Cannot be NULL.

Constraints

Primary Foreign

key: userID, mustbe unique and not NULL.

Must exist in the Users relation.

Business Rule:the bank information must be present in order to charge for bets. The bankAcctNomust be unique for the given routingNo, thus giving a unique bank account.

Candidate Keys

userID

Page |21

Bookie

Attributes

userID

Domain: unsigned integer: 0 to 2^32-1. Cannot be NULL.

position

Domain: string: Must be less than 20 numeric characters long. Cannot be NULL.

The Structured Query Language (SQL) is the standard language for interacting with a DBMS. SQL

allows for a standard, efficient way of using a database management system regardless of thespecific database type. Several variations of the SQL language are

T-SQL, MySQL, and SQL*PLUS.While these may have slight variations between them, they are all the same basic SQL language.SQL*PLUS allows users to executeSQL scripts that run queries. This allows users the ability todestroy and recreate a database in a few seconds.

2.

Oracle Schema Objects

A set of logical data structures or schema objects comprises a schema. Schemaobject are notphysically related in a one-to-one relationship to their physical files. Rather, schema objects arelogically stored within a tablespace of the database and the phisical files are contained in one ormore of the tablespace’s datafiles. Oracle has a number of Schema Objects, the most widelyused being the Table.



Tables

Tables are the most basic unit of storage in the Oracle database. Tuples and attributestake the form of rows and columns.Columns are assigned unique names, a datatypeand a width. The width can be predetermined for some datatypes, but needs to bespecified for others. Rules can be set for tables, called constraints, that limit theacceptable values that go into a column for a given row.



Views

A view is a customizable presentation of the data contained in a table, or presented inseparate views. A view could be considered a virtual table in that it takes the result setof a query and presents it as a table. For the most part, you can operate a view like youwould a table, with some restrictions on the update, insert, and delete operations. Aview is stored as only its definition (the query) and so takes very little space in thedatabase.



Dimensions

A dimension declares the hierarchical relationship between columns. It is a container oflogical relationships between columns, and does not have any data storage assigned toit. If adimension

is denormalized, the columns will come from the same table.Conversely, if they are from multiple tables the dimension is considered to befully orpartially normalized.

Page |41



Sequence Generator

A sequence generator allows for

faster throughput in a multiuser environment. Thesequence generator avoids the serialization necessary when two users are inserting andwaiting for sequential numbers at the same time. Thus the user’s wait time is reduced.Oracle stores the definitions for all of a database’s sequences in the SYSTEM tablespaceas a single dictionary table. The sequence numbers are generated independently oftables, and therefore can be reused within a database.



Synonyms

A synonym is an alias for any other Schema object,even a synonym. They require nomore storage than their definition in the data dictionary. There are both public andprivate synonyms, depending on who has access to it. The can be used to shorten thestring used to access a schema object, or to hide its identity or location in a schema.



Indexes

Indexes are optional structures used to enhance the access time associated with a table.Indexes store associations between columns based on a specific logical indexingscheme.They are best used only on the columns that are frequently used to identify andreturn rows, and do little good on columns that contain frequently repeated data. Whileindexes help with information retrieval, they can cause increased latency in insertion.



Database Links

A database link can be thought of as a pointer to a constant database server. They are aread-only link that allows users to access the information on another server, howeverthey cannot manipulate the data on that server. They are useful for access informationwithout being an actual user of the remote database.



Stored Procedures and Functions

Stored procedures in Oracleare PL/SQL procedures and operate much like a crossbetween a function and a query. Functions accept parameters and return a scalar value.Stored procedures

also accept parameters, however they return a result set, much like atable or view.



Packages

Packages are a construct of PL/SQL objects (such as procedures, variables, cursors, orfunctions). A package has two parts: the specification and the body. The

specificationdefines what objects comprise the package, while the body implements the objects inthe code. Their purpose is to execute as a single instruction.

Page |42

3.

Relation Schemas and Instances

Tables in the project were created in a similar format tothe relation example below:

CREATE TABLE NK_Team

( teamID

number(10)

PRIMARY KEY,

teamName

varchar2(25)

unique not null,

city

varchar2(25)

unique not null,

state

varchar2(25)

unique not null,

record

varchar2(25)

not null,

streak

number(10)

DEFAULT 0)

ENABLE PRIMARY KEY USING INDEX TABLESPACE cs342index;

Relation names are directly related to table names in that: NK_RelationName. The following arethe schemas and instances of the relational database implemented: