IB Questionbank

User interface language: English | Español

Date	May 2018	Marks available	2	Reference code	18M.2.SL.TZ0.3
Level	SL	Paper	2	Time zone	no time zone
Command term	Outline	Question number	3	Adapted from	N/A

Question

Jackson City University has a Music Department that provides music lessons to students in a number of high schools in the city.

The Jackson City University Music Department teachers visit the different schools in the city to teach students a range of musical instruments.

The following diagram shows an unnormalized table of student data.

Explain one benefit of normalizing a database.

[3]

Identify three ways that incorrect data could be prevented from being added into the School_phone_no field.

[3]

Outline what would be necessary to make the above unnormalized table conform to 1st Normal Form (1NF).

[2]

Construct the 3rd Normal Form (3NF) of the unnormalized relation shown above.

[8]

Explain the difference between 2nd Normal Form (2NF) and 3rd Normal Form (3NF).

[3]

Markscheme

Award up to [3 max].
Normalization leads to a reduction in the chance of data redundancy occurring;
Because each item of data only occurs on one location in the database;
Therefore this can reduce the possibility of update anomalies occurring;
More efficient use of memory;

Normalization reduces the likelihood of update anomalies occurring;
Because each item of data only occurs on one location in the database;
Therefore if the data is edited there is no chance that the data may exist in its original form anywhere else in the database;

Normalization leads to smaller tables with less information in each row;
This leads to a reduction of input/output transfers;
Which means that the likelihood of CPU activities being suspended are reduced/the CPU is able to work at full capacity;

Note: Do not award marks between clusters.

Use a text field for the School_phone_no with a length of 9 characters (Accept limit check);
Use an input mask such as 000000000 to ensure only numbers can be entered;
Use a validation rule to ensure the first three entries can only be “065”
Accept format check;

Award up to [2 max].
Some values are not atomic in the Student_Choice and School_phone_no fields/attributes;
Repeat rows for students with multiple choices / phone numbers with one choice per row;
Each record must have a unique key;

For Student_Choice have three columns, Stud_Choice1, Stud_Choice2 and Stud_Choice3, with null values (or default values set to “none”) as appropriate or two columns for School_phone_no;

Award up to [8 max].

Alternative 1
For a three table solution:

Student table
Award [1] for primary key: student_ID // Do not accept other suggestions for the primary key.
Award [1] for attributes relating to student e.g.

First_Name
Family_Name
Date_of_birth

Note: Do not penalize additional attributes such as Student_choice.

School table
Award [1] for primary key. Either school_Name or user defined key such as SchoolID // Do not accept a composite key.
Award [1] for attributes relating to school such as:

School(School_Name)
School_Post_code,
School_phone_no.

Note: Do not penalize additional attributes such as Student_choice.

Student choice Table/Instrument table
Award [1] for primary key. Either user-defined or a composite key.
Award [1] for attributes relating to choice such as Student_choice/Instrument choice

Award [1] for each foreign key up to a maximum of [2 max].

Example of three table solution
STUDENT (Student ID, First_Name, Family_Name, Date_of_Birth)
SCHOOL (School_Name, School_PostCode, School_Phone_no)
STUDENT CHOICE (Choice ID, Student_ID*, School_Name*, Choice)

Alternative 2
For a four table solution

Student table
Award [1] for primary key: student_ID // Do not accept other primary keys
Award [1] for attributes relating to student e.g.

First_Name
Family_Name
Date_of_birth

Note: Do not penalize additional attributes such as Student_choice.

School table
Award [1] for primary key. Either school_Name or user defined key such as SchoolID // Do not accept a composite key
Award [1] for attributes relating to school such as:

School(School_Name)
School_Post_code
School_phone_no

Note: Do not penalize for additional second School_Phone_no field.

Student choice Table/Instrument table
Award [1] for primary key. Either Lesson_ID or Instrument_ID // Do not accept a composite key.
Award [1] for attributes relating to choice such as Student_choice/Instrument choice.

Details Table/Lessons Table
Award [1] for primary key. Any ID or composite.
Award [1] for the correct foreign key in this table.

Note: This is the only table in which marks can be awarded for the use of a composite [primary] key.

Example of four table solution
STUDENT (Student_ID, First_Name, Family_Name, Date_of_Birth)
SCHOOL (School_Name, School_PostCode, School_Phone_no)
STUDENTCHOICE(Lesson_ID,Instrument)
DETAILS (Lesson_ID*, Student_ID*, School_Name*)

2NF allows non-prime attributes to be functionally dependent on non-prime attributes which means there are transitive dependencies at 2NF;
While 3NF allows non-prime attributes to be functionally dependent only on the primary / super key and is already in 2NF;
Therefore it is not possible for update anomalies to occur when a database is in 3NF;

Examiners report

[N/A]

Syllabus sections

Option C: Web science » C.1 Creating the web

Show 51 related questions

17N.2.SL.TZ0.8d:

A fragment of a script and a web form are provided below.
In the script some functions are not implemented, and only their specification is provided.

<?php
  //include a database of urls
  include('url_db.php');
  $url = $short = "";

  if ($_SERVER["REQUEST_METHOD"] == "POST")
  { $url = $_POST['url'];
    $short = make_short($url);
    function make_short($u)
    { $x = make_alpha_string($u);
      $y = first4_last4($x);
      $z = limits($x);
      $v = $y. ".".$z;   //string concatenation
      return $v;
  }

  function make_alpha_string($u)
  { // It removes, in this order: substrings corresponding to
    // protocol names, the substring www, and all characters
    // except for letters
  }

  function first4_last4($u)
  { // It returns the string made of the first 4 characters
    // followed by the last 4 characters of $u
  }

  function limits($u)
  { // It returns the string made of the first character and
    // last character of $u
  }

  mysql_query("
    INSERT INTO url_db(orig_url, short_url, url_ip) VALUES
    ( '".$_POST['url']."',
      '".$short."',
      '".$_SERVER['REMOTE_ADDR']."'
    )
  ");
 }
?>

<form method="post" action="">
URL:
<input type="text" name="url" />
<br><br>
<input type="submit" name="Submit" value="Submit" />
</form>

Describe the processing that occurs when the form is filled with the URL https://www.the2nd.org/bin.php?id=70 that the server discovers is associated with the IP address 172.16.254.1.

Hide related questions

Option C: Web science » C.2 Searching the web

Option A: Databases » A.2 The relational database model

Option C: Web science » C.3 Distributed approaches to the web

Option C: Web science » C.4 The evolving web

Option C: Web science » C.5 Analysing the web

Option C: Web science » C.6 The intelligent web

Option A: Databases

Option C: Web science

Question

Markscheme

Examiners report

Syllabus sections

View options