6830 6814 Lecture 2 Data Models Sam Madden 9112017 PS1 Out Those who cannot remember the past are doomed to repeat it Zoo Data Model Entity Relationship Diagram Animal Cage Keeper keeps Name ID: 761559
Download Presentation The PPT/PDF document "6.830 / 6.814 Lecture 2 Data Models Sam ..." is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
6.830 / 6.814 Lecture 2Data Models Sam Madden9/11/2017PS1 Out “Those who cannot remember the past are doomed to repeat it”
Zoo Data Model Entity Relationship Diagram Animal Cage Keeper keeps Name 1 name Time 1 feedTime Age Name Species 1 1 1 name age species Animals have names, ages, species Keepers have names Cages have cleaning times, buildings Animals are in 1 cage; cages have multiple animals Keepers keep multiple cages, cages kept by multiple keepers 1 1 1 Building 1 bldg entity entity entity contains 1 relationship n n n
Zoo Tables id name age species cageno 1Sam3 Salamander 12Aaron12 Giraffe1 3Sally 1Student2 no feedtime building1 12:301 21:302 id name 112:30 21:30 kid cageno1 112 21 AnimalsCages Keepers Keeps Foreign Key Primary Key Primary Key PrimaryKey Foreign Key Foreign Key
Cages in Building 32 Imperative for each row a in animals for each row c in cages if a.cageno = c.no and c.bldg = 32 output aDeclarativeSELECT a.name FROM animals AS a, cages AS c WHERE a.cageno = c.no AND c.bldg = 32 JOIN NESTED LOOPS
Average Age of Bears DeclarativeSELECT AVG(age) FROM animals WHERE species = ‘bear’
Complex Queries Find pairs of animals of the same species and different genders older than 1 year:SELECT a1.name,a2.name FROM animals as a1, animals as a2 WHERE a1.gender = M and a2.gender = F AND a1.species = a2.species AND a1.age > 1 and a2.age > 1 Find cages with salamanders fed later than the average feedtime of any cage: SELECT cages.cageid FROM cages, animals WHERE animals.species = ’salamander'AND animals.cageid = cages.cageidAND cages.feedtime > (SELECT AVG(feedtime) FROM cages ) “self join” “nested queries”
Modified Zoo Data Model Slightly different than last time: Each animal in 1 cage, multiple animals share a cage Each animal cared for by 1 keeper, keepers care for multiple animals Animals have feed times, not cages
Example IMS Hierarchy Jane (keeper) (HSK 1) Sam, salamander, … (2) 1, 100sq ft, … (3) Mike, giraffe , … (4) 2, 1000sq ft, … (5) Sally, student, … (6) 1, 100sq ft, … (7) Joe (keeper) (8) Keepers segment A1 Segment A2 Segment A3 Segment C1 Segment C2 Segment C3 Segment
IMS Commands GU (segment type, predicate) - get unique Optional predicate can be used when root is indexed Finds the first segment satisfying a predicate, and set cursor there GN (seg, pred) - get next key in hierarchical ordermoving on to other parentsoptional predicatesearch begins from last GU/GN call GNP ( seg) - get next record of the specified segment type, stopping when leaving current parent D - delete this recordI - add a new record
Example IMS Programs Find the cages that Jane keeps GetUnique( Keepers, name = "Jane") Until done: cageid = GetNextParent (cages).no print cageid
Example IMS Programs Find the keepers that keep cage 6 GetUnique(keepers) GetNextParent (cages, id = 6) Until done: GetNext(keepers) GetNextParent(cages, id = 6)
Study break #1 Consider a course schema with students, classes, rooms (each has a number of attributes) Classes in exactly one room Students in zero or more classes Classes taken by zero or more studentsRooms host zero or more classes Classes Students Rooms isin takenby
Questions Describe one possible hierarchical schema for this dataIs there a hierarchical representation that is free of redundancy? Classes Students Rooms isin takenby
Example CODASYL Hierarchy
Example CODASYL Program Find the cages that Joe keepsFind keepers (name = 'Joe')Until done: Find next animal in caredforby Find cage in livesin
Simplified Zoo Relations name age species cageno keptby feedtime mike 13 giraffe 1 1 10:00am sam 3 salam 2 1 11:00am sally 1 student 1 2 1:00pm keeper name 1 jenny 2 joe keepers animals cages cageno bldg 1 2 2 3 Primary Key Foreign Key
Study Break # 2 Schema: classes: (cid , c_name , c_rid , …) rooms: (rid, bldg, …) students: (sid, s_name, …) takes: (t_sid , t_cid) SELECT s_name FROM student,takes,classes WHERE t_sid = sid AND t_cid=cid AND c_name=‘6.830’
Questions Write an equivalent relational algebra expression for this queryAre there other possible expressions?Do you think one would be more “efficient” to execute? Why? SELECT s_name FROM student,takes,classes WHERE t_sid = sid AND t_cid = cid AND c_name=‘6.830’