Lecture 7: Comprehensive Review & Practice

1. Theoretical Concepts Review

• Database vs. File System: Database offers data integrity, security, and concurrent access, whereas file systems suffer from redundancy and isolation.
• Hierarchy: Database -> Tables -> Records (Rows) -> Fields (Columns).
• Referential Integrity: A system of rules in DBMS that ensures relationships between records in related tables are valid (e.g., you cannot have an Order for a non-existent Customer).

2. Access Implementation Review

• Junction Table: To implement a Many-to-Many relationship (e.g., Students <-> Courses), you create a third table (Enrollments) containing Student_ID and Course_ID.
• Design View: The environment used to modify field properties (Field Size, Default Value, Validation Rules).

3. SQL Practice Scenarios

• Scenario 1: Retrieve all employees in the 'Sales' department.

  SELECT * FROM Employees WHERE Department = 'Sales';

• Scenario 2: Add a new student.

  INSERT INTO Students (ID, Name, Age) VALUES (1, 'Ali', 20);

• Scenario 3: Delete a specific course.

  DELETE FROM Courses WHERE CourseCode = 'CS101';

• Scenario 4: Create a Payroll table linked to Employee.

  CREATE TABLE Payroll (
      Payroll_ID INT PRIMARY KEY,
      Amount DECIMAL(10,2),
      Emp_ID INT,
      FOREIGN KEY (Emp_ID) REFERENCES Employees(ID)
  );

4. Key Takeaways for Exam

• Identify the Primary Key and Foreign Key in any diagram.
• Know the syntax for SQL commands (Focus on SELECT, WHERE, INSERT, UPDATE).
• Understand relationship types (1:1, 1:M, M:N) and how to draw/link them.