Introduction to Database Management Systems

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

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)

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

Entity Sets instructor and student, with extensions instructor_ID instructor_name student-ID student_name

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) | e1E1,e2E2,…,eNEN}

Relationship Set advisor RELATIONSHIP RELATIONSHIP SET

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

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

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

Cardinality • A relationship can have rules about how often an entity can participate in a relationship • Restrict discussion to binaryrelationshipsfor 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

Mapping Cardinalities One to many One to one Note: Some elements in A and B may not be mapped to any elements in the other set

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

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

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 student instructor ID Name Major ID Name Salary Advisor Date

Showing Cardinality • Use an arrow to indicate the ‘one’ side of a cardinality (like a funnel!): 1 to 1 1 to many many to 1 many to many

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?

Practice • All together: Give an E-R diagram for a music database that includes artists, albums and tracks

Labeling Relationship Roles • Usually the context of a relationship is obvious, but sometimes not! • Most frequent example: relating an entity set to itself: Band BID Name Headlined Toured With Supporting Date

Binary vs. Non-Binary Relationships • Binary = relating two entity sets. Most common. • Non-Binary: more than two sets. Artist Producer Track AID Name PRID Name Company TID Name Recorded

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

Redundant Attributes • Big difference from a schema diagram: Relationship diamonds imply relationships, not common attributes! • Only necessary attributes should be shown: Instructor Department ID name salary dept_name dept_name building budget inst_dept

Redundant Attributes • Big difference from a schema diagram: Relationship diamonds imply relationships, not common attributes! • Only necessary attributes should be shown: Course Section course_id title credits course_id sec_id semester year building room_number capacity Course_sec

Redundant Attributes • Big difference from a schema diagram: Relationship diamonds imply relationships, not common attributes! • Only necessary attributes should be shown: Course Section course_id title credits course_id sec_id semester year building room_number capacity Course_sec WHAT’S WRONG HERE??

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 Course Section course_id title credits sec_id semester year building room_number capacity Course_sec

Practice • In groups: Give an E-R diagram for a doctor’s office that includes doctors, patients, tests, test results, and diagnoses

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!

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

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 Course Section course_id title credits sec_id semester year building room_number capacity CRS_SEC

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!

Relationship Primary Keys: Binary • Suppose a relationship set associates two entity sets A and B • Let PA and PBbe 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: PAUNION PB • One-to-One Relationship: PAOR 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.

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!

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

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

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 Course Section course_id title credits sec_id semester year CRS_SEC

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

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

ER Design Issues • Multivalued and Complex Attributes • Binary vs. N-Ary relationships • Location for relationship Attributes

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.

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

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

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 Cby 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 eiin the entity set E 2. add (ei , ai ) to RA 3. add (ei , bi) to RB 4. add (ei , ci ) to RC

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

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

Loan-branch Payment Branch Loan Account Employee Customer borrower E-social-security Employee-name Telephone-number Start-date Social-security Customer-name Customer-street Customer-city Loan-number amount Payment-number Payment-date Payment-amount Account-number Balance Branch-name Branch-city assets type Cust-Banker Access-Date Loan-payment Depositor manager Works-for worker

Introduction to Database Management Systems