Problem 1, page 223

1
Problem 1, page 223
2
Problem 1, page 223
Customer
3
Problem 1, page 223
Customer CustomerNum RepNum
4
Problem 1, page 223
Customer CustomerNum RepNum
Rep RepNum LastName
5
Problem 1, page 223
Customer CustomerNum RepNum
Rep RepNum LastName
6
Problem 1, page 223
Customer CustomerNum RepNum
Rep RepNum LastName
This is an example of a one to many relationship
as follows one customer can be served by ONE rep
and one rep can serve MANY customers.
7
Problem 1, page 223
Customer CustomerNum
Rep RepNum LastName
Assignments CustomerNum RepNum
8
Problem 1, page 223
Customer CustomerNum
Rep RepNum LastName
Assignments CustomerNum RepNum
This is a many to many relationship one customer
can be connected to MANY reps and one rep can be
assigned to MANY customer.
9
Problem 1, page 223
Customer CustomerNum
Rep RepNum LastName
Assignments CustomerNum RepNum
When a customer places an order the sales that
gets the comission should be one of the
collection of the sales reps that represents
the customer.
10
Problem 1, page 223
Customer CustomerNum
Rep RepNum LastName
Assignments CustomerNum RepNum
Orders OrderNum OrderDate CustomerNum
RepNum
11
Problem 1, page 223
Customer CustomerNum
Rep RepNum LastName
Assignments CustomerNum RepNum
Orders OrderNum OrderDate CustomerNum
RepNum
12
Problem 1, page 223
Customer CustomerNum
Rep RepNum LastName
Assignments CustomerNum RepNum
Orders OrderNum OrderDate CustomerNum
RepNum
13
Problem 1, page 223
Customer CustomerNum
Rep RepNum LastName
Assignments CustomerNum RepNum
Orders OrderNum OrderDate CustomerNum
RepNum
PK
14
Problem 1, page 223
Customer CustomerNum
Rep RepNum LastName
Assignments CustomerNum RepNum
Orders OrderNum OrderDate CustomerNum
RepNum
PK
FK
15
Problem 1, page 223
Customer CustomerNum
Rep RepNum LastName
Assignments CustomerNum RepNum
Orders OrderNum OrderDate CustomerNum
RepNum
PK
FK
16
Problem 2, page 223
17
Problem 2, page 223
Customer CustomerNum
18
Problem 2, page 223
Customer CustomerNum
Rep RepNum
19
Problem 2, page 223
Customer CustomerNum
Rep RepNum
Orders CustomerNum RepNum
20
Problem 2, page 223
Customer CustomerNum
Rep RepNum
Orders CustomerNum RepNum
21
Problem 2, page 223
Customer CustomerNum
Rep RepNum
PK
Orders CustomerNum RepNum
FK
22
Problem 2, page 223
Customer CustomerNum
Rep RepNum
PK
Orders CustomerNum RepNum
FK
23
Problem 2, page 223
Customer CustomerNum
Rep RepNum
PK
PK
Orders CustomerNum RepNum
FK
FK
24
Problem 3, page 223
25
Problem 3, page 223
Part PartNum Description Class Price
26
Problem 3, page 223
Warehouse WarehouseNum WarehouseDescription
Part PartNum Description Class Price
27
Problem 3, page 223
Warehouse WarehouseNum WarehouseDescription
Part PartNum Description Class Price
WarehouseNum
28
Problem 3, page 223
Warehouse WarehouseNum WarehouseDescription
Part PartNum Description Class Price
WarehouseNum
29
Problem 3, page 223
Warehouse WarehouseNum WarehouseDescription
Part PartNum Description Class Price
WarehouseNum
PK
FK
30
Problem 3, page 223
Warehouse WarehouseNum WarehouseDescription
Part PartNum Description Class Price
WarehouseNum
But this would allow us to store each part in
just one location.
31
Problem 3, page 223
Warehouse WarehouseNum WarehouseDescription
Part PartNum Description Class Price
32
Problem 3, page 223
Warehouse WarehouseNum WarehouseDescription
Part PartNum Description Class Price
Storage PartNum WarehouseNum
NumberOfUnits
33
Problem 3, page 223
Warehouse WarehouseNum WarehouseDescription
Part PartNum Description Class Price
Storage PartNum WarehouseNum
NumberOfUnits
34
Problem 3, page 223
Warehouse WarehouseNum WarehouseDescription
Part PartNum Description Class Price
Storage PartNum WarehouseNum
NumberOfUnits
35
Problem 4, page 223
36
Problem 4, page 223
Territory TerritoryNum TerritoryName
37
Problem 4, page 223
Territory TerritoryNum TerritoryName
Customer CustomerNum TerritoryNum
38
Problem 4, page 223
Territory TerritoryNum TerritoryName
Rep RepNum TerritoryNum
Customer CustomerNum TerritoryNum
39
Problem 4, page 223
Territory TerritoryNum TerritoryName
Rep RepNum TerritoryNum
But we need a tighter connection.
Customer CustomerNum TerritoryNum
40
Problem 4, page 223
Territory TerritoryNum TerritoryName
Rep RepNum
Assignments RepNum CustomerNum
TerritoryNum
Customer CustomerNum
41
Problem 4, page 223
Territory TerritoryNum TerritoryName
Rep RepNum
Assignments RepNum CustomerNum
TerritoryNum
Customer CustomerNum
42
Problem 4, page 223
Territory TerritoryNum TerritoryName
Rep RepNum
PK
PK
Assignments RepNum CustomerNum
TerritoryNum
FK
FK
FK
Customer CustomerNum
PK
43
Problem 5, page 223
44
Problem 5, page 223
Territory TerritoryNum TerritoryName
45
Problem 5, page 223
Territory TerritoryNum TerritoryName
Customer CustomerNum TerritoryNum
46
Problem 5, page 223
Rep RepNum TerritoryNum
Territory TerritoryNum TerritoryName
Customer CustomerNum TerritoryNum
47
Problem 5, page 223
Rep RepNum TerritoryNum
Territory TerritoryNum TerritoryName
Assignments RepNum CustomerNum
Customer CustomerNum TerritoryNum
48
Problem 5, page 223
Rep RepNum TerritoryNum
Territory TerritoryNum TerritoryName
Assignments RepNum CustomerNum
Customer CustomerNum TerritoryNum
49
Problem 1, page 224
50
Problem 1, page 224
Director DirectorNum DirectorName
YearBorn YearDeceased
51
Problem 1, page 224
Director DirectorNum DirectorName
YearBorn YearDeceased
Movie MovieNum MovieTitle Year Type
52
Problem 1, page 224
Director DirectorNum DirectorName
YearBorn YearDeceased
Movie MovieNum MovieTitle Year Type
DirectorNum CriticsRating MPAARating
Nominations Awards
53
Problem 1, page 224
Director DirectorNum DirectorName
YearBorn YearDeceased
Movie MovieNum MovieTitle Year Type
DirectorNum CriticsRating MPAARating
Nominations Awards
54
Problem 1, page 224
Actor ActorNum ActorName Birthplace
YearBorn YearDeceased
Director DirectorNum DirectorName
YearBorn YearDeceased
Movie MovieNum MovieTitle Year Type
DirectorNum CriticsRating MPAARating
Nominations Awards
55
Problem 1, page 224
Actor ActorNum ActorName Birthplace
YearBorn YearDeceased
Director DirectorNum DirectorName
YearBorn YearDeceased
PlaysIn MovieNum ActorNum
Movie MovieNum MovieTitle Year Type
DirectorNum CriticsRating MPAARating
Nominations Awards
56
Problem 1, page 224
Actor ActorNum ActorName Birthplace
YearBorn YearDeceased
Director DirectorNum DirectorName
YearBorn YearDeceased
PlaysIn MovieNum ActorNum
Movie MovieNum MovieTitle Year Type
DirectorNum CriticsRating MPAARating
Nominations Awards
57
Problem 1, page 224
Actor ActorNum ActorName Birthplace
YearBorn YearDeceased
Director DirectorNum DirectorName
YearBorn YearDeceased
PlaysIn MovieNum ActorNum
Movie MovieNum MovieTitle Year Type
DirectorNum CriticsRating MPAARating
Nominations Awards
58
Problem 1, page 224
Actor ActorNum ActorName Birthplace
YearBorn YearDeceased
Director DirectorNum DirectorName
YearBorn YearDeceased
PlaysIn MovieNum ActorNum
Movie MovieNum MovieTitle Year Type
DirectorNum CriticsRating MPAARating
Nominations Awards
59
Problem 2, page 224
Actor ActorNum ActorName Birthplace
YearBorn YearDeceased
Director DirectorNum DirectorName
YearBorn YearDeceased
PlaysIn MovieNum ActorNum
Movie MovieNum MovieTitle Year Type
DirectorNum CriticsRating MPAARating
Nominations Awards
60
Problem 2, page 224
Actor ActorNum ActorName Birthplace
YearBorn YearDeceased
Director DirectorNum DirectorName
YearBorn YearDeceased
Members MemberNum MemberName Address
NumberOfRentals MemberSince BonusPoints
PlaysIn MovieNum ActorNum
Movie MovieNum MovieTitle Year Type
DirectorNum CriticsRating MPAARating
Nominations Awards
61
Problem 2, page 224
Actor ActorNum ActorName Birthplace
YearBorn YearDeceased
Director DirectorNum DirectorName
YearBorn YearDeceased
Members MemberNum MemberName Address
NumberOfRentals MemberSince BonusPoints
PlaysIn MovieNum ActorNum
Movie MovieNum MovieTitle Year Type
DirectorNum CriticsRating MPAARating
Nominations Awards
62
Problem 3, page 224
Actor ActorNum ActorName Birthplace
YearBorn YearDeceased
Director DirectorNum DirectorName
YearBorn YearDeceased
Members MemberNum MemberName Address
NumberOfRentals MemberSince BonusPoints
PlaysIn MovieNum ActorNum
Movie MovieNum MovieTitle Year Type
DirectorNum CriticsRating MPAARating
Nominations Awards
DVDs DVDNum MovieNum DatePurchased
TimesRented CurrentMember BranchNum
63
Problem 3, page 224
Actor ActorNum ActorName Birthplace
YearBorn YearDeceased
Director DirectorNum DirectorName
YearBorn YearDeceased
Members MemberNum MemberName Address
NumberOfRentals MemberSince BonusPoints
PlaysIn MovieNum ActorNum
Movie MovieNum MovieTitle Year Type
DirectorNum CriticsRating MPAARating
Nominations Awards
DVDs DVDNum MovieNum DatePurchased
TimesRented CurrentMember BranchNum
64
Problem 3, page 224
Actor ActorNum ActorName Birthplace
YearBorn YearDeceased
Director DirectorNum DirectorName
YearBorn YearDeceased
Members MemberNum MemberName Address
NumberOfRentals MemberSince BonusPoints
PlaysIn MovieNum ActorNum
Movie MovieNum MovieTitle Year Type
DirectorNum CriticsRating MPAARating
Nominations Awards
Transactions MemberNum DVDNum DateOut
DateReturned
DVDs DVDNum MovieNum DatePurchased
TimesRented CurrentMember BranchNum
65
Problem 3, page 224
Actor ActorNum ActorName Birthplace
YearBorn YearDeceased
Director DirectorNum DirectorName
YearBorn YearDeceased
Members MemberNum MemberName Address
NumberOfRentals MemberSince BonusPoints
PlaysIn MovieNum ActorNum
Movie MovieNum MovieTitle Year Type
DirectorNum CriticsRating MPAARating
Nominations Awards
Transactions MemberNum DVDNum DateOut
DateReturned
DVDs DVDNum MovieNum DatePurchased
TimesRented CurrentMember BranchNum
66
Problem 3, page 224
Actor ActorNum ActorName Birthplace
YearBorn YearDeceased
Director DirectorNum DirectorName
YearBorn YearDeceased
Members MemberNum MemberName Address
NumberOfRentals MemberSince BonusPoints
PlaysIn MovieNum ActorNum
Movie MovieNum MovieTitle Year Type
DirectorNum CriticsRating MPAARating
Nominations Awards
Transactions MemberNum DVDNum DateOut
DateReturned
DVDs DVDNum MovieNum DatePurchased
TimesRented CurrentMember BranchNum
67
Problem 3, page 224
Actor ActorNum ActorName Birthplace
YearBorn YearDeceased
Director DirectorNum DirectorName
YearBorn YearDeceased
Members MemberNum MemberName Address
NumberOfRentals MemberSince BonusPoints
PlaysIn MovieNum ActorNum
Movie MovieNum MovieTitle Year Type
DirectorNum CriticsRating MPAARating
Nominations Awards
Transactions MemberNum DVDNum DateOut
DateReturned
DVDs DVDNum MovieNum DatePurchased
TimesRented CurrentMember BranchNum
68
Problem 3, page 224
Actor ActorNum ActorName Birthplace
YearBorn YearDeceased
Director DirectorNum DirectorName
YearBorn YearDeceased
Members MemberNum MemberName Address
NumberOfRentals MemberSince BonusPoints
PlaysIn MovieNum ActorNum
Movie MovieNum MovieTitle Year Type
DirectorNum CriticsRating MPAARating
Nominations Awards
Transactions MemberNum DVDNum DateOut
DateReturned
DVDs DVDNum MovieNum DatePurchased
TimesRented CurrentMember BranchNum
69
Problem 3, page 224
Actor ActorNum ActorName Birthplace
YearBorn YearDeceased
Director DirectorNum DirectorName
YearBorn YearDeceased
Members MemberNum MemberName Address
NumberOfRentals MemberSince BonusPoints
PlaysIn MovieNum ActorNum
Movie MovieNum MovieTitle Year Type
DirectorNum CriticsRating MPAARating
Nominations Awards
Transactions MemberNum DVDNum DateOut
DateReturned
DVDs DVDNum MovieNum DatePurchased
TimesRented CurrentMember BranchNum