🗊Презентация Practice exercises. Database design. Relational model. (Chapter 2, 3)

Категория: Информатика
Нажмите для полного просмотра!
Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №1Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №2Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №3Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №4Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №5Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №6Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №7Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №8Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №9Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №10Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №11Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №12Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №13Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №14Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №15Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №16Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №17Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №18Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №19Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №20Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №21Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №22Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №23Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №24Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №25Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №26Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №27Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №28Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №29Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №30Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №31Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №32Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №33Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №34Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №35Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №36Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №37Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №38Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №39Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №40Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №41Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №42Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №43Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №44Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №45Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №46Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №47
Скачать

Содержание ▲

Вы можете ознакомиться и скачать презентацию на тему Practice exercises. Database design. Relational model. (Chapter 2, 3). Доклад-сообщение содержит 47 слайдов. Презентации для любого класса можно скачать бесплатно. Если материал и наш сайт презентаций Mypresentation Вам понравились – поделитесь им с друзьями с помощью социальных кнопок и добавьте в закладки в своем браузере.

Слайды и текст этой презентации

Слайд 1


COP-5725 Practice Exercises Chapter 2: Database Design Chapter 3: Relational Model
Описание слайда:
COP-5725 Practice Exercises Chapter 2: Database Design Chapter 3: Relational Model

Слайд 2


Exercise 2.4 Problem A company database needs to store information about employees (identified by ssn, with salary and phone as attributes), departments (identified by dno, with dname and budget as attributes), and children of employees (with name and age as attributes).
Описание слайда:
Exercise 2.4 Problem A company database needs to store information about employees (identified by ssn, with salary and phone as attributes), departments (identified by dno, with dname and budget as attributes), and children of employees (with name and age as attributes).

Слайд 3


Exercise 2.4 Problem Employees work in departments; each department is managed by an employee; a child must be identified uniquely by name when the parent (who is an employee; assume that only one parent works for the company) is known. We are not interested in information about a child once the parent leaves the company. Draw an ER diagram that captures this information.
Описание слайда:
Exercise 2.4 Problem Employees work in departments; each department is managed by an employee; a child must be identified uniquely by name when the parent (who is an employee; assume that only one parent works for the company) is known. We are not interested in information about a child once the parent leaves the company. Draw an ER diagram that captures this information.

Слайд 4


Exercise 2.4 Solution First, we shall design the entities and relationships. “Employees work in departments…” “…each department is managed by an employee…” “…a child must be identified uniquely by name when the parent (who is an employee; assume that only one parent works for the company) is known.”
Описание слайда:
Exercise 2.4 Solution First, we shall design the entities and relationships. “Employees work in departments…” “…each department is managed by an employee…” “…a child must be identified uniquely by name when the parent (who is an employee; assume that only one parent works for the company) is known.”

Слайд 5


Exercise 2.4 Solution
Описание слайда:
Exercise 2.4 Solution

Слайд 6


Exercise 2.4 Solution Now, we will design the constraints. “…each department is managed by an employee…” “…a child must be identified uniquely by name when the parent (who is an employee; assume that only one parent works for the company) is known. “ “We are not interested in information about a child once the parent leaves the company.”
Описание слайда:
Exercise 2.4 Solution Now, we will design the constraints. “…each department is managed by an employee…” “…a child must be identified uniquely by name when the parent (who is an employee; assume that only one parent works for the company) is known. “ “We are not interested in information about a child once the parent leaves the company.”

Слайд 7


Exercise 2.4 Solution
Описание слайда:
Exercise 2.4 Solution

Слайд 8


Exercise 2.8 Problem Although you always wanted to be an artist, you ended up being an expert on databases because you love to cook data and you somehow confused database with data baste. Your old love is still there, however, so you set up a database company, ArtBase, that builds a product for art galleries. The core of this product is a database with a schema that captures all the information that galleries need to maintain.
Описание слайда:
Exercise 2.8 Problem Although you always wanted to be an artist, you ended up being an expert on databases because you love to cook data and you somehow confused database with data baste. Your old love is still there, however, so you set up a database company, ArtBase, that builds a product for art galleries. The core of this product is a database with a schema that captures all the information that galleries need to maintain.

Слайд 9


Exercise 2.8 Problem Galleries keep information about artists, their names (which are unique), birthplaces, age,and style of art. For each piece of artwork, the artist, the year it was made, its unique title, its type of art (e.g., painting, lithograph, sculpture, photograph), and its price must be stored. Pieces of artwork are also classified into groups of various kinds, for example, portraits, still lifes, works by Picasso, or works of the 19th century; a given piece may belong to more than one group.
Описание слайда:
Exercise 2.8 Problem Galleries keep information about artists, their names (which are unique), birthplaces, age,and style of art. For each piece of artwork, the artist, the year it was made, its unique title, its type of art (e.g., painting, lithograph, sculpture, photograph), and its price must be stored. Pieces of artwork are also classified into groups of various kinds, for example, portraits, still lifes, works by Picasso, or works of the 19th century; a given piece may belong to more than one group.

Слайд 10


Exercise 2.8 Problem Each group is identified by a name (like those just given) that describes the group. Finally, galleries keep information about customers. For each customer, galleries keep that person’s unique name, address, total amount of dollars spent in the gallery (very important!), and the artists and groups of art that the customer tends to like. Draw the ER diagram for the database.
Описание слайда:
Exercise 2.8 Problem Each group is identified by a name (like those just given) that describes the group. Finally, galleries keep information about customers. For each customer, galleries keep that person’s unique name, address, total amount of dollars spent in the gallery (very important!), and the artists and groups of art that the customer tends to like. Draw the ER diagram for the database.

Слайд 11


Exercise 2.8 Solution Like before, we begin with the entities and relationships. “…artists, their names (which are unique), birthplaces, age, and style of art.” “For each piece of artwork, the artist, the year it was made, its unique title, its type of art … and its price must be stored.”
Описание слайда:
Exercise 2.8 Solution Like before, we begin with the entities and relationships. “…artists, their names (which are unique), birthplaces, age, and style of art.” “For each piece of artwork, the artist, the year it was made, its unique title, its type of art … and its price must be stored.”

Слайд 12


Exercise 2.8 Solution “Pieces of artwork are also classified into groups of various kinds, … Each group is identified by a name (like those just given) that describes the group. “ For each customer, galleries keep that person’s unique name, address, total amount of dollars spent in the gallery (very important!), and the artists and groups of art that the customer tends to like.
Описание слайда:
Exercise 2.8 Solution “Pieces of artwork are also classified into groups of various kinds, … Each group is identified by a name (like those just given) that describes the group. “ For each customer, galleries keep that person’s unique name, address, total amount of dollars spent in the gallery (very important!), and the artists and groups of art that the customer tends to like.

Слайд 13


Exercise 2.8 Solution
Описание слайда:
Exercise 2.8 Solution

Слайд 14


Exercise 2.8 Solution Now we look at constraints. Although not explicitly mentioned in the problem, we assume that each piece of artwork had to be painted by an artist. We also assume that each piece of artwork was created by exactly one artist.
Описание слайда:
Exercise 2.8 Solution Now we look at constraints. Although not explicitly mentioned in the problem, we assume that each piece of artwork had to be painted by an artist. We also assume that each piece of artwork was created by exactly one artist.

Слайд 15


Exercise 2.8 Solution
Описание слайда:
Exercise 2.8 Solution

Слайд 16


Exercise 2.8 Solution Suppose we had several piece of artwork with the same title, and we told them apart by artist? Example: “What is Love?” by Cheryl D, “What is Love?” by Joe Brown, etc.
Описание слайда:
Exercise 2.8 Solution Suppose we had several piece of artwork with the same title, and we told them apart by artist? Example: “What is Love?” by Cheryl D, “What is Love?” by Joe Brown, etc.

Слайд 17


Exercise 2.8 Solution
Описание слайда:
Exercise 2.8 Solution

Слайд 18


Exercise 3.14 Problem Consider the scenario from Exercise 2.4, where you designed an ER diagram for a company database. Write SQL statements to create the corresponding relations and capture as many of the constraints as possible. If you cannot capture some constraints, explain why.
Описание слайда:
Exercise 3.14 Problem Consider the scenario from Exercise 2.4, where you designed an ER diagram for a company database. Write SQL statements to create the corresponding relations and capture as many of the constraints as possible. If you cannot capture some constraints, explain why.

Слайд 19


Exercise 3.14 ER Diagram from Exercise 2.4
Описание слайда:
Exercise 3.14 ER Diagram from Exercise 2.4

Слайд 20


Exercise 3.14 Solution First we begin with the entities “Employees” and “Departments. Translating these to SQL is straightforward.
Описание слайда:
Exercise 3.14 Solution First we begin with the entities “Employees” and “Departments. Translating these to SQL is straightforward.

Слайд 21


Exercise 3.14 Solution
Описание слайда:
Exercise 3.14 Solution

Слайд 22


Exercise 3.14 Solution Next, we translate the relationships, Manages and Dependents. We translate each these to a table mapping one entity to another. We also use foreign constraints to make sure every row in the relationship tables refers only to rows that exist in the entity tables.
Описание слайда:
Exercise 3.14 Solution Next, we translate the relationships, Manages and Dependents. We translate each these to a table mapping one entity to another. We also use foreign constraints to make sure every row in the relationship tables refers only to rows that exist in the entity tables.

Слайд 23


Exercise 3.14 Solution
Описание слайда:
Exercise 3.14 Solution

Слайд 24


Exercise 3.14 Solution Why did we make dno the primary key for Manages? Since each department can have at most one manager, each dno can appear at most once in the Manages table, making it a key for Manages. Note that if we had made (ssn, dno) the key for Manages, a department could have more than one Manager.
Описание слайда:
Exercise 3.14 Solution Why did we make dno the primary key for Manages? Since each department can have at most one manager, each dno can appear at most once in the Manages table, making it a key for Manages. Note that if we had made (ssn, dno) the key for Manages, a department could have more than one Manager.

Слайд 25


Exercise 3.14 Solution Finally, we translate the weak entity “Child” and its corresponding relationship “Dependent”
Описание слайда:
Exercise 3.14 Solution Finally, we translate the weak entity “Child” and its corresponding relationship “Dependent”

Слайд 26


Exercise 3.14 Solution
Описание слайда:
Exercise 3.14 Solution

Слайд 27


Exercise 3.18 Problem Write SQL statements to create the corresponding relations to the ER diagram you designed for Exercise 2.8. If your translation cannot capture any constraints in the ER diagram, explain why.
Описание слайда:
Exercise 3.18 Problem Write SQL statements to create the corresponding relations to the ER diagram you designed for Exercise 2.8. If your translation cannot capture any constraints in the ER diagram, explain why.

Слайд 28


Exercise 3.18 ER Diagram from Exercise 2.8
Описание слайда:
Exercise 3.18 ER Diagram from Exercise 2.8

Слайд 29


Exercise 3.18 Solution The entities are translated similarly to Exercise 3.4. Since these are fairly simple, we shall skip them. Now, we shall translate the relationships.
Описание слайда:
Exercise 3.18 Solution The entities are translated similarly to Exercise 3.4. Since these are fairly simple, we shall skip them. Now, we shall translate the relationships.

Слайд 30


Exercise 3.18 Solution
Описание слайда:
Exercise 3.18 Solution

Слайд 31


Exercise 3.18 Solution
Описание слайда:
Exercise 3.18 Solution

Слайд 32


Exercise 3.18 Solution
Описание слайда:
Exercise 3.18 Solution

Слайд 33


Exercise 3.18 Solution
Описание слайда:
Exercise 3.18 Solution

Слайд 34


Exercise 3.8 Problem Answer each of the following questions briefly. 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)
Описание слайда:
Exercise 3.8 Problem Answer each of the following questions briefly. 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)

Слайд 35


Exercise 3.8 Problem 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 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?
Описание слайда:
Exercise 3.8 Problem 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 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?

Слайд 36


Exercise 3.8 Solution for (1) An example of a foreign constraint that involves Dept is:
Описание слайда:
Exercise 3.8 Solution for (1) An example of a foreign constraint that involves Dept is:

Слайд 37


Exercise 3.8 Solution for (1) Furthermore, when a user attempts to delete a tuple from Dept, we can also delete all Works tuples that refer to it. disallow the deletion of the Dept tuple if some Works tuple refers to it. for every Works tuple that refers to it, set the did field to the did of some (existing) ’default’ department. for every Works tuple that refers to it, set the did field to null.
Описание слайда:
Exercise 3.8 Solution for (1) Furthermore, when a user attempts to delete a tuple from Dept, we can also delete all Works tuples that refer to it. disallow the deletion of the Dept tuple if some Works tuple refers to it. for every Works tuple that refers to it, set the did field to the did of some (existing) ’default’ department. for every Works tuple that refers to it, set the did field to null.

Слайд 38


Exercise 3.8 Problem 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 Write the SQL statements required to create the preceding relations, including appropriate versions of all primary and foreign key integrity constraints.
Описание слайда:
Exercise 3.8 Problem 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 Write the SQL statements required to create the preceding relations, including appropriate versions of all primary and foreign key integrity constraints.

Слайд 39


Exercise 3.8 Solution for (2) Emp(eid: integer, ename: string, age: integer, salary: real) Works(eid: integer, did: integer, pcttime: integer)
Описание слайда:
Exercise 3.8 Solution for (2) Emp(eid: integer, ename: string, age: integer, salary: real) Works(eid: integer, did: integer, pcttime: integer)

Слайд 40


Exercise 3.8 Solution for (2) Dept(did: integer, dname: string, budget: real, managerid: integer
Описание слайда:
Exercise 3.8 Solution for (2) Dept(did: integer, dname: string, budget: real, managerid: integer

Слайд 41


Exercise 3.8 Problem 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 Define the Dept relation in SQL so that every department is guaranteed to have a manager.
Описание слайда:
Exercise 3.8 Problem 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 Define the Dept relation in SQL so that every department is guaranteed to have a manager.

Слайд 42


Exercise 3.8 Problem 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 Write an SQL statement to add John Doe as an employee with eid = 101, age = 32 and salary = 15, 000.
Описание слайда:
Exercise 3.8 Problem 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 Write an SQL statement to add John Doe as an employee with eid = 101, age = 32 and salary = 15, 000.

Слайд 43


Exercise 3.8 Problem 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 Write an SQL statement to give every employee a 10 percent raise.
Описание слайда:
Exercise 3.8 Problem 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 Write an SQL statement to give every employee a 10 percent raise.

Слайд 44


Exercise 3.8 Problem 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 Write an 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.
Описание слайда:
Exercise 3.8 Problem 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 Write an 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.

Слайд 45


Exercise 3.8 Solution for (6)
Описание слайда:
Exercise 3.8 Solution for (6)

Слайд 46


Exercise 3.8 Solution for (6) What other actions can the system take on deleting a Dept tuple? What are the pros and cons of each action? On delete set null On delete set default On delete cascade
Описание слайда:
Exercise 3.8 Solution for (6) What other actions can the system take on deleting a Dept tuple? What are the pros and cons of each action? On delete set null On delete set default On delete cascade

Слайд 47


Practice exercises. Database design. Relational model. (Chapter 2, 3), слайд №47
Описание слайда:

Скачать

Похожие презентации
Mypresentation.ru
Загрузить презентацию