1

Suppose a relational database has employee and company entity with workFor relation, such as

employee(ssn, first_name, last_name), where ssn is the key.

company(company_name, location), where company_name is the key.

workFor(ssn, company_name), where ssn and company_name are foreign keys.

Suppose the database is huge, and we need to query the number of employees for a company very often. Rather than request a query each time, we decide to add a attribute of number_of_employ somewhere in the relational schema for performance consideration. My question is that if we add the number_of_employee to the company(company_name, location, number_of_employee) relation, does it still satisfy the Boyce–Codd normal form (BCNF)? And is it a good design?

Intuitively, I do not think it still satisfies the BCNF because the company_name alone can not determine this attributes (and it is not a key any more). Is there any term for this "aggregation" attribute of number_of_employ, which is determined not by a specific value but by the whole workFor relation? What would be a good schema design for it if I have to add this attribute somewhere in my schema?

jaedong
  • 47
  • 6
  • Why company_name is not an attribute of employee? Does an employee work for multiple companies? – Renzo Jan 08 '18 at 21:22
  • I neglect this when I asked this question. We could assume an employee is allowed to work for more than one company. Where should I add the number_of_employee attribute? Add it to company relation? Or make a new relation with companyCount(company_name, number_of_employee)? – jaedong Jan 08 '18 at 22:03
  • Add it to company, then add triggers to maintain it consistent with the data. – Renzo Jan 08 '18 at 23:28
  • What it stopping you from just applying the definition of BCNF? Asking us is asking us to rewrite your textbook & do your homework. And what does "good design" mean? Again, if you are asking us then you are asking us to write a textbook & apply it. PS "company_name alone can not determine this attribute" does not use "determine" in the sense it is meant in normalization, because in that table it *does* functionally determine. So again, you need to read & apply your textbook, & ask a question when you are stuck. – philipxy Jan 10 '18 at 03:18
  • 1. The term for such an "aggregation" is... an aggregation (or aggregate (value)). 2. Do not think in terms of attributes, think in terms of business/application relation(ship)s/associations (on attributes) of interest to you, which is what relations/tables represent in the Relational Model (and the Entity-Relationship Model). (Once you have those plus what situtations can arise you know what states can arise & hence the constraints.) PS Please do not clarify in comments, edit your post. – philipxy Jan 10 '18 at 03:39
  • Possible duplicate of [Can I use a counter in a database Many-to-Many field to reduce lookups?](https://stackoverflow.com/questions/45407402/can-i-use-a-counter-in-a-database-many-to-many-field-to-reduce-lookups) – philipxy Feb 20 '18 at 00:23

0 Answers0