1 / 36

Database Systems Planning and Administration

Database Systems Planning and Administration. Strategic Data Planning. Database Administration. Data Administration. Planning. Planning/ Administration. Administration. Strategic Data Planning. Data Requirements. Processes. IS Priorities. Data Architecture. Pragmatic Outcomes.

paulina
Download Presentation

Database Systems Planning and Administration

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database SystemsPlanning and Administration Strategic Data Planning Database Administration Data Administration Planning Planning/Administration Administration

  2. StrategicDataPlanning Data Requirements Processes IS Priorities Data Architecture Pragmatic Outcomes Integration Future Corporate Database Model Logical

  3. Application Data Model Types Legacy Strategic Logical Legacy Strategic Physical Current Future

  4. Corporate Data Modelling real world requirements analysis conceptual modelling Corporate Data Model

  5. Types of Corporate Data Model • 1. High-level data map • Controlling the information systems development process • 2. Minimum data set • Standardising data capture and usage

  6. Rationale for CDM CDM = important tool for information management 1. Encourages the production of integrated and inter-operable systems: a. Reduces the replication of data and systems across organisations. b. Introduces cost-savings in development effort. 2. Can be used as an important element of information systems planning. a. To determine which systems need to be produced to serve business needs. b. Enables prioritisation of development options. c. Encourages consideration of out-sourcing of systems. 3. Can be used to identify the strategic use of information systems.

  7. CDM in the NHS NHS/DHSS Health Services Information Steering Group Appointed in 1980 Chaired by Mrs Edith Korner Remit to identify a minimum data set to be used routinely for management purposes in every health authority Number of working groups set up with membership from the NHS Korner Data Model Report (KDMR) 1985

  8. MDSM Korner Data Model Report (KDMR) Information Mangement Centre (IMC) 1989 1991 Minimum Data Set Model (MDSM) The Data Dictionary 'to define for health authorities those data which they have all agreed to collect consistently. The model provides a tool for assessing ready-made systems, for creating new ones and for evaluating the impact of proposals for change' (NHS IMC, 1989)

  9. A Data Model from MDSM MDSM MDS07 Accident and Emergency Service Attendances Health Authority MDS01 MDS01 Hospital On Site Service Point GP Patient AandE Department Incident Type AandE Cause AandE Dept Operational Plan AandE Episode Lodged Patient Departure Method InitiatorOf Attendance ModeOf Arrival AandE Attendance

  10. A Dictionary Entry from MDSM AandEAttendance (Accident and Emergency Attendance) Health Authority Code Service Point Code District Patient Number Arrival Date Arrival Time First Attendance Initiator of Attendance *F INITIATOR OF ATTENDANCE* Method of Departure *F DEPARTURE METHOD* Mode of Arrival *F MODE OF ARRIVAL* Start Date *F AandE Episode* K - Primary Key F - Foreign Key

  11. Summary - Strategic Data Planning • Strategic data planning is the activity devoted to the construction of future/logical data models. • A corporate data model is a map of the data requirements of the whole or a substantial part of an organisation. • There are two main types of corporate data model: a type 1 CDM maps key features of the data terrain; a type 2 CDM provides a minimum data set for key organisational functions. • Organisations generally conduct strategic data planning with one or more of the following objectives in mind: the implementation of integrated information systems; the development of a corporate wide data architecture; the clear identification of information systems priorities; the rethinking of an organisation’s key processes; the education and communication of data requirements throughout the organisation. • Strategic data planning may be relavant only for certain forms of organisation. Generally speaking those orginisations with homogeneous and interdependent subunits will benefit most from the data integration arising from strategic data planning.

  12. DataAdministration Untimely Data Conflicting Data Irrelevant Data Logical Inconsistent Data Physical Conceptual Need Multiple Data Definitions Passive Level Active Data Definition Data Sharing Data Control Data Dictionary Consultancy Scope Corporate Awareness Data Integrity Corporate Requirements Data Analysis Data Dictionary Management Data Security Data Privacy

  13. Data Administration Data administration is that function concerned with the management, planning and documentation of the data resource of some organisation. Data administration is concerned with the management of an organisation’s meta-data, that is, data about data. It is a function which deals with the conceptual or business view of an organisation's data resource.

  14. The Scope of Data Administration • Consultancy. Offering consultancy on all aspects related to an organisation’s meta-data, particularly expertise in data analysis. • Corporate Awareness. Educating to increase awareness of the importance of data. Also disseminating information on what data exists and for what purpose. • Corporate Requirements. Identifying corporate data requirements. Particularly building a corporate data architecture which incorporates strategic planning. • Data Analysis. Coordinating the use of a standard data analysis methodology. Using such a methodology to develop business data models. • Data Control. Implementing standards for ensuring that access to data is controlled. Also ensuring that suitable recovery procedures are in place. • Data Security. Ensuring that both technical and administrative controls are in place to protect against threats. • Data Definition. Implementing standards for the definition of data and controlling the medium for the recording and communication of such definitions. • Data Integrity. Implementing standard mechanisms for ensuring the integrity of an organisation’s data. Also, documenting the rules for ensuring integrity. • Data Dictionary Management. Promoting the use of a logical data dictionary and implementing standards for its control. Also monitoring the use of and content of the data dictionary. • Data Privacy. Implementing procedures to ensure that the organisation complies with any legislation concerning national data regulation. • Data Sharing. To encourage sharing of data across applications and to promote the idea of data that is independent of applications

  15. The Need for Data Administration • A number of applications are developing within some organisation which use different definitions for the same data. • Data held by a number of diverse applications is inconsistent. • Decision-makers within an organisation receive conflicting data from different sources within that organisation. • Decision-makers receive data too late for it to be useful. • Decision-makers receive too much irrelevant data. • There are notable gaps in the data collected by an organisation. • Departments within an organisation have no clear idea why they collect certain data.

  16. The Data Dictionary • A data dictionary is a means for recording the meta-data of some organisation Conceptual Data Dictionary Active Data Dictionary Logical Data Dictionary Passive Data Dictionary Physical Data Dictionary

  17. Database Security Data Administration Threats Computer-based Measures Non-Computer-based Measures

  18. Summary - Data Administration • Database technology, because of its centrality in the modern IS architecture has stimulated the development of a large range of roles for servicing this technology. Two of the most common-place roles are the data administrator and the database administrator. • The data administrator is a high-level, corporate function. • As far as development is concerned, the data administrator will be involved in the analysis and design of a database system. Data administrators are also particularly involved in the management of the organisational data resource. • The main tool of the data administrator is the data dictionary, particularly conceptual and logical data dictionaries. A data dictionary is a means for recording the metadata of some organisation • Database security is a concern of the data administrator in association with the database administrator. Database security involves protecting a database from external threat by using computer-based and non-computer-based controls.

  19. DatabaseAdministration Tuning DBMS Irrelevant Data Monitoring Usage Training Privacy,Security Integrity Configuration Control Administration DBMS Impact Asessment Installation Database Environment Backup& Recovery Data Archiving Tuning Databases Administration Database Data Control Monitoring Data Usage View Create View Revoke Data Standards Physical Design Grant

  20. Database Administration The database administrator is responsible for the technical implementation of database systems, managing the database systems currently in use and setting and enforcing policies for their use. Whereas the data administrator works primarily at the conceptual level of business data, the database administrator works primarily at the physical level. The place where the data administrator and the database administrator meet is at the logical level. Both the data administrator and database administrator must be involved in the system-independent specification and management of data.

  21. Key Functions of Database Administration • Administration of the Database • Administration of the DBMS • Administration of the Database Environment

  22. Administration of the Database • Physical Design. Whereas the data administrator will be concerned with the conceptual and logical design of database systems, the database administrator will be concerned with the physical design and implementation of databases • Data Standards and Documentation. Ensuring that physical data is documented in a standard way such that multiple applications and end-users can access the data effectively • Monitoring Data Usage and Tuning Database Structures. Monitoring live running against a database and modifying the schema or access mechanisms to increase the performance of such systems • Data Archiving. Establishing a strategy for archiving of ‘dead’ data • Data Backup and Recovery. Establishing a procedure for backing-up data and recovering data in the event of hardware or software failure

  23. Administration of the DBMS • Installation. Taking key responsibility for installing DBMS or DBMS components • Configuration Control. Enforcing policies and procedures for managing updates and changes to the software of the database system • Monitoring DBMS Usage and Tuning DBMS. Monitoring live running of database systems and tailoring elements of the DBMS structure to ensure the effective performance of such systems

  24. Administration of the Database Environment • Data Control. Establishing user groups, assigning passwords, granting access to DBMS facilities, granting access to databases • Impact Assessment. Assessing the impact of any changes in the use of data held within database systems • Privacy, Security and Integrity. Ensuring that the strategies laid down by data administration for data integrity, security and privacy are adhered to at the physical level • Training. Holding responsibility for the education and training of users in the principles and policies of database use

  25. Database Administration Tools Data Management Subsystem DBMS Data Maintenance Information Retrieval Database Administration Database

  26. DBA Issues • Storage • Data Control (Security) • Backup/recovery • Performance • Distribution

  27. DBMS, File Manager, Disk Manager Interaction Application System Requests rows DBMS Requests files File Manager Requests logical pages Disk Manager Requests physical pages Disk

  28. User Table A User Table B Oracle Physical and Logical Structures User Structure Data Blocks Data Blocks Tablespace A Tablespace B Logical Structure Physical Structure File 2 File 1 File 3

  29. Creating an Oracle Database CREATE TABLESPACE TA DATAFILE ‘file2.ora’ SIZE 100 MB, ‘file4.ora’ SIZE 100MB; ALTER TABLESPACE TA ADD ‘file6.ora SIZE 20MB;

  30. Data Control • 1. Prevent would-be users from logging on to the database • (non-standard facilities) • 2. Allocate access to specific parts of the database to specific users • (standard facilities) • 3. Allocate access to specific operations to specific users • (non-standard facilities)

  31. Views • Three main uses for views: Simplification. Functionality. Security. • CREATE VIEW S1 AS • SELECT * • FROM Students • WHERE courseCode = 'CSD' • CREATE VIEW S2 AS • SELECT studentName, sex, courseCode • FROM Students • SELECT * • FROM S1 • WHERE sex = 'F'

  32. GRANT and REVOKE GRANT [ALL : SELECT : INSERT : UPDATE : DELETE ] ON [<table name> : <view name>] TO <user name> GRANT INSERT ON Modules TO pbd GRANT SELECT ON Lecturers TO pbd REVOKE [ALL : SELECT : INSERT : UPDATE : DELETE ] ON [<table name> : <view name>] FROM <user name> REVOKE INSERT ON Modules FROM pbd REVOKE SELECT ON Lecturers FROM pbd

  33. Granting Access via Views CREATE VIEW Evans AS SELECT * FROM Lecturers WHERE deptName = (SELECT deptName FROM Lecturers WHERE staffName = ‘Evans R’) AND staffName <> 'Evans R' GRANT SELECT, UPDATE ON research TO REvans

  34. DBA Privileges in ORACLE GRANT {CONNECT : RESOURCE : DBA} TO <username> [IDENTIFIED BY <password>] Connect user is able to look at other users' data only if allowed by other users, perform data manipulation tasks specified by the DBA, and create views Resource privilege allows the user to create database tables and indexes and grant other users access to these tables and indexes. Dba privilege is normally given to a chosen few. Such privileges permit access to any user’s data, and allows the granting and revoking of access privileges to any user in the database.

  35. Database Backup Copy Log File Database Recovery Database recovery involves the restoration of a database to a consistent state after system failure Backup Recovery

  36. Summary - Database Administration Database technology, because of its centrality in the modern IS architecture has stimulated the development of a large range of roles for servicing this technology. Two of the most common-place roles are the data adninistraor and the database administrator. Data administration is a high-level business function. Data administrators are given responsibility for documenting corporate-wide data requirements and designing database systems to meet these requirements. Database administration is a low-level, technical function. DBAs are give responsibility for implementing and maintaining databases run under particular DBMS. Because modern-day DBMS are complex piecs of technology, DBAs are specialists in DBMS. ISO SQL has a limited range of DBA functions, particularly focused on granting access to data and revoking access to data. Most DBMS also have a range of non-standard functions for declaring users and passwords, fine-tuning database sizing, monitoring the performance of a database, and fragmenting databases. ORACLE is a DBMS which offers all these facilities.

More Related