Computer Science • Databases

Normal Form or Not?

The database

Your answer

Normal Form or Not? — Explanation

Each question is a database: its tables, and sometimes some of its data. Some are past paper questions from Cambridge International AS & A Level Computer Science (9618) Paper 1, and some were written for this site in the same style. Decide whether it's in Third Normal Form (3NF); then, a part at a time, say what stops it or what makes it so, and how to fix it.

Each problem is fixed by moving the attributes into a table keyed on what they depend on, leaving a foreign key behind to link the tables.

Answering. Choose an answer for each part. A wrong answer says what the mistake was, and you can try again; the right one shows the working. Once every part is done, the worked answer shows the normal form checklist, the normalisation step by step (click through each step to see the attributes move), and an E-R diagram of the tables in 3NF.

Signed in, your answers 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 part is worth a mark if you get it right first time: a part you had to try again, or whose answer you were shown, gets no mark. 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: explain why a given set of database tables are, or are not, in 3NF, and produce a normalised database design (9618 syllabus 8.1, Database Concepts).

Where this fits

  • AQA: AQA A Level Computer Science (7517)
  • Cambridge: Cambridge A Level Computer Science (9618); Cambridge AS Level Computer Science (9618); Cambridge A Level Information Technology (9626); Cambridge AS Level Information Technology (9626)
  • 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 A Level Information Technology

Normal Form or Not? — 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 ones in each question.
Table (relation)ripanga表테이블A grid of rows and columns holding data about one kind of thing.
Row (record)haupae / pūkete行행One entry in a table — for example, one student, one booking, one rental.
Column (field)tīwae / āpure字段필드One named piece of information stored for every row in a table.
Datararaunga数据데이터The values held in the schema’s tables.
Duplicate datatāruatia重复数据중복 데이터The same fact stored more than once — the problem normal form is designed to remove.
Keykī键키A field, or combination of fields, that identifies each row.
Primary keypātuhi matua主键기본 키The key chosen to identify each row of a table: underlined in each question.
Foreign keykī kē外键외래 키An attribute in one table that holds the primary key of another, to link them: tagged FK in each question.
Entityhinonga实体개체A distinct kind of thing a table is designed to hold data about, such as STUDENT or BOOKING.
Redundancyno attested term冗余중복성Unnecessary duplication of data across a table — one of the faults normal form checks for.
Normal form / normalisationno attested term范式 / 规范化정규형 / 정규화The set of rules, including 3NF, used to design tables that avoid redundancy and update anomalies.
Attributeno attested term属性속성One named piece of data stored about each thing in a table: a column, or field.
Composite keyno attested term复合键복합 키A primary key made of two or more attributes together, such as (OrderNo, ItemCode).
Candidate keyno attested term候选键후보 키An attribute, or smallest set of attributes, that could identify each row; the primary key is chosen from the candidate keys.
Repeating groupno attested term重复组반복 그룹More than one value in a single field, or a set of fields repeated along a row; a table with one isn’t in 1NF.
First Normal Form (1NF)no attested term第一范式제1정규형Every field holds a single, atomic value (no repeating groups), and each row is identified by a primary key.
Second Normal Form (2NF)no attested term第二范式제2정규형In 1NF, and every non-key attribute depends on the whole of the primary key: no partial dependencies.
Third Normal Form (3NF)no attested term第三范式제3정규형In 2NF, and no non-key attribute depends on another non-key attribute: no transitive dependencies.
Partial dependencyno attested term部分依赖부분 종속A non-key attribute that depends on only part of a composite primary key.
Transitive dependencyno attested term传递依赖이행적 종속A non-key attribute that depends on another non-key attribute, and so on the key only indirectly.
Link tableno attested term连接表연결 테이블A table that turns a many-to-many relationship into two one-to-many relationships, holding the primary keys of the two tables it links.
Relationship (link)pātahitanga关系관계How the rows of two tables are connected: one-to-one, one-to-many or many-to-many.
Entity-relationship (E-R) diagramno attested term实体关系图개체-관계 다이어그램A diagram of a database’s entities and the relationships between them, showing the degree of each (one-to-one, one-to-many or many-to-many).

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".