Question

Consider the following relation schema about project meetings:

PMG(projID, title, type, manager, jobID, start-date, end-date, contractor, contractNo)

Some notes on the semantics of attributes are as follows:
• Each project has a unique project ID (projID) and also has a title, type and manager. Each manager has a specialty project type.
• A project often contracts jobs to contractors with start-date and end-date. Contracts are identified by contract numbers (contractNo), but contract details are out of the scope of the database.

FDs based on business rules are given as follows:
• projID → title, type, manager
• manager → type
• jobID → projID, start-date, end-date, contractor
• projID, title, jobID → contractNo
• contractNo → jobID, contractor, start-date, end-date
• jobID → contractNo

Answer questions below:
2.1. (3 points) The given FDs have redundancies. Give the minimal basis for the given FDs.
2.2. (3 points) The PMG relation is not in BCNF or 3NF. Explain why. Your explanation must be based on the functional dependencies in Question 2.1.
2.3. (3 points) Decompose the PMG relation into relations in BCNF or 3NF. Your decomposition must keep all functional dependencies and must be lossless. For each resultant relation, discuss if it is in BCNF or 3NF and indicate the primary key (underline) and any foreign keys (*). Note that relations must be written in the form as shown in the examples below:
Student(sno, name, address)
Course(cno, title)
Take(sno*, cno*, grade)

Note: Please mention the question numbers clearly before answering the question. (e.g. 2.1 - (then the answer)

Answer 1