Database 1, 2, 3 Lecture Notes PDF

Southern Technical University Basra Management Technical College Information Technology Dept. Prepared by: Asst. Lec. Ismaeil Aziz Bahar Weeks 1, 2 , and 3 1 General Definitions 2  1...

Southern Technical University Basra Management Technical College Information Technology Dept. Prepared by: Asst. Lec. Ismaeil Aziz Bahar Weeks 1, 2 , and 3 1 General Definitions 2  1- Data  A collections of raw facts and figures is called data. General  The word raw means that the facts have not yet been processed to get their exact meaning.  Data is collected from different sources. It collected for different purpose. Data Definitions may consist of numbers, characters, symbols or pictures etc.  Data is a vital resource for any organizations.  A resource is anything that is valuable for an organization.  Resources include building, furniture, vehicles, machinery and employees.  Data is considered a resource because it provides correct information for making proper and timely decisions. It enables the management of the organization to utilize other resources effectively. It is not possible to make good decision if data is not available in desired format. 3  1- Data Example of Data When students get admission in colleges of universities, they have to fill out an General admission form. The form consists of raw facts about the students. These raw facts are student’s name, father name, address etc. The purpose of collecting data is to maintain the records of students during their study period in college Definitions or university. Type of Data 1. Numeric Data 2. Alphabetic Data 3. Alphanumeric Data 4. Image Data 5. Audio Data 6. Video Data 4  2- Information  Information is the processed data. It is an organized and processed form of data. It is more meaningful than data and is used form making decisions. General  Data is used as input for the processing and information is the output of this processing. This Information can be used again in some other processing Definitions and will be considered as data in that processing. Data Processing Information Example of Information 5  3-The difference between Data and information as follows: Data Information General 1 2 Data consists of unprocessed raw facts. Data is used ad input in the computer. Data is not meaningful. 1 Information is the processed form of data. 2 Information is the output of computers. 3 3 Information is meaningful. Data is normally is huge in its volume. 4 Information is normally short in volume. Definitions 4 5 Data is the asset of organizations and is not available to people for sale. 5 Information is normally available to people for sale. 6 Data is difficult or even impossible to 6 Information is easier to reproduce if lost. For reproduce. Suppose Government loses data of example, if the list of illiterate citizens is lost, it can census. It is almost impossible to reproduce it. be reproduced easily from stored data. 7 Data is used rarely. 7 Information is used frequently. 8 Data is an independent entity. 8 Information depends on data. 9 Data is not used in decision-making. 9 Information is very important for decision-making. 6 File Processing System VS Databases 7  1- File Processing System Computer File Processing System is a business application to store, manipulate, File and retrieve large files of data. (there were no database).  Traditional or simple file processing is the first computer-based method to handle business application. Processing  In the past, many organization stored data in files on tape or disk. The data was managed using file-processing system. In a typical file processing system, each System department in an organization has its own set of files. The files are designed especially for their own applications. The records in one files are not related to the VS records in any other file. Database Old file processing systems 8  1- File Processing System Disadvantages of File Processing System Business organizations have used file-processing system for many years. But File this systems has many disadvantage. Processing 1- Data Redundancy and Inconsistency In the file processing system, the same data may be duplicated in several files. Suppose there are two files “Students” and “library”. The file “students” System contains the Roll No, name, address and telephone number and other details of Student in a college. The file “Library” contains the Roll No and name of those VS student who get a book from library along with the information about the rented books. The data of one student appears in two files. This is known as data redundancy. This redundancy cause higher storage. Database 9  1- File Processing System This situation can also result in data inconsistency. Inconsistency means that File two files may contain different data of the same student. For example, if the address of student is changed, it must be changed in both files. There is a possibility that it is changed in the “Students” file and not from “Library” file. Processing The data becomes inconsistent in this situation. 2- Data Isolation System The data in files processing system is stored in various files. It becomes very difficult to write new application programs to retrieve the appropriate data. VS Suppose that student emails are stored in “Students” file and fee information is stored in “Fee” file. The data from both files is required to send an email message to inform a student that the date for fee payment is over. In file Database processing system, it difficult to generate such type of list from multiple files. 10  1- File Processing System 3- Integrity Problems File Integrity means reliability and accuracy of data. The stored data must satisfy certain types of consistency constrains. For Example, Roll No and Marks of students should be numeric value. It is very difficult to apply theses constrains Processing on files in file processing system. 4- Program Data Dependency System Program data dependency is a relationship between data in files and program required to update and maintain the file. Application program are developed VS according to a particular file format in file processing system. If the format of file is changed, the application program also needs to be changed accordingly. For example, if there is a changed in the length of postal code, it requires Database change in the program. The changes may be costly to implement. 11  1- File Processing System 5- Atomicity Problem An operation on data may consist of different steps. A collection of all steps File required to complete a process is known as transaction. The atomicity means that either one transaction should take place as whole or should not take place Processing at all. Suppose a user wants to transfer money form account A to account B. this process consists of two steps:  Deduct the money from account A. System  Add the money to account B. Suppose that the system fails when the computer has performed the first step. VS It means that amount has been deducted from account A, but has not been added to account B. This situation can make data inconsistent. Database File processing system does not provide the facility to ensure atomicity of data. 12  2- Database Database is an organized collection of related data that is stored in an efficient and compact manner. File  The word organized means that data is stored in such a way that the user can use this data easily. Processing  The word related means that a database is normally created to store the data about a particular topic. System For Example if a database is created for students, it will contain data about the students such as roll no, name, address etc. Similarly if the database is about the employees of an organization it will contain the data of employees such as VS employee ID, grade and salary etc. All data in database is arranged in table.  The word efficient means that the user can search the required data quickly. Database  The word compact means that the stored data occupies as little space as possible in computer. 13  3- Database Management System (DBMS)  The (DBMS) is software provided by the database Vendor to provide all the basic services required to organize and maintain the database, including File the following:  Moving data to and from the physical data files as needed. Processing  Managing concurrent data access by multiple users including provisions to prevent simultaneous updates from conflicting with one another.  Managing transactions so that each transaction’s database changes are an all- System or-nothing unit of work. In other words, if the transaction succeeds, all database changes made by it are recorded in the database; if the transaction VS fails, none of the changes it made are recorded in the database  Support for a query language, which is a system of commands that a database Database user employs to retrieve data from the database  Provisions for backing up the database and recovering from failures  Security mechanisms to prevent unauthorized data access and modification 14  3- Database Management System (DBMS) Software products such as Microsoft Access, Oracle, Microsoft SQL Server, Sybase, DB2, Ingres, and MySQL are all DBMSs. File Processing System VS Database 15  4- Advantage of Database Approach Some important advantage of database approach are follows: File 1- Redundancy Control The data in a database appears only once and is not duplicated. For example the Processing data of a student in college database is stored in one table. The table can be accessed for different purposes. For example, if we want to store the marks of the student in a table, only Roll No of the student will be used in second table. System The second table will be connected to the student table for accessing the information about the student as follows: VS Roll No Marks Table Subject Marks Roll No 1 2 Name Ali Usman Address Basra Basra Email [email protected] Usman@ utc.edu.iq Student Table Phone 07723587777 07825345879 Database 1 1 2 2 Math English Math English 98 87 81 92 the details of the students are stored in Student table. The Marks table stores only the Roll No of students. The remaining data is not duplicated. Roll No in the Marks table is duplicated for joining two tables. 16  4- Advantage of Database Approach 2- Data Consistency File An important benefit of controlling redundancy is that the data is consistent. It a data item appears only at one place, it is easy to maintain. If it is required to update data, the updating is performed at only one place. The change will Processing automatically take effect at all places where ever this data is used. 3- Consistency Constrains System Consistency constraints are the rules that must be followed to enter data in database. It the constraints are not fulfilled, data cannot be entered in VS database. Database management systems provide any easy way to apply different consistency constraints to ensure data consistency. For example, a constraint can be applied to ensure that the data is always entered in a specific Database range etc. 17  4- Advantage of Database Approach 4- Data Atomicity File A collection of all steps to complete a process is known as transaction. Data atomicity means that either a transaction should take place as s whole of it should not take place at all. It ensures that the database will always have correct Processing and consistent data. Suppose a user want to transfer money from account A to account B. This process consist of two steps: System  Deduct the money from account A.  Add the money to account B. Suppose that the system fails when the computer has performed the first step. VS It means that the amount has been deducted from account A, but has not been added to account B, This situation can make data inconsistence. The database Database management system does not allow such a situations to happen. Database management system either executes both step of dose not execute any step. 18  4- Advantage of Database Approach 5- Data Security File Data security is the protection of the database from unauthorized access. The database management system provides several procedures to maintain data security. The security is maintained by allowing access to the database through Processing the user of password. Not every user of database system should be able to accesses all data. System In some situations, it is required to provide different type of access permission to data for different users. For example, a data entry operator should only be allowed to enter data. The chairman of organization should be able to access or VS delete the data completely. Database management system provides different levels of security options for different users. Database 19  4- Advantage of Database Approach 6- Reduced Development A database organizes data more efficiently than a file processing system. It is File often easier and faster to develop program that use this data. Many database management system also provide several tools to assist in program Processing development. So it reduces the overall time for developing applications. 7- Compactness The database management system store data compactness and efficiency. It System requires less storage space than the file system. It saves the storage resources of the system and memory is not wasted. VS 8- Easier Reporting Reports are very important part of database applications. The reports are very essential for taking crucial decisions in an organization. The data in database is Database stored in an organized manner. It can easily be retrieved for creating different reports. The reports can prepared very easily and quickly in required format in database management system. 20  4- Advantage of Database Approach 9- Data sharing File Once database is developed, it can be used by several users in the organization. The database can also be shared by different applications. If a new application required the same data, it can share the existing database instead of developing Processing again. 10- Increased concurrency System In some situations, two of more users may access the same file simultaneously. It is possible that the accesses will interfere with each other. This may result in VS loss of information or even loss of integrity. Many DBMS manage concurrent access and ensure such problems cannot occur. Database 21  4- Advantage of Database Approach 11- Improved Backup and Recovery File In file based system, it is the responsibility of the user to protect data from failures of the computer system or application program. This may require taking backup of the data daily. If the data is lost, the backup is restored. The modern Processing Database Management system provide facilities to minimize the amount of processing that can be lost due to a failure. System 12- Data Independence Database approach provides the facility of data independence. It means that VS the data and the applications programs are separate from each other. It is possible to change data storage structure and operations without changing the Database application program. 22  5- The difference between File and Database approach is as follows: File-based approach Database approach 1 1 The program and data are independent of each The programs and data are inter-dependent. File 2 The data may be duplicated in different files that cause data redundancy. 2 other. The data is not duplicated and appears only once. 3 The same data in different file may be 3 The data appear only once so it is always Processing 4 different that create inconsistency. The data is separately stored in various files and it is difficult for application to retrieve the 4 consistent. The data is stored in tables which are linked together. The application can retrieve the System 5 appropriate data. The data I distributed in many difficult files and cannot be shared. 5 required data easily. The data is stored at one place and can be shared easily. 6 It is difficult to apply data integrity checks on 6 It provides many constraints for data integrity. VS 7 files. It provides poor security as the data is widely spread. 7 It provides many procedures to maintain data security. 8 It is difficult to maintain as it provides less 8 It provides many facilities to maintain the Database 9 controlling facilities. It is less complex system. 9 program easily. It is very complex system. 10 The cost is very less than DBMS. 10 The cost is much more than file system. 11 One application can fail without affecting the 11 All application relying one database fail if the others. database fails. 23 Data Models 24  1- Data Models  Data Models is a set of concepts that can be used to describe the structure of data in a database.  A database model shows the logical structure of a database, include the relationships and constraints that determine how data can be stored and Data accessed.  Data Models are used to support the development of information systems by providing the definition and format of data to be involved in future systems.  Data model also gives idea about possible alternatives to achieve targeted Models solution.  There are four common types of database model that are useful for different types of data or information. Depending upon your specific needs, one of these models can be used. 1. Hierarchical databases. 2. Network databases. 3. Relational databases. 4. Object-oriented databases. 25  1.1- Hierarchical databases Model  A hierarchical database model is a data model in which the data are organized into a tree-like structure. The data are stored as records which are connected to one another through links. A record is a collection of fields, with each field containing only one value. The type of a record defines which fields the record Data contains  It is one of the oldest database model developed by IBM for information Management System. In a hierarchical database model. Models  This type of Database model is rarely used nowadays.  The windows registry used in MS Windows is an example of a hierarchical database. Configuration settings are stored as tree structures with nodes.  This Model uses pointers to navigate between stored data.  Rule parent node can have many child nodes, but one child cannot have more then one parent. Relationship is one to man. 26  1.1- Hierarchical databases Model  The hierarchical database model mandates that each child record has only one parent, whereas each parent record can have one or more child records. In order to retrieve data from a hierarchical database, the whole tree needs to be Data traversed starting from the root node. Models 27  1.1- Hierarchical databases Model Advantages  The model allows us easy addition and deletion of new information.  Data at the top of the Hierarchy is very fast to access.  It worked well with linear data storage mediums such as tapes. Data  It relates well to anything that works through a one to many relationships. Disadvantages  It requires data to be repetitively stored in many different entities.  Now a day there is no longer use of linear data storage mediums such as tapes. Models  Searching for data requires the DBMS to run through the entire model from top to bottom until the required information is found, making queries very slow.  This model support only one to many relationships, many to many relationships are not supported. 28  1.2- Network databases Model  Network database model looks like a Hierarchical database model due to which many time it is called as modified version of Hierarchical database.  Network database model organized data more like a graph and can have more than one parent node. This model also uses pointers towards data but there is Data no need of parent to child association. It uses graph data structure. A child can have more than one parent. It has one to many or many to many relationship.  The network model was adopted by the CODASYL Data Base Task Group in 1969 and underwent a major update in 1971. A number of network database systems became popular on mainframe and minicomputers through the 1970s Models before being widely replaced by relational databases in the 1980s. 29  1.2- Network databases Model Advantage  The network model is conceptually simple and easy to design.  The network model can represent redundancy in data more effectively than in Data the hierarchical model.  The network model can handle the one to many and many to many relationships which is real help in modelling the real-life situations.  The data access is easier and flexible than the hierarchical model.  The network model is better than the hierarchical model in isolating the Models programs from the complex physical storage details. Disadvantage  All the records are maintained using pointers and hence the whole database structure becomes very complex.  The insertion, deletion and updating operations of any record require the large number of pointers adjustments.  The structural changes to the database is very difficult. 30  1.3- Relational databases Model  Relational database model is a collection of 2-D tables of data which consist of rows and columns. These table related to each other. In relational model, three key terms are heavily used such as relations, attributes, and domains. A relation nothing but is a table with rows and columns. The named columns of the relation are called as Data attributes, and finally the domain is nothing but the set of values the attributes can take. A particular attribute or combination of attribute is chosen as primary key that can be referred to in other tables, when it’s called as foreign key. Keys of a relation Models Primary key- it is the key that uniquely identifies a table. It doesn’t have null values. Foreign key- it refers to the primary key of some other table.it permits only those values which appear in the primary key of the table to which it refers. The model accounts for types of relationships between those table using keys including one-to-one, one-to-many, and many to many relationships. 31  1.3- Relational databases Model  A relational database is developed by E. F. Codd in 1970. The various software systems used to maintain relational databases are known as a relational database management system (RDBMS). Data  The relational model (RM) is an approach to managing data using a structure and language consistent with first-order predicate logic (SQL).  Most relational databases use the SQL data definition and query language Models In Figure, three of the five tables have been represented with sample data in selected columns. In particular, note that the customer ID column is stored in both the Customer table and the Order table. When the customer ID of a row in the Order table matches the customer ID of a row in the 32  1.3- Relational databases Model Customer table, you know that the order belongs to that particular customer. Similarly, the Employee ID column is stored in both the Employee and Order tables to indicate the employee who accepted each order. Data Models 33  1.3- Relational databases Model Advantage  Relational model is one of the most popular used database model.  In relational model, changes in the database structure do not affect the data access. Data  The revision of any information as tables consisting of rows and columns is much easier to understand.  The relational database supports both data independence and structure Models independence concept which makes the database design, maintenance, administration and usage much easier than the other models.  In this we can write complex query to accesses or modify the data from database.  It is easier to maintain security as compare to other models. 34  1.3- Relational databases Model Disadvantages  Mapping of objects in relational database is very difficult.  Object oriented paradigm is missing in relation model. Data  Data Integrity is difficult to ensure with Relational database.  Relational Model is not suitable for huge database but suitable for small database.  Hardware overheads are incurred which make it costly. Models  Ease of design can lead to bad design.  Relational database system hides the implementation complexities and the physical data storage details from the users. 35  1.4- Object-oriented databases Model  In the Object-Oriented Data Model, data and their relationships are contained in a single structure which is referred to as an object in this data model. In this, real-world problems are represented as objects with different attributes. All objects have multiple relationships between them. Basically, it is a combination Data of Object Oriented programming and a Relational Database Model.  Object oriented databases are different from relational databases which are table-oriented. The object-oriented data model is based on the object- Models oriented- programming language concept, which is now in wide use. Inheritance, polymorphism, overloading. object-identity, encapsulation and information hiding with methods to provide an interface to objects, are among the key concepts of object-oriented programming that have found applications in data modelling. The object-oriented data model also supports a rich type system, including structured and collection types. 36  1.4- Object-oriented databases Model The following figure shows the difference between relation and object- oriented database model. Data Models The following figure shows an example of object-oriented model. 37  1.4- Object-oriented databases Model Advantages  Object database can handle different types of data while relational data base handles a single data. Unlike traditional databases like hierarchical, network or Data relational, the object-oriented databases can handle the different types of data, for example, pictures, voice video, including text, numbers and so on.  Object-oriented databases provide us code reusability, real world modelling, Models and improved reliability and flexibility.  The object-oriented database is having low maintenance costs as compared to other model because most of the tasks within the system are encapsulated, they may be reused and incorporated into new tasks. 38  1.4- Object-oriented databases Model Disadvantages  There is no universally defined data model for an OODBMS, and most models lack a theoretical foundation. Data  In comparison to RDBMSs the use of OODBMS is still relatively limited.  There is a Lack of support for security in OODBMSs that do not provide adequate security mechanisms. Models  The system more complex than that of traditional DBMSs. 39