1 / 19

D111-Data Fundamentals

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.

feryal
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. D111-Data Fundamentals Week 5

  2. Objectives • Recap • Primary Keys • Foreign Keys • Data Dictionary • Creating the Tables in MS Access • Creating relationships in MS Access

  3. 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."

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

  5. Entity Matrix

  6. Conceptual Data Model

  7. Create the Associative Entity

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

  9. Read the relationship between entities from Left to Right We know the relationship from the CLIENT point of view is One to Many

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

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

  12. Possible Solution

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

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

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

  16. Visio

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

  18. Data Dictionary • Download the Data Dictionary Template from Moodle

More Related