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
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
