dba-4-monitorsupportdataconversion-updated-ppt-220528194211-3e86db74

1. ICT Database Administration Level IV Unit Title : Monitor and Support Data Conversion Unit Code: ICT DBA 04 0811

2. Continuous assessment (100%) • Attendance (10%) • Lab exam :- (10%) 22/9/2014 • Quiz:- 10% • Group assignment with presentation submitted Date 22/9/2014 • weight (30%) • Documentation15% • Presentation15% • Theory exam (40%) (Lo1 &Lo2) date 23/9/2014

3. LO1. Monitor data conversion • Defining concepts of data conversion and Data Terminologies • Data is raw facts or unorganized things (such as alphabets, numbers, or symbols) that refers to, or represent, conditions, ideas, or objects. • It can be qualitative or quantitative. • Qualitative data is descriptive information (it describes something) • Quantitative data is numerical information (numbers).

4. CONT..

5. CONT.. • Discrete data can only take certain values (like whole numbers) • Continuous data can take any value (within a range) • Discrete data can be counted, • Continuous data can be measured • Example: • Qualitative: • It is brown and black • It has long hair • It has lots of energy • Quantitative: • Discrete: • It has 4 legs • It has 10 fingers • Continuous: • It weighs 25.5 kg • It is 565 mm tall

6. CONT.. • Data conversion is the conversion of one file or database from one format (from one physical environment) to another. • Often, when data is moved from one system to another, some form of data conversion is required to convert the data to a format the receiving system can interpret • Types of conversion: • DB conversion (SQL, MySQL, MS Access, XLS, XML etc) • File format conversion (PDF to Word) • Image conversion (GIF to JPG, TIFF, PNG etc) • Character or string conversion(numeric to alphabet or viceversa)

8. Image compressionis minimizing the size in bytes of a graphics file without degrading the quality of the image. • What is lossy image? • Lossy compression refers to compression in which some of the data from the original file (JPEG) is lost. • The process is irreversible. • JPEGs and GIFs are both lossy image formats. • What does lossless image mean? • Lossless compression refers to compression in which the image is reduced without any quality loss. • Ex. RAW, BMP, GIF, & PNG are lossless image formats. • An Image variable is a type of variable that represents an unknown image in your document. Ex. TIFF image formats.

9. CONT.. • 1.2 Reading and Analysing Existing Data Conversion Documents • The data conversion process can often be a complex and difficult task during an implementation. • When performing data conversions, you must include analysis of your source data and continues through to system testing and user acceptance. • Throughout the conversion process, we perform quality control checks to ensure correctness of the conversion.

10. CONT.. 1.3 Understanding Data and Its Characteristics 1.3.1 Data Conversion Systems and Tools • Data Conversion Tool allows you to convert data both from and to (both sides are supported) a wide variety of formats,including: • SQLServerTables (data is logically organized in a row-and-column format • Oracle Tables (data storage in an Oracle Database. Data is stored in rows and columns. • ODBC Tables (Open Database Connectivity)

11. OleDb Tables (Object Linking and Embedding, DB.) • Microsoft Access Tables • XML Files (Extensible Markup Language (XML) is a markup language and file format for storing, transmitting, and reconstructing arbitrary data.

12. Cont.… • Once a conversion type is defined, it can be saved and reused either in a future conversion or as a step within a batch conversion. • 1.3.2 Data Modelling Methodologies • Data modeling is the formalization and documentation of existing processes and events that occur during application software design and development. • Data modeling techniques and tools capture and translate complex system designs into easily understood representations of the data flows and processes, creating a blueprint for construction or re-engineering. 

13. Cont.… • A data model can be thought of as a diagram or flowchart that illustrates the relationships between data. • Different approaches of data modeling, including: • Conceptual Data Modeling - identifies the highest-level relationships between different entities. • Logical Data Modeling - illustrates the specific entities, attributes & relationships involved in business function. • Physical Data Modeling - represents an application and database-specific implementation of a logical data model.

14. The 3 basic tenants of Conceptual Data Model are • Entity: A real-world thing • Attribute: Characteristics or properties of an entity • Relationship: Dependency or association between two entities • Data model example: • Customer and Product are two entities. • Customer number and name are attributes of the Customer entity • Product name and price are attributes of product entity • Sale is the relationship between the customer and product

15. Logical Data Model • The Logical Data Model is used to define the structure of data elements and to set relationships between them. • The logical data model adds further information to the conceptual data model elements. The advantage of using a Logical data model is to provide a foundation to form the base for the Physical model. However, the modeling structure remains generic.

16. A Physical Data Model describes a database-specific implementation of the data model. • It offers database abstraction and helps generate the schema. • The physical data model also helps in visualizing database structure by replicating database column keys, constraints, indexes, triggers, and other RDBMS features.

18. CONT.. • 1.3.3 Data Conditioning and cleaning • Data conditioning (Pre-processing) is the use of data management and optimization techniques which result in the intelligent routing, optimization and protection of data for storage or data movement in a computer system. • Data cleaning is the act of detecting and removing or correcting dirty data (i.e.: data that is incorrect, out-of-date, redundant, incomplete, or formatted incorrectly). • Data Cleaning helps to increase the overall efficiency of your data management systems and leads to an increase in the productivity of the organization.

19. CONT.. 1.3.4 Data Transformation and integration • Data transformation is one of the collective processes known as extract, transform or load(ETL). • which is one of the most important processes in data warehouse implementation from different data sources. • ETL tools EX. • IBM DataStage. • Oracle Data Integrator. • InformaticaPowerCenter. • SAS Data Management. • Talend Open Studio. • Pentaho Data Integration. • Singer. • Hadoop.

20. ETL process

21. CONT.. • 3.5 Sorting, updating, exporting and convert data • Sorting data is the process of arranging items into meaningful order so that you can analyze it more effectively. • Example: • sort text data into alphabetical order • sort numeric data into numerical order

22. Cont.… • Updating Data • The modification of data that is already in the database is referred to as updating. • The update operation allows you to change an existing database record in a logical or physical file. • You can update individual rows, all the rows in a table. Each column can be updated separately without affecting other columns. • UPDATEtable_nameSET column1=value, column2=value2, ...WHEREsome_column=some_value

23. To perform an update, you need three pieces of information: • The name of the table and column to update, • The new value of the column, • Which row(s) to update? • Exporting data • You can export data from one application to another application using the Export Wizard. • Exporting lets you share data from one application by providing a copy of data.

24. Cont.… 1.3.6 Ensuring Data Quality • DBMS provides a set of features that enable you to ensure the quality of data that is moved from source systems to your data destination. • Data profiling is a feature that enables you to analyse the content and structure of your data to determine inconsistencies, anomalies, and redundancies in the data.

25. Data profiling

26. Cont.… 1.4 Collection, Organization and Analysis of Data and Information • The data is typically organized to model relevant aspects of reality (for example, the availability of rooms in hotels), in a way that supports processes requiring the information (for example, finding a hotel with vacancies). • Organization of data • Organization of data is any one of the data management conventions for physical and spatial arrangement of the physical records of a data set.

27. Cont.… • Analysis of data • Analysis of data is the process of evaluating datausing analytical and logical reasoning to examine each component of the data provided. • Data from various sources is gathered, reviewed, and then analyzed to form some sort of finding or conclusion.

28. CONT.. • Information • Information is processed data that can affect behaviour, a decision or outcome. • It is valuable that is: • accurate and timely • specific and organized for a purpose • presented within a context that gives it meaning and relevance • Can lead to an increase in understanding and decrease in uncertainty.

29. CONT.. • 1.5 Validating Data Conversion Systems • 1.5.1 Data Accuracy • Data accuracy is generally expressed as a confidence interval (CI). • This means more of the information collected can be trusted as valid and free of confounding variables. • Example • My birth date is December 13, 1941. If a personnel database has a BIRTH_DATE data element that expects dates in USA format, a date of 12/13/1941 would be correct. A date of 12/14/1941 would be inaccurate because it is the wrong value.

30. Cont.… 1.5.2 Data Integrity • Data integrity (also known as data validity) refers to the overall completeness, accuracy and consistency of data. • This can be indicated by the absence of alteration (unchanged) between two instances or between two updates of a data record. • Data integrity can be maintained through the use of various error checking methods and validation procedures.

31. Data Integrity

32. 1.5.3 Back-up before Conversion • Before and after the process of converting your data, it is strongly recommended that you perform a full data backup.

33. Cont.… 1.6 Identifying and Confirming Data Conversion Tools • Software • Hardware • Environmental Pre-Requisites (Dust, Heat, Extreme Cold, Temperature Stability, Air Circulation & Moisture) • Dust and Dirty environment increases the overheating problem and mostly affects • The Motherboard. • The Processor and Power Supply fan.

36. LO2. Support data conversion • Results must be verified based on the relevant checklist. • Verified data must be presented and approved by appropriate persons. • Back-up copies of conversion files must be maintained and documented according to requirements • Developing clear and coherent technical documentation

37. Summary • Object explorer: provides a hierarchical user interface to view and manage the objects in each instance of SQL Server. • What are the four system databases in SQL Server? • master. • model. • msdb. • tempdb. • Template explorer allows you to store custom scripts, parameterize them for re-use, and have them at your fingertips without ever having to leave SSMS

38. Part one

40. Data Type • SQL Server data types determine what type of data will be populated within a given column. • All columns in a table must be associated with a data type, there are no exceptions to this rule. • There are several data types available, but often we find ourselves using one of three different categories of data types. • Character (CHAR, VARCHAR, NVARCHAR, TEXT) • Numeric (INT, BIT, DECIMAL, MONEY, NUMERIC, FLOAT, etc.) • Date (DATE, TIME, DATETIME, YEAR, MONTH, etc.)

41. How to create database and Table-Insert-Update-Delete in SQL Server step by step 1. create database

44. 2. create table “table1”

45. 3. Insert data into “table1”

46. 4. to view data in “table1”

47. 5 . to update data in “table1”

48. to view the updated data in “table1”

49. 6. to delete data in “table1”

50. to view the delete data in “table1”