Flix2You Database Plan Proposal 

Table of Contents

Executive Summary        p. 2

Project Overview         p. 3

Project Management         p. 5

User Analysis         p. 10

Organization of Data        p. 13

Database Administration       p. 17

Database Dashboard and Analytics     p. 21

Legal Issues         p. 26

List of References        p. 28

Appendix          p. 29

Appendix A – Gantt Chart      p. 29

Executive Summary

Our team at No Judgment, Inc. is delighted to offer Flix2You with a wonderful business strategy. The plan’s goal is to allow Flix2You to properly use the immense data already collected to ensure that they become information rich. This proposal is meant to assist Flix2You in the growth of their already competitive company against current competitors such as Redbox and Netflix. No Judgment, Inc. has compiled a strategic plan that will help to deliver the desired outcome for Flix2You’s growing company. The end state of this proposal is to assist Flix2You in their initiative to make their mark within the media rental market.

The team at No Judgment, Inc. strives to assist Flix2You in expanding its current operations. This expansion will build off of Flix2You’s already established website, database, and customer-base. Flix2You’s website is a foundation of our proposal and will be used as part of the building blocks for our strategy. Flix2You’s customers will be able to enjoy the benefits of the added data usage that they will acquire from this proposal.

Team No Judgment will provide Flix2You with the means to utilize the extensive amount of data already collected about their customers to provide a better understanding of what their customers want. This will not only assist in providing a better understanding of their data, but also put the compiled data to use to increase company revenue.

No Judgment aims to create a data repository that will allow for more simple queries and the ease of developing proper analysis. The retrieval of this data will be controlled through assigned tier level access. Employees that are able to access historical data, may not be able to access more localized current data. This will create a system of checks and balances and ensure proper utilization of information to protect Flix2You customers’ interests.

In addition to controlled access to data, No Judgment presents solutions to any potential legal issues. A newly developed Privacy Policy in conjunction with a Terms and Conditions for Use document will be implemented within our proposal. Flix2You customers will be assured that their information will not be abused by remaining confidential from sources outside the company.

Project Overview

Flix2You is a media rental business that planted its roots in Scranton, PA. They have grown over their 12 years of history to establish a valid presence against the media giants Netflix, Blockbuster, and Redbox. To promote further growth, the Flix2You Board of Directors and requested that a company create a proposal that will allow them to continue to grow amid their competitors. Higher-level management is currently unable to access customer data and therefore an inability exists to execute well-informed decisions that will benefit the company. Within this eager and growing company, there is a need to have a better understanding of the data that already exists within its current database.

Currently Flix2You has closed down all of its former physical locations to pave the way for its exclusively online website access to its nearly twenty million subscribers. At the moment Flix2You’s website receives approximately 20,000 daily hits. This number is expected to increase as they expand their movie rental selection.  In addition to movies, Flix2You looks to possibly offer video game and app downloads in the future as well.

Flix2You needs to be able to produce useful reports from information gathered from the database. Certain level management also needs to be able to access data from the website, as it is currently the heart of the company. These reports will allow executives to employ proper business decisions. Information on popular titles and other rental trends depending on customer locations will be drawn much easier in this manner.

No Judgment, Inc. will create a project plan that will outline the necessary course of action to complete the proposal. This will include Gantt Charts that break down chronologically the work that will take place. This will include a project timeline from start to end date covering all major milestones. The plan will also include a proper budget explaining the personnel and other resources needed to accomplish the plan.

The current Flix2You database will experience a complete overhaul that will allow for what the company requires. The original software cannot keep up with the demand of data retrieval without affecting customer transactions. Analysis of the current database schema, entities and their relationships will allow for the proper database structure required. A denormalized approach will be used to solve the issues with the existing database. An entity relationship diagram (ERD) will provide a visualization of the new database design.

A database backup and recovery program will also be employed. This will ensure that there is loss protection of all collected data from daily transactions. A database administrator (DBA) will be responsible for this backup and recovery activity. A systematic method of backup activity will be in place that will mitigate the possibility of data loss down to the lowest level possible.

Security measures will be created that include both technical and managerial aspects. Through technical measures such as password rotation, password complexity levels, system updates, data encryption, and monitoring of user activity, Flix2You can be confident in combating intrusions. The DBA will ensure that these measures are implemented effectively and maintain a tight control on any potentially overdue measure.

Flix2You desires to see a “dashboard” produced to allow for a secure login by management personnel. This will allow them to view specific reports that break down customer demographics and sales. They want this dashboard to be versatile and have many key capabilities. Flix2You wishes for the dashboard to be mobile device friendly and have the ability to be exported to other programs such as Microsoft Excel or Adobe Acrobat making printing a simple task. This dashboard needs to be easy to use and understand, allowing for informative results.

Legal issues will be addressed as well to include a Privacy Statement and a Terms of Use agreement. These policies are critical in providing Flix2You with a legal backbone to fall on if there are any malicious activities within its customer base.

Project Management

Initial Project Plan

As an experienced database development company, No Judgment, Inc. has perfected a way of designing and implementing databases for their clients. No Judgment, Inc. always strives to deliver top quality products to their clients, which includes custom built databases tailored to the exact specifications of their clients needs. No Judgment, Inc. has created a project management plan for your company, which meets these high standards and ensure that you will have a high performing database for years to come.

When creating the Flix2You database replacement plan, No Judgment, Inc. decided on 6 phases: Planning, Analysis, Development, Implementation, Maintenance, and Completion. These phases were chosen to ensure that No Judgment, Inc. could deliver a comprehensive project management plan. Each of these phases consists of specific milestones and deliverables that will help ensure you know what is happening in each phase and where we are in the implementation process.

The first phase, the Planning phase, is where we work together to evaluate what you need from a database. We look at your current database and identify what problems you would like to solve and what processes you would like to implement in your new database. We will then create a comprehensive implementation proposal, which we will review with you so you know what to expect throughout the process. In this proposal, No Judgment, Inc. will also take into account your budget needs and your time constraints. For your convenience, No Judgment, Inc. has already created this proposal, as well as the deliverables for our detailed proposal. This phase will take 3 days in total.

After reviewing our proposal and deciding whether you would like to pursue a contract with No Judgment, Inc., we will move on to the next phase. In the Analysis phase, we determine the business rules that are unique to your business. These rules include anything unique to your business, such as incorporating portions of your client loyalty plan into your database for ease of access for your employees. Once defined, we use these rules to create a foundation for your new database system.

During this phase, we also do an overview of your current system, and review what is successful and what is unsuccessful with your current database. If there is anything you would like to carry over from your current database, we will define it here. Once this is done, we will determine what the hardware and software needs are for your new database and place any necessary orders. After this, our team will begin to design new data structures for your business with the data we have gathered from our review with you and your team. We will then show you how this data will be manifested in your new system. At the end of this phase, we will present you with an Entity Relationship Diagram that represents your business and our final proposal. Once we receive your final approval, we will move on to the next phase. This phase will take 9 days in total.

The next phase is the Development phase. This will be our longest and most time-consuming phase, and No Judgment, Inc. has taken careful consideration for our timeline. This is where we begin to create the data tables in your database, and then we will begin to test the sample data as it is inputted. This is also where we begin to formulate your end user system, and begin to enable your company’s users to access the system. However, to have the full data we will need to consult with a third-party company, Dundas Solutions, who will create reports based on your current business trends, such as sales performances. They will also conduct a survey on your clients to identify what your clients’ needs are. Once this phase is complete, you will be able to view the sample data, reports from the sample data, and all reports compiled by Dundas Solutions. Once these have been reviewed, we will move on to the next phase. This phase will take 18 days in total.

As we move on to the Implementation phase, we will begin to implement the finalized server and user environments. This will be done in a separate system, called a sandbox, so it will not interrupt the current data. We will implement all the tables and data created in the previous phase, and open the server and migrate over all the data from the former database. Once this has been completed, we will run queries to test the database and ensure that all data has been migrated over successfully. This phase will complete the final implementation of the new database. This phase will take 13 days in total.

From here, we move on to the Completion phase of the project. We will assist in any debugging of the new database, and will help train and guide your employees to make sure they feel comfortable with the new system. We will also develop a comprehensive training and reference manual, which will help assist you in training any future employees. Finally, before leaving, we will complete any initial reports and host a final meeting with you to ensure your happiness and satisfaction with your new system.

At No Judgment, Inc., we always strive to deliver the best products to our clients in a timely manner. We expect this project to take 48 days in total. Under Appendices, you will find a Gantt Chart, which outlines each step and their estimated start and completion dates. We will review this timeline during our initial Planning meeting.

Please see ‘Appendix A’ for Gantt Chart.

Project Resources and Budget

No Judgment, Inc. has taken great care in understanding your current database needs while finding cost effective solutions to solve these needs. While we understand that you have little concerns about your budget, we want to ensure that we will go above and beyond to meet your needs without gathering unnecessary expenses. In this section, we will go over any necessary project resources and your budget.

The first resources to mention and possibly the most important, is our No Judgment, Inc. team. We will have four highly skilled individuals working on your project that we can assure will do whatever it takes to meet your needs.

Project Manager – Charles MacDonnell

Charles MacDonnell has been with No Judgment, Inc. for 10 years, and is a veteran with our company. Charles has proven his ability to lead effective teams while keeping them on track and always strives to do whatever it takes to make his clients happy. Charles graduated from Penn State University with a Bachelor’s degree in Business Management, and has over 15 years of experience in database administration and programming experience. Charles will oversee the project, ensuring that all phases stay on schedule, and will be your initial point of contact during the contract.

Database Administrator – Rebekah McKinley

Rebekah McKinley has been with us for 8 years and is another veteran of No Judgment, Inc. Rebekah graduated from Penn State University with her Bachelor’s in Information Science and Technology. She is known as an extremely hard worker throughout our company and is recognized by her peers for her attention to detail and successful completion of any project she is put on. Rebekah has experience with many different areas in the technology world, however she specializes in database administration and programming. Rebekah will oversee the actual creation of the database, as well as creating the database backup and recovery plan. She will oversee the initial migration of data from the old database to the new database, and will be the initial sign-off on the new server before our team turns it over to yours. 

Programmer – Nicolas Meyer

Nicolas Meyer graduated from Penn State University with his Bachelor’s in Information Sciences and Technology. Nicolas has been with No Judgment, Inc. for 5 years and quickly integrated himself into the company, becoming known for his talents with programming and database design. Nicolas’ main responsibilities will include: writing, developing, and testing many of the programs for the database, developing sample SQL & reports from the database, and to document and implement any procedures currently used by your employees to be carried over into the new database.

Data Analyst – Krisha Sleister

Krisha Sleister graduated from Penn State University with her Bachelor’s in Computer Engineering, and joined the company shortly after her graduation. Krisha has proven that she is a hard-working individual with a passion for data and the processes that go into collecting and analyzing it. Krisha will be in charge of collecting all data from your company, working with Dundas Solutions to understand what data they collect and how it ties into your company’s data needs, and ensuring that your company is delivered a well-rounded system that meets your needs and more. 

As previously mentioned, No Judgment, Inc. has taken many factors into account when creating our budget, which includes the labor costs of our team. While we have allocated sufficient resources to ensure that we are delivering you a top-quality product, it should be noted that labor would be a significant cost to the project. Our labor costs are defined below:

Team Member	Hourly Rate	Total Labor Costs
Charles MacDonnell	$56.43	$27,086.40
Rebekah McKinley	$36.62	$17,577.60
Nicolas Meyer	$35.43	$17,006.40
Krisha Sleister	$36.37	$17,457.60
	Total:	$79,128

Beyond the team, listed below is the proposed new hardware and software that will be needed for the new system. We propose that we will need upgraded server, hardware, and software components, as this will allow easier upgrading in the future as technologies change. We suggest that you continue to use Microsoft SQL Server. We want you to use a system that is familiar to you, but will be adaptable with future technologies. The software will be upgraded to the newest system, Windows 10 and we will be upgrading your Server to Windows Server 2016. With the upgrade to Windows Server 2016, we highly recommend moving to Microsoft Azure for your movie storage. We will also be upgrading your memory to support your new data.

Component	Model	Prices
CPU/Motherboard	HP ProLiant DL360 G9 1U Rack Server – 2 x Intel Xeon E5-2697 v3 2.60 GHz 800082-S01	$8,130.99
Memory	DELL 317-9079 32GB (4X8GB) 1600MHZ PC3-12800R CL11 2RX4 ECC Registered 1.5V DDR3 SDRAM 240-PIN RDIMM Dell Memory Kit	$910.00
Storage	Microsoft Windows Azure Hybrid Use (Discount Included)	$1,777
Operating System	Microsoft Windows 10 Professional – 64 bit	$199.99
Database	Microsoft Server 2016 Datacenter – Core-based	$6,155
	Total (sales tax included):	$17,172.98

Overall total costs (labor + components): $96,300.98

User Analysis

Users

The Flix2You database will be accessed and used by four main types of users.  Each category of user will have different needs, access and editing abilities, and privileges. It will be necessary to create and manage roles for each type of user and assign users to these roles.

The first category of user will be the administrator; there will one primary database administrator (DBA) and possibly one or two others who ordinarily have other jobs but are able to perform the DBA’s tasks if necessary. The DBA will have read and write access to all data and will be able to add, update, and delete any data in the database. Authentication will be required to prevent malicious activity on a DBA account; a password will be the minimum required. An additional possibility, depending on the resources available, would be some type of biometric authentication, ideally finger vein authentication (which is more accurate and much harder to defeat than fingerprint authentication). In addition to the account authentication, a window will pop up if the administrator attempts to modify or delete anything an ordinary user would not be able to modify or delete. This window will ask the administrator if he or she wants to do this and clarify the consequences; the administrator will then be prompted for his or her password. The administrator will be responsible for managing and maintaining user accounts, overall security, and the database in general, as described below. He or she will not ordinarily interact with everyday data, but will have the ability to do so and to override any user actions if necessary.

The second category of user will be the upper level management and company administrators. These individuals will be primarily concerned with the view of the database at large and will need a broad view of the company’s data. In general, they will not be interested in everyday data. They will have read access to all data, but write access only to high-level data, such as price changes for the entire company. Upper management and administrators will also have views automatically created and updated for them and a dashboard that enables them to quickly see where the company is strategically and make appropriate mid- and long-term decisions. These strategic decisions will affect the company’s long-term business model and determine how well the company ultimately does. Upper management and administrators will be trained on effective database use and will be able to create additional views as needed. They will also be required to make and use strong passwords. It will not be as important for them to have additional authentication methods, as they do not have the ability to make substantial changes to the entire database.

The third category of user will be the middle level management. These users will be making tactical and operational decisions and will need access to everyday transaction data, as well as short-term trends. They will be trained on effective database use for these items and will also be required to use strong passwords. They will have read access to three months worth of data in order to make tactical and operational decisions with ample information. The tactical decisions will impact company growth and sales in the one to three month ranges, while operational decisions will impact the business on a daily or weekly basis (such as changing prices in response to market pressures). Middle level management will not have write access to current transactions, as this is the domain of the fourth category of user, but they will have write access to the previous six weeks’ worth of data. This is to ensure that they can resolve any customer complaints as well as fix issues noted by customers or employees in recent data before it is stored permanently. They will also have views and a dashboard created for them and will be trained on how to effectively use them to make decisions.

The fourth and final category of user will be the hourly employees who work in warehousing, logistics, and customer service. These employees will not need access to any long-term data. They will have read and write access to same-day transactions in order to effectively serve customers and correct any mistakes or make updates. They will have read only access to previously created customer accounts and sales and will need a supervisor to make changes to customer accounts or payment methods. They will also be required to get a supervisor for any returns or refunds that are not same-day, but will be able to add customer accounts to the system. These employees will have no access to other data and will be also required to maintain strong passwords.

It is important to note that no one outside the company will have any access to company data. This will be achieved by maintaining the company’s public website on a separate server, maintaining the publicly available data in a demilitarized zone (DMZ) on a server between the website and the regular database server, and storing and linking only the data about available rentals in this zone. This data will be able for access on the public website.

Database Access

As described above, there will be several levels of database access. Only a select few users will be able to access all of the information in the database; most will have access to only the data necessary to do their jobs. All users will have their accounts created, maintained, and monitored by the DBA and will be required to use strong passwords. When a new employee is hired, the DBA will create an account for him or her and a temporary password; the new user will then create his or her own password. The DBA will be responsible for changing any access privileges and will do so at the request of the HR manager upon an employee’s status change. If a user leaves the company, his or her account will be deleted immediately. The DBA is also responsible for performing audits to ensure appropriate use of company data.

There will be four “tiers” of users, each of which has different abilities based on their job requirements. The first tier is the DBA, who will have read and write access to all of the data and whose account will thus have stronger authentication requirements.  The second tier is the upper management and administrators, who will be able to read all data but write to only high-level data. The third tier is the middle level management, who will have read and write access to recent data. Both the second and third tiers of users will have views and dashboards automatically created and updated for them and will be trained on how to create more if necessary and to use these for effective decision making. The fourth and final tier of user, the hourly employees, will have read and write access to same-day transactions and read-only access to customer accounts in order to serve the customers. A mid-level manager/supervisor will be required for any returns not on the same day, as well as changes to other customer data. 

Organization of Data

Our goal at No Judgment, Inc. is to ensure that we are creating the most effective and efficient databases for our clients. Upon diagnosis and surveys into your current database, we have discovered that while your current database is working, there is much that can be done to make it more efficient. Upon this discovery, we have decided that it would be the best option to upgrade the current database. This will make sure it is user friendly, works consistently with few errors and downtime, and will keep up with changing technologies. Below, we will dive into what we will be doing with your old and new databases.

We’ve heard your concerns regarding your current database which are:

• Slow to pull transactions information.
• Information pulling up wrong from the database.
• Information randomly deleting or disappearing.
• Inability to upgrade the database to keep up with needs.
• General stability problems.

Upon our investigation into your current database, we have discovered many areas that are leading to the above problems. The first is an overall poor database design. Many entries are duplicated and either lead to multiple paths or lead to no paths at all, which is the main cause to your database concerns. We have also discovered there is a poor system for deleting incorrect data and overall data maintenance has not been maintained. No Judgment, Inc. has put together a comprehensive plan for replacing your database and ensuring the integrity of your data.

It is outlined as such:

• We will be removing all repeated data.
• Data will be rearranged into more tables for better data storage.
• The database will now be able to represent incomplete data.
• All tables will be done in 3^rd Normal Form (3NF), using a normalization approach, which will allow data to be pulled more quickly and efficiently.

To elaborate on 1^st Normal Form (1NF), this will be done by identifying primary keys, removing similar or repeating data (including nulls) and identify all dependencies. After all tables are in 1NF, we will modify all the tables so they are in Second Normal Form (2NF). We will make more tables to eliminate partial dependencies and re-assign corresponding dependent attributes for each table. Finally, once 1NF and 2NF steps have been completed, we will move all tables into Third Normal Form (3NF). Essentially, in this form all tables depend on a primary key and we will delete all data not dependent on a primary key. This will ensure that strongest integrity of your data is present.

Once the normalization process has been completed, your database will be set up with no data redundancies and will be easier for your users to access the database without difficulty. This will also ensure that when data is added to the database, it will eliminate any anomalies that could occur that could negatively affect your data.

Finally, listed below are a sample of the SQL commands we will be using to create your new tables in your new database. These sample commands will show the initial creation of the tables, the base data in the tables (more will be added after we meet and discuss your needs), and the primary key creation.

/* customer */

CREATE TABLE customer(
customer_id int IDENTITY(1,1) NOT NULL,
zip_code int NOT NULL,
member_id int NOT NULL,
trans_id int NOT NULL,
customer_address varchar(32) NOT NULL,
customer_phone varchar(20) NOT NULL,
customer_email varchar(128) NOT NULL,
customer_dob date NOT NULL);

/* customer_rating */
CREATE TABLE customer_rating(
Customer_rating_code int IDENTITY(1,1) NOT NULL,
customer_id int NOT NULL,
movie_id int NOT NULL);

/* genre */
CREATE TABLE genre(
genre_id int IDENTITY(1,1) NOT NULL,
genre_descriptions varchar (1024) NOT NULL);

/* cast */
CREATE TABLE cast(
cast_id int IDENTITY(1,1) NOT NULL);

/* movie */
CREATE TABLE movies(
movie_id int IDENTITY(1,1) NOT NULL,
director_id int NOT NULL,
genre_id int NOT NULL,
cast_id int NOT NULL,
product_id int NOT NULL,
movie_rating int NOT NULL,
file_size int int NOT NULL,
movie_time int NOT NULL,
>movie_summary varchar(1024) NOT NULL);

/* membership */
CREATE TABLE membership(
member_id int IDENTITY(1,1) NOT NULL,
customer_id int NOT NULL,
payment_code int NOT NULL,
member_login int NOT NULL,
member_password int NOT NULL);

/* invoice */
CREATE TABLE invoice(
invoice_id int IDENTITY (1,1) NOT NULL,
member_id int NOT NULL(32) NOT NULL;

trans_id int NOT NULL(32) NOT NULL;

tax_code int NOT NULL(32) NOT NULL;
invoice_payment money NOT NULL,
invoice_total money NOT NULL);

/* product */
CREATE TABLE product(
product_id int IDENTITY(1,1) NOT NULL,
price_id NOT NULL);

/* transactions */
CREATE TABLE transactions(
trans_id int IDENTITY(1,1) NOT NULL,
product_id int NOT NULL,
member_id int NOT NULL);

/* create primary key with ALTER TABLE statement */
ALTER TABLE customer ADD CONSTRAINT pk_customer_id PRIMARY KEY (customer_id);
ALTER TABLE genre ADD CONSTRAINT pk_genre_id PRIMARY KEY (genre_id);
ALTER TABLE invoice ADD CONSTRAINT pk_invoice_id PRIMARY KEY (invoice_id);
ALTER TABLE membership ADD CONSTRAINT pk_member_id PRIMARY KEY (member_id);
ALTER TABLE movies ADD CONSTRAINT pk_movie_id PRIMARY KEY (movie_id);
ALTER TABLE customer_rating ADD CONSTRAINT pk_customer_rating_code PRIMARY KEY (rating_code);
ALTER TABLE product ADD CONSTRAINT pk_product_id PRIMARY KEY (product_id);
ALTER TABLE cast ADD CONSTRAINT pk_cast_id PRIMARY KEY (cast_id);
ALTER TABLE transactions ADD CONSTRAINT pk_trans_id PRIMARY KEY (trans_id);

/* end of primary key creation */

/* END OF SCRIPT*/

Our Proposed ERD is below:

Data Administration

Roles, Responsibilities, and Required Knowledge of a DBA

A database administrator (DBA) will need to be hired in order to maintain and manage the technical, security, training, and administrative aspects of the database implementation and use. The DBA role will require knowledge of many technological and managerial aspects, including hardware, software, infrastructure, clustering, administration, security, and training. The DBA will be responsible for installing, testing, operating, and maintaining the database; training users; providing support; and maintaining security of both the computerized data and all physical copies of any portion.

The DBA will need to know how to use the hardware (computers, monitors, servers, tablets, phones, workstations, firewalls, supercomputers, storage and backup devices, printers, switches, routers, credit card machines, etc…) and have a good idea of how to troubleshoot any database related issues encountered with the hardware. However, the DBA cannot be expected to know everything about the hardware, particularly any non-database issues that may arise, and will need either a reliable in-house technical support team or a reliable technical support hotline for the company’s hardware. The DBA will also need a solid understanding of the database infrastructure and set-up (which he or she will create and maintain); it will be essential to set up the infrastructure in the most efficient way possible. This includes the servers, processors, and storage devices utilized by the database, as well as the user devices. One method the DBA may wish to employ is called clustering; this is achieved by connecting several servers to the database. This way, if one server goes down, the database is still usable; in addition, if there is a heavy load of processing requirements, the servers can share the work in a preconfigured way so as to maximize their utility. The DBA may also wish to employ the clustered table storage model on the data itself. This method stores related data in neighboring locations on the hard disks and provides faster data processing.

Similarly, the DBA role requires a good working knowledge of any software employed by the database. At the most basic level, the DBA will need a solid working knowledge of whichever operating system is used by the company’s computers. This could be Windows, Linux, or Mac OS; these are the most common operating systems and would therefore have the most software and support available. The most essential aspect of a DBA’s knowledge is a thorough understanding of the database software (database management system, or DBMS). The design for Flix2You employs the Microsoft SQL Server software and the DBA will need to be an expert on how to use, manage, and train others to use this software. The DBA will also need to know how to create views and manage dashboards, as he or she will be responsible for maintaining these for managers, as well as training them on how to create and use them effectively.  Finally, the DBA will need to use security software, such as antivirus programs.

Besides the technical aspects of running the database, the DBA will have several administrative responsibilities. These include creating, maintaining, and auditing user accounts to ensure security, appropriate access levels, and proper usage; the DBA will also need to train users on how to use the database at whatever access level they are assigned. The DBA will need to provide security training and inform users any time there is an update to hardware or software that will affect their usage of the system. Periodically, the DBA will be required to provide refresher training on how to properly use the database, as well security issues and legal requirements.

Database Backup and Recovery

The DBA will need to implement a robust database backup and recovery program. This program will entail regular backups of the database and storage of these backups, as well as a recovery program in case of data breach or loss.

The primary component of the database backup program will be the hard drives that store copies of the data. A transaction log backup will be completed at close of business every day as soon as the last employee has logged off of his or her database account. This backup will capture all of the transactions of that day and store them in a local hard drive.  At the end of the day, the DBA or another designated person will put a copy of this data into a secure safe (described below). A differential backup will be completed every other day at close of business; this will also be a localized backup and will store a new copy of any data that has been modified in any way. The differential backups will also be stored in the safe. A full backup of the entire database will be completed weekly on the last full business day; the backup will be set to complete and collect all data by the close of business. A copy of this data will be stored in a fire- and water-proof safe in the Flix2You building; this safe will have at least two authentication mechanisms and only the DBA and upper management will be able to access it. An additional two copies of the full backup will be placed in a locked bag, taken to the local branch of the bank utilized by Flix2You, and stored in a secure safe there. Each month, the bank will securely transport the latest copy of the database to a central bank location for storage. In the event of a weather alert that may impact the physical location or the ability of the technology to complete a backup, the DBA will manually run a complete backup and take a physical copy of the backup to the secure storage location.  All transaction and differential backups will be kept for six months, after which time the oldest ones will be destroyed; all complete backups will be kept for three years, after which time the upper management and DBA will sign off on their destruction as appropriate.

Data Access and Security

It will be necessary for the DBA to implement a plan to keep data secure. This includes confidentiality (no unauthorized access or use of the data), integrity (consistency of the data including freedom from errors and no unauthorized changes), and availability (keeping the data accessible so authorized users can use it for their jobs).

There are both technical and managerial aspects to maintaining data security.  Technical aspects include changing default passwords, creating and enforcing password standards, updating all systems to the latest version, logging user access and usage, and potentially using encryption in the company network and communications. The DBA will be responsible for creating classes of users and assigning access privileges to each class, then assigning each user to a class and creating an appropriate account. The user will then be required to update the temporary password assigned by the DBA. Only users with the assigned login information will be allowed to use the system, and the DBA will be required to implement an enforced password change every three months (with some random changes). In addition, sensitive data will be encrypted both for storage and during any transmission over networks (including the company network). Managerial aspects of data security include assigning access privileges to users (this is done by the category of user), making sure no class of users can see more data than is necessary, auditing system use, and training users so that they will know what the legal and policy requirements are regarding their data usage.

An essential function of the DBA position will be keeping abreast of all security developments, including updates to company systems that improve security, possible current and emerging security vulnerabilities, and ways to guard against these vulnerabilities so they do not become threats. The DBA will need to keep records of all system usage and include these with the regular backup process so that, should there be any security problems, the DBA and his or her team will be able to trace them effectively. These records will include usage by company users and will also capture any attempted internal or external breaches of the data.

Although database security is important to the success and competitiveness of the business, it is also important for privacy and legal purposes. The DBA will be responsible for creating a privacy statement that will be distributed to all customers and updating it as needed. Customers will be required to agree to this statement before proceeding with their account creation; if there are any changes to the statement, the customer will be presented with the updated statement during his or her next transaction and will be required to agree to it before proceeding with the transaction.  The statement will specify where the customer’s data is stored and what will be done with it, as well as anything required by U.S. and Pennsylvania law (see legal section).  All employees will be required to take privacy training commensurate with their levels of access to customer data and will be required to sign a statement of privacy and non-disclosure in regards to all customer information, including but not limited to personal data, rental records, and financial data.

Dataload

It will be vital to properly update the system to prevent loss of data and correct reloading of the data into the system. This will be accomplished by using appropriate techniques to ensure the updated system will have all of the new features as well as all of the data.

The updates will take place during off-business hours and will not begin until every user has logged off for the day (or been automatically timed out). If the updates are unable to be completed before the opening of business, they will be stopped until the close of business so as not to interfere with operations. Ideally, there will be a holiday during which Flix2You is closed for a day or two so that the updates can be completed all at once. If possible, a backup computer setup will be available, in which case the current operating system, software, and data will be temporarily set up on the backup system and there will be no need to use off-business hours only. However, this is not likely to be the case.

In order to ensure the safety of the data in case of unexpected problems, the entire operating system and current software will be backed up, as will the database. Two copies of the backup will be sent to the bank and two will be placed in the company safe. Once the backups are completed and the business is closed for the day, the updates will begin. While we anticipate the successful completion of the updates within two days, Flix2You should be prepared for the updates to take up to a week. After the updates occur, the database will be copied into the updated system from the backups. Following this, the project will be nearly complete; the remainder will be quality and security testing to ensure the successful migration of the database back into the updated system, as well as initial user training. After this, the database will be turned over to the DBA and he or she will manage it throughout its lifecycle.

Database Dashboard and Analytics

Overview

Within Team No Judgment’s proposal, is an entirely new database design that will feature a robust database dashboard to provide Flix2You executives with a rapid snapshot of the company’s sales, customer demographics and behavior. This front-end user interface presents data in a clear and concise format to aid in the decision-making process to drive membership growth and total revenue, while allowing total customization capability. In addition to providing a bird’s eye view of business metrics, the dashboard will enable users to generate reports previously reserved for specialist positions like data scientists. Executives and key management are now in a position of power. The goal is to make smarter and faster decisions with query results returning in seconds, rather than days.

Design

Flix2You will feature a combination of Analytical, Operational, and Strategic dashboard design elements for a holistic business intelligence approach in an Executive Dashboard format, or “birds-eye view.”

Team No Judgment has selected third-party software provider, Dundas BI, for dashboard design, application, and management. Dundas has been selected for their excellent flexibility, stunning dashboard designs, easy usability, scalability, mobility, and above all, value for features amongst competitors. Dundas provides data insight in seconds, rather than hours or days, with little to no input from IT, by use of cloud storage management, and web app experience.

The key role of the dashboard is to display vital metrics of Flix2You. Metrics displayed include (in no specific order of importance):

• Movie Ratings – indicated by stars (1 through 5).
• Trending list of most rented movie titles.
• Monthly and quarterly revenues.

select year(2012) as SalesYear, month(2012) as SalesMonth, sum(transaction_amount) as TotalMonthlySales from financial_transactions;

• Total revenues to date.

select sum(transaction_amount) from financial_transactions;

• Number of titles in database/inventory.

select count(movie_id) from movies;

• Number of members.

select count(customer_id) from customers;

• Number of active members.

select customer_id from customers where exists (select customer_id from  customers where customer_id = customer_rentals.item_rental_id having count(item_rental_id) > 1);

• Number of inactive members.

select customer_id from customers where exists (select customer_id from  customers where customer_id = customer_rentals.item_rental_id having count(item_rental_id) < 1);

• Number of cancelled members in last 30 days.
• Projected number of future members based on current rate of activation – filtered by 3mo, 6mo, 1yr, 2yr, 5yr
• Geographic heatmap of nationwide users.

Features

• Connectivity: Dundas provides native connectivity to Microsoft SQL Server, which enables ease of data upload capabilities without having to write time-consuming lines of code.
  • In addition to pulling data from relational databases such as Microsoft SQL Server, Dundas can also pull data from Microsoft Excel, Access, and from web-based data sources such as Google Analytics or Amazon Redshift, which provides Flix2You with virtually limitless possibilities for expansion.
  • Any time data is changed on one source, data automatically updates in Dundas.
• Hosting: Dundas will provide cloud storage for scalability and security purposes. Through hybrid optimization, Dundas installs software on the local Flix2You server and then auto-uploads data to the Sisense cloud
• Mobility: optimized for iOS, Android devices or any device with HTML5 browser. Best of all, users can create and share dashboards, perform data analysis, and run reports on mobile devices.

Example of mobile dashboard view:

• Security:
• Administrative Control: admins can easily add and remove users, assign independent security profiles, and have complete control of Flix2You’s Dundas profile.
• Platform Security: top-tier firewall and network security technology is provided to prevent attacks, intellectual property & data theft, and corporate sabotage.
• Access Security: independent authentication for each user that complies with the Federal Information Processing Standards.
  • Security can be applied to multi-levels, such as a system whole, data level, down to individual row levels.
  • HTTPS for all pages.
  • Data is backed-up in multiple, geographic locations.
• Export: data can be exported to a CSV file format.

Data

Interactive analytics allows the dashboard to become a real, interactive experience, exploring data from many angles and specificity. Data can be visualized graphically with pie charts, maps, heat maps, bubble charts, pivots, tables, gauges, and sunburst.

Executive Dashboard Example:

Cost

Dundas offers a flexible licensing model that allows for scalability with affordability. The initial number of users is selected and the license can be purchased or leased yearly.

To support growth, Dundas features allow the tracking and monitoring of Flix2You’s usage, and will notify admin when full capacity is nearly reached, to make adjustments to the plan so all users can be supported. Essentially, pricing is scaled based on the scale of the business which means Flix2You will not overpay for unnecessary licenses.

Legal Issues

Flix2You Terms and Conditions for Use

1) Customers have accepted the Terms and Conditions for Use upon the start of subscription. Customers understand that by using, visiting, or browsing the Flix2You website, you accept and agree to these Terms of Use. If you do not agree to the Terms of Use, do not use Flix2You’s service. The Terms and Conditions for Use can also be located on Flix2You’s website.

2) Customers can expect to have their privacy maintained, as this is an important issue for Flix2You. Any personally identifying information is subject to Flix2You’s Privacy Statement, which can be found following this document. The Privacy Statement can also be located on Flix2You’s website.

3) Billing: The membership fee for Flix2You service will be billed at the beginning of each month following the first payment received. Customers are responsible for maintaining the service fee to avoid any service interruption.

There will be no refunds. Payments are nonrefundable and there will be no refunds or credits for partially used service periods.

Flix2You customers may cancel their membership at any time. Customers will still maintain access to the media downloading service through the entire paid billing period.

4) Individuals must be at least 18 years of age to become a member of the Flix2You service. Individuals under the age of 18 may be users and maintain separate profiles. However, these individuals will not have abilities to alter account information & settings.

Customers must agree to use Flix2You’s service in accordance with all applicable laws and regulations. Customers must agree not to archive, reproduce, distribute or use any content received from Flix2You without written permission from Flix2You.

Flix2You reserves the right to terminate your use of service, without notice if you are in violation of any of these Terms of Use.

5) Passwords: Flix2You customers are responsible for safeguarding their account passwords. While it is not required, Flix2You suggests changing account passwords at least every 6 months. Flix2You is not responsible for account misuse if customers share their passwords. Flix2You WILL NEVER REQUEST YOUR ACCOUNT PASSWORD.

6) Customer service support is available for all Flix2You members. Information regarding customer service and support can be found on Flix2You’s website. Assistance with your account can be attained through our Help Center via telephone or email.

Privacy Statement

Flix2You customers are responsible for maintaining their account information from exposure. Customers are encouraged not to share account information or passwords with other individuals to safeguard their account from misuse. Any mobile devices used to access Flix2You service should not store password information to avoid the possibility of misuse if the device is compromised.

Personally Identifiable Information (PII) will be used by Flix2You to verify users and maintain account records. Initial account setup will require customers to provide this PII to allow Flix2You to bind proper information to create accounts. This will also permit Flix2You to verify billing information. Flix2You will only request customers to additionally provide this PII in the event that customers forget their username and/or password.

Customer PII will be maintained to assist Flix2You in processing account registration, billing, and rental delivery locations. Flix2You will also use this information to reach out to customers to provide surveys, notifications or reminders to any future service changes. Flix2You may use customer information to provide a better membership experience. Flix2You may use this information to assist in customization, media selection, and to present promotional offers. These offers may include discounts on individual or bulk rental costs.

Customers are permitted to change account settings to limit the types of notifications received by Flix2You. However, customers will not be able to remove all notification settings such as account expiration, security issues or changes in service.

Flix2You will also monitor customer activity to influence a better membership experience. This may include the selections made by account users to create trends that will allow Flix2You to present more applicable media choices. This information will remain confidential and will not be shared with third-party companies or businesses.

By using Flix2You services, customers agree to the terms within the Privacy Statement. This Privacy Statement can also be located on Flix2You’s website. Any issues or concerns about the use of customer’s information can be presented to Flix2You’s Help Desk via telephone or e-mail.

List of References

Dashboard Reporting. Choosing the right type of dashboard for your business. (2017). Klipfolio.com. Retrieved 31 March 2017, from https://www.klipfolio.com/resources/articles/operational-analytical-bi-dashboards

Dundas Data Visualization, s. (2017). Business Intelligence and Analytics – Dundas Data Visualization. Dundas.com. Retrieved 15 April 2017, from http://www.dundas.com/dundas-bi

Netflix, Inc. (2017). Netflix Terms of Use. Help.netflix.com. Retrieved 16 March 2017, from https://help.netflix.com/legal/termsofuse

Computer Parts, Laptops, Electronics, and More. (n.d.). Retrieved April 25, 2017, from https://www.newegg.com/

Appendix