D111 data fundamentals
This presentation is the property of its rightful owner.
Sponsored Links
1 / 19

D111-Data Fundamentals PowerPoint PPT Presentation


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

D111-Data Fundamentals. Week 5. Objectives. Recap Primary Keys Foreign Keys Data Dictionary Creating the Tables in MS Access Creating relationships in MS Access. Problem Statement.

Download Presentation

D111-Data Fundamentals

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


D111 data fundamentals

D111-Data Fundamentals

Week 5


Objectives

Objectives

  • Recap

  • Primary Keys

  • Foreign Keys

  • Data Dictionary

  • Creating the Tables in MS Access

  • Creating relationships in MS Access


Problem statement

Problem Statement

  • "As a salesman, I'm responsible for 10 clients . Each of my clients makes an appointment to come into the showroom to view the merchandise we have to offer for the current season. Part of my job is to answer any questions they have about our merchandise and make recommendations regarding the most popular items . Once they make a decision on the merchandise they'd like to purchase, I write up a sales order for the client. Then I give the sales order to my assistant, who promptly fills the order and sends it to the client."


Review the list

Review the List

Salesman

Client

Appointment

Showroom

View

Merchandise

Season

Question

Recommendations

Item

Decision

Purchase

Sales Order

Assistant

Sends Order

  • Salesman

  • Client

  • Appointment

  • Item

  • Sales Order

  • Assistant


Entity matrix

Entity Matrix


Conceptual data model

Conceptual Data Model


Create the associative entity

Create the Associative Entity


Invert the outer many crows feet

Invert the Outer MANY crows feet

?

?

Because we automatically invert the many side of a Many to Many inward, there will always be a One to Many relationship from the outsides in.

THIS WILL NOT NESSESSARILY BE THE CASE FROM INSIDE ENTITY OUT.

The relationship could be One to One or One to Many


Read the relationship between entities from left to right

Read the relationship between entities from Left to Right

We know the relationship from the CLIENT point of view is One to Many


Decide the formal cardinalities

Decide the FORMAL Cardinalities

Is the relationship optional or mandatory?

A single CLIENT can make many PURCHASES

Can means the relationship is OPTIONAL

In other words: EACH CLIENTDOES NOT NEED TO MAKEAPURCHASE


Check for direct relationships

Check for Direct Relationships

If I have the Client ID no.

In the Appointment table,

then I know the Client.

Then if I have the Salesman

ID no. in the Client table, then I

know the Salesman.

Would I then need to associate the

Salesman with the appointment?


Possible solution

Possible Solution


The primary key

The Primary Key

  • Definition: 

    • uniquely identifies each record in the table.

    • can either be a normal attribute that is guaranteed to be unique

    • can be generated by the DBMS (such as an auto number, or GUID in Microsoft SQL Server).

    • may consist of a single attribute or multiple attributes in combination.


The foreign key

The Foreign Key

  • Definition:

    • a field in one table that points to the primary key of another table.

    • The purpose of the foreign key is to ensure referential integrity of the data.

    • In other words, only values that are supposed to appear in the database are permitted.


Referential integrity

Referential Integrity

  • A database management safeguard that ensures every foreign key value entered matches an existing primary key value.


Visio

Visio


Data types

Data Types

  • Definition:

    • specifies the type of data that the attribute (field) can hold: integer data, character data, monetary data, date and time data


Data dictionary

Data Dictionary

  • Download the Data Dictionary Template from Moodle


  • Login