🏗️ Creating Simple Databases

A well-designed database is the foundation of any effective information system. This guide will walk you through the process of creating simple yet functional databases.

🎯 Database Design Process

📝 Planning Phase

• Identify purpose: Determine what information the database needs to store
• Define entities: Identify the main objects or concepts (e.g., students, courses, products)
• Determine relationships: Establish how entities relate to each other
• List attributes: Specify what information to store about each entity

🧩 Database Schema Design

• Tables: Create one table for each entity
• Fields: Define columns for each attribute
• Primary keys: Designate unique identifiers for each record
• Foreign keys: Establish relationships between tables

🛠️ Creating a Database in DBMS

📊 Using a Graphical Interface (e.g., Microsoft Access)

1. Create new database

   • Open the DBMS application
   • Select "Create New Database" option
   • Name your database and choose a location to save it

2. Create tables

   • Use the "Create Table" wizard or design view
   • Define field names, data types, and properties
   • Set primary key(s)
   • Save the table with a descriptive name

   Example table structure:

   Table: Students
   ----------------
   StudentID (Primary Key, AutoNumber)
   FirstName (Text)
   LastName (Text)
   DateOfBirth (Date/Time)
   ClassID (Number, Foreign Key)

3. Define relationships

   • Use the "Relationships" tool
   • Connect primary keys to foreign keys
   • Set relationship properties (one-to-many, etc.)
   • Enable referential integrity when appropriate

💻 Using SQL Commands

1. Create database

   CREATE DATABASE SchoolDB;
   USE SchoolDB;

2. Create tables

   CREATE TABLE Students (
     StudentID INT PRIMARY KEY AUTO_INCREMENT,
     FirstName VARCHAR(50) NOT NULL,
     LastName VARCHAR(50) NOT NULL,
     DateOfBirth DATE,
     ClassID INT
   );
   
   CREATE TABLE Classes (
     ClassID INT PRIMARY KEY AUTO_INCREMENT,
     ClassName VARCHAR(20) NOT NULL,
     ClassTeacher VARCHAR(100)
   );

3. Define relationships

   ALTER TABLE Students
   ADD CONSTRAINT FK_StudentClass
   FOREIGN KEY (ClassID) REFERENCES Classes(ClassID);

🧪 Database Normalization

📋 First Normal Form (1NF)

• Eliminate repeating groups
• Create separate tables for each set of related data
• Identify each record with a unique field (primary key)

🔄 Second Normal Form (2NF)

• Meet all requirements of 1NF
• Remove subsets of data that apply to multiple rows
• Create separate tables for these subsets
• Use foreign keys to link them

🔍 Third Normal Form (3NF)

• Meet all requirements of 2NF
• Remove fields that don't depend on the primary key
• These fields should be moved to separate tables

Example of normalization:

Unnormalized:
Student(ID, Name, Course1, Grade1, Course2, Grade2, Course3, Grade3)

1NF:
Student(ID, Name, Course, Grade)

2NF and 3NF:
Student(ID, Name)
Enrollment(StudentID, CourseID, Grade)
Course(CourseID, CourseName)

📱 Practical Example: Creating a School Database

🏫 Step 1: Identify Entities and Relationships

• Entities: Students, Teachers, Classes, Subjects
• Relationships:
  • Students belong to Classes
  • Teachers teach Subjects
  • Classes have multiple Subjects
  • Students receive Grades in Subjects

📊 Step 2: Design Tables

Students:
- StudentID (PK)
- FirstName
- LastName
- DateOfBirth
- ClassID (FK)

Teachers:
- TeacherID (PK)
- FirstName
- LastName
- Specialization

Classes:
- ClassID (PK)
- ClassName
- ClassTeacher (FK)

Subjects:
- SubjectID (PK)
- SubjectName
- TeacherID (FK)

Grades:
- GradeID (PK)
- StudentID (FK)
- SubjectID (FK)
- Score
- Term

🔧 Step 3: Implementation

1. Create each table with appropriate fields and data types
2. Define primary keys for each table
3. Establish foreign key relationships
4. Add sample data to test the structure

🔍 Testing Your Database

✅ Validation Tests

• Verify all tables and relationships are correctly defined
• Check that primary and foreign keys work as expected
• Test data entry to ensure constraints are enforced

🧮 Sample Queries

• Run basic queries to retrieve information
• Test relationships with JOIN operations
• Verify that the database meets the requirements

Example test query:

SELECT s.FirstName, s.LastName, c.ClassName, g.Score
FROM Students s
JOIN Classes c ON s.ClassID = c.ClassID
JOIN Grades g ON s.StudentID = g.StudentID
JOIN Subjects sub ON g.SubjectID = sub.SubjectID
WHERE sub.SubjectName = 'Mathematics'
ORDER BY g.Score DESC;

🚀 Best Practices

📝 Documentation

• Create an entity-relationship diagram (ERD)
• Document table structures and relationships
• Maintain a data dictionary explaining fields and constraints

🛡️ Security Considerations

• Implement appropriate access controls
• Use strong passwords for database accounts
• Consider encryption for sensitive data

🔄 Backup Strategy

• Regular database backups
• Test restoration procedures
• Store backups in secure locations

Creating a well-designed database is a foundational skill in information management. By following these principles and practices, you can build databases that efficiently store and retrieve information while maintaining data integrity and security.