Announcements

1
Announcements

• Reading for Wednesday
• 3.5 3.9
• Homework 2 - Due 9/22
• Select paper topic and email me your choice

2
The Entity-Relationship Model Entities,
Attributes, and Keys

• Lecture 5

3
Purpose of E-R Model

4
Symbols used in E-R Diagram

• Entity rectangle
• Attribute oval
• Relationship diamond
• Link - line

5
Entity

• Object that exists and that can be distinguished
  from other objects
• Can be person, place, event, object, concept in
  the real world
• Can be physical object or abstraction
• In E-R diagram, rectangle represents entity set

6
Entity Vocabulary

• Entity instance is a particular person, place,
  etc.
• Entity type is a category of entities
• Entity set is a collection of entities of same
  type
• Entity set must be well-defined

7
Attributes

• Domain

8
Attribute Variations

• May have null values for some entity instances
• May be multi-valued
• May be composite
• May be derived

9
Keys

• Superkey
• Composite key
• Candidate key
• Primary key

10
More Keys

• Alternate key
• Secondary key
• Foreign key term used in relational model (but
  not in the E-R model) for an attribute that is
  primary key of a table and is used to establish a
  relationship, usually with another table, where
  it appears as an attribute also

11
Recognizing Entities, Attributes, and Keys

• A dentists office needs to keep information
  about patients, the number of visits they make to
  the office, work that must be preformed,
  procedures preformed during the visit, charges
  and payments for the treatment, and laboratory
  supplies and services. Assume there is only one
  dentist, so there is no need to store information
  about the dentist in the database. There are
  several hundred patients. Patients make many
  visits, and the database should store information
  about the services performed during each visit,
  and the charges for each of the services. there
  is a standard list of charges, kept outside of
  the database. The office uses three dental
  laboratories that provides supplies and services,
  such as fabricating dentures.

12
(No Transcript)
13
Recognizing Entities, Attributes, and Keys

• An interior design firm would like to have a
  database to represent its operations. A client
  (customer) requests that the firm perform a job
  such as decorating a new home, redecorating
  rooms, locating and purchasing furniture, and so
  forth. One of the firms decorators is placed in
  charge of each job. For each job, the firm
  provides an estimate of the amount of time and
  money required for the entire job. Some of the
  work for a job, such as planning furniture
  placement, is done by the decorator in charge of
  the job. In addition, the firm might hire
  contractors to work on a daily or hourly basis on
  a particular job. A job might also include
  several activities, such as painting, installing
  floor covering, fabricating draperies,
  wallpapering, constructing, installing cabinets,
  and so on. These activities are done by
  contractors hired by the firm .The contractor
  provides an estimate for each activity. An
  activity or job might also require materials such
  as paint or lumber, and the firm has to keep
  track of the cost of materials for each activity
  or job, in order to bill the client. The
  database should store the estimated costs of all
  activities and all jobs.

14
(No Transcript)
15
Recognizing Entities, Attributes, and Keys

• An automobile body repair shop needs to keep
  information about its operations. Customers
  initially bring their cars to the shop for an
  estimate of the repairs. A mechanic looks at the
  car and estimates the cost and time required for
  the entire job. If the customer accepts the
  estimate, a job number is assigned and the
  customers name and contact information the
  cars license plate number, make, model, and
  year and a list of repairs needed is recorded.
  The customer than makes an appointment to bring
  in the car on a specified date. When the car is
  brought in for repairs, the work begins. The shop
  keeps track of the charges for the parts and
  labor as they accumulate. Only one mechanic
  works on the car for the entire job. A job might
  include several repairs (e.g., replacing the left
  fender, painting the passenger door). The time
  actually spent for each repair is recorded and
  used to calculate the cost of labor, using a
  fixed hourly rate.

16
(No Transcript)