Bachelor of Information Technology Information Systems and Technology

Bachelor of Information Technology Information Systems and Technology - IT 1105 03. Organizing Data and Information Part V 3. 1 Data Management Prabhajinie Jayabaalasundaram MBCS, MBA

Bachelor of Information Technology Information Systems & Technology IT 1105 03. Organizing Data and Information 3. 1 Data Management - Part V Prabhajinie Jayabaalasundaram MBCS, MBA

IT 1105 Information Systems and Technology Last Lesson Database Structures Relational Structure Object-oriented structure Data Modeling Enterprise data modeling Relational Database Entity-relationship (ER) diagrams Types of Relationships

IT 1105 Information Systems and Technology Outline Relational Database Manipulating Data Cleanup

IT 1105 Information Systems and Technology Relational Database The relational model describes data using a standard tabular format. All data elements are placed in two-dimensional tables, called relations, which are the logical equivalent of files. The tables in relational databases organize data in rows and columns, simplifying data access and manipulation.

IT 1105 Information Systems and Technology Relational Database In the relational model, each row of a table represents a data entity - a record and each column of the table represents an attribute - a field. Each attribute can accept only certain values. The allowable values for these attributes are called the domain. The domain for a particular attribute indicates what values can be placed in each column of the relational table.

IT 1105 Information Systems and Technology Manipulating Data After entering data into a relational database, users can make inquiries and analyze the data. Basic data manipulations include selecting, projecting, and joining. Selecting involves eliminating rows according to certain criteria.

IT 1105 Information Systems and Technology Selecting Suppose a project table contains the project number, description, and department number for all projects a company is performing. The president of the company wants to find the department number for Project 22, a Construction project. Using selection, the president can eliminate all rows but the one for Project 22 and see that the department number for the department completing the Construction.

IT 1105 Information Systems and Technology Projecting involves eliminating columns in a table. Eg. : a department table might contain the Department No, Department Name, Manager and EPF number of the manager in charge. A HR Manager wants to create a new table with only the Department Number and the EPF Number of the manager in charge. The HR Manager can use projection to eliminate other columns and create a new table containing only the Department and EPF number

IT 1105 Information Systems and Technology Joining involves combining two or more tables. For example, you can combine the project table and the department table to create a new table with the project number, project description, department number, department name, and the manager in charge of the project.

IT 1105 Information Systems and Technology Relational Database As long as the tables share at least one common data attribute, the tables in a relational database can be linked to provide useful information and reports. Being able to link tables to each other through common data attributes is one of the keys to the flexibility and power of relational databases. Suppose the president of a company wants to find out the name of the manager of the Consultancy project and the length of time the manager has been with the company.

IT 1105 Information Systems and Technology Relational Database Assume that the company has the Manager, Department, and Project tables shown below Project Table Project 15 49 22 Description Payroll Construction Consultancy Dept. No. 257 632 598

IT 1105 Information Systems and Technology Relational Database Department Table Dept. Name No. Manager’s EPF 257 Accounting 005 632 Manufacturing 051 598 Marketing 042

IT 1105 Information Systems and Technology Relational Database Manager Table EPF Last First Hire-Date Dept. Name No. 005 Perera Kalum 10 -07 -2007 257 051 Peter John 02 -17 -2009 632 042 Jegan Siva 01 -05 -2002 598

IT 1105 Information Systems and Technology Relational Database ER Diagram White board

IT 1105 Information Systems and Technology Relational Database One of the primary advantages of a relational database is that it allows tables to be linked. This linkage reduces data redundancy and allows data to be organized more logically. The ability to link to the manager's EPF stored once in the manager table eliminates the need to store it multiple times in the project table.

IT 1105 Information Systems and Technology Relational Database The relational database model is by far the most widely used. It is easier to control, more flexible, and more intuitive than other approaches because it organizes data in tables. A relational database management system provides tips and tools for building and using database tables. The ability to link relational tables also allows users to relate data in new ways without having to redefine complex relationships.

IT 1105 Information Systems and Technology Relational Database Because of the advantages of the relational model, many companies use it for large corporate databases. The relational model can also be used with personal computers and mainframe systems. A travel reservation company, for example, can develop a farepricing system by using relational database technology that can handle millions of daily queries from online travel companies

IT 1105 Information Systems and Technology Data Cleanup Valuable data is accurate, complete, economical, flexible, relevant, simple, timely, verifiable, accessible, and secure. The database must also be properly designed. The purpose of data cleanup is to develop data with these characteristic.

IT 1105 Information Systems and Technology Question The relational model a) describes data using a standard tabular format. b) All data elements are placed in twodimensional tables. c) Attributes are called relations. d) The tables in relational databases organize data in rows and columns. e) Each row of a table represents a data entity

IT 1105 Information Systems and Technology Answer The relational model a) describes data using a standard tabular format. b) All data elements are placed in twodimensional tables. c) Attributes are called relations. d) The tables in relational databases organize data in rows and columns. e) Each row of a table represents a data entity

IT 1105 Information Systems and Technology Question Match the processes and the definitions

IT 1105 Information Systems and Technology Answer Match the processes and the definitions

IT 1105 Information Systems and Technology Data Cleanup Consider a database for a fitness center designed to track member dues. The table contains the attribute name, phone number, gender, dues paid, and date paid.

IT 1105 Information Systems and Technology Data Cleanup Name Phone Number Gender Dues Paid Date paid Bamini, A 2345678 Female Rs. 3000 Sept. 15 John, S 2734909 Male Rs. 1500 Sept. 15 John, S 2643506 Male Rs. 1500 Sept. 25

IT 1105 Information Systems and Technology Relational Database As the records in Table Arun Bamini and Stephan John have paid their dues in September. Stephan has paid his dues in two installments. Note that no primary key uniquely identifies each record. Because Stephan John has paid dues twice in September, the data in the database is now redundant. The name, phone number, and gender for John are repeated in two records.

IT 1105 Information Systems and Technology Relational Database Notice that the data in the database is also inconsistent. John has changed his phone number, but only one of the records reflects this change. Further reducing this database's reliability is the lack of a primary key to uniquely identify Stephan Thomas's record. These problems and irregularities in data are called anomalies. Data anomalies often result in incorrect information causing database users to be misinformed about actual conditions. Anomalies must be corrected.

IT 1105 Information Systems and Technology Relational Database To solve these problems in the fitness center's database, we can add a primary key, such as member number, and put the data into two tables. A. Fitness Center Members table with gender, phone number, and related information B. Dues Paid table with dues paid and date paid. Both tables include the member number attribute so that they can be linked

IT 1105 Information Systems and Technology Relational Database Member Name Phone Gender No SN 123 Ruwani, A 2345678 Female SN 656 John, S 2643506 Male Member No Dues Paid Date Paid SN 123 Rs. 3000 September 15 SN 656 Rs. 1500 September 25

IT 1105 Information Systems and Technology Relational Database The relations in those tables reduce the redundancy and eliminate the potential problem of having two different phone numbers for the same member. Also note that the member number gives each record in the Fitness Center Members table a primary key. Because the Dues Paid table lists two payment entries (Rs. 1500) with the same member number (SN 656), one person clearly made the payments, not two different people. Formalized approaches, such as database normalization, are often used to clean up problems with data.

IT 1105 Information Systems and Technology Question One of the primary advantages of a relational database is that it allows tables to be linked. Advantage of the linkage is/are A) it reduces data redundancy B) it allows data to be organized more logically. C) it won’t allow to create new table/s D) it allows users to relate data in new ways without having to redefine relationships E) it doesn’t allow relationships

IT 1105 Information Systems and Technology Answer One of the primary advantages of a relational database is that it allows tables to be linked. Advantage of the linkage is/are A) it reduces data redundancy B) it allows data to be organized more logically. C) it won’t allow to create new table/s D) it allows users to relate data in new ways without having to redefine relationships E) it doesn’t allow relationships

IT 1105 Information Systems and Technology Question By using a relational database management system, as opposed to traditional approach to data management, it is possible to (a) improve data integrity. (b) increase data redundancy. (c) improve data security. (d) query the database quickly. (e) manipulate data easily.

IT 1105 Information Systems and Technology Answer By using a relational database management system, as opposed to traditional approach to data management, it is possible to (a) improve data integrity. (b) increase data redundancy. (c) improve data security. (d) query the database quickly. (e) manipulate data easily.

IT 1105 Information Systems and Technology Summery Relational Database Manipulating Data Cleanup

Bachelor of Information Technology Information Systems & Technology IT 1105 03. Organizing Data and Information 3. 1 Data Management - Part V Prabhajinie Jayabaalasundaram MBCS, MBA

Bachelor of Information Technology Information Systems and Technology - IT 1105 03. Organizing Data and Information Part V 3. 1 Data Management Prabhajinie Jayabaalasundaram MBCS, MBA