Example: Dropping a View

Figure 10-4 Dropping a View

Appendix A Sample Database Structures A.1 Sample Databases

In this course, we use two sample databases:

(i) HR database (provided by Oracle) and

(ii) JustLeeBooks database (provided by Casteel).

The lab materials are based on the HR database, and the lab exercises are based on the JustLeeBooks database.

A.2 HR (Human Resources) Database

The Oracle database sample schemas portray a sample company. The HR database tracks information about the employees and facilities. In the Human Resource (HR) records, each employee has an identification number, email address, job identification code, salary, and manager. Some employees earn commissions in addition to their salary.

The company also tracks information about jobs within the organisation. Each job has an identification code, job title, and a minimum and maximum salary range. Some employees have been with the company for a long time and have held different positions within the company. When an employee resigns, the duration the employee was working for, the job identification number, and the department are recorded.

The sample company is regionally diverse, so it tracks the locations of its warehouses and departments. Each employee is assigned to a department, and each department is identified either by a unique department number or a short name. Each department is associated with one location, and each location has a full address that includes the street name, postal code, city, state or province, and the country code.

In places where the departments and warehouses are located, the company records details such as the country name, currency symbol, currency name, and the region where the country is located geographically.

A.3 The HR Entity Relationship Diagram

Figure A-1 ER Diagram for HR Database

A.4 The Human Resources (HR) Table Descriptions A.4.1 countries Table

A.4.2 departments Table

A.4.3 employees Table

A.4.4 job Table

A.4.5 job_history Table

A.4.6 locations Table

A.4.7 regions Table

A.5 JustLeeBooks Database

This is an Oracle sample schemas for Just Lee book store. This database tracks information about the customers, orders and promotions. The database structure of JustLeeBooks is shown below (Figure A-2 ), and you can find the table descriptions of JustLeeBooks database in Casteel, Appendix A (pp. 511-517).

Figure A-2 JustLeeBooks ER Diagram

Appendix B Instructions to connect to a server database in Oracle SQL Developer

1. Select Oracle SQL Developer from the menu.

2. Your Oracle SQL Developer screen should look similar to Figure B-1:

Figure B-1 Select Configure File Type Associations

3. Select all Configure File Types associate with SQL Developer as shown in Figure B-1, and click the OK button.

4. To connect to a database, you need to click the ‘+’ in Connections menu. A form, similar to Figure B-2, will prompt you to enter fields to connect to a server database:

5. The fields you have to enter in the form shown in Figure B-2 are as follows:

Connection Name: HR

Username: z (your student number)

Password: (your password – note the password is

case sensitive)

Save Password: If you are running on your own laptop or other devices, you might want to click this box so you do not have to enter your password every time you log in.

Hostname: sage.business.unsw.edu.au

Port: 1521

SID: orcl01 (note: the fourth character is ‘l’ for ‘Larry’ not ‘one’)

Once completed, your form should look similar to Figure B-3 (note: substitute the Username field with your student number):

Figure B-3 Database Connection Details

To test the connection, click the Test button as shown in Figure B-3. You will see an error if the connection is unsuccessful. Otherwise, click the Connect button to connect to the server database.

6. Once connected, you will see two sets of tables as shown in Figure B-4: one set of tables is used in the Oracle Manual, and the other set is used in your lab exercises.

Figure B-4 Sample Tables

7. You need to format the dates to the DD-MON-RR. To change the date format, you need to go the option: Tools > Preferences > Database > NLS, and change the format to DD-MON-RR as shown:

Appendix C Instructions to connect to Oracle SQL Developer Data Modeler

Oracle SQL Developer Data Modeler is a data modeling and database design tool. For instance, you can use the tool to design an Entity Relationship Diagrams (ERD). You can download the software free from the Oracle website.

You can create a database from scratch using Data Modeler. Alternatively, you can create an ERD by importing tables of a database you have already created. We will now demonstrate how to import tables from an existing database to Data Modeler.

First, you have to select the Data Dictionary (or Ctrl-Shift-B) option from the File menu as shown in Figure C-1:

Figure C-1 Select Option to import a Data Dictionary

You have to establish a connection to an existing database by following four steps as shown in Figure C-2:

Figure C-2 Connect to an existing database

Next, you have to choose a database and you might have to enter your username and password as shown in Figure C-3. In this case, we select the HR database:

Next, you have to select a schema/database – either student or group account - you want to use in your data modelling (Figure C-4):

Figure C-4 Select a Schema

Once you have connected to the schema, you then select the tables you want to model.

As an example, the tables we selected as shown in Figure C-5 are countries, departments, employees, jobs, job_history and locations:

Figure C-5 Select Six Tables

A summary of six tables imported is shown in Figure C-6:

Figure C-6 Summary of Number of Imported Tables

Once all the tables are imported successfully, a log similar to Figure C-7 appears, and clicks on the close button to complete the importing task:

Figure C-7 A Successful Log

Data Modeler will automatically attempt to create an ERD based on the tables and constraints from the schema/database. The ERD shown in Figure C-8 is created based on the selected six tables:

Figure C-8 ERD for the Selected Tables