Exercises Week 3(Database)

DESIGN.md

Design Document - "Course and Career Guidance Database"

By Jean Felix Nibishaka

Scope

Purpose

The purpose of this database is to assist students in selecting courses that align with their future career aspirations. It helps students understand the possible careers for each course, the potential careers when combining courses, the duration required to complete their studies, the expected salary after graduation, and the challenges associated with each career.

Scope of the Database

Included:

• Students: Personal details and preferences.
• Courses: Details about each course, including duration and difficulty.
• Careers: Information on possible careers, required courses, duration, salary, and difficulty.
• Course Combinations: Possible course combinations and their respective careers.

Excluded:

• Institutional Data: Information about specific universities or colleges.
• Financial Data: Detailed financial aid information or tuition costs.
• Non-Academic Factors: Personal life factors that may influence career choices (e.g., location preferences).

Functional Requirements

User Capabilities

• Search for courses based on career interests.
• View possible careers for individual courses and combinations of courses.
• See estimated duration, difficulty, and expected salary for each career.
• Get recommendations based on their preferences and academic background.

Beyond Scope

• Real-time interaction with academic advisors.
• Automatic enrollment in courses.
• Detailed personal financial planning.

Entities and Relationships

Entity Relationship Diagram (ERD)

ERD image here

Description of Relationships

• Student enrolls in Course.
• Course can lead to multiple Careers.
• Career requires one or more Courses.
• CourseCombination is a combination of Courses that can lead to a Career.

Representation

Entities and Attributes

Student

• id (Primary Key)
• name (VARCHAR)
• email (VARCHAR, Unique)
• preference (TEXT)

Course

• id (Primary Key)
• name (VARCHAR)
• duration (INT)
• difficulty (ENUM('easy', 'medium', 'hard'))

Career

• id (Primary Key)
• name (VARCHAR)
• required_courses (TEXT) (Comma-separated course IDs)
• duration (INT)
• expected_salary (DECIMAL)
• difficulty (ENUM('easy', 'medium', 'hard'))

CourseCombination

• id (Primary Key)
• course_ids (TEXT) (Comma-separated course IDs)
• career_id (Foreign Key referencing Career(id))

Types and Constraints

• VARCHAR: Chosen for text attributes to handle varying lengths of strings.
• INT: Chosen for numerical attributes for simplicity and range.
• DECIMAL: Used for salary to accommodate precision.
• ENUM: Used for difficulty to ensure data consistency.

Optimizations

Indexes

• Index on Student(email): To ensure unique email addresses and speed up searches by email.
• Index on Career(name): To quickly retrieve career details by name.

Views

• View for Career Recommendations:

  CREATE VIEW CareerRecommendations AS
  SELECT s.id AS student_id, c.name AS course_name, cr.name AS career_name, cr.expected_salary
  FROM Student s
  JOIN Course c ON s.preference LIKE CONCAT('%', c.name, '%')
  JOIN Career cr ON FIND_IN_SET(c.id, cr.required_courses);

queries.sql

-- Example queries

-- Insert sample data into Student
INSERT INTO Student (name, email, preference) VALUES ('John Doe', 'john.doe@example.com', 'Engineering, Computer Science');

-- Insert sample data into Course
INSERT INTO Course (name, duration, difficulty) VALUES ('Computer Science', 4, 'medium');
INSERT INTO Course (name, duration, difficulty) VALUES ('Electrical Engineering', 4, 'hard');

-- Insert sample data into Career
INSERT INTO Career (name, required_courses, duration, expected_salary, difficulty) VALUES ('Software Developer', '1,2', 4, 80000.00, 'medium');
INSERT INTO Career (name, required_courses, duration, expected_salary, difficulty) VALUES ('Electrical Engineer', '2', 4, 85000.00, 'hard');

-- Insert sample data into CourseCombination
INSERT INTO CourseCombination (course_ids, career_id) VALUES ('1,2', 1);

-- View for Career Recommendations
CREATE VIEW CareerRecommendations AS
SELECT s.id AS student_id, c.name AS course_name, cr.name AS career_name, cr.expected_salary
FROM Student s
JOIN Course c ON s.preference LIKE CONCAT('%', c.name, '%')
JOIN Career cr ON FIND_IN_SET(c.id, cr.required_courses);

schema.sql

### schema.sql

CREATE TABLE Student (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    preference TEXT
);

CREATE TABLE Course (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    duration INT NOT NULL,
    difficulty ENUM('easy', 'medium', 'hard') NOT NULL
);

CREATE TABLE Career (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    required_courses TEXT NOT NULL,
    duration INT NOT NULL,
    expected_salary DECIMAL(10, 2) NOT NULL,
    difficulty ENUM('easy', 'medium', 'hard') NOT NULL
);

CREATE TABLE CourseCombination (
    id INT AUTO_INCREMENT PRIMARY KEY,
    course_ids TEXT NOT NULL,
    career_id INT,
    FOREIGN KEY (career_id) REFERENCES Career(id)
);

CREATE INDEX idx_student_email ON Student(email);
CREATE INDEX idx_career_name ON Career(name);