COMP 207 Database Design Lecture 1 Introduction PDF

COMP 207: Database Systems. ‫نظم قواعد البيانات‬ Lecture 1: Introduction Dr. Wael Zakaria Associate Professor Computer Science 1 Dept. of Mathematics, Faculty of Science. Ain Shams University  Course Code/Title: 2 Course Descri...

COMP 207: Database Systems. ‫نظم قواعد البيانات‬ Lecture 1: Introduction Dr. Wael Zakaria Associate Professor Computer Science 1 Dept. of Mathematics, Faculty of Science. Ain Shams University  Course Code/Title: 2 Course Description COMP 207 Database Design Level/semester 2 / 1 (2020-2021) #credit hours 4 #hours/Lecture 3 #hours/Tutorial 0 #hours/Practical 2 Lecturer Dr. Wael Zakaria Contact [email protected] Teaching Assistants  Course Code/Title: COMP 207 Database Design 3 Course Description Grading System Total is 200 Final exam 120 Mid-Term 20 Lab 50 (in total) will be distributed as follows Attendance and activity 10 Projects 15 Quiz 5 Final Lab exam 20 Oral 10 Final Exam (hours) 3 All Lectures, book, assignments, and sheets will be on this link Book El Masri, R and Navathe, S., “Fundamentals of Database Systems”, 7th Edition, Pearson 6th Edition, Addison Wesley, ISBN-13: 978-0-136-08620-8 (2016) 4  Edrawsoft  pinterest  Course Objectives 5 The goal of the course is to introduce the concepts of designing, constructing, implementing, and managing relational database systems using entity–relationships/Enhanced entity-relationships model (ER/EER model) and the relational data model. the mapping algorithm for converting ER/EER into relational data model. Also, the course introduces structured query language (SQL), the relational algebra, relational calculus, functional independencies and normal forms. In addition, the student will have an overview of current web database warehouses and languages, OLAP, and OLTP.  6 SQL Structured Query Language ERD EERD Enhanced Entity Relationship Diagram Entity Relationship Diagram mapping algorithm  Course Contents 7 This course provides an introduction to design and implement database systems. Topics include:  Introduction to databases, database management system (DBMS) and database users  Data Modeling using the Entity-Relationship (ER)  Enhanced Entity-Relationship (EER) Model  The Relational Model  Relational database design using ER and EER-to-relational mapping  Structured query language (SQL)  Relational Algebra and Relational Calculus  Functional Dependencies  Normalization  Motivations of the course 8  Most of us encounter several activities every day that involve some interaction with a database. For example,  Bank: we go to the bank to deposit or withdraw funds.  Hotel: we make a hotel reservation.  Credit hours: registers of students enrolled in a university and grades in their exams  Airline: we make an airline reservation.  Computerized Library: we access a computerized library catalog to search for a bibliographic item.  Online Shopping: we purchase something such as a book, toy, or computer.  Supermarket: Even purchasing items at a supermarket often automatically updates the database that holds the inventory of grocery items.  Motivations of the course (Cont.) 9  Most of us encounter several activities every day that involve some interaction with a database. For example,  Social media Web sites: such as Facebook, and Twitter has required the creation of huge databases that store nontraditional data, such as posts, tweets, like, share, images, and video clips.  multimedia databases is a new media technology has made it possible to store images, audio clips, and video streams digitally.  Geographic information systems (GIS): can store and analyze maps, weather data, and satellite images.  Data warehouses and online analytical processing (OLAP) systems are used in many companies to extract and analyze useful business information from very large databases to support decision making.  Real-time and active database technology is used to control industrial and manufacturing processes.  World Wide Web (WWW) database search techniques: are being applied to WWW to improve the search for information that is needed by users browsing the Internet.  ‫قواعد البيانات‬ 10  COVID 19 Database  ‫قواعد البيانات‬ 11  COVID 19 Database  ‫قواعد البيانات‬ 12  University Example Database  ‫قواعد البيانات‬ 13  University Example Database  ‫قواعد البيانات‬ ‫‪14‬‬ ‫‪Database‬‬ 15 ‫قواعد البيانات‬  Social Media Database 16 ‫قواعد البيانات‬  Social Media Database  Basics Concepts of Databases 17  Databases and database technology It has a major impact on the growing use of computers. It is fair to say that databases play a critical role in almost all areas where computers are used, including business, electronic commerce, engineering, medicine, genetics, law, education, and library science.  Definition: (Data) It is 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.  Database 18 Definition (Database) is an organized collection of related data stored electronically in a computer system. Database must have the following properties: A database is an organized collection of structured information, or data, typically stored electronically in a computer system. 1. It represents some aspect of the real world, sometimes called the miniworld. Changes to the miniworld are reflected in the database. 2. It is a logically coherent collection of data. A random assortment of data cannot correctly be referred to as a database. 3. It is designed, built, and populated with data for a specific purpose. It has a specific group of users and some applications in which users are interested.  Databases challenges 19  A database can be of any size and complexity. Database for Library may contain half a million entries organized under different categories organized alphabetically (primary author’s last name, by subject, by book title) Database for facebook contain more than a billion users. The database must maintain information on users, posts, likes, and share.  Databases challenges Cont. 20  Database for commercial large databases such as Amazon.com.  It contains data for over 60 million active users, and millions of books, CDs, videos, DVDs, games, electronics, apparel, and other items. The database occupies over 42 terabytes and is stored on hundreds of computers (called servers). Millions of visitors access Amazon.com each day and use the database to make purchases.  The database is continually updated as new books and other items are added to the inventory, and stock quantities are updated as purchases are transacted.  Manually Database Vs. Computerized Database 21 A database may be generated and maintained manually or computerized: Manually: a library card catalog is a database that may be created and maintained manually. A computerized database may be created and maintained either by a group of application programs written specifically for that task or by a database management system DBMS  Database management system (DBMS) 22  Definition: (Database management system (DBMS) ) DBMS is a general-purpose software system that facilitates the processes of defining, constructing, manipulating, and sharing databases among various users and applications. These processes are called DBMS functionalities  DBMS’s Functionalities 23 1. Defining databases specifies the data types, structures, and constraints of the data to be stored.  The database definition or descriptive information is also stored by the DBMS in the form of a database catalog or dictionary; it is called meta- data. 2. Constructing databases is the process of storing the data on some storage medium, The reminder of DBMS’s Functionalities will be continued in the next slide.  DBMS’s Functionalities Cont. 24 3.Manipulating databases: includes functions such as: Querying the database to retrieve specific data, Updating the database to reflect changes in the miniworld, and Generating reports from the data. The reminder of DBMS’s Functionalities will be continued in the next slide.  DBMS’s Functionalities Cont. 25 4.Sharing databases: is a process of sharing the database among various users and applications to access the database simultaneously. 5.Protecting the databases: protects the database over a long period of time. Protection includes system protection against hardware or software malfunction (or crashes) and security protection against unauthorized or malicious access.  DBMS’s Functionalities Cont. 26 6.Maintain the database: allows the system to evolve/update as requirements change over time. Examples of popular database software or DBMSs include MySQL, Microsoft Access, Microsoft SQL Server, FileMaker Pro, Oracle Database, and dBASE.  Database system 27  Definition 4: (Database system) It is the database and DBMS software together. Definition 4: (Application program) It accesses the database by sending queries or requests for data to the DBMS.  Case Study: A University Database example 28 A University database for maintaining information concerning students, courses, and grades in a university environment We have: STUDENT file stores data on each student COURSE file stores data on each course SECTION file stores data on each section of each course GRADE_REPORT file stores the grades that students receive PREREQUISITE file stores the prerequisites  Case Study: A University Database example 29  Case Study: Database manipulation 30 Database manipulation involves querying and updating Examples of querying are: 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  Case Study: Database manipulation 31 Database manipulation involves querying and updating Examples of updating are: Change the class of ‘Smith’ to sophomore or 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  Case Study: Database manipulation 32 These informal queries and updates must be specified precisely in the query language of the DBMS before they can be processed. At this stage, it is useful to describe the database as a part of an information system (IS) within any organization. The Information Technology (IT) department within a company designs and maintains an information system consisting of various computers, storage systems, application software, and databases.  Case Study: Database manipulation 33 Design of a new application for an existing database or design of a brand new database starts off with a phase called requirements specification and analysis. These requirements are documented in detail and transformed into a conceptual design that can be represented and manipulated using some computerized tools so that it can be easily maintained, modified, and transformed into a database implementation. We will introduce a model called the Entity-Relationship model in the next lecture that is used for this purpose.  Case Study: Database manipulation 34  The design is then translated to a logical design that can be expressed in a data model implemented in a commercial DBMS.  Some books emphasizes a data model known as the Relational Data Model. This is currently the most popular approach for designing and implementing databases using relational DBMSs.  The final stage is physical design, during which further specifications are provided for storing and accessing the database.  The database design is implemented, populated with actual data, and continuously maintained to reflect the state of the miniworld.  Other storages (Word, Excel, txt files) 35 Files such as Word documents and Excel spreadsheets, etc... Is used to store data but not efficient as databases:  Advantages of using files especially when the amount of data is small  Besides, there is no need to get a database system or learn how to use it.  It is easy to write applications using standard programming languages.  Disadvantages of using files:  Limited user interface  Are not efficient in handling large amount of data  not provide support to specifying relationships between data items  Do not provide a query language  Database approach Versus File approach 36 In traditional file processing,  Each user defines and implements the files needed for a specific software application as part of programming the application.  For example, one user, the grade reporting office,  may keep files on students and their grades.  Programs to print a student’s transcript and to enter new grades are implemented as part of the application.  A second user, the accounting office,  may keep track of students’ fees and their payments.  Although both users are interested in data about students, each user maintains separate files— and programs to manipulate these files—because each requires some data not available from the other user’s files.  This redundancy in defining and storing data results in wasted storage space and in redundant efforts to maintain common up-to-date data.  In the database approach, a single repository maintains data that is defined once and then accessed by various users.  Database approach Versus File approach Cont. 37  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.  Main characteristics of the database 38 Self-describing nature of a database system. Insulation between programs and data, and data abstraction. Support of multiple views of the data. Sharing of data and multiuser transaction processing.  Self-Describing Nature of a Database System 39 A database system contains both  A database itself  A complete definition or description of the database structure and constraints. This definition is stored in the DBMS catalog. The information stored in the catalog is called meta-data. which contains information such as the structure of each file, the type and storage format of each data item, and various constraints on the data  Self-Describing Nature of a Database System 40  Self-Describing Nature of a Database System 41 In traditional file processing, data definition is typically part of the application programs themselves. Hence, these programs are constrained to work with only one specific database, whose structure is declared in the application programs.  Insulation between programs and data, and data 42 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, A 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.  Support of Multiple Views of the Data 43  A database typically has many users, each of whom may require a different perspective or view of the database. Example  one user of the database may be interested only in accessing and printing the transcript of each student.  Support of Multiple Views of the Data 44  A second user, who is interested only in checking that students have taken all the prerequisites of each course for which they register.  Sharing of Data and Multiuser Transaction 45 Processing A multiuser DBMS allow multiple users to access the database at the same time. The DBMS must include concurrency control software to ensure that several users trying to update the same data do so in a controlled manner so that the result of the updates is correct. several reservation agents try to assign a seat on an airline flight, the DBMS should ensure that each seat can be accessed by only one agent at a time for assignment to a passenger.  Sharing of Data and Multiuser Transaction 46 Processing  For example,  when several reservation agents try to assign a seat on an airline flight, the DBMS should ensure that each seat can be accessed by only one agent at a time for assignment to a passenger. These types of applications are generally called online transaction processing (OLTP) applications. A fundamental role of multiuser DBMS software is to ensure that concurrent transactions operate correctly and efficiently.  Databases Users 47  In large organizations, many people are involved in the design, use, and maintenance of a large database with hundreds of users. There are two types of people:  actors on the scene: whose jobs involve the day-to-day use of a large database; we call them the actors on the scene.  workers behind the scene—those who work to maintain the database system environment but who are not actively interested in the database contents as part of their daily job.  actors on the scene 48  Database Administrators DBA. The DBA is responsible for authorizing access to the database, coordinating and monitoring its use, and acquiring software and hardware resources as needed. The DBA is accountable for problems such as security breaches ‫الخروقات األمنية‬ and poor system response time.  Database Designers. Database designers communicates with all prospective database users in order to understand their requirements and to create a design that meets these requirements.  actors on the scene 49  End Users.  Users are the people whose jobs require access to the database for querying, updating, and generating reports.  System Analysts and Application Programmers (Software Engineers).  System analysts determine the requirements of end users and develop specifications for standard transactions that meet these requirements.  Application programmers implement these specifications as programs; then they test, debug, document, and maintain these transactions.  Software developers or software engineers ( analysts and programmers ) are familiar with the full range of capabilities provided by the DBMS to accomplish their tasks.  Workers behind the Scene 50  DBMS system designers and implementers  Design and implement the DBMS modules and interfaces as a software package.  Tool developers  Design and implement tools.  Operators and maintenance personnel  Responsible for running and maintenance of hardware and software environment for database system.  Advantages of Using the DBMS Approach 51  Controlling Redundancy.  Restricting Unauthorized Access.  Providing Storage Structures and Search Techniques for Efficient Query Processing.  Providing Backup and Recovery.  Providing Multiple User Interfaces.  Representing Complex Relationships among Data.  Enforcing Integrity Constraints.  DBMS 52 http://db- engines.com/en/system/Microsoft+SQL+Server%3BM ySQL%3BOracle  MySQL is an open-source relational database management system based on SQL. 53 can run on any platform. As new and different requirements emerged with the internet, MySQL became the platform of choice for web developers and web-based applications. Because it’s designed to process millions of queries and thousands of transactions, MySQL is a popular choice for ecommerce businesses that need to manage multiple money transfers. On-demand flexibility is the primary feature of MySQL. MySQL is the DBMS behind some of the top websites and web-based applications in the world, including Airbnb, Uber, LinkedIn, Facebook, Twitter, and YouTube.  Data, Information, Knowledge, and Wisdom 54 http://www.systems-thinking.org/dikw/dikw.htm According to Russell Ackoff, the content of the human mind can be classified into five categories:  Data ‫ البيانات‬: data is raw.  Information ‫ المعلومات‬: information is data that has been given meaning by way of relational connection. [what, who, "where", and "when" questions]  Knowledge‫ المعرفة‬: knowledge is the appropriate collection of information, [how]  Understanding ‫الفهم‬: appreciation of "why"  Wisdom‫ الحكمة‬: evaluated understanding.  Data, Information, Knowledge, and Wisdom 55