Notes-for-CAIE

1.8 Database and data modelling

1.8.1 Database Management Systems (DBMS)

• show understanding of the limitations of using a file-based approach for the storage and retrieval of data
• describe the features of a relational database which address the limitations of a file-based approach
• show understanding of the features provided by a DBMS to address the issues of:
  • data management, including maintaining a data dictionary
  • data modelling
  • logical schema
  • data integrity
  • data security, including backup procedures and the use of access rights to individuals/groups of users
• show understanding of how software tools found within a DBMS are used in practice:
  • developer interface
  • query processor
• show awareness that high-level languages provide accessing facilities for data stored in a database

Table/Relation: all the data about one entity Record/Tuple: the data in one row of a table Attribute: a column or field in a table

Developer Interface

• To create user-friendly features e.g. forms
• To create output reports
• To create interactive features e.g. buttons

Query processor

s16 11 Q8 [3]

• To create SQL queries
• To search for data that meets the criteria

Why web application uses server-side scripting [3]

• All data is held on the server
• The client doesn’t have access to all the data,
• … which keep the data more secure
• Customers can be identified when they log in,
• … from a database of usernames and passwords

1.8.2 Relational database modelling

• show understanding of, and use, the terminology associated with a relational database model: entity, table, tuple, attribute, primary key, candidate key, foreign key, relationship, referential integrity, secondary key and indexing
• produce a relational design from a given description of a system
• use an entity-relationship diagram to document a database design
• show understanding of the normalisation process: First (1NF), Second (2NF) and Third Normal Form (3NF)
• explain why a given set of database tables are, or are not, in 3NF
• make the changes to a given set of tables which are not in 3NF to produce a solution in 3NF, and justify the changes made

Features of a rational database [4]

s16 11 Q8 [3]: comparing to file-based approach

Max 3 from each group

• Multiple tables are linked together
• …, which eliminates data redundancy
• … and increase data consistency
• data need only to be updated once
• … and associated data will be automatically updated

• …, which make it easier to maintain the data

• Complex queries can be more easily written as it have a query language

• Allows concurrent access to the data
• … by the use of record locking

Referential integrity

w16 11 Q1 [3]

• Ensures related data in tables are consistent
• Cascading update
• If a record in the ‘primary’ table is modified,
• … all linked records in foreign tables will also be modified

Normalization

1NF

• No repeating groups

2NF

• No partial dependencies

3NF

• No non-key(transitive) dependencies

When explaining why is not in 3NF, say also ... is dependent on ... which is not the primary key.

s15 12 Q9

1.8.3 Data Definition Language (DDL) and Data Manipulation Language (DML)

• show understanding that DBMS software carries out:
  • all creation/modification of the database structure using its DDL
  • query and maintenance of data using its DML
• show understanding that the industry standard for both DDL and DML is Structured Query Language (SQL)
  • show understanding of a given SQL script
  • write simple SQL (DDL) commands using a sub-set of commands for:
    • creating a database (CREATE DATABASE)
    • creating a table definition (CREATE TABLE)
    • changing a table definition (ALTER TABLE)
    • adding a primary key or foreign key to a table (ADD PRIMARY KEY)
  • write a SQL script for querying or modifying data (DML) which are stored in (at most two) database tables
    • Queries:
    • SELECT, FROM, WHERE, ORDER BY, GROUP BY, INNER JOIN
  • Data maintenance:
    • INSERT INTO, DELETE FROM, UPDATE

SQL scripts examples

Example 1

s16 s11 Q8 [4]

Table definition:

STUDENT(StudentID, FirstName, LastName, Year, TutorGroup)
CLASS-GROUP(StudentID, ClassID)

Task:

Display the LastName of all students who attend the class whose ClassID is CS1 SQL:

SELECT STUDENT.LastName
FROM STUDENT, CLASS-GROUP
WHERE ClassID = "CS1" // WHERE (ClassID = "CS1") 
AND CLASS-GROUP.StudentID = STUDENT.StudentID;