Create entity relationship diagrams (conceptual, logical)

2. Entity‑Relationship modelling

2.1 Conceptual ER diagram

• Shows only the essential entities, primary keys and relationships.
• Attributes are limited to those required to understand the business rules.
• Notation (Cambridge‑recommended):
  • Rectangle = entity.
  • Diamond = relationship.
  • Lines with cardinality symbols (1, 0..1, 1..N, N) or crow‑foot notation.

2.2 Logical ER diagram

• All attributes for each entity are listed inside the entity rectangle.
• Primary keys are underlined; foreign keys are identified (FK).
• Data types may be noted as a side comment (e.g., VARCHAR(30), INT).
• Repeating groups, partial and transitive dependencies have been removed (i.e. the diagram is already normalised).

2.3 Referential integrity

Referential integrity guarantees that a foreign‑key value always matches an existing primary‑key value in the referenced table. It prevents orphan records and maintains consistency across related tables. In most relational DBMSs it is enforced by defining FK constraints (e.g., ON DELETE CASCADE or ON UPDATE RESTRICT).

2.4 Mapping ER‑diagram elements to the syllabus (10.1)

2.5 Example – University Course Management System

Conceptual ER diagram (suggested)

Logical ER diagram (suggested)

3. Normalisation to Third Normal Form (10.2)

3.1 Why normalise?

• Eliminate data redundancy.
• Reduce update, insertion and deletion anomalies.
• Make the database easier to maintain and extend.
• Improve query performance when proper indexes are used.

3.2 Normalisation steps – worked example

StudentID | StudentName | CourseCodes               | Grades
-----------------------------------------------------------------
001       | Alice       | CS101, MA102              | A, B
002       | Bob         | CS101                     | B

StudentID | StudentName | CourseCode | Grade
------------------------------------------------
001       | Alice       | CS101      | A
001       | Alice       | MA102      | B
002       | Bob         | CS101      | B

-- Student table
StudentID | StudentName
-------------------------
001       | Alice
002       | Bob

-- Enrolment table
EnrolmentID | StudentID | CourseCode | Grade
---------------------------------------------
E001        | 001       | CS101      | A
E002        | 001       | MA102      | B
E003        | 002       | CS101      | B

-- Student (PK = StudentID)
StudentID | StudentName | DateOfBirth | Email

-- Course (PK = CourseCode)
CourseCode | CourseTitle | Credits | Department

-- Enrolment (PK = EnrolmentID)
EnrolmentID | StudentID (FK) | CourseCode (FK) | EnrolDate | Grade

3.3 Quick normalisation checklist

• 1NF – remove repeating groups; ensure each field holds an atomic value.
• 2NF – eliminate partial dependencies (only relevant when the primary key is composite).
• 3NF – eliminate transitive dependencies; each non‑key attribute must depend solely on the primary key.

4. Data‑dictionary (10.3)

4.1 Purpose

• Provides a definitive description of every field in the database.
• Facilitates clear communication between designers, developers and end‑users.
• Supports validation, verification and documentation.

4.2 Standard template

4.3 Mini‑exercise

Complete the data‑dictionary for the Course entity (add fields such as CourseTitle, Credits, Department). Submit the filled table for peer review.

5. File and data management (10.4)

5.1 Types of files

5.2 Management Information Systems (MIS)

• Collects, stores and processes data to support decision‑making.
• Relies on a well‑designed database for accurate reporting.
• Typical MIS reports: student enrolment statistics, course popularity, grade distributions.

5.3 DBMS families – advantages & disadvantages

5.4 Indexing and access methods

• Primary‑key index: automatically created; enables fast direct look‑up.
• Secondary (non‑unique) index: created on frequently searched fields (e.g., Email, Grade).
• Choosing the right index reduces query time but adds overhead on insert/update operations.

6. Validation, verification and error‑checking (10.1 & 10.4)

6.1 Common validation rules

• Domain constraints – e.g., Grade must be one of {A,B,C,D,F}.
• Format checks – e.g., Email must contain “@” and a domain.
• Range checks – e.g., Credits between 1 and 10.
• Uniqueness – primary‑key and candidate‑key enforcement.
• Referential integrity – foreign‑key values must exist in the referenced table.

6.2 Implementation checklist (Microsoft Access / LibreOffice Base)

1. Open the table in **Design view**.
2. Set the **Data Type** and **Field Size** for each attribute.
3. Mark the primary key (right‑click → **Primary Key**).
4. For foreign keys, choose **Lookup Wizard** or set a **Relationship** in the Relationships window.
5. Define **validation rules** (e.g., Between 1 And 10 for Credits).
6. Set **Required** = Yes for fields that cannot be left blank.
7. Enable **Referential Integrity** in the Relationships window (check “Enforce Referential Integrity”).
8. Save the table and test by entering sample data.

7. Designing data‑entry forms (10.1)

7.1 Form design checklist

• Layout – group related fields together; use tabs or sections.
• Tab order – set a logical sequence for keyboard navigation.
• Required fields – mark with an asterisk and set the **Required** property.
• Control types – use text boxes for free text, combo boxes or list boxes for limited choices, date pickers for dates.
• Default values – pre‑populate fields where appropriate (e.g., current date for EnrolDate).
• Error messages – customise the **Validation Text** property to give clear feedback.
• Navigation buttons – include **Next**, **Previous**, **New Record**, **Save**, **Delete**.
• Form caption – clearly state the purpose (e.g., “Student Registration Form”).

8. Switchboard / menu design (10.1)

A switchboard provides a single point of entry to the database’s forms, reports and queries.

• In Access: File → New → Blank Database → Switchboard Manager.
• In LibreOffice Base: create a **Main Form** with command buttons linked to other forms/reports.
• Design tips:
  • Group actions logically (e.g., “Data Entry”, “Reports”, “Maintenance”).
  • Use clear button labels (“Add Student”, “View Enrolments”).
  • Include an **Exit** button that closes the application.
  • Apply a consistent colour scheme and font for a professional look.

9. Importing and exporting data (10.1 & 10.4)

9.1 Importing CSV/TXT files

1. In Access: External Data → New Data Source → From File → Text File.
2. In Base: File → New → Database → Connect to Existing Database → Text.
3. Choose the source file (CSV or TXT).
4. Select **First Row Contains Field Names** if applicable.
5. Specify the delimiter (comma for CSV, tab for TXT).
6. Map each source column to the appropriate table field; adjust data types if needed.
7. Run the import and verify the data in the target table.

9.2 Exporting to CSV/TXT/RTF

1. Open the table or query you wish to export.
2. In Access: External Data → Export → Text File. In Base: File → Export → Text.
3. Choose the destination folder and file name (e.g., Students.csv).
4. Select the appropriate format:
   • CSV – comma‑separated values.
   • TXT – tab‑delimited or fixed‑width.
   • RTF – for formatted reports (use the Report wizard, then export).
5. Check “Include Field Names on First Row” if required.
6. Complete the wizard and open the exported file to confirm correct formatting.

10. Summary checklist for Cambridge 9626 (10.1‑10.4)

• Identify entities, attributes, PKs and FKs.
• Draw conceptual and logical ER diagrams (with correct notation).
• Explain and enforce referential integrity.
• Normalise to 3NF and justify each step.
• Create a complete data‑dictionary.
• Design a functional data‑entry form and a switchboard/menu.
• Define validation, verification and error‑checking rules.
• Choose appropriate file types and DBMS; discuss advantages/disadvantages.
• Demonstrate import and export of CSV, TXT and RTF files.