Question

Suppose we have a database for an university. The requirements are as follows: Professors have an SSN, a name, an age, a rank, and a research specialty. Projects have a project number, a sponsor name (e.g., NSF), a starting date, an ending date, and a budget. Graduate students have an SSN, a name, an age, and a degree program (e.g., M.S. or Ph.D.). Each project is managed by one professor (known as the project’s principal investigator). Each project is worked on by one or more professors (known as the project’s co-investigators). Professors can manage and/or work on multiple projects. Each project is worked on by one or more graduate students (known as the project’s research assistants). When graduate students work on a project, a professor must supervise their work on the project. Graduate students can work on multiple projects, in which case they will have a (potentially different) supervisor for each one. The FDs for the above requirements are given as,

PSSN → Name, Age, Rank, Specialty; Proj → Sponsor, Start, End, Budget; GSSN → Name, Age, Program • Proj → PI; GSSN, Proj → Supervisor • PI → PSSN; Supervisor → PSSN. Answer the following questions: Create one relational schema (R) with all the attributes. How many candidate keys does R have? Justify your answer. Do a BCNF decomposition. Is it lossless? Is it dependency preserving? Do a 3NF decomposition. Is it lossless? Is it dependency preserving? Are there any FDs that this example has which are not in F+?

Answer 1

The relational schema for the university database would include entities and relationships with multiple attributes. The number of candidate keys for R depends on the specific combination of attributes in the schema, and BCNF or 3NF decomposition's characteristics depend on the decomposition process and original functional dependencies.

Step-by-step explanation:

The university database described involves multiple entities: professors, projects, and graduate students, along with various relationships among them. Creating a relational schema with all attributes would involve including the relevant fields for each entity and their relationships, such as professor SSN (PSSN), name, age, rank, specialty, project number (Proj), sponsor name, starting and ending dates, budget, graduate student SSN (GSSN), and degree program.In terms of candidate keys, the candidate keys for the relation would depend on the attributes that uniquely identify a record within the database. The given functional dependencies suggest that both PSSN and Proj are candidate keys for their respective entities, with PSSN being a unique identifier for professors and Proj for projects. However, without additional information regarding how attributes are combined in the single relational schema, it's not possible to determine the exact number of candidate keys for R.A BCNF decomposition would break down the schema into smaller relations that satisfy Boyce-Codd Normal Form, ensuring that each non-trivial functional dependency's left-hand side is a superkey for that relation. Determining whether the decomposition is lossless and dependency preserving requires analyzing the functional dependencies and the resulting relations post-decomposition.Similarly, a 3NF decomposition would decompose the schema into relations that satisfy Third Normal Form, which prevents transitive dependencies. Again, whether this decomposition is lossless and preserves all functional dependencies depends on the process followed during decomposition and the functional dependencies of the original schema.Without the exact detailing of R and the process of decomposition, it's not possible to discuss the presence of functional dependencies that are not in the closure of F (F+).