Question
You have just started a dog business. You want to create a to keep of your customers and their dogs. Use the concepts and techniques presented in this chapter to design a database to meet the following requirements. 1. The Dog walkers database must support the following requirements: a. For each d list the dog ID, dog name, breed name - for example, Dalmatian, mixed, collie and so on - size (large, medium, small) gender, number of times walked per day, and owner first name and last name as well as cell telephone number. b. For each customer, list the customer ID. the customers first name, last name, address (street city, postal code), home telephone number, cell telephone number, current amount due, and amount paid. c. The database also must store the cost per walk. The cost to walk each dog depends on both the dog and the customer. A dog belongs to only one breed, but a breed can have many dogs. An owner also can have many dog. Based on these requirements: a. Identify and list the entities and the attributes of those entities. b. Identify and list the functional dependencies. c. Create a set of 3NF relations using the shorthand notation given in the chapter. Be sure to identify all primary keys and foreign keys appropriately. Submit your database design in the format specified by your instructor.
Explanation / Answer
2.
a.
Entities
Attributes
DogID,DogName,Breed,Size,Gender,TimesWalked,OwnerCellPhone,OwnerFirstname,OwnerLastName,OwnerCellPhone
CustID,CustFirstName,CustLastName,Street,city,PostalCode,HomePhone,CellPhone,AmountDue,AmountPaid
DogID,CustID,CostPerWalk
b. Functional Dependencies
DogID -> DogName
DogID -> Breed
DogID -> Size
DogID -> Gender
DogID -> TimesWalked
DogID -> OwnerCellPhone
DogID -> OwnerFirstname
DogID -> OwnerLastName
DogID -> OwnerCellPhone
CustID -> CustFirstName
CustID ->CustLastName
CustID ->Street
CustID -> City
CustID -> PostalCode
CustID ->HomePhone
CustID ->CellPhone
CustID ->AmountDue
CustID ->AmountPaid
DogID,CustID -> CostPerWalk
c. 3NF
DOG(DogID,DogName,Breed,Size,Gender,TimesWalked,OwnerCellPhone,OwnerFirstname,OwnerLastName,OwnerCellPhone)
CUSTOMER(CustID,CustFirstName,CustLastName,Street,city,PostalCode,HomePhone,CellPhone,AmountDue,AmountPaid)
COSTPERWALK(DogID,CustID,CostPerWalk)
underlined are primary keys and italicised are foreign keys. Some attributes are both.