5VSves99

1ISM 6218 Advance Database Management
2Project Report 
3on
4BullFlix, a Movie App (with additional functionality of messaging)
5
6
7
8
9
10
11
12
13
14
15
16
17
18Table of Contents
19
201.	Executive Summary………………………………………………………………………………3
212.	Assumptions…………………………………………………………………………………………4
223.	Logical Design……………………………………………………………………………………….6
234.	Capacity Planning………………………………………………………………………………….9
245.	Data Generation…………………………………………………………………………………..10
256.	DDL Queries………………………………………………………………………………..……….16
267.	Data Integrity………………………………………………………………………………..……..23
278.	Performance Tuning………………………………………………………………..…………..26
289.	Stored Procedure…………………………………………………………………..…………….30
2910.	 DBA Scripts……………………………………………………………………………..…………..31
3011.	 Interface Design………………………………………………………………………….……...36
3112.	 Data Visualization………………………………………………………………………….…...39
3213.	 Queries………………………………………………………………………………………….…...45
33
34
35
36
37
38
39
40Executive Summary:
41The BullFlix database has been developed to provide an application to users through which they can search movie names and released year, awards and genres, cast and directors, theaters and their location. We have also come up with a design for this database where users would be able to follow other users, named as “fans”. 
42They can like the description given by another fan or can send the friend request to another fan. Once, a fan accepts the friend request send by another user, these both users would be able to send instant message to each other. This facility is only available to “friends”.
43For this project, we have shown how we were able to gather data, the logical design. We have performed the integrity checks, optimization and have shown the DBA scripts and Visualization.
44We have given following weights to these activities:
45
46Topic Weights:
47Topic Area	Description	Weight
48Database Design:	Logical Design, Data Generation, Data Integrity, DDL Statements	30
49Query Writing: 	Queries, Stored Procedure	25
50Performance Tuning: 	Indexing, Btree, Bitmap	15
51Other Topics: 	Capacity Planning, DBA Scripts, Interface Design, Data Visualization	30
52
53
54
55
56Assumptions:
57We are assuming following things in designing our BullFllix app. 
581.	[MESSAGE] table will contain information about sender (MES_SENDER_FAN_ID), message body, subject (optional), date when the message was created (automatically time stamped), message expiry date (optional) to allow several attempts to deliver message and drop it if unsuccessful. This attribute can be uniformly set automatically (e.g. to 1 year or never) or manually.
59
602.	[MESSAGE] also allows creation of “threads” where a flow of messages (both peer-to-peer or chat groups) may be organized in threads. A special attribute (MES_PARENT_MESSAGE_ID) represents unary relation that may anchor a group of messages to a “parent” message.
61
623.	[MESSAGE] table has no information about who a message was sent to. [MESSAGE_RECIPIENT] table was created to accommodate one to many nature of group chats (please see p. 4 and 5 below). When a sender sends a direct (peer-to-peer) message to recipient, information on such recipient is stored in [MESSAGE_RECIPIENT] table under (MR_Message_Rec_Fan_ID) attribute along with time stamp of when a message was created. Although we assume that only “friends” can exchange direct messages it is not explicitly imbedded in a DB design.
63
644.	Information on chat groups is stored in [GROUP_CHAT] table. When a fan creates a chat group this information is inserted into [GROUP_CHAT] table (one tuple per every group chat existing in the system), where title, creation date and “active” flag are stored. List of participants of every group is stored in [GROUP_CHAT_LIST] table.
65
665.	When a group message is sent a row is inserted into [MESSAGE_RECIPIENT] with (GCL_Group_ID) attribute. MR_Message_Rec_Fan_ID is null in that case, and vice versa GCL_Group_ID is null for direct chat. [MESSAGE_RECIPIENT] may be divided into 2 tables: one for direct chat and another for group chat, but since no additional options will be introduced (other than direct or group) and to have a more compact and clear design we assume that a column of “wasted” values for each message is not a big cost.
67
686.	[FRIEND_REQUEST] table keeps track of all interactions among fans with respect to establishing friends’ relationships. There are only 4 types of requests allowed. Initially a fan (REQ_SENDER_FAN_ID) can send a “friend request” to any other fan (REQ_RECEPIENT_FAN_ID). That recipient fan can either “accept request”, “reject request” or simply ignore. Later if request is accepted and when relationship is established either “friend” can “unfriend” the other one. That type of request is defined in (RET_REQUEST_TYPE_TITLE) attribute which is a foreign key from a separate [REQUEST_TYPES] table.
69
707.	Running SQL queries on [FRIEND_REQUEST] we may determine if a friend status exist for any pair of fans at any particular time. For example, to determine if Alice and Bob are friends we can select rows from [FRIEND_REQUEST] table where (REQ_SENDER_FAN_ID) is Alice or Bob and  (REQ_RECEPIENT_FAN_ID) is Bob or Alice, look at the last row’s (RET_REQUEST_TYPE_TITLE) attribute. If it says “accept request” then a friend relation exists. If it is either one of “friend request”, “reject request” and “unfriend” or the tuple does not exist than there is no friend relation.
71
728.	We have assumed that many movies can be shown in a single theater and many theaters may show a particular movie. We store date and time of respective shows along with Movie and Theater IDs. However, at this point we do not plan to utilize a possibility that a movie may be shown in a particular theater at 2 or more screens at the same time and date. 
73
749.	We assume that our customer is interested in schedule first. We think it is a viable compromise for our app since otherwise we would be dealing with unstructured data from many theaters and would have to account for physical peculiarities of the venues and normalize it to be used in a single search output.
75
7610.	We will store theaters' physical location as GPS coordinates in our Theaters table. Additional fields concerning usual address format (street, city, state, zip) will be stored in a separate table. 
77
7811.	We assume that only one theater may be located at one set of GPS coordinates. 
79
8012.	We further assume that we might be able to use third party services to populate and update (as needed) our Theaters Locations table. GPS coordinates will not change, but once our DB grows older our street address may become outdated because of changes in street names, expansion overseas (with different address structure) and other reasons. 
81
8213.	 Our DB can store Theaters' ratings by fans registered in the system (existing Fans table). 
83
8414.	We assume that any fan may rate any number of Theaters and add a written review (many to many relation). 
85
86
87
88
89
90
91
92Logical Design:
931.	The logical design for BullFlix, with an additional capability of “Theatres”:
94
95 
96
97
98
99
1002.	Whereas the logical design for BullFlix with functionality of “Friends and followers”:
101 
102
103
1043.	The logical Design of the whole BullFlix consolidated into one: 
105
106 
107
108In the above ERD diagram, we have displayed the original design in Green, the theatres in Red and in Blue the functionality of messaging.
109
110
111
112Capacity Planning:
113Capacity planning is done in order to utilize physical resources like disk space, memory, CPU, storage space etc. effectively. We plan this by keeping business requirements in mind. For instance, what would be the growth rate of tables in future, how much extra spce we should keep etc.
114Below we are going to show the available and allocated spaces in our database.
115Select * FROM DBA_TEMP_FREE_SPACE
116 
117From the above screen shot we can see how much free space we have. Now, we are going to see how much space is consumed by each table.
118SELECT table_name, num_rows, avg_space, avg_row_len
119from all_tables
120where owner = 'RELMDB'
121 
122
123From the above table we can see that the space utilized by each table in relmdb database. Here, we are going to calculate the space requirement for fans and movies table.
124We can see that average row length for fans is 254. At present we have 7655 fans. So, together all these fans will take 254 * 7655 = 1944370 bytes. Similarly, movies table will require 271 * 283 = 76,693 bytes.
125On an average if we say that 500 movies are added every year and 1000 fans, based on this our database tables will grow very fast. For this we should look for range portioning or distributed database.
126
127
128
129Data Generation:
130To load data into our tables, we have generated data sets using Excel. Using this data, we were able to simulate what an application’s social media elements could produce. 
131Message and message recipient tables
132One of the most important tables that tied together communications among fans is the MESSAGE table. Core of MESSAGE table is MES_MESSAGE_BODY attribute that is to store texts of the messages sent among fans. We further intended those messages to be ready for text sentiment analysis (see p. Sentiment analysis to gauge users’ evaluation of movies) so it could not be a mere random combination of characters. To satisfy that condition we came up with the following requirements to the strings making MES_MESSAGE_BODY attribute:
1331.	Messages must be compiled of English words.
1342.	Total number of messages to be generated was arbitrarily chosen to be 5000.
1353.	Messages must be of a variable length uniformly distributed in a range from 1 to 20 words.
1364.	Some portion of all messages (arbitrarily chosen to be 30%) shall contain movies’ titles as part of the message body. Such messages containing titles shall be randomly distributed among all messages. Title to be mentioned in such messages shall be randomly chosen from a list contained in MOVIE table (283 movies). Position of the title (among all words making such message’s body) must be randomly chosen from 1 to message’s length.
137To satisfy the abovementioned requirements, and specifically p. 1, a population of 20 words was created to be used in messages:
138good 	cool 	not a fan 	bad 	worst 
139disgusting 	hello 	what 	when 	I 
140you 	like 	want 	grab 	food 
141address 	car 	pick up 	director 	know 
142Words were chosen to include typical vocabulary that fans discussing movies and their plans would use. Though 20 words is obviously a “poor man’s version” of what actual vocabulary would be, it was used as an example and can be extended.
143MS Excel, including its random(), norm.inv() and other functions, was used to put together the strings for MESSAGE table. In the picture below a screenshot of our working spreadsheet is presented.
144
145 
146
147Legend for the screenshot above:
148 
149Message size is determined. It is randomly chosen from 1 to 20 using Excel’s randbetween() function.
150 
151Based on the size of the message (determined at step 1) a position of the movie title is determined randomly from 1 to message size using Excel’s randbetween() function.
152 
153Movie title is selected from the list of 283 movies contained in MOVIE table using Excel’s randbetween() and lookup functions.
154 
155Flag is set to TRUE (1) if a message shall contain a movie title, selected at step 3, and put to position selected at step 2 (about 30% of the time).
156 
157Set of words that can be used in constructing a message.
158 
159Based on size of the message, determined at step 1, words are randomly selected from the set (see step 5) using Excel’s randbetween() and lookup functions.
160 
161All words selected at step 6 are concatenated to form a string using Excel’s concat() function.
162 
163Several base inputs like maximum message size are outlined in that control form.
164
165After creation of messages’ bodies, the rest of the attributes of MESSAGE table were generated. Screenshot bellow shows a part of generated table.
166 
167
168Legend for the screenshot above:
169
170 
171Message identifier, determined as a positive integer, keeping track of number of messages.
172 
173Message subject is decided to be a movie title in case such title is present as one of the words in a message body; null if no movie title is mentioned in a message.
174 
175Sender is selected randomly from a list of fan’s IDs of the FAN table.
176 
177First 20 messages were arbitrarily chosen as the only messages that could be “parent” messages to other messages to simulate possible “threads” of messages. It was further assumed that each time a sender (see step 3) who sent one of the first 20 messages shows up as a sender in other message, such message is considered to be a part of “thread” and a MES_PARENT_MESSAGE_ID is set accordingly.
178 
179Message body, generated at previous steps as shown above.
180 
181On a separate sheet distribution of messages by time was determined. Such distribution was set to obey 3 assumptions: a) distribution within a week: Mon - 7%, Tue - 11%, Wed – 10%, Thu - 29%, Fri - 21%, Sat - 18%, Sun ¬5%; b) weeks 15 through 30 shall have 2 times more messages than weeks 1 through 15 and 50% less messages than weeks 30+; c) actual number of messages for a specific date shall be randomly determined assuming normal distribution with mean equals to calculated based on previous 2 assumptions and standard deviation of 1 using Excel’s random() and norm.inv() functions.
182 
183Messages are set to never expire by setting expiration date to Jan 1, 2099.
184
185MESSAGE_RECIPIENT table was generated the following way: 4900 messages out of 5000 were set aside to be peer-to-peer messages between friends. Random friends pair were selected to attributeв ещ those messages. 100 messages out of 5000 were supposed to illustrate group messaging. Groups IDs were selected manually to evenly distribute 100 messages across 5 existing groups (see p. Groups and Friends tables).
186
187Groups and Friends Tables
188GROUP_CHAT was manually populated with 5 groups (“Tarantino movies”, “Matrix trilogy”, “I'm gonna make him an offer he can't refuse”, “Paulie from Goodfellas” and “Not a fan of Scarface”) all created in 2016.
189GROUP_CHAT_LIST was manually populated with 5 groups’ members. Each group got 5 members, that were comprised of fans with FAN_Fan_ID equal to 1 through 25.
190FRIEND_REQUEST table was populated to showcase a typical activity of fans willing to establish connections among each other. First illustrated case: fans with ID = 3, 8 and 16 sent requests (RET_REQUEST_TYPE_TITLE is “Send friend request”) to randomly chosen fans (41, 43 and 94 fans respectively). Each of those requests is answered with acceptance (RET_REQUEST_TYPE_TITLE is “Accept friend request”). Second case illustrated is fan with ID = 22 sent friend requests to 76 other fans but is answered with rejection (RET_REQUEST_TYPE_TITLE is “Reject friend request”). Finally, fan with ID = 3 “unfriends” 2 of his or her friends. All requests take place during January 2016.
191
192
193Followers, Movie Ratings and Likes Table
194‘FOLLOWER’ table was generated using similar techniques employed for MESSAGE table. Below a screenshot of our working model is presented.
195 
196
197Legend for the screenshot above:
198 
199On step 1 a random list of fans (followees) was generated with a number of items arbitrarily set to 364 or about 5% of the total number of registered fans using Excel’s randbetween() function. 
200 
201Random number of followers in a range of 1 to 20 was assigned to each followee using Excel’s randbetween() function.
202 
203Random fans’ IDs were assigned to each followee accounting for total number of followers such followee has (see step 2).
204
205
206After a matrix of follower-followee relations was built, it was dynamically transposed into 2 columns for FOL_FOLLOWER_FAN_ID and FOL_FOLLOWEE_FAN_ID attributes of the FOLLOWER table. Date of statuses was randomly chosen sometime in January of 2016.
207MOVIE_RATING table was generated to illustrate functionality that allows followers to “like” ratings and descriptions made by their followees.
208 
209
210Legend for the screenshot above:
211
212 
213Rating identifier, determined as a positive integer, keeping track of number of ratings.
214 
215Random fan ID selected from the FAN table using Excel’s randbetween() function.
216 
217Random movie ID selected from the MOVIE table using Excel’s randbetween() function.
218 
219Random numerical rating normally distributed with mean equals to movie’s IMDB rating and standard deviation of 4 using Excel’s random() and norm.inv() functions. Further adjusted to remain within 0 to 10 range using Excel’s min() and max() functions.
220 
221Rating description randomly generated by the same algorithm employed for MES_MESSAGE_BODY attribute of the MESSAGE table. Word bank was somewhat changed and included the following: good, cool, not a fan, bad, worst, disgusting, best, never, script, action, recommend, fake, real, president, gang, guns, car, happy end, director, know.
222 
223Date was randomly selected within 2016.
224 
225No favorite flag was set.
226
227After generating data, the next step was to create tables and load this data into those tables. For this step, we have given DDL queries below.
228DDL Queries:
2291.	Message Table
230CREATE TABLE "DB204"."MESSAGE" 
231   (	"MES_MESSAGE_ID" NUMBER(4,0), 
232	"MES_SUBJECT" VARCHAR2(128 BYTE), 
233	"MES_SENDER_FAN_ID" NUMBER(6,0), 
234	"MES_PARENT_MESSAGE_ID" NUMBER(4,0), 
235	"MES_MESSAGE_BODY" VARCHAR2(256 BYTE), 
236	"MES_CREATE_DATE" VARCHAR2(26 BYTE), 
237	"MES_EXPIRE_DATE" VARCHAR2(26 BYTE)
238   ) SEGMENT CREATION IMMEDIATE 
239  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
240 NOCOMPRESS LOGGING
241  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
242  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
243  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
244  TABLESPACE "STUDENTS" ;
245 
246
2472.	Follower Table
248  CREATE TABLE "DB204"."FOLLOWER" 
249   (	"FOL_FOLLOWER_FOLLOWEE_ID" NUMBER(4,0), 
250	"FOL_FOLLOWER_FAN_ID" NUMBER(6,0), 
251	"FOL_FOLLOWEE_FAN_ID" NUMBER(5,0), 
252	"FOL_FOLLOW_STATUS" VARCHAR2(26 BYTE), 
253	"FOL_FOLLOW_START_DATE" VARCHAR2(26 BYTE), 
254	"FOL_FOLLOW_STATUS_UPDATE_DATE" VARCHAR2(26 BYTE)
255   ) SEGMENT CREATION IMMEDIATE 
256  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
257 NOCOMPRESS LOGGING
258  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
259  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
260  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
261  TABLESPACE "STUDENTS" ;
262
263 
2643.	Friend Request Table
265CREATE TABLE "DB204"."FRIEND_REQUEST" 
266   (	"REQ_REQUEST_ID" NUMBER(4,0), 
267	"REQ_SENDER_FAN_ID" NUMBER(6,0), 
268	"REQ_RECEPIENT_FAN_ID" NUMBER(6,0), 
269	"RET_REQUEST_TYPE_TITLE" VARCHAR2(26 BYTE), 
270	"REQ_REQUEST_DATE" VARCHAR2(26 BYTE)
271   ) 
272SEGMENT CREATION IMMEDIATE 
273  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
274 NOCOMPRESS LOGGING
275  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
276  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
277  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
278  TABLESPACE "STUDENTS" ;
279
280 
281
2824.	Group Chat Table
283CREATE TABLE "DB204"."GROUP_CHAT" 
284   (	"GC_GROUP_ID" NUMBER(3,0), 
285	"GC_NAME" VARCHAR2(128 BYTE), 
286	"GC_CREATE_DATE" VARCHAR2(26 BYTE), 
287	"GC_IS_ACTIVE" NUMBER(3,0)
288   ) SEGMENT CREATION IMMEDIATE 
289  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
290 NOCOMPRESS LOGGING
291  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
292  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
293  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
294  TABLESPACE "STUDENTS" ;
295 
296
2975.	Likes Table
298  CREATE TABLE "DB204"."LIKES" 
299   (	"LIK_LIKE_ID" NUMBER(4,0), 
300	"FOL_FOLLOWER_FOLLOWEE_ID" NUMBER(6,0), 
301	"MR_MOVIE_RATING_ID" NUMBER(6,0), 
302	"LIK_LIKE_DATE" VARCHAR2(26 BYTE)
303   ) SEGMENT CREATION IMMEDIATE 
304  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
305 NOCOMPRESS LOGGING
306  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
307  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
308  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
309  TABLESPACE "STUDENTS" ;
310 
311
3126.	Group Chat List Table
313CREATE TABLE "DB204"."GROUP_CHAT_LIST" 
314   (	"GCL_GROUP_ID" NUMBER(4,0), 
315	"GC_GROUP_ID" NUMBER(3,0), 
316	"FAN_FAN_ID" NUMBER(4,0), 
317	"GCL_CREATE_DATE" VARCHAR2(26 BYTE), 
318	"GCL_IS_ACTIVE" NUMBER(3,0), 
319	"GCL_CHAT_ADMIN" NUMBER(3,0)
320   ) SEGMENT CREATION IMMEDIATE 
321  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
322 NOCOMPRESS LOGGING
323  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
324  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
325  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
326  TABLESPACE "STUDENTS" ;
327
328 
329
3307.	Message Recipient Table
331  CREATE TABLE "DB204"."MESSAGE_RECIPIENT" 
332   (	"MR_MESSAGE_REC_ID" NUMBER(4,0), 
333	"MES_MESSAGE_ID" NUMBER(4,0), 
334	"GCL_GROUP_ID" VARCHAR2(26 BYTE), 
335	"MR_MESSAGE_REC_FAN_ID" NUMBER(6,0), 
336	"MR_IS_READ" NUMBER(3,0)
337   ) SEGMENT CREATION IMMEDIATE 
338  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
339 NOCOMPRESS LOGGING
340  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
341  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
342  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
343  TABLESPACE "STUDENTS" ;
344
345 
346
347
3488.	Request Types Table
349CREATE TABLE "DB204"."REQUEST_TYPES" 
350   (	"REQUEST_TYPES" VARCHAR2(26 BYTE)
351   ) SEGMENT CREATION IMMEDIATE 
352  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
353 NOCOMPRESS LOGGING
354  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
355  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
356  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
357  TABLESPACE "STUDENTS" ;
358 
359
360Data Integrity:
361We have applied integrity checks on our tables so that data remains in a consistent state. To begin this, first we have assigned primary keys on the tables and then we have made appropriate foreign keys where ever it was required.
3621.	Primary Key Constraint on Message Table
363ALTER TABLE message
364ADD PRIMARY KEY (MES_MESSAGE_ID);
365
366 
3672.	Primary Key Constraint on Group_Chat_List
368ALTER TABLE GROUP_CHAT_LIST
369ADD PRIMARY KEY (GCL_GROUP_ID);
370
371 
372
373
3743.	Foreign Key Constraint on Message Recipient 
375ALTER TABLE message_recipient
376ADD FOREIGN KEY (mes_message_id) REFERENCES message(mes_message_id);
377
378 
379
3804.	Foreign Key Constraint on Group Chat List
381ALTER TABLE GROUP_CHAT_LIST
382ADD FOREIGN KEY (GC_GROUP_id) REFERENCES GROUP_CHAT(GC_GROUP_ID);
383
384 
385
386
387
3885.	Foreign Key Constraint on Friend Request
389ALTER TABLE FRIEND_REQUEST
390ADD FOREIGN KEY (REQ_SENDER_FAN_ID) REFERENCES FAN(FAN_ID);
391
392 
393
394
395
396Performance Tuning:
397To perform optimization on our database, we have done indexing on our tables. The below examples will show performance without an index and after indexing. We have also performed B-tree indexing and Bitmap indexing.
398Case 1: Query without an Index
399We have written a query to fetch film title results that contains the word ‘Lord’ in them. Without an index, we see the cost as 4 and session logical reads 956.
400SELECT film_title
401FROM movies
402WHERE film_title like '%Lord%';
403
404 
405
406Case 2: Querying with a B-Tree Index
407To demonstrate that indexes reduce cost or session logical reads, we created a B-tree index and ran the same query. The cost has come down to 2 and session logical reads have reduced to 497.
408 
409
410Case 3: Querying using Bitmap Index
411To demonstrate that bitmap indexing lower downs the cost, we have ran the same query with Bitmap Index. We see in the result that cost is 2 and session logical reads have further come down to 10.
412
413 
414
415
416
417Case 4: Querying using Complex Queries
418To study this, we are going to query first without an index and then after creating an index.
419
4204.1 Query without Index
421CREATE TABLE casts AS SELECT * 
422FROM RELMDB.casts;
423Select distinct movies.film_title, casts.cast_member
424from relmdb.movies, relmdb.casts
425where movies.Film_ID = casts.Film_ID and (cast_member = 'Tom Hanks' or cast_member = 'Tim Allen')
426order by film_title;
427
428 
429
430From the above query, we have got cost for running the statement as 8 and session logical reads as 143. In the next step we will create an index and will compare the result.
431
432
4334.2 Query with Index:
434We are creating an index on cast_member column and will execute the same query from the above step.
435CREATE INDEX cast_member_btree ON casts (CAST_MEMBER);
436We can easily conclude from the below result that session logical reads has reduced significantly ( it has come down to 15 from 143 ) even if the cost of statement is same as the previous step.
437
438 
439
440
441
442
443
444
445
446Stored Procedure:
4471.	To validate username and password, following procedure is required:
448CREATE proc log_in(username IN VARCHAR2, password IN VARCHAR2) 
449RETURN VARCHAR2 
450AS count_match NUMBER; 
451BEGIN SELECT COUNT(*) 
452INTO count_match 
453FROM ACCOUNT 
454WHERE fan_id=username AND password=abc; 
455IF count_match = 0 THEN dbms_output.put_line('Wrong username or password'); 
456ELSIF count_match = 1 
457THEN dbms_output.put_line('Login successful'); 
458ELSE dbms_output.put_line('Try Again'); 
459END IF;
460END;
461
4622.	To find out movie rating by a fan:
463create proc MovieFanRating (
464FILM_TITLE VARCHAR2(80),
465FAN_ID VARCHAR2(10,0))
466As
467BEGIN
468SELECT FILM_ID, FILM_TITLE, FILM_YEAR,
469(SELECT IMDB_RATING FROM FAN_RATINGS fanR
470WHERE FanR.FAN_ID = FAN_ID AND FanR.FILM_ID = move.FILM_ID
471) USER_RATING,
472(SELECT COUNT(*) FROM FAN_RATINGS fr
473WHERE fr.FAN_ID = FAN_ID AND fr.FILM_ID = move.FILM_ID
474) IS_RATED
475FROM MOVIES move
476WHERE FILM_TITLE LIKE '%' + FILM_TITLE + '%'
477ORDER BY FILM_TITLE
478END;
479
480
481
482DBA Scripts:
483DBA scripts are run by database administrators to for monitoring the database. The scripts can be run at server side known as shell scripts or can be run at sql developer. In our project, we are focusing on sql developer scripts.
484
4851.	To query information on sessions, following script is required:
486SELECT statsname.name, sstatistics.value FROM v$sesstat sstatistics, v$statname statsname, v$session sen 
487WHERE sstatistics.statistic# = statsname.statistic# AND sen.audsid = SYS_CONTEXT ('USERENV','SESSIONID') AND sen.sid = sstatistics.sid ;
488
489 
490
491
4922.	To check default passwords, the query written below is used:
493SELECT a.username, b.account_status
494FROM   dba_users_with_defpwd a
495       JOIN dba_users b ON a.username = b.username
496ORDER BY 1;
497 
498
4993.	For backing up purposes, a DBA can record path of the directories in a database with this script:
500SELECT directory_name, ORIGIN_CON_ID, directory_path 
501FROM dba_directories;
502
503 
504
505
5064.	To identify a locked session, the following query is used by a DBA. This display’s session id, the user name and so on:
507SELECT l.session_id||','||v.serial# sid_serial, l.oracle_username DB508, 
508o.object_name, o.object_type, 
509DECODE( l.locked_mode, 0, 'None',1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(l.locked_mode) ) lock_mode, 
510o.status, to_char(o.last_ddl_time,'dd.mm.yy') last_ddl FROM dba_objects o, 
511gv$locked_object l, v$session v 
512WHERE o.object_id = l.object_id AND l.session_id=v.sid ORDER BY 2, 3;
513 
514Note: As there is no lock present, we get a blank output screen.
515
5165.	To identify session statistics, this query is used:
517SELECT job_name, owner, log_date, actual_start_date, run_duration, status
518FROM dba_scheduler_job_run_details 
519ORDER BY log_date;
520 
5216.	To identify the disk free space, the DBA can run following query:
522Select df.file_name, nvl(fr.bytes/(1024*1024),0) free_space_MB, df.bytes/(1024*1024) total_size, df.tablespace_name, ((df.bytes-nvl(fr.bytes,0))/df.bytes)*100 pct_used 
523from (select sum(bytes) bytes, file_id 
524from dba_free_space 
525group by file_id) fr, dba_data_files df 
526where df.file_id = fr.file_id(+) 
527order by 1, df.file_id;
528
529 
530
531
532
533
534
535
536Interface Design:
537In this section, we are going to show the interface design for the BullFlix application. Here, we are concentrating on BullFlix Message aspect.
538
539Login Page:
540
541The login page is the gateway of our application. All the users must login to access the system. If a user has login name and password, he can enter the credentials and use the system otherwise a user can sign up for becoming registered member.
542
543 
544
545
546Chat Page:
547This is the page where users will be able to send message to each other, in the below screenshot, we have given the group chat functionality.
548 
549
550Dashboard:
551The dashboard gives us the option of various functionalities present, user can add a group, delete a group or add friends or delete friends etc.
552 
553
554Friends:
555On friends page, user is able to see the list of his friends, other users who has sent him reuest and add them to his friend list or return to the home page.
556 
557Data Visualization:
558In this section we present a model analysis of our data. All data used was generated by us to mimic what a messaging application could present in terms of user engagement statistics.
559
5601.	Users Engagement and General Trend Analysis
561
562Every manager needs to know how its product is doing in terms of basic metrics. For our messaging application a basic measurement of performance is number of messages users exchange within Bullflix app. To perform such analysis, we exported data from MESSAGE table to Excel and made several plots described below.
563First daily use of messaging application was analyzed by plotting daily number of messages exchanged in BullFlix over the period of its operations (Jan 1, 2017 till Nov 13, 2017).
564
565 
566
567Raw data plot demonstrates a rather noisy behavior which mirrors highly volatile engagement of users within period of observation. Visually one may spot that consistently increasing high points suggest that messaging application gains popularity as measured by number of messages. To get a better idea about overall trend a linear trendline was depicted (dotted line on a graph above). It’s positive slope also confirm visual assessment that number of messages indeed grows within a period in question. Frequent and repeating spikes and troughs also suggest that users’ behavior is affected by seasonality.
568
5692.	Pattern of Growth Analysis
570
571Deciphering growth pattern required us to produce aggregated data to see through noisy raw statistics. A natural time frame to analyze messaging activity is one week. Consequently, number of messages was combined on a weekly basis starting January 1, 2017 till November 11, 2017, excluding two days of observations which do not make a full week.
572 
573Plotting weekly data, we can see growth that we could witness on daily graph as well. However, with reduced noise we can more clearly see growth pattern. Specifically, we can determine that a steady growth was observed since the inception of messaging services till the end of observations in the middle of November 2017. Taking a closer look, one can determine two “hops” that user’s engagement had. First – in the end of April; second – in the end of July.
574Weekly data was further divided into 15 weekly periods (or roughly 4 months each). For each period an average number of messages per week was calculated. At the graph above, one might see that messaging service started in January at around 58 messages per week. In late April weekly exchange almost doubled and reached an average of 107 messages per week. In August weekly exchange jumped roughly 50% and reached almost 170 messages per week.
575
5763.	Seasonality Analysis
577
578To study seasonal effect on users’ engagement we need to analyze general product’s life cycle as well as understand patterns of how clients use the application.
579Since we review only first 11 months of application’s life it is hard to expect that we could spot any variability stemming from differences of application use in different seasons of the year. Any upward trend would smooth the seasonal effect that span over a month. Therefore, a more granular time periods may be needed to make any judgement about usage seasonality. A natural time frame to analyze messaging activity is one week. Therefore, distribution of messages within a week (i.e. on each weekday) was analyzed. For each day of observations starting January 1, 2017 day of week was defined. After that total number of messages that were exchanged on every day of week (e.g. on all Mondays, on all Tuesdays, etc.) during 11 months of observations were summarized in the table, shown below.
580
581Day of week	Total number of messages 
582Monday	350
583Tuesday	551
584Wednesday	465
585Thursday	1488
586Friday	1040
587Saturday	869
588Sunday	237
589Total	5000
590
591Summary data shown above was plotted to visually analyze distribution of message throughout a week. Such relative (share of total number) adjusted data is shown in the graph below.
592 
593As one may see users’ activity (based on all observations) peak on Thursdays, remains high on Fridays and Saturdays. On Sundays activity bottoms and on Monday through Wednesday stays moderate. One hypothesis to explain such behavior could be the following: users are getting ready for weekends when they want to go to watch movies, primarily on Friday night and on Saturday. When working week start on Monday, users’ activity is relatively low, then as people do away with their business tasks and as weekends approach they slightly increase engagement with the app. On Thursday people feel the urgency to make plans and their activity peaks. On Friday messaging remains high as people complete their last-minute planning. Same activities continue Saturday. However, on Sunday people tend to get busy with any other plans they had and keep communication at a minimum level.
594
595
5964.	Dynamics of Seasonality of Service Usage
597
598Despite quite conclusive pattern of distribution of messages within a week shown in previous paragraph, managers shall be interested in dynamics of such seasonality. Did it change in the first 11 months of application’s life?
599To answer that question seasonality analysis has been repeated for each of 45 full weeks of observations. Data points were divided into full weeks (Monday through Sunday) starting January 2, 2017 till November 12, 2017, excluding 2 days of observations which did not make full weeks. Distribution of messages within a week throughout 11 months of 2017 was depicted in a graph below.
600
601 
602Though distribution of messages on weekdays did not remain intact, no clear trend could be determined based on 11 months of observations. Shares of weekdays varied, but remained close to its average values. No weekday gained or lost attention over time relative to other weekday. For planning purposes and timing of marketing activities, Bullflix’s managers can rely on general use pattern within a week that was determined in the previous section.
603
6045.	Sentiment Analysis to Gauge Users’ Evaluation of Movies
605
606In this section we describe our attempt to use messages text data to derive users’ attitude towards movies they discuss. Since all data used in this experiment was generated by us, the result is predetermined to a great extent. However, techniques illustrated here could be used on other data sets.   First, a dictionary of terms was assembled along with sentiment scores, attributed to those terms. Dictionary that was used for that analysis is provided below.
607	Positive sentiment	Negative sentiment
608Keywords	Good, cool	Not a fan, bad, worst, disgusting
609Sentiment score	1	-1
610
611Second, we determined which messages contain movies titles. Out of 5000 messages 1521 messages contained a movie title. Third, within those 1521 messages containing a movie title, each message was evaluated individually to determine its net positive sentiment score, which is sum of sentiment scores of all identified terms in the message. Fourth, based on total net positive sentiment score each message was evaluated to be either “positive”, or “negative”, or “neutral”. Summary of “positive” and “negative” sentiments of users expressed in their messages is depicted in a graph below.
612 
613As we can see users tend to express their negative sentiment much more frequently, which outweighs the positive sentiment. For most movies it makes their net score negative. After such observation we made a conclusion that most of negative sentiment is self-sustaining “hate speech” that skews our observations. Until better methodology is developed we decided to ignore any negative sentiment. Having done so, our top liked movies chart looks the following:
614Position	Title	Total number of positive messages
6151	The General 	9
6162	Shutter Island 	9
6173	Howl's Moving Castle 	9
6184	Gladiator 	8
6195	The Lord of the Rings: The Two Towers 	8
6206	The Big Lebowski 	7
6217	The Man Who Shot Liberty Valance 	7
6228	The Matrix 	7
6239	Notorious 	6
62410	My Neighbor Totoro 	6
625
626
627
628Queries:
6291. For a Fan with ID say ‘3’ list the types of requests he has sent to users on the app.
630Select REQUEST_TYPES.REQUEST_TYPES, FRIEND_REQUEST.REQ_REQUEST_ID, FAN.FAN_ID
631from REQUEST_TYPES 
632inner join FRIEND_REQUEST on REQUEST_TYPES.REQUEST_TYPES=FRIEND_REQUEST.RET_REQUEST_TYPE_TITLE
633inner join FAN on FRIEND_REQUEST.REQ_SENDER_FAN_ID=FAN.FAN_ID
634where FAN.FAN_ID='3';
635
6362. Display the ID of the fan who has received a message and also the status if he/she has read the message or not when a fan sends a message to the recipient.
637Select MESSAGE_RECIPIENT.MR_IS_READ, MESSAGE_RECIPIENT.MR_MESSAGE_REC_FAN_ID
638from MESSAGE_RECIPIENT
639inner join MESSAGE on MESSAGE_RECIPIENT.MES_MESSAGE_ID=MESSAGE.MES_MESSAGE_ID
640join FAN on MESSAGE.MES_SENDER_FAN_ID=FAN.FAN_ID;
641
6423. Query to display if the group chat window of a particular fan is active or not.
643select GROUP_CHAT.GC_IS_ACTIVE
644from GROUP_CHAT
645join GROUP_CHAT_LIST on GROUP_CHAT_LIST.GC_GROUP_ID=GROUP_CHAT.GC_GROUP_ID
646join FAN on GROUP_CHAT_LIST.FAN_FAN_ID=FAN.FAN_ID;