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.

Notes

Overview of Databases notes  Click here to download the notes.

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

  1.  Give the lecturer your Name and Student number.  An account will be created on the School of Computing Oracle Database server for you. Login in details will be given next week
  2. Look over the notes for next week.  Do some revision of Database design.
  3. Do some research and reading of some of the topics listed for Assignment A. A lottery will be held next week to randomly allocate the topics to each student.
  4. Check out the SQL Language Reference for Oracle Database.  Check out all the Database Documentation.

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.

Lab – Exercise 3 – Create a Test Lab Environment

  1. Create a Home Database Lab. You can use this Virtual Machine to complete the various tutorials and labs.

The first step is to install VirtualBox. This is software to allow you to run virtual machines.

Second step is to download and install a pre-built virtual machine. Oracle provides several virtual machines that are pre-built with lots of software. Download this pre-built virtual machine. It contains an installed Oracle Database Enterprise Edition (it has all the database features), contains some additionally installed software, and also some tutorials. Follow the installation instructions.

You will need a PC/Laptop that is 64bit and capable of running Virtualisation.

You will need to change some of the configuration settings for this machine. Change RAM to 4G, and number of CPUs to 2.

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

Assignment A
Click here to download the handout for Assignment A.
Topics will be allocated using a lottery during class in Week 2.