Relationships

1. Relationships Relational Database

2. In the previous tutorial you learnt about identifying entities in a flat file database. • Also its limitations and why a relational database is better avoids data redundancy and anomalies/orphaned data. • In this tutorial, you will create a basic relational database. • e.g. two tables in Microsoft Access that are joined by a relationship. Identifying Entities…

3. Looking at our example flat file spreadsheet that has two tables/entities one about the Sales Rep, the other about Jobs, create a new blank Access database on your H drive. • Save it as tutorial • Create a Sales Rep table with the appropriate fields and data types to hold the first set of 3 sales rep and their details. Tables

4. Create a Jobs table with the appropriate fields and data types to hold the second set of information. • Add Brent working on two projects, one for McDonalds, the other for KFC, one took him 19 hours, the other 22 for a date last week. Tables

5. When you need to track multiple tables, a relational DB is best. • Need some way to uniquely identify each employee and each of the jobs . These are known as a Primary key i.e.could have same employee name, so need a unique way to identify each record e.g. student ID, jobID, empID Primary Keys

6. In your database, add another column to each table to reflect these unique ID’s. These should be data type autonumber and need to be the Primary key for that table. • When you refer to a particular employee for a job, they can now be known by their empID, instead of their name e.g. according to this job table, what were Carla’s total hours worked? Primary Keys

7. Tables that are related need to have fields in common. E.g. the job table will have an EmpID field in it. Though the field “label” doesn’t have to match, these fields must have data that matches i.e. the same “data type” and data. • The field in one table – Primary Key, will match or “relate” to a field in another table – Foreign Key. • In this example, the Primary Key is yellow and the Foreign key it “relates” to is red. • NOTE – if your primary key is autonumber, its foreign key must be data type “Number” to create a relationship. Foreign Keys

8. To depict tables/entities and their relationships, you draw what’s called an ERD. • Below is an example using the DVD example from tutorial 1. Movies Studios Entity Relationship Diagrams - ERDs

9. This shows a studio can have M or Many movies made, but a movie can only be made at 1 studio. • There are other relationship types but for your assessment, this is the only one to worry about. Movies Studios Entity Relationship Diagrams - ERDs

10. Draw an ERD for your assessment club/tables you created in tutorial 1, and where the 1 to M relationships will flow. • Ask yourself what entity can have more than one entry? Or what entity can only have a single entry? E.g. a customer can have many orders, but an order must only have one customer.

11. Under Database Tools in Access, Add both tables and create a 1 to M relationship between the Primary key in Sales Rep to the Foreign key in Jobs. Tip - you should drag from the M to the 1 or the foreign key to the primary key. Tip – close tables before trying to create the relationship. Relationships in Access

12. NOTE – Every relationship should always have “Enforce Referential integrity” ticked. • This gives a database two advantages: • It makes sure a primary and foreign key match e.g. we could not assign a jobID to empID J, because this does not exist in the Sales Rep table. • You wont be able to delete a Sales Rep if they have Jobs in the other table. i.e no orphans. Relationships in Access

13. If Miss has signed off on your Entities and Fields template and your ERD, have a go at creating your tables for your assessment database, assigning the correct data types, and then establish the relationships. • DO NOT input data until Miss has signed off on the “structure” of your DB. Assessment Task