4.10 Fundamentals of databases Flashcards
(26 cards)
what is an entity and attributes
an entity is a real-world object or a concept in a database that data is stored about
an attribute is a piece of information that describes the entity
what is a database
an organised collection of data that can be accessed, managed and updated - they consist of multiple tables where each table stores data about a specific entity, in a table each row represents one instance of the entity and each column holds an attribute describing that entity
what is the form of an entity description
entity_name (entitiy_identifier, attribute1, attribute2)
what is a relational database
when tables in a database can be linked with a common attribute
what are the three types of relationships between tables
one-to-one - each item has another item e.g each car has an owner
one-to-many - each item has many items e.g each car has many passengers
many-to-many - many items have many items e.g a driver can have many cars and a car can have many drivers
what is a primary key
an attribute that provides a unique identifier for each entity in the table
what is a foreign key
a primary key from another table used to link the two tables together
what is a composite primary key
a primary key made up of two or more fields in a table that together uniquely identify a record
why are databases normalised
to reduce data redundancy, prevent update anomalies and ensure data is stored efficiently and consistently - therefore faster searching and sorting
what is first normal form
requires that all fields in a table does not contain repeating attributes - data is also atomic (single column only contains one value)
what is second normal form
first normal form must also be satisfied, every non-key field depends on the whole primary key, not just part of it - important when the primary key has more than one field (a composite key)
what is third normal form
second normal form must also be satisfied, all non-key fields are only dependent on the primary key, not on other non-key fields
SQL to retrieve data from a table
SELECT <attribute> FROM <table> WHERE <condition> ORDER BY <ASC/DESC></condition></attribute>
SQL to update data in a table
UPDATE <table> SET <attribute> = <value> WHERE *<attribute> = <value>*
* - primary key</value></attribute></value></attribute>
SQL to delete data from a table
DELETE FROM <table> WHERE <condition></condition>
SQL to insert data into a table
INSERT INTO <table> VALUES (<value1>,<value2>)</value2></value1>
What is a wildcard in SQL commands
used to specify all attributes - notated with an asterix (*)
how to define a table using SQL
CREATE TABLE <table_name>(<attribute><type>,<attribute><type>, PRIMARY KEY (<attribute>)) - when defining primary key the attribute should already be defined</attribute></type></attribute></type></attribute></table_name>
what are the data types and how to define in SQL
fixed length string : CHAR(size) - string with specific size
variable length string: VARCHAR(size) - string with number of characters up to specified size
integer: INT(size) - whole number with number of bits specified
number with fractional part: FLOAT(size, precision) - number stored using number of bits specified and number of digits after decimal point also specified
date: DATE - format YYYY-MM-DD
date and time : DATETIME - format YYYY-MM-DD HH:MM:SS
time: TIME - format HH:MM:SS
year: YEAR - either YY or YYYY
describe a client server database system
provides simultaneous access to the database for multiple clients
what is a concurrent access problem and what can occur because of this
when two users attempt the same field at the same time, can lead to database updates being lost
what are the ways that concurrent access problems can be managed
record locks, serialisation, timestamp ordering, commitment ordering
how are record locks used to manage concurrent access
when a record is accessed by a user, it is immediately locked to other users until the first user has finished
how is serialisation used to manage concurrent access
requests from other users are places in a queue
