Data Modeling and Databases

Содержание

Слайд 2

Q1 (10 points) Design an E/R diagram describing the following domain:

Q1

(10 points) Design an E/R diagram describing the following domain:
· A

Person has attributes pid (key) and name.
· A Skier is a type of Person with attribute ski_size.
· A Snowboarder is a type of Person with attribute board_size.
· A PairOfSkis has attribute sid (key) and model.
· A Snowboard has attribute sid (key) and model.
· A Skier owns zero or more PairOfSkis. The ownership relation has a purchase price. A PairOfSkis is owned by at most one Skier.
· A Snowboarder owns zero or more Snowboards. The ownership relation has a purchase price. A Snowboard is owned by at most one Snowboarder.
· A Person can rent a PairOfSkis or a Snowboard. A person cannot rent more than one PairOfSkis or one Snowboard at the same time. A person cannot rent a PairOfSkis and a Snowboard at the same time either. A piece of equipment can be rented by at most one person at a time. The rental comes with a start date and an end date.
Слайд 3

Q1: Solution

Q1: Solution

Слайд 4

Q1: Solution

Q1: Solution

Слайд 5

Q1. Common mistakes: Owns relationship A Skier owns zero or more

Q1. Common mistakes: Owns relationship

A Skier owns zero or more PairOfSkis.

The ownership relation has a purchase price.
A PairOfSkis is owned by at most one Skier.
Messing up the notation
Слайд 6

Q1. Common mistakes: Owns relationship A Skier owns zero or more

Q1. Common mistakes: Owns relationship

A Skier owns zero or more PairOfSkis.

The ownership relation has a purchase price.
A PairOfSkis is owned by at most one Skier.
Only one pair of skies for a Skier?!
Слайд 7

Q1. Common mistakes: Owns relationship A Skier owns zero or more

Q1. Common mistakes: Owns relationship

A Skier owns zero or more PairOfSkis.

The ownership relation has a purchase price.
A PairOfSkis is owned by at most one Skier.
Should each and every pair of skies be owned by someone?!
Слайд 8

Q1. Common mistakes: Rents relationship A Person can rent a PairOfSkis

Q1. Common mistakes: Rents relationship

A Person can rent a PairOfSkis or

a Snowboard. A person cannot rent more than one PairOfSkis or one Snowboard at the same time. A person cannot rent a PairOfSkis and a Snowboard at the same time either. A piece of equipment can be rented by at most one person at a time. The rental comes with a start date and an end date.
Why is this not right?
Слайд 9

Q1. Common mistakes: Rents relationship A Person can rent a PairOfSkis

Q1. Common mistakes: Rents relationship

A Person can rent a PairOfSkis or

a Snowboard. A person cannot rent more than one PairOfSkis or one Snowboard at the same time. A person cannot rent a PairOfSkis and a Snowboard at the same time either. A piece of equipment can be rented by at most one person at a time. The rental comes with a start date and an end date.
Why is this not right?
Слайд 10

Q1. Common mistakes: Rents relationship A Person can rent a PairOfSkis

Q1. Common mistakes: Rents relationship

A Person can rent a PairOfSkis or

a Snowboard. A person cannot rent more than one PairOfSkis or one Snowboard at the same time. A person cannot rent a PairOfSkis and a Snowboard at the same time either. A piece of equipment can be rented by at most one person at a time. The rental comes with a start date and an end date.
Why is this not right?
Слайд 11

Q1. Common mistakes: Rents relationship A Person can rent a PairOfSkis

Q1. Common mistakes: Rents relationship

A Person can rent a PairOfSkis or

a Snowboard. A person cannot rent more than one PairOfSkis or one Snowboard at the same time. A person cannot rent a PairOfSkis and a Snowboard at the same time either. A piece of equipment can be rented by at most one person at a time. The rental comes with a start date and an end date.
Duplicate sid fields
Слайд 12

Q1. Common mistakes: Rents relationship A Person can rent a PairOfSkis

Q1. Common mistakes: Rents relationship

A Person can rent a PairOfSkis or

a Snowboard. A person cannot rent more than one PairOfSkis or one Snowboard at the same time. A person cannot rent a PairOfSkis and a Snowboard at the same time either. A piece of equipment can be rented by at most one person at a time. The rental comes with a start date and an end date.
Possible, but why have two ids?
Слайд 13

Q1. Common mistakes: Rents relationship A Person can rent a PairOfSkis

Q1. Common mistakes: Rents relationship

A Person can rent a PairOfSkis or

a Snowboard. A person cannot rent more than one PairOfSkis or one Snowboard at the same time. A person cannot rent a PairOfSkis and a Snowboard at the same time either. A piece of equipment can be rented by at most one person at a time. The rental comes with a start date and an end date.
A better option
Слайд 14

Q2 (6 points) Write the SQL CREATE TABLE statement for the

Q2

(6 points) Write the SQL CREATE TABLE statement for the owns

relation between Skier and PairOfSkis. Make sure that your statement specifies the PRIMARY KEY and any FOREIGN KEYS. Additionally, we would like to enforce the constraint that purchase price be greater than zero.
Слайд 15

Q2: Solution CREATE TABLE owns ( sid INT PairOfSkis, pid INT

Q2: Solution

CREATE TABLE owns ( sid INT PairOfSkis, pid INT Skier, purchase_price INT,

PRIMARY KEY (sid), FOREIGN KEY (sid) REFERENCES PairOfSkis, FOREIGN KEY (pid) REFERENCES Skier, CHECK ( purchase_price > 0) )
Слайд 16

Q2. Common mistakes: PK choice CREATE TABLE owns ( sid INT

Q2. Common mistakes: PK choice

CREATE TABLE owns ( sid INT PairOfSkis, pid

INT Skier, purchase_price INT, PRIMARY KEY (sid,pid) (sid), FOREIGN KEY (sid) REFERENCES PairOfSkis, FOREIGN KEY (pid) REFERENCES Skier, CHECK ( purchase_price > 0) )
Слайд 17

Q2. Common mistakes: PK choice CREATE TABLE owns ( sid INT

Q2. Common mistakes: PK choice

CREATE TABLE owns ( sid INT PairOfSkis, pid

INT Skier, purchase_price INT, PRIMARY KEY (sid,pid) (sid), FOREIGN KEY (sid) REFERENCES PairOfSkis, FOREIGN KEY (pid) REFERENCES Skier, CHECK ( purchase_price > 0) )
“A PairOfSkis is owned by at most one Skier.”
Слайд 18

Q2. Common mistakes: PK choice CREATE TABLE owns ( sid INT

Q2. Common mistakes: PK choice

CREATE TABLE owns ( sid INT PairOfSkis, pid

INT Skier, purchase_price INT, PRIMARY KEY (pid) (sid), FOREIGN KEY (sid) REFERENCES PairOfSkis, FOREIGN KEY (pid) REFERENCES Skier, CHECK ( purchase_price > 0) )
Слайд 19

Q2. Common mistakes: PK choice CREATE TABLE owns ( sid INT

Q2. Common mistakes: PK choice

CREATE TABLE owns ( sid INT PairOfSkis, pid

INT Skier, purchase_price INT, PRIMARY KEY (pid) (sid), FOREIGN KEY (sid) REFERENCES PairOfSkis, FOREIGN KEY (pid) REFERENCES Skier, CHECK ( purchase_price > 0) )
Only one pair of skis for one skier?! “A Skier owns zero or more PairOfSkis.”
Слайд 20

Q2. Common mistakes: Excessive attributes CREATE TABLE owns ( sid INT

Q2. Common mistakes: Excessive attributes

CREATE TABLE owns ( sid INT PairOfSkis, pid

INT Skier, purchase_price INT, model varchar, ski_size INT PRIMARY KEY (sid), FOREIGN KEY (sid) REFERENCES PairOfSkis, FOREIGN KEY (pid) REFERENCES Skier, FOREIGN KEY (model) REFERENCES PairOfSkis, FOREIGN KEY (ski_size) REFERENCES Skier, CHECK ( purchase_price > 0) ) What for?!
Слайд 21

Q2. Second option - combining Owns and PairOfSkis CREATE TABLE pairOfSkisOwns

Q2. Second option - combining Owns and PairOfSkis

CREATE TABLE pairOfSkisOwns

( sid INT PairOfSkis, model VARCHAR, pid INT Skier, purchase_price INT, PRIMARY KEY (sid), FOREIGN KEY (pid) REFERENCES Skier, CHECK ( purchase_price > 0) )
Слайд 22

Q3: Consider the two tables: Table Driver (licenseNum, firstName, lastName, age)

Q3: Consider the two tables:
Table Driver (licenseNum, firstName, lastName, age) –

part of a simple driver registration database. Every row of Driver has a unique licenceNum.
Also consider table Voter (voterID, firstName, lastName, district) – where every row of Voter has a unique voterID.
3.1 Write a query in SQL to give the first and last names of all drivers that share a last name with another driver.
SELECT firstName, lastName from Driver d1
WHERE EXISTS(SELECT 1 FROM Driver d2
WHERE d1.lastname = d2.lastname
AND d1.firstName != d2.firstname)
Слайд 23

Q3: Consider the two tables: Table Driver (licenseNum, firstName, lastName, age)

Q3: Consider the two tables:
Table Driver (licenseNum, firstName, lastName, age) –

part of a simple driver registration database. Every row of Driver has a unique licenceNum.
Also consider table Voter (voterID, firstName, lastName, district) – where every row of Voter has a unique voterID.
3.1 Write a query in SQL to give the first and last names of all drivers that share a last name with another driver.
Forgetting to remove self references
SELECT firstName, lastName from Driver d1
WHERE EXISTS(SELECT 1 FROM Driver d2
WHERE d1.lastname = d2.lastname)
Слайд 24

Q3: Consider the two tables: Table Driver (licenseNum, firstName, lastName, age)

Q3: Consider the two tables:
Table Driver (licenseNum, firstName, lastName, age) –

part of a simple driver registration database. Every row of Driver has a unique licenceNum.
Also consider table Voter (voterID, firstName, lastName, district) – where every row of Voter has a unique voterID.
3.1 Write a query in SQL to give the first and last names of all drivers that share a last name with another driver.
Comparing lastName with a set that possibly has multiple elements
SELECT firstName, lastName from Driver d1
WHERE lastName = (SELECT lastName FROM Driver d2
WHERE d1.lastName = d2.lastName
AND d1.fistName != d2.firstName)
Слайд 25

Q3: Consider the two tables: Table Driver (licenseNum, firstName, lastName, age)

Q3: Consider the two tables:
Table Driver (licenseNum, firstName, lastName, age) –

part of a simple driver registration database. Every row of Driver has a unique licenceNum.
Also consider table Voter (voterID, firstName, lastName, district) – where every row of Voter has a unique voterID.
3.1 Write a query in SQL to give the first and last names of all drivers that share a last name with another driver.
Use IN instead
SELECT firstName, lastName from Driver d1
WHERE lastName IN (SELECT lastName FROM Driver d2
WHERE d1.lastName = d2.lastName
AND d1.fistName != d2.firstName)
Слайд 26

Q3: Consider the two tables: Table Driver (licenseNum, firstName, lastName, age)

Q3: Consider the two tables:
Table Driver (licenseNum, firstName, lastName, age) –

part of a simple driver registration database. Every row of Driver has a unique licenceNum.
Also consider table Voter (voterID, firstName, lastName, district) – where every row of Voter has a unique voterID.
3.1 Write a query in SQL to give the first and last names of all drivers that share a last name with another driver.
SELECT DISTINCT d1.firstName, d1.lastName from Driver d1, Driver d2
WHERE d1.lastName = d2.lastName AND d1.firstName != d2.firstName
Слайд 27

Q3: Consider the two tables: Table Driver (licenseNum, firstName, lastName, age)

Q3: Consider the two tables:
Table Driver (licenseNum, firstName, lastName, age) –

part of a simple driver registration database. Every row of Driver has a unique licenceNum.
Also consider table Voter (voterID, firstName, lastName, district) – where every row of Voter has a unique voterID.
3.1 Write a query in SQL to give the first and last names of all drivers that share a last name with another driver.
Forgetting to put distinct
SELECT DISTINCT d1.firstName, d1.lastName from Driver d1, Driver d2
WHERE d1.lastName = d2.lastName AND d1.firstName != d2.firstName
Слайд 28

Q3: Consider the two tables: Table Driver (licenseNum, firstName, lastName, age)

Q3: Consider the two tables:
Table Driver (licenseNum, firstName, lastName, age) –

part of a simple driver registration database. Every row of Driver has a unique licenceNum.
Also consider table Voter (voterID, firstName, lastName, district) – where every row of Voter has a unique voterID.
3.1 Write a query in SQL to give the first and last names of all drivers that share a last name with another driver.
Using < instead of !=
SELECT DISTINCT d1.firstName, d1.lastName from Driver d1, Driver d2
WHERE d1.lastName = d2.lastName AND d1.firstName < d2.firstName
Слайд 29

Q3: Consider the two tables: Table Driver (licenseNum, firstName, lastName, age)

Q3: Consider the two tables:
Table Driver (licenseNum, firstName, lastName, age) –

part of a simple driver registration database. Every row of Driver has a unique licenceNum.
Also consider table Voter (voterID, firstName, lastName, district) – where every row of Voter has a unique voterID.
3.1 Write a query in SQL to give the first and last names of all drivers that share a last name with another driver.
SELECT d1.firstName, d1.lastName from Driver d1
WHERE d1.lastName IN (SELECT d2.lastName FROM Driver d2
GROUP BY d2.lastName
HAVING COUNT(firstName) > 1)
Слайд 30

Q3: Consider the two tables: Table Driver (licenseNum, firstName, lastName, age)

Q3: Consider the two tables:
Table Driver (licenseNum, firstName, lastName, age) –

part of a simple driver registration database. Every row of Driver has a unique licenceNum.
Also consider table Voter (voterID, firstName, lastName, district) – where every row of Voter has a unique voterID.
3.2 (5 points) Write a query in SQL to find all people (first name, last name) who are both voters from district ‘32’ and drivers under the age 25.
Almost no issues with this question.
SELECT firstName, lastName FROM Driver WHERE age < 25
INTERSECT
SELECT firstName, lastName FROM Voter WHERE district = ‘32’
Слайд 31

Q3: Consider the two tables: Table Driver (licenseNum, firstName, lastName, age)

Q3: Consider the two tables:
Table Driver (licenseNum, firstName, lastName, age) –

part of a simple driver registration database. Every row of Driver has a unique licenceNum.
Also consider table Voter (voterID, firstName, lastName, district) – where every row of Voter has a unique voterID.
3.2 (5 points) Write a query in SQL to find all people (first name, last name) who are both voters from district ‘32’ and drivers under the age 25.
Almost no issues with this question.
SELECT firstName, lastName
FROM Driver NATURAL JOIN Voter
WHERE age < 25 AND district = ‘32’
Слайд 32

Q4: Consider the following schema: Suppliers(sid: integer, sname: string, address: string)

Q4: Consider the following schema:
Suppliers(sid: integer, sname: string, address: string)
Parts(pid: integer,

pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)
Find the names of suppliers who supply some red part.
SELECT DISTINCT S.name
FROM Parts P, Catalog C, Supplier S
WHERE P.color = ‘red’ AND P.pid = C.pid AND C.sid = S.sid
Слайд 33

Q4: Consider the following schema: Suppliers(sid: integer, sname: string, address: string)

Q4: Consider the following schema:
Suppliers(sid: integer, sname: string, address: string)
Parts(pid: integer,

pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)
Find the names of suppliers who supply some red part.
SELECT DISTINCT S.name
FROM Parts P, Catalog C, Supplier S
WHERE P.color = ‘red’ AND P.pid = C.pid AND C.sid = S.sid
Слайд 34

Q4: Consider the following schema: Suppliers(sid: integer, sname: string, address: string)

Q4: Consider the following schema:
Suppliers(sid: integer, sname: string, address: string)
Parts(pid: integer,

pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)
Find the names of suppliers who supply some red part.
SELECT S.name
FROM Supplier s WHERE s.sid IN (SELECT c.sid FROM Catalog c
WHERE c.pid IN (SELECT p.pid FROM Parts p WHERE p.color = ‘red’)
Слайд 35

Q4: Consider the following schema: Suppliers(sid: integer, sname: string, address: string)

Q4: Consider the following schema:
Suppliers(sid: integer, sname: string, address: string)
Parts(pid: integer,

pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)
Find the sids of suppliers who supply some red or green part.
SELECT C.sid
FROM Parts P, Catalog C
WHERE (P.color = ‘red’ OR P.color = ‘green’) AND P.pid = C.pid
Слайд 36

Q4: Consider the following schema: Suppliers(sid: integer, sname: string, address: string)

Q4: Consider the following schema:
Suppliers(sid: integer, sname: string, address: string)
Parts(pid: integer,

pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)
Find the sids of suppliers who supply some red or green part.
SELECT C.sid
FROM (SELECT p.pid FROM Parts P WHERE P.color = ‘red’
UNION SELECT p.pid FROM Parts P WHERE P.color = ‘green’) PS, Catalog C
WHERE PS.pid = C.pid
Слайд 37

Q5 5.1 Which of the following relational algebra operations do not

Q5

5.1 Which of the following relational algebra operations do not require

the participating tables to
be union-compatible?
(A) Union
(B) Intersection
(C) Difference
(D) Join
5.2 Relational Algebra does not have
(A) Selection operator.
(B) Projection operator.
(C) Aggregation operators.
(D) Division operator.
5.3 In an E-R diagram a thick line indicate
(A) Total participation.
(B) Multiple participation.
(C) Cardinality N.
(D) None of the above.
Слайд 38

Q5 5.4 The operation which is not considered a basic operation

Q5

5.4 The operation which is not considered a basic operation of

relational algebra is
(A) Join.
(B) Selection.
(C) Union.
(D) Cross product.
5.5 In SQL the statement select * from R, S is equivalent to
(A) Select * from R natural join S.
(B) Select * from R cross join S. (cross product)
(C) (Select * from R) union (Select * from S).
(D) (Select * from R) intersect (Select * from S).
5.6 In SQL, testing whether a subquery is empty is done using
(A) DISTINCT
(B) UNIQUE
(C) NULL
(D) EXISTS
Слайд 39

Q5 5.7 A trigger is? (A) A statement that is executed

Q5

5.7 A trigger is?
(A) A statement that is executed automatically by

the system as a side effect of modification to the
(B) A statement that enables to start any DBMS
(C) A statement that is executed by the user when debugging an application program
(D) A condition the system tests for the validity of the database user
5.8 Entity set that does not have enough _________ to form a _______ is a weak entity set.
(A) attribute, primary key
(B) records, foreign key
(C) records, primary key
(D) attribute, foreign key
- Предыдущая
Создание базы данных «Агентство недвижимости»
Следующая -
Гадаад бодлогын шийдвэр гаргалтад нөлөөлох хүчин зүйлс

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

Большие данные
Разработка приложения для изучения языка программирования
Робота з об'єктами файлової системи
Графический редактор Paint
Мобильный эквайринг. Сравнение POS-терминалов и mPOS
Моделирование, формализация, визуализация
Композиция. Правила и приемы
Что такое оптоинформатика
Базы данных. Система управления базами данных
Язык запросов SQL. Введение. (Лекция 6, 7)
Словесные информационные модели. 7 класс
Школьная служба медиации
Онлайновые СМИ как составляющая современного информационного пространства
Визуальные компоненты мультимедиа. Компьютерная графика. Модели рангов
Організація баз даних та знань. Системи баз даних. Основні поняття й архітектур. Основні поняття баз даних
Особенности проведения первого этапа аккредитации в дистанционной форме
Презентация "Переполнение буфера - 2" - скачать презентации по Информатике
Профессия программист
Решение логических задач
Презентация "Информация и информационные процессы в технике" - скачать презентации по Информатике_
Виртуальная выставка
Основные понятия и сущность информационных ресурсов
Запросы в поисковых системах
Continuous integration
Ветвления и исполнители алгоритмов.
Введение в язык с++ (тема 2-1)
პერსონალური მონაცემების დაცვა. პერსონალური მონაცემები და მათი კატეგორიები
Выполнение курсовой работы по дисциплине Программирование на языке высокого уровня
Вы можете прислать нам Вашу презентацию и мы опубликуем ее на сайте.
Обратная связь
Для правообладателей
Email: Нажмите что бы посмотреть