Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Database Management Open Ended lab sheet
Typology: Summaries
1 / 25
This page cannot be seen from the preview
Don't miss anything!
1. Problem Understanding: We need to design and implement a comprehensive database for an Online Job Recruitment Platform that can:
● Store information about 15 users (10 job seekers and 5 employers) ● Manage 10 active job postings across various industries ● Track 20+ job applications with different statuses ● Maintain a skill inventory of 10 different skills ● Support complex matching between job requirements and candidate skills
Key Entities and Their Attributes: User (15 records)
user_id, email, password, user_type, registration_date, last_login
Job Seeker (10 records)
seeker_id, first_name, last_name, phone, address, resume_file
Employer (5 records)
employer_id, company_name, company_description, industry, website
Job Posting (10 records)
job_id, employer_id, title, description, requirements, location, salary_range, job_type, posted_date, deadline, status
Application (20 records)
application_id, job_id, seeker_id, application_date, cover_letter, status
Skill (10 records)
skill_id, skill_name, category
Seeker_Skill (30+ records)
3. Database Implementation:
CREATE DATABASE job_recruitment_platform; USE job_recruitment_platform;
CREATE TABLE User ( -> user_id INT AUTO_INCREMENT PRIMARY KEY, -> email VARCHAR(100) UNIQUE NOT NULL, -> password VARCHAR(255) NOT NULL, -> user_type ENUM('seeker', 'employer') NOT NULL, -> registration_date DATETIME DEFAULT CURRENT_TIMESTAMP, -> last_login DATETIME -> );
CREATE TABLE User ( -> user_id INT AUTO_INCREMENT PRIMARY KEY, -> email VARCHAR(100) UNIQUE NOT NULL, -> password VARCHAR(255) NOT NULL, -> user_type ENUM('seeker', 'employer') NOT NULL, -> registration_date DATETIME DEFAULT CURRENT_TIMESTAMP, -> last_login DATETIME -> );
CREATE TABLE Job_Seeker ( -> seeker_id INT PRIMARY KEY, -> first_name VARCHAR(50) NOT NULL, -> last_name VARCHAR(50) NOT NULL, -> phone VARCHAR(20), -> address TEXT, -> resume_file VARCHAR(255), -> FOREIGN KEY (seeker_id) REFERENCES User(user_id) ON DELETE CASCADE -> );
CREATE TABLE Employer ( -> employer_id INT PRIMARY KEY,
-> FOREIGN KEY (seeker_id) REFERENCES Job_Seeker(seeker_id) ON DELETE CASCADE, -> UNIQUE KEY unique_application (job_id, seeker_id) -> ); CREATE TABLE Seeker_Skill ( -> seeker_id INT NOT NULL, -> skill_id INT NOT NULL, -> proficiency_level ENUM('beginner', 'intermediate', 'advanced', 'expert') NOT NULL, -> PRIMARY KEY (seeker_id, skill_id), -> FOREIGN KEY (seeker_id) REFERENCES Job_Seeker(seeker_id) ON DELETE CASCADE, -> FOREIGN KEY (skill_id) REFERENCES Skill(skill_id) ON DELETE CASCADE -> );
CREATE TABLE Job_Skill ( -> job_id INT NOT NULL, -> skill_id INT NOT NULL, -> importance_level ENUM('required', 'preferred', 'plus') NOT NULL, -> PRIMARY KEY (job_id, skill_id), -> FOREIGN KEY (job_id) REFERENCES Job_Posting(job_id) ON DELETE CASCADE, -> FOREIGN KEY (skill_id) REFERENCES Skill(skill_id) ON DELETE CASCADE -> );
INSERT INTO User (email, password, user_type, registration_date) VALUES -> -- Job Seekers (10) -> ('[email protected]', 'hashed_pass1', 'seeker', '2025-01-15'), -> ('[email protected]', 'hashed_pass2', 'seeker', '2025-01-20'), -> ('[email protected]', 'hashed_pass3', 'seeker', '2025-02-05'), -> ('[email protected]', 'hashed_pass4', 'seeker', '2025-02-10'), -> ('[email protected]', 'hashed_pass5', 'seeker', '2025-02-15'), -> ('[email protected]', 'hashed_pass6', 'seeker', '2025-02-20'), -> ('[email protected]', 'hashed_pass7', 'seeker', '2025-03-01'), -> ('[email protected]', 'hashed_pass8', 'seeker', '2025-03-05'), -> ('[email protected]', 'hashed_pass9', 'seeker', '2025-03-10'), -> ('[email protected]', 'hashed_pass10', 'seeker', '2025-03-15'), -> -> INSERT INTO User (email, password, user_type, registration_date) VALUES -> -- Job Seekers (10)
-> ('[email protected]', 'hashed_pass1', 'seeker', '2025-01-15'), -> ('[email protected]', 'hashed_pass2', 'seeker', '2025-01-20'), -> ('[email protected]', 'hashed_pass3', 'seeker', '2025-02-05'), -> ('[email protected]', 'hashed_pass4', 'seeker', '2025-02-10'), -> ('[email protected]', 'hashed_pass5', 'seeker', '2025-02-15'), -> ('[email protected]', 'hashed_pass6', 'seeker', '2025-02-20'), -> ('[email protected]', 'hashed_pass7', 'seeker', '2025-03-01'), -> ('[email protected]', 'hashed_pass8', 'seeker', '2025-03-05'), -> ('[email protected]', 'hashed_pass9', 'seeker', '2025-03-10'), -> ('[email protected]', 'hashed_pass10', 'seeker', '2025-03-15'), -> -> -- Employers (5) -> ('[email protected]', 'hashed_pass11', 'employer', '2025-01-10'), -> ('[email protected]', 'hashed_pass12', 'employer', '2025-01-12'), -> ('[email protected]', 'hashed_pass13', 'employer', '2025-01-25'), -> ('[email protected]', 'hashed_pass14', 'employer', '2025-02-01'), -> ('[email protected]', 'hashed_pass15', 'employer', '2025-02-05');
INSERT INTO Job_Seeker (seeker_id, first_name, last_name, phone, address, resume_file) VALUES -> (1, 'John', 'Doe', '1234567890', '123 Main St, City', 'john_doe_resume.pdf'), -> (2, 'Jane', 'Smith', '0987654321', '456 Oak Ave, Town', 'jane_smith_resume.pdf'), -> (3, 'Michael', 'Johnson', '5551234567', '789 Pine Rd, Village', 'michael_johnson_resume.pdf'), -> (4, 'Emily', 'Williams', '5559876543', '321 Elm St, Borough', 'emily_williams_resume.pdf'), -> (5, 'David', 'Brown', '5554567890', '654 Maple Ave, District', 'david_brown_resume.pdf'), -> (6, 'Sarah', 'Jones', '5557890123', '987 Cedar Ln, Township', 'sarah_jones_resume.pdf'), -> (7, 'Robert', 'Garcia', '5552345678', '159 Birch Blvd, County', 'robert_garcia_resume.pdf'), -> (8, 'Jennifer', 'Martinez', '5558765432', '753 Spruce Dr, Hamlet', 'jennifer_martinez_resume.pdf'), -> (9, 'Thomas', 'Davis', '5553456789', '246 Willow Way, Parish', 'thomas_davis_resume.pdf'), -> (10, 'Lisa', 'Rodriguez', '5557654321', '864 Fir Ct, Precinct', 'lisa_rodriguez_resume.pdf');
INSERT INTO Employer (employer_id, company_name, company_description, industry, website) VALUES -> (11, 'Tech Solutions Inc.', 'A leading technology company specializing in software development', 'Information Technology', 'www.techsolutions.com'),
-> (7, 'Robert', 'Garcia', '5552345678', '159 Birch Blvd, County', 'robert_garcia_resume.pdf'), -> (8, 'Jennifer', 'Martinez', '5558765432', '753 Spruce Dr, Hamlet', 'jennifer_martinez_resume.pdf'), -> (9, 'Thomas', 'Davis', '5553456789', '246 Willow Way, Parish', 'thomas_davis_resume.pdf'), -> (10, 'Lisa', 'Rodriguez', '5557654321', '864 Fir Ct, Precinct', 'lisa_rodriguez_resume.pdf');
INSERT INTO Employer (employer_id, company_name, company_description, industry, website) VALUES -> (11, 'Tech Solutions Inc.', 'A leading technology company specializing in software development', 'Information Technology', 'www.techsolutions.com'), -> (12, 'Marketing Pros', 'Digital marketing agency with 10+ years of experience', 'Marketing', 'www.marketingpros.com'), -> (13, 'Green Energy Corp', 'Renewable energy solutions provider', 'Energy', 'www.greenenergycorp.com'), -> (14, 'HealthCare Plus', 'Healthcare services and medical technology', 'Healthcare', 'www.healthcareplus.org'), -> (15, 'Global Finance Group', 'International financial services company', 'Finance', 'www.globalfinance.com');
INSERT INTO Skill (skill_name, category) VALUES -> ('Java', 'Programming'), -> ('Python', 'Programming'), -> ('SQL', 'Database'), -> ('Digital Marketing', 'Marketing'), -> ('Project Management', 'Business'), -> ('JavaScript', 'Programming'), -> ('Data Analysis', 'Analytics'), -> ('Cloud Computing', 'IT'), -> ('Graphic Design', 'Creative'), -> ('Financial Modeling', 'Finance');
INSERT INTO Job_Posting (employer_id, title, description, requirements, location, salary_range, job_type, posted_date, deadline, status) VALUES -> (11, 'Senior Java Developer', 'Develop and maintain enterprise Java applications', '5+ years Java, Spring Framework', 'Remote', '$100,000-$130,000', 'full-time', '2025-03-01', '2025-05-31', 'active'),
-> (11, 'Database Administrator', 'Manage and optimize SQL databases', '3+ years SQL, performance tuning', 'New York', '$90,000-$110,000', 'full-time', '2025-03-05', '2025-06-15', 'active'), -> (12, 'Digital Marketing Manager', 'Lead digital marketing campaigns', '4+ years marketing, Google Ads', 'Chicago', '$75,000-$95,000', 'full-time', '2025-03-10', '2025-05-30', 'active'), -> (12, 'Content Marketing Specialist', 'Create engaging content for digital platforms', '2+ years content creation', 'Remote', '$60,000-$75,000', 'full-time', '2025-03-12', '2025-06-30', 'active'), -> (13, 'Renewable Energy Engineer', 'Design solar energy systems', 'Degree in Engineering, 3+ years experience', 'San Francisco', '$85,000-$105,000', 'full-time', '2025-03-15', '2025-07-15', 'active'), -> (13, 'Sustainability Consultant', 'Advise clients on green initiatives', 'Sustainability knowledge, consulting experience', 'Boston', '$70,000-$90,000', 'full-time', '2025-03-18', '2025-06-30', 'active'), -> (14, 'Healthcare Data Analyst', 'Analyze patient data and outcomes', 'SQL, Python, healthcare experience', 'Los Angeles', '$80,000-$100,000', 'full-time', '2025-03-20', '2025-07-01', 'active'), -> (14, 'Medical Software Developer', 'Develop healthcare applications', 'Java/Python, healthcare IT', 'Austin', '$95,000-$115,000', 'full-time', '2025-03-22', '2025-07-10', 'active'), -> (15, 'Financial Analyst', 'Prepare financial reports and models', 'Excel, financial modeling', 'New York', '$85,000-$100,000', 'full-time', '2025-03-25', '2025-06-20', 'active'), -> (15, 'Investment Banking Associate', 'Support M&A and capital raising', '2+ years IB experience', 'Chicago', '$120,000-$150,000', 'full-time', '2025-03-28', '2025-06-15', 'active');
INSERT INTO Seeker_Skill (seeker_id, skill_id, proficiency_level) VALUES -> -- Seeker 1 -> (1, 1, 'advanced'), (1, 3, 'intermediate'), (1, 5, 'beginner'), -> -- Seeker 2 -> (2, 2, 'intermediate'), (2, 4, 'advanced'), (2, 7, 'intermediate'), -> -- Seeker 3 -> (3, 1, 'intermediate'), (3, 6, 'advanced'), (3, 8, 'beginner'), -> -- Seeker 4 -> (4, 4, 'advanced'), (4, 9, 'intermediate'), (4, 5, 'intermediate'), -> -- Seeker 5 -> (5, 2, 'advanced'), (5, 3, 'advanced'), (5, 7, 'intermediate'), -> -- Seeker 6 -> (6, 4, 'expert'), (6, 9, 'advanced'), (6, 5, 'intermediate'), -> -- Seeker 7 -> (7, 1, 'advanced'), (7, 6, 'advanced'), (7, 8, 'intermediate'),
-> (2, 9, '2025-03-11', 'Expert in SQL optimization...', 'shortlisted'), -> -> -- Applications for Job 3 -> (3, 2, '2025-03-12', 'I led marketing campaigns...', 'under_review'), -> (3, 4, '2025-03-13', 'Digital marketing specialist...', 'shortlisted'), -> (3, 6, '2025-03-14', '8 years marketing experience...', 'submitted'), -> -> -- Applications for Job 4 -> (4, 4, '2025-03-15', 'Content creation portfolio...', 'under_review'), -> (4, 6, '2025-03-16', 'Award-winning content...', 'shortlisted'), -> -> -- Applications for Job 5 -> (5, 5, '2025-03-18', 'Mechanical engineering degree...', 'submitted'), -> (5, 9, '2025-03-19', 'Renewable energy projects...', 'under_review'), -> -> -- Applications for Job 6 -> (6, 8, '2025-03-20', 'Sustainability consulting...', 'shortlisted'), -> -> -- Applications for Job 7 -> (7, 5, '2025-03-22', 'Healthcare data analysis...', 'under_review'), -> (7, 9, '2025-03-23', 'Python for healthcare...', 'submitted'), -> -> -- Applications for Job 8 -> (8, 1, '2025-03-25', 'Medical software experience...', 'shortlisted'), -> (8, 7, '2025-03-26', 'Healthcare IT projects...', 'under_review'), -> (8, 10, '2025-03-27', 'Java in medical field...', 'submitted'), -> -> -- Applications for Job 9 -> (9, 8, '2025-03-28', 'Financial modeling expert...', 'shortlisted'), -> -> -- Applications for Job 10 -> (10, 8, '2025-03-30', 'Investment banking intern...', 'under_review');
Output:
Complex SQL Queries:
