DATABASE THOERY and practice o data.pptx

DATABASE THOERY

1. DATABASE THOERY FACILITATOR ALEX

2. INTRODUCTION Database. A shared collection of logically related data, and a description of this data, designed to meet the information needs of an organization . A DBMS is a software system that enables users to define, create, maintain, and control access to the database . A relation is a table with columns and rows . An attribute is a named column of a relation . Relation schema is a named relation defined by a set of attribute and domain name pairs.

3. CONT’’ A domain is the set of allowable values for one or more attributes. A tuple is a row/record of a relation. Degree of a relation is the number of attributes it contains. Cardinality of a relation is the number of tuples it contains. Relational database is a collection of normalized relations with distinct relation names.

4. Cont ’’ Relation = Table= File. Tuple=Row=Record. Attribute=Column=Field

5. Properties of Relations/table Must have a distinct name from all other relation names. Each cell of the relation contains exactly one atomic (single) value. Each attribute has a distinct name . Each tuple is distinct; there are no duplicate tuples. The values of an attribute are all from the same domain

6. KEYS A superkey is a set of one or more attributes that, taken collectively, allow us to identify uniquely a tuple in the relation. Example In a relation/table instructor{ID,NAME,DEPARTMENT NAME} {ID} { ID,NAME} {ID,DEPARTMENT NAME} { NAME,DEPARTMENT NAME}

7. CONT’’ The ID attribute of the relation INSTRUCTOR is sufficient to distinguish one instructor tuple from 29 another. Thus, ID is a super key. The name attribute of instructor, on the other hand, is not a super key, because several instructors might have the same name. And a combination of name and dept name is sufficient to distinguish among members of the instructor relation. ID and name together can distinguish instructor tuples, their combination, {ID, name}

8. Candidate KEY Candidate A superkey such that no proper subset is a superkey within the key relation . INSTRUCTOR TABLE {ID } is the candidate key and {NAME,DEPARTMENT NAME }. Properties Irreducibility-in each tuple of R, the values of K uniquely identify that tuple. Uniqueness-no proper subset of K has the uniqueness property.

9. PRIMARY KEY PRIMARY KEY. The candidate key that is selected to identify tuples uniquely within the key relation . IN THE RELATION INSTRUCTOR The primary key is {ID} Properties The value of primary key can never be NULL. The value of primary key must always be unique. The values of primary key can never be changed i.e. no updation is possible. The value of primary key must be assigned when inserting a record. A relation is allowed to have only one primary key.

10. Foreign key An attribute ‘X’ is called as a foreign key to some other attribute ‘Y’ when its values are dependent on the values of attribute ‘Y’.

11. Functional dependency Functional dependency (FD) is a set of constraints between two attributes in a relation. Functional dependency says that if two tuples have same values for attributes A1, A2,..., An, then those two tuples must have to have same values for attributes B1, B2, ..., Bn. Functional dependency is represented by an arrow sign (→) that is, X→Y, where X functionally determines Y. The left-hand side attributes determine the values of attributes on the right-hand side. Armstrong's Axioms If F is a set of functional dependencies then the closure of F, denoted as F+, is the set of all functional dependencies logically implied by F. Armstrong's Axioms are a set of rules, that when applied repeatedly, generates a closure of functional dependencies .

12. CONT’’ Reflexive rule − If alpha is a set of attributes and beta is_subset_of alpha, then alpha holds beta. Augmentation rule − If a → b holds and y is attribute set, then ay → by also holds. That is adding attributes in dependencies, does not change the basic dependencies . Transitivity rule − Same as transitive rule in algebra, if a → b holds and b → c holds, then a → c also holds. a → b is called as a functionally that determines b.

13. Normalization Normalization is a formal method that can be used to identify relations based on their keys and the functional dependencies among their attributes. It is a method of reducing duplication/data redundancy of a relation/table. Normal forms First Normal A relation in which the intersection of each row and column contains Form (1NF) one and only one value. Second Normal A relation that is in First Normal Form and every non-primary-key Form (2NF) attribute is fully functionally dependent on the primary key. Third Normal A relation that is in First and Second Normal Form and in which no Form (3NF) non-primary-key attribute is transitively dependent on the primary key . Etc.

14. CONT’’ Trivial Functional Dependency Trivial − If a functional dependency (FD) X → Y holds, where Y is a subset of X, then it is called a trivial FD. Trivial FDs always hold. Non-trivial − If an FD X → Y holds, where Y is not a subset of X, then it is called a non-trivial FD. Completely non-trivial − If an FD X → Y holds, where x intersect Y = Φ, it is said to be a completely non-trivial FD.