INF2603 ASSIGNMENT 2 SEMESTER 2.

INF2603 ASSIGNMENT 2 SEMESTER 2. INF2603 - Databases I. What is a subtype discriminator? Given an example of its use. (3) A subtype discriminator is the attribute in the supertype entity that is used to determine to which entity subtype the supertype occurrence is related. For any given supertype occurrence, the value of the subtype discriminator will determine which subtype the supertype occurrence is related to. For example, an EMPLOYEE supertype may include the EMP_TYPE value "P" to indicate the PROFESSOR subtype. b. Under what circumstances are composite primary keys appropriate? (4) Composite primary keys are particularly useful in two cases:  As identifiers of composite entities, where each primary key combination is allowed only once in the M:N relationship.  As identifiers of weak entities, where the weak entity has a strong identifying relationship with the parent entity. c. What are time-variant data and how would you deal with such data from a database design point of view? (2) As the label implies, time variant data are time-sensitive. For example, if a university wants to keep track of the history of all administrative appointments by date of appointment and date of termination, you see time-variant data at work. S - The study-notes marketplace Downloaded by: renier | Distribution of this document is illegal S - The study-notes marketplace d. Why are entity integrity and referential integrity important in a database? (2)  Referential integrity means that, if the foreign key contains a value, that value refers to an existing valid tuple (row) in another relation.  Entity integrity ensures that each row is uniquely identified by the primary key. e. What two conditions must be met before an entity can be classified as a weak entity? Give an example of a weak entity. (3)  Existence-dependent  Has a primary key partially or totally derived from the parent entity in the relationship f. Explain briefly but precisely the difference between single-valued attributes and simple attributes. Give an example of each. (4) A single -valued attribute is one that can have only one value. For example, a person has only one first name and only one social security number. A simple attribute is one that cannot be decomposed into its component pieces. For example, a person's sex is classified as either M or F and there is no reasonable way to decompose M or F. Question 2 Downloaded by: renier | Distribution of this document is illegal S - The study-notes marketplace b. Write the business rules reflected in this ERD. The following business rules are reflected in the ERD:  A store may place many orders. (Note the use of “may” – which is reflected in the ORDER optionality.)  An order must be placed by a store. (Note that STORE is mandatory to ORDER. In this ERD, the order environment apparently reflects a wholesale environment.)  An order contains at least one order line. (Note that ORDER_LINE is mandatory to ORDER, and vice - versa.)  Each order line is contained in one and only one order. (Discussion: Although a given item –such as a hammer –may be found in many orders, a specific hammer sold to a specific store is found in only one order.)  Each order line has a specific product written in it.  A product may be written in many orders. (Discussion: Many stores can order one or more specific products, but a product that is not in demand may never be sold to a store and will, therefore, not show up in any order line--note that ORDER_LINE is optional to PRODUCT. Also, note that each order line may indicate more than one of a specific item. For example, the item may be “hammer” and the number sold may be 1 or 2, or 500. The ORDER_LINE entity would have at least the following attributes: ORDER_NUM, ORDLINE_NUM, PROD_CODE, ORDLINE_PRICE, ORDLINE_QUANTITY. The ORDER_LINE composite PK would be ORDER_NUM + ORDLINE_NUM. You might add the derived attribute ORDER_LINE_AMOUNT, which would be the result of multiplying ORDLINE_PRICE and ORDLINE_QUANTITY.)  A store may employ many employees. (Discussion: A new store may not yet have any employees, yet the database may already include the new store information ... location, type, and so on. If you made the EMPLOYEE entity Downloaded by: renier | Distribution of this document is illegal S - The study-notes marketplace mandatory to STORE, you would have to create an employee for that store before you had even hired one.)  Each employee is employed by one (and only one) store.  An employee may have one or more dependents. (Discussion: You cannot require an employee to have dependents, so DEPENDENT is optional to EMPLOYEE. Note the use of the word “may” in the relationship.)  A dependent must be related to an employee. (Discussion: It makes no sense to keep track of dependents of people who are not even employees. Therefore, EMPLOYEE is mandatory to DEPENDENT.) Question 3 Question 3 [46] Suppose that you have been given the table structure and data shown in the table below, which was imported from an Excel spreadsheet. The data reflect that a professor can have multiple advisees, can serve on multiple committees, and can edit more than one journal. Table: Sample PROFESSOR Records Downloaded by: renier | Distribution of this document is illegal S - The study-notes marketplace Given the information in the table: a. Draw the dependency diagram. (6) FIG above reflects several ambiguities. For example, although each PROF_OFFICE value shown in Table P6.9 is unique, does that limited information indicate that each professor has a private office? If so, the office number identifies the professor who uses that office. This condition yields a dependency. However, this dependency is not a transitive one, because a nonkey attribute, PROF_OFFICE, determines the value of a key attribute, EMP_NUM. (We have indicated this potential transitive dependency through a dashed dependency line.) Notes : Also, note that there is no reliable way to identify the effect of multivalued attributes on the dependencies. For example, EMP_NUM = 123 could identify any one of five advisees. Therefore, knowing the EMP_NUM does not identify a specific ADVISEE value. The same is true for the COMMITTEE_CODE and JOURNAL_CODE attributes. Therefore, these attributes are not marked with a solid arrow line. However, if you know that EMP_NUM = 123, you will also know all five advisees, all four committee codes, and all three journal codes for that employee number value. But you do not have a unique identification for each of those attribute values. Therefore, you cannot Downloaded by: renier | Distribution of this document is illegal S - The study-notes marketplace nor can you conclude that EMP_NUM determines COMMITTEE_CODE or that EMP_NUM determines JOURNAL_CODE b. Identify the multivalued dependencies. (3) Table above shows several professor attributes ADVISEE, COMMITTEE_CODE and JOURNAL_CODE that represent multivalued dependencies c. Create the dependency diagrams to yield a set of table structures in 3NF. (4)  Note that we have assumed that it is possible that professors can share an office. d. Eliminate the multivalued dependencies by converting the affected table structures to 4NF. (7) EMP_NUM EMP_NAME RANK_CODE OFFICE_CODE RANK_CODE RANK_DETAIL OFFICE_CODE OFFICE EMP_NUM ADVISEE DEPT_CODE DEPT_NAME Downloaded by: renier | Distribution of this document is illegal S - The study-notes marketplace e. Draw the Crow’s Foot ERD to reflect the dependency diagrams you drew in part C. (Note: You might have to create additional attributes to define the proper PKs and FKs. Make sure that all of your attributes conform to the naming conventions.) (26) Question 4 a. What three join types are included in the OUTER JOIN classification? (3) An OUTER JOIN is a type of JOIN operation that yields all rows with matching values in the join columns as well as all unmatched rows. (Unmatched rows are those without matching values in the join columns). The SQL standard prescribes three different types of join operations: