Now consider a revised version of the previous database. Assume that the first attribute in each table is always the primary key, except for Assigned, where the primary key is a composite key consisting of (ssn, number).

Schema:

Employee (ssn, name, title, home-dept)

home-dept FOREIGN KEY REFERENCES Department(code)

Department (code, name, manager)

manager FOREIGN KEY REFERENCES Employee(ssn)
manager NOT NULL

Project (number, name, start-date, end-date, budget, sponsor)

sponsor FOREIGN KEY REFERENCES Department(code)
sponsor NOT NULL

Assigned (ssn, number)

ssn FOREIGN KEY REFERENCES Employee(ssn)
number FOREIGN KEY REFERENCES Project(number)

Which of the following statements is true?

Question

Now consider a revised version of the previous database. Assume that the first attribute in each table is always the primary key, except for Assigned, where the primary key is a composite key consisting of (ssn, number).

Schema:

Employee (ssn, name, title, home-dept)

home-dept FOREIGN KEY REFERENCES Department(code)

Department (code, name, manager)

manager FOREIGN KEY REFERENCES Employee(ssn)
manager NOT NULL

Project (number, name, start-date, end-date, budget, sponsor)

sponsor FOREIGN KEY REFERENCES Department(code)
sponsor NOT NULL

Assigned (ssn, number)

ssn FOREIGN KEY REFERENCES Employee(ssn)
number FOREIGN KEY REFERENCES Project(number)

Which of the following statements is true?

BlackTom AI · Accepted Answer

To tackle this question, I will first restate the scenario and list all answer choices, then evaluate each option one by one.

Question restated: We have a revised database schema where the first attribute in each table is the primary key, except for Assigned which uses a composite key (ssn, number). The schema is: Employee(ssn, name, title, home-dept) with home-dept as a foreign key to Department(code); Department(code, name, manager) where manager is a foreign key to Employee(ssn) and cannot be NULL; Project(number, name, start-date, end-date, budget, sponsor) where sponsor is a foreign key to Department(code) and cannot be NULL; Assigned(ssn, number) with ssn referencing Employee(ssn) and number referencing Project(number).

Options:
1) There may be an employee with no department.
2) All projects are assigned to employees.
3) Not every department needs a manager.
4) Not every manager is an employee.
5) There are some unsponsored projects.

Option 1: There may be an employee with no department.
- This is plausible because in Employee, home-dept is listed but there is no explicit NOT NULL constraint given for home-dept. If home-dept can be NULL, an Employee could exist without being linked to any Department, satisfying this statement. The schema does not force every employee to belong to a department, so this option is potentially true.

Option 2: All projects are assigned to employees.
- The existence of the Assigned relation indicates which employees are assigned to which projects, but nothing in the schema enforces that every project appears in Assigned. A project could exist without any corresponding row in Assigned, making this statement false in general.

Option 3: Not every department needs a manager.
- The Department relation specifies manager FOREIGN KEY REFERENCES Employee(ssn) and manager NOT NULL. This means every Department must have a manager, and that manager must be an existing Employee. Therefore, not every department needs a manager is contradicted by the schema and is false.

Option 4: Not every manager is an employee.
- Since Department.manager is a foreign key to Employee(ssn) and is NOT NULL, every manager must be an existing Employee. It is impossible for a manager to exist who is not an Employee in this design. Thus this statement is false.

Option 5: There are some unsponsored projects.
- Project.sponsor is NOT NULL and is a foreign key to Department(code). This guarantees that every project must have a sponsor, i.e., be sponsored by a department. Therefore the statement about unsponsored projects is false.

Summary of reasoning across options:
- Options 2, 3, 4, and 5 are all contradicted by the given constraints: projects require sponsors, departments require managers, managers must be employees, and assignments alone do not guarantee all projects are assigned. 
- Option 1 aligns with the possible NULL state for home-dept in Employee, given no NOT NULL constraint is specified for that attribute.

Putting it together, the only statement that can be true under this schema is the first one, while the others are incompatible with the specified constraints.

ECON3310001.1251 Quiz 8: Database Design

View Explanation

Log in for full answers

Similar Questions

In a consumer society, many adults channel creativity into buying things

Economic stress and unpredictable times have resulted in a booming industry for self-help products

People born without creativity never can develop it

A product has a selling price of $20, a contribution margin ratio of 40% and fixed cost of $120,000. To make a profit of $30,000. The number of units that must be sold is: Type the number without $ and a comma. Eg: 20000

Which of the following statement regarding cost is correct:

Under the assumptions used in cost-volume-profit analysis, as the activity increases:

The CVP and break-even analysis are supported by a range of important assumptions. Which of the following is not an assumption of the CVP:

More Practical Tools for International Students

Homework AI Solver

Stylized Writing Assistant

Plagiarism Checker Assistant

Citation Assistant

In-Class Translation Assistant

Class Notes Assistant

Quiz Search Assistant

Past Exam Questions by School

Smart Practice Assistant

Adaptive Practice

Making Your Study Simpler