INTRO PROGRAMMING PRINCIPLES
INTRO PROGRAMMING PRINCIPLES CSCI 1301
Popular in Course
Popular in ComputerScienence
This 27 page Class Notes was uploaded by Freida Schaefer DDS on Saturday October 3, 2015. The Class Notes belongs to CSCI 1301 at Armstrong Atlantic State University taught by Y. Liang in Fall. Since its upload, it has received 26 views. For similar materials see /class/217868/csci-1301-armstrong-atlantic-state-university in ComputerScienence at Armstrong Atlantic State University.
Reviews for INTRO PROGRAMMING PRINCIPLES
Report this Material
What is Karma?
Karma is the currency of StudySoup.
Date Created: 10/03/15
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 Copyright Y Daniel Liang 2005 CHAPTER 1 Introduction to Database Systems Objectives 0 To know What a database system is 0 To understand database schemas internal schemas logical schemas and external schemas and their relationships 0 To understand logical data independence and physical data independence 0 To become familiar with the roles of database administrators database application developers and applications users 0 To become familiar with the functions of database management systems 0 To use simple SQL commands and execute them from MySQL command prompt from Oracle SQLPLUS and iSQLPlus and from Access ll lntroduction 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 Copyright Y Daniel Liang 2005 introduces how database systems work and sets the tone for the book 12 What is a Database System A database system consists of a database the software that stores and manages data in the database and the application programs that present data and enable the user to interact with the database system as shown in Figure 11 Application Users Application Programs Database Management System DBMS database System Users Figure 11 A database system consists of data DBMS and application programs Literally a database is a repository of data that forms information Strictly speaking information and data are two different terms Information is an interpretation of data and data is stored in the database represented as numbers characters texts images etc Nevertheless the distinction between them can be ignored most of the time so a database system is also known as an information system The software that stores and manages data is known as database management system or DBMS DBMS is usually provided by database vendors When you purchase a database system from a software vendor such as Oracle IBM Microsoft and Sybase you actually purchase the DBMS from the vendor The database management systems are designed for use by the professional programmers and they are not suitable for the ordinary customers to use The application programs are built on the DBMS for the customers to access and update the database The application programs can be viewed as the interfaces between the database system and its users The application programs may be standalone GUI applications or Web applications The application programs may access several different database systems in the Copyright Y Daniel Liang 2005 network as shown in Figure l2 Application Users Application Programs i i Database Management System I Database Management System 39 39 39 database An application program may access multiple database systems Figure 12 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 l3 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 threeeschema architecture The relationship among these schemas is shown in Figure l3 Copyright Y Daniel Liang 2005 External Schema W F ExtemalLogical Mapping F F F Logical Schema 9 Disk LogicalInternal Mapping Intemal Schema Figure 13 Database systems define data schemas at three levels 131 Logical Schemas Consider the Course table as shown in Figure 14 The logical schema of the Course table is shown in Figure 15 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 subjectld 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 l Course Table courseld subjectld courseNumber title numofcredits 11111 CSCI 1301 Introduction to Java I 4 11112 CSCI 1302 Introduction to Java 11 3 11113 CSCI 3720 Datab e Syst 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 14 Copyright Y Daniel Liang 2005 A course is described by course ID subject ID title and credit hours Table Name Column Name Column Type Course Schema Coursecourse d char5 subjectId char4 number int title varchar5 0 numOfCredits int Constraint numOfCredits gt 1 Figure 15 The schema of the Course table defines the table name column names and types and column constraints The schema is stored in the database Who defines the schema and how the schema is defined Usually the designer of the database defines the schema using the data definition language The DBMS system processes the data definition language and stores schema into the database For example the Course table can be defined as follows create table Course courseld char5 subjectld char4 courseNumber int title varchar50 numOfC redits int NOTE The table definition is descriptive and easy to read However you don t need to know the complete language syntax for defining database schemas at this time The syntax for creating tables will be introduced in Chapter 4 SQL NOTE This book adopts the following naming conventions The tables are named in the same way as Java classes and the attributes are named in the same was as Java variables The SQL keywords are named in the same way as Java keywords 132 Internal Schemas The preceding statement creates a table It defines table columns types and constraints but it does not specify how a table is stored in the database The internal schema can be used to specify a desired data structure for organizing the data in the table If no internal schema is specified Copyright Y Daniel Liang 2005 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 F Hr Tnd 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 133 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 subjectld 39MATH39 NOTE MySQL and Access currently don t support views 131 kbppings 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 7 externallogical mapping and logicalinternal mapping Externallogical mapping handles the correspondents between the external schemas and logical schemas and the logicalinternal mapping handles the correspondents between the logical schema and internal schema Mappings are handled by the DBMS and they are transparent to the users 132 Data Independence Copyright Y Daniel Liang 2005 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 l4 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 DEA 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 clientserver 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 l5 Database Management Systems Copyright Y Daniel Liang 2005 A database management system DBMS is the software that handles all accesses and operations to the database It provides an interface for database administrators to manage the database and for application programs to access the database This section discusses its major functions 151 Supporting Database Languages Every DBMS supports the database language for accessing the database The database language consists of three sublanguages data definition language DDL data manipulation language DRE and data control language DCL Data definition language is for defining database schemas data manipulation language is for querying and updating databases and data control language is for managing transactions and maintaining securities The statements may be issued interactively or embedded in a programming language such as Java The DBMS compiles and executes the statements The DBMS usually provides software that enables the user to enter commands interactively For example you can use MySQL s command line tool and Oracle s SQLPlus or iSQLPlus to enter SQL commands to access and manipulate MySQL and Oracle databases In Chapter 7 JDBC you will learn to develop an interactive SQL interface using Java 152 Implementing Database Schemas Database administrators define the database schemas using the commands in the data definition language The DBMS interprets the commands and implements the schemas in the database Specifically the DBMS supports the following functions 0 Mappings between the external schema and the logical schema and mappings between the logical schema and internal schema 0 Enforcing the constraints 0 Maintaining data dictionary The schemas itself are stored as data in the database This data is known as the database meta data 153 anaging Transactions Since a database is often accessed by multiple users simultaneously it is important to control concurrent accesses to avoid database inconsistency For example a Copyright Y Daniel Liang 2005 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 154 khintaining 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 l6 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 l960s 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 l960s IBM developed a product called Information Management System IMS that uses a hierarchical data model to describe data and their relationship Copyright Y Daniel Liang 2005 The relational data model was proposed by E F Codd in a l970 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 seguel In I979 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 l986 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 SOL 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 SOL as well as SOL extensions in MySQL Oracle and Access l7 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 SOL 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 Copyright Y Daniel Liang 2005 command For example the following statement shows the schema of the Department table describe Oracle and MySQL only To insert a record into a table use the insert statement For example the following statement inserts a record 39SC39 39Science39 3999900llll39 into the College table insert into College values 39SC39 39Science39 39999001lll39 To update a record use the update statement For example the following statement changes the deanId to 3988800llll39 for the Science college update College set deanld 3988800llll39 where name 39Science39 To delete a record use the delete statement For example the following statement deletes the Science college from the College table To query the database use the select statement For example the following statement selects the departments in the Science college select name from where ll 7 F d 39SC39 To find how many departments are in the Science college you can use the following query select count from where ll 7 F d 39SC39 You will learn to write SQL statement in Chapters 4 and 5 SQL is a noniprocedural language As shown in the preceding statements a SQL statement tells the database what to do but not how to do The DBMS translates SQL into procedures using the operators similar to relational algebra Furthermore the DBMS can optimize queries and choose the most efficient strategy to execute gueries All of these details are performed behind the scenes to make SQL simple and easy to use l8 Database Applications Using Java Before l970s database systems were mainly developed for use within organizations The systems were primarily used for storing data and for performing tasks such as printing bank statements telephone bills and student transcripts The users usually access the database through text based terminals These systems often don t communicate with each other With the advancement of computer network technologies the networked database systems were developed Copyright Y Daniel Liang 2005 to enable companies and their partners to share data The personal computers and workstations brought graphical user interfaces to the clients The clientserver 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 l0 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 ll Copyright Y Daniel Liang 2005 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 l9 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 lnterBase 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 SQLZ 191 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 wwwmysglcom 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 wwwcsarmstrongeduliangdbbookhtml 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 mysgl from the cmysglbin directory as shown in Figure l6 Copyright Y Daniel Liang 2005 Inlelcome to the MySOL monitor Commands end with 1 or 9 Vour HgSaL connection id is 3 to server version lle12nt Type 39help39 or 39h39 for help Type 39c39 to clear the buffer msl v Hq Figure 16 You can access a MySQL database server from the command window NOTE On Windows your MySQL database server starts every time your computer starts You can stop it by typing the command net stop mysgl and restart it by typing the command net start mysgl By default the server contains two databases named mysgl and test You can see these two databases displayed in Figure 17 using the command show databases lam u 2 rows in set 000 sec mslgt Eq I gt igure 17 The show databases command displays all available databases in the MySQL database server The mysgl database contains the tables that store the information about the server and its users This database is intended for the server administrator to use For example the administrator can use it to create users and grant or revoke user privileges Since you are the owner of the server installed on your system you have full access to the mysgl database However you should not create user tables in the mysgl database You can use the test database to store data or create new databases You can also create a new database using the command create database databasename or drop an existing database using the command drop database databasename To select a database for use type the use databasename command Since the test database is created by default in Copyright Y Daniel Liang 2005 every MySQL database let us use it to demonstrate SQL commands As shown in Figure 18 the test database is selected amiPmnl Figure 18 The use test command selects the test database Enter the following SQL statements from the MySQL command prompt as shown in Figure 19 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 varchar15 not null capital varcharlt25 population integer insert into State Values 39Georgia39 39Atlanta39 8383915 insert into State Values 39New York39 39Albany39 19011378 select from State n mgsq1gt create table state 2 39 gt name ar h 15 not null 5 gt population integer auerg 0K 0 rows affected 001 sec mysqlgt insert into state ua1ues Georgia39 39Rtlanta39 8383915 auerg 0K 1 row affected 000 sec mysqlgt insert into state ua1ues 39Neu Vork39 39nibang39 19011313 auerg 0K 1 row affected 000 sec mysqlgt select x From state 1 name 1 capital 1 population 1 Georgia 1 ntianta 1 838 15 1 New Vork 1 nibang 1 19011313 2 rows in set 001 sec m sq1gt v 4 l gt igure 19 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 eg named testsgl Copyright Y Daniel Liang 2005 which contains the commands You can create the text file using any text editor eg NotePad as shown in Figure 110 To comment a line precede with two dashes You can now run the script file by typing source testsgl from the SQL command prompt as shown in Figure 111 a Testesqerolepad T i 1 3 Eiie Format heip 1mm W SQL commands in a file Create table State m m 5 n AV 1 o n 3 capital varchar 25 population integer insert into State values Geor gia 39Atlanta 8383915 insert into State values New Vork 39Albany 19011378 select 4 From State J igure 110 You can use Notepad to create a text file for SQL commands aldg mysql drop table st te A auer 0K 0 rows affected 000 sec mysqlgt source cdbbookTestsq1 auerg 0K 0 rows affected 000 sec auerg 0K 1 row affected 000 sec auerg 0K 1 row affected 000 sec 1 name 1 capital 1 population 1 Georgia 1 nt1anta 1 8383915 1 New Vork 1 Rlbang 1 19011373 2 rows in set 000 sec ppeq1gt igure 111 You can run the SQL commands in a script file from MySQL 192 Using Oracle Oracle 10g Enterprise runs on Windows ZOOONTXP 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 log on their personal computer Oracle 109 can be downloaded free from Copyright Y Daniel Liang 2005 htt wwworaclecomt hn l i softwareproduc tsdatabaseoraclelOgindexht 1 There are many ways to access Oracle The easiest is to use isOmPlus which enables you to access Oracle from a Web o r It re r no installatio b the use pose an Oracle 10g Enterprise database has been installed on th ost iang arm ong ed wit P server enabled the user UR httpli no rm tr m du5560isqlplus as shown in Figure 112 Ede gm Mew Favorites Tools help esack v o v E E quotSearch Favomas AM A Langarmsrrongmu sssulsqlplus v 50 wk 7 9 anquot 09 a mamas reqmred eld a Username scan a Passwurd lum cunnecudenmer 3 tom lt Figure 112 You can start 15QLPlus from a Web browser Enter the username eg scott and password eg tiger and click Log In to log into the database The 15QLPlus user interface is shown in Figure l 13 18 Copyright YV Daniel Liang 2005 s ale gut yen Favorites 1003 help 539 eBaCkY J v m g 0 DSearcn Favomgs 33 2v quot7 a v h pLangarmslrongedu25560sqpusworkspacemxe Go 39 Y v 2 er 39739 39QH EMa v Myvanoo gtgt 3395 a Z 39 L L J m m Hlslu Connected as sco r rarcl Workspace Enter SOL PLSQL and SQL Pms statements clear a cable Skew A rener15nmnull eepuel varchar25 pupulenon unteger Insen nm State values Genrguex Auantex 8383915 Insert nm State values 39New York Albanyg 19011378 Salad frum State 39Execme Luauswm SaveSch cancel Warkspace Hlstum Luguul Preferences Helg munm omemrrg39 we 7 v 4 I gt 0 Internet 21 igure 1 13 You can enter SQL statements save a SQL script and load SQL script from i39SQLPlus 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 114 create table State name varcharus not null capital varchar25r O ulatlon integer lnsert lnto State values 39Georgla39 39Atlanta39 8383915 lnsert lnto State values 39New York39 39Albany39 19D1137E 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 Copyright Y Daniel Liang 2005 QLPIus Rekasel Elll E13 gun yew Favorres 1001s e1p 1quot em 7 J a 5311mm Favonts 5111 i E 39 1 Rama 4 htthhangarmstmngrdu 556015q1p1u5w0rk5pac2u1 Y v Q v g v Emu v Myvahoor Lugum Prererenees Hem H1s mrv W Cmmected as sco r rercl Workspace Emer SQL FUSQL and SQL P1LAS statements C12av create table 5 A caprra1varcnar25 papulatran inleger rnsen mm scare values 39Genrgla 39At1antaquot 3333915 rnsen mm State va1ues 39New Yurk A1Dany 19011373 56151 from Smke Execme Leaeswm saveswm Cance1 Table Created 1 ruw created 1 rew created NAM CAPITAL r POPULATION Geurgla Arrama 3333915 New York A1bany 19011373 lt1 39 f Q 1 1 1 1 1 10 Internet a igure 114 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 1921 Accessing Oracle from Command Window Optional Another way to access Oracle is through SOLtPlus You have to first install SOLtplus on your computer If Oracle database is also installed on your machine you can access it by typing sqlplus scotttiger from the command window If Oracle database is on a i erent machine you create an Oracle network service name for the remote database For the information on Oracle network service name please see wwwprenhalleduliangdbhtml Figure 115 Copyright Y Daniel Liang 2005 shows an example of using SQLPlus to access the oracle database on the host liangarmstrongedu The alias name for the database is liangorcl Command From 7 anngd SOLxPlus Release 101020 39 Production on Mon Rug 22 11H533 2005 Copyright c 1932 zeeu Oracle 911 rights reserued Connected to Oracle Database 109 Enterprise Edition Release 101020 39 Production with the Partitioning OLRP and Data Mining options SOL select X From State NRME CRPITRL POPULRTION Georgia ntianta 8383915 New Vork nibang 19011373 SOL v 4 4 Figure 115 You can start SQLPlus from the DOS command prompt 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 type edit filenamesq1 from the SQL command prompt as shown in Figure 116 This command invokes Windows Notepad as shown in Figure 117 Type SQL commands in the Notepad and save and exit Notepad To comment a line precede with two dashes You can now run the script file by typing filenamesgl as shown in Figure 118 SOL ed 1 mgSuLCommandsql 4 gt Figure 116 You can use the edit command to launch an editor Copyright Y Daniel Liang 2005 E mySQLCommandsql Notepad He Edit Format jew Help drop tab1e state create tab1 e State name varchar1s not nu11 capital varcharlt25 population integer 39Atlanta39 8383915 39A1bany39 19011378 insert into state va1ues 39Geor gia39 39nsert 39 State va1ues 39New York mto se1ect 139 from State mwk 1 quotigure 117 You can save the SQL commands using Notepad D 1 SOL mgSOLComman A Table dropped Table created 1 row created 1 row created NRME CRPITRL POPULRTION Georgia Rtlanta 8383815 New Vork Rlbang 19011378 Commt complete SOL v 4 gt quotigure 118 You can run the SQL commands in a script file from Oracle As shown in Figure 118 an error is generated when the create table statement is executed because the table already exists The insert statements cause errors because they violate the primary key constraint 1 9 3 Using Access Access is a ubiquitous database running on Microsoft Windows It is usually used by a single user Access provides an intuitive graphical user interface that enables you to create tables and insert update and delete data from the windows without using the SQL commands However to develop database applications using Java you still have to learn and use SQL This chapter demonstrates using SQL with Access Copyright Y Daniel Liang 2005 Here are the steps to create a database and execute SQL statements from Microsoft Access 1 Launch Microsoft Access database as shown in Figure 119 Check Blank Access database in the Create a new database section Click OK to display the File New Database dialog box as shown in Figure 120 Create and select the directory dbbook in the Save in field and type DBBook MDBmdb in the File name field Click Create to create a new database The DBBookiMDB database is created as shown in Figure 121 c vambwangammayoa c vadm angmkoajwoa c dbbunkmaauumwoa Numwm Sample Database Figure l 19 You can create a new database or use an existing database Copyright Y Daniel Liang 2005 File New Database 4 7 ll Sav in El gbbnnk v v E i a X 1 v Tunis panama W V severasype MirrusD ArressDa abasesC mdb v Carmel Figure 120 The Pile New Database dialog box enables you to specify a new database 9 Miaosoft Amegs D X le Edit llew insert I001 window Help I a DBBooLMDB Database Ewen Mueskgn twew X mdy Figure 121 The DBBook4MDB database is created in cdbbookDBBookMDBmdb NOTE An Access database is contained in a single file with a mdb extension If you have an existing database you can open it without having to create a new database 2 Choose Queries in the Objects column and click Create query in Design View see Figure 121 to display the Query Design View see Figure 122 Click Close to close the Show Table dialog box Copyright Y Daniel Liang 2005 39 3211 Hale Edit mew Insert Query 1001 wruaw elp J I DWH QET X mglt h l vl lglu g454 519331 ka lnlxl Quervl Tables Qusrlss lamh Figure 1 22 The Show Table dialog box must be closed to display the SQL View 3 Select SQL View in the SQL combo box see Figure 123 to display the SQL command window as shown in Figure 124 4 Type in the statement for creating the State table see Figure 2212 and click the Run toolbar button to execute the statement me Edy mew Insert Query 190 wndow Help J luleav leavlnl u I2 A 4am t 7 A ieswgn mew DE aha 13 Damweetv ew m Query Readv ll 2 Figure 1 23 The SQL command window can be displayed by choosing the SQL View command Copyright Y Daniel Liang 2005 We Eat mew lmert Query Tools mmndow Help gym eevlnh h a DBBonkMDBJESaM 39 R Egpen geesgn EJNew x ObJecB Create querv ln Deslgn mew Create querv by usv39vg leard wave We Wab name varrtar15nntnuH tapnal varmar25 papulauan meger Raa Figure 124 You can type the SQL command in the Query window and execute it 5 Type the following SQL statements one at a time and execute each statement to insert data into the State table and select them from the table The selection result is shown in Figure 125 insert lnto state values 39Georgla39 39Atlanta39 8383915 lnsert lnto state values 39New York 39Albany39 19D1137E select from state We Eat mew Insert Fgrmat Records ools Mndow Help ngm emm gs ream le s EW EVMM K Jam Create query ln Desgn mew Create query by usv39vg leard rv Ima popunauon 383915 Rama 4 4 1 gt w m an 5 Ma W i i i i Figure 125 The selection result is displayed in a window on Access NOTE Access cannot execute SQL commands from a script file You have to type and execute one SQL command at a time from the query window l 26 Copyright Ya Daniel Llang 2005 Chapter Summary This chapter introduced the concept of database systems You learned database management systems You also learned how to access Oracle database from iSQLPlus and SQLPlus and how to use simple SQL commands to create tables and manipulate data in the tables Review Questions ll What is a database system l2 What is a database schema What are external schema logical schema and internal schema l3 What is logical data independence What is physical data independence l4 What are the responsibilities of a DBA l5 What are the functions of a DBMS l6 What is the difference between a procedural language and non procedural language Exercises ll Create a table for Course insert records into the table as shown in Figure l4 27 Copyright Y Daniel Liang 2005