Section 11: Databases and Software Development Flashcards
Chapter 64:
What does SQL stand for?
Structured Query Language.
Chapter 64:
What command is used to retrieve data from an SQL database?
SELECT.
Chapter 64:
What does the SQL command SELECT do?
Retrieves data from a database.
Chapter 64:
What is the syntax for an SQL SELECT statement?
SELECT column, another_column, …
FROM my_table;
Chapter 64:
What command is used to indicate the table that is to be searched in an SQL database?
FROM.
Chapter 64:
What does the SQL command FROM do?
Used to indicate the table that you want to search.
Chapter 64:
What command is used to filter searches in SQL?
WHERE.
Chapter 64:
What does the SQL command WHERE do?
Used to filter searches.
Chapter 64:
What is the syntax for SQL WHERE statement?
SELECT column, another_column, …
FROM my_table
WHERE condition;
Chapter 64:
What are the 4 main commands in SQL?
SELECT
FROM
WHERE
ORDER BY
Chapter 64:
What command is used to order the search results in an SQL database?
ORDER BY.
Chapter 64:
What does the SQL command ORDER BY do?
Orders the search results from an SQL database.
Chapter 64:
Write a general SQL query using SELECT, FROM, WHERE, and ORDER BY.
SELECT column, another_column, …
FROM my_table
WHERE condition AND/OR another_condition
ORDER BY field;
Chapter 64:
How do you select a whole table in an SQL query?
SELECT *
FROM my_table;
Chapter 64:
What does SELECT * do?
Selects all fields in a table.
Chapter 64:
There are 13 SQL conditions.
(6 Arithmetic, 4 General, and 3 Logic)
List them.
Arithmetic = Equal to. > Greater than. < Less than. >= Greater than, or equal to. <= Less than, or equal to. <> Not equal to.
General
IN Equal to a value in a set of values.
LIKE Similar to.
BETWEEN … AND Within a range (inclusive).
IS NULL Field doesn’t contain a value.
Logic
AND Both expressions must be true.
OR Either expression must be true.
NOT True if false, false if true.
Chapter 64:
In SQL, how do you SELECT Fields from multiple different tables?
SELECT table1.field, table2.field
You can just use field if the field name is not defined in another table.
Chapter 62:
What is a Data Entity?
A Data Entity is a category of object, person, event, or thing of interest to an organisation about which data is to be recorded.
Examples: Employee, Actor, Recipe, Player Character.
Chapter 62:
What is a Data Attribute?
Data Attributes are the data categories that make up a Data Entity.
A characteristic of an object.
Examples: Location, Type, Health, Velocity.
Chapter 62:
A Dentist’s Surgery employs several dentists, and has many patients. They book appointments for patients to see specific dentists at specific times.
What are the Entities, and what are the Attributes in a database for this scenario?
Entities:
Dentists, Patients, Appointments.
Attributes of Dentists:
Dentist ID, Firstname, Surname, Qualification, Phone Number…
Attributes of Patients:
Patient ID, Firstname, Surname, Dentist ID (?), Address, Phone Number…
Attributes of Appointments:
Appointment ID, Dentist ID, Patient ID, Date, Time…
Chapter 62:
What form do Entity Descriptions usually take?
Entity1 ( Attribute1, Attribute2… )
Chapter 62:
What is an Entity Identifier?
An Attribute that is unique for every Entity in the database.
Known as a Primary Key in a Relational Database.
In most cases, the Entity Identifier / Primary Key will be an arbitrary ID, rather than another field (e.g. Name) as they can conflict (i.e. different people can have the same name).
Chapter 62:
Is a National Insurance Number a suitable Primary Key for a Patient?
Why?
No.
National Insurance numbers are issued to residents of their country when they turn 16.
This means that Patients under the age of 16 and/or not originating from the operating country will not have a National Insurance Number.
{
Patients often don’t know their National Insurance Number either.
}
Chapter 62:
What are the three different types of relationship between Entities?
one-to-one
one-to-many
many-to-many
Chapter 62:
What is an example of a one-to-one Entity Relationship?
Headteacher and School.
A Person is Headteacher of only one school;
A School has only one Headteacher.
Chapter 62:
What is an example of a one-to-many Entity Relationship?
Customer and Orders.
A Customer can have many orders;
An Order is ordered by only one Customer.
Chapter 62:
What is an example of a many-to-many Entity Relationship?
Students and Courses.
A Student can take many Courses;
A Course can be taken by many Students.
Chapter 62:
What is a Relational Database?
A Database where a separate table/”relation” is created for each Entity identified in the system. They can interact, as they share attributes.
Each row holds a Record (instance of Entity).
Each column holds an Attribute.
Chapter 62:
What are Primary Keys and Foreign Keys?
When an Attribute is shared by multiple tables in a Relational Database, they can interact with each other.
The Primary Key is the source Attribute.
The Foreign Key is the copy in the secondary table.
Chapter 62:
What is the point in using a Relational Database?
To cut down on repeat data.
For Example:
{ID, Firstname, Surname}
for a given ID, the Firstname and Surname will not be variable (the ID is unique). This means that only the ID needs to be used in the main table, and the Firstname and/or Surname can be retrieved from a branch table.
Chapter 62:
How can Primary Keys and Foreign Keys be identified in a Relational Database?
Primary Keys are followed by an asterisk (*).
Foreign Keys are written in italics.
Chapter 62:
What is a Composite Primary Key?
Where the Primary Key is comprised of more than one attribute.
For Example:
PRIMARY KEY( Attribute1, Attribute2 )
[In SQL]
Chapter 63:
The Book entity has several attributes: BookID, DeweyCode, Title, Author, DatePublished.
How would this be written?
Book( BOOKID, DeweyCode, Title, Author, DatePublished ).
Entity name on the outside of the brackets.
Attributes listed inside the brackets.
Primary key is UNDERLINED (underline not available, so caps used instead).
