Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Community
Ask the community for help and clear up your study doubts
Discover the best universities in your country according to Docsity users
Free resources
Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors
Material Type: Notes; Professor: Liang; Class: INTRO PROGRAMMING PRINCIPLES; Subject: Computer Science; University: Armstrong Atlantic State University; Term: Unknown 2005;
Typology: Study notes
1 / 27
This page cannot be seen from the preview
Don't miss anything!
1
Part I
Database Concept and Design
The first part of the book is a stepping stone that will prepare you to embark on the journey of learning database programming. You will begin to know database systems, database language, relational data model, and will learn how to design efficient database systems using ER Modeling and normalization.
2
Objectives
1.1 Introduction
By now you may have heard a lot about database systems. Database systems are everywhere. Your social security information is stored in a database by the government. If you shop online, your purchase information is stored in a database by the company. If you attend a university, your academic information is stored in a database by the university. Database systems not only just simply store data, but also provide means of accessing, updating, manipulating, and analyzing data. Your social security information is updated periodically and you can register courses online. Database systems have played an important role for the society and for the commerce. This chapter
4
network, as shown in Figure 1.2.
Database Management System
… (^) database
Application Programs
Application Users
Database Management System
Figure 1.
An application program may access multiple database systems.
Most of today’s database systems are relational database systems , in which all of its data are stored in tables. A row of a table represents a record and a column of a table represents the value of a single attribute of the record. The relational data representation and manipulation is introduced in Chapter 2, “Relational Data Model.” The ubiquitous language for accessing relational databases is the SQL (Structured Query Language), which will be introduced in Part II, “SQL.” The focus of this book is to develop database application programs using SQL and Java.
1.3 Database Schemas
Database schemas describe the relationships, structures and constraints of the data in the database. There are three levels of schemas: internal schema , logical schema , and external schema. An internal schema defines how data is stored internally in the database. A logical schema presents a logical view of the data. An external schema presents part of the database that is interested to the users. This is also known as the three-schema architecture. The relationship among these schemas is shown in Figure 1.3.
5
External Schema
View 1 View 2
View n
Logical Schema
Table 1 Table 2
Table m
Internal Schema
External/Logical Mapping
Logical/Internal Mapping
Disk
Figure 1.
Database systems define data schemas at three levels.
1.3.1 Logical Schemas
Consider the Course table as shown in Figure 1.4. The logical schema of the Course table is shown in Figure 1.5. The schema defines the table name, the column names and types, and the constraints on the column values. The types define the range of the values. In the Course table, subjectId is a string of four characters, courseNumber and numOfCredits are integers, title is a variable-length string with maximum size 50. Constraints are additional restrictions on the columns. For example, the constraint in the Course schema is that numOfCredits must be greater than or equal to 1.
Course Table
courseId subjectId courseNumber title numOfCredits 11111 CSCI 1301 Introduction to Java I 4 11112 CSCI 1302 Introduction to Java II 3 11113 CSCI 3720 Database Systems 3 11114 CSCI 4750 Rapid Java Application 3 11115 MATH 2750 Calculus I 5 11116 MATH 3750 Calculus II 5 11117 EDUC 1111 Reading 3 11118 ITEC 1344 Database Administration 3
Figure 1.
7
the system’s default internal schema is used. If the table is used frequently for search on titles, you can create an index on the title attribute in the Course table to improve efficiency. The statement to create such a structure can be as follows:
create index CourseIndex on Course (title);
The internal schema defines how data is stored in the database, but not where data is stored. Where data is stored is specified by the database administrator and depends on the configuration of the database system.
1.3.3 External Schemas
The logic schemas define the entire logical view of the database system. A particular user needs to see only part of the database. An external schema can be used to define external views (usually abbreviated to just “view”) that present the part of the database interested to the user. For example, you can create a view that consists of only the MATH courses as follows:
create view MathCourse as select * from Course where subjectId = 'MATH';
NOTE: MySQL and Access currently don’t support views.
1.3.1 Mappings of the Schemas
The external schema describes the part of the database to a particular user group, the logical schema describes the entire database to all users, and the internal schema describes the physical storage structures of the database. All the schemas are the descriptions of the database at three levels; the actual data is stored on disks. A user’s request to access data through an external view involves actual tables and data structures for the table. The process of transforming requests and results between levels are called mappings. There are two types of mappings – external/logical mapping and logical/internal mapping. External/logical mapping handles the correspondents between the external schemas and logical schemas, and the logical/internal mapping handles the correspondents between the logical schema and internal schema. Mappings are handled by the DBMS and they are transparent to the users.
1.3.2 Data Independence
8
Obviously it takes time to handle mappings. So why do we need three schemas? The three-schema architecture achieves logical data independence and physical data independence and makes developing and maintaining database systems easier.
Logical data independence means that you can change the logical schema without changing the external schema. For example, you may have to modify the logical schema to expand the database by adding more tables. The existing external schemas are not changed. Therefore, the application programs that use the external schema remain intact.
Physical data independence means that you can change the internal schema without changing the logical schema. For example, you may have to modify the internal schema to add a new index in a table to improve the performance or drop an index if it is no longer needed. These changes do not affect the existing logical schema. So the applications that use the external schema or the logical schema are not affected.
1.4 Database Administrators, Developers, and End Users
A database system involves many people with different roles. Database administrators are the managers of the database systems. The developers are the people who build database applications for the users. Database end users are the people who use the applications. For example, a bank clerk who uses the database system to access the customer’s account is an end user for the bank database system. All the people who use the database are referred to as database users.
Database administrators (DBA) are responsible for creating user accounts, defining database schemas, allocating storage space for the database, maintaining database integrity, controlling access to the database system, and monitoring and optimizing database performance. DBAs coordinate with the developers and the users on database design and ensure the database is available and secured.
Database developers are the software professionals who develop database applications that extend the functionality of the database system. These applications can be standalone client/server or Web-based applications, report generating programs, and a variety of business applications. Database developers work with the users to determine the system requirements and design the database schemas and user interfaces. The developers work with the DBAs to create the schemas in the database.
1.5 Database Management Systems
10
bank database stores customer account information. Two users may access the same account at the same time to withdraw money from two different ATM machines. Without concurrency control, both customers may be able to withdraw all money from the account. To avoid problems like this, DBMS employs the mechanism to manage concurrent transactions.
It is possible that a transaction failure may occur before the transaction is completed. This would cause data corruption. To avoid it, it is the responsibility of the DBMS to recover the data and restore the database to the state prior to the execution of the transaction. DBMS usually employs the mechanism to log database updates and keep the copy of both old value and new value in a log file. In the event of a transaction failure, DBMS can restore the data using the log file. You will learn currency control and recovery in Chapter 6, “Transaction and Concurrency.”
1.5.4 Maintaining Security
A database is shared by many users, but not all the users can access all the information in the database and perform all types of operations in the database. For example, the students can query the Course table, but cannot update the Course table. The DBA can use the database language to grant or revoke privileges for accessing data and for performing certain operations. The DBMS checks the privileges to enforce the security. Typically, DBA creates accounts for users. The privileges and restrictions are associated with the accounts. You will learn maintaining database securities in Chapter 7, “Security.”
1.6 History of Database Systems
Prior to database systems, data were stored using file systems. File systems are inflexible. There are many drawbacks associated with the file systems. To develop applications using file-based systems, you have to write the code to deal with file structures at the operating systems level.
The first significant development in the database system dates back to early 1960s with the success of the Integrated Data Store (IDS) developed by a group led by Charles Bachman at General Electric. IDS was the major drive for the development of the first standard data model known as networked DBMS by the Conference on Data Systems Languages (CODASYL). In the middle 1960s, IBM developed a product called Information Management System (IMS) that uses a hierarchical data model to describe data and their relationship.
11
The relational data model was proposed by E. F. Codd in a 1970 research paper, “A Relational Model of Data for Large Shared Data Banks.” Codd’s research led to the development of the relational database systems. The experimental relational database System R developed at IBM’s San Jose Research Lab in California demonstrated that a relational database system has the simplicity and flexibility for supporting database applications without sacrificing performance. The Sequel language used in System R is the basis for the SQL language (still pronounced “sequel”). In 1979, Relational Software, Inc. (now Oracle Corporation) introduced the first commercially implementation of SQL. The standardization of the SQL language by American National Standards Institute (ANSI) and International Standards Organization (ISO) in 1986 boosts the success of the relational database systems.
Today relational database systems dominate the database market. The trend will continue as companies migrate their legacy systems into Web-friendly relational database systems. There are hundreds of relational database management systems. They share the common SQL language, but not all the DBMSs support all the features in SQL. For example, MySQL does not support views. Some systems have their own extensions to SQL. For example, MySQL, Oracle and Access have their own proprietary functions. This book introduces standard SQL as well as SQL extensions in MySQL Oracle, and Access.
1.7 SQL
To access or write applications for database systems, you need to use the Structured Query Language (SQL). SQL is the universal language for accessing relational database systems. Application programs may allow users to access database without directly using SQL, but these applications themselves must use SQL to access the database.
You have used SQL to define database schemas. You can also use SQL to query and modify database. This section gives some examples of simple SQL statements.
To drop a table, use the drop table statement. For example, the following statement drops the Department table. Be careful, this command drops the table and its contents from the database.
drop table Department;
To see the definition of the table, use the describe
13
to enable companies and their partners to share data. The personal computers and workstations brought graphical user interfaces to the clients. The client/server database systems became a popular solution for database applications. Clients connect to a DBMS through the network. The database server is at the back end to process data and the client is at the front end to present the data and interact with the user. The clients were typically developed using Microsoft Visual Basic, Borland Delphi, and other development tools provided by database vendors, such as PowerBuilder by Sybase and Oracle Developer 2000 by Oracle. These are excellent rapid application development tools, but the programs developed suffer three problems. First, they can only run on certain platforms. Second, they have to be installed and updated on the clients. Third, they cannot run from a Web browser. Java came to the rescue. Java programs are platform independent so that they can run on any platform with a Java virtual machine.
Java is a full-featured, general-purpose, object-oriented programming language that is capable of developing robust mission-critical systems. Java can be used to develop standalone applications, applets, servlets and JSP. Java applications are like a traditional database client developed using MS Visual Basic. Java applets are special type of Java programs that can run from a Web browser. All these programs interact with the databases through JDBC, which is a Java API that provides a uniform interface for accessing and manipulating relational databases. You will learn how to develop database applications and applets using JDBC in Chapter 32, “Java Database Programming,” in the text.
Java servlets are the Java programs that run from the Web server to generate dynamic Web contents. Web browsers are universal client to interact with databases. Data is stored in databases. Servlets can generate Web pages that contain current information from the database. Servlets can also be used to update information in the database. Servlets can connect to any relational database via JDBC. For example, you can develop an HTML form to accept student registration information. Upon clicking the Submit button in the form, the servlet is invoked to store the registration information to the database. You will learn to develop dynamic Web pages using Java servlets in Chapter 10, “Servlets.” Using servlets, you have to embed HTML tags and text inside the Java source code. This makes the code difficult to maintain. Java ServerPages can be used to separate HTML tags and text from the Java code. JSP enables you to write regular static HTML code in the normal way and embed Java code to produce dynamic contents. JSP will be introduced in Chapter 11,
14
“Java ServerPages.”
Using Java, you can not only develop programs that access relational databases, but also write functions, procedures and triggers in the database server such as in Oracle. Oracle has a built-in Java virtual machine that is capable of executing Java programs inside the database server. Java Stored Procedures are introduced in Appendix G, “Java Stored Procedures.”
1.9 Getting Started with Database Systems
To develop database applications, you need to use a database server. There are more than one hundred DBMSs in the market. You can use any relational database like MySQL, Microsoft SQL server, Microsoft Access, IBM DB2, Sybase, Borland InterBase, and Oracle. This book chooses MySQL, Oracle, and Access for the reasons stated in the preface. It is important to emphasize that the database design and development concepts are universal and applicable to all database systems and the SQL examples in the book can run on all relational database systems provided that the DBMS supports SQL2.
1.9.1 Using MySQL
MySQL is a popular database with more than 4 million users. It is one of the fastest relational databases in the market. Many companies are using it to support their websites, data warehouses, and business applications. MySQL was developed by a Swedish company named MySQL AB. The product is distributed under GNU General Public License (GPL). You can download it free from www.mysql.com. MySQL runs on Windows, Linux and Solaris. It can support multiple users concurrently on the network. Students can access a MySQL database server standalone on their own computer or from the network. The installation is simple and straightforward. For help on installation, please see www.cs.armstrong.edu/liang/dbbook.html. This book demonstrates using MySQL from the Windows operating system.
Assume that you have installed MySQL with the default configuration; you can access MySQL from the DOS command prompt using the command mysql from the c:\mysql\bin directory, as shown in Figure 1.6.
16
every MySQL database, let us use it to demonstrate SQL commands. As shown in Figure 1.8, the test database is selected.
Figure 1.
The use test command selects the test database.
Enter the following SQL statements from the MySQL command prompt, as shown in Figure 1.9. The create table statement creates a table named State. The insert statement inserts the values into the table. The select statement displays the contents from the table.
create table State( name varchar(15) not null, capital varchar(25), population integer);
insert into State values ('Georgia', 'Atlanta', 8383915); insert into State values ('New York', 'Albany', 19011378);
select * from State;
Figure 1.
The execution result of the SQL statements is displayed in the MySQL command tool.
If you have typing errors, you have to retype the whole command. To avoid retyping the whole command, you can save the command in a file, and then run the command from the file. To do so, create a text file, e.g., named test.sql,
17
which contains the commands. You can create the text file using any text editor, e.g., NotePad, as shown in Figure 1.10. To comment a line, precede with two dashes. You can now run the script file by typing source test.sql from the SQL command prompt, as shown in Figure 1.11.
Figure 1.
You can use Notepad to create a text file for SQL commands.
Figure 1.
You can run the SQL commands in a script file from MySQL.
1.9.2 Using Oracle
Oracle 10g Enterprise runs on Windows 2000/NT/XP, Linux and Solaris. It can support multiple users concurrently on the network. Instructors can set up an Oracle 10g database on a server and let students access it from a Web browser so students are not required to install any Oracle software.
NOTE: Students may choose to install Oracle 10g on their personal computer. Oracle 10g can be downloaded free from
19
Figure 1.
You can enter SQL statements, save a SQL script, and load SQL script from iSQL*Plus.
Enter the following SQL statements in the Enter Statement text box and click the Execute button. The execution result is displayed on the work screen, as shown in Figure 1.14.
create table State( name varchar(15) not null, capital varchar(25), population integer);
insert into State values ('Georgia', 'Atlanta', 8383915); insert into State values ('New York', 'Albany', 19011378);
select * from State;
commit;
The commit statement ends the current transaction and makes permanent all changes performed in the transaction. Transactions will be covered in Chapter 6.
20
Figure 1.
The execution result of the SQL statements is displayed in the work screen.
You can choose the Output type to display the result in the work screen, file, or a separate window. You can save the statements in a script file by clicking the Save Script button. You can load the script file from the local client by clicking the Browse button.
1.9.2.1 Accessing Oracle from Command Window (Optional)
Another way to access Oracle is through SQLPlus. You have to first install SQLPlus on your computer. If Oracle database is also installed on your machine, you can access it by typing sqlplus scott/tiger from the command window. If Oracle database is on a different machine, you need to create an Oracle network service name for the remote database. For the information on Oracle network service name, please see www.prenhall.edu/liang/db.html. Figure 1.
