HW 4
louxiadefengjing
PetClassDemoERDModelingusingVertabelo.docx
IT 240 Introduction to DBs Spring 2020 Page 1 of 2
Erd Modeling using Vertabelo (http://www.vertabelo.com/)
Pet DB Example from Text
Read:
Conceptual, Logical and Physical Data Model
https://www.visual-paradigm.com/support/documents/vpuserguide/3563/3564/85378_conceptual,l.html
If you have not already done so, sign up for a free Student Vertabelo account at http://www.vertabelo.com/
Make certain to go to Pricing Academic Student account
Pet Case Study
Examine Functional dependencies
OWNER(LastName, FirstName, Phone, Email)
PET(Name, Breed, DOB, Weight)
SERVICE(Date, Charge)
PET_TYPE(pet, type)
Pet_SERVICES(Date, Charge)
Conceptual
-- Created by Vertabelo (http://vertabelo.com)
-- Last modification date: 2020-05-19 18:53:22.4
-- tables
-- Table: OWNER
CREATE TABLE OWNER (
LastName NOT NULL,
FirstName NOT NULL,
Phone NOT NULL,
Email NOT NULL
);
-- Table: PET
CREATE TABLE PET (
Name NOT NULL,
Breed NOT NULL,
DOB NOT NULL,
Weight NOT NULL
);
-- Table: PET_SERVICES
CREATE TABLE PET_SERVICES (
Date NOT NULL,
Charge NOT NULL
);
-- Table: PET_TYPE
CREATE TABLE PET_TYPE (
Pet NOT NULL,
Type NOT NULL
);
-- Table: SERVICE
CREATE TABLE SERVICE (
Service NOT NULL,
Charge NOT NULL
);
-- End of file.
Logical
-- Created by Vertabelo (http://vertabelo.com)
-- Last modification date: 2020-05-19 19:06:42.486
-- tables
-- Table: OWNER
CREATE TABLE OWNER (
OwnerID integer NOT NULL,
LastName text NOT NULL,
FirstName text NOT NULL,
Phone text NOT NULL,
Email text NOT NULL
);
-- Table: PET
CREATE TABLE PET (
PetID integer NOT NULL,
Name text NOT NULL,
Breed text NOT NULL,
DOB date NOT NULL,
Weight real NOT NULL
);
-- Table: PET_SERVICES
CREATE TABLE PET_SERVICES (
TransactionID integer NOT NULL,
Date date NOT NULL,
Charge real NOT NULL
);
-- Table: PET_TYPE
CREATE TABLE PET_TYPE (
Pet-TypeID integer NOT NULL,
PetType text NOT NULL
);
-- Table: SERVICE
CREATE TABLE SERVICE (
ServiceID integer NOT NULL,
Service text NOT NULL,
Charge real NOT NULL
);
-- End of file.
Physical Model for Access
LastName FirstName, Phone, Email
….
OWNER(OwnerID, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail)
PET(PetID, PetName, PetBreed, PetDOB, PetWeight, OwnerID, PetTypeID)
SERVICE(ServiceID, Service, Charge)
PET_TYPE(PetTypeID, PetType)
PET_SERVICES(TransactionID, TransactionLineNumber, Date, Charge, ServiceID, PetID)
Contraints
1. OwnerID in PET must exist in OWNER
2. PetTypeID in PET must exist in PET_TYPE
3. ServiceID in PET_SERVICES must exist in SERVICE
4. PetID in PET_SERVICES MUST EXIST IN PET
Assumptions
1. An owner can have more than one pet, or no pets
…….
-- Created by Vertabelo (http://vertabelo.com)
-- Last modification date: 2020-05-19 19:25:52.586
-- tables
-- Table: OWNER
CREATE TABLE OWNER (
OwnerID integer NOT NULL CONSTRAINT OWNER_pk PRIMARY KEY,
OwnerLastName text NOT NULL,
OwnerFirstName text NOT NULL,
OwnerPhone text NOT NULL,
OwnerEmail text NOT NULL
);
-- Table: PET
CREATE TABLE PET (
PetID integer NOT NULL CONSTRAINT PET_pk PRIMARY KEY,
Name text NOT NULL,
Breed text NOT NULL,
DOB date NOT NULL,
Weight real NOT NULL,
OWNER_OwnerID integer NOT NULL,
PET_TYPE_PetTypeID integer NOT NULL,
CONSTRAINT PET_OWNER FOREIGN KEY (OWNER_OwnerID)
REFERENCES OWNER (OwnerID),
CONSTRAINT PET_PET_TYPE FOREIGN KEY (PET_TYPE_PetTypeID)
REFERENCES PET_TYPE (PetTypeID)
);
-- Table: PET_SERVICES
CREATE TABLE PET_SERVICES (
TransactionID integer NOT NULL CONSTRAINT PET_SERVICES_pk PRIMARY KEY,
Date date NOT NULL,
Charge real NOT NULL,
PET_PetID integer NOT NULL,
SERVICE_ServiceID integer NOT NULL,
CONSTRAINT PET_SERVICES_PET FOREIGN KEY (PET_PetID)
REFERENCES PET (PetID),
CONSTRAINT PET_SERVICES_SERVICE FOREIGN KEY (SERVICE_ServiceID)
REFERENCES SERVICE (ServiceID)
);
-- Table: PET_TYPE
CREATE TABLE PET_TYPE (
PetTypeID integer NOT NULL CONSTRAINT PET_TYPE_pk PRIMARY KEY,
PetType text NOT NULL
);
-- Table: SERVICE
CREATE TABLE SERVICE (
ServiceID integer NOT NULL CONSTRAINT SERVICE_pk PRIMARY KEY,
Service text NOT NULL,
Charge real NOT NULL
);
-- End of file.
