1 / 27

Introduction to Relational Databases

Learn about the widely used approach of relational databases for storing and retrieving data, including the benefits of using relations and the basics of one-to-many relationships.

lconwell
Download Presentation

Introduction to Relational Databases

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. Relational Databases • “Relational Database” is a very widely used approach to storing and retrieving data • Many vendors: • Microsoft (Access, FoxPro, BackOffice. . .) • Oracle • Sybase • One database may contain many tables • Tables are connected with “relations” • Hence the term: “relational database”

  2. Why use relations? • Store data more efficiently • no need to duplicate information that is common to many records • Example: if many employees are in the same location, why not store the location information just once? • Make changes more easily and reliably • Let’s say the location information changes; if you know it is just stored in one place, then you can change it in one place • Facilitate searching and retrieval • Trust me on this: if you put everything into a single, big spreadsheet, you could never formulate a reliable query to retrieve the data you need

  3. This stuff is non-intuitive at first • Expect to be puzzled and frustrated at first • it’s not just you -- nearly everyone is • Most people want to store all the information related to an entity in the table that describes that entity • Example: put my work history and educational history into the table that holds my name, address, and phone number • THIS IS A HUGE, HORRIBLE ERROR -- NEVER DO IT • Store related information in separate, related tables • It’s hard to think this way at first • It seems to take more work, but it is critical in the long run • The basic tool: one-to-many relationships

  4. Relationships in ActRep example • If only one person used the ActRep database, then all of the “activities” in that database would “belong” to that person • If multiple people are using the database, then we need to track which activity belongs to which person • Question: can a person have more than one activity? • Answer: yes, hopefully they are doing many activities • Question: can an activity have more than one “person”? • Answer A: this system tracks individual achievements only; each activity has one and only one person • Answer B: shouldn’t we allow (and reward) group activities?

  5. One-to-Many relationships • Example: Work history • One person can have many previous jobs, but each previous job was held by a particular, unique person • Example: Dependents (for benefits) • One employee can have many dependents, but each dependent is assigned to one employee • BUT: What if both parents are employees? • Example: Job descriptions and job openings • One job description may have many job openings, but each opening is for a particular job. • BUT: what if the opening is split between jobs?

  6. Relationships define DB structure • Entities & relations among them are DESIGN CHOICES • there are frequently reasonable alternatives • there are also better and worse choices • The design of entities and relations imposes IMPORTANT constraints on how data is stored and how it is retrieved • Relational databases are much more efficient and flexible than other ways of storing data • BUT poor choices in the “ER model” will hamper the usefulness of the database

  7. Nearly anything: people, things, events, categories, etc. Entities are stored in tables For each kind of entity, you need an additional table But you can store any number of instances of an entity in the same table Confused? It gets worse, and then it gets easier. What is an entity?

  8. Need to set up tables first! • Have to define tables (entities) first, before we can create relations • Need to define what fields will be used to relate tables to each other • Related fields are usually ID numbers, or codes, or internally generated keys (autonumber) • Must MATCH in datatype (number, text, etc.) • Names do not have to match

  9. ActRep Example • Who is reporting these activities? • I created a table for “people” -- a new entity! (Digression into ActRep example…)

  10. Three entities so far... • People, activities, and categories... (Digression into ActRep example…)

  11. How are they related? Click here to view and edit relationships

  12. For which entities do you want to view or edit the relationships?

  13. No relationships yet...

  14. Need to edit “activities” table • Add fields that can be used to create relations • Add a field for PersonID • type must be number, to match the type of the PersonID field in the People table • Change the “category” field to CategoryID • type must be number, to match the type of the CategoryID field in the Category table (which is also new) (Digression into ActRep example…)

  15. New table definition (Digression into ActRep example…)

  16. Will the category lookup work? • The combo box and the lookup table are still connected, and they seem to work…. • But we changed the field type from text to number... (Digression into ActRep example…)

  17. Ooops! We broke something • We changed the CategoryID from text to Number, but the Lookup table is still putting text into that field. This results in an error message. (Digression into ActRep example…)

  18. Dealing with Frustration • Take your time and try to relax • Read error messages carefully • Get help: • Read documentation on the subject • Check out the on-line help • Discuss the situation with classmates • Call the professor • Be prepared to back up and start over from scratch • Going through the steps again can help you identify things you may have overlooked • It may also make the problem go away

  19. Change the category table • Add a numeric field for the categoryID (Digression into ActRep example…)

  20. Change the lookup properties • “Bound column” determines which field gets saved from the lookup table This stuff is tricky, expect to get frustrated (Digression into ActRep example…)

  21. How to Create Relationships Click here... Drag to here...

  22. Relationship have properties Fields MUST have same data type, but don’t need the same name This is a One-to-Many relationship

  23. What is Referential integrity?

  24. Referential integrity. . . • Prevents data from getting “out of synch” • Examples: • Should we allow users to create an “activity” performed by a person that does not exist in the person table? • Should we allow users to create a dependent for a person who is not a valid employee? • “Cascade update/cascade delete…” • If I change the “one” table, it will update/delete related records in the “many” table • Like any input validation rule, it can be VERY frustrating for users

  25. You can add/delete relationships Right-button on the lineto edit/delete

  26. Activities, people, categories One category, many activities One person, many activities

  27. Why bother? • Good ER models are critical to database design • You will get a head-full of them in HR/Vantage • Exercise: • What are the main entities in the “resume” database? • How are they related? • Next Lesson: queries (searching, sorting…)

More Related