Database Design and Development

Question 1

What law do companies have to adhere to concerning peoples private data?

Answer 1

The General Data Protection Regulation (GDPR)

Question 2

Under the General Data Protection Regulation (GDPR) data must be:

Answer 2

•processed lawfully, fairly 
  and in a transparent 
  manner 
  in relation to individuals
•used for the declared 
  purpose only
•limited to the data needed 
  for the declared purpose
•accurate
•not kept for longer than •necessary
•held securely

Question 3

Describe and exemplify entity-relationship diagrams

Answer 3

Entity – a place where information about a person, thing or concept is collected – a table.
Attribute – describe the facts, details or characteristic of an entity – a field.

Question 4

What are the rules about an entity diagram?

Answer 4

crows foot- means one to many, one line that branches into three.
primary key- underline and label
foreign key- put an asterix* and label

Question 5

What are the attribute types for a database?

Answer 5

— text —A combination of 
               text and number e.g. 
               name, address, 
               telephone number.
— number—whole numbers 
                     and decimal 
                     numbers.
— date— usually in the format 
                 DD/MM/YYYY
— time— usually in the format 
                00:00 but can be 
                00:00:00
— Boolean— true or false

Question 6

What are the different types of validation?

Answer 6

Range – ensures data is
between a lower and
upper limit
Restricted Choice – limits the
user to a
list of
options.
Length Check – restricts the
number of
characters that
can be
entered.
Presence Check – ensures that
the field is
not left
blank – it is
required

Question 7

What is a foreign key used for?

Answer 7

A FOREIGN KEY is a key used to link two tables together

Question 8

What is a primary key used for?

Answer 8

A primary key is a unique identifier for a row/record in a table.

Question 9

Display the team name, town, contact person and e-mail address for all teams in the east district from the table “team”

Answer 9

SELECT teamName, teamTown, teamContact, teamEmail
FROM Team
WHERE teamDistrict= “East”;

Question 10

Display the surname, DOB and number of points scored for all players aged under 18 who have scored more than 5000 points from the table “Player”

Answer 10

SELECT playerSurname, playerDOB, playerPoints
FROM Player
WHERE playerAge< 18
AND playerPoints >= 5000;

Question 11

Display the forename, surname and town of any players who play for a team based in Hamilton (equi-join):

Answer 11

SELECT playerForename, playerSurname, teamTown
FROM Player, Team
WHERE (Team.teamID= Player.teamID)
AND teamTown = “Hamilton”;

Question 12

Display the forename, surname, number of points scored, team name and team ID for any players who have scored at least 4000 points for a team in the West district. As the teamID field appears in both tables, you must indicate which table you want to use, e.g. player.teamID (equi-join):

Answer 12

SELECT playerForname, playerSurname, playerPoints, teamName, Team.teamID
FROM Team, Player
WHERE (Team.teamID=Player.teamID)
AND (playerPoints>= 4000)
AND (teamDistrict="West");

Question 13

Display the forename, surname,number of points scored,team name and team ID for any players who has scored afewer than 1000 points this season, with the player who has scored the most points listed first:

Answer 13

SELECT playerForename, playerSurname, teamID, playerPoints
FROM Player
WHERE playerPoints < 1000
ORDER BY playerPoints DESC;

Question 14

Display the forename, surname and teamID of all players from teams based in Glasgow. The listing should be in alphabetical order of surname. players with the same surname should be listed in alphabetical order of first name.

Answer 14

SELECT playerForename, playerSurname, Player.TeamID,
FROM Player,Team
WHERE teamTown = “Glasgow”
ORDER BY playerSurname ASC, playerForename ASC;

Question 15

Add the following data into the table player:
PLAY2048
Stewart
Chalmers
TEAM007

Answer 15

INSERT INTO PLAYER (playerID, playerForename,playerSurname,teamID)
VALUES (“PLAY2048”,”Stewart”,”Chalmers”,”TEAM007”);

Question 16

in the table player the surname for PLAY2048 has been misspelled. It should be “stuart”, rather than “stewart”. you could update the correct record of the database by writing…….

Answer 16

UPDATE Player
SET playerForename = “Stuart”
WHERE playerID=”PLAY2048”;

Question 17

In the Team table the contact details for the Airdrie Avengers have changed. the teams contact person is now steve rogers and the email address is now airdrie@scotsdarts.com, you could update the table by……

Answer 17

UPDATE Team
SET teamContact = “Steve Rogers”, teamEmail = “airdrie@scotdarts.com”
WHERE teamName = “Airdrie Avengers”;

Question 18

Remove the team Border Reivers from the table Team.

Answer 18

DELETE FROM Team

WHERE teamName=”Border Reivers”;

Question 19

From the table Player delete Jessica Stark’s information as she has now given up darts

Answer 19

DELETE FROM Player
WHERE playerForname = “Jessica”
AND playerSurname = “Stark”;

Question 20

Give the example SELECT SQL

Answer 20

SELECT fieldName1, fieldName2, fieldName3, etc
FROM tableName
WHERE feildName = data;

Question 21

Give the example EQUI-JOIN SELECT SQL

Answer 21

SELECT fieldName1, fieldName2,fieldName3
FROM tableName1, tableName2
WHERE tableName1.fieldName = tableName2.fieldName
AND fieldName = data;

Question 22

Give the example SELECT SQL that is ordered either ascending or descending

Answer 22

SELECT fieldName1, fieldName2, fieldName3
FROM tableName
WHERE feildName = data
ORDER BY fieldName ASC/DESC;

Question 23

Give the example INSERT SQL

Answer 23

INSERT INTO tableName (fieldName1, fieldName2,)

VALUES (value 1, value2);

Question 24

Give the example UPDATE SQL

Answer 24

UPDATE tableName
SET fieldName=”updated value”
WHERE fieldname = “value”;

Question 25

Give the example DELETE SQL

Answer 25

DELETE FROM tablename

WHERE fieldname=”value”;