Programming
Data Storage Organization Physical Database Design

Question Description

You've been hired as a database consultant for a start-up company that will stream movies over the Internet (similar to Hulu, Netflix, and others).

Use the following document Database Design Project

Define data storage organization for the physical design of the database.

Make sure any referenced sources are properly cited.

Unformatted Attachment Preview

Running head: LEARNING TEAM C 1 Learning Team C Angela Moore, Brandon Lester, Carl Dodge, James Bowlin, Lori Filz DBM/502 September 14, 2015 Reggie Haseltine LEARNING TEAM C 2 Learning Team C FlixMyWay is a startup company offering movie streaming over the Internet to its customers. As a consulting company, FlixMyWay has hired us to determine what database requirements will be required for the successful operation of FlixMyWay. We recommend one database be created with the following tables to store the necessary information to support the FlixMyWay operations. CUSTOMERINFO TABLE Column Name Key Type Size Required Value Range Default Value Description Number 10 Yes NULL Unique Customer ID (Increment by 1) CustFirstName Character 30 Yes NULL Customer's First Name CustLastName Character 40 Yes NULL Address1 Character 40 Yes NULL Customer's Last Name Customer Address Line 1 Address2 Character 40 No NULL Customer Address Line 2 City Character 40 Yes NULL Customer City State CustomerID Primary Data Type Character 40 Yes NULL Customer State Zip Number 5 Yes NULL Customer Zip CreditCardNo Number 16 No NULL Customer Credit Card Number CreditCardExp Date 10 No NULL Customer Credit Card Expiration GenreLike1 Character 10 No Selection NULL Genre Customer Likes #1 GenreLike2 Character 10 No Selection NULL Genre Customer Likes #2 GenreLike3 Character 10 No Selection NULL Genre Customer Likes #3 MOVIEINFO TABLE Column Name MovieID SupplierID MovieTitle MovieYear MovieDesc Genre RentalCost DateAdded RentalCount Key Type Data Type Size Required Primary Foreign Number Number Character Number Character Character 15 15 40 4 250 10 Yes Yes Yes Yes Yes Yes Currency 4 Yes Date Number 10 6 Yes Yes Value Range Selection 00.01 20.00 Default Value Description NULL NULL NULL NULL NULL NULL Movie ID (Unique) Supplier ID of Movie Owner Title of Movie Year the Movie was released Movie Description Movie Genre NULL Movie Rental Cost NULL NULL Date Movie Was Added for Rent Count of Movie Rentals LEARNING TEAM C 3 SUPPLIERINFO TABLE Key Type Primar y Column Name SupplierID Data Type Siz e Require d Number 15 Yes NULL 40 Yes NULL 40 Yes NULL Customer Address Line 1 40 No NULL Customer Address Line 2 40 Yes NULL 40 Yes NULL 5 9 Yes Yes NULL NULL 17 Yes NULL Characte r Characte r Characte r Characte r Characte r Number Number SupplierName SupAddress1 SupAddress2 SupCity SupState SupZip SupBankRouting SupBankAccoun t Number Value Range Default Value Description Supplier ID Movie Supplier Name Customer City Customer State Customer Zip Supplier Bank Routing Number Supplier Bank Account Number RENTALHISTORY TABLE Column Name UniqueKey CustomerID MovieID DateRented Key Type Data Type Size Required Primary Foreign Foreign Number Number Number Character 20 15 15 40 Yes Yes Yes Yes Value Range Default Value NULL NULL NULL NULL Description Unique Key incremented by 1 Unique Customer ID (Increment by 1) Movie ID (Unique) Date rented by customer LEARNING TEAM C 4 Entity and Relationship Diagram Based on the requirements outline above, we were able to illustrate FlixMyWay entity and relationship diagram. Figure 1.1 below is the entity and relationship diagram. CustomerInfo RentalHistoryInfo PK CustomerID CustFirstName CustLastName UniqueKey PK Made By Address1 MovieInfo Stored In Makes MovieID PK FK CustomerID MovieTitle FK MovieID MovieYear DateRented Stores MovieDesc Address2 Genre City RentalCost State DateAdded SupplierInfo Supplies Zip PK RentalCount SupplierID CreditCardNo FK SupplierName CreditCardExp SupAddress1 GenreLike1 SupAddress2 GenreLike2 SupCity GenreLike3 SupState SupZip SupBankRouting SupBankAccount Supplied By SupplierID LEARNING TEAM C 5 Data Model Verification In order to make sure that our ERD is both logical and true against the requirements of the business we must validate it. As a part of the verification process we will create a sample customer history report in the table below and validate the report against our ERD model. CUSTOMER NO.: 24573 NAME: John Smith ADDRESS: 1564 10th ST. Detroit, MI 45635 RENTAL MOVIE HISTORY # TITLE 1524 Spaceballs 24365 Ghostbusters MOVIE DESCRIPTION Spoof of Star Wars Group of guys battle ghosts SUPPLIER MGM Columbia Pictures RENTAL DATE 07/24/2010 10/31/2013 RENTAL COST $4.99 $2.99 Report: Customer Rental History The Customer Rental History involves four entities: CUSTOMERINFO RENTALHISTORYINFO MOVIEINFO SUPPLIERINFO The primary entity is RENTALHISTORYINFO. The data items are accounted for in the E-R Diagram as follows: • CUSTOMER NO: attribute CustomerID of entity CUSTOMERINFO. o CUSTOMERINFO is linked to RENTALHISTORYINFO via the CustomerID foreign key in RENTALHISTORYINFO. • NAME: attributes CustFirstName and CustLastName of entity CUSTOMERINFO. o CUSTOMERINFO is linked to RENTALHISTORYINFO via the CustomerID foreign key in RENTALHISTORYINFO. • ADDRESS: attributes Address1, Address2, City, State, and Zip of entity CUSTOMERINFO. LEARNING TEAM C 6 o CUSTOMERINFO is linked to RENTALHISTORYINFO via the CustomerID foreign key in RENTALHISTORYINFO. • RENTALHISTORY #: attribute UniqueKey of entity RENTALHISTORYINFO. • MOVIETITLE: attribute MovieTitle of entity MOVIEINFO. o MOVIEINFO is linked to RENTALHISTORYINFO via the MovieID foreign key in RENTALHISTORYINFO. • MOVIEDESCRIPTION: attribute MovieDesc of entity MOVIEINFO. o MOVIEINFO is linked to RENTALHISTORYINFO via the MovieID foreign key in RENTALHISTORYINFO. • SUPPLIER: attribute SupplierName of entity SUPPLIERINFO. o SUPPLIERINFO is linked to MOVIEINFO via the SupplierID foreign key in MOVIEINFO. o MOVIEINFO is linked to RENTALHISTORYINFO via the MovieID foreign key in RENTALHISTORYINFO. • RENTALDATE: attribute DateRented of entity RENTALHISTORYINFO. • RENTALCOST: attribute RentalCost of entity MOVIEINFO. o MOVIEINFO is linked to RENTALHISTORYINFO via the MovieID foreign key in RENTALHISTORYINFO. Based upon our analysis, it is possible to create the customer rental history report without errors. Therefore, our ERD model has been verified to be an accurate representation of the entity relationships for FlixMyWay’s database systems. LEARNING TEAM C 7 Database Design with Distributed or non-distributed A distributed database is recommended for web applications services over the Internet such as FlixMyWay and their movie streaming. Replication will occur to keep the information updated centrally and across all instances of the database. The replication will allow the customer to access their FlixMyWay information from anywhere they may travel. Multiple copies will also allow for accessibility at all times should a copy become unavailable at a certain location. Logical model using normalization 1. CustomerInfo Table 1NF- CustomerInfo Table is already in first normal form 2NF- CustomerInfo Table is not in 2NF form because: Credit card number depends on customer name, not address CustomerID CustFirstName CustLastName Address1 Address2 City State Zipcode CustomerID CreditCardNo LEARNING TEAM C 8 CreditCardExp MembershipStartDate MembershipExpDate 3NF- CustomerInfo Table is not in third form because: Membership depends on credit card info, not customer name MembershipStartDate MembershipExpDate CreditCardNo CreditCardExp 2. RentalHistory Table 1NF- RentalHistory table is in first normal form because: It does not have duplicate records, each cell has single value, and each record is unique 2NF- RentalHistory table is in second normal form because: All non- key fields depend on all the components of the primary key 3NF- RentalHistory table is in third from because: All the non-key fields depends on the primary key 3. MovieInfo Table 1NF- MovieInfo table is in first normal form because: It does not have duplicate records, each cell has single value, and each record is unique 2NF- MovieInfo table is in second normal form because: All non- key fields depend on all the components of the primary key LEARNING TEAM C 9 3NF- MovieInfo table is in third from because: All the non-key fields depends on the primary key 4. SupplierInfo Table 1NF- SupplierInfo table is in first normal form because: It does not have duplicate records, each cell has single value, and each record is unique 2NF- SupplierInfo table is in second normal form because: All non- key fields depend on all the components of the primary key 3NF- SupplierInfo table is not in third form because: City and state depends on the Zipcode, not primary key SupplierID Zipcode City State Logical Model Integrity Constraints CustomerInfo Table must have all fields completed. No Null Values are allowed. Duplicate customer names are allowed but CustomerID must be unique. RentalHistory Table must have all fields completed. No Null Values are allowed. Duplicate MovieIDs are allowed as there will be more than one copy of each movie available. Foreign key CustomerID and MovieID match primary keys in CustomerInfo Table and MovieInfo Table. MovieInfo Table must have all fields completed. No Null Values are allowed. Foreign key SupplierID is linked to the primary key in SupplierInfo Table. LEARNING TEAM C 10 SupplierInfo Table must have data in all fields except SupAddress2 field. SupAddress2 is allowed null value. Validation of Logical Model against the Customer Requirements The customer requirements are listed in the table below. The required data highlighted is not currently part of the logical design. We will need to add five fields to the CustomerInfo table to capture up to five movie titles that customer is interested. The five MovieTitle fields can be null. In addition a MembershipStartDate and MembershipExpDate has been included as a result of the logical design. Data Category Customer Information Movie Information Supplier Information Data Required Customer name Customer address Customer ID Credit card number Credit card expiration history of movies rented "likes" (such as, genres, and movies the customer is interested in) Movie title Year Released Supplier ID (owner of movie) Description of movie Statistics on movie rental frequency Rental cost Supplier ID Supplier name Supplier address Supplier bank routing payment information LEARNING TEAM C 11 Defining integrity and security measures To ensure physical and logical integrity of the database for FlixMyWay’s is to ensure data entries from destruction by implementing a backup and data recovery process and plan. Ensuring integrity of each data element inside the FlixMyWay’s database is critical. To achieve this goal is to make sure each element can only be changed or modified by authorized users to enter correct and accurate values for each data element. To ensure security measures for FlixMyWay’s database is to ensure there is proper documentation and access control written in the security policy by the database administrator. The security policy should state who has access to certain tables, table restrictions, server security and connections to the database. Ensuring valid authorization and authentication for authorized users is paramount to the security of FlixMyWay’s database. There should be authentication methods and user identification administered by the database administrator of FlixMyWay’s company. The process should be outlined and detailed enough to ensure users know and understand how they will be authenticated in the database. Encryption and database password protection should be described if implemented to protect the database and the data within the database. Lastly, availability needs to be paramount for users to be able to be authenticated and authorized to access required data (Burtescu, 2009). Determine Performance Measures The DBA is responsible for monitoring the performance of the databases and servers running the databases. To do monitoring, information must be collected and analyzed regularly. The four main performance areas to monitor include: 1. Response times for frequently used queries 2. User connections to a SQL Server instance LEARNING TEAM C 12 3. Availability and use of system resources such as CPU and Memory 4. Performance of application components The tools used to collect the information for performance monitoring can be found by using SQL utilities and Windows server performance tools. The SQL utilities frequently used are the SQL Server Profiler and SQL Trace and Activity Monitor. The Windows server tools are the performance monitor, task manager, performance logs, and performance alerts. Listed below are the common causes of performance that should be included in any monitoring and analysis performed by the DBA: • Blocking locks – If transactions hold locks on rows too long, they block other transactions from processing against those rows. • Disk Input/Output (I/O) – Excessive disk I/O slows down transactions. Excessive disk I/O can be reduced if sufficient table indexes are created. • Server capacity – If the server has reached capacity, transactions will slow. • Memory usage – Memory is used to cache data so it can be accessed quickly. Having sufficient memory allows data to be accessed quickly. • CPU utilization – High CPU usage indicates queries are not properly tuned or an increase in processor power is needed. Database Backup and Recovery No matter how well your database is designed, a database failure is almost inevitable. Human error, hardware failure, and network failures are just a few ways a database could fail. Since the failure cannot be avoided, the backup and recovery process must be strong to get the database back up and running as soon as possible. Downtime must be minimized to ensure key business processes can continue to take place. LEARNING TEAM C 13 Backup facility provides periodic backup copies of portions of the database or the entire database. If there is a failure at noon, the database can be recovered from 11am. Losing one hour of work opposed to months is always a favorable choice. Database objects as well as the repository, source libraries and indexes are copies. Incremental backups will record changes since the last full backup. This saves a lot more time than doing a full system backup. Journalizing facilities provide the DBMS with an audit trail of transactions and database changes. If the database does fail, you can go to the last complete database backup as a starting point. With the journal entries all of the missing transactions and changes can be replaced. In order to recover the database in the event of a failure, procedures must be in place to minimize the downtime. Disk mirroring is one of these procedures. During disk mirroring, two copies of the databases are kept and updated at the same time. RAID technology is used to hot swap in the case of a failure. There is no interruption to the user and the processing of the database is automatically switched over to one of the mirrored images. LEARNING TEAM C 14 References Burtescu, E. (2009). Database Security- Attacks and control methods. Journal of Applied Quantitative Methods. Retrieved from http://www.jaqm.ro/issues/volume-4,issue4/pdfs/burtescu.pdf Skillsoft (n.d.). Microsoft SQL Server 2012: Database Monitoring and Performance Tuning [Video podcast]. Retrieved from https://uopx.skillport.com/skillportfe/assetSummaryPage.action?assetid=md_msdb_a08_i t_enus&fromShare=yes ...
Purchase answer to see full attachment

Final Answer

khakaan (1456)
Carnegie Mellon University

Anonymous
The tutor managed to follow the requirements for my assignment and helped me understand the concepts on it.

Anonymous
The tutor was knowledgeable, will be using the service again.

Anonymous
Awesome quality of the tutor. They were helpful and accommodating given my needs.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4
Similar Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors