1 / 26

Keys

Keys. SuperKey a set of attributes whose values together uniquely identify a tuple in a relation Candidate Key a superkey for which no proper subset is a superkey…a key that is minimal . Can be more than one for a relation Primary Key

marenda-faunus
Download Presentation

Keys

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. Keys • SuperKey • a set of attributes whose values together uniquely identify a tuple in a relation • Candidate Key • a superkey for which no proper subset is a superkey…a key that is minimal . • Can be more than one for a relation • Primary Key • a candidate key chosen to be the main key for the relation. • One for each relation • Keys can be composite

  2. e.g.: Staff(lecturer,roomno,appraiser) SK = {lecturer,roomno,appraiser}, {lecturer,roomno}, {lecturer, appraiser}, {roomno,appraiser}, {lecturer} and {roomno} CK = {lecturer} and {roomno} PK = {lecturer}

  3. Foreign Key • a (set of) attribute(s) in a relation that exactly matches a (primary) key in another relation • the names of the attributes don’t have to be the same but must be of the same domain • a foreign key in a relation A matching a primary key in a relation B represents a • many:one relationship between A and B Student(studno,name,tutor,year) Staff(lecturer,roomno,appraiser)

  4. SCHOOL faculty studno Relationship -v- Relation hons name m 1 REG STUDENT Relationship “an association between several entities represented by a Relationship Type of which there will be many Relationship Instances” STUDENT (studno, name, hons) SCHOOL(hons, faculty) Relation (table)

  5. courseno COURSE subject studno Relationship -v- Relation name m n ENROL STUDENT exammark ENROL Relationship STUDENT (studno, name) ENROL(studno, courseno,exammark) COURSE(courseno, subject) ENROL Relation

  6. STUDENT studno name tutor s1 jones bush s2 brown kahn s3 smith goble STAFF lecturer roomno kahn IT206 bush 2.26 Joins e.g., get studno, name and tutor’s roomno for each student

  7. STUDENT studno name tutor s1 jones bush s2 brown kahn s3 smith goble STAFF lecturer roomno kahn IT206 bush 2.26

  8. STUDENT studno name tutor s1 jones bush s2 brown kahn s3 smith goble STAFF lecturer roomno kahn IT206 bush 2.26 studno,name,roomno (STUDENT tutor=lecturerSTAFF) SELECT FROM STUDENT a, STAFF b WHERE studno name tutor lecturer roomno s1 jones bush kahn IT206 s1 jones bush bush 2.26 s2 brown kahn kahn IT206 s2 brown kahn bush 2.26 s3 smith bush kahn IT206 s3 smith bush bush 2.26

  9. STUDENT studno name tutor s1 jones bush s2 brown kahn s3 smith goble STAFF lecturer roomno kahn IT206 bush 2.26 studno,name,roomno (STUDENT tutor=lecturerSTAFF) SELECT FROM STUDENT a, STAFF b WHERE a.tutor=b.lecturer studno name tutor lecturer roomno s1 jones bush kahn IT206 s1 jones bush bush 2.26 s2 brown kahn kahn IT206 s2 brown kahn bush 2.26 s3 smith bush kahn IT206 s3 smith bush bush 2.26

  10. STUDENT studno name tutor s1 jones bush s2 brown kahn s3 smith goble STAFF lecturer roomno kahn IT206 bush 2.26 studno,name,roomno (STUDENT tutor=lecturerSTAFF) SELECT a.studno, a.name, b.roomno FROM STUDENT a, STAFF b WHERE a.tutor=b.lecturer studno name tutor lecturer roomno s1 jones bush kahn IT206 s1 jones bush bush 2.26 s2 brown kahn kahn IT206 s2 brown kahn bush 2.26 s3 smith bush kahn IT206 s3 smith bush bush 2.26

  11. STUDENT studno name tutor s1 jones bush s2 brown kahn s3 smith goble STAFF lecturer roomno kahn IT206 bush 2.26 studno=‘s1’(studno,name,roomno (STUDENT tutor=lecturerSTAFF)) SELECT a.studno, a.name, b.roomno FROM STUDENT a, STAFF b WHERE a.tutor=b.lecturer and studno=‘s1’ studno name tutor lecturer roomno s1 jones bush kahn IT206 s1 jones bush bush 2.26 s2 brown kahn kahn IT206 s2 brown kahn bush 2.26 s3 smith bush kahn IT206 s3 smith bush bush 2.26

  12. m m 1 1 Residences Resort RegLoc Region Hotel resort region hotelid Join as Path Get hotels and their regions SELECT FROM WHERE HOTEL (hotelid, name, resort) RESORT(resort, region) REGION(region,country)

  13. m m 1 1 Residences Resort RegLoc Region Hotel resort region hotelid Get hotels and their regions SELECT FROM HOTEL, RESORT, REGION WHERE HOTEL (hotelid, name, resort) RESORT(resort, region) REGION(region,country)

  14. m m 1 1 Residences Resort RegLoc Region Hotel resort region hotelid hotel.resort=resort.resort Get hotels and their regions SELECT FROM HOTEL, RESORT, REGION WHERE hotel.resort=resort.resort HOTEL (hotelid, name, resort) RESORT(resort, region) REGION(region,country)

  15. m m 1 1 Residences Resort RegLoc Region Hotel resort region hotelid resort.region=region.region hotel.resort=resort.resort Get hotels and their regions SELECT FROM HOTEL, RESORT, REGION WHERE hotel.resort=resort.resort and resort.region=region.region HOTEL (hotelid, name, resort) RESORT(resort, region) REGION(region,country)

  16. m m 1 1 Residences Resort RegLoc Region Hotel resort region hotelid resort.region=region.region hotel.resort=resort.resort Get hotels and their regions SELECT hotel.name, region.region FROM HOTEL, RESORT, REGION WHERE hotel.resort=resort.resort and resort.region=region.region HOTEL (hotelid, name, resort) RESORT(resort, region) REGION(region,country)

  17. More SQL Cinema (cid, name, city) Film (fid, title, director) Showing (fid, cid) Name, city and the number of different films showing at each cinema, in ascending alphabetical order of city and name

  18. select name, city, count(*) as films

  19. select name, city, count(*) as films from Film f, Cinema c, Showing s

  20. select name, city, count(*) as films from Film f, Cinema c, Showing s where f.fid=s.fid and c.cid=s.cid

  21. select name, city, count(*) as films from Film f, Cinema c, Showing s where f.fid=s.fid and c.cid=s.cid group by c.cid

  22. select name, city, count(*) as films from Film f, Cinema c, Showing s where f.fid=s.fid and c.cid=s.cid group by c.cid order by city, name;

  23. Cinema (cid, name, city) Film (fid, title, director) Showing (fid, cid) Titles of films showing at every cinema listed in database.

  24. (select * from showing s where s.cid=c.cid and f.fid=s.fid)

  25. (select * from cinema c where not exists (select * from showing s where s.cid=c.cid and f.fid=s.fid))

  26. select title from film f where not exists (select * from cinema c where not exists (select * from showing s where s.cid=c.cid and f.fid=s.fid));

More Related