1. Relational Database Design 1 1 n 1 n n n How to Link Tables in a Relational Database 1 Schema for part of a business application relational database

2. Most of us are used to a Flat File database. This involves the use of One table (Access Jargon) only. A Flat file database is fine for very simple database functions but it turns out to be quite limiting. A flat file database’s worst limiting factor is that of data redundancy where data has to be typed into several different tables eg. Surname, Firstname into more than one table. Typo errors usually results when entering the same data into more than one table.

3. Primary Key Primary Key Foreign Key To overcome redundancy, we use two or more tables. Two tables are linked by using a 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 is a primary key of another table. It is used to create a relationship between a pair of tables. A table CANNOT have two or more primary keys. When tables are linked we can access data from other tables either directly or indirectly.

4. 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 various levels of checking for the accuracy & consistency of data Easy data retrieval: data can be retrieved from a particular table or from any number of related tables within a database. Improvements in software and hardware have allowed relational databases to run fast and smoothly. Eg. Access operates very poorly on workstations prior to the Pentium I chip with only 16 mb of RAM.

5. A connection between a pair of tables is known as a relationship. • There are 3 types of relationships between tables and this has impact on • how 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 customer • NOTE: read the tables left to right AND right to left

6. One-to-Many (1:n) -> this occurs when a single record in the first table • can be related to one or more records in the second table, but a single • record 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 minimum • NOTE: read the tables left to right AND right to left

7. 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 second • table, and a single record in the second table can be related to one or more • records 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.

8. n n n Order ID n Product ID 1 1 n n Key: 1 = 1; n = many The diagram on the right is a many-to-many relationship which results in redundant data. NOTE: read the tables left to right AND right to left Linking Table To overcome this, a linking table is inserted by copying the Primary key from each table & adding more fields if desired. Also can be referred to as a transaction table where the transaction number is unique

9. Managers Employee ID PK Email Address Mobile Phone Departments Department ID PK Employee ID FK DeptName DeptCategory Maximum Staff Level 1 1 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 where it becomes a Foreign key. PK = Primary Key, FK = Foreign Key 1 1 Departments Department ID PK DeptName DeptCategory Maximum Staff Level Managers Employee ID PK Email Address Mobile Phone 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

10. n 1 Family Family ID Street Suburb Postcode Phone Students Student ID Preferred Name Surname 1 Family Family ID PK Street Suburb Postcode Phone Students Student ID PK Family ID FK Preferred Name Surname n 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 PK= Primary Key FK= Foreign Key

11. 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.

12. Elements of the Ideal Table • 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

13. Schema for part of a business application relational database 1 1 n 1 n n n 1

14. Bibliography Information Processes and Technology HSC Course, Powers, Heinemann ISBN 0 86462 512 X Designing Databases, Glyn / Dixon, McGraw Hill, ISBN: 0 07 470511 3 Database Design for Mere Mortals, Hernandez, Addison Wesley, ISBN 0-201-69471-9 Developing Databases in Access, Summers G, Nelson, ISBN: 0 17 010311 0 Illawarra Grammar School Steve Madsen