1 / 20

Understanding Database Keys and Relational Model

Learn about keys in database systems, including superkey, candidate key, primary key, foreign key, and their importance in ensuring data integrity. Explore relational model concepts, query languages, and relational algebra operations.

myrna
Télécharger la présentation

Understanding Database Keys and Relational Model

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. IS C332: Database Systems and Applications Today’s Class Data Models Relational Model

  2. Keys • Let K R (I.e., K is a set of attributes which is a subset of the schema of R) • K is a superkey of R if K can identify a unique tuple in a given relationr(R) Customer(CusNo, Name, Address, …)where customers have unique customer numbers and unique names.Possible superkeys: CusNo {CusNo, Name} {CusNo, Name, Address} plus many others • K is a candidate key if K is minimal There are two candidate keys: CusNo and Name • Every relation is guarantee to (must) have at least one key. Why?

  3. Key(Candidate key) • A key can not be determined from any particular instance data • it is an intrinsic property of a scheme • it can only be determined from the meaning of attributes • A relation can have more than one key. • Superkey: A set of attributes that contains any key as a subset. • A key can also be defined as a minimal superkey • Primary Key: One of the candidate keys chosen for indexing purposes ( More details later…)

  4. Keys • Keys are a way to associate tuples in different relations • Keys are one form of integrity constraint (IC) Enrolled Students PRIMARY Key FORIEGN Key

  5. Foreign Keys, Referential Integrity • Foreign key : Set of fields in one relation that is used to `refer’ to a tuple in another relation. • Must correspond to the primary key of the other relation. • Like a `logical pointer’. • If all foreign key constraints are enforced, referential integrity is achieved (i.e., no dangling references.)

  6. Enrolled Students 11111 English102 A Foreign Keys in SQL • E.g. Only students listed in the Students relation should be allowed to enroll for courses. • sid is a foreign key referring to Students: CREATE TABLE Enrolled (sid CHAR(20),cid CHAR(20),grade CHAR(2), PRIMARY KEY (sid,cid), FOREIGN KEY (sid) REFERENCES Students )

  7. Relational Model • Sets • collections of items of the same type • no order • no duplicates • Mappings domain range 1:many many:1 1:1 many:many

  8. Exercise • What are the mapping cardinalities of the following 4 relationships? B C D A

  9. Schema Diagram for University Database

  10. Relational Query Languages • Procedural vs.non-procedural, or declarative • “Pure” languages: • Relational algebra • Tuple relational calculus • Domain relational calculus • Relational operators

  11. Relational Algebra Operators

  12. Select Operation – Example • Relation r A B C D         1 5 12 23 7 7 3 10 • Select tuples with A=B and D > 5 • A=B ^ D > 5 (r) A B C D     1 23 7 10

  13. Project Operation – Example Selection of Columns (Attributes) A B C     10 20 30 40 1 1 1 2 • Relation r: • Select A and C A C A C A,C (r)     1 1 1 2    1 1 2 =

  14. Joining two relations – Cartesian Product • Relations r, s: A B C D E   1 2     10 10 20 10 a a b b r s • r xs: A B C D E         1 1 1 1 2 2 2 2         10 10 20 10 10 10 20 10 a a b b a a b b

  15. Union of two relations    1 2 1   2 3 • Relations r, s: s r • r  s: A B     1 2 1 3

  16. Set difference of two relations    1 2 1   2 3 • Relations r, s: s r • r – s: A B   1 1

  17. Set Intersection of two relations A B A B • Relation r, s: • r s    1 2 1   2 3 r s A B  2

  18. Natural Join • r s Natural Join Example A B C D • Relations r, s: 1 3 1 2 3 a a a b b           1 2 4 1 2      a a b a b s r A B C D E      1 1 1 1 2      a a a a b     

  19. Joining two relations – Natural Join • Let r and s be relations on schemas R and S respectively. Then, the “natural join” of relations R and S is a relation on schema R S obtained as follows: • Consider each pair of tuples tr from r and ts from s. • If tr and ts have the same value on each of the attributes in RS, add a tuple t to the result, where • t has the same value as tr on r • t has the same value as ts on s

  20. Natural Join • Example: R = (A, B, C, D) S = (E, B, D) • Result schema = (A, B, C, D, E) • rs is defined as:r.A, r.B, r.C, r.D, s.E (r.B = s.Br.D = s.D (r x s))

More Related