Содержание
- 2. Exercise 2.4 Problem A company database needs to store information about employees (identified by ssn, with
- 3. Exercise 2.4 Problem Employees work in departments; each department is managed by an employee; a child
- 4. Exercise 2.4 Solution First, we shall design the entities and relationships. “Employees work in departments…” “…each
- 5. Exercise 2.4 Solution Departments Child Employees name age phone ssn salary Dependent budget dno dname Manages
- 6. Exercise 2.4 Solution Now, we will design the constraints. “…each department is managed by an employee…”
- 7. Exercise 2.4 Solution Departments Child Employees name age phone ssn salary Dependent budget dno dname Manages
- 8. Exercise 2.8 Problem Although you always wanted to be an artist, you ended up being an
- 9. Exercise 2.8 Problem Galleries keep information about artists, their names (which are unique), birthplaces, age,and style
- 10. Exercise 2.8 Problem Each group is identified by a name (like those just given) that describes
- 11. Exercise 2.8 Solution Like before, we begin with the entities and relationships. “…artists, their names (which
- 12. Exercise 2.8 Solution “Pieces of artwork are also classified into groups of various kinds, … Each
- 13. Exercise 2.8 Solution Artwork Group Customer Artist Like_Group Classify Paints Like_Artist name title type price year
- 14. Exercise 2.8 Solution Now we look at constraints. Although not explicitly mentioned in the problem, we
- 15. Exercise 2.8 Solution Artwork Group Customer Artist Like_Group Classify Paints Like_Artist name title type price year
- 16. Exercise 2.8 Solution Suppose we had several piece of artwork with the same title, and we
- 17. Exercise 2.8 Solution Artwork Group Customer Artist Like_Group Classify Paints Like_Artist name title type price year
- 18. Exercise 3.14 Problem Consider the scenario from Exercise 2.4, where you designed an ER diagram for
- 19. Exercise 3.14 ER Diagram from Exercise 2.4 Departments Child Employees name age phone ssn salary Dependent
- 20. Exercise 3.14 Solution First we begin with the entities “Employees” and “Departments. Translating these to SQL
- 21. Exercise 3.14 Solution Departments Employees phone ssn salary budget dno dname CREATE TABLE Employees( ssn CHAR(10),
- 22. Exercise 3.14 Solution Next, we translate the relationships, Manages and Dependents. We translate each these to
- 23. Exercise 3.14 Solution Departments Employees phone ssn salary budget dno dname Manages Works_In CREATE TABLE Works_in(
- 24. Exercise 3.14 Solution Why did we make dno the primary key for Manages? Since each department
- 25. Exercise 3.14 Solution Finally, we translate the weak entity “Child” and its corresponding relationship “Dependent”
- 26. Exercise 3.14 Solution Child Employees name age phone ssn salary Dependent Child Dependent name CREATE TABLE
- 27. Exercise 3.18 Problem Write SQL statements to create the corresponding relations to the ER diagram you
- 28. Exercise 3.18 ER Diagram from Exercise 2.8 Artwork Group Customer Artist Like_Group Classify Paints Like_Artist name
- 29. Exercise 3.18 Solution The entities are translated similarly to Exercise 3.4. Since these are fairly simple,
- 30. Exercise 3.18 Solution Group Customer Like_Group name name address amount cust_id CREATE TABLE Like Group (
- 31. Exercise 3.18 Solution Customer Artist Like_Artist birthplace style age name name address amount cust_id CREATE TABLE
- 32. Exercise 3.18 Solution Artwork Artist Paints title type price year birthplace style age name CREATE TABLE
- 33. Exercise 3.18 Solution Artwork Group Classify name title type price year CREATE TABLE Classify ( title
- 34. Exercise 3.8 Problem Answer each of the following questions briefly. The questions are based on the
- 35. Exercise 3.8 Problem Emp(eid: integer, ename: string, age: integer, salary: real) Works(eid: integer, did: integer, pcttime:
- 36. Exercise 3.8 Solution for (1) An example of a foreign constraint that involves Dept is: CREATE
- 37. Exercise 3.8 Solution for (1) Furthermore, when a user attempts to delete a tuple from Dept,
- 38. Exercise 3.8 Problem Emp(eid: integer, ename: string, age: integer, salary: real) Works(eid: integer, did: integer, pcttime:
- 39. Exercise 3.8 Solution for (2) Emp(eid: integer, ename: string, age: integer, salary: real) Works(eid: integer, did:
- 40. Exercise 3.8 Solution for (2) Dept(did: integer, dname: string, budget: real, managerid: integer CREATE TABLE Dept
- 41. Exercise 3.8 Problem Emp(eid: integer, ename: string, age: integer, salary: real) Works(eid: integer, did: integer, pcttime:
- 42. Exercise 3.8 Problem Emp(eid: integer, ename: string, age: integer, salary: real) Works(eid: integer, did: integer, pcttime:
- 43. Exercise 3.8 Problem Emp(eid: integer, ename: string, age: integer, salary: real) Works(eid: integer, did: integer, pcttime:
- 44. Exercise 3.8 Problem Emp(eid: integer, ename: string, age: integer, salary: real) Works(eid: integer, did: integer, pcttime:
- 45. Exercise 3.8 Solution for (6) DELETE FROM Dept D WHERE D.dname = ’Toy’ Since the action
- 46. Exercise 3.8 Solution for (6) What other actions can the system take on deleting a Dept
- 48. Скачать презентацию