Relational Data Base Design

1. Relational Data Base Design

2. Features of Relational Database Design

3. Integrity Constraints • Used to ensure accuracy and consistency of data in a relational database. Data integrity is handled in a relational database through the concept of referential integrity. • something like 'be right' and consistent. • The constraints available in SQL are Foreign Key, Primary Key, Not Null, Unique, Check. • Constraints can be defined in two ways: • The constraints can be specified immediately after the column definition. This is called column-level definition. • The constraints can be specified after all the columns are defined. This is called table-level definition.

4. INTEGRITY CONSTRAINTS • Constraints are the rules enforced on data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database. • Constraints could be column level or table level. Column level constraints are applied only to one column, whereas table level constraints are applied to the whole table.

5. TYPES OF CONSTRAINTS • Following are commonly used constraints available in SQL. • Not Null : Ensures that a column cannot have NULL value. • DEFAULT Constraint: Provides a default value for a column when none is specified. • UNIQUE Constraint : Ensures that all values in a column are different. • PRIMARY Key : Uniquely identified each rows/records in a database table. • FOREIGN Key : Uniquely identified a rows/records in any another database table. • CHECK Constraint : The CHECK constraint ensures that all values in a column satisfy certain conditions. • INDEX: Use to create and retrieve data from the database very quickly.

6. Integrity Constraints An important functionality of a DBMS is to enable the specification of integrity constraints and to enforce them. Knowledge of integrity constraints is also useful for query optimization. Examples of constraints: keys, superkeys foreign keys domain constraints, tuple constraints. Functional dependencies, multivalued dependencies.

7. Integrity Constraints • Domain Constraints-Check • Referential Integrity-Foreign Key • Entity Integrity-Unique, Primary Key, Not null Integrity constraints guard against accidental damage to the database, by ensuring that authorized changes to the database do not result in a loss of data consistency.

8. Domain Constraints • They define valid values for attributes • They are the most elementary form of integrity constraint. • They test values inserted in the database, and test queries to ensure that the comparisons make sense.

9. Domain Constraints • The check clause in SQL permits domains to be restricted • use check clause to ensure that an hourly-wage domain allows only values greater than a specified value.

10. Foreign key account ( account-no, branch-name, balance ) A-123 Perryridge 5000 branch (branch-name, branch-city, asset ) Perryridge Brooklyn 500,000 Primary keys of respective relations Referential Integrity • Ensures that a value that appears in one relation for a given set of attributes also appears for a certain set of attribute in another relation. • If an account exists in the database with branch name “Perryridge”, then the branch “Perryridge” must actually exist in the database. A set of attributes X in R is a foreign key if it is not a primary key of R but it is a primary key of some relation S.

11. R2 ( K2, …, , … ) R1 ( K1, …, … ) t2 t1 Referential Integrity • Formal Definition • Let r1(R1) and r2(R2) be relations with primary keys K1 and K2 respectively. • The subset  of R2 is a foreign key referencing K1 in relation r1, if for every t2 in r2 there must be a tuple t1 in r1 such that t1[K1]=t2[]. • Referential integrity constraint: (r2)  K1 (r1) x x

12. Functional Dependencies • Association among 2 attributes of same relational database table. • One of them is called determinant and other is called the determined • If A is determinant and B is Determined ,we can say that A functionally determines B and it is written as AB • It means A can uniquely identify B

13. Functional Dependencies(Student table)

14. Student Table • If we ask what is the CGPA of A ,we can’t tell the what is the CGPA of A why ???? • There are 2 A’s in this relation, so we require something to uniquely identify an attribute • If we ask What is the CGPA of Roll-4,we can get answer as 9.1.So CGPA can be identified by Roll No • It means RollNoCGPA and RollNoName

15. Example(General Table)

16. Functional Dependency Rules • Here A determines B • It means A functionally determines B,means for instance of A will uniquely identify B • If we say A1B3 [ A1 will uniquely determine B3] • We can’t have A1 determine B1 and B2,meanse A1 can determine only B3 • Example Roll1 is assigned to Adarsh only.

17. Functional Dependency Rules • If B1 and B2 corresponds to A1,It is not possible • AB ,If B1 and B2 corresponds to A1,it is not possible in Functional Dependency • AC ,It is possible because • A1C1 • A2C2 • A3C2 • We can say that A2 and A3 can determine C2 ,it is possible but A1 can’t determine C1 and C2 both. • So AC is possible, So we can find FD on different Attributes

18. Fully Functional Dependencies • If XY,Y is said to be fully Functionally dependent on X if it can’t be determined by any subset of X • Example--- Address is said to be Fully Functional Dependent on Street,City,PinCode • It can’t be determined by Street,Pincode or City alone or Street Alone

19. Supplier Table and Part Table

20. Functional Dependencies

21. Cases • In Supplier Table • Sname is FD on Sno. Sname can take one value for given value of Sno(S1) • SnoSname • Sname is functionally dependent on Sno • SnoCity • SnoStatus • SnoS(Sname,City,Status)

22. Consider another table • Qty is FD on combination of Sno,Pno because each combination of Sno and Pno results in 1 Quantity

23. Fully Functional Dependencies • (Sno,Status)City • City is not FD on status • City is FD on composite attribute of (Sno,Status) but its not fully dependent on composite attribute of Composite attribute • Acc. To definition of FFD,Y must not be fully functionally dependent on any subset of X,But here City is Functionally Depdendent of Sno ,so Y is not functionally dependent on X

24. Fully Functional Dependencies • Consider Shipment table • (Sno,Pno) denoted by XQty denoted by Y • Qty is not FD on Sno because 1 Sno can supply more than 1 Qty • Qty is not FD on Pno,because 1 Pno may be supplied many times by different suppliers • So Qty is FFD on composite attribute of(Sno,Pno)

25. Partial Dependency • A relationship between attributes such that the value of one attribute is dependentonor determined by the value of another attribute which is part of the composite key. • Student: {rollno, name, c_id, c_title, grade} • name attribute is partial dependent on the rollno, because rollno is a part of composite key (Composite key is a Primary key of two or more attributes that uniquely identifies the row.).

26. Transitive Dependency • Exists when values of an attribute is dependent on the value of another dependent attribute. • For example: A B C Is a transitive dependency which shows that attribute ‘C’ is FD on ‘B’ which is further dependent on attribute ‘A’. Transitive Dependency: Dept_idDept_nameCod_name

27. Multi-Valued Dependency • Attribute B has a MVD on attribute A, if for each value of attribute A, there are more than one values of attribute B. • For example: A B Name Mobile_No Both preeti & Akanksha has two Mobile_No, means there are more than one values of attribute Mobile_No for each value of attribute Name.

28. Normalization • Database Design technique • Eliminate Duplication(Redundancy) • Organization of Data in such a way that Duplication is removed

29. Problems in Data Redundancy • Disk Space Wastage • Data Inconsistency • DML Queries Can be Slow • Anomaly

30. Consider a table

31. What is an Anomaly? • Definition • Problems that can occur in poorly planned, un-normalized databases where all the data is stored in one table (a flat-file database). • Types of anomalies: • Insert • Delete • Update

32. Insert Anomaly • occurs when certain attributes cannot be inserted into the database without the presence of other attributes. e.g. we have built a new room (e.g. B123) but it has not yet been time tabled for any courses or members of staff.

33. Delete Anomaly • Exists when certain attributes are lost because of the deletion of other attributes. e.g. if we remove the attribute employeename,we can’t find his or her salary

34. Update Anomaly • If we want to update head name from john to steve,we have to update for all rows, if we have 1 lakh employees in IT department ,we have to update for all 1 lakh employees • Leads to time wastage

35. Effects of Redundancy • Here Departments such as IT,HR are repeated • Department Heads, Location are repeated • Disk Space is wasted • Data Information can be inconsistent • If We have IT department head John has resigned, if we have to change IT department head that has 50,000 Employees, all 50000 rows are required to be updated, if all rows are not updated properly because of any condition, then data becomes inconsistent • This problem is only because of Data Duplication • Queries like insert, update and delete will be slow

36. Effects of Redundancy • If I want to change my IT head from john to steve, if there are 1 million employees • Updating 1 million rows takes time

37. How To Avoid Anomalies?? • The use of “normalization”. • The goal of the normalization process is to define relations • So that each relation is about one kind of thing. Not two. Not three. One. • This seems like a reasonable condition, given the problems that it prevents

38. Solve problem of Redundancy(Normalization) • Broken the previous Table into 2 following tables • Deptid is given primary key in department table • Employeeid is given primary key in Employee table

39. Solve problem of Redundancy(Normalization) • Broken the table into 2 tables employee and department • Repeating columns are moved to separate table • No need to repeat department name,Head,Location • Refer Depid in employee table with department table • Even if we have 50 million employees there is only 1 head for the particular record • Now if we want to change department head from john to steve,we have 1 row to update instead of 1 million rows. • It results in consistency of data when tables are joined

40. 1NF • Data should be atomic (No Multiple values separated by Commas) • Table should not contain any repeating groups • Identify each record uniquely using primary key

41. Atomic Property of 1 NF • We can’t have employee Sam,mike,shan in same column • It should be single value without any commas

42. Non Repeating Groups • Each Column is atomic • If a new employee wants to join the Department we have to use Alter Command • We have to change Table Structure • If HR Department has 1 employee then Employee2 and Employee 3 will be Null • Leads to wastage of Disk Space

43. How to Put Table in 1st NF

44. 1st NF • Deptid is given primary key in department table and selected as foreign key in employee table • Repeating employees are put in another table • So it is in 1 NF

45. 2nd NF • It should be in 1st NF • Move redundant data to another table • Create relationships among tables using Foreign Key • Avoid Partial Dependencies • Every Non Prime attribute is functionally dependent upon primary key

46. Problems occur in a table(Redundancy) • There are 2 departments for 1 lakh employees • Repetition of information regarding deptname,depthead,deptlocation

47. Problems occur in a table(Redundancy) • If 50,000 records of employee is required to be updated and 40000 updatation is done and 10,000 is left ,it creates inconsistency • Break table into 2 tables so that updation is done properly

48. 3rd NF • Meets condition of 1NF,2NF • Does not contain columns that are not full dependent upon primary key. • Avoid Transitive Dependencies

49. 3rd NF • In above table Annual Salary is dependent upon salary • No need to store those Columns that are not fully dependent on primary key • Either remove them completely or move them to separate table.

50. 3rd NF • After Removing the annual salary we are left with the following table, Still it contains Duplicated columns