Full functional dependency

Assignment Instructions

 

Include the below numbering scheme in the submission. DO NOT include the Questions or other content from the instructions:

 

0) Was the Term Project topic approved Yes or No (minimum 15% deduction, if not approved)

 

• (5%) Term Paper Abstract (include the purpose of this “database”, explain one query – you expect the database to be able to process – the query must require the use of information from more than one entity)

 

2) (5%) Who will use, and who will administrator the database.

 

3) (10%) Explain the design methodology / approach you followed – refer to methods in our text.

 

4) (15%) Describe the initial entities (tables), and attributes (fields). State what a row represents in each table, and for each field: describe what the contents of each field represents, the type of field, and any constraints that you would like to see applied. Identify and describe the field(s) you believe could be the primary key for each entity.

 

5) (15%) Using the functional notation shown on pages 410-413 Examples 14.2, 14.3, 14.4 and 14.5, include a functional dependency analysis, ensure all fields in the database are included. Define functional dependency and explain one row of the functional notation using plain English, and fields from your project to demonstrate your understanding. SEE NOTE1

 

EXAMPLE 14.2 Example of a functional dependency that holds for all time

Consider the values shown in staffNo and sName attributes of the Staff relation in Figure 14.2. We see that for a specific staffNo—SL21—for example, we can determine the name of that member of staff as John White. Furthermore, it appears that for a specific sName—for example, John White—we can determine the staff number for that member of staff as SL21. Can we therefore conclude that the staffNo attribute functionally determines the sName attribute and/or that the sName attribute functionally determines the staffNo attribute? If the values shown in the Staff relation of Figure 14.2 represent the set of all possible values for staffNo and sName attributes, then the following functional dependencies hold:

staffNo →sName

sName →staffNo

However, if the values shown in the Staff relation of Figure 14.2 simply represent a set of values for staffNo and sName attributes at a given moment in time, then we are not so interested in such relationships between attributes. The reason is that we want to identify functional dependencies that hold for all possible values for attributes of a relation as these represent the types of integrity constraints that we need to identify. Such constraints indicate the limitations on the values that a relation can legitimately assume.

One approach to identifying the set of all possible values for attributes in a relation is to more clearly understand the purpose of each attribute in that relation. For example,

 

the purpose of the values held in the staffNo attribute is to uniquely identify each member of staff, whereas the purpose of the values held in the sName attribute is to hold the names of members of staff. Clearly, the statement that if we know the staff number (staffNo) of a member of staff, we can determine the name of the member of staff (sName) remains true. However, as it is possible for the sName attribute to hold duplicate values for members of staff with the same name, then we would not be able to determine the staff number (staffNo) of some members of staff in this category. The relationship between staffNo and sName is one-to-one (1:1): for each staff number there is only one name. On the other hand, the relationship between sName and staffNo is one-to-many (1:*): there can be several staff numbers associated with a given name. The functional dependency that remains true after consideration of all possible values for the staffNo and sName attributes of the Staff relation is:

staffNo →sName

An additional characteristic of functional dependencies that is useful for normalization is that their determinants should have the minimal number of attributes necessary to maintain the functional dependency with the attribute(s) on the righthand side. This requirement is called full functional dependency.

Full functional dependency

Indicates that if a and B are attributes of A relation, B is fully functionally dependent on A if B is functionally dependent on A, but not on any proper subset of A.

A functional dependency A→B is a full functional dependency if removal of any attribute from A results in the dependency no longer existing. A functional dependency A→B is a partial dependency if there is some attribute that can be removed from A and yet the dependency still holds. An example of how a full functional dependency is derived from a partial functional dependency is presented in Example 14.3.

EXAMPLE 14.3 Example of a full functional dependency

Consider the following functional dependency that exists in the Staff relation of Figure 14.2:

staffNo, sName →branchNo

It is correct to say that each value of (staffNo, sName) is associated with a single value of branchNo. However, it is not a full functional dependency, because branchNo is also functionally dependent on a subset of (staffNo, sName), namely staffNo. In other words, the functional dependency shown in the example is an example of a partial dependency. The type of functional dependency that we are interested in identifying is a full functional dependency as shown here:

staffNo →branchNo

Additional examples of partial and full functional dependencies are discussed in Section 14.7.

 

In summary, the functional dependencies that we use in normalization have the following characteristics:

• There is a one-to-one relationship between the attribute(s) on the left-hand side (determinant) and those on the right-hand side of a functional dependency. (Note that the relationship in the opposite direction—that is, from the right-hand to the left-hand side attributes—can be a one-to-one relationship or oneto-many relationship.)
• They hold for all time.
• The determinant has the minimal number of attributes necessary to maintain the dependency with the attribute(s) on the right-hand side. In other words, there must be a full functional dependency between the attribute(s) on the left-hand and right-hand sides of the dependency.

So far we have discussed functional dependencies that we are interested in for the purposes of normalization. However, there is an additional type of functional dependency called a transitive dependency that we need to recognize, because its existence in a relation can potentially cause the types of update anomaly discussed in Section 14.3. In this section we simply describe these dependencies so that we can identify them when necessary.

Transitive dependency

A condition where A, B, and C are attributes of a relation such that if A→B and B→C, then C is transitively dependent on A via B (provided that A is not functionally dependent on B or C).

An example of a transitive dependency is provided in Example 14.4.

EXAMPLE 14.4 Example of a transitive functional dependency

Consider the following functional dependencies within the StaffBranch relation shown in Figure 14.3:

staffNo →sName, position, salary, branchNo, bAddress

branchNo →bAddress

The transitive dependency branchNo→bAddress exists on staffNo via branchNo. In other words, the staffNo attribute functionally determines the bAddress via the branchNo attribute and neither branchNo nor bAddress functionally determines staffNo. An additional example of a transitive dependency is discussed in Section 14.8.

In the following sections we demonstrate approaches to identifying a set of functional dependencies and then discuss how these dependencies can be used to identify a primary key for the example relations.

14.4.2 Identifying Functional Dependencies

Identifying all functional dependencies between a set of attributes should be quite simple if the meaning of each attribute and the relationships between the attributes are well understood. This type of information may be provided by the enterprise in

 

the form of discussions with users and/or appropriate documentation, such as the users’ requirements specification. However, if the users are unavailable for consultation and/or the documentation is incomplete, then—depending on the database application—it may be necessary for the database designer to use their common sense and/or experience to provide the missing information. Example 14.5 illustrates how easy it is to identify functional dependencies between attributes of a relation when the purpose of each attribute and the attributes’ relationships are well understood.

EXAMPLE 14.5 Identifying a set of functional dependencies for the StaffBranch relation

We begin by examining the semantics of the attributes in the StaffBranch relation shown in Figure 14.3. For the purposes of discussion, we assume that the position held and the branch determine a member of staff’s salary. We identify the functional dependencies based on our understanding of the attributes in the relation as:

staffNo →sName, position, salary, branchNo, bAddress

branchNo →bAddress

bAddress →branchNo

branchNo, position →salary

bAddress, position →salary

We identify five functional dependencies in the StaffBranch relation with staffNo, branchNo, bAddress, (branchNo, position), and (bAddress, position) as determinants. For each functional dependency, we ensure that all the attributes on the right-hand side are functionally dependent on the determinant on the left-hand side.

 

6) (10%) Using the table names, state what a row represents in each table, and explain the one-to-many or the many-to-many table relationship included in your design. SEE NOTE3

 

7) Design (it is possible that the same table(s) are in 1NF, 2NF, and 3NF – in which case repeat the tables for each normal form level and explain why they meet that definition)

1. a) 1NF Assessment (10%)

Copy the 1NF definition from the text (include quotes and page number)

Using relational schema (table) notation (see page 111 Figure 4.2.6) or spreadsheet format (identify the primary keys), submit the table(s) that represent the 1NF table(s) for your term project.

Explain how each table meets the 1NF definition (use table and field names)

 

4.2.6 Representing Relational Database Schemas

A relational database consists of any number of normalized relations. The relational schema for part of the DreamHome case study is:

 

Branch	(branchNo, street, city, postcode)
Staff	(staffNo, fName, IName, position, sex, DOB, salary, branchNo)
PropertyForRent	(propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo)
Client	(clientNo, fName, IName, telNo, prefType, maxRent, eMail)
PrivateOwner	(ownerNo, fName, IName, address, telNo, eMail, password)
Viewing	(clientNo, propertyNo, viewDate, comment)
Registration	(clientNo, branchNo, staffNo, dateJoined)

 

The common convention for representing a relation schema is to give the name of the relation followed by the attribute names in parentheses. Normally, the primary key is underlined.

1. b) 2NF Assessment (10%)

Copy the 2NF definition from the text (include quotes and page number)

Using relational schema notation or spreadsheet format (identify the primary keys), transform (if necessary) and submit the table(s) that represent the 2NF table(s) for your term project.

Explain how each table meets the 2NF definition (use table and field names)

1. c) 3NF Assessment (10%)

Copy the 3NF definition from the text (include quotes and page number)

Using relational schema notation or spreadsheet format (identify the primary keys), transform (if necessary) and submit the table(s) that represent the 3NF table(s) for your term project.

Explain how each table meets the 3NF definition (use table and field names)

 

8) (10%) writing style, organization of submission, clarity, APA usage

 

Submit a Word compatible document. Name your document Last Name_TermProject (i.e. Smith_TermProject).

Contact the instructor with any questions.

 

NOTE1: Generic functional notation examples:

Field1 -> Field2

Field1 -> Field2, Field3

Field4, Field5 -> Field6

 

NOTE2: You can find an example of relational schema – table notation on page 111 immediately under 4.2.6 label which shows the relational schema for part of the Dream Home database.

The basic table notation structure is: Table-name (field, field1, field2…) underline the primary fields.

DO NOT INCLUDE SPREADSHEET FIGURES

 

NOTE3: Review the Chapter 12 Study Aid document, Table Relationships, and the Parts and Suppliers examples in the Terms and Concepts Forum – if you need to refresh your knowledge base.

 

HINT: review your tables – for this exercise, 2 tables should not have the same primary key “and” keep in mind that tables will need to be linked (to satisfy the one-to-many or many-to-many relationship requirement), if we start with a single unnormalized or 1NF table – the normalization process ensures the needed links are included – often we start with more than one table, and while that is OK – we need to ensure tables are properly linked (sometimes using foreign keys, and sometimes using a junction table)

 

References/ Work Cited:

Connolly, T., Begg, C. Database Systems: A Practical Approach to Design, Implementation, and Management. [VitalSource Bookshelf]. Retrieved from https://online.vitalsource.com/#/books/9781269875356/