01 laboratory exercise 1 - DESIGN A SIMPLE DATABASE APPLICATION

01 laboratory exercise 1 - DESIGN A SIMPLE DATABASE APPLICATION

• 1.
  F0004 01 Laboratory Exercise1 *Property of STI Page 1 of 11 Laboratory Exercise Design a Simple Database Application Objectives At the end of this exercise, the students are expected to:  Create a database  Create tables  Perform basic data manipulation  Apply basic SQL statements  Computer with Windows XP Service Pack 2 or higher  Software installed on the computer terminal:  Sybase SQL Anywhere 9  Opening documents/reading – i.e. MS Word, Acrobat Reader, etc.  Printer and Paper (as necessary) Activity 1 Course Briefing 1. The students are briefed by the instructor on: a. The computer laboratory policies b. The borrowing procedures (as necessary) c. The general “Do’s and Don’ts” for the Laboratory Course o Do’s:  Students should wear proper uniform.  Students should leave the laboratory room if the class is finished.  Students’ belongings should be placed on the table near the door. No things should be brought with the student near their computer.  Follow the safety precautions inside the laboratory.  Follow “Clean As You Go” before leaving the laboratory. o Don’ts:  Using USB flash drive and any other devices without the permission of the laboratory custodians.  Copying files from the computers in the laboratory.  Changing any settings from the computer they are using.  Eating and drinking inside the laboratory.  Staying inside the laboratory premises without the permission of the custodian. d. The Laboratory Course Grading System Activity 2 Creating a Database 1. Select Start > All Programs > SQL Anywhere 9 > Sybase Central. 2. Do any of the following: a. From the Sybase Central menu, choose Tools then select Adaptive Server Anywhere 9 as shown in Figure 1.1.
• 2.
  F0004 01 Laboratory Exercise1 *Property of STI Page 2 of 11 Figure 1.1 Accessing ASA in the Tools Menu b. Select Create Database. Figure 1.2 Creating Database c. The Create a Database wizard will appear. Click Next. Figure 1.3 Create a Database Welcome Window d. Choose the Create a database on the computer option as shown in Figure 1.4 and click Next.
• 3.
  F0004 01 Laboratory Exercise1 *Property of STI Page 3 of 11 Figure 1.4 Location of the Database e. Name the database as “addbase”. Click Next. Figure 1.5 File Name of the Main Database f. Leave the Maintain the following transaction log file check box selected as shown in Figure 1.6 and click Next.
• 4.
  F0004 01 Laboratory Exercise1 *Property of STI Page 4 of 11 Figure 1.6 Maintaining Transaction Log File g. You do not need to mirror the transaction log, just click Next. Figure 1.7 Maintaining a Mirror Log File h. Make sure that the Install jConnect meta-information support is selected as shown in Figure 1.8 and then click Next.
• 5.
  F0004 01 Laboratory Exercise1 *Property of STI Page 5 of 11 Figure 1.8 Installing jConnect Meta-information Support i. Select the Create SYSCOLUMNS and SYSINDEXES views check box and click Next. Figure 1.9 Creating SYSCOLUMNS and SYSINDEXES Views j. Choose 2048 bytes for the page size of the database as shown in Figure 1.10 and click Next.
• 6.
  F0004 01 Laboratory Exercise1 *Property of STI Page 6 of 11 Figure 1.10 Page Size of the Database k. Select Use the default collation option and then click Next. Figure 1.11 Collation Sequence of the Database l. Make sure to connect to the created database as shown in Figure 1.12 and click Finish.
• 7.
  F0004 01 Laboratory Exercise1 *Property of STI Page 7 of 11 Figure 1.12 Finalizing the Database m. The database addbase is created and its structure is displayed in the Folders pane as shown in Figure 1.13. Figure 1.13 ADDBASE Database OR a. In the Folders pane, select Adaptive Server Anywhere plug-in. b. Click the Utilities tab in the right pane. You will see a list of tools that you can use in the administration and management of databases. c. Double-click the Create a Database wizard and follow the instructions in the wizard. 3. Name the database as “addbase”.
• 8.
  F0004 01 Laboratory Exercise1 *Property of STI Page 8 of 11 Activity 3 Creating a Table with Sybase Central The following tables form part of the ADDBASE.db: Hotel (hotelNo, hotelName, city) Room (roomNo, hotelNo, type, price) Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) Guest (guestNo, guestName, guestAddress) Where:  Hotel contains hotel details  hotelNo is the primary key  Room contains room details for each hotel  (roomNo, hotelNo) forms the primary key  Booking contains details of bookings  (hotelNo, guestNo, dateFrom) forms the primary key  Guest contains guest details  guestNo is the primary key Please refer to Table 1.1 below for the data type. Table Column Data Type Size Hotel hotelNo integer hotelName varchar 50 city varchar 20 Room roomNo integer hotelNo integer type varchar 10 price double Booking hotelNo integer guestNo integer dateFrom date dateTo date roomNo integer Guest guestNo integer guestName varchar 50 guestAddress varchar 100 Table 1.1 Data Types and Sizes 1. First, connect to addbase database. The database should appear in the Folders pane if you successfully connected to the database. Note: Make sure that you are connected to ADDBASE database. It should appear in the Folders pane. 2. From addbase database, select Tables in the left pane. 3. In the File menu, click New and choose Table or click the New Table icon ( ). The Table Creation wizard appears. 4. Enter the table name (e.g. Hotel). Note: The owner of the table corresponds to the user id that will be used to connect to the database. 5. Click Next. 6. Listed in the window is a dbspace which will be used to store the table, click Next. 7. Leave the primary key constraint option checked, click Next. 8. Set the memory space for each table to Default and click Next. 9. You can include a comment about the table and click Finish. 10. Enter the column name for the primary key.
• 9.
  F0004 01 Laboratory Exercise1 *Property of STI Page 9 of 11 11. In the drop-down list, choose the appropriate data type. Choose integer. 12. Leave the checkbox unchecked under Nulls to disallow null values. 13. To add more columns, from the File menu, select New Column or press <Ctrl+N>. You can also use the New Column icon ( ). Note: Make sure that the specific table (e.g. Hotel) is selected in the Folders pane. 14. Save the table by clicking the Save Table icon ( ) or press <Ctrl+S>. 15. Repeat steps 4 to 14 to create the other tables given. 16. Select Open Interactive SQL from Adaptive Server Anywhere 9 in the Tools menu. 17. Use the INSERT INTO statement to populate the Hotel, Room, Booking and Guest tables. Insert 10 records for each table. In choosing the values for each column in each table, observe the appropriate relational integrity constraints. Example INSERT statement INSERT INTO Hotel(hotelNo,hotelName,city) VALUES(1001,'Renaissance','Makati') Activity 4 Updating the Table 1. Select the Room table. Right-click on it and select View Data Interactive SQL. This will open the Interactive SQL window. 2. Use the UPDATE statement to update the price of all rooms by 5%. Activity 5 Performing SQL Data Manipulation Perform each query and write the results on the space provided. 1. List all hotels. 2. List full details of all hotels.
• 10.
  F0004 01 Laboratory Exercise1 *Property of STI Page 10 of 11 3. List the bookings for which no dateTo has been specified. 4. How many hotels are there? 5. What is the average price of a room? 6. List all single rooms with a price below Php 2000 per night. If necessary, update some records in the Room table to perform this query.
• 11.
  F0004 01 Laboratory Exercise1 *Property of STI Page 11 of 11 7. List the names and cities of all guests. 8. List the price and type of all rooms at the __________ (choose a hotel). 9. List all guests currently staying at the __________ (choose a hotel). 10. List the guest details (guestNo, guestName and guestAddress) of all guests staying at the __________ (choose a hotel).