Fundamentals of Database Systems Laboratory Manual1 Rajshekhar Sunderraman Georgia State University June 2006 1 To accompany Elmasri and Navathe, Fundamentals of Database Systems, 5th Edition, Addison-Wesley, 2007.2Preface This laboratory manual accompanies the popular database textbook Elmasri and Navathe, Fundamentals of Database Systems, 5th Edition, Addison-Wesley, 2007. It provides supplemental materials to enhance the practical coverage of concepts in an introductory database systems course. The material presented in this laboratory manual complements the first 11 chapters of the Elmasri/Navathe text typically covered in most introductory database systems courses. Chapter Mappings The laboratory manual consists of 6 chapters and the following table shows the mapping to the chapters in the Elmasri/Navathe textbook: Laboratory Manual Chapter Elmasri/Navathe 5th Edition Chapter(s)Chapter 1 Chapters 3, 4, and 7 Chapter 2 Chapter 6 Chapter 3 Chapter 9 Chapter 4 Chapter 26 Chapter 5 Chapters 10 and 11 Chapter 6 Chapters 9 and 26 Chapter 1 presents ERWin, a popular data modeling software that allows database designers to represent Entity-Relationship diagrams and automatically generate relational SQL code to create the database in one of several commercial relational database management systems such as Oracle or Microsoft SQLServer. The material presented in this chapter is tutorial in nature and covers the COMPANY database design of the Elmasri/Navathe text in detail. Chapter 2 presents two interpreters that can be used to execute queries in Relational Algebra and Domain Relational Calculus. These interpreters are part of a Java package that includes a rudimentary database engine capable of storing relations and able to perform basic relational algebraic operations on these relations. It is hoped that these interpreters will allow the student to get a better understanding of abstract query languages such as relational algebra and relational calculus. Chapter 3 presents interacting and programming with Oracle database management system. A popular data loading tool for Oracle databases called SQL Loader is introduced and the COMPANY database of the Elmasri/Navathe text is extended with additional data to make it more interesting to program with. Programming applications that access Oracle databases is then introduced in Java using the JDBC interface. Several non-trivial example programs are discussed.3Chapter 4 covers MySQL database management system, a popular open source database system that is increasing used by small and medium sized organizations. Programming Web applications in PhP that accesses MySQL databases is introduced with a complete database browser application for the COMPANY database. Chapter 5 introduces a Prolog-based toolkit for relational database design. The toolkit, called Database Designer (DBD), allows the student to work with numerous concepts and algorithms that deal with functional dependency theory and data normalization. The student may use DBD to verify answers to many questions related to functional dependency theory and normalization algorithms. Chapter 6 presents several semester long projects for students in introductory database courses to complete. These projects may be implemented in Java, PhP or any other favorite programming language and may access Oracle, MySQL or any other relational database management system. Code The laboratory manual comes with all the code and data presented in the different chapters. The software for the relational query interpreters as well as the database designer (DBD) also accompanies the laboratory manual. Software The software systems discussed and used in the laboratory manual are ERWin from Computer Associates, Oracle DBMS from Oracle, and MySQL, PhP, and SWI-Prolog from open source. Both Computer Associates and Oracle have educational pricing for their software and we expect the individual universities and colleges that use this laboratory manual to provide the software for use by their students. Acknowledgements The author wishes to acknowledge the contributions of many of his students. In particular, the author appreciates the diligent effort put forth by Ms. Manisha Jain in building and testing the DRC interpreter of Chapter 2. Rajshekhar Sunderraman Atlanta, Georgia June 20064Contents 1. ER MODELING TOOLS.......................................................................................................................................... 6 1.1 STARTING WITH ERWIN....................................................................................................................................... 6 1.2 ADDING ENTITY TYPES......................................................................................................................................... 7 1.3 ADDING RELATIONSHIPS .................................................................................................................................... 10 1.4 FORWARD ENGINEERING.................................................................................................................................... 12 1.5 SUPERTYPE/SUBTYPE EXAMPLE......................................................................................................................... 15 EXERCISES................................................................................................................................................................. 17 2. INTERPRETERS FOR ABSTRACT QUERY LANGUAGES............................................................................ 21 2.1 CREATING THE DATABASE ................................................................................................................................. 21 2.2 RELATIONAL ALGEBRA INTERPRETER .............................................................................................................. 23 2.2.1 Relational Algebra Syntax.......................................................................................................................... 23 2.2.2 Naming of Intermediate Relations and Attributes ..................................................................................... 25 2.2.3 Relational Algebraic Operators Supported by the RA Interpreter............................................................. 26 2.2.4