What is a database?

Often abbreviated to DB, a database is a collection of records made up of fields stored in a file. A telephone book is a database. It contains a list of records, each record has three fields: name, address and telephone number. The telephone book is of course, a hard copy of a database stored on a computer. This first topic looks at:

basic concepts and definitions of databases
why we use databases
the standard query language

Concepts & definitions

Ch1 pp 39-41

To begin to understand more about what a database is, read the excerpt from the first chapter of your text: Modern Database Management . This reading gives an introduction to databases, and outlines some basic concepts and definitions. As you read note:

the difference between Data, Information and Metadata
what is meant by a Database Management System (DBMS)

Why databases?

Ch1 pp 42-43

Databases solve a problem with the way data was conventionally stored.

Traditional file systems
We need to look at how data was conventionally stored before going any further. The next reading from your text Traditional File Processing Systems provides an overview as well as the disadvantages of traditional file systems.

Ch1 pp 44-50

The database approach
Now we can look at the advantages and disadvantages of using a database. This reading The Database Approach uses the Pine Valley Furniture Company to justify the decision to go to a relational database.

Structured Query Language (SQL)

Ch6 pp 277-280

Relational databases uses Structured Query Language (SQL) to access the database.

The next reading introduces SQL, explains its role and gives some historical background. As you read, concentrate on developing a feel for SQL’s place and role, without worrying overly about the details.

SQL can be divided into three groups:

DDL – Data Definition Language
DML – Data Manipulation Language
DCL – Data Control Language

Ch6 pp 280-285

It is important to understand this grouping, as the rest of this module, Using a database and the last module Advanced SQL will be based around these groups. This reading goes over the SQL environment and the functions of each group.

While there is a series of standards for SQL, most commercially available packages do not always adhere to these. The good news is that the deviations tend to be minor.

`INVOICE` Database

The INVOICE Database

The INVOICE database will be used for practicals in this unit This is a relational database consisting of four tables.

Getting
your hands dirty

Student information

1. Redesign Table 1.1, in Ch 1 p. 41 of your textbook to reflect enrolment practices at CDU.

For example, we don’t use Social Security number nor have Majors. What do we use instead?
What would be the appropriate metadata for these alternatives?

Name	Type	Length	Min	Max	Description	Source
Unit	Alphanumeric	30			Course ID and name	Academic Unit
Mode	Alpha	1			Internal or External	Student Services
Semester	Integer	1	0	5	Semester	Student Services
Year	Integer	4	1980	2100	Year	Student Services
Name	Alphanumeric	30			Student name	Student Services
sID	Integer	6			Student ID	Student Services

2. Extend your table from question One to include the extra metadata columns:

Required: is an entry required for this field or can it be left blank?
Input Mask: what patterns does the information have to follow? For example, what pattern does your student number have?

Name	Type	Length	Required	Input Maks	Min	Max	Description	Source
Unit	Alphanumeric	30	Yes	3 alpha, 3 Integers			Course ID and name	Academic Unit
Mode	Alpha	1	Yes	I or E			Internal or External	Student Services
Semester	Integer	1	Yes		0	5	Semester	Student Services
Year	Integer	4	Yes		1980	3000	Year	Student Services
Name	Alphanumeric	30	Yes				Student name	Student Services
sID	Integer	6	Yes				Student ID	Student Services