【正文】 atabase: Review Sept. 2022 Yangjun Chen ACS3902 64 //load driver class (dsDriver)。 //connect to data source String url = dsProtocol + “:” + dsSubprotocol + “:” + dsName。 con = (url, dsUsername, dsPassword) } else throw new Exception(“*Cannot find property file” + configFile)。 return con。 } catch (ClassNotFoundException e) { throw new Exception(“* Cannot find driver class “ + dsDriver + “!”)。 } } Database: Review Sept. 2022 Yangjun Chen ACS3902 65 //dir: directory of configuration file //filename: file name public Properties loadConfig(String dir, String filename) throws Exception { File inFile = null。 Properties prop = null。 try { inFile = new File(dir, filename)。 if (() { prop = new Properties()。 (new FileInputStream(inFile))。 } else throw new Exception(“* Error in finding “ + ())。 } finally {return prop。} } } Database: Review Sept. 2022 Yangjun Chen ACS3902 66 Using class DatabaseAccess, DataSourceDemo1 should be modified a little bit: DatabaseAccess db = new databaseAccess()。 con = (“config”, “”)。 Database: Review Sept. 2022 Yangjun Chen ACS3902 67 function dependencies data redundancy, update anomalies what is a function dependency? inference rules, minimal set of FDs normal forms first normal form second normal form third normal form Boyce Codd normal form Normalization Database: Review Sept. 2022 Yangjun Chen ACS3902 68 Data redundancy and update anomalies: ename ssn bdate address EmployeeDepartment dnumber dname This is similar to Employee, but we have included dname. Database: Review Sept. 2022 Yangjun Chen ACS3902 69 EmployeeProject ssn pnumber hours ename plocation This is similar to Works_on, but we have included ename and plocation Database: Review Sept. 2022 Yangjun Chen ACS3902 70 In the two prior cases with EmployeeDepartment and EmployeeProject, we have redundant information in the database … ? if two employees work in the same department, then that department name is replicated ? if more than one employee works on a project then the project location is replicated ? if an employee works on more than one project his/her name is replicated Redundant data leads to ? additional space requirements ? update anomalies Database: Review Sept. 2022 Yangjun Chen ACS3902 71 Suppose EmployeeDepartment is the only relation where department name is recorded insert anomalies ? adding a new department is plicated unless there is also an employee for that department deletion anomalies ? if we delete all employees for some department, what should happen to the department information? modification anomalies ? if we change the name of a department, then we must change it in all tuples referring to that department Database: Review Sept. 2022 Yangjun Chen ACS3902 72 Functional dependencies: Suppose we have a relation R prising attributes X,Y, … We say a functional dependency exists between the attributes X and Y, if, whenever a tuple exists with the value x for X, it will always have the same value y for Y. X Y X Y LHS RHS Database: Review Sept. 2022 Yangjun Chen ACS3902 73 student_no student_name course_no gender Student Given a specific student number, there is only one value for student name and only one value for gender found with it. Student_no Student_name gender Database: Review Sept. 2022 Yangjun Chen ACS3902 74 Inference Rules for Function Dependencies ? From a set of FDs, we can derive some other FDs Example: F = {ssn ? {Ename, Bdate, Address, dnumber}, dnumber ? {dname, dmgrssn}} ssn ? {dname, dmgrssn}, ssn ? dnumber, dnumber ? dname. inference ? F+ (closure of F): The set of all FDs that can be deduced from F (with F together) is called the closure of F. Database: Review Sept. 2022 Yangjun Chen ACS3902 75 Inference Rules for Function Dependencies ? Inference rules: IR1 (reflexive rule): If X ? Y, then X ? Y. (X ? X.) IR2 (augmentation rule): {X ? Y} |= ZX ? ZY. IR3 (transitive rule): {X ? Y, Y ? Z} |= X ? Z. IR4 (deposition, or projective, rule): {X ? ZY} |= X ? Y, X ? Z. IR5 (union, or additive, rule): {X ? Y, Y ? Z} |= X ? ZY. IR6 (pseudotransitive rule): {X ? Y, WY ? Z} |= WX ? Z. Database: Review Sept. 2022 Yangjun Chen ACS3902 76 Equivalence of Sets of FDs E and F are equivalent if E+ = F+. Minimal sets of FDs ? every dependency has a single attribute on the RHS ? the attributes on the LHS of a dependency are minimal ? we cannot remove any dependency from F and still have a set of dependencies that is equivalent to F. ssn pnumber hours ename plocation {ssn, pnumber} ? hours, ssn ? ename, pnumber ? plocation. Database: Review Sept. 2022 Yangjun Chen ACS3902 77 Normal Forms ? A series of normal forms are known that have, successively, better update characteristics. ? We?ll consider 1NF, 2NF, 3NF, and BCNF. ? A technique used to improve a relation is deposition, where one relation is replaced by two or more relations. When we do so, we want to eliminate update anomalies without losing any information. Database: Review Sept. 2022 Yangjun Chen ACS3902 78 1NF First Normal Form The domain of an attribute must only contain atomic values. ? This disallows repeating values, sets of values, relations within relations, nested relations, … ? In the example database we have a department located in possibly several locations: department 5 is located in Bellaire, Sugarland, and Houston. ? If we had the relation then it would not be 1NF because there are multi