Computer Science • Databases

Schema Architect

The database

Your design

Schema Architect — Explanation

Each question is a database from a Cambridge International AS & A Level Computer Science (9618) Paper 1: its tables, its fields, and often some of its data. Design it: give every field a data type, mark the fields that make up each table's primary key, and say which fields are foreign keys, and which table each refers to. The SQL that creates your design is written as you go (the SQL toggle shows it).

Checking. Check your design once every field has a type. Each field that's wrong says why, and you can change it and check again. Once it's all right, or you show the model answer, you see the model SQL and an E-R diagram of the database. Where a question's mark scheme gives the SQL for a table, that's the model; elsewhere the types follow the data and the question, and where nothing shows what an ID looks like, INTEGER and text are both right.

Signed in, your designs are saved: come back to any question and carry on where you left off. Done questions are ticked in the list, and Random question picks one you haven't done yet.

Set by your teacher. Questions your teacher has set you are marked in the list, and come up first. Each field is worth a mark if it's right (its type and keys) the first time you check. If you had already done one before it was set, it starts again, fresh; after that you can have as many attempts as you like, and your best counts.

Objective: write an SQL script to define a table (CREATE TABLE) with appropriate data types, a primary key and foreign keys (9618 syllabus 8.3, Data Definition Language).

Where this fits

  • AQA: AQA A Level Computer Science (7517)
  • Cambridge: Cambridge IGCSE Information and Communication Technology (0417); Cambridge A Level Computer Science (9618); Cambridge AS Level Computer Science (9618); Cambridge A Level Information Technology (9626); Cambridge AS Level Information Technology (9626) Goes beyond Cambridge IGCSE Information and Communication Technology (0417): 0417 builds tables in database software; writing CREATE TABLE in SQL goes beyond it. Goes beyond Cambridge A Level Information Technology (9626): 9626 builds tables in database software; writing CREATE TABLE in SQL goes beyond it. Goes beyond Cambridge AS Level Information Technology (9626): 9626 builds tables in database software; writing CREATE TABLE in SQL goes beyond it.
  • IB: IB Computer Science HL; IB Computer Science SL
  • NCEA Level 2 Digital Technologies: 91891 Apply conventions to develop a design for a digital technologies outcome; 91891 Apply conventions to develop a design for a digital technologies outcome; 91892 Use advanced techniques to develop a database; 91892 Use advanced techniques to develop a database
  • NCEA Level 3 Digital Technologies: 91902 Use complex techniques to develop a database; 91902 Use complex techniques to develop a database
  • Pearson Edexcel International: Edexcel International GCSE ICT (4IT1); Edexcel International A Level Information Technology Goes beyond Edexcel International GCSE ICT (4IT1): 4IT1 builds tables in database software; writing CREATE TABLE in SQL goes beyond it.

Schema Architect — Key Terms

Key concepts in English, with te reo Māori, Chinese (Simplified) and Korean.

EnglishTe reo Māori中文（简体）한국어What it means on this page
Databasepātengi raraunga数据库데이터베이스A structured collection of related tables, such as the one in each question.
Table (relation)ripanga表테이블A grid of rows and columns holding data about one kind of thing; CREATE TABLE defines one.
Row (record)haupae / pūkete行행One entry that will be stored in a table once it is built.
Column (field)tīwae / āpure字段필드One named piece of data stored for every row of a table, with a data type.
Datararaunga数据데이터The values a finished database will hold.
Data typemomo raraunga数据类型자료형The kind of value a column can hold — e.g. INTEGER, VARCHAR, DATE.
Primary keypātuhi matua主键기본 키The field, or fields, that identify each row of a table: marked KEY in your design.
Foreign keykī kē外键외래 키A field that holds another table’s primary key, linking the two tables; it has the same data type as that key.
Relationship (link)pātahitanga关系관계How the rows of two tables are connected through a foreign key: one-to-one, one-to-many or many-to-many.
Schemaaronuinga模式스키마The overall design of tables, columns and relationships you are building.
DDL (data definition language)no attested term数据定义语言데이터 정의어The SQL statements — CREATE TABLE and so on — this page generates from your design.
Composite keyno attested term复合键복합 키A primary key made of two or more fields together, such as (StudentID, TestID).
Entity-relationship (E-R) diagramno attested term实体关系图개체-관계 다이어그램A diagram of a database’s tables and the relationships between them, showing the degree of each.
INTEGERno attested term整数정수An SQL data type for whole numbers, such as a count or a mark.
REALno attested term实数실수An SQL data type for numbers with a decimal part, such as a height or a price.
VARCHAR(n)no attested term可变长字符串가변 길이 문자열An SQL data type for text of up to n characters; CHARACTER (CHAR) is the other text type.

On the te reo Māori column. Terms marked as gaps have no attested equivalent in the sources checked — Karaitiana Taiuru's Dictionary of Māori Computer and Social Media Terms, Paekupu, the Reserve Bank's te reo financial glossary, NZQA and Te Aka. No coinage is printed as though it were established; where a class needs one, commission it from Te Taura Whiri i te Reo Māori and credit the translator. Te reo Māori is not italicised and takes no plural "s".