lecture2-relationalmodelandterminology-240324190857-58c59e46.pptx

education

1. UNIT 2 RELATIONAL AND E-R MODELS[2.0-2.2]

2. Objectives In this lecture, you will learn: Some relational model terminologies The difference between the relation schema and relational database schema The properties of a relation Relational keys Views and purpose of views in relational database

3. Relational Model Terminology An entity is a person, place, event, or thing/Object for which we intend to collect data Examples: University -- Students, Faculty Members, Courses Airline -- Pilots, Aircraft, Routes, Suppliers A relation is a table with columns and rows . Only applies to logical structure of the database, not the physical structure .

4. Relational Model Terminology Attribute is a named column of a relation. Example : Student -- Student Number, Name... etc Domain is the set of allowable values for one or more attributes. Attribute Domain Name Meaning Domain Definition Stu_id StudentID ID of a student Character: Size 10

5. Relational Model Terminology Tuple is a row of a relation. Degree is the number of attributes in a relation. Cardinality is the number of tuples in a relation. Relational Database A collection of normalized relations with distinct relation names.

6. Example of Attribute Domain

7. Entity and Attribute

8. Database Relations Relation schema Named relation defined by a set of attribute and domain name pairs. Relational database schema Set of relation schemas, each with a distinct name.

9. Definition Summary Informal Terms Formal Terms Table Relation Column Header Attribute All possible Column Values Domain Row Tuple Table Definition Schema of a Relation Populated Table State of the Relation

10. Relation and Relational Schema A relation schema is the name and the list of attributes of a relation Example : Grade ( studentId , assignmentId , points, dateSubmitted ) Relational Schema

11. Properties of Relation Relation name is distinct from all other relation names in relational schema. Each cell of relation contains exactly one atomic (single) value. Each attribute has a distinct name. Values of an attribute are all from the same domain . Each tuple is distinct; there are no duplicate tuples. Order of attributes has no significance. Order of tuples has no significance, theoretically .

12. Relational Keys Super key An attribute, or a set of attributes, that uniquely identifies a tuple within a relation. Candidate Key Super key (K) such that no proper subset is a super key within the relation. Candidate key, K, for a relation R has two properties Uniqueness - in each tuple of R, values of K uniquely identify that tuple . Irreducibility - no proper subset of K has the uniqueness property

13. Relational Key Super key Uniquely identifies each entity STU_NUM STU_NUM, STU_LNAME STU_NUM, STU_LNAME, STU_INIT Candidate key Minimal super key STU_NUM STU_LNAME, STU_FNAME, STU_INIT, STU_PHONE When a key consists of more than one attribute, it is called a composite key

14. Example Which attributes of this table can be used as a candidate key ? BranchNo and PostalCode can be used as candidate keys . City attribute cannot be used as a candidate key, because it has duplicate values . Branch No Street City PostalCode B005 22 Deer Rd London SW1 4EH B007 16 Argyll St Aberdeen AB2 3SU B003 163 Main St Glasgow G11 9QX B004 32 Manse Rd Bristol BS99 1NZ B003 56 Clover Dr London NW10 6EU

15. Relational Key Primary Key Candidate key selected to identify tuples uniquely within relation. Alternate Keys Candidate keys that are not selected to be primary key. Foreign Key Attribute, or set of attributes, within one relation that matches candidate key of some (possibly same) relation

16. Links to Refer https://youtu.be/EwEY _Y2Zz0s https://youtu.be/RRUeFwuJ39Q