1.Answer each of the following questions brie?y. The questions are based on the following relational schema:
Emp(eid: integer, ename: string, age: integer, salary: real) Works(eid: integer, did: integer, pcttime: integer)
Dept(did: integer, dname: string, budget: real, managerid: integer)
1. Give an example of a foreign key constraint that involves the Dept relation. What are the options for enforcing this constraint when a user attempts to delete a Dept tuple?
2. Write the SQL statements required to create the preceding relations, including appropriate versions of all primary and foreign key integrity constraints.
3. De?ne the Dept relation in SQL so that every department is guaranteed to have a manager.
4. Write an SQL statement to add John Doe as an employee with eid = 101, age = 32 and salary = 15,000.
5. Write an SQL statement to give every employee a 10 percent raise.
6. Write an SQL statement to delete the Toy department. Given the referential integrity constraints you chose for this schema, explain what happens when this statement is executed.
2.What is a foreign key constraint? Why are such constraints important? What is referential integrity?
3.Examples of non-candidate keys include the following: {name}, {age}. (Note that {gpa} can not be declared as a non-candidate key from this evidence alone even though common sense tells us that clearly more than one student could have the same grade point average.)
You cannot determine a key of a relation given only one instance of the relation. The fact that the instance is “legal” is immaterial. A candidate key, as de?ned here, is a key, not something that only might be a key. The instance shown is just one possible “snapshot” of the relation. At other times, the same relation may have an instance (or snapshot) that contains a totally di?erent set of tuples, and we cannot make predictions about those instances based only upon the instance that we are given.
4.many distinct tuples are in a relation instance with cardinality 22?
5.What is the di?erence between a candidate key and the primary key for a given relation? What is a superkey?