Compiled Contents of:Week 1 Database Introduction

 

Content File: Topic 1 Objectives


Topic 1 Objectives

Topic 1 Objectives

After completing this weeks study students should be able to:

  1. Understand the role of data as a resource in an organisation
  2. Provide the Definitions: Data, Database, DBMS
  3. Have knowledge of the major components a DB interface usually offers
  4. Understand the Motivation for the Database approach
  5. State the objectives of the database approach
  6. Understand what data independence means
  7. Define a database transaction
  8. Define a database backup and recovery procedure
  9. Understand how a database improves security of data
  10. Understand how a database Inter-relates Data through a Model
  11. Have knowledge of the different models for a DBMS these being: Relational, Hierarchal, Network, Object-Oriented
  12. Be aware of the costs as well as the benefits of DBMSs
  13. Connect to the BSIT database on llama and run simple scripts to create/select from/drop basic tables


Topic 1 - Where are We

 

Content File: FIT1004SG1.pdf


FIT1004SG1.pdf
 

Content File: fit1004wk1-lect.pdf


fit1004wk1-lect.pdf
 

Content File: fit1004wk1-lect-4up.pdf


fit1004wk1-lect-4up.pdf
 

Content File: Tutorial 1 - Database: Introductory Topics


Tutorial 1 - Database: Introductory Topics

Tutorial 1 – Database: Introductory Topics

Theory

Reference:

Rob, P. & Coronel, C. Database Systems: Design, Implementation & Management, 6th Edition, 2004, Chapter 1. Review Questions 2 – 7, 9 – 10. Problems 1 – 10.

Rob, P. & Coronel, C. Database Systems: Design, Implementation & Management, 7th Edition, 2006, Chapter 1, Review Questions 2 – 7, 9 – 10. Problems 1 – 10.

Review Questions:

2. What is data redundancy, and which characteristics of the file system can lead to it?

3. Discuss the lack of data independence in file systems.

4. What is a DBMS and what are its functions?

5. What is data independence, and why is it important?

6. Explain the difference between data and information.

7. Explain what data inconsistency is and why it occurs.

9. What are the main components of a database system environment?

10. What is metadata?

Problems

Figure P1.1: Given the file structure shown, answer Problems 1 through 4.

1. How many records does the file contain and how many fields are there per record?

2. What problem would you encounter if you wanted to produce a listing by city? How would you solve this problem by altering the file structure?

3. If you wanted to produce a listing of the file contents by last name, area code, city, state, or zip code, how would you alter the file structure?

4. What data redundancies do you detect, and how could these redundancies lead to anomalies?

Figure P1.5 The File Structure for Problems 5-8

5. Identify and discuss the serious data redundancy problems exhibited by the file structure shown in Figure P1.5.

6. Looking at the EMP_NAME and EMP_PHONE contents in Figure P1.5, what changes would you recommend?

7. Identify the different data sources in the file you examined in problem 5.

8. Given your answer to Problem 7, what new files should you create to help eliminate the data redundancies found in the file shown in figure P1.5?

Figure P1.9 The File Structure for Problems 9 - 10

9. Identify and discuss the serious data redundancy problems exhibited by the file structure shown in Figure P1.9. (The file is meant to be used as a teacher class assignment schedule. One of the many problems with data redundancy is the likely occurrence of data inconsistencies – note that two different initials have been entered for the teacher named Maria Cordoza.)

10. Given the table structure shown in Figure P1.9, what problem(s) might you encounter if you deleted building KOM?

Practical Issues - Introduction to Oracle:

For our on campus work we will be using an Oracle database maintained by IT Services on the server:

   llama.its.monash.edu

Your lecturer/tutor will supply you with the SID for the database which your group is using - in the examples belpw the SID BSIT has been used (this is the database used by Berwick FIT students)

Using ORACLE on llama On-Campus

There are a number of different ways to connect to the Oracle database on the LLAMA server. We will use several Oracle products in the on-campus labs - SQL*Plus for setting our Oracle password and SQL*Plus Worksheet/SQLDeveloper for all other activities.

Connection via MS Windows SQL*Plus (for setting your password only)

Select - Start, Programs, Development, Oracle - OraHome92, Application Development, SQL Plus

Note:

  • Username will be your authcate ID,
  • Password (your Oracle server password, not your authcate password) will be supplied by your tutor/lecturer, and
  • Host String value is the LLAMA DBMS SID name - BSIT at Berwick, yours will be different if you are not at Berwick

Please remember you must replace the username with your authcate username and the host string with your database SID (the values being used on this page are for instruction only)

CLAYTON based students must use a host string of FT1004CL

After your first login please run the password command to set a new password for your account

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Mar 8 18:38:59 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

SQL> password
Changing password for LSMI1
Old password: *******
New password: ****
Retype new password: ****
Password changed
SQL>

Do NOT set your Oracle password the same as your standard authcate password. Oracle has several important limits on the password you set:

  • The password is not case sensitive
  • Must be 1 - 30 characters in length
  • Must begin with an alphabetic character
  • Can contain only alphanumeric characters and the underscore (_) or dollar sign ($)

After setting the password, logout and back in to ensure it works.

To close SQL*Plus type exit at the SQL prompt:

SQL>exit

Connection via MS Windows SQL*Plus Worksheet

Select - Start, Programs, Development, Oracle - OraHome92, Application Development, SQL Plus Worksheet

Connection via SQL Developer (the preferred client)

Start SQLDeveloper (your tutor will indicate the desired method for the lab). You will first need to configure a connection to the LLAMA DBMS - write click the connections icon in the left panel and enter the following details:

new connection settings

for Clayton based students, using the FT1004CL database, the Connection details would be:

The username is your authcate username, the password is the value you set with SQL*Plus above (Ensure you do not have "Save Password" checked in the on-campus labs). After connection you will see:

SQLDeveloper

SQL Statements can be entered in the left hand panel, labelled "Enter SQL Statement". Oracle SQL Developer can be downloaded from the MUSO site or direct from Oracle and used at home to access the University Oracle database. Alternative database clients are also available - see the linked page above.

Data storage in Relational DBMS

Data in relational databases such as Oracle are stored in TABLES as the base structure - tables are created via the RDBMS Data Definition Language (DDL) statement create table:

create table tennant (
tennant#     smallint not null,
family_name  char (20) not null,
given_name   char (20) not null,
address      char (30) not null,
phone#       char (10) not null );

There are three SQL Standards

  • Original ANSI SQL (1986) standard (SQL1) - later the Integrity Enhancement Feature (IEF) were added
  • SQL92 (SQL2) includes IEF features (plus many other features) as part of standard , and
  • SQL3 - currently being worked on as a standard

At this stage of the unit we will not be concerned with these various SQL standards - we simply wish to practice log on to Oracle and creation and dropping of tables. The tables we will be creating are seriously 'flawed' with respect to database enforced integrity - we will return to this issue over the next few weeks.

To list all the tables you currently own (ie. in your Oracle user space) use the SQL command:

select * from cat;

After you have logged in list all your current tables using this SQL command. Commands are entered into the top panel of SQL*Plus worksheet, run by clicking on the lighting bolt and output is displayed in the lower panel:

No results displayed, indicates you currently have no tables in your Oracle userspace. Now create a sample table using the SQL CREATE command:

create table test
(name char(20),
town char(20));

Check the creation of your table using:

select * from cat;

You should see output of the form:

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
TEST                           TABLE

To add data to this table we use the SQL INSERT command (note character data is between single quotes):

insert into test values ('Lindsay Smith','Berwick');

This should create output to say:

1 row inserted.

You can use right click in the SQL statement window and select "Save File" to save the SQL command into a .sql file for later reuse:

To examine the contents of the table use the SQL SELECT command:

select * from test;

To remove a table use the SQL DROP command:

drop table test purge;

This will produce the output:

Table dropped.

Check that your table has been removed - do you remember how?

Using ORACLE to create a CUSTOMER table:

Two techniques are possible:

  • GUI interface - here a graphical interface is used to build a table column by column (most databases have such a tool), for example:

    Right click the Table icon, select "New Table" and build a table using the SQLDeveloper GUI

  • or using a text schema file - this is the required approach because of it's ease of repeatability

The 'basic' ORACLE datatypes are:

DATA NOTATION TYPE RANGE
Char(1) - char(2000) character a string of 1 to 2000 characters
Date date 1-Jan-4712 BC to 31-dec-4712 AD
Number(p,s)   Number column:

p is the precision, or the total number of digits

s is the scale, or the number of digits to the right of the decimal point

eg. NUMBER(5,2) can hold a max of 999.99

A typical script, called a schema file, which we would create in a text editor would be:

rem
rem Table               : customer
rem cust_numb           : Customer number 
rem custname            : Customer Name 
rem cust_address        : Customer Address 
rem cust_bal            : Customer current balance 
rem cust_cred_limit     : Customer Credit limit 
rem slsr_number         : Sales rep who services customer 
rem

create table customer (
    cust_numb       char (10) not null,
    custname        char (30) not null,
    cust_address    char (40) not null,
    cust_bal        number (8,2) not null,
    cust_cred_limit number (8,2) not null,
    slsr_number     number (2));

Note the use of the REM command to create a REMARK line in the script - Oracle ignores the contents of such lines. A create table statement begins with the words

create table

followed by the table name, an opening bracket and a list of the attributes of the table and their data type as a comma separated list. The create table statement is closed with a closing bracket and a semi colon. From the word create to the semicolon is one SQL statement.

WEEK 1 Tutorial Exercise

Tutorial Exercise Part I:

SETTING UP A SAMPLE DATABASE

Step A: Create a schema file - you can copy and paste this code (save the commands to a file called custschm.sql):

rem
rem Table               : customer
rem custno              : Customer Number
rem custname            : Customer Name
rem custbalance         : Customer balance
rem

create table customer (
    custno         char(5) not null,
    custname       char(20) not null,
    custbalance    number(6,2) not null);

rem
rem Table              : orders
rem ordno              : Order Number
rem orddate            : Order Date
rem custno             : Customer Number
rem

create table orders (
    ordno           number(5,0) not null,
    orddate         date not null,
    custno          char(5) not null);

Step B: run the schema file

You should see as output:

create table succeeded..

create table succeeded..

Again, check that these tables actually created - do you remember how?

The tables are created within the Oracle database under your Oracle (authcate) login name. As indicated above you can list the Oracle objects you currently own via the select * from cat SQL command. If you wish to create a new object, one of the same name must not currently exist. For example, if you already have a table called customer, then the schema above, will cause an error of the type:

ORA-00955: name is already used by an existing object

In such a situation you need to first remove the customer table via the SQL drop command:

drop table customer purge;

If you currently have both a table called CUSTOMER and a table called ORDERS and try to run the schema file, the ORA-00955 error will occur twice since both names conflict. In such a situation you need to drop both tables.

Tables must be dropped in the reverse order from which they were created to protect the parent-child relationship (referential integrity) - this is not applicable in our examples for this week, but will be very important for future work.

Step C: Load data into the tables - create an insert script

After creating the tables, data must be loaded into the tables using the SQL insert statement for single rows

eg. Insert into customer values ('SMI01', 'Lindsay Smith', 3000.00);

Dates and ORACLE:

Date storage is often a problem with various DBMS's - for Oracle (at this point in time) use the date data format of: 'DD-MMM-YYYY' in single quotes within an insert statement eg. '09-MAR-2007'

eg. Insert into orders values (125, '09-MAR-2007', 'SMI01')

Create a new text file called custinsert.sql which contains several rows as above, inserting say 3 customers and 6 orders. Execute the script, after the commands have completed check that the data loaded correctly using the SQL SELECT command

Step D: Drop the tables - create an drop script

Create a third new text files called custdrop.sql which contains the SQL commands to drop your tables and then execute the script.

You should now have three text files:

  • custschm.sql - the schema file (script) containing SQL create table statements
  • custinsert.sql - the insert file (script) containing SQL insert statements
  • custdrop.sql - the drop file (script) containing SQL drop commands

which should be able to be used in sequence to create the tables, load the data and drop the tables.

Tutorial Exercise Part II:

Create three tables as follows and load them with sample data using SQL insert commands - again we are ignoring the issues of entity and referential integrity here (do not worry about primary and foreign keys, we will return to this later). This weeks exercise is about connecting to Oracle and using the Oracle client software rather than technically correct table creation.

a. SWIMMER

Attributes:

Swimmer registration code eg. BE101 (maximum of 5 characters)

Name (maximum of 20 characters)

Date of Birth

Swimming Club Name (max 15 characters)

b. EVENT

Attributes:

Event Number 1 .. 50

Event Description (maximum of 20 chars)

c. ENTRY

Attributes:

Swimmer registration code eg. BE101 (maximum of 5 characters)

Event Number 1 .. 50

Entry time (m.sshh) eg. 0.3506 (store as a number)

Final time (m.sshh) - will not be know until after the race has been swum (in the insert file put the word null for this value) - ie. as an example: insert into ENTRY values ('BE101',5,0.3506, null)

You should create three separate text files and test run them within SQL

  • a schema file called swimschm.sql to create the tables (swimmer, event and then entry)
  • a file called swimload.sql containing several insert statements to load data into all three tables, and
  • a file containing drop table statements (eg. drop table swimmer purge) to drop all tables - ensure the tables are dropped in the reverse of the order they are created ie child tables before parent tables.

Where multiple SQL statements exist in one file be sure to terminate each with a semi-colon (;). You can check the contents of your loaded tables by using the SQL select command eg. select * from swimmer

Your tutor will check these files during next weeks tutorial.

Tutorial as a PDF file

 

 

Content File: fit1004wk1-tut.pdf


fit1004wk1-tut.pdf
 

Content File: swim-soln.zip


swim-soln.zip