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).
Chapter 63:
What is the idea of Database Normalisation?
Process of adapting a Relational Database to give it a more efficient design without losing data.
Chapter 63:
What are the four main objectives of Normalisation?
No data is unnecessarily duplicated.
Data is consistent (Address is different in different places) (fixed by removing duplicates).
Structure of each table is flexible enough to allow for more or less items.
Structure should allow for Complex Queries relating to different tables.
Chapter 63:
What is the default (one table) state of a database called in Normal Form notation?
0NF
Not Normalised.
Chapter 63:
What requirements must be met for a Database to be in First Normal Form (1NF)?
There must be No Repeating Data.
Chapter 63:
What requirements must be met for a Database to be in Second Normal Form (2NF)?
Must be in 1NF and have no partial dependencies.
Chapter 63:
What problems arise from using unnormalised Databases?
Repetition of data; requires more space.
Repetition also leads to anomalies in:
Insertion,
Deletion,
Updation.
Chapter 63:
What is the Insertion Anomaly when using 0NF?
Repeated Data is inserted for every new entity.
This is especially problematic when hundreds of entities are to be entered.
Chapter 63:
What is the Deletion Anomaly when using 0NF?
Deleting one dataset can Delete another as well.
Example: School: Students are entities.
Removing Students at the end of the year will also remove Teacher Data, as the Teacher Data is not stored elsewhere.
Chapter 63:
What is the Updation Anomaly when using 0NF?
Also know as Modification Anomaly.
When repeated data is changed, it must be changed for every instance of the data.
If any instance is missed, there will be inconsistent data.
Chapter 63:
What is Partial Dependency in a Database?
When a Composite Primary Key is used (Primary Key = 2 or more Attributes/Fields), And there is data that is consistent for one of the Primary Key Fields, but is not affected by the others.
Example: School Marks
Primary Key = (StudentID, TeacherID)
Attribute = TeacherName.
There is a Partial Dependency between TeacherName, and TeacherID; TeacherName relates to TeacherID, but not to StudentID.
Chapter 63:
What is Transitive Dependency in a Database?
When Attribute1 depends on Attribute2 but not on the Primary Key (or any component of the Composite Primary Key).
Chapter 63:
What requirements must be met for a Database to be in Third Normal Form (3NF)?
Must be in 2NF and have no Transitive Dependencies.
Chapter 65:
What command can be used to Create a Table in SQL?
CREATE TABLE TableName
(
{tableData}
)
Chapter 65:
Inside an SQL Table, how would you define an Integer?
CREATE TABLE TableName ( fieldName INTEGER, fieldName2 INTEGER )
Chapter 65:
Inside an SQL Table, how would you define a Character String of variable length, max 20?
CREATE TABLE TableName ( fieldName VARCHAR(20), fieldName2 VARCHAR(20) )
Chapter 65:
Inside an SQL Table, you can have Attributes that are compulsory.
How would you create a compulsory Integer?
CREATE TABLE TableName ( fieldName INTEGER NOT NULL, fieldName2 INTEGER NOT NULL )
Chapter 65:
Inside an SQL Table, you need a Primary Key.
How would you define an Attribute as a Primary Key?
CREATE TABLE TableName ( fieldName INTEGER, PRIMARY KEY, fieldName2 INTEGER )
Chapter 65: There are 8 common Data Types in SQL: 2 Character Types, 1 Boolean Type, 3 Number Types, 2 Time Types.
What are they?
CHAR(n) Character string of fixed length n
VARCHAR(n) Character string of variable length, max = n
BOOLEAN TRUE or FALSE
INTEGER Integer (whole number)
FLOAT Number with floating decimal point (decimal number)
CURRENCY Formats numbers in the currency used in your region.
DATE Stores Day, Month, Year values
TIME Stores Hour, Minute, Second values
Chapter 65:
What statement can be used to add, delete, or modify columns (Attributes/Fields) in SQL?
ALTER TABLE.
Chapter 65:
How would you Add a Field to an SQL Table?
ALTER TABLE Table
ADD fieldName INTEGER
Chapter 65:
How would you Delete a Field from an SQL Table?
ALTER TABLE Table
DROP COLUMN fieldName
Chapter 65:
How would you Modify/Update a Field in an SQL Table?
ALTER TABLE Table
MODIFY COLUMN fieldName INTEGER NOT NULL
Chapter 65:
What commands are used to Insert a new Entity into a Table in SQL?
INSERT INTO,
and VALUES.
Chapter 65:
How do we Insert a new Entity into a Table in SQL?
INSERT INTO TableName ( fieldName1, fieldName2… )
VALUES ( value1, value2… )
Chapter 65:
From the standard syntax, what shortcut can we take when Inserting a new Entity into a Table, assuming that all fields are to be added to in the new Entity?
fieldNames can be ignored;
INSERT INTO TableName
VALUES ( value1, value2… )
is fine.
Chapter 65:
What commands are used to Change an existing Entity in a Table in SQL?
UPDATE,
SET,
and WHERE.
Chapter 65:
How do we Update an Entity in a Table in SQL?
UPDATE tableName
SET fieldName1 = value1, fieldName2 = value2, …
WHERE fieldNameX = value
Chapter 65:
What commands are used to Delete an Entity from a Table in SQL?
DELETE FROM
and WHERE.
Chapter 65:
How do we Delete an Entity from a Table in SQL?
DELETE FROM tableName
WHERE fieldName = value
Chapter 65:
What is a Client-Server Database?
A Server stores a Database. Clients can send requests to the Server and the Server will send back a copy of a part of the Database.
With a regular Database, it will send a full copy back to the Client, which can slow down the system, as more data is being sent, and then it is filtered by a (probably) less powerful machine.
Chapter 65:
What are the advantages of using a Client-Server Database?
Database Consistency is maintained, as there is only one copy of the data (on the server), rather than on every machine.
Database is made available to a large number of users.
Access Rights and Security can be managed centrally.
Backup and Recovery can be managed centrally.
Chapter 65:
What are the disadvantages of using a Client-Server Database?
Allowing multiple users to simultaneously update a database may cause one of the updates to be lost, unless measures are taken.
This can be especially problematic when multiple different departments need to access the same record at the same time (i.e. finance accesses credit score, and customer service accesses address).
Chapter 65:
There are four main ways of avoiding data loss with a Client-Server Database.
List them.
Record Locks,
Serialisation,
Timestamp Ordering,
Commitment Ordering.
Chapter 65:
How does Record Locking work in Client-Server Databases?
A Record is Locked when the first person tries to access it; if you try and open a record that is being accessed by someone else, you will be denied access until they are done.
Chapter 65:
One problem that can arise when using Record Locking in a Client-Server Database is Deadlock.
What is Deadlock?
If two clients access one account each, and try to transfer money (for example) from their own to each other’s, they will both lock their own Record, so that it cannot be accessed by the other.
Chapter 65:
What is Serialisation in a Client-Server Database?
Technique to combat Deadlocking in Record Locking.
Transactions will be run serially (one at a time), preventing a Deadlock.
Chapter 65:
What is Timestamp Ordering in a Client-Server Database?
Whenever a transaction starts, it is given a timestamp, so that if two transactions affect the same object, the transaction with the earlier timestamp should be applied first.
Every object has a Read Timestamp, and a Write Timestamp that are updated whenever an object in a database is read or written to.
When a client reads an object, it will store the timestamps. When the client goes to write, it will first check the stored timestamps to the current timestamps. If they are the same, data has not been changed.
Chapter 65:
What is Commitment Ordering in a Client-Server Database?
Serialisation Technique that orders transactions in terms of their dependencies on each other as well as the time they were initiated.
Chapter 66:
What are the four main parts of a good Analysis?
The Data - Origins, Uses, Volumes, Characteristics.
The Procedures - What is done? When? Where? How?
How are Errors and Exceptions handled?
The Problems - What Errors still exist inside the program, what effect they have.
The Future - How could the Project be improved , given more time.
Chapter 66:
What makes a successful Software Development project?
Simple Model.
Rapid User Feedback.
Understanding that user requirements may change.
Being prepared to make Incremental Changes.
Chapter 66:
What are the seven main design features that the system designer must consider?
Processing.
Data Structures.
Input.
Output.
User Interface.
Security.
Hardware.
Chapter 66:
What process is used to model a complex idea in a simple way?
Abstraction.
Removing data that is not important for solving the problem.
Chapter 66:
Why are Prototypes / Demos important for a Software Developer?
So that the user can test how arduous the program is to use, and so that features can be implemented to correct these issues.
Prototypes / Demos also means that more people are using the program, which will make errors more apparent, which helps the Developer to locate and fix them.
Chapter 66:
What are the five main types of Testing?
Unit Testing, Module Testing, Sub-system Testing, System Testing, Acceptance Testing.
Chapter 66:
What is Unit Testing?
Testing a Unit of the Project. E.g. a header file.
Chapter 66:
What is Module Testing?
Testing a Subroutine, or Class. I.e. subset of a Unit.
Chapter 66:
What is Sub-system Testing?
Carried out prior to System Testing, Sub-system Testing is testing the program against a part of the system.
For example, if there are components of the hardware that aren’t in place yet, you can test the software on the hardware that is available.
This can also help locate errors.
Chapter 66:
What is System Testing?
Testing the whole Project against the System. E.g. Testing the effect of different Inputs, peripherals, Operating Systems.
Chapter 66:
What is Acceptance Testing?
Where a potential User tests the product, to see if it works how they want it to. I.e. Test the functionality and the arduousness of the Program.
Chapter 66:
What is an Evaluation in Software Development?
A Post-Implementation Review / Breakdown.
Usually made 3-6 months after the finished product, as to allow for users to learn how to use the system, and get experience before giving feedback. The waiting period can also reduce Developer Bias, as they have likely learned new techniques or worked on different projects since the completion of the project in question.
Chapter 66:
What three features of a project should a good Evaluation focus on?
Effectiveness,
Usability,
Maintenance.
