1 / 21

Mastering Database Key Relationships for Efficient Data Retrieval

Learn about keys in databases, including Primary Key, Candidate Key, Foreign Key, and how they establish relationships within tables for organized data retrieval. Explore entity associations and SQL queries.

davidnjones
Download Presentation

Mastering Database Key Relationships for Efficient Data Retrieval

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. studno given family name STUDENT d   year postgraduate undergraduate thesis title Sub and Super-Types (Classes) STUDENT undergraduate jones brown postgraduate

  18. Sub and Super-Types (Classes) PERSON staff given family name PERSON o   brown student staff student

  19. Sub and Super-Types (Classes) STAFF technical STAFF smith o   admin technical

  20. Referential Integrity • Student(studno,name,tutor,year) • Staff(lecturer,roomno,appraiser) • CASCADE • delete all matching foreign key tuples e.g. STUDENT • RESTRICT • can’t delete primary key tuple STAFF whilst a foreign key tuple STUDENT matches • NULLIFY • foreign key STUDENT.tutor set to null if the foreign key ids allowed to take on null

  21. Lab Extensions • Completed work must be handed in by 9:30 on the day of the subsequent lab!

More Related