ISM 6218 Advance Database Management
Project Report
on
BullFlix, a Movie App (with additional functionality of messaging)













Table of Contents

1.	Executive Summary………………………………………………………………………………3
2.	Assumptions…………………………………………………………………………………………4
3.	Logical Design……………………………………………………………………………………….6
4.	Capacity Planning………………………………………………………………………………….9
5.	Data Generation…………………………………………………………………………………..10
6.	DDL Queries………………………………………………………………………………..……….16
7.	Data Integrity………………………………………………………………………………..……..23
8.	Performance Tuning………………………………………………………………..…………..26
9.	Stored Procedure…………………………………………………………………..…………….30
10.	 DBA Scripts……………………………………………………………………………..…………..31
11.	 Interface Design………………………………………………………………………….……...36
12.	 Data Visualization………………………………………………………………………….…...39
13.	 Queries………………………………………………………………………………………….…...45







Executive Summary:
The 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”.
They 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”.
For 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.
We have given following weights to these activities:

Topic Weights:
Topic Area	Description	Weight
Database Design:	Logical Design, Data Generation, Data Integrity, DDL Statements	30
Query Writing: 	Queries, Stored Procedure	25
Performance Tuning: 	Indexing, Btree, Bitmap	15
Other Topics: 	Capacity Planning, DBA Scripts, Interface Design, Data Visualization	30




Assumptions:
We are assuming following things in designing our BullFllix app.
1.	[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.

2.	[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.

3.	[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.

4.	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.

5.	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.

6.	[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.

7.	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.

8.	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.

9.	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.

10.	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.

11.	We assume that only one theater may be located at one set of GPS coordinates.

12.	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.

13.	 Our DB can store Theaters' ratings by fans registered in the system (existing Fans table).

14.	We assume that any fan may rate any number of Theaters and add a written review (many to many relation).







Logical Design:
1.	The logical design for BullFlix, with an additional capability of “Theatres”:






2.	Whereas the logical design for BullFlix with functionality of “Friends and followers”:



3.	The logical Design of the whole BullFlix consolidated into one:



In the above ERD diagram, we have displayed the original design in Green, the theatres in Red and in Blue the functionality of messaging.



Capacity Planning:
Capacity 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.
Below we are going to show the available and allocated spaces in our database.
Select * FROM DBA_TEMP_FREE_SPACE

From 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.
SELECT table_name, num_rows, avg_space, avg_row_len
from all_tables
where owner = 'RELMDB'


From 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.
We 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.
On 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.



Data Generation:
To 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.
Message and message recipient tables
One 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:
1.	Messages must be compiled of English words.
2.	Total number of messages to be generated was arbitrarily chosen to be 5000.
3.	Messages must be of a variable length uniformly distributed in a range from 1 to 20 words.
4.	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.
To satisfy the abovementioned requirements, and specifically p. 1, a population of 20 words was created to be used in messages:
good 	cool 	not a fan 	bad 	worst
disgusting 	hello 	what 	when 	I
you 	like 	want 	grab 	food
address 	car 	pick up 	director 	know
Words 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.
MS 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.



Legend for the screenshot above:

Message size is determined. It is randomly chosen from 1 to 20 using Excel’s randbetween() function.

Based 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.

Movie title is selected from the list of 283 movies contained in MOVIE table using Excel’s randbetween() and lookup functions.

Flag 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).

Set of words that can be used in constructing a message.

Based 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.

All words selected at step 6 are concatenated to form a string using Excel’s concat() function.

Several base inputs like maximum message size are outlined in that control form.

After creation of messages’ bodies, the rest of the attributes of MESSAGE table were generated. Screenshot bellow shows a part of generated table.


Legend for the screenshot above:


Message identifier, determined as a positive integer, keeping track of number of messages.

Message 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.

Sender is selected randomly from a list of fan’s IDs of the FAN table.

First 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.

Message body, generated at previous steps as shown above.

On 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.

Messages are set to never expire by setting expiration date to Jan 1, 2099.

MESSAGE_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).

Groups and Friends Tables
GROUP_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.
GROUP_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.
FRIEND_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.


Followers, Movie Ratings and Likes Table
‘FOLLOWER’ table was generated using similar techniques employed for MESSAGE table. Below a screenshot of our working model is presented.


Legend for the screenshot above:

On 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.

Random number of followers in a range of 1 to 20 was assigned to each followee using Excel’s randbetween() function.

Random fans’ IDs were assigned to each followee accounting for total number of followers such followee has (see step 2).


After 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.
MOVIE_RATING table was generated to illustrate functionality that allows followers to “like” ratings and descriptions made by their followees.


Legend for the screenshot above:


Rating identifier, determined as a positive integer, keeping track of number of ratings.

Random fan ID selected from the FAN table using Excel’s randbetween() function.

Random movie ID selected from the MOVIE table using Excel’s randbetween() function.

Random 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.

Rating 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.

Date was randomly selected within 2016.

No favorite flag was set.

After 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.
DDL Queries:
1.	Message Table
CREATE TABLE "DB204"."MESSAGE"
   (	"MES_MESSAGE_ID" NUMBER(4,0),
	"MES_SUBJECT" VARCHAR2(128 BYTE),
	"MES_SENDER_FAN_ID" NUMBER(6,0),
	"MES_PARENT_MESSAGE_ID" NUMBER(4,0),
	"MES_MESSAGE_BODY" VARCHAR2(256 BYTE),
	"MES_CREATE_DATE" VARCHAR2(26 BYTE),
	"MES_EXPIRE_DATE" VARCHAR2(26 BYTE)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "STUDENTS" ;


2.	Follower Table
  CREATE TABLE "DB204"."FOLLOWER"
   (	"FOL_FOLLOWER_FOLLOWEE_ID" NUMBER(4,0),
	"FOL_FOLLOWER_FAN_ID" NUMBER(6,0),
	"FOL_FOLLOWEE_FAN_ID" NUMBER(5,0),
	"FOL_FOLLOW_STATUS" VARCHAR2(26 BYTE),
	"FOL_FOLLOW_START_DATE" VARCHAR2(26 BYTE),
	"FOL_FOLLOW_STATUS_UPDATE_DATE" VARCHAR2(26 BYTE)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "STUDENTS" ;


3.	Friend Request Table
CREATE TABLE "DB204"."FRIEND_REQUEST"
   (	"REQ_REQUEST_ID" NUMBER(4,0),
	"REQ_SENDER_FAN_ID" NUMBER(6,0),
	"REQ_RECEPIENT_FAN_ID" NUMBER(6,0),
	"RET_REQUEST_TYPE_TITLE" VARCHAR2(26 BYTE),
	"REQ_REQUEST_DATE" VARCHAR2(26 BYTE)
   )
SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "STUDENTS" ;



4.	Group Chat Table
CREATE TABLE "DB204"."GROUP_CHAT"
   (	"GC_GROUP_ID" NUMBER(3,0),
	"GC_NAME" VARCHAR2(128 BYTE),
	"GC_CREATE_DATE" VARCHAR2(26 BYTE),
	"GC_IS_ACTIVE" NUMBER(3,0)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "STUDENTS" ;


5.	Likes Table
  CREATE TABLE "DB204"."LIKES"
   (	"LIK_LIKE_ID" NUMBER(4,0),
	"FOL_FOLLOWER_FOLLOWEE_ID" NUMBER(6,0),
	"MR_MOVIE_RATING_ID" NUMBER(6,0),
	"LIK_LIKE_DATE" VARCHAR2(26 BYTE)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "STUDENTS" ;


6.	Group Chat List Table
CREATE TABLE "DB204"."GROUP_CHAT_LIST"
   (	"GCL_GROUP_ID" NUMBER(4,0),
	"GC_GROUP_ID" NUMBER(3,0),
	"FAN_FAN_ID" NUMBER(4,0),
	"GCL_CREATE_DATE" VARCHAR2(26 BYTE),
	"GCL_IS_ACTIVE" NUMBER(3,0),
	"GCL_CHAT_ADMIN" NUMBER(3,0)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "STUDENTS" ;



7.	Message Recipient Table
  CREATE TABLE "DB204"."MESSAGE_RECIPIENT"
   (	"MR_MESSAGE_REC_ID" NUMBER(4,0),
	"MES_MESSAGE_ID" NUMBER(4,0),
	"GCL_GROUP_ID" VARCHAR2(26 BYTE),
	"MR_MESSAGE_REC_FAN_ID" NUMBER(6,0),
	"MR_IS_READ" NUMBER(3,0)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "STUDENTS" ;




8.	Request Types Table
CREATE TABLE "DB204"."REQUEST_TYPES"
   (	"REQUEST_TYPES" VARCHAR2(26 BYTE)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "STUDENTS" ;


Data Integrity:
We 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.
1.	Primary Key Constraint on Message Table
ALTER TABLE message
ADD PRIMARY KEY (MES_MESSAGE_ID);


2.	Primary Key Constraint on Group_Chat_List
ALTER TABLE GROUP_CHAT_LIST
ADD PRIMARY KEY (GCL_GROUP_ID);




3.	Foreign Key Constraint on Message Recipient
ALTER TABLE message_recipient
ADD FOREIGN KEY (mes_message_id) REFERENCES message(mes_message_id);



4.	Foreign Key Constraint on Group Chat List
ALTER TABLE GROUP_CHAT_LIST
ADD FOREIGN KEY (GC_GROUP_id) REFERENCES GROUP_CHAT(GC_GROUP_ID);





5.	Foreign Key Constraint on Friend Request
ALTER TABLE FRIEND_REQUEST
ADD FOREIGN KEY (REQ_SENDER_FAN_ID) REFERENCES FAN(FAN_ID);





Performance Tuning:
To 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.
Case 1: Query without an Index
We 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.
SELECT film_title
FROM movies
WHERE film_title like '%Lord%';



Case 2: Querying with a B-Tree Index
To 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.


Case 3: Querying using Bitmap Index
To 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.





Case 4: Querying using Complex Queries
To study this, we are going to query first without an index and then after creating an index.

4.1 Query without Index
CREATE TABLE casts AS SELECT *
FROM RELMDB.casts;
Select distinct movies.film_title, casts.cast_member
from relmdb.movies, relmdb.casts
where movies.Film_ID = casts.Film_ID and (cast_member = 'Tom Hanks' or cast_member = 'Tim Allen')
order by film_title;



From 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.


4.2 Query with Index:
We are creating an index on cast_member column and will execute the same query from the above step.
CREATE INDEX cast_member_btree ON casts (CAST_MEMBER);
We 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.









Stored Procedure:
1.	To validate username and password, following procedure is required:
CREATE proc log_in(username IN VARCHAR2, password IN VARCHAR2)
RETURN VARCHAR2
AS count_match NUMBER;
BEGIN SELECT COUNT(*)
INTO count_match
FROM ACCOUNT
WHERE fan_id=username AND password=abc;
IF count_match = 0 THEN dbms_output.put_line('Wrong username or password');
ELSIF count_match = 1
THEN dbms_output.put_line('Login successful');
ELSE dbms_output.put_line('Try Again');
END IF;
END;

2.	To find out movie rating by a fan:
create proc MovieFanRating (
FILM_TITLE VARCHAR2(80),
FAN_ID VARCHAR2(10,0))
As
BEGIN
SELECT FILM_ID, FILM_TITLE, FILM_YEAR,
(SELECT IMDB_RATING FROM FAN_RATINGS fanR
WHERE FanR.FAN_ID = FAN_ID AND FanR.FILM_ID = move.FILM_ID
) USER_RATING,
(SELECT COUNT(*) FROM FAN_RATINGS fr
WHERE fr.FAN_ID = FAN_ID AND fr.FILM_ID = move.FILM_ID
) IS_RATED
FROM MOVIES move
WHERE FILM_TITLE LIKE '%' + FILM_TITLE + '%'
ORDER BY FILM_TITLE
END;



DBA Scripts:
DBA 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.

1.	To query information on sessions, following script is required:
SELECT statsname.name, sstatistics.value FROM v$sesstat sstatistics, v$statname statsname, v$session sen
WHERE sstatistics.statistic# = statsname.statistic# AND sen.audsid = SYS_CONTEXT ('USERENV','SESSIONID') AND sen.sid = sstatistics.sid ;




2.	To check default passwords, the query written below is used:
SELECT a.username, b.account_status
FROM   dba_users_with_defpwd a
       JOIN dba_users b ON a.username = b.username
ORDER BY 1;


3.	For backing up purposes, a DBA can record path of the directories in a database with this script:
SELECT directory_name, ORIGIN_CON_ID, directory_path
FROM dba_directories;




4.	To identify a locked session, the following query is used by a DBA. This display’s session id, the user name and so on:
SELECT l.session_id||','||v.serial# sid_serial, l.oracle_username DB508,
o.object_name, o.object_type,
DECODE( 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,
o.status, to_char(o.last_ddl_time,'dd.mm.yy') last_ddl FROM dba_objects o,
gv$locked_object l, v$session v
WHERE o.object_id = l.object_id AND l.session_id=v.sid ORDER BY 2, 3;

Note: As there is no lock present, we get a blank output screen.

5.	To identify session statistics, this query is used:
SELECT job_name, owner, log_date, actual_start_date, run_duration, status
FROM dba_scheduler_job_run_details
ORDER BY log_date;

6.	To identify the disk free space, the DBA can run following query:
Select 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
from (select sum(bytes) bytes, file_id
from dba_free_space
group by file_id) fr, dba_data_files df
where df.file_id = fr.file_id(+)
order by 1, df.file_id;








Interface Design:
In this section, we are going to show the interface design for the BullFlix application. Here, we are concentrating on BullFlix Message aspect.

Login Page:

The 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.




Chat Page:
This 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.


Dashboard:
The dashboard gives us the option of various functionalities present, user can add a group, delete a group or add friends or delete friends etc.


Friends:
On 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.

Data Visualization:
In 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.

1.	Users Engagement and General Trend Analysis

Every 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.
First 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).



Raw 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.

2.	Pattern of Growth Analysis

Deciphering 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.

Plotting 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.
Weekly 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.

3.	Seasonality Analysis

To 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.
Since 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.

Day of week	Total number of messages
Monday	350
Tuesday	551
Wednesday	465
Thursday	1488
Friday	1040
Saturday	869
Sunday	237
Total	5000

Summary 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.

As 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.


4.	Dynamics of Seasonality of Service Usage

Despite 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?
To 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.


Though 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.

5.	Sentiment Analysis to Gauge Users’ Evaluation of Movies

In 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.
	Positive sentiment	Negative sentiment
Keywords	Good, cool	Not a fan, bad, worst, disgusting
Sentiment score	1	-1

Second, 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.

As 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:
Position	Title	Total number of positive messages
1	The General 	9
2	Shutter Island 	9
3	Howl's Moving Castle 	9
4	Gladiator 	8
5	The Lord of the Rings: The Two Towers 	8
6	The Big Lebowski 	7
7	The Man Who Shot Liberty Valance 	7
8	The Matrix 	7
9	Notorious 	6
10	My Neighbor Totoro 	6



Queries:
1. For a Fan with ID say ‘3’ list the types of requests he has sent to users on the app.
Select REQUEST_TYPES.REQUEST_TYPES, FRIEND_REQUEST.REQ_REQUEST_ID, FAN.FAN_ID
from REQUEST_TYPES
inner join FRIEND_REQUEST on REQUEST_TYPES.REQUEST_TYPES=FRIEND_REQUEST.RET_REQUEST_TYPE_TITLE
inner join FAN on FRIEND_REQUEST.REQ_SENDER_FAN_ID=FAN.FAN_ID
where FAN.FAN_ID='3';

2. 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.
Select MESSAGE_RECIPIENT.MR_IS_READ, MESSAGE_RECIPIENT.MR_MESSAGE_REC_FAN_ID
from MESSAGE_RECIPIENT
inner join MESSAGE on MESSAGE_RECIPIENT.MES_MESSAGE_ID=MESSAGE.MES_MESSAGE_ID
join FAN on MESSAGE.MES_SENDER_FAN_ID=FAN.FAN_ID;

3. Query to display if the group chat window of a particular fan is active or not.
select GROUP_CHAT.GC_IS_ACTIVE
from GROUP_CHAT
join GROUP_CHAT_LIST on GROUP_CHAT_LIST.GC_GROUP_ID=GROUP_CHAT.GC_GROUP_ID
join FAN on GROUP_CHAT_LIST.FAN_FAN_ID=FAN.FAN_ID;