| <!DOCTYPE html> |
| <html lang="en"> |
| <head> |
| <meta charset="UTF-8"> |
| <meta name="viewport" content="width=device-width, initial-scale=1.0"> |
| <title>Registration Application ERD</title> |
| <script src="https://cdn.tailwindcss.com"></script> |
| <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css"> |
| <style> |
| .entity { |
| transition: all 0.3s ease; |
| } |
| .entity:hover { |
| transform: translateY(-5px); |
| box-shadow: 0 10px 20px rgba(0,0,0,0.1); |
| } |
| .relationship-line { |
| position: absolute; |
| background-color: #6b7280; |
| transform-origin: left center; |
| } |
| .diagram-container { |
| min-height: 600px; |
| overflow: auto; |
| } |
| @media (max-width: 768px) { |
| .diagram-container { |
| transform: scale(0.7); |
| transform-origin: top left; |
| width: 140%; |
| } |
| } |
| </style> |
| </head> |
| <body class="bg-gray-50"> |
| <div class="container mx-auto px-4 py-8"> |
| <header class="mb-8 text-center"> |
| <h1 class="text-3xl md:text-4xl font-bold text-indigo-700 mb-2">Registration Application Database Design</h1> |
| <p class="text-gray-600 max-w-2xl mx-auto">Entity Relationship Diagram for a comprehensive registration system</p> |
| <div class="mt-4 flex justify-center space-x-4"> |
| <button id="toggle-view" class="px-4 py-2 bg-indigo-600 text-white rounded-lg hover:bg-indigo-700 transition"> |
| <i class="fas fa-exchange-alt mr-2"></i>Toggle View |
| </button> |
| <button id="download-btn" class="px-4 py-2 bg-green-600 text-white rounded-lg hover:bg-green-700 transition"> |
| <i class="fas fa-download mr-2"></i>Download Diagram |
| </button> |
| </div> |
| </header> |
|
|
| <div class="bg-white rounded-xl shadow-lg p-6 mb-8"> |
| <div class="diagram-container relative" id="erd-diagram"> |
| |
| <div class="entity absolute top-20 left-1/4 w-64 bg-blue-50 border-2 border-blue-300 rounded-lg p-4 shadow-md" data-entity="applicant"> |
| <div class="flex justify-between items-center mb-2"> |
| <h3 class="font-bold text-blue-800 text-lg">Applicant</h3> |
| <i class="fas fa-user text-blue-500"></i> |
| </div> |
| <ul class="text-sm text-gray-700 space-y-1"> |
| <li><span class="font-semibold">applicant_id</span> (PK)</li> |
| <li>first_name</li> |
| <li>last_name</li> |
| <li>date_of_birth</li> |
| <li>gender</li> |
| <li>email</li> |
| <li>phone</li> |
| <li>address</li> |
| <li>created_at</li> |
| <li>updated_at</li> |
| </ul> |
| </div> |
|
|
| <div class="entity absolute top-20 right-1/4 w-64 bg-purple-50 border-2 border-purple-300 rounded-lg p-4 shadow-md" data-entity="application"> |
| <div class="flex justify-between items-center mb-2"> |
| <h3 class="font-bold text-purple-800 text-lg">Application</h3> |
| <i class="fas fa-file-alt text-purple-500"></i> |
| </div> |
| <ul class="text-sm text-gray-700 space-y-1"> |
| <li><span class="font-semibold">application_id</span> (PK)</li> |
| <li><span class="font-semibold">applicant_id</span> (FK)</li> |
| <li>application_date</li> |
| <li>status</li> |
| <li>program_type</li> |
| <li>term</li> |
| <li>year</li> |
| <li>submission_date</li> |
| <li>review_date</li> |
| <li>decision_date</li> |
| </ul> |
| </div> |
|
|
| <div class="entity absolute top-80 left-1/4 w-64 bg-green-50 border-2 border-green-300 rounded-lg p-4 shadow-md" data-entity="document"> |
| <div class="flex justify-between items-center mb-2"> |
| <h3 class="font-bold text-green-800 text-lg">Document</h3> |
| <i class="fas fa-file-upload text-green-500"></i> |
| </div> |
| <ul class="text-sm text-gray-700 space-y-1"> |
| <li><span class="font-semibold">document_id</span> (PK)</li> |
| <li><span class="font-semibold">application_id</span> (FK)</li> |
| <li>document_type</li> |
| <li>file_name</li> |
| <li>file_path</li> |
| <li>upload_date</li> |
| <li>verified</li> |
| <li>verification_date</li> |
| </ul> |
| </div> |
|
|
| <div class="entity absolute top-80 right-1/4 w-64 bg-yellow-50 border-2 border-yellow-300 rounded-lg p-4 shadow-md" data-entity="payment"> |
| <div class="flex justify-between items-center mb-2"> |
| <h3 class="font-bold text-yellow-800 text-lg">Payment</h3> |
| <i class="fas fa-credit-card text-yellow-500"></i> |
| </div> |
| <ul class="text-sm text-gray-700 space-y-1"> |
| <li><span class="font-semibold">payment_id</span> (PK)</li> |
| <li><span class="font-semibold">application_id</span> (FK)</li> |
| <li>amount</li> |
| <li>payment_date</li> |
| <li>payment_method</li> |
| <li>transaction_id</li> |
| <li>status</li> |
| <li>receipt_number</li> |
| </ul> |
| </div> |
|
|
| <div class="entity absolute top-80 left-1/2 transform -translate-x-1/2 w-64 bg-red-50 border-2 border-red-300 rounded-lg p-4 shadow-md" data-entity="education"> |
| <div class="flex justify-between items-center mb-2"> |
| <h3 class="font-bold text-red-800 text-lg">Education</h3> |
| <i class="fas fa-graduation-cap text-red-500"></i> |
| </div> |
| <ul class="text-sm text-gray-700 space-y-1"> |
| <li><span class="font-semibold">education_id</span> (PK)</li> |
| <li><span class="font-semibold">applicant_id</span> (FK)</li> |
| <li>institution_name</li> |
| <li>degree</li> |
| <li>field_of_study</li> |
| <li>start_date</li> |
| <li>end_date</li> |
| <li>gpa</li> |
| <li>country</li> |
| </ul> |
| </div> |
|
|
| <div class="entity absolute top-440 left-1/4 w-64 bg-indigo-50 border-2 border-indigo-300 rounded-lg p-4 shadow-md" data-entity="reference"> |
| <div class="flex justify-between items-center mb-2"> |
| <h3 class="font-bold text-indigo-800 text-lg">Reference</h3> |
| <i class="fas fa-user-friends text-indigo-500"></i> |
| </div> |
| <ul class="text-sm text-gray-700 space-y-1"> |
| <li><span class="font-semibold">reference_id</span> (PK)</li> |
| <li><span class="font-semibold">application_id</span> (FK)</li> |
| <li>name</li> |
| <li>email</li> |
| <li>phone</li> |
| <li>relationship</li> |
| <li>institution</li> |
| <li>position</li> |
| <li>submission_status</li> |
| </ul> |
| </div> |
|
|
| <div class="entity absolute top-440 right-1/4 w-64 bg-pink-50 border-2 border-pink-300 rounded-lg p-4 shadow-md" data-entity="test_score"> |
| <div class="flex justify-between items-center mb-2"> |
| <h3 class="font-bold text-pink-800 text-lg">Test Score</h3> |
| <i class="fas fa-star text-pink-500"></i> |
| </div> |
| <ul class="text-sm text-gray-700 space-y-1"> |
| <li><span class="font-semibold">test_score_id</span> (PK)</li> |
| <li><span class="font-semibold">applicant_id</span> (FK)</li> |
| <li>test_type</li> |
| <li>score</li> |
| <li>test_date</li> |
| <li>reporting_date</li> |
| <li>valid_until</li> |
| </ul> |
| </div> |
|
|
| |
| <div class="relationship-line" id="line1" style="width: 100px; height: 2px; top: 120px; left: 280px;"></div> |
| <div class="relationship-line" id="line2" style="width: 2px; height: 180px; top: 120px; left: 380px;"></div> |
| <div class="relationship-line" id="line3" style="width: 100px; height: 2px; top: 300px; left: 280px;"></div> |
| <div class="relationship-line" id="line4" style="width: 100px; height: 2px; top: 300px; left: 480px;"></div> |
| <div class="relationship-line" id="line5" style="width: 2px; height: 180px; top: 120px; left: 580px;"></div> |
| <div class="relationship-line" id="line6" style="width: 100px; height: 2px; top: 300px; left: 580px;"></div> |
| <div class="relationship-line" id="line7" style="width: 2px; height: 120px; top: 300px; left: 680px;"></div> |
| |
| |
| <div class="absolute top-150 left-360 bg-white px-2 py-1 rounded text-xs font-bold text-gray-700">1 to N</div> |
| <div class="absolute top-220 left-280 bg-white px-2 py-1 rounded text-xs font-bold text-gray-700">1 to N</div> |
| <div class="absolute top-220 left-480 bg-white px-2 py-1 rounded text-xs font-bold text-gray-700">1 to N</div> |
| <div class="absolute top-150 left-580 bg-white px-2 py-1 rounded text-xs font-bold text-gray-700">1 to N</div> |
| <div class="absolute top-220 left-580 bg-white px-2 py-1 rounded text-xs font-bold text-gray-700">1 to N</div> |
| <div class="absolute top-360 left-680 bg-white px-2 py-1 rounded text-xs font-bold text-gray-700">1 to N</div> |
| </div> |
| </div> |
|
|
| <div class="grid grid-cols-1 md:grid-cols-2 gap-6 mb-8"> |
| <div class="bg-white rounded-xl shadow-lg p-6"> |
| <h2 class="text-xl font-bold text-gray-800 mb-4">Database Schema Explanation</h2> |
| <ul class="space-y-3"> |
| <li class="flex items-start"> |
| <span class="bg-blue-100 text-blue-800 p-1 rounded mr-2"><i class="fas fa-user"></i></span> |
| <div> |
| <h3 class="font-semibold">Applicant</h3> |
| <p class="text-sm text-gray-600">Stores personal information about individuals applying for registration.</p> |
| </div> |
| </li> |
| <li class="flex items-start"> |
| <span class="bg-purple-100 text-purple-800 p-1 rounded mr-2"><i class="fas fa-file-alt"></i></span> |
| <div> |
| <h3 class="font-semibold">Application</h3> |
| <p class="text-sm text-gray-600">Tracks each application submission with status and timeline information.</p> |
| </div> |
| </li> |
| <li class="flex items-start"> |
| <span class="bg-green-100 text-green-800 p-1 rounded mr-2"><i class="fas fa-file-upload"></i></span> |
| <div> |
| <h3 class="font-semibold">Document</h3> |
| <p class="text-sm text-gray-600">Manages all supporting documents uploaded with each application.</p> |
| </div> |
| </li> |
| <li class="flex items-start"> |
| <span class="bg-yellow-100 text-yellow-800 p-1 rounded mr-2"><i class="fas fa-credit-card"></i></span> |
| <div> |
| <h3 class="font-semibold">Payment</h3> |
| <p class="text-sm text-gray-600">Records all payment transactions related to application fees.</p> |
| </div> |
| </li> |
| </ul> |
| </div> |
|
|
| <div class="bg-white rounded-xl shadow-lg p-6"> |
| <h2 class="text-xl font-bold text-gray-800 mb-4">Key Relationships</h2> |
| <ul class="space-y-3"> |
| <li class="flex items-start"> |
| <span class="bg-red-100 text-red-800 p-1 rounded mr-2"><i class="fas fa-graduation-cap"></i></span> |
| <div> |
| <h3 class="font-semibold">Education</h3> |
| <p class="text-sm text-gray-600">Stores academic history of applicants with institution details.</p> |
| </div> |
| </li> |
| <li class="flex items-start"> |
| <span class="bg-indigo-100 text-indigo-800 p-1 rounded mr-2"><i class="fas fa-user-friends"></i></span> |
| <div> |
| <h3 class="font-semibold">Reference</h3> |
| <p class="text-sm text-gray-600">Manages reference contacts provided by applicants.</p> |
| </div> |
| </li> |
| <li class="flex items-start"> |
| <span class="bg-pink-100 text-pink-800 p-1 rounded mr-2"><i class="fas fa-star"></i></span> |
| <div> |
| <h3 class="font-semibold">Test Score</h3> |
| <p class="text-sm text-gray-600">Tracks standardized test scores submitted by applicants.</p> |
| </div> |
| </li> |
| <li class="flex items-start"> |
| <span class="bg-gray-100 text-gray-800 p-1 rounded mr-2"><i class="fas fa-project-diagram"></i></span> |
| <div> |
| <h3 class="font-semibold">Relationships</h3> |
| <p class="text-sm text-gray-600">All entities relate back to Applicant and Application with 1-to-many relationships.</p> |
| </div> |
| </li> |
| </ul> |
| </div> |
| </div> |
|
|
| <div class="bg-white rounded-xl shadow-lg p-6"> |
| <h2 class="text-xl font-bold text-gray-800 mb-4">SQL Schema Generation</h2> |
| <div class="bg-gray-100 p-4 rounded-lg overflow-x-auto"> |
| <pre class="text-sm text-gray-800"> |
| -- Applicant Table |
| CREATE TABLE applicants ( |
| applicant_id SERIAL PRIMARY KEY, |
| first_name VARCHAR(100) NOT NULL, |
| last_name VARCHAR(100) NOT NULL, |
| date_of_birth DATE NOT NULL, |
| gender VARCHAR(20), |
| email VARCHAR(255) NOT NULL UNIQUE, |
| phone VARCHAR(50), |
| address TEXT, |
| created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, |
| updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP |
| ); |
|
|
| -- Application Table |
| CREATE TABLE applications ( |
| application_id SERIAL PRIMARY KEY, |
| applicant_id INTEGER REFERENCES applicants(applicant_id), |
| application_date DATE NOT NULL, |
| status VARCHAR(50) NOT NULL, |
| program_type VARCHAR(100), |
| term VARCHAR(50), |
| year INTEGER, |
| submission_date TIMESTAMP, |
| review_date TIMESTAMP, |
| decision_date TIMESTAMP |
| ); |
|
|
| -- Document Table |
| CREATE TABLE documents ( |
| document_id SERIAL PRIMARY KEY, |
| application_id INTEGER REFERENCES applications(application_id), |
| document_type VARCHAR(100) NOT NULL, |
| file_name VARCHAR(255) NOT NULL, |
| file_path TEXT NOT NULL, |
| upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, |
| verified BOOLEAN DEFAULT FALSE, |
| verification_date TIMESTAMP |
| ); |
|
|
| -- Payment Table |
| CREATE TABLE payments ( |
| payment_id SERIAL PRIMARY KEY, |
| application_id INTEGER REFERENCES applications(application_id), |
| amount DECIMAL(10,2) NOT NULL, |
| payment_date TIMESTAMP NOT NULL, |
| payment_method VARCHAR(50), |
| transaction_id VARCHAR(100), |
| status VARCHAR(50) NOT NULL, |
| receipt_number VARCHAR(100) |
| ); |
|
|
| -- Education Table |
| CREATE TABLE education ( |
| education_id SERIAL PRIMARY KEY, |
| applicant_id INTEGER REFERENCES applicants(applicant_id), |
| institution_name VARCHAR(255) NOT NULL, |
| degree VARCHAR(100), |
| field_of_study VARCHAR(255), |
| start_date DATE, |
| end_date DATE, |
| gpa DECIMAL(3,2), |
| country VARCHAR(100) |
| ); |
|
|
| -- Reference Table |
| CREATE TABLE references ( |
| reference_id SERIAL PRIMARY KEY, |
| application_id INTEGER REFERENCES applications(application_id), |
| name VARCHAR(255) NOT NULL, |
| email VARCHAR(255), |
| phone VARCHAR(50), |
| relationship VARCHAR(100), |
| institution VARCHAR(255), |
| position VARCHAR(255), |
| submission_status VARCHAR(50) DEFAULT 'Pending' |
| ); |
|
|
| -- Test Score Table |
| CREATE TABLE test_scores ( |
| test_score_id SERIAL PRIMARY KEY, |
| applicant_id INTEGER REFERENCES applicants(applicant_id), |
| test_type VARCHAR(100) NOT NULL, |
| score VARCHAR(50), |
| test_date DATE, |
| reporting_date DATE, |
| valid_until DATE |
| ); |
| </pre> |
| </div> |
| </div> |
|
|
| <div class="bg-white rounded-xl shadow-lg p-6 mt-8"> |
| <h2 class="text-xl font-bold text-gray-800 mb-4">Database Operations</h2> |
| <div class="grid grid-cols-1 md:grid-cols-2 gap-6"> |
| <div> |
| <h3 class="font-semibold mb-3">Add New Applicant</h3> |
| <form id="applicant-form" class="space-y-4"> |
| <div> |
| <label class="block text-sm font-medium text-gray-700">First Name</label> |
| <input type="text" name="first_name" required class="mt-1 block w-full rounded-md border-gray-300 shadow-sm focus:border-indigo-500 focus:ring-indigo-500"> |
| </div> |
| <div> |
| <label class="block text-sm font-medium text-gray-700">Last Name</label> |
| <input type="text" name="last_name" required class="mt-1 block w-full rounded-md border-gray-300 shadow-sm focus:border-indigo-500 focus:ring-indigo-500"> |
| </div> |
| <div> |
| <label class="block text-sm font-medium text-gray-700">Email</label> |
| <input type="email" name="email" required class="mt-1 block w-full rounded-md border-gray-300 shadow-sm focus:border-indigo-500 focus:ring-indigo-500"> |
| </div> |
| <button type="submit" class="w-full bg-indigo-600 text-white py-2 px-4 rounded-md hover:bg-indigo-700 focus:outline-none focus:ring-2 focus:ring-indigo-500 focus:ring-offset-2"> |
| Submit Applicant Data |
| </button> |
| </form> |
| </div> |
| <div> |
| <h3 class="font-semibold mb-3">Query Database</h3> |
| <div class="space-y-4"> |
| <select id="entity-select" class="block w-full rounded-md border-gray-300 shadow-sm focus:border-indigo-500 focus:ring-indigo-500"> |
| <option value="applicants">Applicants</option> |
| <option value="applications">Applications</option> |
| <option value="documents">Documents</option> |
| </select> |
| <button id="fetch-data" class="w-full bg-green-600 text-white py-2 px-4 rounded-md hover:bg-green-700 focus:outline-none focus:ring-2 focus:ring-green-500 focus:ring-offset-2"> |
| Fetch Data |
| </button> |
| <div id="query-results" class="bg-gray-100 p-4 rounded-md overflow-auto max-h-60 hidden"> |
| <pre class="text-sm text-gray-800"></pre> |
| </div> |
| </div> |
| </div> |
| </div> |
| </div> |
| </div> |
|
|
| <script> |
| ======= |
|
|
| 2. Now let's add the backend connection functionality to the script section: |
|
|
| <<<<<<< SEARCH |
| }); |
| }); |
| </script> |
| ======= |
| }); |
|
|
| // Backend connection functionality |
| const API_BASE_URL = 'http://localhost:3000/api'; // Update with your backend URL |
|
|
| // Handle applicant form submission |
| document.getElementById('applicant-form').addEventListener('submit', async function(e) { |
| e.preventDefault(); |
| const formData = new FormData(this); |
| const data = Object.fromEntries(formData.entries()); |
|
|
| try { |
| const response = await fetch(`${API_BASE_URL}/applicants`, { |
| method: 'POST', |
| headers: { |
| 'Content-Type': 'application/json' |
| }, |
| body: JSON.stringify(data) |
| }); |
|
|
| const result = await response.json(); |
| alert('Applicant added successfully!'); |
| console.log('Success:', result); |
| } catch (error) { |
| console.error('Error:', error); |
| alert('Error submitting applicant data'); |
| } |
| }); |
|
|
| // Handle data fetching |
| document.getElementById('fetch-data').addEventListener('click', async function() { |
| const entity = document.getElementById('entity-select').value; |
| const resultsDiv = document.getElementById('query-results'); |
| const pre = resultsDiv.querySelector('pre'); |
|
|
| try { |
| const response = await fetch(`${API_BASE_URL}/${entity}`); |
| const data = await response.json(); |
| |
| resultsDiv.classList.remove('hidden'); |
| pre.textContent = JSON.stringify(data, null, 2); |
| } catch (error) { |
| console.error('Error:', error); |
| resultsDiv.classList.remove('hidden'); |
| pre.textContent = 'Error fetching data'; |
| } |
| }); |
| }); |
| </script> |
| document.addEventListener('DOMContentLoaded', function() { |
| // Toggle between compact and detailed view |
| const toggleViewBtn = document.getElementById('toggle-view'); |
| const entities = document.querySelectorAll('.entity'); |
| let isCompact = false; |
| |
| toggleViewBtn.addEventListener('click', function() { |
| isCompact = !isCompact; |
| |
| entities.forEach(entity => { |
| if (isCompact) { |
| entity.classList.add('w-40'); |
| entity.classList.remove('w-64'); |
| entity.querySelector('ul').classList.add('hidden'); |
| } else { |
| entity.classList.remove('w-40'); |
| entity.classList.add('w-64'); |
| entity.querySelector('ul').classList.remove('hidden'); |
| } |
| }); |
| |
| toggleViewBtn.innerHTML = isCompact |
| ? '<i class="fas fa-expand mr-2"></i>Expand View' |
| : '<i class="fas fa-compress mr-2"></i>Compact View'; |
| }); |
|
|
| // Download functionality (simulated) |
| const downloadBtn = document.getElementById('download-btn'); |
| downloadBtn.addEventListener('click', function() { |
| alert('Diagram download would be initiated here in a real implementation.'); |
| }); |
|
|
| // Add hover effects to entities |
| entities.forEach(entity => { |
| entity.addEventListener('mouseenter', function() { |
| const entityType = this.getAttribute('data-entity'); |
| document.querySelectorAll(`[data-entity="${entityType}"]`).forEach(el => { |
| el.classList.add('ring-2', 'ring-offset-2'); |
| if (entityType === 'applicant') el.classList.add('ring-blue-400'); |
| if (entityType === 'application') el.classList.add('ring-purple-400'); |
| if (entityType === 'document') el.classList.add('ring-green-400'); |
| if (entityType === 'payment') el.classList.add('ring-yellow-400'); |
| if (entityType === 'education') el.classList.add('ring-red-400'); |
| if (entityType === 'reference') el.classList.add('ring-indigo-400'); |
| if (entityType === 'test_score') el.classList.add('ring-pink-400'); |
| }); |
| }); |
|
|
| entity.addEventListener('mouseleave', function() { |
| const entityType = this.getAttribute('data-entity'); |
| document.querySelectorAll(`[data-entity="${entityType}"]`).forEach(el => { |
| el.classList.remove('ring-2', 'ring-offset-2', |
| 'ring-blue-400', 'ring-purple-400', |
| 'ring-green-400', 'ring-yellow-400', |
| 'ring-red-400', 'ring-indigo-400', |
| 'ring-pink-400'); |
| }); |
| }); |
| }); |
| }); |
| </script> |
| <p style="border-radius: 8px; text-align: center; font-size: 12px; color: #fff; margin-top: 16px;position: fixed; left: 8px; bottom: 8px; z-index: 10; background: rgba(0, 0, 0, 0.8); padding: 4px 8px;">Made with <img src="https://enzostvs-deepsite.hf.space/logo.svg" alt="DeepSite Logo" style="width: 16px; height: 16px; vertical-align: middle;display:inline-block;margin-right:3px;filter:brightness(0) invert(1);"><a href="https://enzostvs-deepsite.hf.space" style="color: #fff;text-decoration: underline;" target="_blank" >DeepSite</a> - 🧬 <a href="https://enzostvs-deepsite.hf.space?remix=wantech/test" style="color: #fff;text-decoration: underline;" target="_blank" >Remix</a></p></body> |
| </html> |
