Database Design
DBM502 – Database Management
March 24th, 2014
University of Phoenix
Professor: Sam Haidar

EzStream
This paper will provide an overview of the database to be utilized for the startup company EzStream Inc. The core business of EzStream is to provide a complete solution to stream media content via online or WIFI. Customers will have the choice to rent, buy, or pay a monthly subscription to watch media content. Several components will break down the development of the database and provide details to the database infrastructure.
Conceptual Design
The conceptual design of EzStream’s DB will consist of Movies, Suppliers, and Customers. Customers will either rent or purchase movies, and have the option of paying a monthly subscription rate to watch movies via digital streaming.
Data Analysis and Requirements * Tasks during Research and Analysis: * Identify essential "real world" information (e.g. interviews) * Remove redundant, unimportant details * Clarify unclear natural language statements * Fill remaining gaps in discussions * Distinguish data and operations
Requirement Analysis
First step: filter essential information vs unimportant details * Essentials * There are customers, suppliers, and media content * Identify age of audience for rentals * Customers have a customer identification number * Four weeks maximal rental time. * Unimportant details * "...Rentals since a few months" * "... John Wayne.." * Names of the categories (but categories are important!) * Upcoming releases * Note: what is important depends on the application * Clarify unclear statements * Video club: admission / annual fee? * Charge per rental: price for one day the minimum? * Fill gaps * Any discounts? * What happens after maximal rental time? * Distinguish data from operations * Processing a bill * Becoming a member of the club.

Distributed Model
EzStream's database will use the distributed database model to support its current customers and future growth. Distributed databases offer several advantages over a traditional centralized database. A distributed database provides improved availability over a centralized database. A CPU or network failure on the centralized database server could completely bring down access to the server. By using a distributed database model, a server failure can be rerouted around and connections sent to one of the many other still-functioning nodes. A distributed database improves performance by enabling designers to place servers geographically closer to those that will need access, which is especially important for an online streaming company. Having servers close to our customers improves connection speeds and the quality of the user experience (Thakur).
Using a distributed model also makes managing growth much simpler. In a centralized server, an increasingly more powerful and expensive server needs to be purchased, configured, and swapped out with the older one. This can lead to downtime and customer dissatisfaction. There is also a limit to how powerful a single system can be. With a distributed database model, as the business necessitates growth, more servers can be brought online to handle the increased needs and provide greater capacity (Thakur).
Logical Design
User requirements * Register as a member * Forgot password recovery * Pay online for media content * View account data / transactions * Update account information * Cancel monthly subscriptions * View movie rentals (past and current) * Search movies by genre and category

Physical Design
Data Storage
EzStream will make use of storage area network (SAN) technology to help manage the large amount of data used in providing the company’s streaming services to its customers. A SAN is a dedicated network, distinct from the local area network, often using fiber to connect servers directly to the storage systems. This greatly increases access speeds to the data and offloads disk management tasks from the database server. The database server can focus on its role as the DBMS and not worry about managing data storage tasks (PCMag).

Integrity
Data integrity is enforced to ensure data quality is maximized with databases. Both hierarchical and relational databases enforce data integrity. Achieving data integrity can be performed by following these integrity constraints within a relational database: * Entity Integrity: this rule requires each table must have its own primary key, which cannot be null and has to be unique. * Referential Integrity: a concept of foreign keys. The foreign key value has two different states. The first condition states the foreign key refers to a primary key within another table, or it has the ability to be null. A null value condition would mean the relationship is unknown or there may be no relationships. * Domain Integrity: This condition requires all relational database columns to be in a defined domain. This ensures all data can be located and connects to other data.
All data will be searchable and recoverable. Well defined data integrity systems increase performance in all aspects of the database. Any of these features that cannot be implemented will have to be implemented through the software application.
Security
Database security utilizes an array of security controls for protection against theft, confidentiality, availability and integrity. Database security risks include: * Hackers * Unauthorized users * Inappropriate security settings * Incorrect T-SQL commands * Malware infections * Performance overloads and no fail-over configuration * Hardware failure * Natural disasters
Performance Measures
It is extremely important to carefully select the appropriate tool to monitor or tune the performance of a database. Microsoft SQL Server Management Studio contains a large array of tools to assist in monitoring and managing SQL Server Databases. Each toolset plays a specific roll within the performance management and monitoring process. These features include: * Activity Monitor – is useful for ad-hoc reporting of the current activity and provides graphical information regarding: * Processes executing * Processes that are blocked * Locked transactions * User activity * Windows application event log – provides a high level snapshot of the events on the Windows server, operating system, and SQL Server. The information within can assist in troubleshooting SQL-Server issues. * Built-in stored procedures * sp_who – provides information about current users, processes, and the current executing SQL statements. * sp_lock – identifies type of lock, and which resource the lock applies to. * sp_spaceused – provides an estimate of current disk space volume for each table and database. * sp_monitor – provides statistics for CPU and I/O usage, and idle time since stored procedure sp_monitor was last executed. * Built-in functions – there are many internal functions. A few examples can provide information such as: the amount of executing CPU time for executing SQL Server code; the number of current connections and attempted connections; the amount of network packets for all SQL Server connections. * Trace flags – provides resources and types of locks involved with deadlock and which command is being affected. * Database Engine Tuning Advisor – provides analysis of T-SQL statements that addresses several categories. * Index changes (add, remove, or modify) * Best mix of indexes * Disk space constraints * Physical design structures * SQL Server Profiler – monitors and troubleshoots T-SQL statements from applications, deadlocks, blocking locks, hardware issues, and disk space. * Display the estimated and actual execution plan – within the SQL Query window, this will provide a visual overview of what the SQL Server Database Engine would most likely utilize to execute a query. This provides ability to change query structures, save the execution results, compare the differences, and maximize optimization and normalization.
A recommendation my Microsoft is to establish a performance baseline. Performance measurements need to be taken at scheduled intervals for a specific period of time. With EzStream being a 24x7 operations, these intervals should include every hour, for every day, for each month. These statistics can be compared to the other intervals to determine when peak times occur, response times of queries and transactions, and backup completion times. The effect of SQL Server performance covers all applications hitting the DB, server hardware and software, the operating system, and the structure of the network design. After establishing performance baselines, further investigation can identify areas in need of fine tuning or configuration changes (MS SQL Server©).
Backup and Recovery Process
Database backup is the implementation and action of copying data to a secured backup repository. It is vital for all companies to maintain a current backup of its data. Data can be corrupted due to user accidents, hardware failures, hackers, and natural disasters. Backups need to be scheduled when system and database performance is at its lowest level. This can be identified through the performance baseline analysis. In addition to the backup of data and files, it is a recommendation to backup copies of software also. The two types of backups are known as physical and logical. Physical backups are copies of the actual data files and are stored on disk or tape. Logical backups are copies of the logical objects such as tables, views, functions, security roles, and stored procedures. Logical data is stored in binary format and utilized for rebuilding the structure of the database. The recovery process is the actual process of restoring data due to the unexpected loss of data. The disaster recovery plan should document and identify the parties responsible for performing the disaster recovery, and identify both recovery time and recovery point. Recovery time is determined by the maximum amount of time allowed before an organization begins to experience financial losses. The recovery point identifies the exact time the data needs to be restored to for the company to continue business as normal with none or minimal impacts (TechTarget,2014).

Database Attributes and T-SQL The following attributes and T-SQL statements were required to be captured and will be found within the EzStream Database attachment ‘DBM502_Week6_T-SQL_TeamA_1.1.txt’ as follows: * Attributes * Customer: * Name – Table [ MbrInfo ] * Address – Table [ MbrInfo ] * Customer ID – Table [ MbrInfo ] * Credit card number and expiration – Table [ MbrAccount ] * History of movies rented – T-SQL Statement * Genres and movie of interest – Table [ MovieGenre and MovieCategory ] * Movie: * Title – Table [ MovieInfo ] * Year – Table [MovieInfo ] * SupplierID – Table [ Supplier ] * Rental cost – Table [ MovieSupplier ] * Suppler: * SupplierID – Table [ Supplier ] * Name – Table [ Supplier ] * Address – Table [ Supplier ] * Bank routing information – Table [ SupplierPayInfo ]

* T-SQL Statements (see attached file ‘DBM502_Week6_T-SQL_TeamA_1.1.txt’) * Create database – T-SQL Statement * Create index – T-SQL Statement * Create table – T-SQL Statement * Select – T-SQL Statement * Insert – T-SQL Statement * Delete – T-SQL Statement * Update – T-SQL Statement * Alter – T-SQL Statement
Conclusion
EzStream has a great design in place that maximizes normalization and data integrity. With the database being optimized and normalized, future enhancements should not affect any application layers built on top of the database layer. Much work will need to be performed once the database goes into production for performance monitoring and fine tuning. EzStream has dedicated a strong foundation to make sure the online company is running efficiently and effectively. By normalizing the database, it is clearer for upper management to understand the process of the online system that is being implemented. With the security measures that are in place, customers can feel secure about storing personal information in our database. The core business of EzStream is to provide a complete solution to stream media content via online or WIFI.

References

PCMag. (n.d.). Definition of: SAN. Retrieved from http://www.pcmag.com/encyclopedia/term/50794/san
Thakur, D. (n.d.). What are the advantages and disadvantages of distributed database management system? Retrieved from http://ecomputernotes.com/database-system/adv-database/advantages-and-disadvantages-of-ddbms
MS SQL Server© 2014 (ND), Establish a Performance Baseline. Retrieved from: http://technet.microsoft.com/en-us/library/ms190943.aspx TechTarget©, Burton A.(2012, August), Establish a Performance Baseline. Retrieved from: http://searchdisasterrecovery.techtarget.com/definition/data-recovery Definition of: Integrity. Retrieved from http://www.technopedia/integrity