Lecture 6.pptx - DATABASE MANAGEMENT SYSTEMS 1 Lecture 6 BRIEF RECAP Relational Model Represents data and relationship among data in the form of tables

# Lecture 6.pptx - DATABASE MANAGEMENT SYSTEMS 1 Lecture 6...

• Notes
• 31

This preview shows page 1 - 8 out of 31 pages.

DATABASE MANAGEMENT SYSTEMS Lecture 6 1
BRIEF RECAP Relational Model Represents data and relationship among data in the form of tables Each tuple is distinct; there are no duplicate tuples Each relation name must be unique in relational database 2
RELATIONAL KEYS The concept of Keys is very important to the relational model. We'll discuss the following types of keys. Candidate Keys Primary Keys and Alternative Keys Foreign Keys 3
FORMAL DEFINITIONS Candidate key : minimal number of attributes that uniquely identify each tuple in a relation. Primary key : a designated candidate key; cannot contain null values Foreign key : Attribute, or set of attributes, within one relation that matches candidate key of another relation. 4
CANDIDATE KEYS Let R be a table. By definition, at any given time, no two tuples in the value of R are duplicates of one another. Let K be a set of attributes of R. Then K is a candidate key for R if and only if it satisfies: (1) Uniqueness: In each tuple of R, values of K uniquely identify that tuple (2) Irreducibility (or minimality): No proper subset of K has the uniqueness property. 5
S# S1 S1 S1 S1 S1 S1 S2 S2 S3 S4 S4 S4 P# P1 P2 P3 P4 P5 P6 P1 P2 P2 P2 P4 P5 QTY 300 200 400 200 100 100 300 400 200 200 300 400 SP S# SNAME STATUS CITY S1 S2 S3 S4 S5 Smith Jones Blake Clark Adams 20 10 30 20 30 London Paris Paris London Athens S SSN 13 23 33 44 66 CANDIDATE KEY EXAMPLES For S {S#, SNAME, STATUS, CITY}, S# and SNAME do not have Duplicates, so Candidate KEY {S#} Candidate KEY {SNAME} 6
PRIMARY AND ALTERNATIVE KEYS Exactly one candidate key must be chosen as the primary key, and the others are alternative keys. Primary key and alternative keys are both candidate keys. For S {S#, SNAME, STATUS, CITY}, if we choose KEY {S#} as the primary key, then KEY {SNAME} is an alternative key. For SP {S#, P#, QTY}, We must choose KEY {S#, P#} as the primary key, and there will be no alternative key.

#### You've reached the end of your free preview.

Want to read all 31 pages?

• Fall '19
• ali

### What students are saying

• As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

Kiran Temple University Fox School of Business ‘17, Course Hero Intern

• I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

Dana University of Pennsylvania ‘17, Course Hero Intern

• The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

Jill Tulane University ‘16, Course Hero Intern