Database Web Interface

First Name
Last Name
ID#
Email Address

Table of Contents

How to submit1

P1 – Pet Adoption System (40 Points)2

Database (25 Points)2

PHP/HTML Programming (15 Points)3

P2 Cities (35 Points)4

Part 1 – Arrays – City Distances (12 points)4

Part 2 – Central Point (12 Points)5

Part 3 – Add UI (11 Points)5

P3 – Forms – Certificate Generator Tool (25 Points)5

How to submit

After filling all the parts in this file, please follow the following steps.

• Add your name and ID to the first page.
• Save the file in the original format (Docx or Doc)
• Rename the file as
• Upload and submit your file (only via Blackboard).

(please do not convert to other file formats e.g. PDF, ZIP, RAR, …).

YOUR First Name– YOUR Last Name–ID.docx

Example: John-Smith-234566435.docx

Note: The final run results of each problem (screenshot or a copy of the results) should be added to the run result section “orange boxes”. Displaying the final run results accounts for 20% of the points given to each part.

P1 – Pet Adoption System (40 Points)

Part 1 – Database (25 Points)

This question requires PhpMyAdmin to create the pet_adoption database.Follow the below instructions to create this database.Make sure to include the database scripts (CREATE, INSERT, etc.) with your PHP project.They are also to be graded along with your PHP/HTML code.

The database must be named as pet_adoption.The database will have these tables:

PET_TYPES:

• ID [INT] – Primary key and auto-generated
• NAME [VARCHAR(50) / NOT NULL] – Type of a pet.

Notes: Currently, there are only two supported pet types (Dog and Cat). You need to manually insert these pet types using the INSERT statements.Despite of having two supported pet types, make sure that no duplicates of pet types is allowed.

PET_BREEDS:

• ID [INT] – Primary key and auto-generated
• NAME [VARCHAR(50) / NOT NULL] – Breed of a pet.
• IS_DOG_BREED [TINYINT / NOT NULL] – If set to 1, this is the dog breed.Otherwise, it’s the cat breed.
• ID [INT] – Primary key and auto-generated
• NAME [VARCHAR(50) / NOT NULL] – Name of a pet to adopted.

Notes:

• You need to manually insert the pet breeds using the INSERT statements.Make sure that there are no duplicates of dog breeds and/or cat breeds.In other words, create a unique key index on both columns (NAME and IS_DOG_BREED).
• Using the INSERT statements to insert the following cat breeds:
  • Persian
  • Sanvannah
  • Japanese Bobtail
  • Himalayan
• Using the INSERT statements to insert the following dog breeds:
  • Boxer
  • Dalmatian
  • Golden Retriever
  • Shih Tzu

PETS:

• BREED [INT / NOT NULL] – Foreign key to the pet_breeds table.
• TYPE [INT / NOT NULL] – Foreign key to the pet_types table.
• ARRIVAL_DATE [DATETIME / NOT NULL] – Date when the pet arrives to the adoption center.
• OWNER_FIRST_NAME [VARCHAR(50) / NULL] – First name of the new owner.
• OWNER_LAST_NAME [VARCHAR(50) / NULL] – Last name of the new owner.
• OWNER_MIDDLE_NAME [VARCHAR(50) / NULL] – Middle name of the new owner.
• ADOPTION_DATE [DATETIME / NULL] – Date when the pet is adopted.

Notes:

• Enable the delete and update cascading for foreign keys for BREED and TYPE.
• Insert the pets for adoption using the INSERT statements with the information below:

• The below SELECT statement is used to generate the above data table.You are welcomed to use it as is or to modify it to suite your needs.

SELECT p.name AS pet_name,

t.name AS pet_type,

b.name AS pet_breed,

p.arrival_date,

CONCAT(IFNULL(p.owner_first_name, ”),

IFNULL(p.owner_last_name, ”),

IFNULL(p.owner_middle_name, ”)) AS owner_full_name,

p.adoption_date

FROM `pets` p

INNER JOIN `pet_types` t ON t.id = p.type

INNER JOIN `pet_breeds` b ON b.id = p.breed

ORDER BY t.name, b.name;

Insert your SQL code for creation of database in the following box.

Your SQL code for creating the database

—Copy and paste your code here Also please add screenshots of the database tables in the phpMyAdmin environment here.

Part 2 – PHP/HTML Programming (15 Points)

• Implement the home page (index.php) to display the above data table with two additional columns: Edit and Delete.
• The Edit column will allow the user to enter the new owner name and the adoption date.
• The Delete column will allow the user to remove the selected pet from the pet adoption center.

Your PHP Code

–Copy and paste your code here

Run the code and insert the result in the following box.

The run result

Copy and paste the result here (e.g. the screen shot of the result you get by running the code) Please add the result (screenshot) of data table as well as a sample case for deleting and editing one of the pets in the PETS table (result of clicking on the delete and edit buttons).

P2 Cities (35 Points)

Part 1 – Arrays – City Distances (12 points)

Consider the following X and Y values for the locations of 5 cities.

City#XY

—————————————-

1|2.55

2|5.13

3|19

4|5.454

5|5.52.1

Write a PHP program that calculates the distance among all cities and display it as a 5 * 5 table. The element at row = i and col = j will show the distance between cities i and j. For example, row 2 and col 3 will show distance between city 2 and city 3.

Note: Distance between two cities is defined as Euclidian distance. For example, the distance between city 2 and city 3 will be:

D = sqrt ( (x2-x3)^2+(y2-y3)^2 ) = sqrt ( (5.1-1)^2+(3-9)^2 ) = 7.267

Your PHP Code

–Copy and paste your code here

Run the code and insert the result in the following box.

The run result

Copy and paste the result here (e.g. the screen shot of the result you get by running the code)

Part 2 – Central Point (12 Points)

Given a set of cities, the central point is the city that has the shortest total distance to all other cities. Write a PHP program that finds the central city and its total distance to all other cities for the 5 cities in part 1.

Your PHP Code

–Copy and paste your code here

Run the code and insert the result in the following box.

The run result

Copy and paste the result here (e.g. the screen shot of the result you get by running the code)

Part 3 – Add UI (11 Points)

Modify the code in part 1 and 2 and add the following UI (Form) to the code.

After running the code, by entering X and Y and clicking on “Add New City” we add a new city. This process continues until all the cities are entered. Then we click on “Show Distance Table” to see the distance table (result of part 1). Clicking on the “Find Central City” will show the central point (result of part 2). Test your code with the list of cities given in part 1 (entered one by one) and show the result.

Your PHP Code

–Copy and paste your code here

Run the code and insert the result in the following box.

The run result

Copy and paste the result here (e.g. the screen shot of the result you get by running the code)

P3 – Forms – Certificate Generator Tool (25 Points)

In this problem we are going to design a certificate form. For this problem, the design of the certificate (both HTML and CSS files) are given in the “Problem 3” folder. You just need do add the following form to the page and then link it to the certificate fields using PHP code. More details about this problem and expected is given in the following 3-minute YouTube Video (the video has no voice. Please CTRL+Click on the link or simply copy and paste it in the address bar of your web browser).

• YouTube Video: https://youtu.be/27VbBvx9fOo

Note: No design work is needed for this problem. All the files (HTML, CSS and images) are given. Just the above form should be added to the page and then the fields should be linked to the certificate fields, so that when we click on “Create Certificate” button, then the certificate with info entered by the user is created.

Your PHP Code

–Copy and paste your code here

Run the code and create a certificate for Name: John Smith, Date: 10/20/2020 University: San Diego State University, Program: Computer Science and insert the result in the following box.

The run result

Copy and paste the result here (e.g. the screen shot of the result you get by running the code)

The end