ACS-2814-003

Application of Database Systems

Dr. Mary Adedayo

Question: Can you explain an Identifying relationship? If there is a total participation, must the relationship be an identifying relationship?

Answer: A relationship between 2 entity types, say A and B is an identifying relationship if the key in one of the entity types (say A) is required to identify instances of the other entity type B. The entity type B in this case would be a weak entity type. Being a weak entity also implies that B’s participation in the relationship would be a total participation. For example, if we need the course number of a course to identify the section, then section would be a weak entity that has a total participation in the relationship between course and section. And the relationship would be an identifying relationship because section needs the key of course to be uniquely identified.

Note that, it is possible for an entity type to have a total participation without necessarily being a weak entity. For example, the Dean relationship between College and Instructors that we have in lab 8 shows that the participation from college is a total participation even though neither of the entity types are weak entities. This implies that each college must participate in the relationship meaning each college must have a dean. The relationship is not an identifying relationship because neither college nor Instructor entity types needs the key of the other entity to identify their instances.

Question: For assignment 3, how would my database respond to “what project(s) an employee was working on at any given time.”? Do I have to fill in missing data?

Answer: I have a feeling you are creating your database based off the description rather than your ERD and this would be a wrong approach. Please ensure that you create your ERD first and then map it to a database.

“What project(s) an employee was working on at any given time” is an example of a question that could be asked. We know that “A project is identified by its number and also its name and has a location.” We also know that “An employee must work on at least one project, and a project must have at least one employee who is working on it”, when you map your ERD to a database, you should be able to list for example, the names of projects that an employee is working on.

If the value of a specific attribute is not given, you are welcome to make up that value.

Question: For assignment 4 question 1, how do I write the DDL to handle the validation rule on the UnitPrice column?

Answer: Although this can be done using SQL, this concept is taught at higher level database courses and is not a requirement of ACS-2814 (which is why it was not taught in our discussion of DDL). So, with regards to this question, your DDL is not expected to create the validation rule.

Question: When writing my DDL query, I get the error, “No unique index found for the referenced field of the primary table”. What does this mean, and how do I fix it?

Answer: This means (like the error states) that you do not have a unique index for the field that is being referenced. A foreign key (say in table A) is typically associated with some primary key in the associated table (say table B). In the DDL query, you would need to add a foreign key to table A and not table B, if the table A and B are swapped in the DDL statement than you would be trying to do the opposite of what you actually intended, which might lead to this error. To fix this, you need to ensure that the foreign key is being added to the correct table.

Question: When writing my DDL query, I get the error, “Relationship must be on the same number of fields with the same data types”. What does this mean, and how do I fix it?

Answer: This means (like the error states) that you are trying to create a foreign key that is referencing a column with a different data type. This may happen specifically when using an autonumber data type, in which case the associated data type is a Long Integer (LONG). To fix this, you need to make sure the foreign key and the associated column have the same data type. Note that the NUMBER data type in MS can be further broken down into specific data type based on the field size e.g. INTEGER, LONG, DOUBLE, SINGLE, BYTE e.t.c. For example, a COUNTER is by default a LONG (shown as Long Integer) in the field size. You need to ensure that the data types are the same for fields that reference one another.

Question: For Assignment 4, how do I write the DDL to handle validation rules, default values, input mask and other things we did not discuss in the note?

Answer: Although there are DDL statements to achieve this, they go beyond the scope of ACS-2814 and will be covered in higher level database courses (ACS-3902). The focus (with respect to DDL) in ACS-2814 is mainly around creating the structure of the table (column, data types, primary key) and setting the referential integrity (foreign key). So, details like validation rules, default value, input masks e.t.c. can be ignore in the assignment.