Slide 1Slide 2Zoo Data Model Entity Relationship DiagramOur ZooSQL – Structured Query LanguageNames of ShrewsCages in Building 32Average Age of BearsComplex QueriesComplex Queries 2Introduction to Databases6.830 Lecture 1Sam [email protected] Data ModelEntity Relationship DiagramAnimalAnimalCageCageKeeperKeeperkeepsNameName1nameTimeTime1feedTimeAgeAgeNameNameSpeciesSpecies111nameagespeciesAnimals have names, ages, speciesKeepers have namesCages have cleaning times, buildingsAnimals are in 1 cage; cages have multiple animalsKeepers keep multiple cages, cages kept by multiple keepers111BuildingBuilding1bldgentityentityentitycontains1relationshipOur ZooLankySlimySally the StudentSam the SalamanderMike the GiraffeSQL – Structured Query LanguageSELECT field1, …, fieldM FROM table1, … WHERE condition1, …INSERT INTO table VALUES (field1, …)UPDATE table SET field1 = X, …WHERE condition1,…Names of Shrews•Imperativefor each row r in animalsif r.species = ‘giraffe’output r.name•DeclarativeSELECT r.name FROM animalsWHERE r.species = ‘giraffe’Cages in Building 32•Imperativefor each row a in animalsfor each row c in cagesif a.cageno = c.no and c.bldg = 32output a•DeclarativeSELECT a.name FROM animals AS a, cages AS c WHERE a.cageno = c.no AND c.bldg = 32Average Age of Bears•DeclarativeSELECT AVG(age) FROM animalsWHERE species = ‘bear’Complex QueriesFind pairs of animals of the same species and different genders older than 1 year:SELECT a1.name,a2.nameFROM animals as a1, animals as a2WHERE a1.gender = M and a2.gender = FAND a1.species = a2.speciesAND a1.age > 1 and a2.age > 1Find cages with salamanders fed later than the average feedtime of any cage:SELECT cages.cageid FROM cages, animalsWHERE animals.species = ’salamander'AND animals.cageid = cages.cageidAND cages.feedtime > (SELECT AVG(feedtime) FROM cages )“self join”“nested queries”Complex Queries 2Find keepers who keep both students and salamanders:SELECT keeper.name FROM keeper, cages as c1, cages as c2, keeps as k1, keeps as k2, animals as a1, animals as a2WHERE c1.cageid = k1.cageid AND keeper.keeperid = k1.keeperid AND c2.cageid = k2.cageid AND keeper.keeperid = k2.keeperidAND a1.species = ’student' AND a2.species = ’salamander' AND c1.cageid = a1.cageid AND c2.cageid = a2.cageida1a1c1c1k1k1a2a2c2c2k2k2keeperkeeperkeeper.keeperid = k2.keeperidkeeper.keeperid = k1.keeperidspecies = ‘shrew’species =
View Full Document
Unlocking...