La lecture en ligne est gratuite
Le téléchargement nécessite un accès à la bibliothèque YouScribe
Tout savoir sur nos offres
Télécharger Lire

JOB DESCRIPTION

De
23 pages
  • expression écrite - matière potentielle : skills
  • cours - matière potentielle : research through consideration of current literature
  • exposé
Final JOB DESCRIPTION POST TITLE: Research Assistant DEPARTMENT: Chemistry POST RESPONSIBLE TO: Professor Martin Wills SALARY IN THE RANGE: £23,661 - £26,629 pa REFERENCE NUMBER: 71600-121 CLOSING DATE: 3 February 2012 JOB PURPOSE: To carry out postdoctoral research in synthetic organic and organometallic chemistry including asymmetric catalysis. This project will be directed at the development of novel methodology for the synthesis and use of asymmetric and photoactive organometallic complexes for light-promoted oxidation reactions through dehydrogenation of alcohols and related reactions.
  • disciplinary project area of research
  • promoted oxidation reactions through dehydrogenation of alcohols
  • requirements of the project
  • organometallic chemistry
  • final job description post title
  • interdisciplinary research
  • organic chemistry
  • post holder
  • post-holder
Voir plus Voir moins

Vous aimerez aussi

CHAPTER 7: QUESTIONS AND ANSWERS

1. What is an insertion anomaly?
Ans: An insertion anomaly occurs when extra data beyond the desired data must be
added to a table.

2. What is an update anomaly?
Ans: An update anomaly occurs when it is necessary to change multiple rows to modify
only a single fact.

3. What is a deletion anomaly?
Ans: A deletion anomaly occurs whenever deleting a row inadvertently causes other data
to be deleted.

4. What is the cause of modification anomalies?
Ans: Poor database design causes the modification anomaly. A good database design
avoids modification anomalies by eliminating excessive redundancies.

5. What is a functional dependency?
Ans: A functional dependency is a constraint about two or more columns of a table. X
determines Y (X fi Y) if there exists at most one value of Y for every value of X.

6. How is a functional dependency like a candidate key?
Ans: You can think about functional dependency as identifying potential primary keys.
By stating that X fi Y, if X and Y are placed together in a table without other columns,
X is a candidate key. Every determinant (LHS) is a potential primary key if placed in a
table with the other columns that it determines.

7. Can a software design tool identify functional dependencies? Briefly explain your
answer.
Ans: No, a software design tool cannot identify functional dependencies. Functional
dependencies must be asserted during the database development process. Typically, the
database designer interacts with users to understand the functional dependencies that
exist for a table. Software design tools can aid a designer by eliminating FDs that do not
exist and by suggesting FDs that are not contradicted by the data. Design tools examine
sample rows in a table to see what functional dependencies do not hold. There are
several commercial database design tools that automate the process of eliminating
dependencies through examination of sample rows. Ultimately, the database designer
must make the final decision about what functional dependencies exist in a table.

8. What is the meaning of an FD with multiple columns on the right-hand side?
Ans: Multiple columns on the RHS abbreviate separate FDs with the LHS determining
each of the RHS columns.

9. Why should you be careful when writing FDs with multiple columns on the left-hand
side? Ans: Multiple columns on the LHS indicate that the combination of columns determines
the RHS column(s). You should be careful that you have written the LHS with the
minimal columns for the dependency. Otherwise, the normalization rules and software
procedures may not work as intended.

10. What is a normal form?
Ans: A normal form is a rule about allowable dependencies. Each normal form removes
certain kinds of redundancies.

11. What does 1NF prohibit?
Ans: 1NF prohibits nesting or repeating groups in tables. A table not in 1NF is
unnormalized or non-normalized.

12. What is a key column?
Ans: A column is a key column if it is part of a candidate key or a candidate key by
itself.

13. What is a non-key column?
Ans: A column is a non-key column if it is not a key column.

14. What kinds of FDs are not allowed in 2NF?
Ans: FDs in which part of a key determines a nonkey column.

15. What kinds of FDs are not allowed in 3NF?
Ans: FDs in which a nonkey column determine another nonkey column.

16. What is the combined definition of 2NF and 3NF?
Ans: A table is in 3NF if every nonkey column is dependent on a candidate key, the
whole candidate key, and nothing but candidate keys.

17. What kinds of FDs are not allowed in BCNF?
Ans: FDs in which the determinant is not a candidate key.

18. What is the relationship between BCNF and 3NF? Is BCNF a stricter normal form
than 3NF? Briefly explain your answer.
Ans: BCNF is the revised 3NF definition. Yes BCNF is stricter than 3NF in that every
table in BCNF is in 3NF but not every table in 3NF is in BCNF.
19. Why is the BCNF definition preferred to the 3NF definition?
Ans: BCNF is the preferred definition because it is a simpler definition and provides the
basis for the simple synthesis algorithm.

20. What are the special cases covered by BCNF but not by 3NF?
Ans: BCNF covers two special cases not covered by 3NF: (1) part of a key determines
part of a key and (2) a nonkey column determines part of a key.

21. Are the special cases covered by BCNF but not by 3NF significant? Ans: The special cases are not significant because they rarely occur.

22. What is the goal of the simple synthesis procedure?
Ans: The simple synthesis procedure is used to generate tables satisfying BCNF starting
with a list of functional dependencies.

23. What is a limitation of the simple synthesis procedure?
Ans: The simple synthesis procedure does not work well on complex dependency
structures. To make the synthesis procedure easy to use, some of the details have been
omitted. In particular, step 2 can be rather involved because there are more ways to
derive dependencies than transitivity. Even checking for transitivity can be difficult with
many columns. Even if you understand the complex details, step 2 cannot be done by
hand for complex dependency structures. For complex dependency structures, you need
to use a CASE tool even if you are an experienced database designer.

24. What is a transitive dependency?
Ans: A transitive dependency is an FD derived by the law of transitivity. The law of
transitivity says that if an object A is related to B, and B is related to C, then A is related
to C.

25. Are transitive dependencies permitted in 3NF tables? Explain why or why not.
Ans: No, 3NF prohibits transitive dependencies. Because transitive dependencies are
easy to overlook, the preferred definition of 3NF does not use transitive dependencies.

26. Why eliminate transitive dependencies in the simple synthesis procedure?
Ans: The simple synthesis procedure requires a complete list of FDs without
redundancies. Because transitive dependencies are redundant, they should be eliminated.

27. When is it necessary to perform the fifth step of the simple synthesis procedure?
Ans: When there are multiple candidate keys for a table, the fifth step is necessary.

28. How is relationship independence similar to statistical independence?
Ans: In statistical dependence, two variables are independent if knowing something
about one variable tells you nothing about another variable. If two variables are
independent, it is redundant to store data about how they are related. The concept of
relationship dependence is similar to statistical independence. If two relationships are
independent (that is, not related), it is redundant to store data about a third relationship.
You can derive the third relationship by combining the two essential relationships
through a join operation.

29. What kind of redundancy is caused by relationship independence?
Ans: Modification anomaly is caused by relationship independence.

30. How many columns does an MVD involve?
Ans: An MVD involves three columns.
31. What is a multivalued dependency (MVD)?
Ans: A multivalued dependency is a relationship that can be derived from other
relationships.

32. What is the relationship between MVDs and FDs?
Ans: MVDs are generalizations of FDs. Every FD is an MVD, but not every MVD is an
FD.

33. What is a non-trivial MVD?
Ans: An MVD that is not also an FD.

34. What is the goal of 4NF?
Ans: To prohibit redundancies caused by non trivial MVDs.

35. What are the advantages of using normalization as a refinement tool rather than as an
initial design tool?
Ans: In the refinement approach, you perform conceptual data modeling using the ERD.
If the design is large, you can split the conceptual data model into view design and view
integration. Through development of an ERD, you intuitively group related fields. There
is less normalization to perform, which ensures that you have not overlooked any
redundancies.

36. Why is 5NF not considered a practical normal form?
Ans: Because situations when a three-way relationship should be replaced with three
binary relationships (not two binary relationships as for 4NF) are rare, 5NF is generally
not considered a practical normal form.

37. Why is DKNF not considered a practical normal form?
Ans: Because there is no known algorithm for converting a table into DKNF. In addition,
it is not even known what tables can be converted to DKNF.

38. When is denormalization useful? Provide an example to depict when it may be
beneficial for a table to violate 3NF.
Ans: Students' responses may vary. However, the following is a sample response: When
a database is used predominantly for queries, denormalization may be appropriate.
Another time to consider denormalization is when an FD is not important. For example,
FDs Zip ? City, State in a customer table, these independencies may not be important to
maintain if there is not a need to manipulate zip codes independent of customers.

39. What are the two ways to use normalization in the database development process?
Ans: There are two opposite ways to use normalization in the database development
process: (i) as a refinement tool or (ii) as an initial design tool. In the refinement
approach, you perform conceptual data modeling using the Entity Relationship Model. In
the initial design approach, you use normalization techniques in conceptual data
modeling. Instead of drawing an ERD, you identify functional dependencies and apply a
normalization procedure like the simple synthesis procedure.
40. Why does this book recommend using normalization as a refinement tool, not as an
initial design tool?
Ans: Through development of an ERD, you intuitively group related fields. Much
normalization is accomplished in an informal manner without the tedious process of
recording functional dependencies. As a refinement tool, there is usually less
normalization to perform. The purpose is to ensure that you have not overlooked any
redundancies. Normalization provides a rigorous way to reason about the quality of the
design.


PROBLEM SOLUTIONS

Besides the problems presented here, the case study in Chapter 13 provides additional
practice. To supplement the examples in this chapter, Chapter 13 provides a complete
database design case including conceptual data modeling, schema conversion, and
normalization.
1. For the big university database table, list FDs with the column StdCity as the
determinant that are not true due to the sample data. With each FD that does not
hold, show the sample data that violate it. Remember that it takes two rows to
demonstrate a violation of an FD. The sample data are repeated in Table 7P–1 for
your reference.
Ans: stdcity FDs and sample data that violates the FDs. The rows refer to the sample
data below.
stdcity fi offerno is violated by the first two rows and the last two rows
stdcity fi offterm is violated by the last two rows fi grade is violated by the first two rows and the last two rows
stdcity fi courseno is violated by the first two rows and the last two rows fi crsdesc

stdss stdcity stdclas offerno offter offyea enrgrad course crsdesc
n s m r e no
s1 seattle jun o1 fall 2003 3.5 c1 db
s1 jun o2 fall 2003 3.3 c2 vb
s2 bothell jun o3 winter 2003 3.1 c3 oo s2 jun o2 fall 2003 3.4 c2 vb

2. Following on problem 1, list FDs with the column StdCity as the determinant that the
sample data do not violate. For each FD, add one or more sample rows and then
identify the sample data that violate the FD. Remember that it takes two rows to
demonstrate a violation of an FD.
Ans: stdcity FDs not violated by the original sample data are listed below along with a
reference to new rows (after row 4) that violate the FDs.
stdcity fi stdssn is violated by either the first or second row and the fifth row stdcity fi stdclass is violated by either the first or second row and the fifth row fi offyear is violated by either the first or second row and the fifth row

stdss stdcity stdclas offerno offter offyea enrgrad course crsdesc
n s m r e no
s1 seattle jun o1 fall 2003 3.5 c1 db
s1 jun o2 fall 2003 3.3 c2 vb
s2 bothell jun o3 winter 2003 3.1 c3 oo s2 jun o2 fall 2003 3.4 c2 Vb
seattle s3 SR O1 Fall 2004 3.3 C1 DB

3. For the big patient table, list FDs with the column PatZip as the determinant that are
not true due to the sample data. Exclude the FD PatZip fi PatCity because it is a
valid FD. With each FD that does not hold, show the sample data that violate it.
Remember that it takes two rows to demonstrate a violation of an FD. The sample
data are repeated in Table 7P–2 for your reference.
Ans: PatZip FDs and sample rows that violate the FDs. The rows refer to the sample data
below.
PatZip fi PatNo is not violated by the sample data fi PatAge
PatZip fi VisitDate is not violated by the sample data fi VisitNo is not violated by the sample data
PatZip fi ProvNo is violated by the first two rows fi ProvSpecialty
PatZip fi Diagnosis is violated by first two rows

VisitNo VisitDate PatNo PatAge PatCity PatZip ProvNo ProvSpecialty Diagnosis
V10020 1/13/2000 P1 35 DENVER 80217 D1 INTERNIST EAR INFECTION
V10020 P1 35 80217 D2 NURSE PRACTIONER INFLUENZA
V93030 1/20/2000 P3 17 ENGLEWOOD 80113 D2 OBGYN PREGNANCY
V82110 1/18/2000 P2 60 BOULDER 85932 D3 CARDIOLOGIST MURMUR

4. Following on problem 3, list FDs with the column PatZip as the determinant that
sample data do not violate. Exclude the FD PatZip ? PatCity because it is a valid
FD. For each FD, add one or more sample rows and then identify the sample data
that violate the FD. Remember that it takes two rows to demonstrate a violation of an
FD.

Ans: PatZip FDs not violated by the original sample data are listed below along with a
reference to the new rows (after row 4) that violate the FDs.
PatZip fi PatNo is violated by the third and fifth rows fi PatAge
PatZip fi VisitDate is violated by the third and fifth rows fi VisitNo is violated by the third and fifth rows
VisitNo VisitDate PatNo PatAge PatCity PatZip ProvNo ProvSpecialty Diagnosis
V10020 1/13/2000 P1 35 DENVER 80217 D1 INTERNIST EAR INFECTION P1 35 D2 NURSE PRACTIONER INFLUENZA
V93030 1/20/2000 P3 17 ENGLEWOOD 80113 D2 OBGYN PREGNANCY
V82110 1/18/2000 P2 60 BOULDER 85932 D3 CARDIOLOGIST MURMUR
V34210 P4 65 80113 D3 IRREGULAR BEAT


5. Apply the simple synthesis procedure to the FDs of the big patient table. The FDs are
repeated in Table 7P–3 for your reference. Show the result of each step in the
procedure. Include the primary keys and the foreign keys in the final list of tables.
Ans: The simple synthesis procedure is applied to the following list of FDs:

PatNo fi PatAge, PatCity, PatZip
PatZip fi PatCity
ProvNo fi ProvSpecialty
VisitNo fi PatNo, VisitDate, PatAge, PatCity, PatZip
VisitNo, DocNo fi Diagnosis

Step 1: There are no extraneous columns to remove
Step 2: Remove the following FDs because they can be derived through
transitivity:
VisitNo fi PatCity fi PatZip
VisitNo fi PatAge
PatNo fi PatCity

Step 3: Arrange the remaining FDs into groups by determinant

PatNo fi PatAge, PatZip
PatZip fi PatCity
ProvNo fi ProvSpecialty
VisitNo fi PatNo, VisitDate
VisitNo, DocNo fi Diagnosis

Step 4: For each FD group, make a table with the determinant as the primary
key. In the table list, the primary keys are underlined.
Patient (PatNo, PatAge, PatZip)
FOREIGN KEY (PatZip) REFERENCES ZipCode
ZipCode(PatZip, PatCity)
Provider(ProvNo, ProvSpecialty) Visit(VisitNo, VisitDate, PatNo)
FOREIGN KEY (PatNo) REFERENCES Patient
DiagnosisTbl(VisitNo, ProvNo, Diagnosis)
FOREIGN KEY (VisitNo) REFERENCES Visit
FOREIGN KEY (ProvNo) REFERENCES Provider

Step 5: Merge tables with the same columns. There is no work because no
duplicate tables are present.

6. The FD diagram in Figure 7P.1 depicts relationships among columns in an order
entry database. Figure 7P.1 shows FDs with determinants CustNo, OrderNo, ItemNo,
the combination of OrderNo and ItemNo, the combination of ItemNo and PlantNo,
and the combination of OrderNo and LineNo. In the bottom FDs, the combination of
LineNo and OrderNo determines ItemNo and the combination of OrderNo and
ItemNo determines LineNo. To test your understanding of dependency diagrams,
convert the dependency diagram into a list of dependencies organized by LHSs.

Ans:

CustNo fi CustBal, CustDiscount
OrdNo fi CustNo, ShipAddr, OrderDate
ItemNo fi ItemDesc
ItemNo, PlantNo fi ReorderPoint, QtyOnHand
ItemNo, OrdNo fi LineNo, QtyOrdered,
QtyOutstanding
ItemNo, LineNo fi ItemNo, QtyOrdered,
QtyOutstanding


7. Using the FD diagram (Figure 7.P1) and the FD list (solution to problem 6) as
guidelines, make a table with sample data. There are tow candidate keys for the
underlying table: the combination of OrderNo, ItemNo, and PlantNo and the
combination of OrderNo, LineNo, and PlantNo. Using sample data, identify
insertion, update, and deletion anomalies in the table.

Ans:

custno custbal orderno orderdate itemno itemdesc qtyord plantno reordpoint qtyonhand
ShipAddr
C1 100 O1 S1 2/1/2004 I1 Bolt 10 P1 10 15
C1 100 O1 S1 I2 Nut 5 P1 20 25
C2 50 O2 S2 2/3/2004 I1 Bolt 1 P1 10 15
C1 100 O3 S3 2/4/2004 I3 Screw 10 P1 10 14
C1 100 O3 S3 I3 10 P2 15 20

The above table is missing several fields due to space limitations. There are many
modification anomalies in the table:
Ø Insertion anomalies: cannot insert a new customer without having an order, item, and
a plant to stock the item.
Ø Update anomaly: must change the customer balance multiple times.
Ø Deletion anomaly: deleting an order (for example order O2) causes deletion of
customer if customer has only one order.


8. Derive 2NF tables starting with the FD list from problem 6 and the table from
problem 7.

Ans:
2NF tables are shown below:

O1(orderno, shipaddr, orderdate, custno, custbal, custdiscount)
O2(itemno, itemdesc)
O3(orderno, itemno, lineno, qtyordered, qtyoutstanding)
O4(plantno, itemno, qtyonhand, reorderpoint)

9. Derive 3NF tables starting with the FD list from problem 6 and the 2NF tables from
problem 8.

Ans:
All tables except O1 are in 3NF. For 3NF, O1 should be split as shown below:

O1.1(orderno, shipaddr, orderdate, custno)
O1.2(custno, custbal, custdiscount)

10. Following on problems 6 and 7, apply the simple synthesis procedure to produce
BCNF tables.

Ans:
The steps of the BCNF process are listed below.

Step 1: There are no extraneous columns to remove
Step 2: There are no transitively derived FDs. If the following FDs were in the
dependency diagram, they should be removed:
orderno fi custbal fi custdiscount
Step 3: Arrange the remaining FDs into groups by determinant

custno fi custbal, custdiscount
orderno fi orderdate, shipaddr, custno
itemno fi itemdesc
orderno, itemno fi qtyord, qtyoutstanding,
lineno
orderno, lineno fi
itemno
itemno, plantno fi qtyonhand, reorderpoint

Step 4: For each FD group, make a table with the determinant as the primary
key. In the table list, the primary keys are underlined.
Order(OrderNo, ShipAddr, OrderDate, CustNo)
FOREIGN KEY (CustNo) REFERENCES Customer
Customer(CustNo, CustBal, CustDiscount)
Item(ItemNo, ItemDesc)
OrderLine1(OrderNo, ItemNo, LineNo, QtyOrdered, QtyOutstanding)
FOREIGN KEY (OrderNo) REFERENCES Order
FOREIGN KEY (ItemNo) REFERENCES Item
OrderLine2(OrderNo, LineNo, ItemNo, QtyOrdered, QtyOutstanding)
OrderNo

PlantStocking(PlantNo, ItemNo, QtyOnHand, ReorderPoint)



Step 5: Merge tables with the same columns. The tables orderline1 and
orderline2 should be merged into one table. Either (orderno, itemno) or (orderno,
lineno) can be chosen as the primary key.

11. Modify your table design in problem 10 if the shipping address (ShipAddr) column
determines customer number (CustNo). Do you think that this additional FD is
reasonable? Briefly explain your answer.
Ans:
If shipaddr determines custno, the order table is not in BCNF because shipaddr is a
determinant but not a candidate key. The order table should be split into two tables as
shown below:

order(orderno, shipaddr, orderdate)