Q1 A database has been designed to store data about salespersons and the

products they have sold.

The following facts help to define the structure of the
database: each salesperson works in a particular
shop
each salesperson has a unique first
name each shop has one or more
salespersons
each product which is sold is manufactured by one
company only each salesperson can sell any of the
products
the number of products that each salesperson has sold is recorded.
The table ShopSales was the first attempt at designing the database.

(a) State why the table is not in First Normal Form (1NF).

....................................................................................................................................
........
...................................................................................................................................
....[1]
(b) The database design is changed to:
SalesPerson (FirstName, Shop)
SalesProducts (FirstName, ProductName, NoOfProducts, Manufacturer)
Using the data given in the first attempt table (ShopSales), show how
these data are now stored in the revised table designs.
Table: SalesPerson
Table: SalesProducts

[3]

(c) (i) A relationship between the two tables has been

implemented. Explain how this has been done.
...................................................................................................................................
........
...................................................................................................................................
........
...................................................................................................................................
........
...................................................................................................................................
........
...................................................................................................................................
....[2]
(ii)Explain why the SalesProducts table is not in Third Normal Form (3NF).
....................................................................................................................................
.......
...................................................................................................................................
........
...................................................................................................................................
........
...................................................................................................................................
....[2]
 (iii) Write the table definitions to give the database in 3NF.
...................................................................................................................................
........
...................................................................................................................................
........
...................................................................................................................................
........
...................................................................................................................................
....[2]
Q.2 A school stores a large amount of data. This includes student
attendance, qualification, and contact details. The school’s software uses a
file-based approach to store this data.
(a) The school is considering changing to a DBMS.
(i) State what DBMS stands for.
...................................................................................................................................
....[1]
(ii) Describe two ways in which the Database Administrator (DBA) could use
the DBMS software to ensure the security of the student data.
1 .................................................................................................................................
.......
...................................................................................................................................
........
...................................................................................................................................
........
...................................................................................................................................
........
2 .................................................................................................................................
.......
...................................................................................................................................
........
...................................................................................................................................
........
...................................................................................................................................
....[4]

(iii) A feature of the DBMS software is a query

processor. Describe how the school secretary could
use this software.
...................................................................................................................................
........
...................................................................................................................................
........
...................................................................................................................................
........
...................................................................................................................................
....[2]
(iv) The DBMS has replaced software that used a file-based approach with a
relational database.
Describe how using a relational database has overcome the previous
problems associated with a file-based approach.
...................................................................................................................................
........
...................................................................................................................................
........
...................................................................................................................................
........
...................................................................................................................................
....[3]

(b) The database design has three tables to store the classes that
students attend. STUDENT(StudentID, FirstName, LastName, Year,
TutorGroup) CLASS(ClassID, Subject)
CLASS-GROUP(StudentID, ClassID)

Primary keys are not shown. There is a one-to-many relationship between CLASS
and
CLASS–GROUP.
(i) Describe how this relationship is implemented.
...................................................................................................................................
........
...................................................................................................................................
........
...................................................................................................................................
....[2]
(ii) Describe the relationship between CLASS-GROUP and STUDENT.
....................................................................................................................................
...[1]
(iii) Write an SQL script to display the StudentID and FirstName of all
students who are in the tutor group 10B. Display the list in alphabetical
order of LastName.
....................................................................................................................................
.......
...................................................................................................................................
........
...................................................................................................................................
........
...................................................................................................................................
....[4]
(iv) Write an SQL script to display the LastName of all students who attend
the class whose ClassID is CS1.
....................................................................................................................................
.......
...................................................................................................................................
........
...................................................................................................................................
........
...................................................................................................................................
........
...................................................................................................................................
........
...................................................................................................................................
........
...................................................................................................................................
........