This preview shows page 1-2-3-4-5-6-7-8-9-10-11-12-13-14-100-101-102-103-104-105-106-107-108-109-110-111-112-113-114-202-203-204-205-206-207-208-209-210-211-212-213-214-215 out of 215 pages.
Fundamentals of Database Systems Laboratory Manual1 Rajshekhar Sunderraman Georgia State University August 2010 1 To accompany Elmasri and Navathe Fundamentals of Database Systems 6th Edition Addison Wesley 2010 2 Preface This laboratory manual accompanies the popular database textbook Elmasri and Navathe Fundamentals of Database Systems 6th Edition Addison Wesley 2010 It provides supplemental materials to enhance the practical coverage of concepts in an introductory database systems course The material presented in this laboratory manual complement many of the chapters of the Elmasri Navathe text typically covered in most introductory database systems courses Chapter Mappings The laboratory manual consists of 8 chapters and the following table shows the mapping to the chapters in the Elmasri Navathe textbook Laboratory Manual Chapter Elmasri Navathe 6th Edition Chapter s Chapter 1 Chapters 7 8 and 9 Chapter 2 Chapters 3 6 and 26 Chapter 3 Chapters 4 5 and 13 Chapter 4 Chapters 4 5 and 14 Chapter 5 Chapters 15 and 16 Chapter 6 Chapter 11 Chapter 7 Chapter 12 Chapter 8 Chapters 13 and 14 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 three interpreters that can be used to execute queries in Relational Algebra Domain Relational Calculus and Datalog 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 Chapter 3 presents techniques to interact and program 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 3 Chapter 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 as well as a complete Online Address Book application 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 programming with a popular open source Object Oriented Database Management system db4o Creating and populating objects in db4o is covered as well various methods to query and retrieve data from the object oriented database is introduced Db4o supports various object oriented programming interfaces but the Java interface is covered in the lab manual Chapter 7 presents XML and its related technologies Query languages XPath and XQuery are covered as well as schema specification language XML Schema Numerous examples are presented including a complete specification of the company database in XML along with a XML Schema Chapter 8 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 db4o 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 Rajshekhar Sunderraman Atlanta Georgia August 2010 4 Contents 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 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 Examples 27 2 3 DOMAIN RELATIONAL CALCULUS INTERPRETER 30 2 3 1 Domain Relational Calculus Syntax 30 2 3 2 Safe DRC Queries 32 2 3 3 DRC Query Examples 34 2 4 DATALOG INTERPRETER 35 2 4 1 Datalog Syntax 35 2 4 2 Datalog Query Examples 36 EXERCISES 42 RELATIONAL DATABASE MANAGEMENT SYSTEM ORACLE 45 3 1 COMPANY DATABASE 45 3 2 SQL PLUS UTILITY 49 3 3 SQL LOADER UTILITY 50 3 4 PROGRAMMING WITH ORACLE USING THE JDBC API 53 EXERCISES 63 RELATIONAL DATABASE MANAGEMENT SYSTEM MYSQL 69 4 1 COMPANY DATABASE 69 4 2 MYSQL UTILITY 73 4 3 MYSQL AND PHP PROGRAMMING 75 4 4 ONLINE ADDRESS BOOK 87 EXERCISES 100 DATABASE DESIGN DBD TOOLKIT 103 5 1 CODING RELATIONAL SCHEMAS AND FUNCTIONAL DEPENDENCIES 103 5 2 INVOKING THE SWI PROLOG INTERPRETER 103 5 3 DBD SYSTEM PREDICATES 105 5 3 1 xplus R F X Xplus 105 5 3 2 finfplus R F X Y 106 5 3 3 fplus R F Fplus 106 5 3 4 implies R F1 F2 and equiv R F1 F2 107 5 3 5 superkey R F K and candkey R F K 108 5 3 6 mincover R F FC 109 5 3 7 ljd R F R1 R2 ljd R F D and fpd R F D 110 5 3 8 is3NF R F and threenf R F D 113 5 5 3 9 isBCNF R F and bcnf R F D 113 EXERCISES 114 OBJECT ORIENTED DATABASE MANAGEMENT SYSTEMS DB4O 119 6 1 DB4O INSTALLATION AND GETTING STARTED 119 6 2 A SIMPLE EXAMPLE 120 6 3 DATABASE UPDATES AND DELETES 123 6 4
View Full Document
Unlocking...