ADVDB_WK1

Overview of this Lecture / week

As this is the first week we will keep things simple. Most of you will have some experience or knowledge of databases. Databases are a core component of all applications and a key component for storing, managing and analysing data.

Microsoft’s CEO has said he wished Microsoft had developed Databases and be the first to make them commercially successful. Oracle is the company that has achieved this going back to the early 1970’s. Just imagine a piece of software originally developed over 40 years ago is a key component of every application. But it is not the same product. It has had over 40 years of development work to make it the “gold standard” database that everyone else want to compete with.

SQL is a very powerful language and you can do so much more with SQL than you might think. Have you read the SQL Language manual for any Database? If not check out the SQL Language Reference for Oracle Database.

Read this article before class: The Next 50 Years of Databases

Notes

Overview of Databases notes  Click here to download the notes.

L1 - Overview of Advanced Databases Subject

Database Design Notes – Click here to download the notes

Video

Creating a Logical Data Model

Creating a Physical Data Model

130 SQL Statements in 20 Minutes

Lab Exercises – 1 – Create an Oracle Database Environment

You have a few options for creating an Oracle Database Environment.

1. I can give you a login to an Oracle Cloud Database that I have. If you want to have an account on this send me the following information

      •  Your full Name, Student number and course/module details.  I will create the account and email you your login details

2. Create your own Oracle Database using the Oracle Free Tier. This is FREE, as in Free cost. This is a great way to explore the various concepts in your own workspace. To create a Database in Oracle Free Tier follow these instructions.

The user interface is constantly evolving and the screens you will see might be slightly different to what is shown in the above webpages, but all the same details are needed.

3. Create a Docker image with an Oracle Database. Containerization is very popular. If you are familiar with this technology following these instructions to create an Oracle Database Docker Container.

4. Use a pre-built VirtualBox Image. Follow these instructions to install and setup the Oracle Pre-Built Database appliance.

      • Oracle Database pre-built VirtualBox Appliance/VM
      • You will need a PC/Laptop that is 64bit and capable of running Virtualization.
      • You will need to change some of the configuration settings for this machine. Change RAM to 4G, and number of CPUs to 2.

For next week, read/research the following

Lab Exercises – 2 – ER Diagrams refresh

  1. Complete the in-class exercises on paper.
  2. Open Oracle SQL Data Modeler and use this tool to create the data model.  Do this for each in-class exercise.

Oracle SQL Data Modeler is install on all lab PC and is also available in the Virtual Machine (see last week).

You can download and install this software onto your own laptop. You don’t need a database to run this software.

To download SQL Developer go to the Download Page.

Click on the ‘Accept License’ radio button.

Then download the version of Oracle Data Modeler that has the JDK built into it. (this is the first one in the download list).

More details about the is product cane be found on the Data Modeller webpage.

    1. Produce the Logical and Physical data model for each exercise.
    2. Generate the DDL for each data model.
    3. Run the DDL script in your Oracle schema (in the SoC Database Server or in the Database VM).  If using the SoC Database server, get your login and password from the lecturer.
    4. Inspect what was created.

SQL Tutorials and Learning

If you need to refresh SQL, try these tutorials:

SQL and PL/SQL Basics

SQL Zoo

W3School for SQL

Let know if there are other resources you would recommend to other students.

Additional Reading Materials

Gartner – Critical Capabilities for Operational DBMS

SQL and PL/SQL Coding standard

History of Databases Paper
Introduction to Databases – Chris Date
Introduction to Oracle Book Chapter
What is a Database?
DBA 101 – Craig Mullins

16 Parts of the SQL Standard