Relationships
Download
1 / 13

Relationships - PowerPoint PPT Presentation


  • 66 Views
  • Uploaded on

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 .

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Relationships' - leoma


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

Tables


Primary keys

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

Movies

Studios

Entity Relationship Diagrams - ERDs


Entity relationship diagrams erds1

Movies

Studios

Entity Relationship Diagrams - ERDs


  • 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

Assessment Task


ad