Introduction to Databases - University of Zakho Lecture Notes PDF

University of Zakho Introductio n to DATABASE S  University of Zakho Basic Types of Charact definitio databas eristics An ns e...

University of Zakho Introductio n to DATABASE S  University of Zakho Basic Types of Charact definitio databas eristics An ns e Historic of the example databas al for a e develop databas Databas Advanta approac ment of e e Users gesh of databas When using e not to the technol use a DBMS ogy DBMS approac Discussion Why Databases? Introduction Databases and database systems are an essential component of life in modern society. There are two types of database applications: Traditional database applications: Bank system. Hotel or airline reservation. Library. Online shopping. Supermarket. Modern database applications: Geographic information systems (GIS). Social Media Networks. What is a database ? Database is a collection of related data. Data means known facts that can be recorded and that have implicit meaning. For example, consider the names, telephone numbers, and addresses of the people you know. A database represents some aspect of the real world, sometimes called the miniworld. Changes to the miniworld are reflected in the database. A database can be of any size and any complexity What is a database ? What is a DBMS ? A database management system (DBMS) is a collection of programs that enables users to create and maintain a database. The DBMS is a general-purpose software system that facilitates the processes of defining, constructing, manipulating, and sharing databases among various users and applications. The DBMS typical functionalities are: 1. Defining 2. Constructing 3. Manipulating 4. Sharing 5. Protection 6. Maintaining A Simplified Database System Environment Figure 1 Types of databases A DBMS can support many different types of databases. Databases can be classified according to the: Number of users: Single user database / multiuser database Database location(s): Centralized database / distributed database Expected type and extent of use: Operational database / data warehouse An Example Figure 2 An Example Examples of querying (retrieving): Retrieve the transcript—a list of all courses and grades—of ‘Smith’ List the names of students who took the section of the ‘Database’ course offered in fall 2008 and their grades in that section List the prerequisites of the ‘Database’ course. Examples of updates include the following: Change the class of ‘Smith’ to 2 Create a new section for the ‘Database’ course for this semester Enter a grade of ‘A’ for ‘Smith’ in the ‘Database’ section of last semester Characteristics of the Database Approach Several characteristics distinguish the database approach from the much older approach of programming with files. In traditional file processing, each user defines and implements the files needed for a specific software application as part of programming the application. In the database approach, a single repository maintains data that is defined once and then accessed by various users. In file systems, each application is free to name data elements independently. In contrast, in a database, the names or labels of data are defined once, and used repeatedly by queries, transactions, and applications. Following are the main characteristics of the database approach: Characteristics of the Database Approach 1. Self-Describing Nature of a Database System Database system contains not only the database itself but also a complete definition or description of the database structure and constraints. This definition is stored in the DBMS catalog (meta-data). Characteristics of the Database Approach Figure 3 2. Insulation between Programs and Data, and Data Abstraction In traditional file processing, the structure of data files is embedded in the application programs, so any changes to the structure of a file may require changing all programs that access that file. By contrast, DBMS access programs do not require such changes in most cases. The structure of data files is stored in the DBMS catalog separately from the access programs. We call this property program-data independence. Characteristics of 3. Support of the Database Approach Multiple Views of the Data A database typically has many users, each of whom may require a different perspective or view of the database. For example, one user of the database may be interested only in accessing and printing the transcript of each student; second user, who is interested only in checking that students have taken all the prerequisites of each course for which they register. Characteristics of the Database 4. Sharing of Approach Data and Multiuser Transaction Processing A multiuser DBMS, as its name implies, must allow multiple users to access the database at the same time. Database users There are two types of people who dealing with database: People who may be called workers behind the People whose jobs involve scene those who work to the day-to-day use of a maintain the database large database; we call system environment but them the actors on the who are not actively scene. interested in the database contents as part of their daily job. [email protected] 19 Actors on the scene 1. Database Administrators: Database administrator (DBA). The DBA is responsible  for: to the database, 1- Authorizing access coordinating and monitoring its use. 2- Acquiring software and hardware resources as needed. 3- The DBA is accountable for problems such as security breaches and poor system response time. In large organizations, the DBA is assisted by a staff that carries out these functions. [email protected] 20 Actors on the scene 2. Database Designers: Database designers are responsible for: 1- Identifying the data to be stored in the database and for choosing appropriate structures to represent and store this data. 2- Communicate with all prospective database users in order to understand their requirements and to create a design that meets these requirements. [email protected] 21 Actors on the scene 3. End Users: End users are the people whose jobs require access to the database for querying, updating, and generating reports; the database primarily exists for their use. 1.Casual end users 1.Naive or parametric end users There are several 1.Sophisticated categories of end users: end users 1.Standalone users [email protected] 22 Actors on the 4. System Analysts and scene Application Programmers (Software Engineers) System analysts determine the requirements of end users, especially naive and parametric end users, and develop specifications for standard canned transactions that meet these requirements. Application programmers implement these specifications as programs; then they test, debug, document, and maintain these canned transactions. [email protected] 23 Workers behind the scene In addition to those who design, use, and administer a database, others are associated with the design, development, and operation of the DBMS software and system environment. These persons are typically not interested in the database content itself. We call them the workers behind the scene, and they include the following categories: DBMS system designers and implementers. Tool developers. Operators and maintenance personnel (system administration personnel) are responsible for the actual running and maintenance of the hardware and software environment for the database system. [email protected] 24 Advantages of Using the DBMS Approach Controlling Redundancy. 1 Data Normalization Controlled Redundancy 2 Restricting Unauthorized Access 3 Providing Persistent Storage for Program Objects object-oriented database systems Providing Storage Structures and Search Techniques for Efficient Query 4 Processing 5 Providing Backup and Recovery 6 Providing Multiple User Interfaces 7 Representing Complex Relationships among Data 8 Enforcing Integrity Constraints 9 Permitting Inferencing and Actions Using Rules [email protected] 25 An Example Figure 2 [email protected] 26 Advantages of Using the Figure 3: Redundancy DBMS Approach [email protected] 27 Advantages of Using the DBMS Approach Figure 4 Relationships [email protected] 28 Historical development of database technology 1. Hierarchical Databases A kind of database management system that links records together like a family tree such that each record type has only one owner, e.g. an order is owned by only one customer. Hierarchical structures were widely used in the first mainframe database management systems. However, due to their restrictions, they often cannot be used to relate structures that exist in the real world. [email protected] 29 Historical development of database technology ORDER CUSTOMER PRODUCT Figure 5 [email protected] 30 Historical development of database technology Product_NO Customer_ID 10 113 30 204 Address Name Customer_ID Description Product_NO Duhok Ahmad 113 TV 10 Zakho Ali 204 Phone 30 [email protected] 31 Historical development of database technology 2. Relational Databases Relational databases were originally proposed to separate the physical storage of data from its conceptual representation and to provide a mathematical foundation for data representation and querying. The relational data model also introduced high-level query languages that provided an alternative to programming language interfaces, making it much faster to write new queries. Relational systems were initially targeted to the same applications as earlier systems and provided flexibility to develop new queries quickly and to reorganize the database as requirements changed. Hence, data abstraction and program-data independence were much improved when compared to earlier systems. [email protected] 32 Historical development of database technology 3. Object-Oriented Databases The emergence of object-oriented programming languages in the 1980s and the need to store and share complex, structured objects led to the development of object-oriented databases (OODBs). Initially, OODBs were considered a competitor to relational databases, since they provided more general data structures. They also incorporated many of the useful object-oriented paradigms, such as abstract data types, encapsulation of operations, inheritance, and object identity. [email protected] 33 Historical development of database technology 4. Emergence of Big Data Storage Systems and NOSQL Databases In the first decade of the twenty-first century, the proliferation of applications and platforms such as social media Web sites, large e-commerce companies, Web search indexes, and cloud storage/backup led to a surge in the amount of data stored on large databases and massive servers. New types of database systems were necessary to manage these huge databases— systems that would provide fast search and retrieval as well as reliable and safe storage of nontraditional types of data, such as social media posts and tweets. [email protected] 34 Historical development of database technology Some of the requirements of these new systems were not compatible with SQL relational DBMSs (SQL is the standard data model and language for relational databases). The term NOSQL is generally interpreted as Not Only SQL, meaning that in systems than manage large amounts of data, some of the data is stored using SQL systems, whereas other data would be stored using NOSQL, depending on the application requirements. [email protected] 35 When Not to Use a DBMS The overhead costs of using a DBMS are due to the following: High initial investment in hardware, software, and training The generality that a DBMS provides for defining and processing data Overhead for providing security, concurrency control, recovery, and integrity functions [email protected] 36  When Not to Use a DBMS It may be more desirable to use regular files under the following circumstances: Simple, well-defined database applications that are not expected to change at all Stringent, real-time requirements for some application programs that may not be met because of DBMS overhead. Embedded systems with limited storage capacity, where a general-purpose DBMS would not fit No multiple-user access to data [email protected] 37  What is the difference between controlled and uncontrolled redundancy? Illustrate with examples. Some Questions Give examples of systems in which it may make sense to use traditional file processing instead of a database approach. What are the responsibilities of the DBA and the database designers? Discuss the main characteristics of the database approach and how it differs from traditional file systems. Discuss the main advantages of the DBMS and how it differs from traditional file systems. [email protected] 38