DOC PREVIEW
SJSU CMPE 226 - Misc. Topics in DB

This preview shows page 1-2-3-4-5 out of 14 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

Database DesignPowerPoint PresentationSlide 3Terminology: ANSI-SPARC Database ArchitectureMore TerminologyExampleSlide 7Properties of Relations (1)Properties of Relations (2)Relational Keys (1)Relational Keys (2)Relational Keys (3)Relational Keys (4)Discussion Questions© M.E. Fayad 2002-2005SJSU -- CmpE L8-2-S1 Misc TopicsDatabase Design Dr. M.E. Fayad, ProfessorComputer Engineering Department, Room #283I College of EngineeringSan José State UniversityOne Washington SquareSan José, CA 95192-0180 http://www.engr.sjsu.edu/~fayad© M.E. Fayad 2002-2005SJSU – CmpE --- M.E. Fayad L8-2-S2 Misc Topics2Lesson 08-2:Misc. Topics in DB© M.E. Fayad 2002-2005SJSU – CmpE --- M.E. Fayad L8-2-S3 Misc Topics Lesson ObjectivesObjectives3 Understand DB Terminology Explain Relation Properties Understand how to Identify Candidate, Primary, Alternate, and Foreign KeysUnderstand Entity Integrity and Referential Integrity© M.E. Fayad 2002-2005SJSU – CmpE --- M.E. Fayad L8-2-S4 Misc TopicsExternal Level View Level -- Users’ views of the db.Conceptual Level Logical View & Constraints ViewInternal Level Physical View or Computer’s view of the db.4Terminology: ANSI-SPARC Database Architecture© M.E. Fayad 2002-2005SJSU – CmpE --- M.E. Fayad L8-2-S5 Misc Topics A Relation is a table with columns & Rows. An attribute is a named column of a relation. A domain is the set of allowable values for one or more attributes. A tuple is a row of a relation. The degree of a relation is the number of attributes it contains.5More Terminology© M.E. Fayad 2002-2005SJSU – CmpE --- M.E. Fayad L8-2-S6 Misc Topics6ExampleBranchbranchNo street city postCodeB005 22 Deer Rd London SW1 4EHB007 22 Post St Aberdeen AB2 3SUB003 22 S. Main St Glasgow GI1 9QXB004 22 Mo Ave Bristol BS9 1NX B002 56 Clover Dr London NW5 6EUstaffNobranchNoB005B007B003B004B005StaffPrimary Key Foreign Key© M.E. Fayad 2002-2005SJSU – CmpE --- M.E. Fayad L8-2-S7 Misc Topics A Relation Schema – A named relation defined by a set of attribute and domain name pairs where:R (A1:d1, A2:d2, …., An:dn) A Database Schema – A set of relation schema each with a distinct name.R = (R1, R2, ….., Rn)7More Terminology© M.E. Fayad 2002-2005SJSU – CmpE --- M.E. Fayad L8-2-S8 Misc Topics A relation has a name that is distinct from all other relation names within the same db. Each cell of the relation contains exactly one atomic (single) value. Each attribute has a distinct name. The values of an attribute are all from the same domain.8Properties of Relations (1)© M.E. Fayad 2002-2005SJSU – CmpE --- M.E. Fayad L8-2-S9 Misc Topics Each tuple is distinct, there are no duplicate tuples. The order of attributes has no significance. The order of tuples has no significance,9Properties of Relations (2)© M.E. Fayad 2002-2005SJSU – CmpE --- M.E. Fayad L8-2-S10 Misc Topics Superkey – An attribute, or set of attributes, that uniquely identifies a tuple within a relation. Candidate key – A Superkey, such that no proper subset is superkey within the relation. A candidate key, k, for a relation R has two properties:1) Uniqueness: In each tuple of R, the value of k uniquely identify the tuple.2) Irreducibility – no proper subset of k has the uniqueness property10Relational Keys (1)© M.E. Fayad 2002-2005SJSU – CmpE --- M.E. Fayad L8-2-S11 Misc Topics They may be several candidate keys for a relation. Composite key – A key consists of more than one attribute. Exampes:1) Do you think “city” in Branch relation is a candidate key? The answer is no. Why? Because London has two branch offices, therefore, the attribute “city” cannot be a candidate key.2) Is “branchNo” a candidate key? Yes, Why? By Examining “branchNo”, each branch office has a unique branchNo,3) Is postcode is a candidate key? Yes.. Why?11Relational Keys (2)© M.E. Fayad 2002-2005SJSU – CmpE --- M.E. Fayad L8-2-S12 Misc TopicsPrimary key: The candidate key that is selected to identify tuples uniquely within the relation Alternate keys – The Candidate keys that are not selected to be the primary key. Exampes:1) In the Branch Relation, we can choose branchNo as the primary key and2) Postcode as an alternate key.12Relational Keys (3)© M.E. Fayad 2002-2005SJSU – CmpE --- M.E. Fayad L8-2-S13 Misc Topics13Relational Keys (4)–Foreign key: An attribute, or a set of attributes, with one relation that matches the candidate key of some (possibly the same) relation.–branchNo appeared in two relations: Branch and Saff relations–branchNo is a primary key in Branch relation and it is a foreign key in the Staff relation.© M.E. Fayad 2002-2005SJSU – CmpE --- M.E. Fayad L8-2-S14 Misc TopicsT/F1. Each cell of the relation contains exactly on atomic (single) value.2. Primary key is the candidate key that is selected to identify tuples uniquely within the database.3. Foreign key is an attribute or a set of attributes within on relation that matches the candidate key of some relation.4. The degree of a relation is the number of tuples it contains.14 Discussion


View Full Document

SJSU CMPE 226 - Misc. Topics in DB

Documents in this Course
SQL-99

SQL-99

71 pages

XML

XML

52 pages

XML

XML

14 pages

Chapter 9

Chapter 9

45 pages

Load more
Download Misc. Topics in DB
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Misc. Topics in DB and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Misc. Topics in DB 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?