1 / 34

CS 405G: Introduction to Database Systems

CS 405G: Introduction to Database Systems. Lecture 5: Logical Design by Relational Model Instructor: Chen Qian Spring 2014. (1) 7 points. 0.5 for each mistake. (2) 3 points.

yaphet
Download Presentation

CS 405G: Introduction to Database Systems

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. CS 405G: Introduction to Database Systems Lecture 5: Logical Design by Relational Model Instructor: Chen Qian Spring 2014

  2. (1) 7 points. 0.5 for each mistake

  3. (2) 3 points. • No. If starting and ending dates are recorded in multi-valued attributes, we cannot determine which starting date is corresponding to which ending data.

  4. Today’s Outline • Relational Model Constraints (unfinished part of last class) • Update Operations • Insertion • Deletion • Update • Transaction • Dealing with Constraint Violations

  5. Relational Integrity Constraints • Integrity Constraints are conditionsthat must hold on allvalid relation instances. • There are four main types of constraints: • Domain constraints • The value of a attribute must come from its domain • Key constraints • Entity integrity constraints • Referential integrity constraints

  6. Primary Key Constraints • A set of fields is a candidate key(abbreviated as key)for a relation if : 1. No two distinct tuples can have same values in all key fields, and 2. Property 1 is not true for any subset of the key. • What if Part 2 is false? A super key: a set of fields that contains a key. • If there are multiple keys for a relation, one of the keys is chosen (by DBA) to be the primary key.

  7. Key Example • E.g., given a schema Student(sid: string, name: string, gpa: float) we have: • sid is a key for Students. (What about name?) The set {sid, gpa} is a superkey. • CAR (licence_num: string, Engine_serial_num: string, make: string, model: string, year: integer) • What is the candidate key(s) • Which one you may use as a primary key • What are the super keys

  8. Entity Integrity • Entity Integrity: The primary key attributes(PK) of each relation schema R cannot have null values in any tuple of r(R). • Other attributes of R may be similarly constrained to disallow null values, even though they are not members of the primary key.

  9. Foreign Keys, Referential Integrity • Foreign key: Set of fields in one relation that is used to `refer’ to a tuple in another relation. (Must correspond to primary key of the second relation.) Like a `logical pointer’. • Foreign key constraint: The foreign key in the referencing relation must match the primary key of the referenced relation. • E.g. sid is a foreign key referring to Students: • Student(sid: string, name: string, gpa: float) • Enrolled(sid: string, cid: string, grade: string) • If all foreign key constraints are enforced, referentialintegrity is achieved, i.e., no dangling references.

  10. Foreign Key constraints • Only students listed in the Students relation should be allowed to enroll for courses. Enrolled Students • Possible violation: Add <50000, History105, B> to Enrolled. • Possible violation: delete <53650, Smith, …> from Students.

  11. Other Types of Constraints • Semantic Integrity Constraints: • based on application semantics and cannot be expressed by the model per se • e.g., “the max. no. of hours per employee for all projects he or she works on is 56 hrs per week” • A constraint specification languagemay have to be used to express these • SQL-99 allows triggers and ASSERTIONS to allow for some of these

  12. Update Operations on Relations • Update operations • INSERT a tuple. • DELETE a tuple. • MODIFY a tuple. • Constraints should not be violated in updates

  13. Update Operations on Relations • In case of integrity violation, several actions can be taken: • Cancel the operation that causes the violation (REJECT option) • Perform the operation but inform the user of the violation • Trigger additional updates so the violation is corrected (CASCADE option, SET NULL option) • Execute a user-specified error-correction routine

  14. Key concept: Transaction • an atomic sequence of database actions (reads/writes) • takes DB from one consistent state to another transaction consistent state 1 consistent state 2

  15. Example • Here, consistency is based on our knowledge of banking “semantics” • In general, up to writer of transaction to ensure transaction preserves consistency • DBMS provides (limited) automatic enforcement, via integrity constraints • e.g., balances must be >= 0 Transaction “transfer $100 from Saving to checking” checking: $300 savings: $900 checking: $200 savings: $1000

  16. From E/R Diagrams to Relations • Called logical design (different from conceptual design) • Entity sets become relations with the same set of attributes. • Relationships become relations whose attributes are only: • The keys of the connected entity sets. • Attributes of the relationship itself.

  17. Design principles • KISS • Keep It Simple, Stupid • Avoid redundancy • Redundancy wastes space, complicates updates and deletes, promotes inconsistency • Capture essential constraints, but don’t introduce unnecessary restrictions • Use your common sense Luke Huan Univ. of Kansas

  18. Entity Set -> Relation name manf Beers Relation: Beers(name, manf)

  19. Relationship -> Relation • To represent a relationship, the attributes of the relation include: • 1. the primary key attributes of each participating entity set, becoming foreign keys. • 2. the descriptive attributes of the relationship set name duration name addr location Work employee department Work(employee name, dept name, duration)

  20. Relationship -> Relation • The set of nondescriptive attributes is a candidate key, if there are no key constraints. name duration name addr location Work employee department Work(employee name, dept name, duration)

  21. Relationship -> Relation • If there is a key constraint, • the key of the entity with an arrow is the candidate key of the relation. name duration name addr location manage employee department Manage(employee name, dept name, duration)

  22. Likes husband 2 1 Favorite Buddies Favorite(drinker name, beer name) wife Married Buddies(name1, name2) Married(husband name, wife name) Relationship -> Relation name name addr manf Drinkers Beers Likes(drinker name, beer name)

  23. Combining Relations name name addr manf Favorite Drinkers Beers It is OK to combine the relation for an entity-set E with the relation R for a many-one relationship from E to another entity set. Example: Drinkers(name, addr) and Favorite(drinker, beer) combine to make Drinker1(name, addr, favBeer).

  24. Likes Redundancy Combining Relations name name addr manf Drinkers Beers name addr beer Sally 123 Maple Bud Sally 123 Maple Miller Risk with Many-Many Relationships: Combining Drinkers with Likes would be a mistake. It leads to redundancy

  25. Handling Weak Entity Sets Relation for a weak entity set must include attributes for its complete key (including those belonging to other entity sets), as well as its own, nonkey attributes. An identifying (double-diamond) relationship is redundant and yields no relation.

  26. number name In Rooms Buildings capacity year In number Seats L/R? Translating weak entity sets • Remember the “borrowed” key attributes • Watch out for attribute name conflicts Building (building_name, year) Rooms (building_name, room_number, capacity) Seats (building_name, room_number, seat_number, left_or_right)

  27. Example name name Logins At Hosts time

  28. Must be the same At becomes part of Logins Example name name Logins At Hosts time Hosts(hostName) Logins(loginName, hostName, time) At(loginName, hostName, hostName2)

  29. Mapping of N-ary Relationship Types • For each n-ary relationship type R, where n>2, create a new relationship to represent R. • Include • all foreign keys of the participating entity types. • include any attributes of the n-ary relationship type

  30. Ternary relationship types. (a) The SUPPLY relationship.

  31. Mapping the n-ary relationship type SUPPLY

  32. Some exercise • Consider the relations Students, Faculty, Courses, Rooms, Enrolled, Teaches, and Meets. • 1. List all the foreign key constraints among these relations. • 2. Give an example of a (plausible) constraint involving one or more of these relations that is not a primary key or foreign key constraint.

  33. Some exercise • 1. • No foreign keys for Students, Faculty, Courses, Rooms • Enrolled: sid and cid should both have FKCs placed on them. (Real students must be enrolled in real courses.) • Teaches: fid and cid • Meets: cid and rno. • 2. • the length of sid, cid, and fid could be standardized; • limits could be placed on the size of the numbers entered into the credits, room/course capacity, and faculty salary; • an enumerated type should be assigned to the grade field • etc

  34. Next class • Relational algebra (hard part!)

More Related