IT 204 Final Project PDF

Preview

Summary

Download IT 204 Final Project PDF

Description

Running Head: FINAL PROJECT

1

7-2 Final Project Christopher T. Gouchenouer Instructor Mike Irick IT-204 Intro to Data and Info management Southern New Hampshire University 10/21/2018

Introduction

FINAL PROJECT

2

The current system that Birchwood Lane School is using is to capture all data relating to the business of the school on paper which is maintained in filing cabinets. The school has seen that this system is severely inadequate and results in extended searches for data concerning students which at times can be missing or misplaced. Birchwood has implemented a project in which they are tasked with designing/updating the data storage systems that will house all information electronically. This update will benefit the school, staff, parents and most of all students by giving them instant access to grades, attendance and credits to name a few. Before this project can be started there will be a gap analysis to determine what Birchwood is doing that works and what they wish to accomplish at the time of completion. Once this is determined, a database management system (DBMS) will be designed, built and implemented within the school’s system that will satisfy the government and other parties’ requirements. Business Case and Gap Analysis The current setup that Birchwood is using is to capture all student data on paper and stores them in filing cabinets which can be accessed by anyone that enters. The information that is gathered and stored in this filing system contains the student’s personal information (name, address, contact number, email, birthdate, graduation date, prior/current course schedule), the instructors personal information (name, address, birthdate, date hired, education, prior/current courses taught), course data (name, start/end date, instructor, student enrollment). This system introduces a risk of the data being lost or accessed by an individual that should not have the information due to a lack of security. A paper filing system is not capable of having a backup/recovery file so if there are missing or misplaced files they are gone without a chance of recovering them from any source.

FINAL PROJECT

3

The benefits of moving Birchwood to a database will be many and implementing a DBMS will show an increased financial impact for the school. The use of a relational database will allow Birchwood to purchase cost efficient servers that will handle the required amount of data that will be stored on site providing the school with the capabilities of searching the stored data in a fraction of the time at which they are currently performing. The result of implementing a DBMS will be the reduction of expenses for searching through the current filing system as well as the heightened security that will be gained. Additional Information There can be information added to the database should Birchwood find a need for it. This will allow the school to remain current with all information that may be needed for contacting students, parents or school officials as well as creating a more in-depth relationship within the school community. Additional information that may be captured could concern a person’s medical records (protected by HIIPA), students’ future goals and/or interests, scholarships awarded/accepted, extracurricular activities and state/federal test scores that have been taken throughout the students’ academic career. The information that is currently being stored on paper will need to be assembled into groups and labeled according to what they contain. These labels will have the ability to be edited or added through the SQL database. Maintaining this data will be of upmost importance to Birchwood and extremely vital to their future. It is because of the importance of accurate data that I feel Microsoft SQL will be the best option. This software is very user friendly and easy to use. MS SQL Server is a relational database management system (RDBMS) that can be developed to identify entities, relationships and attributes while identifying/normalizing the data that is gathered. With this being said, it is my suggestion to implement an Entity Relationship model that will include entities that will need to be used such

FINAL PROJECT as the students, teachers/instructors as well as the courses that both have either taught or attended. This model will also include attributes that will be used such as student information (full name, address, phone number, email, birthdate, courses taken, courses currently enrolled, enrollment date and graduation date), instructor information and course information(course name, course category, instructor, course start date, course end date and the students that are taking the course). This data will be tracked by last name as well as the course number ((Junic, Vrbsky & Nestrov, 2014). Below is a basic diagram of what the database should entail:

4

FINAL PROJECT

5

When implementing a logical database, there is much more information gathered with a focus on the details of the data. The data that is gathered will have much more detail involved. The key elements of a logical database that are used include all entities that are involved (students, courses taken, instructors, courses taught) and the relationships that connect them to one another. A logical database will also show the specific attributes associated to each entity that is entered. With a logical database, there are many steps that are taken in the design that will ensure the data is complete. The first step specifies the primary keys for each entity that is used. The second step is to define the relationship between the entities used. The third step is to locate the attributes for all entities used. The fourth step is to resolve relationships that have a many-tomany relationship. This refers to any relationship between different tables within the database such as when a parent’s name is associated with several children that are in another table. The fifth and final step is the normalization of the data. This will remove all data that is repetitive ensuring that all dependencies are logical (Database Systems, 2008). Below is a basic design of a Logical database:

FINAL PROJECT

6

That database that I have selected is Microsoft SQL Server 2017. MS SQL Server 2017 is available in four separate editions: Enterprise, Standard, Express and Developer. The Enterprise edition is available with unlimited cores and a maximum data size limit of 524 PB. It also offers Access mission-critical capabilities to achieve unparalleled scale, security, high availability, and leading performance for your Tier 1 database, business intelligence, and advanced analytics workloads. The Standard edition is available with up to 24 cores as well as a maximum data size limit of 524 PB. It also offers rich programming capabilities, security innovations, and fast performance for mid-tier applications and data marts. Easily upgrade to the Enterprise edition without having to change any code. The Express edition offers the capability to build small, datadriven web and mobile applications up to 10 GB in size with this entry-level database and is

FINAL PROJECT

7

available for free. The developer edition allows the user to build, test, and demonstrate applications in a non-production environment with this full-featured edition of SQL Server 2017. The cost of each of these editions fluctuate depending on the edition that is purchased and the number of cores needed. The SQL Server 2017 Enterprise edition has a cost of $14,256 per core, which are sold in 2 core packs. This edition is ideal for Comprehensive, mission-critical performance for demanding database and business intelligence requirements. Provides the highest service and performance levels for Tier-1 workloads. The SQL Server 2017 Standard edition has a cost of roughly $3,717 per core and is ideal for Core data management and business intelligence capabilities for non-critical workloads with minimal IT resources. The SQL Server 2017 Express edition is a free to use entry-level database that's ideal for learning, as well as building desktop and small server data-driven applications of up to 10 GB (Microsoft.com, 2017). With the SQL Server maintenance solution, the users allowed to restructure only the fragmented indexes. While in the Index/optimize section, the user can define the preferred maintenance operation for each fragmented group. When purchasing MS SQL Server 2017, customers must license every server that is being utilized. This simply says that the customer will have to purchase a license for all server installs and each client that will be accessing the server. Microsoft offers separate licensing for the non-production server that is used for development and testing which is a less expensive option (Microsoft.com, 2017). The licenses for the developer edition are normally used for the design, development and testing of a database. It is also used for non-production purposes such as demonstrations. When these types of licenses are provided, it is for programmers, technical writers, database professionals and testers. Evaluation licenses are used to assess software for potential business

FINAL PROJECT

8

use and can only be used in nonproduction environments with the primary sources for the evaluation software coming from TechNet (which is a per-user subscription for IT professionals), downloads from the Microsoft website and volume license contracts which all users to evaluate the software for up to 60 days. Evaluation licenses are not used in any development or testing environment. Production licenses are generally applied to software being used to run the business. An SQL server environment will be a production model when or if any individuals within the organization use the server for anything other than development, evaluation and reviewing an application before it is implemented for use by employees. Production use is also defined as when the SQL Server environment connects to another production database or provides backup or disaster-recovery to a production environment (Microsoft.com, 2017).

Minimum Operating System Requirements: SQL Server Edition Supported Operating System SQL Server Developer Windows Server 2016 Datacenter Windows Server 2016 Standard Windows Server 2016 Essentials* Windows Server 2012 R2 Datacenter Windows Server 2012 R2 Standard Windows Server 2012 R2 Essentials Windows Server 2012 R2 Foundation Windows Server 2012 Datacenter Windows Server 2012 Standard Windows Server 2012 Essentials

FINAL PROJECT SQL Server Edition

9 Supported Operating System Windows Server 2012 Foundation Windows 10 Home Windows 10 Professional Windows 10 Enterprise Windows 10 IoT Enterprise Windows 8.1 Windows 8.1 Pro Windows 8.1 Enterprise Windows 8 Windows 8 Pro Windows 8 Enterprise

SQL Server Express

Windows Server 2016 Datacenter Windows Server 2016 Standard Windows Server 2016 Essentials* Windows Server 2012 R2 Datacenter Windows Server 2012 R2 Standard Windows Server 2012 R2 Essentials Windows Server 2012 R2 Foundation Windows Server 2012 Datacenter Windows Server 2012 Standard Windows Server 2012 Essentials Windows Server 2012 Foundation

FINAL PROJECT SQL Server Edition

10 Supported Operating System Windows 10 Home Windows 10 Professional Windows 10 Enterprise Windows 10 IoT Enterprise Windows 8.1 Windows 8.1 Pro Windows 8.1 Enterprise Windows 8 Windows 8 Pro Windows 8 Enterprise

Memory: Express editions: 512 MB, All other editions: 1 GB Processor Speed: x86 Processor: 1.0 GHz, x64 Processor: 1.4 GHz. Recommended: 2.0 GHz or faster. Hard Disk: 6GB of physical uncompressed disk space. Processor Type: x64 Processor: AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, Intel Pentium IV with EM64T support. x86 Processor: Pentium III-compatible processor or faster (Microsoft.com, 2017). Some of the more desirable features that come with SQL Server 2017 are as follows: Atomicity: This is also known as an atomic transaction. This means that either all the actions that had happened or none of them. If an action has failed midway through the transaction, then then the previous actions must be deleted as if they had never happened.

FINAL PROJECT

11

Consistency: No transaction will break the rules of integrity pertaining to the database meaning that the database must be left in a state of consistency.

Isolation: The SQL Server is designed to service many concurrent users. However, from this viewpoint, the data set must look like the user is the only one on the system. Every transaction that is made within the database must be completely self-contained with all changes that have been made are readable by no other transaction. Durability: When each transaction is committed, it must persist even if there is a system failure that follows immediately. When there is a user that commits a transaction within the SQL Server, all the data that is needed to repeat it is physically written to the transaction log before the commit is returned to the user as successful.

Referential Integrity: This will cause a failure on any INSERT or UPDATE SQL statement changing value of customer_id in child table, if value of customer_id is not present in Customer table (Wordpress.org, 2011). Referential Integrity is used to ensure relationship integrity is not broken. For instance, if there’s a parent child relationship between two records, the database will ensure that there must be a parent for the child record. The database can be configured to not allow the parent to be deleted, or do a cascading delete where the child will also be deleted if the parent is deleted. Parent records deleted that have child records are called orphaned records. This can happen if the developer has neglected to setup a relationship between two tables where a relationship was not initially established.

Performance / Scalability

FINAL PROJECT

12

Microsoft SQL Server optimizes its performance through created algorithms, optimistic concurrency, the elimination of any physically present locks/latches and storing any tables in memory. All stored data can be transformed into what is known as a Hekaton procedure that will enhance the database speed by compiling all files into the native C coding language to increase the database efficiency. SQL developers will also code needed indexes into the database within tables where the data is kept and queries are heavily requesting the data. Adding indexes can be more art than science and will need to index those columns they feel will be accessed during querying of the table. SQL Server will also create executions plans to assist in optimizing the execution of queries in the event a query is run using identical parameters and values. (Microsoft.com, 2017). Clustering: SQL Server 2017 can support additional nodes of the server engine to update the database. These are normally run on the same network and communicate with each other so that the remain in sync. This presents the possibility of performance benefits and can be configured to failover if a node goes down which will support a 24/7/365 days uptime. In the event that there is a situation presenting a load balancing while accessing the database, a load balancer will ping each node to determine which one has a lower ping and direct the request to that node (Microsoft.com, 2017). Additional Technologies Implementation of the database system will take place once there has been an adopted model that will be used. The implementation process is most often the most time-consuming part of the DBMS creation process. Implementing the database model chosen will involve the development of the data tables based on the logic that has been chosen to be used. The data must be entered into the database, either by importing it into the DBMS or physically inputting the

FINAL PROJECT

13

data by hand. The data can include text information (name and address), pictures/graphics and sounds. The data tables must be created and populated using the correct/appropriate commands and then setting rules/relationships which will define how the data is going to be retrieved. A relationship is a defining value or characteristic which determines exactly how data is accessed. For example, when any medical personnel enter a list of symptoms, a trigger tells the database to populate a list of data which includes numerous diagnoses and/or treatments for the symptoms. When these symptoms are altered, or a new symptom is added, a new variable is introduced that could change the list of treatments and causes. The more complex the system has been designed, the applications will also have the capability of becoming more advanced. 1.

Populate the database with the following data: •

Student Data: full name, phone number, email address, birthdate, enrollment date, graduation date, courses taken, courses currently enrolled

•

Instructor Data: full name, phone number, email address, birthday, hire date, courses taught, and courses currently assigned.

References A Quick-Start Tutorial on Relational Database Design. (n.d.). Retrieved from http://www.ntu.edu.sg/home/ehchua/programming/sql/Relational_Database_Design.html Database Systems (2008). Database Systems: The Complete Book http://infolab.stanford.edu/~ullman/dscb.html.

FINAL PROJECT

14

Junic, N. Vrbsky S. and Nestrorov, S. (2014). Database Systems: Introduction to Databases and Data Warehouses. Pearson Prentice Hall.

Microsoft (n.d.). SQL Server 2017 Editions. Retrieved from https://www.microsoft.com/enus/sql-server/sql-server-2017-editions#CP_StickyNav_1 Relational Database Management System Examples Concept and Types. (2017, March 06). Retrieved from http://technicgang.com/relational-database-management-systemexamples/ Referential and Self-Referential Integrity Constraints. (2011, November 28). Retrieved from https://kaanmutlu.wordpress.com/2011/11/28/referential-and-self-referential-integrityconstraints/...