introduction to database management systems
DESCRIPTION
Introduction to Database Management Systems. Dr. Adam Anthony Fall 2012. Lecture Overview. Database Design Process Introduction to Entities and Relationships Practice Exercise. Design Phases. Data Needs Assessment Talk to experts, users about what data should be stored Modeling Phase - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/1.jpg)
INTRODUCTION TO DATABASE MANAGEMENT SYSTEMSDr. Adam AnthonyFall 2012
![Page 2: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/2.jpg)
Lecture Overview Database Design Process Introduction to Entities and Relationships Practice Exercise
![Page 3: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/3.jpg)
Design Phases Data Needs Assessment
Talk to experts, users about what data should be stored
Modeling Phase Communicates how the data is represented and
interacts Functional Requirements
Document detailing how the data will be used on a daily basis
Implementation Translate model to a schema, implement in system
![Page 4: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/4.jpg)
Cost of Re-design Group activity:
Discuss what you would literally have to do if we decided to change the university database so that each section had a unique DB identifier:
section(SEC_DBID, course_id, sec_id, semester, year, building room_no, time_slot_id)
And then we want to update the TAKES and TEACHES relations to use SEC_DBID instead of (course_id,sec_id,semester,year)
![Page 5: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/5.jpg)
Identifying Entity Sets Entity: a ‘thing’ or object in the real world,
distinguishable from other objects Has Properties (Attributes) that should
uniquely identify a single entity Entity set: collection of things that all have
the same properties, but with different values Like a relation, it is considered in the abstract,
without thinking about actual entities that are in the set
An entity set with particular members is called an extension
![Page 6: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/6.jpg)
Entity Sets instructor and student, with extensions
instructor_ID instructor_name student-ID student_name
![Page 7: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/7.jpg)
Identifying Relationship Sets Relationship: An association between
two or more entities May be same or different types!
Relationship sets: Given N entity sets that participate in a
relationship: E1,E2,…,EN
A Relationship Set is: {(e1,e2,…,eN) | e1E1, e2E2,…, eNEN}
![Page 8: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/8.jpg)
Relationship Set advisorRELATIONSHIP
RELATIONSHIPSET
![Page 9: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/9.jpg)
Relations Can Have Attributes! Relations also sometimes have extra info
Date/time for when transaction takes place Payment Amount for purchase relation Grade in the Student-Takes-Section
relationship Simple idea to grasp, but can impact
how we assign keys (next slide!)
![Page 10: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/10.jpg)
Relationship Sets For instance, the advisor relationship set between entity
sets instructor and student may have the attribute date which tracks when the student started being associated with the advisor
![Page 11: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/11.jpg)
Keys Entity-relationship design also uses
primary keys to identify unique values Entity: usually identified by designer Relationship: Keys are built using the
primary keys of the related entity sets Depending on nature of relationship, some
combination of one or all of the primary keys will become the primary key for the relation
Advisor example
![Page 12: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/12.jpg)
Cardinality A relationship can have rules about how
often an entity can participate in a relationship Restrict discussion to binary
relationships for now! One-To-One: Items in both sets can
relate to at most one item in the other set
One-To-Many: A is one-to-many with B when an item in A can relate to any number of items in B
Many-To-One: Like above, but in other direction
Many-To-Many: unrestricted relation
![Page 13: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/13.jpg)
Mapping Cardinalities
One to one One to many
Note: Some elements in A and B may not be mapped to any elements in the other set
![Page 14: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/14.jpg)
Mapping Cardinalities
Many to one Many to many
Note: Some elements in A and B may not be mapped to any elements in the other set
![Page 15: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/15.jpg)
In-Class Example Let’s think of a database domain, and
then come up with: At least 3 entity sets At least 2 relationship sets Primary Keys Cardinality of relations
![Page 16: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/16.jpg)
ER Diagram Basics Rectangle with header: Entity Set Diamond: Relationship Set Plain Rectangle + dashed line:
Relationship Attribute Lines link entity sets to relationship sets
Single: optional participation Double: total participation
studentIDNameMajor
instructorIDNameSalary
Advisor
Date
![Page 17: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/17.jpg)
Showing Cardinality Use an arrow to indicate the ‘one’ side of
a cardinality (like a funnel!):
1 to many
1 to 1
many to 1many to many
![Page 18: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/18.jpg)
Advanced Cardinality You can be very precise about how many
connections are allowed using numeric notation:
Can we do the same with double lines and arrows?
![Page 19: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/19.jpg)
Practice All together: Give an E-R diagram for a
music database that includes artists, albums and tracks
![Page 20: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/20.jpg)
Labeling Relationship Roles Usually the context of a relationship is
obvious, but sometimes not! Most frequent example: relating an
entity set to itself: Band
BIDName
Toured With
Date
HeadlinedSupporting
![Page 21: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/21.jpg)
Binary vs. Non-Binary Relationships Binary = relating two entity sets. Most
common. Non-Binary: more than two sets.
ArtistAIDName
ProducerPRIDNameCompany
TrackTIDName
Recorded
![Page 22: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/22.jpg)
Cardinality Constraints on Ternary Relationship
We allow at most one arrow out of a ternary (or greater degree) relationship to indicate a cardinality constraint
E.g., an arrow from proj_guide to instructor indicates each student has at most one guide for a project
If there is more than one arrow, there are two ways of defining the meaning. E.g., a ternary relationship R between A, B and C with
arrows to B and C could mean 1. each A entity is associated with a unique entity from B
and C or 2. each pair of entities from (A, B) is associated with a unique C entity, and each pair (A, C) is associated with a unique B
Each alternative has been used in different formalisms To avoid confusion we outlaw more than one arrow
![Page 23: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/23.jpg)
Redundant Attributes Big difference from a schema diagram:
Relationship diamonds imply relationships, not common attributes!
Only necessary attributes should be shown: InstructorIDnamesalarydept_name
Departmentdept_na
mebuildingbudget
inst_dept
![Page 24: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/24.jpg)
Redundant Attributes Big difference from a schema diagram:
Relationship diamonds imply relationships, not common attributes!
Only necessary attributes should be shown: InstructorIDnamesalarydept_name
Departmentdept_na
mebuildingbudget
inst_dept
![Page 25: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/25.jpg)
Redundant Attributes Big difference from a schema diagram:
Relationship diamonds imply relationships, not common attributes!
Only necessary attributes should be shown:
Coursecourse_idtitlecredits
Sectioncourse_idsec_idsemesteryearbuilding room_numbercapacity
Course_sec
![Page 26: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/26.jpg)
Redundant Attributes Big difference from a schema diagram:
Relationship diamonds imply relationships, not common attributes!
Only necessary attributes should be shown:
Coursecourse_idtitlecredits
Sectioncourse_idsec_idsemesteryearbuilding room_numbercapacity
Course_sec
WHAT’S WRONG HERE??
![Page 27: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/27.jpg)
Weak Entity Sets Some entity sets only make sense in the context of
other entity sets A section cannot exist without a course A child cannot exist without a mother/father Other examples?
The underlined portion in section is the discriminator, which, when combined with primary key from Course, will uniquely identify a section
Coursecourse_idtitlecredits
Sectionsec_idsemesteryearbuilding room_numbercapacity
Course_sec
![Page 28: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/28.jpg)
Practice In groups: Give an E-R diagram for a
doctor’s office that includes doctors, patients, tests, test results, and diagnoses
![Page 29: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/29.jpg)
Converting from ER to a Schema Why not just design at the schema level?
Flow charts are more interactive Easier for non-technical workers to
comprehend Relations are more obvious Interactions easier to track Conceptual level vs Logical level
Regardless, we have to turn these into a database eventually!
![Page 30: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/30.jpg)
Converting a Basic Strong Entity Set To start: Just make a schema with the
same name, with all attributes, and the same primary key
When we process relations, more may be added, so be prepared to come back to these!
Use our in-class example from last time to demonstrate
![Page 31: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/31.jpg)
Converting a Weak Entity Set Also pretty easy:
All attributes for the weak entity, plus All primary key attributes for the associated
strong entity Primary key will be the primary key of
coupled strong entity, plus the discriminator attributes for the weak entity
Coursecourse_idtitlecredits
Sectionsec_idsemesteryearbuilding room_numbercapacity
CRS_SEC
![Page 32: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/32.jpg)
Converting Relationship Sets—Preliminary Step
A relationship set schema needs attributes from two sources: All primary key attributes from the related
entity sets All attributes attached to the relationship
set itself Choosing the primary key is tough!
![Page 33: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/33.jpg)
Relationship Primary Keys: Binary Suppose a relationship set associates two entity sets A
and B Let PA and PB be the set of attributes that make the
primary key for A and B respectively Primary key for the schema for a:
Many-to-Many Relationship: PA UNION PB One-to-One Relationship: PA OR PB Many-to-One: PA One-to-Many: PB
If you want to allow multi-matches between entities, then also include a distinguishing relationship attribute to complete the key.
![Page 34: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/34.jpg)
Relationship Primary Keys: Non-Binary
No Arrows: union of all related entity primary keys
One Arrow: union of all related entity primary keys EXCEPT for the one the arrow points to!
![Page 35: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/35.jpg)
Relationships and Foreign Keys Foreign keys are easy to identify now
too! For each relationship set you convert,
add a foreign key back to each primary key of the referenced entities
![Page 36: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/36.jpg)
Redundancy and Simplification Simple conversion procedure followed so
far, but there are problems Lots of information seems repeated Some information seems overly
segmented Careful analysis shows that some
relations can be safely dropped, and others can be combined
![Page 37: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/37.jpg)
Eliminating Unnecessary Schema Relations Weak entities introduce unneeded relations
Course(course_id, title, credits)Section(course_id,sec_id,semester,year)CRS_SEC(course_id,sec_id,semester,year)
Section and CRS_SEC are identical—remove CRS_SEC Exception: CRS_SEC might have attributes!
Just ‘roll in’ to the weak entitiy’s schema relation
Coursecourse_idtitlecredits
Sectionsec_idsemesteryear
CRS_SEC
![Page 38: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/38.jpg)
Redundancy of Schemas Many-to-one and one-to-many relationship sets that are total
on the many-side can be represented by adding an extra attribute to the “many” side, containing the primary key of the “one” side
Example: Instead of creating a schema for relationship set inst_dept, add an attribute dept_name to the schema arising from entity set instructor
![Page 39: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/39.jpg)
Redundancy of Schemas (Cont.)
For one-to-one relationship sets, either side can be chosen to act as the “many” side That is, extra attribute can be added to
either of the tables corresponding to the two entity sets
If participation is partial on the “many” side, replacing a schema by an extra attribute in the schema corresponding to the “many” side could result in null values
![Page 40: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/40.jpg)
ER Design Issues Multivalued and Complex Attributes Binary vs. N-Ary relationships Location for relationship Attributes
![Page 41: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/41.jpg)
Multivalued and Composite Attributes
Sound database theory requires single-valued attributes, but ER design allows for more complex ideas
Name, Address: composite attributes Phone-number: multi-valued
Means you can have more than one phone number
Age(): can be computed from date_of_birth
Read book to see how these are dealt with. I prefer to just not use them, since they are not quickly translated into a schema.
![Page 42: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/42.jpg)
Multi-Valued Representation Mutli-valued attributes are common
Phone numbers Departments Bank Account Numbers
Design pattern: Represent the attribute as a new entity (phone) Create a one-to-many relationship between
original, new entity (instructor_phone) Nice, because you an add extra information when
relevant Phones usually have a location or are Mobile or Fax
![Page 43: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/43.jpg)
Binary Vs. Non-Binary Relationships Some relationships that appear to be non-
binary may be better represented using binary relationships E.g., A ternary relationship parents, relating a
child to his/her father and mother, is best replaced by two binary relationships, father and mother Using two binary relationships allows partial information
(e.g., only mother being know) But there are some relationships that are naturally
non-binary Example: proj_guide
![Page 44: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/44.jpg)
Converting Non-Binary Relationships to Binary Form In general, any non-binary relationship can be represented
using binary relationships by creating an artificial entity set. Replace R between entity sets A, B and C by an entity set E, and
three relationship sets: 1. RA, relating E and A 2. RB, relating E and B 3. RC, relating E and C Create a special identifying attribute for E Add any attributes of R to E For each relationship (ai , bi , ci) in R, create 1. a new entity ei in the entity set E 2. add (ei , ai ) to RA
3. add (ei , bi ) to RB 4. add (ei , ci ) to RC
![Page 45: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/45.jpg)
Converting Non-Binary Relationships (Cont.) Also need to translate constraints
Translating all constraints may not be possible There may be instances in the translated
schema thatcannot correspond to any instance of R
We can avoid creating an identifying attribute by making E a weak entity set (described shortly) identified by the three relationship sets
![Page 46: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/46.jpg)
Placement of Relationship Attributes If a relationship has attributes, we have
to decide where they go If many-to-many, there will be a table in the
schema If one-to-one, one-to-many, or many-to-
one, then we may have simplified the schema to eliminate the relationship table Keep the attributes on the ‘many’ side, along
with the referencing attribute
![Page 47: Introduction to Database Management Systems](https://reader036.vdocuments.us/reader036/viewer/2022062301/56815fa3550346895dce9f6f/html5/thumbnails/47.jpg)
PaymentPayment-numberPayment-datePayment-amount
CustomerSocial-securityCustomer-nameCustomer-streetCustomer-city
EmployeeE-social-securityEmployee-nameTelephone-numberStart-date
LoanLoan-numberamount
BranchBranch-nameBranch-cityassets
AccountAccount-numberBalance
Loan-payme
ntLoan-branc
h borrower
Cust-Banke
r
type
Works-for
manager
worker
Depositor
Access-Date