1n

1

n

1 1nn

Schema for part of a business applicationrelational database

Most of us are used to a Flat File database. This involves the use of Onetable (Access Jargon) only.

A Flat file database is fine for verysimple database functions but it turnsout to be quite limiting.

A flat file database’s worst limiting factor is that of data redundancy wheredata has to be typed into several differenttables eg. Surname, Firstname into morethan one table. Typo errors usually resultswhen entering the same data into more than one table.

Foreign KeyPrimary Key

Primary Key

To overcome redundancy, weuse two or more tables.Two tables are linked by usinga common field name in both tables.A Primary Key is field that UNIQUELY identifies that object within its table eg. Student ID or Customer ID.

A Primary Key field cannot be empty or Null.A Foreign Key is a field of a table that isa primary key of another table. It is usedto create a relationship between a pair oftables.A table CANNOT have two or more primary keys.When tables are linked we can accessdata from other tables either directly orindirectly.

Some advantages of a relational database include:

Built in multilevel data accuracy (integrity):• table level -> ensure records are not duplicated• detects missing Primary key values• ensures that a relationship between a pair of tables is valid• uses validation techniques to ensure data entry is accurate

Guaranteed data consistency and accuracy due to the variouslevels of checking for the accuracy & consistency of data

Easy data retrieval: data can be retrieved from a particular tableor from any number of related tables within a database.

Improvements in software and hardware have allowed relationaldatabases to run fast and smoothly. Eg. Access operates very poorly on workstations prior to the Pentium I chip with only 16 mb ofRAM.

A connection between a pair of tables is known as a relationship.There are 3 types of relationships between tables and this has impact onhow we design a database on paper before using the actual software.

One-to-One (1:1) -> this occurs when only one record in a table relates only to one record in a second table. Eg:• A primary class has only one teacher and that teacher is assigned only one class.• Only 1 customer can purchase a new car and the sale of each new car is related to only one customerNOTE: read the tables left to right AND right to left

One-to-Many (1:n) -> this occurs when a single record in the first tablecan be related to one or more records in the second table, but a singlerecord in the second table can be related to only one record in the first table. Eg:• One student can take out many books from the library, but any one book can be taken out by one student at a time.• This is by far the most common relationship that exists between a pair of tables within a database• This relationship helps to remove duplicate data and to keep redundant data to an absolute minimumNOTE: read the tables left to right AND right to left

A many-to-many relationship exits between a pair of tables if a single record in the first table can be related to one or more records in the secondtable, and a single record in the second table can be related to one or morerecords in the first table. Eg:• Each student studies many subjects, and each subject is studied by many students• It is difficult to create a direct connection between many-to-many tables To overcome this, we make use of a linking table which consists of copying the primary key from each table involved.

1 1nn

Product ID

Order ID

n

n

n

n

The diagram on the right is amany-to-many relationshipwhich results in redundant data. NOTE: read the tables left to right AND right to left

To overcome this, a linking table is inserted by copying the Primary key from each table & adding more fields if desired.

Linking Table

Also can be referred to asa transaction table where thetransaction number is unique

Key: 1 = 1; n = many

One-to-One Relationship -> to create a relationship one takes a copy of the Primary key from the main table and inserts it into the 2nd table whereit becomes a Foreign key. PK = Primary Key, FK = Foreign Key

Managers

Employee ID PKEmail AddressMobile Phone

Departments

Department ID PKDeptNameDeptCategoryMaximum Staff Level

1 1

Managers

Employee ID PKEmail AddressMobile Phone

Departments

Department ID PKEmployee ID FKDeptNameDeptCategoryMaximum Staff Level

1

1

There is one manager to department and only one department assigned to a manager. NOTE: read the tables left to right AND right to left

One-to-Many Relationship -> is similar to the one-to-one relationship. One copies the Primary key from the table on the “one” side of the relationship and inserts it into the table on the “many” side, where that field becomes the Foreign key.One Family may have more than one Student at school and Many students may belong to only One Family

Family

Family IDStreetSuburbPostcodePhone

Students

Student IDPreferred NameSurname

1 n

Family

Family ID PKStreetSuburbPostcodePhone

Students

Student ID PKFamily ID FKPreferred NameSurname

1

n

PK= Primary Key

FK= Foreign Key

To create tables within a database, on paper one generally:• Groups entities and attributes (fields) together in a way that makes sense• Assigns a Primary key field to each group• Establishes the type of relationship between groups Eg: 1:1, 1:n, n:n• Assigns a Foreign key in 1:1 and 1:n relationships•Creates a link between appropriate pairs of groups

Once the paper design has been completed then one uses their favourite database software to create the respective tables and create the necessary linkages between tables.

•A many-to-many relationship requires a linking table.•A one-to-one relationship may be directly connected•A one-to-many relationship may be directly connected.

• It represents a single subject, which can be an object or event.

• It has a Primary Key

• It does not contain multipart fields. Eg: Wollongong, NSW 2500 (text and number in 1 field)

• It does not contain multivalued fields. Eg: Johns, Andrew (more than one of the same type, text in this case)

• It does not contain calculated fields

• It does not contain unnecessary duplicate fields

• It contains only an absolute minimum amount of redundant (repeated) data

1n

1

n

1 1nn

Schema for part of a business applicationrelational database

Information Processes and Technology HSC Course, Powers, Heinemann ISBN 0 86462 512 XDesigning Databases, Glyn / Dixon, McGraw Hill, ISBN: 0 07 470511 3Database Design for Mere Mortals, Hernandez, Addison Wesley, ISBN 0-201-69471-9Developing Databases in Access, Summers G, Nelson, ISBN: 0 17 010311 0

Illawarra Grammar School Steve Madsen