Database Systems Administration - Aaliya Institute of Management and Technology - Assignment Example

DATABASE SYSTEMS AND ADMINISTRATION by Student’s Name Database Systems and Administration Professor’s Name University’s Name City, State Date of submission Contents Task 1 3 Introduction 3 Deliverables 3 Task 2 4 Entity Relationship Diagram (ERD) 4 Entities and attributes 5 Relationship and cardinality 6 Task 3 7 Relational Table 7 Integrity and business 8 Task 4 9 Task 5 10 User management 10 Data backup and recovery policy 10 Storage management 11 Part B 11 Problem statement 11 Project justification 11 Project objectives 12 Literature Review 12 Life cycle model 14 Waterfall Methodology 14 Strategic and tactical management 16 Findings 16 Solutions 17 Conclusion 17 Recommendations 18 Poster 18 References 19 Database Systems and Administration Case Study Task 1 Aaliya Institute of Management and Technology (AIMT) Introduction AIMT is an institution that is entrusted with the responsibility of conducting courses and finally awarding Bachelors and Master’s degree programs in varied disciplines. The processes are still manually handled though and the firm is looking forward to the automation process of these key tasks. The below are some of the key deliverables that will be in focus going automation processes. Deliverables Deliverable in actual sense in regards to project management is defined as the service or product that needs to be produced as a result of the execution of the project in question (Kuhn, 2010). The below are facts and deliverables that will be in focus in line with the AIMT automation process. id Facts Deliverables 1. The existence of different departments The key deliverable here is the development of a logical as well as conceptual data model that will capture all the information about the department and how it interacts with other parts of the system variables like employees, programs, and courses. The below are fields that will constitute the table department; unique ID, department name, and employee_ID 2. The availability of different programs within each department These are the programs that will be offered for study within a department. The fields that will feature in this module are; program_id, program_name, department_id 3. Availability of varied courses given a specific program These are courses that are mapped to a specific program. The below are the major fields that will exist in this table; id, program_id, course_name, duration, fee 4. Staff/employee members : Dean, Associate/Assistant Dean, HoD, PM, teaching staff such as Professor, Associate Professor, Assistant Professor, Lecturer These are the people that will be involved with the day to day operations within the institution. The table employee will have the below fields; unique ID, name, gender, date of birth, designation, department to which he/she belongs, skills of the faculty 5. Varied modules and different sessions Task 2 Entity Relationship Diagram (ERD) It is a database representation tool that is used to show a set of relationship that exists between a set of entities or data within a database. It models the system data and their relationships within the database. Entities and attributes The main entities here are; 1. Department: this is an entity that will have all the information of all the departments that are within AIMT institution. The attributes of this entity include the below; Dept_id: this is a unique code that is assigned to each department to uniquely identify it. It acts as the primary key (PK) for the table Department_name: this is just a name that identifies the department from other departments within the institution 2. Program: These are the list of all the programs that are in offer. They are linked to the department using the dept_id. The attributes here are; Program_id: this is a unique id that identifies each program. It acts as the primary key Program_name: this is the name of the program Dept_id: this identifies the department in which the program is mapped 3. Course: these are courses that are within each program. The attributes here are Course_id: this is a unique field that identifies each course Program_id: this is links the course to each program Course_name: this is the name of the course in question Duration: the duration of the course in years and months Fee: the amount to be paid for the course 4. Employee: this stores the employees of the firm. Attributes in this case are; Employee_id: unique to each member and uniquely identifies the person Employee_name: the name of the staff Gender: contains the sex/gender of the employee either male or female Dob: stores the date of birth of the employee to assist in age calculations Department_id: maps the member to each employee it belongs Designation: the title if the staff within the institution likes professor, assistant professor, lecturer etc. Faculty: identifies the faculty in which the member is mapped to 5. HOD (head of a department). The attributes of a HOD include Name: the name of the department head Employee id to identify the employee Department id to indicate what department he heads 6. Student. A student is a primary requirement of the school and has the following attributes Student name which shows the name of the student Student id to uniquely identify the student Gender shows whether the student is male or female Dob to show the age of the student Course name to indicate the course they are enrolled in 7. School. The school hosts all the above entities and it has the following attributes School id to identify the school School name to show the name of the school School type to indicate the type and level of school 8. Class. A class is contained in a school and has the following attributes Name of the class Level of the class Class id to uniquely identify the class within the department Relationship and cardinality The table below is a breakdown showing the relationship that exists between the above entities. Entity 1 Relationship Cardinality Entity 2 department Employees belong to a certain department. *..* (many to many) employee department Department offers certain programs *..* (many to many) program program Each program has some course within it *..* (many to many) course employee A given employee is appointed to head a head a department 1..1 (one to one) HOD department Each department is headed by a HOD 1..1 (one to one) HOD Task 3 Relational Table The above list of entities can be converted to relational table as shown by the below Sql queries that creates the above tables when executed; 1. Department Table CREATE TABLE department (dept_id VARCHAR(10) NOT NULL, dept_name VARCHAR(50), PRIMARY KEY(dept_id)); 2. Program Table CREATE TABLE program (program_id VARCHAR(10) NOT NULL, program_name VARCHAR(50), dept_id VARCHAR(10) NOT NULL, PRIMARY KEY(program_id), FOREIGN KEY(dept_id)); 3. Employee table CREATE TABLE Employee (employee_id VARCHAR(10) NOT NULL, course_name VARCHAR(50), name VARCHAR(50), gender VARCHAR(50), dob date, designation VARCHAR(50), faculty_skills VARCHAR(50), dept_id VARCHAR(10) NOT NULL, PRIMARY KEY(employee_id), FOREIGN KEY(dept_id)); 4. Course table CREATE TABLE course (course_id VARCHAR(10) NOT NULL, course_name VARCHAR(50), duration VARCHAR(50), fee VARCHAR(50), program_id VARCHAR(10) NOT NULL, PRIMARY KEY(course_id), FOREIGN KEY(program_id)); 5. HOD table CREATE TABLE HOD (employee_id VARCHAR(10) NOT NULL, name VARCHAR(50), dept_id VARCHAR(10) NOT NULL, PRIMARY KEY(employee_id), FOREIGN KEY(dept_id)); Integrity and business Integrity Rule: This is achieved by ensuring that the primary key column on each table cannot contain NULL values or null entries to be made. Referential Integrity Rule: this is whereby any primary key is referenced to another key on the mother table. To ensure consistency of data on both update and deletion, each foreign key value must be matched to a primary key value in the table referenced Business logic Integrity: this is realized through the data validation process. For example, a name cannot contain numerical and phone number does not contain characters. Task 4 For the purpose of showing the anomalies or repetition of data in a table, employees table is broken down into four other tables to ensure that anomalies of data is remove d completely. Task 5 Data administration is an aspect in data management that separates the business aspects of the data in question from the fast technology that is implemented in the storage and the management of data. The data administrator therefore, is responsible for the understanding of the business processes and transforming them into the logical data model. This includes and not limited to the below; User management In regards the above scenario, management of examinations records are critical. Not every staff in the college can have an access of the data or even manipulation of the data. The below are some of the aspects that ought to be considered; Students can only access their examinations records HOD can access and manipulate both the students and the lecturers records Lecturers can enter students results records into the system and can seek approval when it comes to manipulation of the same data Data backup and recovery policy It is also paramount to keep constant backup of the data just in case of the server or drives failures. Good backup policy requires that data ought to be replicated to an offsite station for recovery in case of a disaster. Backup can be done in the below formats; Incremental backup. This is where the first backup runs a full backup. The sub sequential backups only backup the last changes that happened after the full backup. This is first and saves on the space of the drive Full back-up. This is where full backups are made when a backup process is initiated. It is the most secure but uses a lot of space. Storage management The storage of data should be secure from theft and or unauthorized access. Part B Problem statement The current management functions at AIMT including management of staff, courses, programs, and examinations are still paper based and are done manually. The program and course registration, report generation, exams analysis, and employee registration activities are all manual. It takes a lot of time for college staff to keep track of these crucial records (Wiegers, 2003). It also takes a toll on the resources as it consumes many hours to record, analyze, and retrieve. The project is destined to automate all the operations hence making the business operations to be more effective and the tasks to be more efficient. Project justification In running the college, there are many tedious tasks that are an important part of the management. The current manual management tasks of ensuring all tasks are completed in time, staffing, logistics, finances, and other functions are so tiring and time-consuming (Wiegers, 2003). The operations of examinations entries, analysis, performance tracking and reports generations also involve a lot of time and staff. The developed system makes the work of the Deans, lecturers, and professors more effective and all the tasks can be done efficiently. The system takes care of all the manual operations of the staff and does execute much of this work on its own with ease. Some of the things it can help in are student registration, staff registration, exam entries, and exams reports generation (Wiegers, 2003). The system can also help the staff and the college in general about the general performance of classes, and this can assist in decision-making process. Project objectives The project had the following objectives: a. Investigate the current system against the college requirements needs. b. Automate the course and module. c. Automate the employee registration module. d. Automate the department’s module. e. Capture- records of all the details of staff members. System objectives a. Register students and the program and course enrolled in. b. Register a staff/employee member. c. Keep track of courses and modules offered. d. Keep records of all the details of staff members. Literature Review Database is a collection of related data, and linked through foreign keys. In essence, a database is managed and centrally executed in a database management system (DBMS). DBMS is therefore a set of designed rules that all or database systems accepts and uses universally. The most essential and industrially used DBMS are Oracle, SQL server from Microsoft, mysql and others. Data administration is an aspect in data management that separates the business aspects of the data in question from the fast technology that is implemented in the storage and the management of data. It is more inclined with the actual users of the business data (Leiter, 2009). The data administrator therefore, is responsible for the understanding of the business processes and transforming them into the logical data model. The data administrator is therefore tasked to execute and realize the following tasks; The identification as well as the cataloging process of the business data as demanded by the business (Mullins, 2017) The development of both the logical and the conceptual models of the business data as well as the interrelationships between the identified elements (Mullins, 2017) Generation and the setting up of the access and the usage of data (Mullins, 2017) Setting up the data policies and procedures for the organization and the identification of the system and data users (Mullins, 2017) Furthermore, security of data must also be factored in. This is in regards to the access, modification, and use of data by the employees who have an access right to the stored data (Leiter, 2009). For security reasons, the data access rights must be discriminated in such a way that not all the employees on varied rights have equal access of the data. According to (Kuhn, 2010), the below are some of the major security issues to implement; Use of access passwords and usernames for each employee Use of different levels of access rights. Top employees like Dean will have access, modification, and deletion of data. While students will have only access data rights without neither modification nor deletion rights. Data access and modification logs. This will assist in tracking down who made an insertion of data into the system, who modified the data, or who deleted the records from the system. Lennon, 2010 further goes ahead to define a project as having a defined start time and end time. He noted that a project must also have a defined scope and resources. A project is unique in that it is not a routine operation. So it’s clear that project development and management team often includes people who do not necessarily come and work as one. It is a time a group that cut across companies, organization and even geographical localities. From his scholarly articles, he explains that Project management, is actually the application of knowledge and relevant work experience such as skills in regards the effective and efficient management of a project endeavor. Life cycle model A life cycle model in regards to software development is defined as a set of activities that needs to be completed during the development of the designed system. Different software development processes have been documented in books by different scholars (Wiegers, 2003). The model chosen to be used in the system development depends on the nature of the system to be developed and the programmer who is tasked with the development process. With respect with this system, waterfall model is selected with the below justification (Wiegers, 2003). Waterfall Methodology The waterfall model, a sequential development process that involves the phases of requirements analysis and design process amongst. The various phases of this model may overlap each other. The waterfall methodology was preferred as it lays emphasis on time scheduling, budgets, planning, targeted or projected dates, and completion of an entire system at one time (Wiegers, 2003). Fixed control is also enhanced over the life of the project under development through the use of broad written documentation, as well as through formal reviews and approval by the supervisors occurring at most phases before beginning the next phase. A brief description of the various phases is as follows: 1. Requirements analysis: This involves the collection of the system needs from the client perspective and making a system specification document from the collected requirements (Wiegers, 2003). 2. Design: This phase involves coming up with design documents such as data flow diagrams (Wiegers, 2003). 3. Implementation: The phase entails the final transformation of system into coding process. 4. Testing: Involves testing the system to ensure it successfully performs the tasks in the business environment (Wiegers, 2003). 5. Maintenance: A continuous phase that involves rectifying faults in the working system and also implementing changes required in the working system. Strategic and tactical management Strategic planning relates to issues pertinent and is related to the mission of the company and the rationale justifying the existence of the corporation. Strategic planning and management responsibility is bestowed with those managing the organization e.g. the investors and partners if any (Smallbizlink.monster.com, 2015). Tactical planning and management, on the other hand, relates to the actions taken in every day-to-day, and which their results will propel the company to realizing the objectives that are documented in their strategic plan (Smallbizlink.monster.com, 2015). The responsibility, in this case, is entrusted to those individuals who perform the work. They are the ones who qualify by any means to plan for it. Therefore, tactical and strategic management is vital in any software development endeavor. It provides a road map in regards to the product design through to its completion. It provides the direction, the destination, and how to get there (Smallbizlink.monster.com, 2015). Findings From the above analysis of operations existing in AIMT, the below was the findings; 1. All the processes including the registration of departments, courses, and programs are done manually and kept in files 2. The allocation of classes and courses to the lecturers are still done manually meaning time consuming and prone top errors 3. The recording and storage of examination records are entered manually and stored in files; this means that the security of these crucial records can easily be accessed or manipulated by any person who can have an access. 4. The retrieval of this crucial information are hard since its nice someone to check on the files and pull them out. Solutions The solution is the development of the system that will be in a position to handle the following task in an automated manner; 1. Automate the course and program registration 2. Automate students registration module 3. Automate the examinations recording and entry module 4. Automate the employee registration module 5. Automate the resource allocation module 6. Automate the user management module 7. Automate the data backup and recovery module 8. Develop different levels of data access module 9. Automate the department module that will keep all the records and information for each department Conclusion Information and communication technology has surely caused unprecedented revolution in the service delivery sector. The web branch of ICT has reduced the world to a global village. With the advancement of the IT technology, everything can be automated hence improving the way of managing activities in a school. The system provides convenience to management, staff and students. The developed system was a step towards keeping abreast with the global advancing in technology and to improve the college management activities within AIMT and the country at large. The project was deemed a success as the main requirements implemented successfully and well in time as per the project schedule regardless of the many challenges that were met on the way. Recommendations The developed system met the requirements as stipulated in the project objectives. However, it is believed that further improvements could be made to make the system work in harmony and to be more efficient and effective. Some of these improvements include: 1. Integration of the system with the college’s online website such that exams information can be uploaded directly. 2. Enabling the students to view the results on their own e.g via short messages. Poster The below is an image of the poster for the above system References Kuhn, D. (2010). Pro Oracle database 11g administration. 1st ed. New York: Apress. Leiter, C. (2009). Beginning Microsoft SQL server 2008 administration. 1st ed. Indianapolis, IN: Wiley Pub. Mullins, C. (2017). Database, Data, and System Administration | Why DBAs Must Be Jacks-of-All- Trades | InformIT. Informit.com. http://www.informit.com/articles/article.aspx?p=29321&seqNum=4 SearchSQLServer. (2017). What is database administrator (DBA)? - Definition from WhatIs.com. http://searchsqlserver.techtarget.com/definition/database-administrator Wiegers, K. (2003). Software requirements. Redmond, Wash.: Microsoft Press. Read More