Data and database administration

1. Data and database administration

2. Learning outcomes • At the end of this chapter, you should be able to: • Define terms related to data & dbase administration • List functions and roles of data/database administration • Describe role of data dictionaries and information repositories

3. Traditional Administration Definitions • Data Administration: A high-level function that is responsible for the overall management of data resources in an organization, including maintaining corporate-wide definitions and standards • Database Administration:A technical function that is responsible for physical database design and for dealing with technical issues such as security enforcement, database performance, and backup and recovery

4. Traditional Data Administration Functions • Data policies, procedures, standards • Data Policies: statements that make explicit the goals of DA • Data procedure: written outlines of actions to be taken to perform certain activity • Data Standard: explicit conventions and behaviors that are to be followed and that can be used to help evaluate dbase quality • Planning • Data conflict (ownership) resolution • Managing the information repository • Internal marketing of DA concepts

5. Data Policies, Procedures, standards All users must have passwords Password must be changed every 6 months Password must have minimum of 7 characters Password must have maximum of 12 characters Password must be combination of alphabet and numbers I/C number, names, birth date cannot be used as passwords.

6. Data Policies, Procedures, standards • To create password: • End user send request to DBA • DBA approves and forward it to computer operator • Computer operator creates account, assign temporary password and end it to the user. • A copy of the account information is sent to the DBA • User changes the temporary password.

7. Traditional Database Administration Functions • Selection of DBMS and software tools • Installing/upgrading DBMS • Tuning database performance • Improving query processing performance • Managing data security, privacy, and integrity • Data backup and recovery

8. Functions of DA & DBA DA

9. Functions of DA & DBA DBA DA/DBA DBA DA/DBA

10. Functions of DA & DBA DBA DA/DBA

11. Evolving Approaches to Data Administration • Blend data and database administration into one role • Fast-track development–monitoring development process: • Database planning: improve selection of technology • Database analysis: work on physical design in parallel with development of the logical & physical models • Database design: prioritize application by volume, importance & complexity • Database implementation • Database maintenance

12. Evolving Approaches to Data Administration • New DBA Roles • Procedural DBAs–managing quality of triggers and stored procedures • eDBA–managing Internet-enabled database applications • PDA DBA–data synchronization and personal database management • Data warehouse administration

13. Data Warehouse Administration • New role, coming with the growth in data warehouses • Similar to DA/DBA roles • Emphasis on integration and coordination of metadata/data across many data sources • Specific roles: • Support DSS applications • Manage data warehouse growth • Establish service level agreements regarding data warehouses and data marts

14. Open Source DBMSs • DEFINITION: • Free DBMS source code software that provides the core functionality of an SQL-compliant DBMS • An alternative to proprietary packages such as Oracle, Microsoft SQL Server, or Microsoft Access • mySQL is an example of an open-source DBMS • Advantages: • Less expensive than proprietary packages • Source code available, for modification • Have different version (simple to complex, free to some cost(additional features) • No additional cost for copies or licensing

15. Open Source DBMSs • Disadvantages: • Absence of complete documentation • Ambiguous licensing concerns • Not as feature-rich as proprietary DBMSs • Vendors may not have certification programs

16. Open Source DBMSs • Consider these factors when choosing an open source DBMS: • Features • Support • Ease-of-use • Stability • Speed • Training • Licensing

17. Data modeling responsibilities

18. END OF CHAPTER Information in this slides were taken from Modern Database Management System, Ninth edition by Jeffrey A.Hoffer, Mary B.Prescott & HeikkiTopi.

19. Routines and Triggers • Routines • Program modules that execute on demand • Functions–routines that return values and take input parameters • Procedures–routines that do not return values and can take input or output parameters • Triggers • Routines that execute in response to a database event (INSERT, UPDATE, or DELETE)

20. Routines and Triggers

21. Stored Procedure CREATE PROCEDURE sp_GetInventory@location varchar(10)ASSELECT Product, QuantityFROM InventoryWHERE Warehouse = @location EXECUTE sp_GetInventory ‘Kajang'

22. Triggers CREATE TRIGGER STANDARD_PRICE_UPDATE AFTER UPDATE OF STANDARD_PRICE ON PRODUCT_T FOR EACH ROW INSERT INTO PRICE_UPDATES_T VALUES (PRODUCT_DESCRIPTION, DATE, STANDARD_PRICE);