WIXLIB

“Pet Haven”PHASES 1-5Group Members:Janneth GuarcasBryan GutierrezMariah LimonPaula RodriguezCMPS 3420 - Fall 2020

Table of Contents1.1 Fact-Finding Techniques and Information Gathering . 31.1.1 - Introduction to the Enterprise/Organization . 31.1.2 - Description of Fact-Finding Techniques . 31.1.3 - The Miniverse of Interest . 31.1.4 - Itemized Description of Entity Sets and Relationship Sets . 31.1.5 - User Groups, Data Views, and Operations. 61.2 Conceptual Database Design . 61.2.1 - Entity Type Descriptions . 61.2.2 - Relationship Type Description .201.2.3 - Related Entity Types .221.2.4 - ER Diagram .232.1 - The ER and Relational models . 242.1.1 - Descriptions of ER and Relational Models .242.1.2 - Model Comparisons .242.2 - Conceptual to Logical Conversion Process . 252.2.1 - Converting Entity Types to Relations .252.2.2 - Converting Relationship Types to Relations .252.2.3 - Converting Extended Types to Relations .272.2.4 - Database Constraints .283 - Results of ER to Relational Conversion . 283.1 Relation Schema .293.2 Sample Data .344 - Sample Queries. 434.1 Design of Queries .434.2 Relational Algebra Expressions .434.3 Relational Calculus Expressions.445 - Normalization of Relations. 475.1 Normalization Process .475.2 Application to Relational Model .496 - Database Implementation. 511

6.1 Background Information .516.2 Schema and Hosting .517 - Query Implementation . 548 - Programming Logic for SQL . 608.1 - Introduction: .608.2 - Syntax of Programming Logic: .618.3 - Implementation .638.3.1 - Views:.638.3.2 - Stored procedures/functions: .658.3.3 - Triggers: .689 - GUI Development. 729.1 - GUI Functionalities and User Groups .729.1.1 - Itemized Descriptions of the GUI .729.1.2 - Screenshots and Walkthrough .739.1.3 - Demonstration of Programming Logic.789.2 - GUI Programming .799.2.1 - Server-side Programming .799.2.2 - Middle-tier Programming .869.2.3 - Client-side Programming .902

1.1 Fact-Finding Techniques and Information Gathering1.1.1 - Introduction to the Enterprise/OrganizationOur database will organize a pet store that will market items to pet owners and animallovers such as accessories, snacks, toys, and adoptable pets. The selection of adoptablepets we offer will include species such as fish, reptiles, cats, dogs, small mammals, andbirds. The most important activities our business engages in is providing the supplies forcaring for animal companions and providing adoptees in our store to loving homes andgood families.1.1.2 - Description of Fact-Finding TechniquesOur team is gathering information through extensive research of other massive pet storesenterprises such as PetCo and PetSmart. We also are conducting surveys and interviewswith pet owners, potential pet store customers, and prospective employees to determinethe needs of our pet store database. In addition, our team of developers will self-reflectand utilize our own experiences as pet owners and pet store customers. Although we donot currently know any domain experts, we have former database developers that we mayconsult for insight and assistance.1.1.3 - The Miniverse of InterestOur database will model the inventory of our store and serve as a means for ourcustomers to browse our wares and pets currently up for adoption from both the pet storeand local shelter highlights. The shelter highlights will have a showcase date for when theshelter pet (cats and dogs) will be showcased both in-store and highlighted online duringtheir adoption showcase dates. Major entities of our store will be employees, customers,adoptees, and products.1.1.4 - Itemized Description of Entity Sets and Relationship SetsEntity Sets: Adoptee: the pet companion to be adopted attributeName: (meaning) (dataType) petID: (ID number for pet) (varchar) name: (pet’s name) (varchar ) sex: (sex of pet either male, female, unknown) (varchar ) animalType: (i.e. cat, dog, fish, reptile, small mammal) (varchar ) breed: (the breed of adoptee, can be multiple) (varchar ) specialNeed: (does the adoptee have special needs?) (tinyint) color/pattern: (color or pattern of adoptee, can be multi) (varchar ) age: (age of adoptee in years/months) (integer) weight: (weight of adoptee in lbs/oz) (float)3

size: (height or length of adoptee in feet/inches) (float)estBirthYr: (the estimated birth year of an adoptee) (integer)vaccines: (the vaccines that an animal has been given: 1 fornone/not applicable, 2 for some, and 3 for all) (enum) spayNeuter: (whether the animal has been spayed or neutered)(tinyint) adoptionFee: (the price of adopting an animal) (DECIMAL (19,4)) Example of entity use: an adoptee is adopted by a customer an adoptee is updated by an employee an adoptee is in a shelter an adoptee is adopted at a showcaseCustomer: the person that can either adopt a pet companion, or purchase product attributeName: (meaning) (dataType) name: (customer name, composite) (varchar ) email: (customer email) (email data type or varchar ) address: (customer address) (varchar , int) payment: (to take customer credit card information) (integer) customerID: (customer’s store ID) (varchar) cPword: (customer’s account password) (varchar ) Example of entity use: a customer adopts an adoptee a customer purchases a productEmployee: the employee in charge of checking inventory attributeName: (meaning) (dataType) name: (employee name, composite) (varchar ) ssnVisa: (employee social security or visa ID) (varchar ) employeeID: (employee’s ID) (varchar) address: (employee address) (varchar ) email: (employee email) (email data type or varchar ) payment: (to take customer credit card information) (integer) hireDate: (the date employee was hired) (date) ePword: (employee’s account password) (varchar ) Example of entity use: an employee updates an adoptee an employee updated a product an employee responds to a customerProduct: the product such as a dog bed, shedding rock, or cat food. Thissuperclass entity will be subclassed into food, toys, enclosures, bedding, furniture,and accessories that will also inherit the attributes of the “product” superclass. Allattributes for superclass and subclass are as follows:4

supperclassAttributeName: (meaning) (dataType) name: (product name) (varchar ) price: (the price of a product) (DECIMAL (19,4)) animalType: (type of animal item can be used for) (varchar ) description: (description of product) (varchar ) barcode: (barcode for price and ordering more) (varchar ) numPerPack: (subproduct per product if a pack, else 1) (integer) quantity: (how many does the store currently carry) (integer) storeItemNum: (the item number for inventory) (varchar ) lotNum: (the lot num from manufacturer in case of recalls) (int) subclassAttributeName: (meaning) (dataType) expDate: (when it expires) (integer form YYYY-MM-DD-HH24hr clock) fType: (food type i.e. wet, dry, live, frozen) (varchar ) organic: (is it organic) (tinyint) weight: (weight of product when applicable lbs/oz) (float) spDiet: (adult, digestive, metabolic, elder, puppy/kitten) (varchar ) material*: (what is the item made of: metal, plastic, wood, glass)(varchar ) tType: (type of toy: active/exercise, chew/scratch, brain) (varchar ) encType: (type of enclosure: tank, terrarium, bird cage) (varchar ) sizeSML: (accessories in small/med/large/etc. sizes) (varchar ) encSize: (enclosure size in small/med/large/etc. sizes) (varchar ) bagSize: (bedding in small/med/large/NA for puppy pads) (varchar) realSim: (real or human-made material for rocks/logs etc.) (bool) sizeHWL: (height, length, width of item, composite) (integer) *: means multiple subclasses have this attribute. Example of entity use: a product is purchased by a customer a product is updated by an employeeShelter: the shelter taking care of larger adoptees, especially animals like cats anddogs attributeName: (meaning) (dataType) name: (the name of the shelter) (varchar ) shelterID: (the ID of the shelter) (varchar ) address: (the address of the shelter) (varchar ) phoneNumber: (the phone number of the shelter) (varchar ) Example of entity use: an adoptee is held in a shelter5

Showcase: the adoption event for larger animals, coordinated with shelters andadvertised by our business attributeName: (meaning) (dataType) inStore: (if the showcase is online or in person) (tinyint) showID: (the ID of the showcase event) (varchar ) time: (the date of the event) (date) Example of entity use: an adoptee is adopted at a showcaseRelationship Sets:relName: (meaning/example of relationship) (related entities) (attributes) (cardinality) adopts: (customer adopts pet) (pet is adopted at showcase) (customer, adoptee)(adoptee, showcase) (adoptDate) (M.N)resides: (adoptee resides in shelter) (N/A) (1, M)purchases: (customer purchases product) (customer, product) (orderID,orderDate) (M,N)responds: (emp responds to customer) (customer, employee) (responseID,resTime) (M,N)updates1: (emp updates adoptee) (employee, adoptee) (timeStamp) (M,N)updates2: (emp updates product) (employee, product) (timeStamp) (M,N)1.1.5 - User Groups, Data Views, and OperationsThere are two user groups: Customers and Employees. A customer may create anaccount, view/purchase products, and create support tickets. A user may also modify theiraccount info (like change email and password). An employee may log in through agenerated account to modify product catalogues, product descriptions and update relevantinformation about the adoptees. They may also respond to support tickets generated by acustomer, and an employee may also look at their work schedule and relevant workrelated information.1.2 Conceptual Database Design1.2.1 - Entity Type Descriptions-----ENTITY NAME: Customer----Candidate Keysemail, customerIDPrimary KeycustomerIDStrong/Weak EntitystrongFields to be Indexedname, email,customerID, payment6

Attribute Name: nameAttribute Description: Customer’s name for identifying ingle gleSimple orCompositecompositeAttribute Name: emailAttribute Description: Customer’s email address for order alueDefaultNULLUniqueSingle orSimple orTypeRangeValueValueMultipleCompositevarchar0-9, e Name: addressAttribute Description: Customer address or PO box for delivery of orders and mail/coupons.Domain/ValueDefaultNULLUniqueSingle orSimple ULLnonomultiplecomposite0-97

Attribute Name: paymentAttribute Description: Determines how a customer will pay for their products.Domain/ValueDefaultNULLUniqueSingle gleAttribute Name: customerIDAttribute Description: ID generated for identifying customer’s account.Domain/ValueDefaultNULLUniqueSingle orTypeRangeValueValueMultiplevarcharA-Z, 0-9next freenoyessingleintegerAttribute Name: cPwordAttribute Description: Password for customer’s account (will be encrypted).Domain/ValueDefaultNULLUniqueSingle gle0-9, specialcharactersSimple orCompositecompositeSimple orCompositesimpleSimple orCompositesimple-----ENTITY NAME: Product----Candidate KeysPrimary KeystoreItemNum,barCodestoreItemNumAttribute Name: nameAttribute Description: Product’s LLStrong/WeakEntitystrongNULLValuenoFields to be Indexeddescription, brand, price,storeItemNum, barCode, animalType,fType, organic, sizeSML, encSize,encType, bagSize, realSimUniquenoSingle orMultiplesingleSimple orCompositesimpleAttribute Name: price8

Attribute Description: the price of the DECIMAL(19,4) 0-maxIntNULLnoUniquenoSingle orMultiplesingleAttribute Name: animalTypeAttribute Description: to determine for what animal type this product is for.Domain/ValueDefaultNULLUniqueSingle orTypeRangeValueValueMultiplevarchardog, cat,NULLnonomultiplereptile,smallMammal, fish,birdSimple orCompositesimpleSimple orCompositesimpleAttribute Name: descriptionAttribute Description: Product description of what item is (Clothing, Food, Bedding, Enclosure).Domain/ValueDefaultNULLUniqueSingle orSimple orTypeRangeValueValueMultipleCompositevarchar0-9, A-ZNULLnonosinglesimple9