Basics of Functional Dependencies for Relational Databases - Assignment Example

Basics of Functional Dependencies for Relational Databases #11.25 - Convert the example of GEOMETRY_OBJECTS given in section 11.1.5 from the functional notation to the notation given in Figure 11.2 that distinguishes between attributes and operations. Use the keyword INHERIT to show that one class inherits from another class. define class GEOMETRY_OBJECT type tuple ( Shape: string; Reference_point: integer; operations Area: integer; ) end GEOMETRY_OBJECT; define class RECTANGLE INHERIT GEOMETRY_OBJECT type tuple ( Width: integer; Height: integer; ) end RECTANGLE; define class TRIANGLE INHERIT GEOMETRY_OBJECT type tuple ( Side1: integer; Side2: integer; Angle: integer; ) end TRIANGLE; define class CIRCLE INHERIT GEOMETRY_OBJECT type tuple ( Radius ) end CIRCLE; #11.27 - Consider the UNIVERSITY EER schema of Figure 8.10. Think of what operations are needed for the entity types/classes in the schema. Do not consider constructor and destructor operations. PERSON age STUDENT change_major UNDERGRADUATE_STUDENT change_classification GRADUATE_STUDNT change_degree_program EMPLOYEE hire_emp ALUMNUS new_alumnus RESEARCH_ASSISTANT change_project TEACHING_ASSISTANT assign_to_course #11.31 - Map the COMPANY ER schema of Figure 7.2 into ODL classes. Include appropriate methods for each class. DEPENDENT PROJECT DEPARTMENT EMPLOYEE PROJECT CH 12: XML – EXTENSIBLE MARKUP LANGUAGE #12.9 – Consider the LIBRARY relational database schema in Figure 4.6. Create an XML schema document that corresponds to this database schema. CH13: INTRODUCTION TO SQL PROGRAMMING TECHNIQUES #13.9 – Consider the LIBRARY relational database schema in Figure 4.6. Write a program segment that retrieves the list of books that became overdue yesterday and that prints the book title and borrower name for each. Use embedded SQL and C or any programming language of your choice or even a pseudo-code. Statement statement=“SELECT Title, Name FROM BOOK LEFT JOIN BOOK_LOANS ON BOOK.Book_id=BOOK_LOANS.Book_id LEFT JOIN BORROWER ON BOOK_LOANS.Card_no=BORROWER. Card_no WHERE BOOK_LOANS.Due_date=yesterday; Resultset resl= statement.execute(); Foreach(result){ Print result.Title + “ ”+ result.Name; } CH 15: BASICS of FUNCTIONAL DEPENDENCIES AND NORMALIZATION FOR RELATIONAL DATABASES #15.19 - Suppose we have the following requirements (all five) for a university database that is used to keep track of students’ transcripts: a The university keeps track of each student's name (SNAME), student number (SNUM), social security number (SSSN), current address (SCADDR) and phone (SCPHONE), permanent address (SPADDR) and phone (SPPHONE), birthdate (BDATE), sex (SEX), class (CLASS) (freshman, sophomore, ..., graduate), major department (MAJORDEPTCODE), minor department (MINORDEPTCODE) (if any), and degree program (PROG) (B.A., B.S., ..., Ph.D.). Both ssn and student number have unique values for each student. b Each department is described by a name (DEPTNAME), department code (DEPTCODE), office number (DEPTOFFICE), office phone (DEPTPHONE), and college (DEPTCOLLEGE). Both name and code have unique values for each department. c Each course has a course name (CNAME), description (CDESC), code number (CNUM), number of semester hours (CREDIT), level (LEVEL), and offering department (CDEPT). The value of code number is unique for each course. d Each section has an instructor (INSTUCTORNAME), semester (SEMESTER), year (YEAR), course (SECCOURSE), and section number (SECNUM). Section numbers distinguish different sections of the same course that are taught during the same semester/year; its values are 1, 2, 3, ...; up to the number of sections taught during each semester. e A grade record refers to a student (Ssn), refers to a particular section, and grade (GRADE). Design a relational database schema for this database application. First show all the functional dependencies that should hold among the attributes. Then, design relation schemas for the database that are each in 3NF or BCNF. Specify the key attributes of each relation. Note any unspecified requirements, and make appropriate assumptions to make the specification complete. STUDENT(SNAME, SNUM, SSSN, SCADDR, SCPHONE, SPADDR, SPPHONE, BDATE, SEX, CLASS, MAJORDEPTCODE, MINORDEPTCODE, PROG) DEPARTMENT(DEPTNAME, DEPTCODE, DEPTOFFICE, DEPTPHONE, DEPTCOLLEGE) COURSE(CNAME, CDESC, CNUM, CREDIT, LEVEL, CDEPT) SECION(INSTRUCTORNAME, SEMESTER, YEAR, SECCOURSE, SECNUM) GRADE(SECNUM , SSSN, GRADE) #15.20 - What update anomalies occur in the EMP_PROJ and EMP_DEPT relations of Figure 15.3 and 15.4? In EMP_PROJ the employee and project relations are combined into the same table, so that if the parent employee table gets updated the EMP_PROJ (EMP_PROJ.Ename) may end up having nonexistent employee names/details. The same happens for projects in the EMP_PROJ table and department details mixed with employee details in the EMP_DEPT table. #15.21 - In what normal form is the LOTS relation schema in Figure 15.12(a) with respect to the restrictive interpretations of normal form that take only the primary key into account? Would it be in the same normal form if the general definitions of normal form were used? First Normal Form No #15.35 - Consider the relation: BOOK (Book_Name, Author, Edition, Year) with the data: Book_Name Author Edition Year DB_fundamentals Navathe 4 2004 DB_fundamentals Elmasri 4 2004 DB_fundamentals Elmasri 5 2007 DB_fundamentals Navathe 5 2007 a Based on a common-sense understanding of the above data, what are the possible candidate keys of this relation? Book name, Author b Does the above have one or more functional dependency (do not list FDs by applying derivation rules)? If so, what is it? Show how you will remove it by decomposition. Yes, one: edition implies year Create a separate table for editions with fields edition and year, make edition its primary key and a foreign key in table book c Does the resulting relation have an MVD? If so, what is it? No d What will the final decomposition look like? CH 16: RELATIONAL DATABASE DESIGN ALGORITHMS and FURTHER DEPENDENCIES #16.20 - Show that the relation schemas produced by Algorithm 16.6 are in 3NF. We give a proof by contradiction. Suppose that one of the relations R i resulting from Algorithm 16.6 is not in 3NF. Then a FD Y -> A holds R i in where: (a) Y is not a superkey of R, and (b) A is not a prime attribute. But according to step 2 of the algorithm, R i will contain a set of attributes X union A 1 union A 2 union ... union A n , where X -> A i for i=1, 2, ..., n, implying that X is a key of R i and the A i are the only non-prime attributes of R i . Hence, if an FD Y -> A holds in R i where A is non-prime and Y is not a superkey of R i , Y must be a proper subset of X (otherwise Y would contain X and hence be a superkey). If both Y -> A and X -> A hold and Y is a proper subset of X, this contradicts that X -> A is a FD in a minimal set of FDs that is input to the algorithm, since removing an attribute from X leaves a valid FD, thus violating one of the minimality conditions. This produces a contradiction of our assumptions. Hence, R i must be in 3NF. #16.21 - Specify a template dependency for join dependencies. The following template specifies a join dependency JD(X,Y,Z).  R={A, B, C} Hypothesis a b c1 a b1 c a1 b c conclusion a b c #16.22 - Specify all the inclusion dependencies for the relational schema of Figure 3.5. The inclusion dependencies will correspond to the foreign keys shown in Figure 3.7. Read More