Relationships
This presentation is the property of its rightful owner.
Sponsored Links
1 / 13

Relationships PowerPoint PPT Presentation


  • 49 Views
  • Uploaded on
  • Presentation posted in: General

Relationships. Relational Database. 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 .

Download Presentation

Relationships

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Relationships

Relationships

Relational Database


Identifying entities

  • 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…


Tables

  • 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


Tables1

  • 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


Primary keys

  • 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


Primary keys1

  • 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


Foreign keys

  • 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


Entity relationship diagrams erds

  • 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


Entity relationship diagrams erds1

  • 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


Relationships

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


Relationships in access

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


Relationships in access1

  • 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


Assessment task

  • 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


  • Login