'Often, the best way to learn something is hands-on. To best facilitate this, we will use a scaled-down set of data that mirrors the type of data used in the real world.
This book focuses on two objectives:
To prepare you for the 11g SQL Fundamentals I exam (Oracle exam #1Z0-051).
To present the knowledge needed for the exam in such a way that you can use it in a real-world setting.
To that end, rather than using the default tables included in Oracle, we will be working with simulated real-world data. The database we will use throughout this book is for the fictional company, Companylink. Although most people are aware of the impact of social networking in our private lives, companies are realizing the importance of using it in their industries as well. Companylink is a business that focuses on social networking in the corporate setting. The data model that we will use is a small but realistic set of working data that could support a social networking website. The following tables are included in the Companylink database, which can be downloaded from Packt support site as well as comments about the business rules that constrain them:
Employee: Information about employees that use the Companylink site.
Address: The street address information.
Branch: The corporate branch to which each employee belongs. Each employee belongs to one branch.
Division: It is the corporate division to which each branch belongs. Each division is associated with multiple branches.
E-mail: An employee can store multiple e-mail addresses.
Message: Our fictional Companylink social networking site allows you to send messages to fellow employees. That information is stored here.
Website: Companylink allows users to create their own personal web pages. The URL of these pages is contained in this table.
Blog: In addition to a website, users can optionally create their own blogs. This information is stored in the
Blogtable.Project: Each employee is assigned to a single primary project, which is contained here.
Award: Employees can win corporate awards. The list of possible awards is stored here. Employees can win more than one award.
Employee_award: This table is used to relate employees with their awards. Since multiple employees can win the same award and multiple awards can be won by the same employee, this creates a many-to-many table relationship, which, in the relational paradigm, must be avoided. The
employee_awardtable divides this many-to-many relationship into two distinct one-to-many relationships.
To create our database, we need to run the downloaded Windows command file. Simply unzip the companylink.zip file into a directory and double-click on the companylink_db.cmd file. The execution of the file will do the following:
If you wish, the log files can be used to verify successful execution of the script. The command file is completely reusable, which is to say that if you break any of the tables or data, you only need to disconnect from the database and double-click the command file again. It will drop the existing data and rebuild the tables from scratch. When you do this, keep in mind that any data you add yourself will be deleted as well. Throughout the book, we will continually be writing SQL statements that access these tables and will even add new ones.
The creation of these tables requires a working installation of the Oracle database software on a machine to which you have access. Fortunately, the Oracle software can be downloaded from http://www.oracle.com/technology. There is no purchased license required if you use the software for your own learning purposes.
Tip
SQL in the real world
When you're starting out with SQL and Oracle, it's important to get hands-on. Although Oracle makes its software available at no charge for personal use, many aspiring DBAs are hesitant to install it on their personal computers. By using free desktop virtualization software, such as Virtualbox, you can create a virtual machine on your home computer that can be used as your self-contained database server. Whenever you want to work with Oracle, simply start your virtual machine. Whenever you finish, shutdown the virtual machine, and all the resources it used will be released. Virtualization can be a useful solution to isolate your Oracle work from your home use without buying another computer.