Preface xx ·2ansoaihieithehidedionandSara5tandnanedihodtete .Nilesh Dalvi,Sumit Sanghai,Gaurav Bhalotia,Arvind Hulgeri K.V.Ragha- van,Prateek Kapadia,Sara Strandtman,Greg Speegle,and Dawn Bezviner helped to prepare the instructor's manual for earlier editions. The idea of using ships as part of the cover concept was originally suggested to us by Bruce Stephan. The following people pointed out errors in the fifth edition:Alex Coman, ndra Guravannavar,Arvind Hulgeri,Rohit Kulshreshtha,Sangw Lee.Joe H.C.Lu.Alex N.Napit z Donnie Pinkston Y ma Pin shi Raksh satpal,Am een Ranjan Srivastava,Hans Svensson,Morit se,and Evo Delele Yirc daw The following people offered suggestions and comments for the fifth and ear- lier editions of the book.R.B.Abhyankar,Hani Abu-Salem,Jamel R.Alsab bagh,Raj Ashar,Don Batory,Phil Bernhard,Christian Breimann,Gavin M. Bierman,Janek Bogucki,Haran Boral,Paul Bourgeois,Phil Bohannon,Robert Brazile,Yuri Breitbart,Ramzi Bualuan,Michael Carey,Soumen Chakrabarti, Tom Chappell,Zhengxin Chen,Y.C.Chin,Jan Chomicki,Laurens Damen Prasanna Dhandapani,Qin Ding,Valentin Dinu,I.Edwards,Christos Falout sos,Homma Farian,Alan Fekete,Frantisek Franek,Shashi Gadia,Hector Garcia-Molina.Goetz Graefe.Jim Grav.Le Grue nwald Eitan m Gura Bruce Hillyer r,Ron Hitche Chad H ind Hul nis nnidi nd M.K K ,Hyoung-Jo Kim,Henry K pl n, orth (father o E.),Caro oll, Choon Lee,Sang-Won Levinstein, Llewellyn Gary Lindstrom,L ing L iu,Dave Maier,Keith Marzullo,Marty Maskarinec Fletcher Mattox,Sharad Mehrotra,Jim Melton,Alberto Mendelzon,Ami Motro,Bhagirath Narahari,Yiu-Kai Dennis Ng,Thanh-Duy Nguyen,Anil Nigam,Cyril Orji,Meral Ozsoyoglu,D.B.Phatak,Juan Altmayer Pizzorno, Bruce Porter,Sunil Prabhakar,Jim Peterson,K.V.Raghavan,Nahid Rahman, Rajarshi Rakshit,Krithi Ramamritham,Mike Reiter,Greg Riccardi,Odinaldo Rodriguez,Mark Roth,Marek Rusinkiewicz,Michael R s,Sunita Sarawagi N.L.Sarda.Patrick Schmid.Nikhil Sethi.S.Seshadri.S vart Shen.Shas hi Shekhar Amit Sheth Max Smolen dit Sopa le,Jeff Wahls te乙hristo pher Winslett,Weining Zhang,and Liu Zhenming. Book Production The publisher was Raghu Srinivasan.The developmental editor was Melinda D.Bilecki.The project manager was Melissa Leick.The marketing manager was
Preface xxv • Lyn Dupre copyedited the third edition and Sara Strandtman edited the text ´ of the third edition. • Nilesh Dalvi, Sumit Sanghai, Gaurav Bhalotia, Arvind Hulgeri K. V. Raghavan, Prateek Kapadia, Sara Strandtman, Greg Speegle, and Dawn Bezviner helped to prepare the instructor’s manual for earlier editions. • The idea of using ships as part of the cover concept was originally suggested to us by Bruce Stephan. • The following people pointed out errors in the fifth edition: Alex Coman, Ravindra Guravannavar, Arvind Hulgeri, Rohit Kulshreshtha, Sang-Won Lee, Joe H. C. Lu, Alex N. Napitupulu, H. K. Park, Jian Pei, Fernando Saenz Perez, Donnie Pinkston, Yma Pinto, Rajarshi Rakshit, Sandeep Satpal, Amon Seagull, Barry Soroka, Praveen Ranjan Srivastava, Hans Svensson, Moritz Wiese, and Eyob Delele Yirdaw. • The following people offered suggestions and comments for the fifth and earlier editions of the book. R. B. Abhyankar, Hani Abu-Salem, Jamel R. Alsabbagh, Raj Ashar, Don Batory, Phil Bernhard, Christian Breimann, Gavin M. Bierman, Janek Bogucki, Haran Boral, Paul Bourgeois, Phil Bohannon, Robert Brazile, Yuri Breitbart, Ramzi Bualuan, Michael Carey, Soumen Chakrabarti, Tom Chappell, Zhengxin Chen, Y. C. Chin, Jan Chomicki, Laurens Damen, Prasanna Dhandapani, Qin Ding, Valentin Dinu, J. Edwards, Christos Faloutsos, Homma Farian, Alan Fekete, Frantisek Franek, Shashi Gadia, Hector Garcia-Molina, Goetz Graefe, Jim Gray, Le Gruenwald, Eitan M. Gurari, William Hankley, Bruce Hillyer, Ron Hitchens, Chad Hogg, Arvind Hulgeri, Yannis Ioannidis, Zheng Jiaping, Randy M. Kaplan, Graham J. L. Kemp, Rami Khouri, Hyoung-Joo Kim, Won Kim, Henry Korth (father of Henry F.), Carol Kroll, Hae Choon Lee, Sang-Won Lee, Irwin Levinstein, Mark Llewellyn, Gary Lindstrom, Ling Liu, Dave Maier, Keith Marzullo, Marty Maskarinec, Fletcher Mattox, Sharad Mehrotra, Jim Melton, Alberto Mendelzon, Ami Motro, Bhagirath Narahari, Yiu-Kai Dennis Ng, Thanh-Duy Nguyen, Anil Nigam, Cyril Orji, Meral Ozsoyoglu, D. B. Phatak, Juan Altmayer Pizzorno, Bruce Porter, Sunil Prabhakar, Jim Peterson, K. V. Raghavan, Nahid Rahman, Rajarshi Rakshit, Krithi Ramamritham, Mike Reiter, Greg Riccardi, Odinaldo Rodriguez, Mark Roth, Marek Rusinkiewicz, Michael Rys, Sunita Sarawagi, N. L. Sarda, Patrick Schmid, Nikhil Sethi, S. Seshadri, Stewart Shen, Shashi Shekhar, Amit Sheth, Max Smolens, Nandit Soparkar, Greg Speegle, Jeff Storey, Dilys Thomas, Prem Thomas, Tim Wahls, Anita Whitehall, Christopher Wilson, Marianne Winslett, Weining Zhang, and Liu Zhenming. Book Production The publisher was Raghu Srinivasan. The developmental editor was Melinda D. Bilecki. The project manager was Melissa Leick. The marketing manager was
xxvi Preface Curt Reynolds.The production supervisor was Laura Fuller.The book designer was Brenda Rolwes.The cover designer was Studio Montage,St.Louis,Missouri. The copyeditor was George Watson.The proofreader was Kevin Campbell.The freelance indexer was Tobiah Waldron.The aptara team consisted of raman Arora and Sudeshna nandy Personal Notes Sudarshan would like to acknowledge his wife,Sita,for her love and support and children Madhur and Advaith for their love and joie de vivre.Hank would like to acknowledge his wife,Joan,and his children,Abby and Joe,for their love and understanding.Avi would like to acknowledge Valerie for her love,patience, and support during the revision of this book. A.S. H.F.K. S.S
xxvi Preface Curt Reynolds. The production supervisor was Laura Fuller. The book designer was Brenda Rolwes. The cover designer was Studio Montage, St. Louis, Missouri. The copyeditor was George Watson. The proofreader was Kevin Campbell. The freelance indexer was Tobiah Waldron. The Aptara team consisted of Raman Arora and Sudeshna Nandy Personal Notes Sudarshan would like to acknowledge his wife, Sita, for her love and support, and children Madhur and Advaith for their love and joie de vivre. Hank would like to acknowledge his wife, Joan, and his children, Abby and Joe, for their love and understanding. Avi would like to acknowledge Valerie for her love, patience, and support during the revision of this book. A. S. H. F. K. S. S
CHAPTER Introduction A database-management system (DBMS)is a collection of interrelated data and a set of programs to access those data.The collection of data,usually referred to as the database,contains information relevant to an enterprise.The primary goal dway tostoand trieve datanthi of a DBMs is to Databa agement defining structure for storage of i ormat 01 and providing mechanisms for the manipulation of information.In addition,the database system must ensure the safety of the information stored,despite system crashes or attempts at unauthorized access.If data are to be shared among several users,the system must avoid possible anomalous results. Because information is so important in most organizations,computer scien- tists have developed a large body of concepts and techniques for managing data. These concepts and techniques form the focus of this book.This chapter briefly introduces the principles of database systems. 1.1 Database-System Applications Databases are widely used.Here are some representative applications: .Enterprise Information Sales:For customer,product,and purchase information Accounting:For payments,receipts,account balances,assets and other accounting information. Human resources:For information about employees,salaries,payroll taxes, and benefits,and for generation of paychecks. o Manufacturing:For management of the supply chain and for tracking pro- duction of items in factories.inventories of it ems in warehouses and stores and orders for items 1
CHAPTER 1 Introduction A database-management system (DBMS) is a collection of interrelated data and a set of programs to access those data. The collection of data, usually referred to as the database, contains information relevant to an enterprise. The primary goal of a DBMS is to provide a way to store and retrieve database information that is both convenient and efficient. Database systems are designed to manage large bodies of information. Management of data involves both defining structures for storage of information and providing mechanisms for the manipulation of information. In addition, the database system must ensure the safety of the information stored, despite system crashes or attempts at unauthorized access. If data are to be shared among several users, the system must avoid possible anomalous results. Because information is so important in most organizations, computer scientists have developed a large body of concepts and techniques for managing data. These concepts and techniques form the focus of this book. This chapter briefly introduces the principles of database systems. 1.1 Database-System Applications Databases are widely used. Here are some representative applications: • Enterprise Information ◦ Sales: For customer, product, and purchase information. ◦ Accounting: For payments, receipts, account balances, assets and other accounting information. ◦ Human resources: For information about employees, salaries, payroll taxes, and benefits, and for generation of paychecks. ◦ Manufacturing: For management of the supply chain and for tracking production of items in factories, inventories of items in warehouses and stores, and orders for items. 1
2 Chapter1 Introduction o Online retailers:For sales data noted above plus online order tracking, generation of recommendation lists,and maintenance of online product 。Banking and Finance o Banking:For customer information,accounts,loans,and banking transac- tions. o Credit card transactions:For purchases on credit cards and generation of monthly statements. o Finance:For storing information about holdings,sales,and purchases of financial instruments such as stocks and bonds;also for storing real-time tatoenableietrading bycusomd .Universities:For student information,course registrations,and grades (in addition to standard enterprise information such as human resources and accounting). Airlines:For reservations and schedule information.Airlines were among the first to use databases in a geographically distributed manner. Telecommunication:For keeping records of calls made,generating monthly bills,maintaining balanc rep aid calling cards,a and s oring information about the comm netwo As the list illustrates,databases form an essential part of every enterprise today, storing not only types of information that are common to most ente prises.but also information that is specific to the category of the enterprise Over the course of the last four decades of the twentieth century,use of database erp athough wit ery few ople i nter ed di. ut 31 datab racted with reports such as credit card statements,o through agents such as bank tellers and airline reservat on agents hen auto- mated teller machines came along and let users interact directly with databases Phone interfaces to computers(interactive voice-response systems)also allowed users to deal directly with databases-a caller could dial a number,and press phone keys to enter information or to select alternative options,to find flight arrival/departure times,for example,or to register for courses in a university. The Internet revolution of the late 1990s sharply increased direct user access to databases.Organizations converted many of their phone interfaces to databases into Web interfaces,and made a variety of services and information available online.For instance,when you online bookstore and browse a book or data s a database.Whe er i e.Wh en you acc a bank Websit ve your an e and transac ion information, retrieved from the bank's database system.When you access a Web site,informa
2 Chapter 1 Introduction ◦ Online retailers: For sales data noted above plus online order tracking, generation of recommendation lists, and maintenance of online product evaluations. • Banking and Finance ◦ Banking: For customer information, accounts, loans, and banking transactions. ◦ Credit card transactions: For purchases on credit cards and generation of monthly statements. ◦ Finance: For storing information about holdings, sales, and purchases of financial instruments such as stocks and bonds; also for storing real-time market data to enable online trading by customers and automated trading by the firm. • Universities: For student information, course registrations, and grades (in addition to standard enterprise information such as human resources and accounting). • Airlines: For reservations and schedule information. Airlines were among the first to use databases in a geographically distributed manner. • Telecommunication: For keeping records of calls made, generating monthly bills, maintaining balances on prepaid calling cards, and storing information about the communication networks. As the list illustrates, databases form an essential part of every enterprise today, storing not only types of information that are common to most enterprises, but also information that is specific to the category of the enterprise. Over the course of the last four decades of the twentieth century, use of databases grew in all enterprises. In the early days, very few people interacted directly with database systems, although without realizing it, they interacted with databases indirectly— through printed reports such as credit card statements, or through agents such as bank tellers and airline reservation agents. Then automated teller machines came along and let users interact directly with databases. Phone interfaces to computers (interactive voice-response systems) also allowed users to deal directly with databases—a caller could dial a number, and press phone keys to enter information or to select alternative options, to find flight arrival/departure times, for example, or to register for courses in a university. The Internet revolution of the late 1990s sharply increased direct user access to databases. Organizations converted many of their phone interfaces to databases into Web interfaces, and made a variety of services and information available online. For instance, when you access an online bookstore and browse a book or music collection, you are accessing data stored in a database. When you enter an order online, your order is stored in a database. When you access a bank Web site and retrieve your bank balance and transaction information, the information is retrieved from the bank’s database system. When you access a Web site, informa-
1.2 Purpose of Database Systems 3 tion about you may be retrieved from a database about your Web to select which advertiseme ents rthermore,data accesses may be stored in a Thus,although user interfaces hide details of access to a database and most people are not even aware they are dealing with a database,accessing databases forms an essential part of almost everyone's life today. The importance of database systems can be judged in another way-today, database system vendors like Oracle are among the largest software companies in the world,and database systems form an important part of the product line of Microsoft and IBM. 1.2 Purpose of Database Systems Database system ose in response to early methods of compu rized ment of commercial data.As an example o manage such methods ,typical of the 19 consider part of a university organization that,among other data,keeps infor mation about all instructors,students,departments,and course offerings.One way to keep the information on a computer is to store it in operating system files.To allow users to manipulate the information,the system has a number of application programs that manipulate the files,including programs to: Add new students.instructors.and courses Register students for courses and generate class rosters Assign gradestostudents,computegrade point averages(GPA),and generate transcript System programmers wrote these application programs to meet the needs of the university. New application programs are added to the system as the need arises.For example,suppose that a university decides to create a new major(say,computer science).As a result,the university creates a new department and creates new per- manent files(or adds information to existing files)to record information about all the instructors in the department,students in that major,course offerings,degn quirements.etc.The university may have to write ew to deal with rules specific to the new naior ne Pplicatio have to be written ne in the versity. the em This typi s more file es and re applicat Cie-pocesingsystemaeSas by a conventional opera 盟om d6 he吧 files,and it needs propriate files.Before database management systems(DBMSs)were introduced, organizations usually stored information in such systems. Keeping organizational information in a file-processing system has a number of major disadvantages:
1.2 Purpose of Database Systems 3 tion about you may be retrieved from a database to select which advertisements you should see. Furthermore, data about your Web accesses may be stored in a database. Thus, although user interfaces hide details of access to a database, and most people are not even aware they are dealing with a database, accessing databases forms an essential part of almost everyone’s life today. The importance of database systems can be judged in another way— today, database system vendors like Oracle are among the largest software companies in the world, and database systems form an important part of the product line of Microsoft and IBM. 1.2 Purpose of Database Systems Database systems arose in response to early methods of computerized management of commercial data. As an example of such methods, typical of the 1960s, consider part of a university organization that, among other data, keeps information about all instructors, students, departments, and course offerings. One way to keep the information on a computer is to store it in operating system files. To allow users to manipulate the information, the system has a number of application programs that manipulate the files, including programs to: • Add new students, instructors, and courses • Register students for courses and generate class rosters • Assign grades to students, compute grade point averages (GPA), and generate transcripts System programmers wrote these application programs to meet the needs of the university. New application programs are added to the system as the need arises. For example, suppose that a university decides to create a new major (say, computer science). As a result, the university creates a new department and creates new permanent files (or adds information to existing files) to record information about all the instructors in the department, students in that major, course offerings, degree requirements, etc. The university may have to write new application programs to deal with rules specific to the new major. New application programs may also have to be written to handle new rules in the university. Thus, as time goes by, the system acquires more files and more application programs. This typical file-processing system is supported by a conventional operating system. The system stores permanent records in various files, and it needs different application programs to extract records from, and add records to, the appropriate files. Before database management systems (DBMSs) were introduced, organizations usually stored information in such systems. Keeping organizational information in a file-processing system has a number of major disadvantages: