-
Notifications
You must be signed in to change notification settings - Fork 0
/
README
146 lines (119 loc) · 11.3 KB
/
README
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
Corey Caplan
CSE 341-010
May 6, 2016
Final Project
Hello, and thank you for taking the time to look at my project for CSE 341. I put a lot of time into it and came across
some major blunders and "eureka" moments along the way.
With respect to the ER diagram, I realized after the submission that I forgot to add two minor things that more
accurately reflect the state of my database. Firstly, in the "STOCKS" relationship set between "STORE" and
"PHONE_MODEL" I forgot to add the attribute "QUANTITY" which would reflect the amount of each "PHONE_ID" found in each
store. The other minor thing is that I accidentally added a relationship set between "TEXT_LOG" and "CALL_LOG" that is
not actually in existance in my database. Since there is no gauarantee that the receiver or sender of the text/call is
a Jog Wireless phone, I couldn't add a relationship between those two entity sets and the "SERVICE" entity set. In SQL,
this would mean that I did not add any foreign keys to the "TEXT_LOG" or "CALL_LOG" tables that link them to a phone
number in the "SERVICE" table.
A different blunder I ran into when I was nearing the end of the project was that I should not have calculated quantity
the way I did for each store. Having a quantity attribute is really redundant and introduces a lot of complexities with
ensuring that this number stays in-line with the actual amount of phones that Jog has in inventory. Moreover, you
cannot keep track of each individual phone (which store in which it's located), because there is no way to join the
tables in such a way that verifies the "QUANTITY" for a "PHONE_ID" at a given store matches the actual amount in the
store. Rather, what you can check is the TOTAL inventory for each "PHONE_ID" and even so, my numbers don't align
properly. I used PL/SQL for checking and updating inventory. Thus, to the best of my ability, I tried to keep the
"real" and "reported" quantity as close as possible.
Here are some bottle-necks or choking points I realized with my current setup/code base:
Since billing is setup to be as realistic as possible and still provide the graders the opportunity to test many
billing periods, I found billing to be very challenging. At the moment, whenever a text gets sent or received, a
call gets made, or internet gets used, the "BILL" table updates. Based upon the start time of the call, the time
the text was sent, or the time the internet was used, the corresponding billing period will get inserted/updated.
Billing periods span from the first of the month at 00:00:00 (HH:MM:SS) until the last day of the the month at
11:59:59 (HH:MM:SS). As a result, there are some irregularities when you access Jog's cash collected or accounts
receivable at different billing periods. For example, if a bill's plan is charged a fixed rate throughout the month
and a text gets sent on 2014-05-02 (YYYY-MM-DD), the CEO will see an accounts receivable for that bill as
((2/31) * fixed_rate) where the 2 corresponds to the second day in the month and the 31 corresponds to the total
number of days in the month, multiplied by whatever the fixed rate is. Likewise, when an account views its
usage/billing information, this usage is dependent upon the last text/call/internet usage date.
At the moment, there is no way create more phone numbers, and all phone numbers in the database are permanent.
Meaning, each phone number is never deleted from Jog's database. So if a customer were to leave Jog for another
competitor, the phone number could never leave the company. Doing so wouldn't be a big deal to implement properly
because my "on delete" attributes are setup to work just fine. However, I just didn't put the extra handling in to
manage these strange scenarios. Eventually (if you create enough accounts), Jog will run out of phone numbers.
Until I was about 75 percent of the way through of writing all of the SQL queries necessary to execute the
transactions for the interactive and streaming interfaces, I did not use prepared statements. I completely forgot
about them, and I decided it wasn't worth using them and refactoring my codebase after I had gone through the
effort of writing so much code beforehand. Because I used raw queries for excecuting all of my basic queries,
Professor Korth may be thinking that it'd be fun to mess with me and try injecting some SQL into my string input.
Thankfully, I thought of major input sanitizing blunders such as this and as a result, I am very conservative when
I take string input. At the moment, string input that contains anything but a character, number, parenthesis,
underscore, dash, ampersand, or comma is considered to be invalid. This may lead to hiccups such as names that
contain apostrophes, but I figured that my conservatism would more rewarding than limiting since it would reduce
the likelihood of potential SQL disasters.
I never bothered using Timestamps for keeping track of time with transactions. Rather, all of my tables used the
"Date" attribute whenever a date was needed. I found that there were plenty of useful functions that helped with
manipulating the date and made validating it (when I had to sanitize user input) fairly straight forward and easy.
When you go over your monthly limit for texts, calls, or internet usage, it was hard to distinguish between a large
usage going over the limit or barely going over the limit. So, for example, if it's the first day of the month and
you decide to use 3 gigabytes with a 2 gigabyte limit, then the error message will print out "You cannot use
anymore data for the month. You reached your monthly limit!" Technically, this isn't correct though because the
account really has all 2 gigabytes left. So, keep that in mind if you try to use a large amount of data at once in
the beginning of the billing period.
Some cool things I added into the assignment:
The ability to make customizable plans. When using the CEO interface, one of the options is creating a plan that
allows for a decent amount of creativity when it comes to the different ways you can charge a customer. For
instance, you can specify your plan has a hard limit so if the user goes over the limit for texts, calls, or
internet, the account's service gets suspended for the given category. Moreover, there are different ways to charge
overdraft fees, and charged the account a fixed rate. Definitely try it out!
The CEO interface also comes with the ability to view the details of Jog's financial information. You may simply
view jog's accounts receivable or cash collected, or you may get an in-dept breakdown of it as well.
You may create/add new phone models (in the CEO interface), which will be instantly added to the inventory of every
Jog store.
Inventory is a unique and intricate thing to craft for a database. So, to simplify things, I added the option for
store managers to view their store's inventory and buy more from the online store. Upon replenishing their
inventory, the online store always checks if it's balance is below zero and replenishes itself to a preset value.
I used a lot of PL/SQL since it made complicated queries easier and made sitautions with multiple insertions,
updates and/or deletions simpler. You'll find that some of the procedures get pretty complicated and some were
"misused". Since ORACLE SQL doesn't have the ability to directly auto-increment column values, I thought it'd be
clever to use PL/SQL to do it for me. So, when I add a new customer ID, I just get the "MAX" of the customer ID
attribute from the customer table, put it into a variable, add one to it, and insert a new tuple with it's new
values.
My "Stream Input" interface goes fairly in-depth when it comes to outputting errors for the files. Whenever you
send/receive a text, make a phone call or use the internet, basic information gets outputted to the console.
However, more detailed events about what happened for each inputted file can be found in the "error.log" file and
the "usage_information.log" file. Successful transactions are outputted to "usage_information.log" and failed
transactions are outputted to "error.log". You can find both of these files as well as sample usage files in the
"usage" folder. Lastly, you'll find a file that contains all of the phone numbers that are tied to an account. This
is because if you attempt to use phones that aren't tied to an account, you'll, encounter an error (this error only
occurs when the source and destination number isn't a Jog phone number for texts and calls, or the source number
isn't a Jog phone number for internet usage).
Getting Started with the Program
You'll find that everything necessary for the project is inside of the folder "cdc218". In this folder you will
find several packages which contain my code, the "cdc218.jar" (executable) file, a "usage" folder that contains
files for the "Stream Input" interface, and a Makefile. Please note, if you would like to re-compile my code, you
MUST use the provided Makefile or else it won't compile. Since I used inner classes and different packages, making
the code compile without the luxury of an IDE became a fairly complex challenge. As a result, in order to avoid
giving you a headache and prevent me from receiving a 0 on the executable portion of the project, I ask again,
please use the Makefile for re-compiling my code.
When you first run the cdc218.jar executable and enter my password, I recommend starting with the very first
interactive interface (a customer walking into a store) and working your way down. They're numbered from 1 to 8,
where number 8 is the "Stream Input" interface, and everything else is considered to be an "interactive" interface.
Here are some fun customers you should consider trying out:
Corey Caplan - participates in multiple accounts, and is an owner for a few of them
Lee Caplan - Owns no accounts and participates in a few
Joel Caplan - He is the only owner of a corporate account
Note, certain portions of the program are split up into "residential" and "corporate" transactions. These
transactions correspond to residential and corporate accounts. These two different types of accounts do not mix
together, so a phone that is tied to a corporate account is not accessible in the "residential" interfaces and vice
versa. I thought that Jog for Business should be a separate entity from Jog for Residents.
After the first interface, you might want to jump over to the seventh interface which allows you to use the account
by sending/receiving texts, making phone calls, and using the internet. Then, you can go back to the first
interface and view how your usage and charges have changed.
Beyond those two recommendations, I think that you should just explore the different things that you may do and
mess around with it in interesting ways. Maybe you can go to the CEO interface, create a new billing plan and
phone. Then, you can back to the first interface and create a new account that uses the new plan and phone.
I hope that you enjoy using and reviewing this project as much as I enjoyed creating it, and I'm looking forward to
viewing your feedback on the assignment.
Data Sources
All of my data for the entity sets was generated using generatedata.com. This website provided an easy way to
populate entity sets with dummy data.
Some of the entity sets/relationship sets were populated using PL/SQL, but all of that code is viewable on Edgar1.
Everything else was done using my interfaces in the executable portion of the project.