Docsity
Docsity
Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity

Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips
Guidelines and tips
Sell on Docsity
Sell on Docsity
Docsity AI
Docsity AI
Log inSign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search for your university

Find the specific documents for your university's exams

Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents
download point icon20 Points

For each uploaded document

Answer questions
download point icon5 Points

For each given answer (max 1 per day)

All the ways to get free points
Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

Lecture Notes on MySQL Database - Database System | CPSC 310, Assignments of Deductive Database Systems

Texas A&M University (A&M)Deductive Database Systems

Material Type: Assignment; Class: DATABASE SYSTEMS; Subject: COMPUTER SCIENCE; University: Texas A&M University; Term: Summer 1 2008;

Typology: Assignments

2019/2020

Uploaded on 11/25/2020

koofers-user-dvm-1
koofers-user-dvm-1 🇺🇸

10 documents

1 / 6

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
CPSC 310 HW 1, Summer 2008, RonnieWard
Assigned 5/28/08, DUE in class on 6/4/08
1
The goal of this first homework is to have you claim your MySQL database access using
CSNET, and establish the DERBY open source database management system with your
Eclipse Java development environment. With these you’ll be able to see SQL in action,
and start your understanding of relational data modeling and SQL. Answer specific
questions in the document. Turn in your answers via a text file uploaded to CSNET, and
turn in a hard copy of your answers at class time on the due date. Make sure you include
a signed copy of the Assignment Cover Page posted on the course web site, or your work
will not be graded. ANSWERS in red
1. [This is roughly the same step as Step 1 of Lab1, but there are some significant
differences, which I’ll make clear in class] Make sure your development machine is set
up to run Eclipse, which is a free Java IDE that you can download free from
www.eclipse.org. I’m using version 3.3 of the SDK. Create a project, add a Java ‘Hello
World’ class to it, and run it as a ‘Java application’. You should see ‘Hello World’ appear
in the console window. If this fails, make sure your machine has a recent installation of
the JRE or JDK 5.0, or higher, from http://java.sun.com/javase/downloads/index.jsp.
Check to make sure the installed Java runtime is tied into Eclipse—Under Eclipse, click
Window->Preferences->Java->Installed JRE and you should see it installed. If not, click
the Add button and add it. See screen shot below from my solution.
2. Derby (http://db.apache.org/derby/) is a lightweight, open source, relational database
system that runs on your PC. It has a console for executing SQL commands and script
files (.sql), and a built in JDBC driver to use in executing embedded SQL commands
pf3
pf4
pf5

Partial preview of the text

Download Lecture Notes on MySQL Database - Database System | CPSC 310 and more Assignments Deductive Database Systems in PDF only on Docsity!

Assigned 5/28/08, DUE in class on 6/4/

The goal of this first homework is to have you claim your MySQL database access using CSNET, and establish the DERBY open source database management system with your Eclipse Java development environment. With these you’ll be able to see SQL in action, and start your understanding of relational data modeling and SQL. Answer specific questions in the document. Turn in your answers via a text file uploaded to CSNET, and turn in a hard copy of your answers at class time on the due date. Make sure you include a signed copy of the Assignment Cover Page posted on the course web site, or your work will not be graded. ANSWERS in red

  1. [This is roughly the same step as Step 1 of Lab1, but there are some significant differences, which I’ll make clear in class] Make sure your development machine is set up to run Eclipse, which is a free Java IDE that you can download free from www.eclipse.org. I’m using version 3.3 of the SDK. Create a project, add a Java ‘Hello World’ class to it, and run it as a ‘Java application’. You should see ‘Hello World’ appear in the console window. If this fails, make sure your machine has a recent installation of the JRE or JDK 5.0, or higher, from http://java.sun.com/javase/downloads/index.jsp. Check to make sure the installed Java runtime is tied into Eclipse—Under Eclipse, click Window->Preferences->Java->Installed JRE and you should see it installed. If not, click the Add button and add it. See screen shot below from my solution.
  2. Derby (http://db.apache.org/derby/) is a lightweight, open source, relational database system that runs on your PC. It has a console for executing SQL commands and script files (.sql), and a built in JDBC driver to use in executing embedded SQL commands

Assigned 5/28/08, DUE in class on 6/4/

constructed as Java Strings. Two separate plugins are needed to implement Derby under Eclipse: (http://db.apache.org/derby/releases/release-10.4.1.3.cgi)

a. derby_core_plugin - provides the Derby jar files to other plugins in Eclipse. derby_core_plugin_10.4.1.648739.zip

b. derby_ui_plugin - provides an Apache Derby Nature in Eclipse for easy database application development. derby_ui_plugin_1.1.2.zip

You can read here (http://db.apache.org/derby/integrate/plugin_howto.html ) about installing the plugin(s) on your PC. If you are using a CPSC Lab PC, Derby might already be installed. Turn in nothing for this step.

  1. After you add the Derby plugin(s) to Eclipse, go to the link: http://db.apache.org/derby/integrate/plugin_help/start_toc.html From there, select Adding the Derby nature to a Java project , and follow the steps on that page to enable the Derby features for a new Eclipse project. After that, read how to Start(stop) the Derby network server in a project. Start Derby for your selected project and don’t change the host name, or the port number. Now, select launch ij to create and connect to a Derby database and work the example provided on the page (you can simply copy and paste all of the code to the ij command line from the web page—you might want to maximize the console window since you are not using Java code in the project at this point). Right mouse click the project and select Refresh. What file changes do you observe in the project? Added files myDB, myDB.log, myDB.seg0, myDB.tmp, and we also see the derby.log file. See the screen print below. After that, select Run entire SQL scripts and work the example provided on the web page (you will have to build a SQL script file using Notepad, for example). What happens if you start ij before you start the Derby server? Well, see the second screen below. ij runs, and we are able to connect to the database. We can see the properties of the restaurants table, and its contents since ij runs the Derby server in the same java virtual machine. If we start the server separately, it runs in its own java virtual machine. ij runs in its own virtual machine. Prove this to yourself by looking at the running tasks using the Windows Task Manager. What happens when you type ‘help;’ at the ij prompt? Get a list of commands that ij supports. Is the ‘;’ necessary when you enter an ij command? Yes, ij sits and waits for a “;” before it acts on a command.

Assigned 5/28/08, DUE in class on 6/4/

  1. Follow the instructions for Creating a Java Application on the page http://db.apache.org/derby/integrate/plugin_help/start_toc.html. When you run the code, why does the city name “Berkeley” not ‘line up’ with the other city names listed? It’s a tabbing issue caused by the fact that the other restaurant names are longer than LaVals. Change the application to use the Derby Embedded Driver. What is the difference in the console output? The entry for LaVals in Berkeley with ID 5 appears a second time in the restaurants relation. In other words there is a duplicate tuple in the relation. In reading the web page, how would you characterize the meaning of the word ‘schema’? It appears to refer to be the name of a collection of tables, which we would call the database. APP is the default name, whereas “me” was the default schema name taken from the ‘user’ name for restaurants.
  2. Under ij, issue the commands: CONNECT ‘jdbc:derby:myDB’; SHOW SCHEMAS; SHOW TABLES; SHOW CONNECTIONS; SHOW VIEWS; SHOW INDEXES; to obtain information about the database. Locate each of these commands in the documentation for Derby (http://db.apache.org/derby/manuals/index.html#docs_10.4), and turn in a one-sentence description of their output. From the Derby Tools Manual: ‘show connections;’ shows the connection identifier and the URL of the connection. ‘show schemas;’ shows all the schemas in the current connection. ‘show tables;’ shows the tables in the current connection. ‘show indexes;’ shows all the indexes in the database. ‘show views;’ shows the current views created in the database. Download the PDF versions of the documents for later reference. Fiddle with the Derby commands and table names and answer this question: Is Derby case sensitive? Derby is case sensitive with string constants, and Java style names in JDBC code. In general, it seems to be case insensitive (check the Derby Reference Manual and the Developer Guide).
  3. Go to the HELPDESK in HRBB 2nd^ Floor, and claim a Computer Science account. From an open lab PC, or using your own PC with VPN access to the CS intranet, claim a MySQL database account using the instructions on this page: https://csnet.cs.tamu.edu/helpdesk/docs/view.php?increment=0&doc_id= When you get your first mysql> prompt , enter the command SHOW DATABASES; and turn in the output. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | ward | +--------------------+ 2 rows in set (0.01 sec) Next try the command, SHOW TABLES; Turn in a one-sentence explanation of the output. SHOW TABLES; displays the non-temporary tables in the database, but if no database is selected, an error is produced: ERROR 1046 (3D000): No database selected

Assigned 5/28/08, DUE in class on 6/4/

  1. Ignore the remaining instructions in the helpdesk document (about creating a new database), and enter the command USE name; where name is your last name (a database you should see listed after the SHOW DATABASES; command). Then copy/paste the following commands to mysql> prompts and turn in your output.

create table firsttable (id int primary key, name varchar(25)); insert into firsttable values (10, 'ten'), (20,'twenty'), (30,'thirty'); select * from firsttable; select * from firsttable where id=20; mysql> create table firsttable (id int primary key, name varchar(25)); Query OK, 0 rows affected (0.00 sec)

mysql> insert into firsttable values (10, 'ten'), (20,'twenty'), (30,'thirty'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from firsttable; +----+--------+ | id | name | +----+--------+ | 10 | ten | | 20 | twenty | | 30 | thirty | +----+--------+ 3 rows in set (0.00 sec)

mysql> select * from firsttable where id=20; +----+--------+ | id | name | +----+--------+ | 20 | twenty | +----+--------+ 1 row in set (0.00 sec)

mysql>

  1. Now issue the SHOW TABLES; command to the mysql> prompt and turn in your output. mysql> show tables; +----------------+ | Tables_in_ward | +----------------+ | firsttable | +----------------+ 1 rows in set (0.00 sec) Also, give the command, DESCRIBE firsttable; and turn in the output along with a brief description of it based on what you learned in the first lecture.