Comsci > Databases > Flashcards

Databases Flashcards

(63 cards)

Study These Flashcards
1
Q

What is a data model?

A

An abstract description of the essential data within a given context and how the data is related.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is an entity?

A

A district thing about which data must be stored.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is an attribute?

A

A single property of an entity.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What is an entity identifier?

A

An attribute, or a collection of attributes, that uniquely identifies each instance of an entity.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What is a composite entity identifier?

A

Multiple attributes are required to uniquely identify an instance of an entity

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What do you need to know when designing a data model?

A
  • Data that will be stored
  • Real-Word entities that this data refers to
  • Relationships that exist between entities
  • Constraints that belong to the data
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

How can a data model be visually presented?

A
  • Entity Description
  • Entity-relationship diagram
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What is the format of an entity description?

A

Entity(Entity Identifier, Attribute, Attribute, Attribute)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What is a relationship in an E-R diagram?

A

Two-way association or link between two entities

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What are the different types of relationships in an E-R diagram?

A
  • One-to-Many
  • Many-to-Many
  • One-to-One
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

How are entities represented in E-R diagrams?

A

A rectangle with the name of the entity inside it. The name is always singular.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What is a Relational Database?

A

A collection of tables between which relationships are created through common attributes (primary and foreign keys)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is a primary key?

A

An attribute that will uniquely identify a particular instance of an entity.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What is a composite primary key?

A

Two or more attributes which together uniquely identify a particular instance of an entity

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is a foreign key?

A

An attribute in one table which is the primary key attribute in another table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

How can we store required data from a data model?

A
  • In an OOP language
  • Relational Databases
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

How do we turn a data model into a relational database?

A

Entity —> Table
Attribute —> Field
Instance of an entity —> Record

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What is a Flat File Database?

A
  • Simplest way of storing data in a text file (e.g. CSV)
  • Each line represents a record, with fields separated by commas
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What are the advantages and disadvantages of flat file databases?

A

+ Easy to create and use for small databases
- Inefficient as the data grows

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What are the advantages of relational databases?

A
  • Scales better as a data set grows
  • Avoids data duplication/redundancy
  • Avoids data inconsistencies
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What is a data dictionary?

A
  • Description of the tables of a relational database
  • Presented as a table describing one entity/table in the database
  • Shows constraints on data
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What are the main data types used in databases?

A
  • Integer
  • Float
  • Text - any length
  • Varchar(X) - maximum length X
  • Date
  • DateTime
  • Boolean
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What is the structure of a SELECT query?

A

SELECT field1, field2, field3..
FROM table
WHERE condition
ORDER BY fieldname ASC|DESC

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

How is the LIKE key-word used in a query?

A

SELECT ….
WHERE fieldname LIKE “string%”

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
What are the different ways you can use LIKE and placeholder values?
“%string” = ends with the string “string%” = starts with the string “%string%” = contains the string
26
How can you use the BETWEEN key-word in queries?
SELECT … FROM … WHERE date BETWEEN “01/01/2025” AND “31/01/2025” (This is inclusive)
27
How can you query multiple tables?
SELECT table1.field1, table1.field2, table2.field1 FROM table1 **JOIN table2 ON table1.foreignKey = table2.primaryKey**
28
How do you update records in SQL?
UPDATE table SET fieldname = value WHERE condition
29
How do you delete records in SQL?
DELETE FROM table WHERE condition
30
How do you create a new table using SQL?
CREATE TABLE table_name ( PrimaryFieldName dataType PRIMARY KEY, Field1 dataType constraints, Field2 dataType constraints, )
31
How can you use INSERT INTO in an SQL query?
INSERT INTO tableName (field1, field2, field3) VALUES (value1, value2, value3)
32
What is data normalisation?
All non-key attributes depend upon the key, the whole key and nothing but the key (and there are no repeating groups)
33
What is data redundancy?
Data that is unnecessarily duplicated within a database
34
What is an update anomaly?
Inconsistent data as a result of updating multiple instances of the same data
35
What can data redundancy result in?
- Increased storage costs: require larger storage capacities, including backups - Reduced performance: makes queries much slower as more data to process - Data inconsistency
36
What are the benefits of normalisation in databases?
- Minimising data duplication - Eliminating data redundancy - Eliminating data inconsistencies - Eliminates update and insertion anomalies
37
What are the different degrees to which data can be normalised called?
Normal forms
38
What is a client-server database?
A specific instance of a client-server application that provides simultaneous access to a database for multiple clients
39
What is the lost update problem?
When two users attempt to update the same record simultaneously resulting in one of the updates being ‘lost’
40
What is a record lock?
A ‘lock’ applied to the record when a **transaction** on the record starts
41
What are the advantages of a client-server application model?
- Data is stored in one place - Centrally managed and backed up - Data can be easily shared across client services
42
Why is it better for data and resources to only be stored in one place in a client-server database?
- There is no need for duplication and distribution of data - No redundant data - Risk of data integrity issues if data is out-of-date
43
What are the disadvantages of the client-server application model?
- Everything could be lost or inaccessible if the server is unavailable - Simultaneous access of resources can cause congestion and poor performance - Multiple user access for a resource can result in conflict
44
What are some ways to deal with concurrent access?
- record locks - serialisation / transaction queueing - timestamp ordering - commitment ordering
45
What are the disadvantages of record locks?
- They can result in deadlock - They do not scale well for large databases and lead to performance issues
46
What is concurrent access?
When different users try to access the same record at the same time
47
What does 3rd normal require?
- **No transitive partial key dependancies** Also: - Atomic Data - No repeating groups - No duplicate records
48
What is atomic data?
Data that has been fully decomposed into multiple attributes
49
What are repeating groups?
Two or more fields store data for the same attribute with a single record
50
What are partial key dependencies?
When there is a composite key, not all non-key attributes relate to the whole key
51
What are transitive partial key dependencies?
Non-key attributes that depend/relate to other non-key attributes
52
What is an insertion anomaly?
Two clients insert records into a database simultaneously that create a logical conflict
53
What is a time stamp?
A value that marks the point in time at which a transaction was started
54
What is a transaction?
A single unit of work in a database that can consist of one or more queries
55
What is deadlock?
When two or more transactions have placed locks on records that they are dependant upon
56
What is serialisation?
Handles concurrent access issues by ensuring that transactions are done in a logical order so they do not interfere with one another.
57
What is transaction queuing?
Transactions are put into a processing queue such that when one is finished the next is started.
58
What is timestamp ordering?
- Each transaction is assigned a timestamp - Older transactions are processed before newer ones - Databases records timestamps of last successful read/write transactions for each record - Server applies rules to transactions before committing to determine whether it will result in the loss of data integrity
59
In timestamp ordering, what does the database store for each record?
The timestamp of the last successful read or write transaction for each record.
60
What are the timestamp ordering rules?
- Read: TS(T) >= WT(X) - Write: TS(T) >= RT(X)
61
What is commitment ordering?
An algorithm is used to determine an optimum order to perform transactions by tracking conflicts so that they can be resolved in the correct order or abandoned.
62
What is a commitment in a database?
The permanent application of a change to the data in a database as a result of a transaction
63
How do record locks work?
- Maintain information about which records are currently being accessed - When a user tries to access a record, consult this information and only permit access if it is not currently being used
Comsci
flashcards
Decks in class (11)
# Cards
Brainscape helps you reach your goals faster, through stronger study habits.
© 2026 Bold Learning Solutions. Terms and Conditions