Innovative DBA Hands-On Lab 1

Now that you have learned the strategies and principles of database design and development, you will use these to design a small database.

These fundamental concepts are necessary for the design, use, and implementation of relational database systems such as the Oracle database management system (DBMS).

This lab will help you develop skills for database design, development, creation of the physical database on disks and query formulation.

You will use a data modelling tool called ER Assistant to design a small database (about 5 tables) by developing an Entity Relationship Diagram (ERD).

ERD is a graphical way of presenting the database that’s going to be developed. Once you design the ERD, you’re going to translate that into DDL – data definition language, so you’ll be going from the graphical representation to actually defining tables and columns. Graphical representations are important because it is more cost effective and efficient to modify a database design on an ERD than it is to change database objects after they have been created on disk.

You will use this DDL statements to create your database objects by running them against your database.

Once you have created your database objects, you can load some data into the tables and you can create views (also called virtual tables).

You will be able to retrieve the data you loaded into your database to answer a question using simple SQL select statements.

This is the fundamental level of database design and development.

The exercises and solutions in this Lab simulate real-life database design and development goals.

Every database has a special interface between end-users and the database engine. In Oracle, this special interface is called SQL*Plus. The basic function of the interface is to allow users to execute commands to the database engine. You will connect to your database, create a user account and use run the DDL statements to create your database objects from this account. Once you have created your database objects, you will use DML statement to load some data into the tables and you will be able to retrieve the data by using simple SQL select statements.

Keep in mind!

The Database Management System (DBMS) you will use for this project will be Oracle.

 

Start Here

About Author

1

Hi All,

I am the CEO here at Kudrant Academy and have over 10 years oracle DBA experience. I’m thrilled to be your guide throughout this course.

In this course, I will teach you the strategies and principles needed to stay focused and on track in achieving your goal of becoming an Oracle DBA.

These principles are at the core of any success story and will be reviewed throughout the program

It will be up to you to “take action” by completing all the course assignments.

Ready?

Let's get started!

Imgard Ekokobe
CEO/Database Engineer
Kudrant Academy

Next

How To Get The Most Out Of This Course

2

Firstly, if you are a member of Kudrant Academy, please post questions about this Lab (or any other database related questions) to the  Facebook Group.

Here’s how to get the most out of the hands-on practice exercise:

Study and review the examples and practice along until you are comfortable with the SQL statements used to create, load and retrieve the tables.

After that, you will ready to complete the assignment at the end of the workbook.

Following directions is KEY in this project. Pay attention to the requirements for the lab assignment.

Keep in mind!

ONLY Oracle should be used for these exercises.

Next

What You Will Learn

3
  •  Identify the purpose of your database
    • Identify the entities and attributes of your database tables (five tables).
  •  Define the constraints for your tables.
  •  Create an Entity Relationship Diagram (ERD) for a small database of five tables.
  •  Translate the ERD into tables.
  •  Use SQL*Plus to connect to an oracle database.
  •  Create physical objects in a database using a script.
  •  Create a user account.
  •  Load data into tables using a script.
  •  Retrieve data from tables.
  •  Capture output to a log file.

 

Next

Lab Workbook

4

Download and print out the workbook for this project.

 

 

Available Deliverables

Next

Milestone I - Tools You Will Use

5

Databases are carefully planned and designed after gathering and analyzing user requirements from stakeholders.

After the requirements, have been gathered and defined, a model will be created for the database.

This model will be used to use to create the Entity Relationship Diagrams (ERD) and these ERDs will then be translated into Data Definition Languages (DDL).

The DDL script will be used to create your database tables and the DML script will be used to load some data into your tables.

There are some tools to help you create the necessary database tables.

These tools include:

  •  An editor (notepad in your case).
  •  SQL*Plus, and capabilities to print your source code and capture the output.
  •  ER Assistant tool.

You will learn how to use these tools in this workbook.

 

Next

Milestone II - Statement of Work (SOW)

6

Lab Assignment:

Write a paragraph describing the database you will design and implement. Describe the business solution your database will solve and how it will be used by the end users. Include the following information -

  • Purpose of the database.
  • The hardware your database will run on.
  • The database management system you will use (include the version).
  • The type of Data Definition Language (DDL) and Data Manipulation Language (DML) you plan to use.
  • The tools you will need.
  • How you will access your database.

Next

Milestone III - Create ERD

7

Develop an ERD using the ER Assistant tool you downloaded. You will create entity diagram types for your 5 tables which will depict the entities, attributes, primary keys, foreign keys and the relationships between the tables. Each table must be normalized to the third normal form.

 

Next

Milestone IV - Convert ERD to Tables

8

1. Set up a DDL script to create your 5 tables for your database. Your DDL script will include the following:

a. 5 CREATE TABLE statements to create your five tables.

b. Include drop and create statements in proper order.

c. Constraints defined for your tables (primary, foreign, unique, null, check).

d. Define data types for each column of your tables.

e. A view on a single table.

f. A equijoin view

g. Indexes as needed.

h. A Query of the catalog on your database and be sure the five tables are shown.

2. Create a new user account in the database.

3. Execute the script in SQL*Plus against this new account.

4. Debug the script to clear any errors.

 

Next

Milestone V - Load data

9

1. Set up a script that will load data into your five tables. Script name: TeamName_loadata.sql

2. Spool the output to a file. Script name: TeamName_loadata.log

3. Execute the script in SQL*Plus.

4. Debug the script.

 

Next

Milestone VI - Retrieve Data

10

Set up a script that will retrieve data from the tables. Script Name: TeamName_retreievedata.sql
2. The data retrieved should meet the following constraints:
a. Using a single query select all the rows of one table.
b. Using a single query select some rows of one table.
c. Using a single query select data from a table that meets specific conditions.
d. Use the IN statement to filter rows.
e. Using an equijoin view perform a two table join.
f. Using an equijoin view perform a three table join.
g. Create a table like one of your tables. Alter it and add a column.
3. Use the spool tool to capture the output to a spool file. Name of spooled file: TeamName_retrievedata.log
4. Execute the script in SQL*Plus

Next

Milestone VII - Final Deliverables

11

Data retrieval script and log file.log. Script Name: TeamName_retrievedata.sql

File name: TeamName_retrievedata.log

A word document including the following SQL scripts.

  •  Statement of work
  •  ERD
  •  DDL script including the log files
  •  Load script including the log files
  •  Data retrieval script including the log files

All the log files should be added as an appendix at the end of your final word document and should be properly labeled

Complete Course