Database Systems PDF

Database Systems Dr. Mahmoud El-arabawi Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 2 Course Contents ◼ Characteristics of file-based systems ◼ Introduction to Database and Database Users ◼ DBMS data models, DBMS as a software architecture ◼ Entity-Relationship (ER)...

Database Systems Dr. Mahmoud El-arabawi Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 2 Course Contents ◼ Characteristics of file-based systems ◼ Introduction to Database and Database Users ◼ DBMS data models, DBMS as a software architecture ◼ Entity-Relationship (ER) Model ◼ Enhanced ERD ◼ Relational Data Model ◼ Database Normalization ◼ Relational Algebra ◼ Indexing Structures for Files ◼ Algorithms for Query Processing and Optimization Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Chapter 1 Introduction: Databases and Database Users Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe  File-Based Systems Collection of application programs that perform services for the end users (e.g. reports). Each program defines and manages its own data. Pearson Education © 2014 Limitations of File-Based Approach Separation and isolation of data Each program maintains its own set of data. Users of one program may be unaware of potentially useful data held by other programs. Duplication of data Same data is held by different programs. Wasted space and potentially different values and/or different formats for the same item. Pearson Education © 2014 Limitations of File-Based Approach Data dependence File structure is defined in the program code. Incompatible file formats Programs are written in different languages, and so cannot easily access each other’s files. Pearson Education © 2014 Why we needed Database Definition of data was embedded in application programs, rather than being stored separately and independently. No control over access and manipulation of data beyond that imposed by application programs. Result: the database and Database Management System (DBMS). Pearson Education © 2014 Introduction ◼ Databases and database systems are an essential component of life in modern society: ◼ Most of us encounter several activities every day that involve some interaction with a database. ◼ Databases play a critical role in almost all areas where computers are used ◼ Including business, electronic commerce, engineering, medicine, genetics, law and education. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 9 Types of Databases ◼ Traditional database applications store textual or numeric information. ◼ Multimedia databases 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 to extract and analyze useful business information from very large databases to support decision making. ◼ Real time databases are used to control industrial and manufacturing processes. For example, a stock market changes very rapidly and is dynamic. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 10 Database Applications Examples ▪ Enterprise Information Sales: customers, products, purchases Accounting: payments, receipts, assets Human Resources: Information about employees, salaries, payroll taxes. ▪ Manufacturing: management of production, inventory, orders, supply chain. ▪ Banking and finance customer information, accounts, loans, and banking transactions. Credit card transactions Finance: sales and purchases of financial instruments (e.g., stocks and bonds; storing real-time market data ▪Universities: registration, grades Database Applications Examples ▪ Airlines: reservations, schedules ▪ Telecommunication: records of calls, texts, and data usage, generating monthly bills, maintaining balances on prepaid calling cards ▪ Web-based services Online retailers: order tracking, customized recommendations Online advertisements ▪ Document databases ▪ Navigation systems: Basic Definitions ◼ Database: ◼ A collection of related data. ◼ A database represents some aspects of the real world (miniworld). ◼ Data: ◼ Known facts that can be recorded and have an implicit meaning. ◼ Mini-world: ◼ Represents some aspects of the real world. For example, student grades and transcripts at a university. ◼ In order for a database to be accurate and reliable at all times, it must be a true reflection of the mini-world that it represents. ◼ Transaction ◼ It is an executing program or process that includes one or more database accesses, such Copyright asElmasri © 2007 Ramez reading or updating of database and Shamkant B. Navathe Slide 1- 13 Basic Definitions ◼ Database Management System (DBMS): ◼ A software system to facilitate the creation and maintenance of a computerized database. ▪ A software system that enables users to define, create, maintain, and control access to the database. ▪ (Database) application program: a computer program that interacts with database by issuing an appropriate request (SQL statement) to the DBMS. ◼ Database System: ◼ The DBMS software together with the database itself. Sometimes, the applications are also included. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 14 Example of a simple database Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 15 A database can be of any size and complexity For example: ◼ The list of names and addresses (of people you know) may consist of only a few hundred records, each with a simple structure. ◼ On the other hand, the computerized catalog of a large library may contain half a million entries organized under different categories—by primary author’s last name, by subject, by book title—with each category organized alphabetically. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 16 A database can be of any size and complexity An example of a large commercial database is Amazon.com. ◼ It contains data for over 20 million books, CDs, videos, DVDs, games, electronics, apparel, and other items. ◼ The database occupies over 2 terabytes (a terabyte is 1012 bytes worth of storage) and is stored on 200 different computers (called servers). ◼ About 15 million 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. ◼ About 100 people are responsible for keeping the Amazon database up-to-date. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 17 Simplified database system environment Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 18 Example of a Database (with a Conceptual Data Model) ◼ Mini-world for the example: ◼ Part of a UNIVERSITY environment. ◼ Some mini-world entities: ◼ STUDENTs ◼ COURSEs ◼ SECTIONs (of COURSEs) ◼ (academic) DEPARTMENTs ◼ INSTRUCTORs Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 19 Example of a Database (with a Conceptual Data Model) ◼ Some mini-world relationships: ◼ SECTIONs are of specific COURSEs ◼ STUDENTs take SECTIONs ◼ COURSEs have prerequisite COURSEs ◼ INSTRUCTORs teach SECTIONs ◼ COURSEs are offered by DEPARTMENTs ◼ STUDENTs major in DEPARTMENTs Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 20 Example of a simple database Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 21 Typical DBMS Functionality ◼ Define a particular database in terms of its data types, structures, and constraints ◼ Construct or Load the initial database contents on a secondary storage medium ◼ Manipulating the database: ◼ Retrieval: Querying, generating reports ◼ Modification: Insertions, deletions and updates to its content ◼ Accessing the database through Web applications ◼ Processing and Sharing by a set of concurrent users and application programs – yet, keeping all data valid and consistent Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 22 Typical DBMS Functionality ◼ Other features: ◼ Protection or Security measures to prevent unauthorized access ◼ “Active” processing to take internal actions on data ◼ Presentation and Visualization of data ◼ Maintaining the database and associated programs over the lifetime of the database application ◼ Called database, software, and system maintenance Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 23  Main Characteristics of the Database Approach ◼ Database approach vs. file processing approach! ◼ Inconsistency ◼ Redundancy ◼ Waste storage ◼ Much effort to maintain updates Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 24