Fundamentals of Databases Flashcards
What is a field?
A column in a database
What is a record?
A row in a database
What is a primary key?
A unique field in a database table which indexes a record
Identified by an underline
What are the three entity-relationships?
One-to-one
Many-to-many
One-to-many
What is a foreign key?
A foreign key is a column in a database table that links to the primary key of another table, establishing a connection between the two tables.
Why are databases normalised?
Database normalization is done to reduce data redundancy and improve data integrity, ensuring consistency and efficient use of storage.
What is First Normal Form?
Contains only atomic values; each column has only one value.
A column should only contain values of the same type.
Each row holds a unique set of data; doesn’t have repeating field names.
It doesn’t matter what order the data is in; we can fetch it from anywhere using a query.
What is Second Normal Form?
To be in second normal form, it must be in first normal form and not contain any partial dependency.
What is Partial Dependency?
When a primary key is made up of two columns, and an attribute depends on only one of these columns.
How do you remove Partial Dependency?
Move it to another table where it can be dependent on one key.
What is Third Normal Form?
Where there’s no transitive dependency and it’s in 2nd and 1st normal form.
What is Transitive Dependency?
A condition in which an attribute is dependent on another attribute that isn’t a part of the primary key.
How do you remove Transitive Dependency?
Split the fields which had the transitive dependency into new tables.
SELECT Command
Used to retrieve data from a database table
SELECT <column> FROM <table> WHERE <condition> ORDER BY <column><ASC/DESC></column></condition></column>
The ORDER BY is optional
- Defaults to ASC
UPDATE command
Used to modify the attributes of an existing entity
UPDATE <table> SET <column> = <value>
WHERE <column> = <value></value></column></value></column>
DELETE command
Used for removing entities from a database
DELETE FROM <table>
WHERE <condition></condition>
INSERT command
Used to add new records to an existing table
INSERT INTO <table> VALUES (<value1>, <value2> …)</value2></value1>
INSERT INTO <table> (<column1>, <column2> ...) VALUES (<value1>, <value2> ...)</value2></value1></column2></column1>
What is a Wildcard?
Used in SQL to specify ALL.
Signified by a * symbol
How to create a table?
CREATE TABLE <table_name>
(<column-name> <column-type>,</column-type></column-name></table_name>
<column-name> <column-type>
…
PRIMARY KEY (<column-name>)
)
data types include..... VARCHAR, INT, SMALL MONEY etc
</column-name></column-type></column-name>
What is a client-server database?
A client-server database is a system where one server machine hosts a database and multiple client machines access it over a network. The server manages, stores, and retrieves the data as requested by the clients.
What is Concurrent Access?
This issue arises when multiple users try to access the same field at the same time, leading to potential loss of database updates. It can be managed using record locks, serialisation, timestamp ordering, and commitment ordering.
What are Record Locks?
When a field is accessed by a user, it gets locked for other users until the first user is done. This prevents others from accessing or modifying the field until it’s unlocked.
What is Serialisation?
Instead of locking a field, requests from other users are queued. After the first user is done, the next command in the queue is executed.
What is Timestamp Ordering?
Each command sent to the same field in a database is assigned a timestamp. Commands are executed based on their timestamps.
What is Commitment Ordering?
An algorithm is used to determine the optimal order to execute commands for the same field. This algorithm considers the impact of commands on other parts of the database to minimize potential issues.
What is an INNER JOIN in SQL?
An INNER JOIN combines rows from two tables based on a related column between them. It’s like matching and merging information from two different lists based on a common key.
SELECT Orders.OrderID, Customers.CustomerName, Orders.Product
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
