Flix2You Proposal - Pennsylvania State University



Flix2You ProposalbyDatabase Design AllstarsStephen Kupiec, James Bellefontaine, Michael Breisch, Clayton Henry12 Grove StNew York, NY 10014 (867) 555-5309forThe Pennsylvania State UniversityIST 210Section 001Professor: Gary HeberlingSubmitted: Friday, May 1Table of ContentsTable of Contents (1)Executive Summary (2)Project Overview (2)Project Management (4)Project PlanResources and BudgetUser Analysis (13)UsersDatabase ViewsOrganization of Data (17)Existing Database Analysis and RelationshipsSQL Queries to Create TablesDatabase Administration (21)Backup and RecoveryData Access and SecurityDataloadDatabase Dashboard and Analytics (26)Legal Issues (27)Privacy StatementTerms of ServiceReferences (31)Executive SummaryDatabase Design Allstars is a consulting-oriented company, intending to fill in the market need for a professional, customer-focused project management company. We specializes in modifications to database management systems of all types. ?We are offering this solution to meet the increasing demands of Flix2You. As a company we comprehend the restrictions of their current database design and below offer a design that will supply Flix2You with a new solid database management system that will present them with the most comprehensive data of their customers.Located in New York City, Database Design Allstars realizes that for us to prosper we need to be flexible and responsive. We need to please our clients by providing them with what they want, when they want it, and before any other company can offer it. We intend to achieve this goal of ours by flying the client out their own personal team of our very best here at Database Design Allstars! ?This team will stay with Flix2You from the beginning of the project till the very end of it, when the system goes live.Project OverviewWithin the last decade, an enterprise that has been reaching penultimate heights is the business of online movie rental and internet movie streaming services (Fisher-Ogden, 2014). ?FLIX2YOU is one of the opportunistic companies that have capitalized on this media boom but have currently met some growth challenges. ?Competitors such as Netflix, Redbox, and Amazon have outgrown FLIX2YOU both in terms of customer reach and revenue achieved (Fisher-Ogden, 2014). ?While this is discouraging, FLIX2YOU is determined to improve their standing amongst competition and bring about the dawn of a new era of prosperity within the business.Through an internal audit and vetting process, the following decisions have been made by FLIX2YOU leadership to bring about change regarding their business structure. ?The data collection that has taken place over many years regarding customer views and movie selections has become outdated and is causing performance issues for both company employees and customers of FLIX2YOU. ?It has been determined that this methodology of data collection must be better organized and restructured to fit the growing needs of the customer population. ?Furthermore, the database storing company information regarding the movie rental service must be retooled to allow for easier access to FLIX2YOU company data. ?Even though FLIX2YOU has quantum’s of information stored within the database, it has proven very difficult to effectively analyze the data given the current database structure and capabilities. ?This is the main cause of why growth of FLIX2YOU’s customer base and participation has dwindled.Our goal will be to create a database for Flix2You that makes customer experience a priority while also improving the performance and capabilities of the system as a whole. ?At first, it’s natural to think that the only possible way to achieve this is by upgrading hardware and software systems that are currently being utilized. ?Although that will be necessary in order to improve performance, those gains will not be felt without proper design and implementation of a database that efficiently analyzes the market. ?If you did a poll of what bothers most computer users when it comes to any type of computer technology, there is a high probability that complexity coupled with latency are two of the biggest issues. ?Database Design Allstars will look to reduce these two hindrances drastically through a series of process improvements and upgrades to the existing database system of Flix2You.First steps will involve improving the design of the current database that resides with Flix2You. ?This will be accomplished by effectively normalizing your data which will eliminate data redundancy and increase overall performance of your system. ?In doing so, not only will the database better support company goals but your customers will noticeably experience a much improved experience when using FLIX2YOU products. ?New Structured Query Language (SQL) will accompany this new layout which will benefit all database users by providing quicker and more effective query searches. Moving forward with this newly designed database will require some upgrades and improvements to current system hardware at your facilities. ?We will be adding numerous new state-of-the-art front end web-servers, SQL servers, and a new collocation center to house all vital company hardware among additional technological upgrades dedicated to the business. ?These improvements will ensure that Flix2You stays competitive with technological innovations that continue to advance data utilization across a plethora of media streaming organizations. ?Proper staffing will be essential for this transition to transpire in a smooth, efficient manner and training will be conducted (initially by the Database Administrator - subsequently by a training staff/team) immediately upon implementation to prevent any launch issues or conflicts. ?With this new database structure in place along with implementing numerous technological improvements to enhance overall capability, a new database dashboard will be constructed to assist the DBA and programmers in quickly identifying and analyzing data information as well as general performance. ?This new dashboard software accompanied by an updated website display for Flix2You will provide a variety of reports decision makers can access and use immediately to show trends in a customer’s rental history, favorite movies rented over the last month, and other important trends based upon customer activity. ?In addition, database dashboard information will be accessible across a number of mobile devices including laptop PC’s, mobile phones, and tablets that have secured access to the Flix2You network (Fisher-Ogden, 2014).The new Flix2You website layout will provide customers with easier navigation options and less “clicks” to rent the movie they want (. ?Gone are the days of spending 30 mins or so typing in search queries or billing information (Fisher-Ogden, 2014). ?Once a Flix2You member, a customer will have the option to “click to rent” and immediately (upon secured confirmation) have the movie come off our shelves and begin transit to that individual. ?Additionally, the Flix2You website will be an open-source for the revamped legal disclaimer(s) and privacy policy that all persons linked to the company must acknowledge and adhere to. ?All visitors will and can choose to provide or not provide certain information in regards to their identity and Flix2You will not provide any of that information to any other entity without their strict consent. ?Terms and conditions will be provided upon membership inauguration and must be accepted before being able to use the Flix2You service.Finally and probably most importantly we want to ensure a safe and secure product for both Flix2You company employees and customers. ?Any type of catastrophic failure involving the database will be minimized due to a robust backup and recovery plan that will prevent any loss of critical date or compromise of personal clientele information. ?Furthermore, any transaction conduction will include the proper encryption necessary to prevent any harm from a ??Trust is key to a successful online business and we are looking to guarantee customers that their personable identifiable information is in safe and capable hands when renting movies from the Flix2You movie rental service.This proposal will outline in depth what Database Design Allstars hopes to accomplish with this potential business venture with Flix2You. ?We believe we have the right solution to your database design and information analysis problems and we want this partnership to take off into the stratosphere. ?Please refer to the next section which summarizes the overall timeline of the project along with budgetary and resource information about what we hope to accomplish for Flix2You.Project ManagementMain Objectives Involving New Flix2You DatabaseRevamp and Revolutionize Flix2You database designCreate a comprehensive Backup and Recovery planConduct a user analysis for using the new database and manage views/read-write privilegesCreate database reports and dashboard views across various media platformsIdentify any legal issues for the new repository that Flix2You needs to be aware ofFlix2You Database Project Outline:Initial Setup of Database:Introduce Sample Size of Data - for testing ?use test server and dataDevelop SQL codingCreate SQL commandsTest SQL commands and ConfigurationsEdit/Recreate SQL commands to fit needsFinal Testing and ConfigurationOnsite SetupInstall Hardware at Flix2You HQ - Servers, Computers, etc… Configure/Setup Initial Database StructureLoad FLIX2YOU data ?(User & ?Original Company data)Database Testing and Configuration (see initial setup above)Test SQL commands and ConfigurationsIntegrate Security Softwaremultilevel authentication processRun Backup and Schedule additional Recovery Backups - Tape & Data Disk BackupsOn and Off Site LocationsFinal Configuration and Testing of Flix2You DatabaseEnd-User Training (conducted by DBA initially - subsequently Training specialists)Provide Open Forum to company employees- answer any questions, provide feedback, etc...Timetable for Full Implementation est. 2-3 months. ?Broad Estimate of Cost ?est. $80000-$100000Meet the Team:Project Manager (Stephen Kupiec) - The role of this individual will be to ensure that deadlines are met regarding initial project mapping, implementation, and final rollout of the new database system. ?This person will be the main contact with FLIX2YOU in regards to addressing any concerns in regards to this project initiative.Lead all aspects of project objectivesEnsure no shortcuts are taken while enforcing strict deadlines for project goalsEffectively manage project requirements, expenditures, and staff involved in completionResponsible for addressing any project concerns or risks that may ariseHolds others accountable to established performance levels to achieve individual and group goals.Serve as a positive, dynamic leader by displaying a hard work ethic, creative problem solving techniques, positive attitude and reinforcement, and team oriented leadershipDatabase Administrator (James Bellefontaine) - The Database Administrator will be ultimately responsible for the success or failure of the newly created FLIX2YOU database. ?Responsibilities include planning and organization of the new system, controlling and monitoring performance once system is in place, and constantly looking to maintain efficient operation of the new database. ?Will be required to proactively monitor database behavior and resolve any potential conflicts before they impact customer operations. ?Application administration tasks to oversee will include database privileges, user administration, database tuning and providing an efficient backup/recovery process. Troubleshoot data periodically and resolve customer issues being reported to Customer Support. This individual will need staff to carry out objective tasks in regards to database construction and implementation. ?He/She will supervise database analysts and specialists within the department.Implement New Database Model and provide support of Database environmentImprove upon insufficient design of previous Database ModelCreate secure and effective accessibility for Database usersRemove Data RedundancyManage Database Maintenance Remain informed on latest hardware and software developments, evaluating alternative approaches and recommending new hardware/software modifications to enhance FLIX2YOU customer experienceProvide resolutions of production systems for database problemsMonitor production applications including overseeing ?24x7 customer support requests through utilization of customer support staff membersDatabase Analyst (Michael Breisch) - Will work with Database Administrator in designing new database model for FLIX2YOU and lead a team of Database Operators & Analysts. Provides DBA with data source information, relationship flow diagrams, and other conceptual designs related to database construction. ?Lead analyst is going to determine what needs rectified with old database design while adding new improvements in creating a new model for FLIX2YOU’s data. ?This will include thoroughly examining the pre-existing database programming code, creating new code, and ensuring overall improvement of database functionality. ?Be responsible for explaining, organizing, and presenting data via Database Design Allstar’s dashboard and web applications for users to query and obtain data efficiently. ?Will organize database usage, create applications and tools to increase productivity, and maintain research documentation. ?Create and Utilize SQL Programming to generate actionable data & meaningful data flow.Design solution for current database needs based on specific business requirementsManage Database programming code/language to support user-friendly access interfacesAnalyze Data using various research & analysis techniques/sources to effectively serve as head of team of specialists; be responsible for communicating data characteristicsOversees user application issues to provide effective resolutions, and instructs business specifications to database users to ensure applications are utilized effectively.Enact application process improvements to better organize information into usable formats.Creates and maintains SQL server maintenance plans within the Database group to guarantee server optimization, and that data integrity remains consistent for long-term functionality.Programmer (Clayton Henry) - Writes and develops software programs related to FLIX2YOU project. ?Will work with a team of developers under the supervision of the Database Administrator and with cooperation from numerous Database Analysts to maintain, develop and add functionality to FLIX2YOU’s current database structure. ?Design code for client/server applications and data structures for business applications. Test and implement software solutions to satisfy specific business needs and be available to address any specific coding questions. ?Ensure that software code addresses and rectifies past performances issues.Craft new programming methodologies to implement into FLIX2YOU’s new databaseConsult with Database Administrator and Analyst Team in database code constructionTest and confirm program functionality and rectify any potential issuesQuality Assurance (Michael Breisch) - Will ensure all tasks performed on project meet all Quality Standards set by FLIX2YOU quality department. ?Designs, installs and continually evaluates complex quality assurance control systems to ensure database integrity. ?Develops all policy regarding gathering customer data and provides an open and in-depth policy statement detailing methods used in data gathering. ?Monitor and enforce communications to customer population when changes to company policies or technologies occur. ?Creates legal terms and conditions for FLIX2YOU customers in order to protect FLIX2YOU from any invalidated court actions or sanctions.Build & Revise Privacy Statement(s) and Legal Policies for Flix2YouDevelop Company Policy regarding Customer Membership and RegulationProvide open-source for customers regarding information gathered by Flix2You Budget for Database Design All-Stars - Flix2You Database ProjectManpower/Labor Cost (Salaries averaged from similar positions and multiple companies - )RoleTeam MemberSalaryProject ManagerStephen Kupiec$20000.00Database AdministratorJames Bellofontaine$18500.00Database Analyst & Quality AssuranceMichael Breisch$19250.00ProgrammerClayton Henry$17500.00Total$75250.00Hardware Components EstimateDell PowerEdge R920 Rack Server (Dell PowerEdge r920, 2014)ComponentProduct Make/ModelProcessorIntel ? Xeon ? E7-4800 v2 (4)Operating SystemMicrosoft Windows Server 2012CPU ChipsetIntel C602JMemoryUp to 6TB (96 DIMM slots):4GB/8GB/16GB/32GB/64GB DDR3L, RDIMM, LR-DIMM up to 1600MT/sStorage24 X 1.2 TB HDD Supported Drives Up to 24 2.5" 12Gb/6Gb SAS HDD PowerHot-Plug redundant PSUs: 750W AC, 1100W AC & DCTotal Cost$9699.00 (PowerEdge r920 rack, 2014)Total Cost Labor & Components $84,949.00Flix2You Proposal1/26/15 5/1/15Contract & MOU1/26/151/30/15 James Bellefontaine , Michael Breisch , Clayton Henry , Stephen Kupiec Executive Summary3/2/153/27/15 Stephen Kupiec Project Overview2/2/152/27/15 Stephen Kupiec Project Management3/30/154/24/15 James Bellefontaine , Stephen Kupiec User Analysis2/2/152/27/15 James Bellefontaine Organization of Data3/2/153/27/15 Michael Breisch Database Administration3/2/153/27/15 James Bellefontaine , Clayton Henry Dashboard & Analytics2/2/152/27/15 Michael Breisch , Clayton Henry Legal Issues3/30/154/24/15 Michael Breisch , Clayton Henry Peer Evaluation4/27/155/1/15James Bellefontaine , Michael Breisch , Clayton Henry , Stephen Kupiec Flix2You Project Plan5/4/158/7/15Analysis5/4/155/29/15Project Proposal Analysis5/4/155/8/15Michael Breisch Database Analysis5/4/155/8/15James Bellefontaine , Clayton Henry Setup Development Environment5/18/155/29/15 James Bellefontaine , Michael Breisch , Clayton Henry , Stephen Kupiec Design New Data Structure5/11/155/15/15 Michael Breisch Order Server Rack15/4/155/8/15Stephen Kupiec Build Server5/11/155/15/15James Bellefontaine , Clayton Henry , Stephen Kupiec Development6/1/157/3/15Build DB tables6/1/156/5/15Michael Breisch , Stephen Kupiec Coding (scripts)6/1/156/12/15 James Bellefontaine , Clayton Henry Load Test Data6/15/156/19/15 James Bellefontaine , Michael Breisch , Clayton Henry , Stephen Kupiec Create queries6/8/156/12/15 Michael Breisch , Stephen Kupiec Build Reports6/22/157/3/15Michael Breisch , Stephen Kupiec Build Webpages6/22/157/3/15James Bellefontaine , Clayton Henry Implementation7/6/157/31/15Setup new server7/6/157/8/15James Bellefontaine , Michael Breisch , Clayton Henry , Stephen Kupiec Setup DB Environment7/9/157/10/15James Bellefontaine , Michael Breisch , Clayton Henry , Stephen Kupiec Run DB Build Scripts7/13/157/15/15 James Bellefontaine , Michael Breisch , Clayton Henry , Stephen Kupiec Perform Data Load7/16/157/17/15 James Bellefontaine , Michael Breisch , Clayton Henry , Stephen Kupiec Test Data Load7/20/157/24/15 James Bellefontaine , Clayton Henry Run Queries7/20/157/24/15 Michael Breisch , Stephen Kupiec Schedule Data Refresh7/27/157/31/15 James Bellefontaine Schedule Test Backup7/27/157/31/15 Clayton Henry Train New Users7/27/157/31/15 Michael Breisch , Stephen Kupiec Sign Off8/3/158/7/15Reports8/3/158/7/15James Bellefontaine , Michael Breisch , Clayton Henry , Stephen KupiecUser AnalysisDue to implementing the Database Design Allstars new database schema for FLIX2YOU’s movie rental service, certain objectives will need to be maintained which will require users of the new database to periodically update and effectively manage overall database usage. ?We propose that Flix2You needs to expand staffing requirements to ensure future success as a customer service based organization. These vital roles are as follows:Database Administrator: ?The DBA will oversee the development and administration of the database for Flix2You. The roles and responsibilities are defined in our Project Management portion of our proposal, but this individual will work closely with all Database personnel to ensure the database is being utilized in the most effective, safe, and efficient manner possible.Database Operators: ?The Database Operators will have several important tasks. They will log important database activity such as daily query runs, database changes made, database redundancies detected, and other more critical errors. These logs will be useful for identifying specifically when any problem with the database was encountered. The Database Operators will also be charged with implementing Database access commands which limit access to specific groups or individuals within FLIX2YOU and will be determined by the Database Administrator. Furthermore, when additional movies are added to FLIX2YOU’s library, the Database operators will add the new movie stock to the records of the database.Database Integrity Officer: The Database Integrity Officer will be overseeing overall security and integrity of all entities within the database. Working with the DBA, they will establish and enforce strict rules and guidelines for use of all computer equipment, servers, software, and other technologies related to the database which all staff must adhere work Security SPECIALIST: The role of Network Security Specialist is to ensure ultimate Network Security against all outside or external threats. ?This individual will monitor all customer activity to ensure all incoming server traffic and requests do not contain any harmful threats (virus, Trojan Horses, and other various forms of malware). ?Will serve in support of Database Integrity work Administrator: Contrary to the Network Security Specialist, the Network Administrator will work closely with the DBA to ensure the security of the database’s information and overall structural integrity within Flix2You’s new system. ?Role will include disseminating feedback from Flix2You database users to tune network specifications for business needs. ?Evaluate all network performance benchmarks and continually look to enhance performance.Training Specialist: Following initial training of database functionality by DBA, this individual could serve as the go-to-source for database functionality and features. ?This person will oversee a training department of a handful of qualified database operators who show a willingness to instruct others on how to best make use of the database and make user-friendly instructional modules including proper tutorials on database utilization. Web Connection Analysts: Will continually monitor internet connectivity involving the new Flix2You database. ?Will work closely with Network Administrators and Network Security Specialist and provide reporting information regarding all network traffic patterns. ?Look to constantly enhance bandwidth without compromising security or database integrity. ?Web Connect Analysts will support Flix2You’s Network Administration in ensuring network efficiency.Web Application/Design Specialists: This team will continue to design and suggest new, innovative ways for users to interact with FLIX2YOU customers via the company website. ?Any interaction conducted by a customer will be verified that accurate results are provided in a clear, concise manner and FLIX2YOU will provide the best customer experience possible through applications that point customers to most popular and sought for movies.Senior Management Team: ?This leadership team will determine where FLIX2YOU is going and will be going in terms of business strategy. ?This team with assistance from the various research departments within FLIX2YOU will determine how the company can improve upon its current standing against competitors such as Netflix, Amazon, and Redbox and determine strategic planning for future business ventures. ?Business rules will be established with collaboration from Project Manager.Operation Management Team: ?This team will be headed by the Project Manager and include other heads from various other entities of FLIX2YOU. ?They will follow and enforce business rules enacted by the Senior Management Team and will focus on the overall success of this current project. ?They will manage project guidelines, tasks, budget, staff involved, and overall final goal or goals achieved upon project completion.Human Resources: ?This department of FLIX2YOU will work with Department Leaders and Senior Management in determining company needs in terms of staffing required going forward with FLIX2YOU. ?Charged with interviewing current employees along with people who are potentially seeking employment with FLIX2YOU in order to conclude the best fits for positions needed with the business.Finance/Accounting/Payroll: ?This team will handle all incoming financial transactions and work with Integrity Officer to ensure secure financial transactions between FLIX2YOU and its’ customers. ?In addition, this group will handle all Flix2You expenditure reports, filing of company taxes, and Flix2You employee payroll.Marketing: ?Advertising is extremely important to customer service oriented companies and FLIX2YOU will be heavily invested in positioning the company among the industry leaders in terms of popularity with effective and robust marketing campaigns. ?These individuals will work together with Business Analysts to continually innovate methods of reaching various customer age groups and demographics.Business Intel & Research Analysts: ?Will serve to actively support and provide decision makers across the Flix2You enterprise with in depth analysis of information gathered from customers. ?Utilize multiple analytical techniques to provide insight to company leadership regarding market trends and competitor actions. ?Data will be interpreted by Intel Analyst team and analysis will be provided to leadership in efforts of resolving any potential conflicts with the database.Programmers: ?Falling under strict direction from the Database Administration, the programming team is responsible for effectively programming and designing database applications to be easily accessed by Flix2You personnel. ?Manage all SQL involved in database construction and provide instructions for Flix2You records being stored, organized, managed, and updated within database. Quality Assurance: ?Quality Assurance team will ensure that all Flix2You employees follow industry guidelines regarding all business practices and transactions. ?Develop thorough legal statements and privacy disclosures detailing what information will be gathered from each Flix2You customer upon and during membership. ?Outlines terms & conditions of Flix2You service.Customer Service: ?Comprised of telephone & IT support members, this branch will ensure that all Flix2You customer needs are met and questions are answered to utmost satisfaction. ?Will be working together with Finance division to continually update database with compile accurate customer information including precise addresses, phone numbers, and other customer identifiable information.Database Privileges:To better ensure database integrity, the database views and accesses must be properly managed to prevent any unknowledgeable or unqualified party from making what may seem to be a harmless action turn into a catastrophic error or complete failure of the system. ?Each department will be granted access that fits the roles they fill within the Flix2You business. ?These views will be carefully managed by the DBA and later put into action by the Database Operators. ?These are our suggestions for how these accesses should be implemented:Database Administrator: ?Granted all exclusive rights to every view of database and will be permitted to make any crucial alterations to make certain of proper functionality of database.Database Operators: ?Granted access to all Database Tables (not permitted to edit entities or attributes unless strictly given permission/access by the DBA).Database Integrity Officer: ?Granted to access views and commands given to each specific user of the database to validate security protocols are being work Security Specialist & Network Administrator: ?No access will be granted to database unless needed to correct any connectivity issues with server.Training Specialist: ?Only due to special circumstances for training purposes will be allowed to access certain views but no editing privileges will be granted without approval from DBA.Web Connection Analysts & Web Application/Design Specialists: ?No database views or access needed for role objectives to be successfully met and performed.Senior Management Team: ?Views accessible to effectively make decisions and to properly view different analysis information from database. ?No write accesses granted.Operation Management Team: ?Views accessible to tables that correlate with leadership role within Flix2You. ?Changes must be submitted to DBA or operators (final approval by DBA) to take effect.Human Resources: ?No database views or access needed for role objectives to be successfully met and performed.Finance/Accounting/Payroll: ?Views granted related to customer financial transaction information only. No writing permissions granted.Marketing & Business/Research Analysts: ?Will be granted full view of database to effectively analyze where to target customers or business areas more heavily. ?All suggestions or additions to database must be submitted to DBA.Programmers: ?Given views and writing privileges based off of strict guidelines provided by DBAQuality Assurance: ?No access or views granted. ?All internal information will be provided from Database Integrity Office.Customer Service: ?Access and views pertaining to customer information only. ?Will be allowed to properly edit and maintain attributes of Flix2You customers within anization of DataFlix2You’s current database worked well when the company was small, however and off-the-shelf solution cannot scale to a degree necessary to bring the company to the level of sales we anticipate. The modifications made by the previous consultants were effective in ‘making it work’, however given the current setup, we can expect data redundancies, which will cost the company efficiency and processing power, data inconsistencies which will inevitably lead to costs due to incorrect inventory, improper billing, and a host of business issues, and corrupt data, which will negatively impact all aspects of the business for both customers and stakeholders. In order to avoid the business costs of an improperly designed database, we intend to normalize the database using standard techniques. While there is an efficiency tradeoff involved, but at the level we are dealing with the actual computation (and therefore hardware) costs are minimal between the second and third forms. As the company grows further, there may be a push to denormalize, but for now, we believe organizing the data in what is called the third normal form is ideal.Normalization is an iterative process. Each succeeding form of normalization is in the ‘lower’ form, but also satisfies a requirement. In order to normalize, we analyzed your database with the first normal form (1NF) in mind. The requirement is that there are no repeating elements (or groups of elements). We identified the primary keys of each table, and the dependencies of each of them. The database does pass the requirements for first normal form.We moved to the second normal form (2NF) which requires that we remove partial dependencies. We created new tables to accomplish this. We split out the tables so that all columns were directly dependent on the primary key of that table. Finally, we made sure that the database is in third normal form (3NF). In order to guarantee this, we verified that the database satisfied 1NF and 2NF. Further, we ensured that all transitive dependencies were removed from each table. This means that all dependencies are based on the primary key (PK) and only the primary key. As you can see by the below code snippet, we have reworked several of your tables in order to accomplish this./* Demonstration table creation for FLIX2You *//* proposal by Database Design Allstars *//* Submitted May 1, 2015*//* IST 210, Spring 2015*//* (c) James Bellefontaine, Michael Breisch,*//* (c) Stephen Kupiec, Clayton Henry*/DROP TABLE film_cast;DROP TABLE movie;DROP TABLE actor;CREATE TABLE actor(actor_id int IDENTITY(1,1) NOT NULL,actor_name varchar(32) NOT NULL);CREATE TABLE movie(movie_id int IDENTITY(1,1) NOT NULL,movie_title varchar(32) NOT NULL,movie_year char(4) NOT NULL,movie_description varchar(128) NOT NULL);CREATE TABLE film_cast(cast_id int IDENTITY(1,1) NOT NULL,movie_id int NOT NULL,actor_id int NOT NULL);INSERT INTO actor VALUES('Tom Cruise');INSERT INTO actor VALUES('Val Kilmer');INSERT INTO actor VALUES('Nicole Kidman');INSERT INTO movie VALUES ('Top Gun', '1986', 'The Top Gun Naval Fighter Weapons School is where the best of the best train to refine their elite flying skills. When hotshot fighter pilot Maverick (Tom Cruise) is sent to the school, his reckless attitude and cocky demeanor put him at odds with the other pilots, especially the cool and collected Iceman (Val Kilmer).');INSERT INTO movie VALUES('Batman Forever', '1995', 'Batman (Val Kilmer) faces off against two foes: the schizophrenic, horribly scarred former District Attorney Harvey Dent, aka Two-Face (Tommy Lee Jones), and the Riddler (Jim Carrey), a disgruntled ex-Wayne Enterprises inventor seeking revenge against his former employer by unleashing his brain-sucking weapon on Gotham City\'s residents.');INSERT INTO movie VALUES('Eyes Wide Shut', '1999', 'After Dr. Bill Hartford\'s (Tom Cruise) wife, Alice (Nicole Kidman), admits to having sexual fantasies about a man she met, Bill becomes obsessed with having a sexual encounter.');INSERT INTO film_cast VALUES(1,1);INSERT INTO film_cast VALUES(1,2);INSERT INTO film_cast VALUES(2,2);INSERT INTO film_cast VALUES(3,1);Database AdministrationThe Database Administrator holds the ultimate leadership role involving all maintenance, security, backup, and recovery procedures relating to the database. ?This will guarantee that the database is most likely running at the most efficient and at a acceptable performance level at all times. ?This is the breakdown of the overseeing roles of the DBA in terms of being the caretaker of the new Flix2You Database.Discuss strategies with Operators and other personnel privy to ensuring that the database system runs at the utmost highest performance level possiblePrepare a comprehensive and effective Backup & Recovery Plan for the new systemDetermine and Mandate data access and security for the Flix2You databaseEnsure secure incoming/outgoing network transmissions of data Identify proper scheduling for repository data refresh (Dataload)Our Team at Database Design Allstars believes that security is a top priority. We use multiple levels of security to ensure con?dentiality, integrity, and availability of data. We believe in order to protect this data that it requires a companywide approach. This approach includes:The Users (Internal and External)A detailed privacy statement must be accepted by all users creating an account.All users must have passwords. ?A password must have a minimum of 8 characters.A password must have a maximum of 16 characters.When changing a password at least 4 characters must be different than the previous password.When changing a password it must not be the same as any previous password used.Social Security numbers, names, and birth dates cannot be used as passwords.The Users (Internal Only)Passwords must be changed every four months (Internal)Incorporate multilevel authentication and security screens.Education about sensitive data.Restriction on outside of network activity.Employees must sign a disclaimer on sensitive data.*We also strongly suggest security cameras not only where the database is set up but also at the internal workstation areas. ?Also, room or rooms containing key database servers and hardware will be granted access only to critical personnel whom will have access via keypad entry (code will automatically change every week). ?Hardware Systems Including Workstations & ServersUser access rights assigned to workstations.Security locks installed on hardware.Kill Switch software installed in case of theft.Backup & Recovery plans.Flash Drive use restrictions.Implement file system security.Encrypt data at the file system and database level.Software Including Operating Systems and ApplicationsOS updates must be applied immediately and checked manually at least once a week to ensure success of update. Antivirus and Antispyware installed and updated regularly on all machines.Restrict software installation to only authorized applications with group policies..Periodic software updates and system backups.Enforce auditing of activity.Vulnerability TestingWebsites will follow PCI Compliance StandardsWebsites will have a SSL Certificate*We suggest not allowing email on devices connected to the internal network, if necessary, spam filters and antivirus must be installed with education on safe practices. ?No external hardware or software shall be introduced to these devices to include external hard-drives, usb-flash drives, Compact Discs, Peripheries, and other plug-to-play devices without strict authorization from Security Office.The Network and its HardwareAdvanced Firewalls Installed.Multiple Intrusion Detection Systems (IDSs) used.Active and passive network activity monitoring.Use of Network Access Control (NAC)Use of Virtual Private Networks (VPNs)Backup & Recovery For a business to avoid a potential catastrophic event of infinite data loss by internal or external sources, it is imperative to have in place a comprehensive backup & recovery plan to prevent any such scenario from taking place. ?This is a key responsibility bestowed upon the DBA and should not be taken lightly. ?The main objective of this plan is to ensure that performance does not falter at a time of crisis while also guaranteeing no compromise of data integrity or loss of any database information (Akhtar, Buchholtz, Ryan & Setty, 2012). ?The following criteria outlines the effective Backup & Recovery Procedures we’ll be utilizing:Develop Comprehensive Plan for Backup of Critical Data and Timely Recovery ProcessCreate efficient schedule for database backups to occurPresent Plan to Senior Leadership Team providing full Disclosure of ProtocolEffectively train all employees in handling scenarios that will require implementing Backup & Recovery initiativesKeep all Backup & Recovery tools up to date and fully functioning at all times (Under no circumstance can there be a lapse of service while database is operational). Database Logs and Images will be recorded and stored upon each scheduled BackupFailsafe guards such as backup power supplies, fire-retardant surfaces, and water sealing infrastructure will be implemented around Database Server HardwaresBackup versions of Database will be stored both at internal and external locationsBackup & Recovery Plan will protect the following Flix2You Systems and Information:Operating Software (OS) - System Restore will be initiated upon failure. ?Will backup database server’s default settings and additional system updates or changes.Relational Database Management System (RDBMS) - Backup upon origination and subsequently after patches/updates to system.Database Application Software - Upon installation and following all updatesUser Id’s & Passwords - ?Any information pertaining to Flix2You employees stored with database will be preservedDatabase Entities & Attributes - Structural integrity of database must be maintained to prevent any denial of service for customer populationDatabase Design Allstars will be planning on multiple backup variations involving the newly created Flix2You database system in the event of minor to catastrophic failure. ?With each variation comes different time durations of completion, information covered, and frequency of occurrence (Akhtar, Buchholtz, Ryan & Setty, 2012). ?The DBA must be familiar with all variations and have them complement each other in compiling a wide-range of recovery flexibility. ?These backups will be scheduled to take place a low activity points to prevent database user discomfort or poor database performance at peak hours (Akhtar, Buchholtz, Ryan & Setty, 2012). ?Full RDBMS Backup - Covers entire database - performed weekly - Fri Evening/Sat Morning ??60 min durationDifferential Backups - Covers changes to information or objects since last backup transpired - Daily occurrence ?15 min durationIncremental Backups - Daily Backups including all transaction-log information - Daily occurrence every few hours a day - ?15 min duration ?These backups will be performed onsite and go directly to disk due to greater speed efficiency (Akhtar, Buchholtz, Ryan & Setty, 2012). ?They can either be performed locally by someone at the main interface display of the database or through the network via secure login. ?The disks containing the backups will then be transferred to tapes which will be stored both onsite and offsite for disaster recovery purposes (Akhtar, Buchholtz, Ryan & Setty, 2012). ?Disk backups will provide DBA with a faster restore option while tapes will provide a supplemental backup option if disk backup fails (Akhtar, Buchholtz, Ryan & Setty, 2012). ?These backups must be properly managed in order to prevent any loss of integrity.Recovery Protocol:Database Restores will be tested periodically - will test both disk and tape backupsValidating Backup Structure - DBA can check composition without performing full restorationDuring Actual Restore - Backups will always be performed prior to full system restore Simulate Restoring Database based on various scenarios (corruption, physical damage, etc…)Time of Recovery Plan will be based off of Simulation Results Transfer of FLIX2YOU DataloadDatabase Design Allstars are dedicated to protecting and maintaining a smooth transfer of customer, movies, rental, and other information already collected and stored previously by Flix2You. ?Our customer service division will be able via an encrypted secure server to continually add and update customer information via web-based application. ??A similar looking application will be used to control Flix2You’s ever evolving inventory. ?To transfer the old Flix2You database information will just require loading the backup & recovery software (pulling stored information from backup tape or disk) into the new database server. ?Due to the new database running Microsoft SQL server, this transition should be seamless and thus have little to no impact on daily operations once the new database begins to operate. ?These database archives will be dated or labeled as being from the old format pre-launch but will be integrated into the new database server format as well.Database Dashboard & Analytics Legal IssuesPrivacy Policy ()This privacy policy has been compiled to better serve those who are concerned with how their 'Personally identifiable information' (PII) is being used online. PII, as used in US privacy law and information security, is information that can be used on its own or with other information to identify, contact, or locate a single person, or to identify an individual in context. Please read our privacy policy carefully to get a clear understanding of how we collect, use, protect or otherwise handle your Personally Identifiable Information in accordance with our website. What personal information do we collect from the people that visit our blog, website or app? When ordering or registering on our site, as appropriate, you may be asked to enter your name, email address, mailing address, phone number, credit card information or other details to help you with your experience. When do we collect information? We collect information from you when you register on our site or enter information on our site. How do we use your information? We may use the information we collect from you when you register, make a purchase, sign up for our newsletter, respond to a survey or marketing communication, surf the website, or use certain other site features in the following ways: To personalize user's experience and to allow us to deliver the type of content and product offerings in which you are most interested. To improve our website in order to better serve you. To allow us to better service you in responding to your customer service requests. To administer a contest, promotion, survey or other site feature. To quickly process your transactions. To send periodic emails regarding your order or other products and services. How do we protect visitor information? Our website is scanned on a regular basis for security holes and known vulnerabilities in order to make your visit to our site as safe as possible. We use regular Malware Scanning. Your personal information is contained behind secured networks and is only accessible by a limited number of persons who have special access rights to such systems, and are required to keep the information confidential. In addition, all sensitive/credit information you supply is encrypted via Secure Socket Layer (SSL) technology. We implement a variety of security measures when a user places an order enters, submits, or accesses their information to maintain the safety of your personal information. For your convenience we may store your credit card information kept for more than 60 days in order to expedite future orders, and to automate the billing process. Do we use 'cookies'? Yes. Cookies are small files that a site or its service provider transfers to your computer's hard drive through your Web browser (if you allow) that enables the site's or service provider's systems to recognize your browser and capture and remember certain information. For instance, we use cookies to help us remember and process the items in your shopping cart. They are also used to help us understand your preferences based on previous or current site activity, which enables us to provide you with improved services. We also use cookies to help us compile aggregate data about site traffic and site interaction so that we can offer better site experiences and tools in the future. We use cookies to: Help remember and process the items in the shopping cart. Understand and save user's preferences for future visits. Compile aggregate data about site traffic and site interactions in order to offer better site experiences and tools in the future. We may also use trusted third party services that track this information on our behalf. You can choose to have your computer warn you each time a cookie is being sent, or you can choose to turn off all cookies. You do this through your browser (like Internet Explorer) settings. Each browser is a little different, so look at your browser's Help menu to learn the correct way to modify your cookies. If you disable cookies off, some features will be disabled It won't affect the users experience that make your site experience more efficient and some of our services will not function properly. However, you can still place orders. Third Party Disclosure: We do not sell, trade, or otherwise transfer to outside parties your personally identifiable information unless we provide you with advance notice. This does not include website hosting partners and other parties who assist us in operating our website, conducting our business, or servicing you, so long as those parties agree to keep this information confidential. We may also release your information when we believe release is appropriate to comply with the law, enforce our site policies, or protect ours or others' rights, property, or safety. However, non-personally identifiable visitor information may be provided to other parties for marketing, advertising, or other uses. Third party links: We do not include or offer third party products or services on our website. Google: Google's advertising requirements can be summed up by Google's Advertising Principles. They are put in place to provide a positive experience for users. we have not enabled Google AdSense on our site but we may do so in the future. California Online Privacy Protection Act: CalOPPA is the first state law in the nation to require commercial websites and online services to post a privacy policy. The law's reach stretches well beyond California to require a person or company in the United States (and conceivably the world) that operates websites collecting personally identifiable information from California consumers to post a conspicuous privacy policy on its website stating exactly the information being collected and those individuals with whom it is being shared, and to comply with this policy. - See more at: According to CalOPPA we agree to the following: Users can visit our site anonymously. Once this privacy policy is created, we will add a link to it on our home page, or as a minimum on the first significant page after entering our website. Our Privacy Policy link includes the word 'Privacy', and can be easily be found on the page specified above. Users will be notified of any privacy policy changes: On our Privacy Policy Page Users are able to change their personal information: By logging in to their account How does our site handle do not track signals? We honor do not track signals and do not track, plant cookies, or use advertising when a Do Not Track (DNT) browser mechanism is in place. Does our site allow third party behavioral tracking? It's also important to note that we allow third party behavioral tracking COPPA (Children Online Privacy Protection Act) When it comes to the collection of personal information from children under 13, the Children's Online Privacy Protection Act (COPPA) puts parents in control. The Federal Trade Commission, the nation's consumer protection agency, enforces the COPPA Rule, which spells out what operators of websites and online services must do to protect children's privacy and safety online.We do not collect information from children under 13 We adhere to the following COPPA tenants: Parents can review, delete, manage or refuse with whom their child's information is shared through contacting us directly. Fair Information Practices: The Fair Information Practices Principles form the backbone of privacy law in the United States and the concepts they include have played a significant role in the development of data protection laws around the globe. Understanding the Fair Information Practice Principles and how they should be implemented is critical to comply with the various privacy laws that protect personal information. In order to be in line with Fair Information Practices we will take the following responsive action, should a data breach occur: We will notify the users via email Within 7 business days. We will notify the users via in site notification Within 7 business days. We also agree to the individual redress principle, which requires that individuals have a right to pursue legally enforceable rights against data collectors and processors who fail to adhere to the law. This principle requires not only that individuals have enforceable rights against data users, but also that individuals have recourse to courts or a government agency to investigate and/or prosecute non-compliance by data processors. CAN SPAM Act: The CAN-SPAM Act is a law that sets the rules for commercial email, establishes requirements for commercial messages, gives recipients the right to have emails stopped from being sent to them, and spells out tough penalties for violations. We collect your email address in order to: Send information, respond to inquiries, and/or other requests or questions. Process orders and to send information and updates pertaining to orders. We may also send you additional information related to your product and/or service. Market to our mailing list or continue to send emails to our clients after the original transaction has occurred To be in accordance with CANSPAM we agree to the following: NOT use false, or misleading subjects or email addresses. Honor opt-out/unsubscribe requests quickly. Allow users to unsubscribe by using the link at the bottom of each email If at any time you would like to unsubscribe from receiving future emails, you can email us at: unsub@ Follow the instructions at the bottom of each email and we will promptly remove you from ALL correspondence.Contacting Us: If there are any questions regarding this privacy policy you may contact us using the information below. Flix Drive, Scranton, PA 18508 USA help@ (555) 961-0757Last Edited on 2015-04-13 (Partially Generated with Privacy Policy Services LLC)TERMS OF SERVICE AGREEMENT PLEASE READ THE FOLLOWING TERMS OF USE AGREEMENT CAREFULLY. BY ACCESSING OR USING OUR SITES AND OUR SERVICES, YOU HEREBY AGREE TO BE BOUND BY THE TERMS AND ALL TERMS INCORPORATED HEREIN BY REFERENCE. IF YOU DO NOT EXPRESSLY AGREE TO ALL OF THE TERMS AND CONDITIONS, THEN PLEASE DO NOT ACCESS OR USE OUR SITES OR OUR SERVICES. THIS TERMS OF SERVICE AGREEMENT IS EFFECTIVE AS OF 05/01/2015. ACCEPTANCE OF TERMS. The following Terms of Service Agreement (the "TOS") is a legally binding agreement that shall govern the relationship with our users and others which may interact or interface with Flix2You, also known as FIix2You and our subsidiaries and affiliates, in association with the use of the Flix2You website, and its Services which shall be defined below. DESCRIPTION OF WEBSITE SERVICES OFFERED. The Site is an e-commerce website which has the following description: FIix2You rents and sells movies. Any and all visitors to our site, despite whether they are registered or not, shall be deemed as "users" of the herein contained Services provided for the purpose of this TOS. Once an individual registers for our Services through the process of creating an account the user shall then be considered a "member." The user and for member acknowledges and agrees that the Services provided and made available through our website and applications, which may include some mobile applications and that those applications may be made available on various social media networking sites and numerous other platforms and downloadable programs are the sole property of Flix2You. At its discretion. Flix2You may offer additional website Services and/'or products, or update, modify or revise any current content and Services, and this Agreement shall apply to any and all additional Services and/or products and any and all updated, modified or revised Services unless otherwise stipulated. FlixZYou does hereby reserve the right to cancel and cease offering any of the aforementioned Services and/or products. You as the end user and-"or member acknowledge accept and agree that Flix2You shall not be held liable for any such updates, modifications, revisions, suspensions or discontinuance of any of our Services and or products.ReferencesAkhtar, A. N., Buchholtz, J., Ryan, M., & Setty, K. (2012). Database backup and recovery best practices.?The ISACA Journal,?1, 16. Retrieved from poweredge r920. (2014). Retrieved from , Philip (2014).?Netflix viewing data architecture evolution?[PowerPoint slides]. Retrieved from , P., Kojo, J., Li, J., & Zimmer, M. (2015). Netflix's viewing data: How we know where you are in house of cards [Web Blog]. Retrieved from r920 rack server. (2014). Retrieved from Policy Services LLC (2015) Retrieved from ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download