PRACTICAL 9  

Topic Review/Preview1. The following table contains some valid table records about a company's employees, their involved

project in terms of their starting date, location and the duration. Attributes for the primary key havebeen underlined. Determine all the additional (essential) functional dependencies. Then normalise thetable into 3NF. Please try this out on your own before checking the answers in the lecture notes.Functional dependencies (FDs) that can be derived from the PK and your additional FDs don't need tobe supplied.

empId empName dept manager projId projStartDate location weeksOnProj5 Smith Marketing Jones A 36130 Poole 115 Smith Marketing Jones B 36312 Plymouth 155 Smith Marketing Jones C 36404 Portsmouth 67 Bond Accounts Bloggs B 36312 Plymouth 37 Bond Accounts Bloggs D 36312 Berlin 9

9 King InfoSystems Hume C 36404 Portsmouth 10

10 Davies Accounts Bloggs A 36130 Poole 2110 Davies Accounts Bloggs B 36312 Belfast 1010 Davies Accounts Bloggs D 36312 Hamburg 12

Note: Explanations on the derivation on the functional dependencies from the table data isdesirable, while the normalisation for this question should be left to students themselves as it isalready fully explained in the lecture class.

2. Draw a Relation Diagram to model the following schema. Include the underlined primary keys, thestarred foreign keys and other attributes as shown in the schema. There is no need to use anytechnology to draw this -- just a pen and paper will be fine!

Pet(petId, petName, sex, petTypeId*) PetType(petTypeId, animalType, breed) Owner(ownerId, firstName, lastName, homePhoneNumber, streetAddress, suburb, postcode) PetAndOwner(ownerId*, petId*)

3. The following relation is NOT in 1NF. DepartmentNumber is the primary key of this messyrelation.

DepartmentName

DepartmentNumber

DepartmentManager

Department Location

Research 001 Dr Jackson {Canberra}Administration 002 Mrs Habib {Brisbane,Canberra,

Melbourne}

Communications 003 Miss Jones {Sydney, Melbourne}CommunityRelations

004 Mr Black {Brisbane, Canberra,Melbourne, Perth}

CustomerRelations

005 Mr Kranky {Hobart}

Why this table is not a relation?4. Remove the attribute that violates 1NF and place it in a separate relation -- re-draw the relation and the

additional relation to meet the demands of 1NF.The students need to remove the attribute DepartmentLocation that violates 1NF and placeit in a separate relation along with the primary key DepartmentNumber, see

Department Name Department Number Department ManagerResearch 001 Dr JacksonAdministration 002 Mrs HabibCommunications 003 Miss JonesCommunity Relations 004 Mr BlackCustomer Relations 005 Mr Kranky and

Department Number Department Location001 Canberra002 Brisbane002 Canberra002 Melbourne003 Sydney003 Melbourne004 Brisbane004 Canberra004 Melbourne004 Perth005 Hobart

Alternatively, if one flattens the original table, then the primary key becomes(DepartmentNumber, DepartmentLocation). The functional dependencies within thetable are

(DepartmentNumber,DepartmentLocation)->DepartmentName,DepartmentManager DepartmentNumber->DepartmentName,DepartmentManager

The flattened table is in 1NF but not in 2NF becauseDepartmentNumber->DepartmentName,DepartmentManager is a partial functional dependencyon the primary key. If this table is normalised to 2NF, then it ends up with exactly the same twotables derived in the above.

5. Use the functional dependency notation discussed in lectures (eg X → B) to summarize the functionaldependencies one would expect in a typical business application for the following attributes (we notethat each staff member may be allocated different amount of time to a different project):

Staff ID Staff Name Project ID Hours Project Name Project Location ID Project Location

Then normalise the relation into the Third Normal Form (3NF) according to the derived functionaldependencies.

6. Examine the Patient Medication Form for the Wellmeadows Hospital case study (seeAppendix B) shown in Figure 14.18.

a. Identify the functional dependencies represented by the attributes shown in the form in Figure14.18. State any assumptions that you make about the data and the attributes shown in this form.

b. Describe and illustrate the process of normalizing the attributes shown in Figure 14.18 toproduce a set of well-designed 3NF relations.

c. Identify the primary, alternate, and foreign keys in your 3NF relations.Attached: Wellmeandows Hospital Patient Medication Form.

Wellmeandows Hospital Patient Medication Form

Patient Number: P10034 Full Name: Robert MacDonald Ward Number: Ward 11 Bed Number: 84 Ward Name: Orthopaedic

Drug Number

Name Description Dosage Methodof

Admin

Unitper

Day

StartDate

FinishDate

10223 Morphine Pain Killer 10mg/ml Oral 50 24/03/08 24/04/0910334 Tetracyclene Antibiotic 0.5mg/ml IV 10 24/03/08 17/04/0810223 Morphine Pain Killer 10mg/ml Oral 10 25/04/09 02/05/10

Practical Questions7. Suppose we have already constructed all the tables for a database, such as the product-ordering

database described by in Practical 8 as prac8tables.sql. How do we draw a relation diagram throughthe use of Microsoft SQL Server?

First, make sure all the foreign keys are properly referenced which will link the tablesaccordingly. In this script, we observe that we need to uncomment

/* CONSTRAINT CUS_ORDER_FK FOREIGN KEY(CUS_CODE) REFERENCES CUSTOMER */

to enable the corresponding missing foreign key.

Execute the script to create all the tables if they are not yet created, see

and

Right click Database Diagrams on the left pane and choose New Database Diagram.Then pick the names of all the tables you want to put into the diagram, see below, and

click Add and then click Close to finish.

All the tables will appear as boxes in the diagram window. Drag the boxes around tobetter reflect the diagram, see

We observe that the lines in the above diagram are "loosely" connected in that the linebetween ORDER_DETAIL and PRODUCT, for instance, does not tell us which attributeof ORDER_DETAIL is actually the foreign key. We could drag the connection lines sothat they become more consistent with the format of the global relation diagrams in thetextbook, see the diagram below in which each connection line originates from a foreign

key attribute and ends in the corresponding primary key attribute.

Since this output diagram doesn't yet contain everything one wants to have according tothe requirements of the textbook for the Global Relation Diagram, we consequently addthe participation multiplicities and label the roles, see

(The image here is done by a copy/paste of the screenshot to Powerpoint, and by addingother features there, grouping everything together, and copy/pasting back to theMicrosoft Office Picture Manager). And of course a straightforward cropping gives

(actually an uncompleted global relation diagram)

8. Use SQL Server to build the tables for the Pet database -- based on the relation diagram you createdfor Question 1. Populate the tables with enough data to illustrate the integrity of the database on theprimary keys and foreign keys.

9. (Two-table join) Continuing from the previous question, formulate an SQL statement to list for allpets their petId, petName, animalType, breed, and sex. Sort the listing in the increasing order of petId.

10. (Aggregate function) Continuing from the previous question, write an SQL statement to list thetotal number of pets for each pair of pet type and breed. Sort the listing first in the decreasing order ofthe total number, then in the order of animalType and then in the order of breed.

11. (Multi-table join) Continuing from the previous question, write an SQL statement to list all the petdetails for all the owned pets.

12. Optional: MS ACCESS database can contain tables and data that physically exist at a remote site,such as on the School's SQL server. Some of the remote tables can be mapped/linked to a local MSACCESS database, as if they were defined locally, through the concept of Open DatabaseConnectivity (ODBC). When the linkage is successfully established, editing the content of thosemapped tables in MS ACCESS has the same effect of editing it on the remote server directly. Gothrough the procedure step by step to link to SQL server in MS ACCESS. Ensure you can connect toSQL server from an MS Access database.

13. Optional: We have done table joins in the previous practical, and have done some basic queries inMS ACCESS during the earlier weeks. It is handy to draw a comparison with how we completed themulti-table queries in MS ACCESS by following the Activity 3 in Introduction to MS ACCESS andby following the steps in Invoice in MS ACCESS.

14. Use the remaining practical time to complete any earlier prac work that is not completed yet. If you areup to date with your pracs, then you may use this time to work on the assignment.

Additional Exercises

α. For the staff-project database described and normalised earlier on in this practical, first draw its ERdiagram, then draw its relation diagram. Highlight the pertinent keys in the diagrams. You canignore participation multiplicities in these 2 diagrams.

β. (optional) Complete Question 7. Make sure no tables are empty and no fields are null. Then createthe Relation Diagram through the GUI of the SQL Server similar to the steps in Question 6, andtake a screenshot of the diagram.

γ. For the Pet database described in Q1 and Q7 in this practical, write an SQL statement to list all thepet owners and their respective total number of owned pets. For simplicity, you don't have to list theowners if they don't own any pets. Provide a screenshot for the result.

δ. Give a table of your own that is already in 1NF but not in 2NF, along with the functionaldependencies among the attributes of the table drawn as a diagram. Illustrate the normalisation ofyour table to 2NF. Do not use any tables that already appeared in lecture notes or the practicals. Donot just rename the attributes of tables there.

ACKNOWLEDGEMENT: This document may have included excerpts from the prescribed textbook by Thomas Connolly and Carolyn Begg, and isalso based on an earlier delivery some years back at WSU by Dr Eshan Vossough who may have in turn made use of other contributors' work.

Compiled and typeset by Zhuhan Jiang @2021.