Relational Databases

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
• Lets 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
• its 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
• Its 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 shouldnt 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
What is an entity?

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

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 dont
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)