Compiled Contents of:Week 1 Database Introduction
Content File: Topic 1 Objectives
Topic 1 Objectives
After completing this weeks study students should be able to:
Content File: FIT1004SG1.pdf
Content File: fit1004wk1-lect.pdf
Content File: fit1004wk1-lect-4up.pdf
Content File: Tutorial 1 - Database: Introductory Topics
Tutorial 1 – Database: Introductory Topics
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.
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?
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:
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
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 184.108.40.206.0 - Production on Wed Mar 8 18:38:59 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Do NOT set your Oracle password the same as your standard authcate password. Oracle has several important limits on the password you set:
After setting the password, logout and back in to ensure it works.
To close SQL*Plus type exit at the SQL prompt:
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:
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:
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 (
There are three SQL Standards
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
Check the creation of your table using:
select * from cat;
You should see output of the form:
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:
Check that your table has been removed - do you remember how?
Using ORACLE to create a CUSTOMER table:
Two techniques are possible:
The 'basic' ORACLE datatypes are:
A typical script, called a schema file, which we would create in a text editor would be:
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
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):
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:
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.
You should create three separate text files and test run them within SQL
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.
Content File: fit1004wk1-tut.pdf
Content File: swim-soln.zip