Databases and distributed systems Flashcards
(104 cards)
1
Q
What does SQL stand for?
A
Structured Query Language
2
Q
What are the main operations performed by SQL?
A
- Searching and retrieving records
- Inserting new records
- Deleting records
- Updating existing data
3
Q
What is the standard language for working with databases?
A
Structured Query Language (SQL)
4
Q
What are SQL statements made up of?
A
Key words, such as CREATE, SELECT, INSERT, UPDATE
5
Q
How are SQL statements conventionally written for readability?
A
Key words in capital letters
6
Q
What is the termination character for SQL statements?
A
Semicolon (;)
7
Q
What type of programming language is SQL considered?
A
Declarative language
8
Q
What are the five groups of SQL commands?
A
- Data definition language (DDL)
- Data query language (DQL)
- Data manipulation language (DML)
- Data control language (DCL)
- Data transaction language (DTL)
9
Q
What is the core syntax to create a table in SQL?
A
CREATE TABLE tablename (field1 datatype, field2 datatype, …);
10
Q
What is a primary key in SQL?
A
A field or set of fields that uniquely identifies a record in a table
11
Q
What is the convention for naming table names?
A
Usually singular, e.g., customer, booking, appointment
12
Q
What is the preferred naming convention for field names?
A
Clearly define their purpose, often using upper camel case
13
Q
True or False: SQL is case sensitive.
A
False
14
Q
What is a database view?
A
A virtual table whose contents are the result of a pre-defined query
15
Q
What is the syntax to create a view in SQL?
A
CREATE VIEW name AS SELECT field1, field2, … FROM table WHERE condition;
16
Q
What are the advantages of using database views?
A
- Security and data protection
- Simplify access to data
- Standardise complex queries
- Track performance metrics
- Saving space
17
Q
What does DQL stand for?
A
Data Query Language
18
Q
What is the basic syntax of a SELECT statement?
A
SELECT field1, field2,… FROM table;
19
Q
What does the SELECT * statement do?
A
Returns all available fields from a table
20
Q
How can you sort results of a SELECT statement?
A
By using the ORDER BY clause
21
Q
What are some common data types used in SQL?
A
- CHAR
- VARCHAR
- INT
- DECIMAL
- DATE
22
Q
What is the purpose of the DECIMAL data type?
A
To store fixed point decimals, allowing for no error
23
Q
What is the international standard for date formats?
A
ISO8601
24
Q
What does a composite primary key consist of?
A
Multiple fields that together uniquely identify a record
25
Fill in the blank: A field name must begin with a _______.
letter
26
True or False: Field names can include spaces.
False
27
What is the challenge in storing dates in a database?
Different systems may have different date formats and types
28
What is the significance of using a fixed decimal format for currency?
It ensures 100% accuracy in monetary calculations
29
What should the SELECT * query be used for?
Only when all fields are needed
30
What SQL clause is used to sort the results of a SELECT statement?
ORDER BY
31
How can results be sorted in ascending order?
By default, or using ASC
32
How can results be sorted in descending order?
By using DESC after the field name
33
What happens to records with equal values in a sorted field?
They appear in their original table order
34
Can records be sorted by fields not included in the SELECT statement?
Yes
35
What is the syntax for filtering records in a SELECT query?
SELECT field1, field2... FROM table WHERE selection-criteria
36
What SQL clause allows filtering of records based on specified criteria?
WHERE
37
What caution must be taken when using numeric values in a WHERE clause?
Do not enclose numeric values in quotes
38
What is the purpose of comparative operators in SQL?
To evaluate conditions in the WHERE clause
39
True or False: The WHERE clause can include logical operators like AND and OR.
True
40
What is a common mistake when using logical operators in SQL?
Not repeating the field name in the criteria
41
What wildcard symbol is used to represent zero or more characters in LIKE?
Percent sign (%)
42
What must string values in selection criteria be enclosed in?
Quotation marks
43
How should a date be specified in a WHERE clause?
In the same format as shown in the exemplar data, enclosed in quotes or # marks
44
What is the syntax for a multi-table SELECT query?
SELECT field1, field2... FROM table1, table2... WHERE join-criteria
45
What is an INNER JOIN used for in SQL?
To display details of records that have matching entries in both tables
46
What is the alternative syntax for an INNER JOIN?
SELECT field1, field2... FROM table1 INNER JOIN table2 ON join-criteria
47
What must be specified when selecting data from more than one table?
How the tables are joined
48
What is the basic syntax for inserting new records into a table?
INSERT INTO table (field1, field2...) VALUES (value1, value2...)
49
What should be considered when inserting records with constraints?
Conform to constraints and data types
50
What is the syntax for updating records in a table?
UPDATE table SET field1 = value1, field2 = value2... WHERE criteria
51
What is a crucial consideration when using the WHERE clause in an UPDATE statement?
If omitted, all records in the table will be updated
52
What should be done to ensure a single record is updated?
Select by primary key
53
What is the benefit of using an index in a search of a database?
To speed up the search process
54
What is the result of searching a database using a primary key?
A single matching record
55
What is data integrity?
The accuracy, consistency, and reliability of data throughout its lifecycle.
56
What is an insertion anomaly?
A problem that occurs when new data cannot be added without the presence of other data.
57
What is an update anomaly?
A situation where data inconsistency results from updating the same data in multiple places.
58
What is a deletion anomaly?
Unintended loss of data due to the deletion of other data.
59
What does SQL stand for?
Structured Query Language.
60
What is SQL used for?
Querying, updating, inserting, and deleting data in a relational database.
61
Why is SQL useful in relational databases?
It can extract information across related tables using joins and queries.
62
What is an entity in a database?
A real-world object or concept that is stored in a database table.
63
What is an attribute in a database?
A property or characteristic of an entity; stored as a field in a table.
64
What is metadata?
Data about data, such as field types, descriptions, and constraints.
65
What is an index in a database?
A data structure that improves the speed of data retrieval operations.
66
What are the disadvantages of poor database design?
Redundancy, anomalies, inefficient queries, and maintenance difficulties.
67
What is normalisation?
The process of organizing data to reduce redundancy and improve integrity.
68
Why is normalisation important?
It ensures data is logically stored and relationships are efficient and consistent.
69
What is the first normal form (1NF)?
A table where all fields contain only atomic (indivisible) values and each record is unique.
70
What is the second normal form (2NF)?
A table in 1NF with no partial dependencies on a composite primary key.
71
What is the third normal form (3NF)?
A table in 2NF with no transitive dependencies (non-key fields depend only on the key).
72
What is a composite key?
A primary key made up of more than one field.
73
What is a surrogate key?
A system-generated unique identifier used as a primary key.
74
What does a one-to-many relationship mean in databases?
One record in a table can relate to many records in another table.
75
Give an example of a one-to-many relationship.
One instructor teaches many courses.
76
What does many-to-many relationship mean?
Many records in one table relate to many in another; usually resolved with a junction table.
77
What is a junction table?
A table used to handle many-to-many relationships between two tables.
78
What is a client-server model in databases?
A system where a database server provides resources to client devices over a network.
79
How does a DBMS support data security?
Through user accounts, roles, and access rights to control who can view or modify data.
80
What is data?
Values, measurements, facts or observations that are collected and structured for processing.
81
What is information?
Data that has been interpreted to have meaning within a context.
82
What is a database?
An organised collection of related data.
83
What is a flat file database?
A database where all the data is stored in a single table.
84
When is a flat file database appropriate?
When the data is small, mostly static, and not heavily interrelated.
85
What is a relational database?
A database where data is stored in multiple related tables.
86
Who developed the relational model?
Edgar Codd in the late 1960s.
87
What is a table in a database?
A structure made of rows and columns to store records and fields.
88
What is a field in a database table?
A column representing one attribute or category of data.
89
What is a record in a database table?
A row representing a single set of related data or an instance of an entity.
90
What is a primary key?
A field (or combination) that uniquely identifies each record in a table.
91
What are the rules for a primary key?
It must be unique and cannot be null.
92
What is a foreign key?
A field that appears as the primary key in another table to establish a relationship.
93
What does a foreign key enforce?
Referential integrity between related tables.
94
Can a table have more than one foreign key?
Yes, a table can have multiple foreign keys referencing different tables.
95
Why is MemberId a good primary key in the Sports Club example?
Because it is unique for each member, unlike names or phone numbers.
96
What is the purpose of the Certificate table in the Sports Club example?
To record assessments by linking members, courses, and instructors.
97
Why is space saving not a benefit of databases?
Databases often require extra storage for indexes, metadata, and pointers.
98
Why don't all users use databases?
They require setup, design skills, and understanding of normalisation—flat files or spreadsheets may be easier for some.
99
What are some examples of database applications?
DBMSs, CRM systems, web apps, and social media platforms.
100
What is a DBMS?
A software tool that allows administrators to manage relational databases.
101
What are examples of DBMSs?
MySQL, SQLite, and others.
102
What is MySQL?
An open-source DBMS often used with a GUI like phpMyAdmin.
103
What is SQLite?
A lightweight, open-source DBMS stored as part of an application—ideal for embedded systems.
104
What does a DBMS hide from users?
The complexity of the physical storage and implementation.
