1 / 37

CS 405G: Introduction to Database Systems

CS 405G: Introduction to Database Systems. Instructor: Jinze Liu Fall 2009. 1010111101. Review. A data model is a group of concepts for describing data. What are the two terms used by ER model to describe a miniworld? Entity Relationship What makes a good database design.

mireya
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 Instructor: Jinze Liu Fall 2009

  2. 1010111101 Review • A data model is • a group of concepts for describing data. • What are the two terms used by ER model to describe a miniworld? • Entity • Relationship • What makes a good database design • Student (sid: string, name: string, login: string, age: integer, gpa:real) 10/23/2014 10/23/2014 2 2

  3. Topics Next • More case study • Conversion of ER models to Schemas 10/23/2014 10/23/2014 3 3

  4. ER Case study • Design a database representing cities, counties, and states • For states, record name and capital (city) • For counties, record name, area, and location (state) • For cities, record name, population, and location (county and state) • Assume the following: • Names of states are unique • Names of counties are only unique within a state • Names of cities are only unique within a county • A city is always located in a single county • A county is always located in a single state 10/23/2014 4

  5. Case study : first design • County area information is repeated for every city in the county • Redundancy is bad. • What else? • State capital should really be a city • Should “reference” entities through explicit relationships name name In Cities States population capital county_name county_area 10/23/2014 10/23/2014 5 5

  6. Case study : second design • Technically, nothing in this design could prevent a city in state X from being the capital of another state Y, but oh well… name Cities population In IsCapitalOf name In Counties States name area 10/23/2014 10/23/2014 6 6

  7. Database Design 10/23/2014 10/23/2014 7 7

  8. Attributes (column headers) Tuples (rows) A Relation is a Table name manf Winterbrew Pete’s Bud Lite Anheuser-Busch Beers

  9. Schemas • Relation schema = relation name + attributes, in order (+ types of attributes). • Example: Beers(name, manf) or Beers(name: string, manf: string) • Database = collection of relations. • Database schema = set of all relation schemas in the database.

  10. Why Relations? • Very simple model. • Often matches how we think about data. • Abstract model that underlies SQL, the most important database language today. • But SQL uses bags, while the relational model is a set-based model.

  11. From E/R Diagrams to Relations • 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.

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

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

  14. Combining Relations • 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).

  15. Redundancy Risk with Many-Many Relationships • Combining Drinkers with Likes would be a mistake. It leads to redundancy, as: name addr beer Sally 123 Maple Bud Sally 123 Maple Miller

  16. 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. • A supporting (double-diamond) relationship is redundant and yields no relation.

  17. Example name name Logins At Hosts time

  18. 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)

  19. A (Slightly) Formal Definition • A database is a collection of relations(or tables) • Each relation is identified by a name and a list of attributes (or columns) • Each attribute has a name and a domain (or type) • Set-valued attributes not allowed 19 19

  20. Schema versus instance • Schema (metadata) • Specification of how data is to be structured logically • Defined at set-up • Rarely changes • Instance • Content • Changes rapidly, but always conforms to the schema • Compare to type and objects of type in a programming language 10/23/2014 10/23/2014 20 20

  21. Example • Schema • Student (SID integer, name string, age integer, GPA float) • Course (CID string, title string) • Enroll (SID integer, CID integer) • Instance • { h142, Bart, 10, 2.3i, h123, Milhouse, 10, 3.1i, ...} • { hCPS116, Intro. to Database Systemsi, ...} • { h142, CPS116i, h142, CPS114i, ...} 10/23/2014 10/23/2014 21 21

  22. Relational Integrity Constraints • Constraints areconditions that must hold on all valid relation instances. There are four main types of constraints: • Domain constraints • The value of an attribute must come from its domain • Key constraints • Entity integrity constraints • Referential integrity constraints 10/23/2014 10/23/2014 22 22

  23. Primary Key Constraints • A set of fields is a candidate keyfor a relation if : 1. No two distinct tuples can have same values in all key fields, and 2. This is not true for any subset of the key. • Part 2 false? A superkey. • If there’s >1 key for a relation, one of the keys is chosen (by DBA) to be the primary key. • 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. 10/23/2014 Jinze Liu @ University of Kentucky 23

  24. Key Example • 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 10/23/2014 10/23/2014 24 24

  25. Entity Integrity • Entity Integrity: The primary key attributes PK of each relation schema R in S 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. 10/23/2014 10/23/2014 25 25

  26. Foreign Keys, Referential Integrity • Foreignkey : 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’. • 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. • Can you name a data model w/o referential integrity? • Links in HTML! 10/23/2014 Jinze Liu @ University of Kentucky 26

  27. Foreign Keys • Only students listed in the Students relation should be allowed to enroll for courses. Enrolled Students • Or, use NULL as the value for the foreign key in the referencing tuple when the referenced tuple does not exist 10/23/2014 Jinze Liu @ University of Kentucky 27

  28. In-Class Exercise • (Taken from Exercise 5.16) • Consider the following relations for a database that keeps track of student enrollment in courses and the books adopted for each course: • STUDENT(SSN, Name, Major, Bdate) • COURSE(Course#, Cname, Dept) • ENROLL(SSN, Course#, Quarter, Grade) • BOOK_ADOPTION(Course#, Quarter, Book_ISBN) • TEXT(Book_ISBN, Book_Title, Publisher, Author) • Draw a relational schema diagram specifying the foreign keys for this schema. 10/23/2014 10/23/2014 28 28

  29. In-Class Exercise (Taken from Exercise 5.16) Consider the following relations for a database that keeps track of student enrollment in courses and the books adopted for each course: STUDENT(SSN, Name, Major, Bdate) COURSE(Course#, Cname, Dept) ENROLL(SSN, Course#, Quarter, Grade) BOOK_ADOPTION(Course#, Quarter, Book_ISBN) TEXT(Book_ISBN, Book_Title, Publisher, Author) Draw a relational schema diagram specifying the foreign keys for this schema. Jinze Liu @ University of Kentucky

  30. 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 language may have to be used to express these • SQL-99 allows triggers and ASSERTIONS to allow for some of these 10/23/2014 10/23/2014 30 30

  31. Update Operations on Relations • Update operations • INSERT a tuple. • DELETE a tuple. • MODIFY a tuple. • Constraints should not be violated in updates Jinze Liu @ University of Kentucky

  32. Example • We have the following relational schemas • Student(sid: string, name: string, gpa: float) • Course(cid: string, department: string) • Enrolled(sid: string, cid: string, grade: character) • We have the following sequence of database update operations. (assume all tables are empty before we apply any operations) • INSERT<‘1234’, ‘John Smith’, ‘3.5> into Student Jinze Liu @ University of Kentucky

  33. Example (Cont.) • INSERT<‘647’, ‘EECS’> into Courses • INSERT<‘1234’, ‘647’, ‘B’> into Enrolled • UPDATE the grade in the Enrolled tuple with sid = 1234 and cid = 647 to ‘A’. • DELETE the Enrolled tuple with sid 1234 and cid 647 Jinze Liu @ University of Kentucky

  34. Exercise • INSERT<‘108’, ‘MATH’> into Courses • INSERT<‘1234’, ‘108’, ‘B’> into Enrolled • INSERT<‘1123’, ‘Mary Carter’, ‘3.8’> into Student Jinze Liu @ University of Kentucky

  35. Exercise (cont.) • A little bit tricky • INSERT<‘1125’, ‘Bob Lee’, ‘good’> into Student • Fail due to domain constraint • INSERT<‘1123’, NULL, ‘B’> into Enrolled • Fail due to entity integrity • INSERT <‘1233’,’647’, ‘A’> into Enrolled • Failed due to referential integrity Jinze Liu @ University of Kentucky

  36. Exercise (cont.) • A more tricky one • UPDATE the cid in the tuple from Course where cid = 108 to 109 Jinze Liu @ University of Kentucky

  37. 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 Jinze Liu @ University of Kentucky

More Related