Overview of this Lecture / week

This week we will be concentrating on doing some Database Design. If you had a Database module as part of your undergraduate degree then you would have spent a lot of time covering normalization.  We will not be covering this, because in industry you don’t go through 1st, 2nd, 3rd, normal forms. By following some simple rules you can end up with a data model in 3rd normal form.

But if you would like to revise normalization then here are some links.

This weeks class will consist of doing some exercises and then using a Data Modeling tool.  The notes are a guide and reference purposes.

Notes

Click here to download the notes.

L2 - Database Design

Videos

These videos show you how to create a logical and physical data model.

There is no sound/voice over for each of these videos.  I talked through these videos in class.

Creating a Logical Data Model

Creating a Physical Data Model

Lab Exercises

  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.
  5. JSON Lessons, Learning and Documentation. Make sure you have read over some of the following before next week.

Oracle JSON Documentation

JSON tutorial on SQL Live  – You will need to create an account on the Oracle website to access this tutorial

Tim Hall’s – JSON examples

Additional Reading Materials

The Secret Life of SQL and it’s Longevity

51  Database Terms you need to know

10 Common Mustakes Java Developers Make when writing SQL