Step-by-Step SQL Schema

💡 Step-by-Step SQL Schema Example

---

🎓 1. Student Table

This table will store student information like ID, name, age, etc.

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100),
    Age INT
);

✅ Explanation:

• StudentID is a unique number for each student.

• Name is a text value up to 100 characters.

• Age is a number (integer).

---

📚 2. Course Table

This table will store course info like course ID, title, and credits.

CREATE TABLE Course (
    CourseID INT PRIMARY KEY,
    Title VARCHAR(100),
    Credits INT
);

✅ Explanation:

• CourseID uniquely identifies each course.

• Title is the name of the course.

• Credits is the number of credit hours.

---

🔗 3. Enrollment Table (To link Students and Courses)

This table connects students to the courses they take.

CREATE TABLE Enrollment (
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

✅ Explanation:

• This table tracks which student enrolled in which course and when.

• PRIMARY KEY (StudentID, CourseID) ensures a student can enroll only once per course.

• FOREIGN KEY links to Student and Course tables.

---

✅ Summary of Tables

Table	Purpose
Student	Stores student details
Course	Stores course details
Enrollment	Links students and courses

---

How to develop your logic to write SQL schema:

Step 1: Understand the entities (tables)

• Identify what you want to store.

• In your case: Students and Courses.

Step 2: Define attributes (columns) for each entity

• What information do you need about each Student?

  • StudentID (unique identifier)

  • Name

  • Age

• What about each Course?

  • CourseID (unique identifier)

  • Title

  • Credits

Step 3: Decide the data types for each attribute

• IDs: Use INT (integer) because they are numbers.

• Name and Title: Use VARCHAR(size) (string/text) where size limits characters.

• Age and Credits: Use INT.

• Dates like enrollment date: Use DATE.

Step 4: Choose primary keys

• Each table should have a primary key — a unique identifier.

  • Student → StudentID

  • Course → CourseID

Step 5: Identify relationships

• How are these tables related?

• Students can enroll in many courses, and courses can have many students → many-to-many relationship.

Step 6: Create a junction table for many-to-many relationship

• Create an Enrollment table with:

  • StudentID (foreign key referencing Student)

  • CourseID (foreign key referencing Course)

  • Other attributes like EnrollmentDate

• Use a composite primary key combining both StudentID and CourseID.

Step 7: Define foreign keys

• Foreign keys ensure referential integrity — they link data across tables.

  • Enrollment.StudentID → Student.StudentID

  • Enrollment.CourseID → Course.CourseID

---

Putting the logic into SQL code

-- Step 1 & 2: Create Student table with attributes and primary key
CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100),
    Age INT
);

-- Step 1 & 2: Create Course table with attributes and primary key
CREATE TABLE Course (
    CourseID INT PRIMARY KEY,
    Title VARCHAR(100),
    Credits INT
);

-- Step 5 & 6: Create Enrollment table for many-to-many with composite primary key
CREATE TABLE Enrollment (
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    PRIMARY KEY (StudentID, CourseID),
    
    -- Step 7: Define foreign keys to maintain relationships
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

---

Summary of your logic:

Step	What to do
1	Identify tables/entities
2	Define attributes for each entity
3	Assign data types for each attribute
4	Choose primary keys
5	Identify relationships between tables
6	Create junction table for many-to-many
7	Define foreign keys to enforce relationships

---