1.
8 Database & Data modelling Revision Questions
1. 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.
It’s a database management system
(ii) Describe two ways in which the Database Administrator (DBA) could use the DBMS software
to ensure the security of the student data.
1 Access Rights would eliminate any unathorised access (usernames and passwords)
2 Create regular backups
(iii) A feature of the DBMS software is a query processor.
Describe how the school secretary could use this software.
It allows a developer to extract data from a database,
Allows for criteria to be entered
Searches in the database to meet the criteria
Organises and displays an output to the user
(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.
Each record must have fixed length and each field must contain only one item of data, no variable
number of fields shall be allowed.
(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.
(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.
.....................................................................................................................................................
.....................................................................................................................................................
.................................................................................................................................................... [4]
2. (a) A Database Management System (DBMS) provides the following features.
Draw a line to match each feature with its description.
Feature Description
A file or table containing all the details of the
database design
Data design features to ensure the validity of
Data dictionary
data in the database
A model of what the database will look like,
Data security
although it may not be stored in this way
Methods of protecting the data including the
Data integrity uses of passwords and different access
rights for different users of the database
[3]
(c) The database design has three tables to store the qualifications and grades each student has
attained. The following is a sample of the data from each table.
STUDENT
StudentID FirstName LastName Tutor
001AT Ahmad Tan 11A
003JL Jane Li 11B
011HJ Heather Jones 10A
QUALIFICATION
QualCode Level Subject
CS1 IGCSE Computer Science
MT9 IGCSE Maths
SC12 IGCSE Science
STUDENT-QUALIFICATION
QualCode StudentID Grade DateOfAward
SC12 011HJ A 31/8/2014
SC12 003JL C 31/8/2014
CS1 003JL B 31/8/2014
(i) Draw an Entity-Relationship (E-R) diagram to show the relationships between these three
tables.
STUDENT-
STUDENT QUALIFICATION
QUALIFICATION
[2]
(ii) State the type of relationship that exists between STUDENT and
STUDENT-QUALIFICATION.
ONE TO MANY [1]
(iii) Describe how the relationship between QUALIFICATION and
STUDENT-QUALIFICATION is implemented.
The primary key QualCore in the Qualification table links to the foreign key Qualcore in the
STUDENT-Qualification table [2]
(d) (i) The database will store each student’s date of birth.
Write an SQL script to add a date of birth attribute to the appropriate table.
ALTER TABLE STUDENT
ADD DoB DATE; [2]
(ii) Write an SQL script to display the StudentID, Grade and DateOfAward for the
QualCode value of SC12.
SELECT StudentID, Grade, DateOfAward
FROM STUDENT-QUALIFICATION
WHERE Qualcode= ‘SC12’
(iii) Write an SQL script to display the FirstName and LastName and QualCode for all
STUDENT-QUALIFICATIONs for which the Grade value is A.
SELECT FirstName, LastName, QualCode,StudentID, Grade, DateOfAward
FROM STUDENT-QUALIFICATIONS INNERJOIN STUDENT
WHERE GRADE = ‘A’
3. (a) Five descriptions and seven relational database terms are shown below.
Draw a line to link each description to its correct database term.
Description Database term
Secondary key
Any object, person or thing about which
Candidate key
it is possible to store data
Dataset organised in rows and
columns; the columns form the Entity
structure and the rows form the content
Any attribute or combination of
attributes that can act as a unique key Foreign key
Attribute(s) in a table that link to the
primary key in another table to form a Primary key
relationship
Attribute or combination of attributes
Table
that is used to uniquely identify a record
Tuple
[5]
(b) Explain what is meant by referential integrity.
Referential integrity is a property of data stating that all its references are valid. In the context
of relational databases, it requires that if a value of one attribute of a relation references a
value of another attribute, then the referenced value must exist.
4. A database is designed to store data about students at a college and the subjects which they
study.
• All students are based in a tutor group.
• A tutor supervises all the students in their tutor group.
• Each subject has one subject teacher only.
• Students study a number of subjects.
This table StudentSubjects was a first attempt at the database design.
Table: StudentSubjects
StudentName TutorGroup Tutor Subject Level SubjectTeacher
Tom 6 SAN Physics A SAN
Chemistry A MEB
General Studies AS DIL
Joe 7 MEB Geography AS ROG
French AS HEN
Samir 6 SAN Computer Science A VAR
Chemistry A MEB
Maths A COR
General Studies A DIL
(a) (i) Explain why the table is not in First Normal Form (1NF).
The Table has repeating groups of attributes [1]
(ii) Explain your answer by referring to the data.
Attribute subject has multiple entries for each tuple [1]
(b) The design is changed to:
Student (StudentName, TutorGroup, Tutor)
StudentSubjectChoices (StudentName, Subject, Level, SubjectTeacher)
Using the data given in the first attempt table, show how this data is now stored in the revised
table designs.
Table: Student
StudentName TutorGroup Tutor
Tom 6 SAN
Joe 7 MEB
Samir 6 SAN
Table: StudentSubjectChoices
StudentName Subject Level SubjectTeacher
TOM Physics A SAN
TOM Chemistry A MEB
TOM General Science AS DIL
JOE Geography AS ROG
JOE French AS HEN
SAMIR Computer Science
SAMIR Chemistry
SAMIR Maths
SAMIR General Science
[3]
(c) (i) Explain what is meant by a primary key.
Attribute or combination of them that uniquely define each tuble combination
[2]
(ii) A student is not allowed to choose the same subject at A Level and AS.
What is the primary key of table StudentSubjectChoices?
Subject
(iii) There is a relationship between tables Student and StudentSubjectChoices.
Explain how the relationship is established using a primary key and foreign key.
Foreign key is an attribute or combination of them that relates two tables togerther
Foreign key uses the primary of each table to clink them
StudentName it the foreign key
(d) The design of table StudentSubjectChoices is:
StudentSubjectChoices (StudentName, Subject, Level, SubjectTeacher)
Explain why this table is not in Second Normal Form (2NF).
It’s not in Second Normal Form because there re no primary keys[2]
(e) The design of table Student is:
Student (StudentName, TutorGroup, Tutor)
Explain why this table is not in Third Normal Form (3NF).
5. 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.
FirstName Shop ProductName NoOfProducts Manufacturer
television set 3 SKC
Nick TX refrigerator 2 WP
digital camera 6 HKC
hair dryer 1 WG
Sean BH
electric shaver 8 BG
television set 2 SKC
TX mobile phone 8 ARC
John
digital camera 4 HKC
toaster 3 GK
(a) State why the table is not in First Normal Form (1NF).
Because we have repeating Groups as product name has 3 entries in one record
(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
FirstName Shop
Nick TX
Sean BH
John TX
Table: SalesProducts
FirstName ProductName NoOfProducts Manufacturer
Nick Television set 3 skc
Nick refrigerator 2 WP
Nick Digital camera 6 HKC
Sean Hair dryer 1 wg
Sean Electric shaver 8 BG
Sean Television set 2 skc
John Mobile phone 8 ARC
John Digital camera 4 HKC
John toaster 3 GK
[3]
(c) (i) A relationship between the two tables has been implemented.
Explain how this has been done.
They are linked with a foreign key
(ii) Explain why the SalesProducts table is not in Third Normal Form (3NF).
Because there are no primary keys
(iii) Write the table definitions to give the database in 3NF.
There bust be no nonkey dependencies
6. A health club offers classes to its members. A member needs to book into each class in advance.
(a) The health club employs a programmer to update the class booking system. The programmer
has to decide how to store the records. The choice is between using a relational database or a
file-based approach.
Give three reasons why the programmer should use a relational database.
1 Data sharing, The data belongs to the whole organisation, not to the individual
departments
2 Improved security, enforced security due to selective access
3 Increased productivity,
(b) The programmer decides to use three tables: MEMBER, BOOKING and CLASS.
Complete the Entity-Relationship (E-R) diagram to show the relationships between these tables.
MEMBER CLASS
BOOKING
[2]
(c) The CLASS table has primary key ClassID and stores the following data:
ClassID Description StartDate ClassTime NoOfSessions AdultsOnly
DAY01 Yoga beginners 12/01/2016 11:00 5 TRUE
EVE02 Yoga beginners 12/01/2016 19:00 5 FALSE
DAY16 Circuits 30/06/2016 10:30 4 FALSE
Write an SQL script to create the CLASS table.
CREATE TABLE CLASS(
ClassID VARCHAR (5),
Description VARCHAR(14),
StartDate DATE,
ClassTime TIME,
NoOfSessions Int,
AdultsOnly BOOLEAN,
PRIMARY KEY (ClassID) ); [6]
7. A hospital is divided into two areas, Area A and Area B. Each area has several wards. All the
ward names are different.
A number of nurses are based in Area A. These nurses always work on the same ward. Each
nurse has a unique Nurse ID of STRING data type.
(a) Describe the relationship shown above.
Many to One [1]
(b) A relational database is created to store the ward and nurse data. The two table designs for
Area A are:
A-WARD(WardName, NumberOfBeds)
A-NURSE(NurseID, FirstName, FamilyName, WardName)
(i) Complete the design for the A-NURSE table. [1]
(ii) Explain how the relationship in part (a) is implemented.
Different Nurses with different NurseIDs are allowed to have the same WardName or be in
the same Ward
(c) In Area B of the hospital, there are a number of wards and a number of nurses.
Each Area B ward has a specialism.
Each Area B nurse has a specialism.
A nurse can be asked to work in any of the Area B wards where their specialism matches with the
ward specialism.
The relationship for Area B of the hospital is:
(i) Explain what the degree of relationship is between the entities B-NURSE and B-WARD.
MANY to MANY Relationship [1]
(ii) The design for the Area B data is as follows:
B-NURSE(NurseID, FirstName, FamilyName, Specialism)
B-WARD(WardName, NumberOfBeds, Specialism)
B-WARD-NURSE( NurseID, Specialism, WardName )
Complete the attributes for the third table. Underline its primary key. [2]
(iii) Draw the relationships on the entity-relationship (E-R) diagram.
B-NURSE B-WARD
B-WARD-NURSE
[2]
(d) Use the table designs in part (c)(ii).
(i) Write an SQL query to display the Nurse ID and family name for all Area B nurses with a
specialism of ‘THEATRE’.
SELECT NurseID, FamilyName
FROM B-NURSE
WHERE Specialism=’THEATRE’
(ii) Fatima Woo is an Area B nurse with the nurse ID of 076. She has recently married, and her
new family name is Chi.
Write an SQL command to update her record.
UPDATE FamilyName
SET Chi
WHERE NurseID= 076
[3]
8. Some shops belong to the Rainbow Retail buying group. They buy their goods from one or more
suppliers.
Each shop has:
• a unique shop ID
• a single retail specialism (for example, food, electrical, garden).
Each supplier has:
• a unique supplier ID
• a similar single specialism recorded.
Rainbow Retail creates a relational database to record data about the shops and their suppliers.
The entity-relationship (E-R) diagram for the relationship between the SHOP and SUPPLIER
tables is shown.
(a) Explain what the degree of relationship is between the entities SHOP and SUPPLIER.
MANY to MANY [1]
The database design is as follows:
SHOP(ShopID, ShopName, Location, RetailSpecialism)
SUPPLIER(SupplierID, SupplierName, ContactPerson, RetailSpecialism)
SHOP-SUPPLIER(ShopID, SupplierID)
The SHOP–SUPPLIER table stores the suppliers that each shop has previously used.
Primary keys are not shown.
(b) (i) Label the entities and draw the relationships to complete the revised E-R diagram.
SUPPLIER
[3]
(ii) Complete the following table to show for each database table:
• the primary key
• the foreign key(s) (if any):
° Each table may contain none, one or more foreign key(s).
° For a table with no foreign key, write ‘None’.
• an explanation for the use of any foreign key.
Foreign key(s)
Table Primary key Explanation
(if any)
SHOP
SUPPLIER
SHOP–SUPPLIER
[5]
(iii) The database designer has implemented SUPPLIER.ContactPerson as a secondary key.
Describe the reason for this.
….................................................................................................................................................
….................................................................................................................................................
….................................................................................................................................................
.................................................................................................................................................... [2]
(c) (i) Write an SQL query to display the shop ID and location of all shops with a ‘GROCERY’
specialism.
….................................................................................................................................................
….................................................................................................................................................
.................................................................................................................................................... [3]
(ii) The existing shop with ID 8765 has just used the existing supplier SUP89 for the first time.
Write an SQL script to add this data to the database.
….................................................................................................................................................
….................................................................................................................................................
.................................................................................................................................................... [3]
9. A clinic is staffed by several doctors. The clinic serves thousands of patients. Each day and at
any one time, there is only one doctor in the clinic available for appointments.
The clinic stores patient, doctor and appointment data in a relational database.
(a) (i) Underline the primary key for each table in the following suggested table designs.
PATIENT(PatientID, PatientName, Address, Gender)
DOCTOR(DoctorID, Gender, Qualification)
APPOINTMENT(AppointmentDate, AppointmentTime, DoctorID, PatientID) [2]
(ii) Complete the following entity-relationship (E-R) diagram for this design.
[2]
(b) The doctors are concerned that many patients make appointments but do not attend them.
Describe the changes to the table designs that could be made to store this information.
.....................................................................................................................................................
.................................................................................................................................................... [2]
(c) The doctors are about to set up a new clinic in the neighbouring village, SITE-B.
The original location is identified as SITE-A.
A new table is designed to store the ID of the doctor who is able to work at each site.
DOCTOR-AVAILABILITY(DoctorID, Site)
Five entries stored in the table are:
DoctorID Site
098 SITE-A
074 SITE-A
117 SITE-B
098 SITE-B
033 SITE-B
(i) State what this data shows about the availability of the doctor with the ID of 098.
.....................................................................................................................................................
.................................................................................................................................................... [1]
(ii) Opening a new clinic in the neighbouring village will not require any additional table for storing
appointments. It will need a change to the existing appointment table design.
Show the revised APPOINTMENT table.
APPOINTMENT( .........................................................................................................................
.................................................................................................................................................. ) [1]
(d) The doctor with the ID of 117 has recently been allocated a new DoctorID of 017.
(i) Write an SQL script to update this doctor’s record in the database.
UPDATE .....................................................................................................................................
SET .............................................................................................................................................
WHERE ...................................................................................................................................... [3]
(ii) Describe why this update could cause problems with the existing data stored.
.....................................................................................................................................................
.....................................................................................................................................................
.................................................................................................................................................... [2]
(e) Write an SQL script to display the date and time of all appointments made by the patient with
the PatientID of 556.
.....................................................................................................................................................
.....................................................................................................................................................
.................................................................................................................................................... [3]
10. A company takes customer service for its clients very seriously.
The client
• The client names are unique.
A visit
• The company arranges a date for a visit to gather feedback from a client.
• A visit to a client never takes more than one day.
• Over time, the client receives many visits.
Staff (Interviewers)
• One or more staff attend the visit.
• If there is more than one staff member visiting, each performs a separate interview.
Interviews
• Each interview is classified as either ‘general’ or by some specialism, for example, marketing,
customer service or sales.
• A report is produced for each interview, InterviewText.
• Each interview is conducted by a single staff member.
The client, visit, staff and interview data will be stored in a relational database.
(a) (i) Underline the primary key for each table in the following suggested table designs.
STAFF(StaffID, StaffName, Department)
CLIENT(ClientName, Address, Town)
VISIT(ClientName, VisitDate)
INTERVIEW(ClientName, VisitDate, StaffID, SpecialistFocus, InterviewText) [3]
(ii) For each of the pairs of entities, A, B and C, draw the relationship between the two entities.
A CLIENT VISIT
B VISIT INTERVIEW
C INTERVIEW STAFF
[3]
(b) The company decides to produce a visit report, VisitReportText, for each visit made.
This text will be produced from the one or more interview texts obtained at the visit.
State how one or more of the given table designs can be changed to add this attribute.
.....................................................................................................................................................
.................................................................................................................................................... [1]
(c) Client ABC Holdings are now trading under the name of Albright Holdings.
(i) Write an SQL script to update this client’s record in the database.
UPDATE .....................................................................................................................................
SET .............................................................................................................................................
WHERE ...................................................................................................................................... [3]
(ii) Describe why this update could cause problems with the existing data stored.
.....................................................................................................................................................
.....................................................................................................................................................
................................................................................................................................................... [2]
(d) Write an SQL script to display the Staff ID of each member of staff who performed an
interview when they visited New Age Toys on 13/10/2016.
.....................................................................................................................................................
.....................................................................................................................................................
.................................................................................................................................................. [3]
(e) At present, all interviews are performed in the UK. Many clients now operate in other countries
in Europe. The company wants to perform interviews with the client’s staff in other countries.
Not all interview staff are willing to travel outside of the UK.
State how one or more of the table designs should be revised to store this information.
.....................................................................................................................................................
................................................................................................................................................ [1]
