Databases and SQL

Databases and SQL

Topic 1.3.1

Multiple Choice

A database can be stored on a computer or paper-based (manual)

• True

• False

Database Overview

A collection of information stored together in a structured and organised way

Databases are arranged using tables, records and fields

Each record has a fields that uniquely identifies (separates) that record from others. This is the primary key

Multiple Choice

In a database for an online shopping company, all the information about a single customer would be stored as a ...

• Field

• Record

• Relationship

• Table

Multiple Choice

In a database for an online shopping company, all the customer information would be stored as a ...

• Field

• Record

• Relationship

• Table

Flat-file

• Repeated data (duplication)

• Greater chance for inaccuracy

• Increases data entry time

• Reduced data integrity (trust in data and query results)

• Easy to create

Relational Database

• Data stored and inputted once

• Can setup access rights for individual tables

• New tables or fields can be added without affecting other tables

• Difficult to setup with specialised software required

Multiple Choice

A database has multiple tables with links. What kind of database is it?

• Flat file

• Relational

Multiple Choice

Which kind of database is easier to setup?

• Flat file

• Relational

Multiple Choice

Which kind of database would require greater computer power and resources to run queries (searches)?

• Flat file

• Relational

Multiple Choice

A service allows students to rent videos. The ERD shows the tables related using a many-to-many relationship.

Which diagram would show a more efficient setup?

Multiple Choice

A delivery company sends parcels across the UK.

The database contains a table called parcel and a table called customer.

Which entity relationship diagram shows the parcel and customer relationship?

Structured Query Language

• Allows us to interrogate databases

• Allows modification and creation of databases

• Can be use inputs from web pages as criteria and return query results

SELECT query

SELECT Name, Class

FROM Student

WHERE [ID No.] > 500

Multiple Choice

SELECT First_Name

FROM Employee

WHERE Title = "Programmer" AND Gender = "F"

Would return what?

• Yolanda

• Yolanda and Jennifer

• Jennifer

UPDATE query

UPDATE Student

SET Name="Pete"

WHERE [ID No.] = 412

Multiple Choice

UPDATE Student

SET Room = 49

WHERE Room = 56

How many records would be affected?

• 0

• 1

• 2

• 3

Multiple Choice

UPDATE Student

SET Room = 40

WHERE NOT Class = "5B"

How many records would be affected?

• 0

• 1

• 2

• 3