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

Содержание

1. Practice exercises. Database design. Relational model. (Chapter 2, 3)
2. Exercise 2.4 Problem A company database needs
3. Exercise 2.4 Problem Employees work in departments;
4. Exercise 2.4 Solution First, we shall design
5. Exercise 2.4 Solution Departments Child Employees name
6. Exercise 2.4 Solution Now, we will design
7. Exercise 2.4 Solution Departments Child Employees name
8. Exercise 2.8 Problem Although you always wanted
9. Exercise 2.8 Problem Galleries keep information about
10. Exercise 2.8 Problem Each group is identified
11. Exercise 2.8 Solution Like before, we begin
12. Exercise 2.8 Solution “Pieces of artwork are
13. Exercise 2.8 Solution Artwork Group Customer Artist
14. Exercise 2.8 Solution Now we look at
15. Exercise 2.8 Solution Artwork Group Customer Artist
16. Exercise 2.8 Solution Suppose we had several
17. Exercise 2.8 Solution Artwork Group Customer Artist
18. Exercise 3.14 Problem Consider the scenario from
19. Exercise 3.14 ER Diagram from Exercise 2.4
20. Exercise 3.14 Solution First we begin with
21. Exercise 3.14 Solution Departments Employees phone ssn
22. Exercise 3.14 Solution Next, we translate the
23. Exercise 3.14 Solution Departments Employees phone ssn
24. Exercise 3.14 Solution Why did we make
25. Exercise 3.14 Solution Finally, we translate the
26. Exercise 3.14 Solution Child Employees name age
27. Exercise 3.18 Problem Write SQL statements to
28. Exercise 3.18 ER Diagram from Exercise 2.8
29. Exercise 3.18 Solution The entities are translated
30. Exercise 3.18 Solution Group Customer Like_Group name
31. Exercise 3.18 Solution Customer Artist Like_Artist birthplace
32. Exercise 3.18 Solution Artwork Artist Paints title
33. Exercise 3.18 Solution Artwork Group Classify
34. Exercise 3.8 Problem Answer each of the
35. Exercise 3.8 Problem Emp(eid: integer, ename: string,
36. Exercise 3.8 Solution for (1) An example
37. Exercise 3.8 Solution for (1) Furthermore, when
38. Exercise 3.8 Problem Emp(eid: integer, ename: string,
39. Exercise 3.8 Solution for (2) Emp(eid:
40. Exercise 3.8 Solution for (2) Dept(did:
41. Exercise 3.8 Problem Emp(eid: integer, ename: string,
42. Exercise 3.8 Problem Emp(eid: integer, ename: string,
43. Exercise 3.8 Problem Emp(eid: integer, ename: string,
44. Exercise 3.8 Problem Emp(eid: integer, ename: string,
45. Exercise 3.8 Solution for (6) DELETE FROM
46. Exercise 3.8 Solution for (6) What other
47. This is the end of the lecture! I hope you enjoyed it.

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),

Главная
Разное
Practice exercises. Database design. Relational model. (Chapter 2, 3)

Слайд 1COP-5725 PRACTICE EXERCISES
Chapter 2: Database Design
Chapter 3: Relational Model
M. Amanda Crick

Слайд 2Exercise 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).

Слайд 3Exercise 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.

Слайд 4Exercise 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.”

Слайд 5Exercise 2.4
Solution
Departments
Child
Employees
name
age
phone
ssn
salary
Dependent
budget
dno
dname
Manages
Works_In

Слайд 6Exercise 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.”

Слайд 7Exercise 2.4
Solution
Departments
Child
Employees
name
age
phone
ssn
salary
Dependent
budget
dno
dname
Manages
Works_In
Child
Dependent
name

Слайд 8Exercise 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.

Слайд 9Exercise 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.

Слайд 10Exercise 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.

Слайд 11Exercise 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.”

Слайд 12Exercise 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.

Слайд 13Exercise 2.8
Solution
Artwork
Group
Customer
Artist
Like_Group
Classify
Paints
Like_Artist
name
title
type
price
year
birthplace
style
age
name
name
address
amount
cust_id

Слайд 14Exercise 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.

Слайд 15Exercise 2.8
Solution
Artwork
Group
Customer
Artist
Like_Group
Classify
Paints
Like_Artist
name
title
type
price
year
birthplace
style
age
name
name
address
amount
cust_id

Слайд 16Exercise 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.

Слайд 17Exercise 2.8
Solution
Artwork
Group
Customer
Artist
Like_Group
Classify
Paints
Like_Artist
name
title
type
price
year
birthplace
style
age
name
name
address
amount
cust_id
Artwork
Paints
title

Слайд 18Exercise 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.

Слайд 19Exercise 3.14
ER Diagram from Exercise 2.4
Departments
Child
Employees
name
age
phone
ssn
salary
Dependent
budget
dno
dname
Manages
Works_In
Child
Dependent
name

Слайд 20Exercise 3.14
Solution
First we begin with the entities “Employees” and “Departments.
Translating these

to SQL is straightforward.

Слайд 21Exercise 3.14
Solution
Departments
Employees
phone
ssn
salary
budget
dno
dname
CREATE TABLE Employees(
ssn CHAR(10),
sal INTEGER,
phone CHAR(13),
PRIMARY KEY (ssn) )

CREATE TABLE

Departments (
dno INTEGER,
budget INTEGER,
dname CHAR(20),
PRIMARY KEY (dno) )

Слайд 22Exercise 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.

Слайд 23Exercise 3.14
Solution
Departments
Employees
phone
ssn
salary
budget
dno
dname
Manages
Works_In
CREATE TABLE Works_in(
ssn CHAR(10),
dno INTEGER,
PRIMARY KEY (ssn, dno),
FOREIGN KEY (ssn)
REFERENCES

Employees,
FOREIGN KEY (dno)
REFERENCES Departments)

CREATE TABLE Manages (
ssn CHAR(10),
dno INTEGER,
PRIMARY KEY (dno),
FOREIGN KEY (ssn)
REFERENCES Employees,
FOREIGN KEY (dno)
REFERENCES Departments)

Слайд 24Exercise 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.

Слайд 25Exercise 3.14
Solution
Finally, we translate the weak entity “Child” and its corresponding

relationship “Dependent”

Слайд 26Exercise 3.14
Solution
Child
Employees
name
age
phone
ssn
salary
Dependent
Child
Dependent
name
CREATE TABLE Dependents(
ssn CHAR(10),
name CHAR(10),
age INTEGER,
PRIMARY KEY (ssn, name),
FOREIGN KEY

(ssn) REFERENCES Employees,
ON DELETE CASCADE )

Слайд 27Exercise 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.

Слайд 28Exercise 3.18
ER Diagram from Exercise 2.8

Artwork
Group
Customer
Artist
Like_Group
Classify
Paints
Like_Artist
name
title
type
price
year
birthplace
style
age
name
name
address
amount
cust_id

Слайд 29Exercise 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.

Слайд 30Exercise 3.18
Solution
Group
Customer
Like_Group
name
name
address
amount
cust_id
CREATE TABLE Like Group (
name CHAR(20),
cust name CHAR(20),
PRIMARY KEY (name,

cust_name),
FOREIGN KEY (name) REFERENCES Group,
FOREIGN KEY (cust name) REFERENCES Customer)

Слайд 31Exercise 3.18
Solution
Customer
Artist
Like_Artist
birthplace
style
age
name
name
address
amount
cust_id
CREATE TABLE Like Artist (
name CHAR(20),
cust name CHAR(20),
PRIMARY KEY (name,

cust name),
FOREIGN KEY (name) REFERENCES Artist,
FOREIGN KEY (cust name) REFERENCES Customer)

Слайд 32Exercise 3.18
Solution
Artwork
Artist
Paints
title
type
price
year
birthplace
style
age
name
CREATE TABLE Artwork Paints(
title CHAR(20),
artist name CHAR(20),
type CHAR(20),
price INTEGER,
year INTEGER,
PRIMARY

KEY (title),
FOREIGN KEY (artist name)
REFERENCES Artist)

Слайд 33Exercise 3.18
Solution

Artwork
Group
Classify
name
title
type
price
year
CREATE TABLE Classify (
title CHAR(20),
name CHAR(20),
PRIMARY KEY (title, name),
FOREIGN

KEY (title) REFERENCES Artwork_Paints,
FOREIGN KEY (name) REFERENCES Group )

Paints

Слайд 34Exercise 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)

Слайд 35Exercise 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?

Слайд 36Exercise 3.8
Solution for (1)
An example of a foreign constraint that involves

Dept is:

CREATE TABLE Works (
eid INTEGER NOT NULL ,
did INTEGER NOT NULL ,
pcttime INTEGER,
PRIMARY KEY (eid, did),
UNIQUE (eid),
FOREIGN KEY (did) REFERENCES Dept )

Слайд 37Exercise 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.

Слайд 38Exercise 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.

Слайд 39Exercise 3.8
Solution for (2)

Emp(eid: integer, ename: string, age: integer, salary: real)
Works(eid:

integer, did: integer, pcttime: integer)

CREATE TABLE Emp (
eid INTEGER,
ename CHAR(10),
age INTEGER,
salary REAL,
PRIMARY KEY (eid) )

CREATE TABLE Works (
eid INTEGER,
did INTEGER,
pcttime INTEGER,
PRIMARY KEY (eid, did),
FOREIGN KEY (did) REFERENCES Dept,
FOREIGN KEY (eid) REFERENCES Emp,
ON DELETE CASCADE)

Слайд 40Exercise 3.8
Solution for (2)

Dept(did: integer, dname: string, budget: real, managerid: integer
CREATE

TABLE Dept (
did INTEGER,
budget REAL,
managerid INTEGER ,
PRIMARY KEY (did),
FOREIGN KEY (managerid) REFERENCES Emp,
ON DELETE SET NULL)

Слайд 41Exercise 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.

CREATE TABLE Dept (
did INTEGER,
budget REAL,
managerid INTEGER NOT NULL ,
PRIMARY KEY (did),
FOREIGN KEY (managerid) REFERENCES Emp)

Example of a Solution for (3)

Слайд 42Exercise 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.

INSERT
INTO Emp (eid, ename, age, salary)
VALUES (101, ’John Doe’, 32, 15000)

Solution for (4)

Слайд 43Exercise 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.

UPDATE Emp E
SET E.salary = E.salary * 1.10

Solution for (5)

Слайд 44Exercise 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.

Слайд 45Exercise 3.8
Solution for (6)
DELETE
FROM Dept D
WHERE D.dname = ’Toy’
Since the action

to take on deletion was not specified, the database takes no action by default That is, it rejects the deletion.

CREATE TABLE Works (
…
FOREIGN KEY (did) REFERENCES Dept,
…)

These are the example integrity constraints that affect Dept.

Слайд 46Exercise 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

Слайд 47This is the end of the lecture!
I hope you enjoyed it.

Скачать презентацию

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

Содержание

Слайд 1COP-5725 PRACTICE EXERCISESChapter 2: Database DesignChapter 3: Relational ModelM. Amanda Crick

Слайд 2Exercise 2.4ProblemA company database needs to store information about employees (identified

Слайд 3Exercise 2.4ProblemEmployees work in departments; each department is managed by an

Слайд 4Exercise 2.4SolutionFirst, we shall design the entities and relationships.“Employees work in

Слайд 5Exercise 2.4SolutionDepartmentsChildEmployeesnameagephonessnsalaryDependentbudgetdnodnameManagesWorks_In

Слайд 6Exercise 2.4SolutionNow, we will design the constraints.“…each department is managed by

Слайд 7Exercise 2.4SolutionDepartmentsChildEmployeesnameagephonessnsalaryDependentbudgetdnodnameManagesWorks_InChildDependentname

Слайд 8Exercise 2.8ProblemAlthough you always wanted to be an artist, you ended

Слайд 9Exercise 2.8ProblemGalleries keep information about artists, their names (which are unique),

Слайд 10Exercise 2.8ProblemEach group is identified by a name (like those just

Слайд 11Exercise 2.8SolutionLike before, we begin with the entities and relationships.“…artists, their

Слайд 12Exercise 2.8Solution“Pieces of artwork are also classified into groups of various

Слайд 13Exercise 2.8SolutionArtworkGroupCustomerArtistLike_GroupClassifyPaintsLike_Artistnametitletypepriceyearbirthplacestyleagenamenameaddressamountcust_id

Слайд 14Exercise 2.8SolutionNow we look at constraints. Although not explicitly mentioned in

Слайд 15Exercise 2.8SolutionArtworkGroupCustomerArtistLike_GroupClassifyPaintsLike_Artistnametitletypepriceyearbirthplacestyleagenamenameaddressamountcust_id

Слайд 16Exercise 2.8SolutionSuppose we had several piece of artwork with the same

Слайд 17Exercise 2.8SolutionArtworkGroupCustomerArtistLike_GroupClassifyPaintsLike_Artistnametitletypepriceyearbirthplacestyleagenamenameaddressamountcust_idArtworkPaintstitle

Слайд 18Exercise 3.14ProblemConsider the scenario from Exercise 2.4, where you designed an

Слайд 19Exercise 3.14ER Diagram from Exercise 2.4 DepartmentsChildEmployeesnameagephonessnsalaryDependentbudgetdnodnameManagesWorks_InChildDependentname

Слайд 20Exercise 3.14SolutionFirst we begin with the entities “Employees” and “Departments.Translating these

Слайд 21Exercise 3.14SolutionDepartmentsEmployeesphonessnsalarybudgetdnodnameCREATE TABLE Employees( ssn CHAR(10),sal INTEGER,phone CHAR(13),PRIMARY KEY (ssn) )CREATE TABLE

Слайд 22Exercise 3.14SolutionNext, we translate the relationships, Manages and Dependents.We translate each

Слайд 23Exercise 3.14SolutionDepartmentsEmployeesphonessnsalarybudgetdnodnameManagesWorks_InCREATE TABLE Works_in( ssn CHAR(10), dno INTEGER, PRIMARY KEY (ssn, dno), FOREIGN KEY (ssn) REFERENCES

Слайд 24Exercise 3.14SolutionWhy did we make dno the primary key for Manages?Since

Слайд 25Exercise 3.14SolutionFinally, we translate the weak entity “Child” and its corresponding

Слайд 26Exercise 3.14SolutionChildEmployeesnameagephonessnsalaryDependentChildDependentnameCREATE TABLE Dependents( ssn CHAR(10), name CHAR(10), age INTEGER,PRIMARY KEY (ssn, name),FOREIGN KEY

Слайд 27Exercise 3.18ProblemWrite SQL statements to create the corresponding relations to the

Слайд 28Exercise 3.18ER Diagram from Exercise 2.8 ArtworkGroupCustomerArtistLike_GroupClassifyPaintsLike_Artistnametitletypepriceyearbirthplacestyleagenamenameaddressamountcust_id

Слайд 29Exercise 3.18SolutionThe entities are translated similarly to Exercise 3.4. Since these

Слайд 30Exercise 3.18SolutionGroupCustomerLike_Groupnamenameaddressamountcust_idCREATE TABLE Like Group ( name CHAR(20),cust name CHAR(20),PRIMARY KEY (name,

Слайд 31Exercise 3.18SolutionCustomerArtistLike_Artistbirthplacestyleagenamenameaddressamountcust_idCREATE TABLE Like Artist (name CHAR(20),cust name CHAR(20),PRIMARY KEY (name,

Слайд 32Exercise 3.18SolutionArtworkArtistPaintstitletypepriceyearbirthplacestyleagenameCREATE TABLE Artwork Paints(title CHAR(20),artist name CHAR(20),type CHAR(20),price INTEGER,year INTEGER,PRIMARY

Слайд 33Exercise 3.18SolutionArtworkGroupClassifynametitletypepriceyearCREATE TABLE Classify ( title CHAR(20),name CHAR(20),PRIMARY KEY (title, name),FOREIGN

Слайд 34Exercise 3.8ProblemAnswer each of the following questions briefly. The questions are

Слайд 35Exercise 3.8ProblemEmp(eid: integer, ename: string, age: integer, salary: real)Works(eid: integer, did:

Слайд 36Exercise 3.8Solution for (1) An example of a foreign constraint that involves

Слайд 37Exercise 3.8Solution for (1) Furthermore, when a user attempts to delete a

Слайд 38Exercise 3.8ProblemEmp(eid: integer, ename: string, age: integer, salary: real)Works(eid: integer, did:

Слайд 39Exercise 3.8Solution for (2)Emp(eid: integer, ename: string, age: integer, salary: real)Works(eid:

Слайд 40Exercise 3.8Solution for (2)Dept(did: integer, dname: string, budget: real, managerid: integerCREATE

Слайд 41Exercise 3.8ProblemEmp(eid: integer, ename: string, age: integer, salary: real)Works(eid: integer, did:

Слайд 42Exercise 3.8ProblemEmp(eid: integer, ename: string, age: integer, salary: real)Works(eid: integer, did:

Слайд 43Exercise 3.8ProblemEmp(eid: integer, ename: string, age: integer, salary: real)Works(eid: integer, did:

Слайд 44Exercise 3.8ProblemEmp(eid: integer, ename: string, age: integer, salary: real)Works(eid: integer, did:

Слайд 45Exercise 3.8Solution for (6)DELETEFROM Dept DWHERE D.dname = ’Toy’Since the action

Слайд 46Exercise 3.8Solution for (6)What other actions can the system take on

Слайд 47This is the end of the lecture!I hope you enjoyed it.

Похожие презентации

Обратная связь

Что такое ThePresentation.ru?