Introduction

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.

This week you will be using Oracle SQL Developer. Make sure you have this downloaded and ready to use before the class.

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.

Notes

Click here to download the notes.

L2-Database Design

Videos (these videos have no sound)

Video showing how to create a logical model in SQL Developer

Video showing how to create a physical model and generating the DDL script in SQL Developer

Lab Exercises

Exercise 1

Task 1 – Create an/one ER Diagram that represents the following statements

Each company operates four departments and each department belongs to one company
Each department may or may not employ one or more employees, and each employee is employed by one department
Each employee may or may not have one or more dependents and each dependent belongs to one employee
Each employee may or may not have an employment history

Add in some additional attributes for each Entity.

Task 2 – Watch the video on creating the ER diagram using Oracle Data Modeler (see above)

I’ve recorded a short video that walks you through the steps of creating the ER diagram created for Task 1.
Watch this video (see above) to see how you can create entities, attributes and relationships.

Task 3 – Create the ER diagram in Oracle Data Modeler

Now you can open Oracle Data Model and create the ER diagram.
Remember to add in some additional attributes.

Exercise 2

Complete the ER diagram for our SMS/Text Messaging application.

Add in some additional attributes for each Entity.

Create the ER diagram using Oracle Data Modeler

Remember to add in some additional attributes.

Watch the video on how to generate a Physical Model & generate DDL script  (see above)

I’ve recorded a short video that walks you through the steps of creating a Physical Data model and then generates a DDL script. This script is then run in SQL Developer and the tables are created in the database.
Watch this video to see how you perform these steps.
You will need to create the Logical Data Model first, then convert it into the Physical Data Moidel

Generate the DDL script for Exercise 1

Log into your Oracle schema using Oracle SQL Developer, load in the DDL script and run this script.
Check that the script has run correctly and all the tables and relationships have been created correctly.

If you encounter any errors go back and fix them in the ER diagram, re-generate the DDL script and re-run the DDL script in SQL Developer.

Generate the DDL script for Lab 2 – Using the ER diagram created for the SMS application, create the Physical Data model and generate the DDL script.

Log into your Oracle schema using Oracle SQL Developer, load in the DDL script and run this script.
Check that the script has run correctly and all the tables and realtionships have been created correctly.

If you encounter any errors go back and fix them in the ER diagram, re-generate the DDL script and re-run the DDL script in SQL Developer.

Exercise 3 (Optional)

Complete the ER diagram for the ‘DIT Copy & Print Centers’ , for the following scenarios.

Have you used the DIT Copy/Print Centers? If not you might want to check them out and try using their services before attempting this exercise.

Design an ER diagram for the data that is captured, for the topping up of credit and the usage of their services.

Think out your interactions with using this service such as

  • Topping up/Adding Credit
    Using the copy/print service
    Getting a statement or current balance

What are the data issues ?
What systems/applications does it interact with ?

What issues have you experienced as an end user?

Create the ER diagram using Oracle Data Modeler. Remember to add in some additional attributes.

Generate the DDL script and run in Database. Using the ER diagram created above, create the Physical Data model and generate the DDL script.

Log into your Oracle schema using Oracle SQL Developer, load in the DDL script and run this script.
Check that the script has run correctly and all the tables and realtionships have been created correctly.

If you encounter any errors go back and fix them in the ER diagram, re-generate the DDL script and re-run the DDL script in SQL Developer.